Pandasで日付シリアル値が混在する列をDatetime型に変更する方法

Question

解決したいこと

特定の列を、Datetime型に変換したい。

現在の状況

あるEXCELデータをPandasで読み込み、データフレームに変換しました。

以下はデータフレームの一部です。

itemID	発送日	売上
233	2018-04-10 00:08:55	2000
234	2018-04-10 00:08:56	3000
236	42279	3000

（実際は、100万行＊１５列のデータフレーム）

"日付"列を、DatetimeIndexに型変換しようとしたところ

df["発送日"] = pd.to_datetime(df["発送日"])

ValueError: mixed datetimes and integers in passed array

となります。

特定の行を調べたところ、

**2018-04-10 00:08:55
**
のようなタイプの値と、

**42729（日付シリアル値）
**
が混在していることがわかりました。

###　教えていただきたいこと

• １００万行の中から、日付シリアル値になってしまってる範囲を特定し
• 日付シリアル値をyyyy:mm:dd 00:00:00 のタイプに戻す

方法をアドバイスいただけませんでしょうか。

何卒よろしくお願いいたします。

Answer 1

とりあえず、こんな感じで変換できました

import pandas as pd


df = pd.DataFrame({
    'itemID':[233,244,236,237],
    '発送日':["2018-04-10 00:08:55",
              "2018-04-10 00:08:56",
              41516,
              '42780'],
    '売上げ':[2000, 3000, 3000, 3000]
})

# データが数値型かどうか判定
is_serial_value = df['発送日'].astype('str').str.isdigit()
# 数値型の箇所をDateTime型に変換
tmp1 = pd.to_timedelta(df.loc[is_serial_value, '発送日'].astype('float'), unit='D') + pd.to_datetime('1900/01/01')
# 文字列の箇所をDateTime型に変換
tmp2 = pd.to_datetime(df.loc[~is_serial_value, '発送日'])
# 結合して置き換え
df['発送日'] = pd.concat([tmp1, tmp2])
print(df)
#0     233  2000 2018-04-10 00:08:55
#1     244  3000 2018-04-10 00:08:56
#2     236  3000 2013-09-01 00:00:00
#3     237  3000 2017-02-16 00:00:00

Comments

[YOSHITAKEH]

ご回答ありがとうございました。無事に解決できました。

[YOSHITAKEH]

>magichan様
昨日はご回答ありがとうございました。

追加で質問させて頂いてもよろしいでしょうか。
（もし新たに質問スレッドをたてたほうがよろしければそういたします）

magichan様にご回答頂いた通りに実行すると、無事に解決できました。
しかし、一部エラーが出てしまうデータフレームがありました。

具体的には、

＜元EXCELファイル１＞＜元EXCELファイル２＞＜元EXCELファイル３＞

があり、それぞれを別々のデータフレームにし、回答頂いたような処理でdatetimeに変換しようとしました。（全てのEXCELファイルの列名は同じです。行数が異なるだけです。）

ところが、

# データが数値型かどうか判定
is_serial_value = df['発送日'].astype('str').str.isdigit()
# 数値型の箇所をDateTime型に変換
tmp1 = pd.to_timedelta(df.loc[is_serial_value, '発送日'].astype('float'), unit='D') + pd.to_datetime('1900/01/01')
# 文字列の箇所をDateTime型に変換
tmp2 = pd.to_datetime(df.loc[~is_serial_value, '発送日'])

を実行した際に、

# 文字列の箇所をDateTime型に変換

＜EXCELファイル２＞のデータフレームは、

--------------------------------------------------------------------------
TypeError Traceback (most recent call last)
/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in _convert_listlike(arg, box, format, name, tz)
376 try:
--> 377 values, tz = conversion.datetime_to_datetime64(arg)
378 return DatetimeIndex._simple_new(values, name=name, tz=tz)

pandas/_libs/tslibs/conversion.pyx in pandas._libs.tslibs.conversion.datetime_to_datetime64()

TypeError: Unrecognized value type: <class 'float'>

During handling of the above exception, another exception occurred:

ValueError Traceback (most recent call last)
<ipython-input-185-cf5b8edd84e6> in <module>()
----> 1 tmp2 = pd.to_datetime(df1.loc[~is_serial_value, '発送日'])

/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in to_datetime(arg, errors, dayfirst, yearfirst, utc, box, format, exact, unit, infer_datetime_format, origin, cache)
449 else:
450 from pandas import Series
--> 451 values = _convert_listlike(arg._values, True, format)
452 result = Series(values, index=arg.index, name=arg.name)
453 elif isinstance(arg, (ABCDataFrame, MutableMapping)):

/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in _convert_listlike(arg, box, format, name, tz)
378 return DatetimeIndex._simple_new(values, name=name, tz=tz)
379 except (ValueError, TypeError):
--> 380 raise e
381
382 if arg is None:

/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py in _convert_listlike(arg, box, format, name, tz)
366 dayfirst=dayfirst,
367 yearfirst=yearfirst,
--> 368 require_iso8601=require_iso8601
369 )
370

pandas/_libs/tslib.pyx in pandas._libs.tslib.array_to_datetime()

pandas/_libs/tslib.pyx in pandas._libs.tslib.array_to_datetime()

ValueError: mixed datetimes and integers in passed array

--------------------------------------------------------------------------------

とエラーになってしまいます。

このケースでは、

・数値型かどうかの判断
・文字列かどうかの判断

だけでは判断できない別のデータ型が実際は存在していた、ということでしょうか。

お忙しい中申し訳ございませんが、ご回答いただければ幸いです。

[magichan]

何か想定漏れのデータがあるのかもしれません。
とりあえず to_datetime() に errors パラメータを追加して
tmp2 = pd.to_datetime(df.loc[~is_serial_value, '発送日'], errors='coerce')
として動作するか確認してみてください
（このパラメータにてエラーが発生した箇所はNaTになるはずです。）
その上で
print(df.loc[~is_serial_value, :][tmp2.isnull()])
などとして、どのようなデータが NaT になったのか（エラーがでたのか）を確認してみていただけませんでしょうか。

[YOSHITAKEH]

ご回答ありがとうございます。

ご指示頂いたとおり、errorパラーメタを追加してみました。
適応したデータフレーム２つについて、状況をご報告いたします。

データフレーム（１）

is_serial_value = df['発送日'].astype('str').str.isdigit()

tmp1 = pd.to_timedelta(df.loc[is_serial_value, '発送日'].astype('float'), unit='D') + pd.to_datetime('1900/01/01')

tmp2 = pd.to_datetime(df.loc[~is_serial_value, '発送日'], errors='coerce')
を実行したらエラーは表示されませんでした。
そして、次に
print(df.loc[~is_serial_value, :][tmp2.isnull()])
を実行しました。

すると、

Empty DataFrame
Columns: [ストア, 商品名, 出品者, 発送日, 価格, 発送済み商品, 返品済み商品, 売上]
Index: []

と表示されました。

データフレーム（２）

print(df.loc[~is_serial_value, :][tmp2.isnull()])

を実行すると、

2139個のrowsが表示されました。

エラー状況としては、

１．商品名が文字化してること（例：ある革命家の回想 (1973å¹´)）
２．発送日が小数点つきの数字になっていること
（例：42644.5 42644.7 ）です。

１は関係ないのかもしれませんが、そのような状況です。

[magichan]

なるほど、少数表記のデータもあるのですね・・。
数値型か文字列（日付データ）データかの判定を str.isdigit() を使っているのですが、これは少数を判別できないので少数のデータが文字列型と判別されているのがエラーの原因でしょうね。

[magichan]

とりあえず、あまりスマートではありませんが
is_serial_value = df['発送日'].astype('str').str.replace('.','',1).str.isdigit()
とやって "." を消して isdigit() に入れるというのはどうでしょう。（指数表記だと駄目ですけど）
時間が空いたらあとからちゃんと考えます

[YOSHITAKEH]

ご回答ありがとうございます。
ご回答頂いた方法で、試してみます。
お忙しい中、ありがとうございます。

Answer 2

その文字列が整数に変換できればシリアル値として日付を算出、datetimeに変換、
そうでなければ日付の文字列としてdatetimeに変換

Comments

[YOSHITAKEH]

アドバイスありがとうございました。

Answer 3

df["発送日"] = pd.to_datetime(df["発送日"])
ではなく、
df.loc[:,"発送日"].applymap(to_dt)
でto_dt内で日付変換、以下の感じ
to_dt = lambda s: datetime.strptime(s, '%H:%M:%S') if 条件 else s

Comments

[YOSHITAKEH]

アドバイスありがとうございました！