背景
以下のように日付文字列とExcel特有のシリアル値が混在したCSVファイルを仕事で扱うことがあった
| ID | 年月日 |
|---|---|
| 1 | 2024/12/21 |
| 2 | 2024-12-21 |
| 3 | 45647 |
これをそのままpandas.read_csvで読み込むと、当然このようなデータフレームになる
| ID | 年月日 |
|---|---|
| 1 | 2024/12/21 |
| 2 | 2024-12-21 |
| 3 | 45647 |
目的
今回の目的は上記のdfの年月日列の値をdatetime64[ns]型に統一したい
課題
この目的を達成するために、下記2つの課題を順番に解決していくことが必要になる
- Excelのシリアル値を日付文字列に置換する
- 日付文字列全てをdatetime64[ns]型に変換する
課題1の解決策
結論から述べると、シリアル値に対して以下を実行する
pd.to_timedelta(df.loc[2, "年月日"].astype("float") - 2, unit="D") + pd.to_datetime("1900/1/1")
-
1900/1/1起点になる理由
- Excelのシリアル値では1900/1/1を「1」としている
-
2を引いている理由
- Excelでは1900年が閏年扱いとなっている（実際は閏年ではないので、Excelのバグらしい）
このあたりの検証については、下記の記事を作成されている方が詳しく説明してくださっています
課題2の解決策
日付文字列をdatetimeに変換するには、pandas.to_datetime()を用いる
pd.to_datetime(df.loc[0:1, "年月日"])
※注意
to_datetimeの引数errorsにcoerceを下記のように設定すると、日付文字列でない不適切な文字列に対してNaT(Not a Time)が返されるようになる。
この引数は便利だが、使う際は仕様として適切か検討してください
pd.to_datetime(df.loc[0:1, "年月日"], errors="coerce")
解決策を用いて作成したメソッド
import pandas as pd
from datetime import datetime
def convert_to_datetime(df: pd.DataFrame, list_columns: list):
for column in list_columns:
# astype()は、データ型(dtype)を変更する
df[column] = df[column].astype(str)
# 対象カラムの値に「-」または「/」を含むものをTrueとする
bool_reg_date = df[column].str.match("^.*[-|/].*")
# Excelのシリアル値が入っている行のみを取り出す
df_excel = df[~bool_reg_date].copy()
# 日付文字列が入っている行のみを取り出す
df_reg = df[bool_reg_date].copy()
# 日付文字列をdatetime型に変換する
df_reg[column] = pd.to_datetime(df_reg[column], errors="coerce")
# Excelのシリアル値をdatetime型に変換する
# Excelでは1900年が閏年扱いとなっているため、2を引く
df_excel[column] = pd.to_timedelta(df_excel[column].astype(float) - 2, unit='d') + datetime(1900, 1, 1)
# データを結合する
df[column] = pd.concat([df_reg[column], df_excel[column]])
return df
このメソッドを作成するにあたり、下記を参考にさせていただきました
最後に
このメソッドを用いてデータフレームの日付をdatetimeに統一すると、データフレームは下記のようになります
| ID | 年月日 |
|---|---|
| 1 | 2024/12/21 |
| 2 | 2024/12/21 |
| 3 | 2024/12/21 |
またprint(df["年月日"].dtype)を用いて、統一後のデータフレームの年月日列のdtypeを確認したところ、datetime64[ns]になっていたので問題ないと思います