【要注意】PandasでExcelワークシート内の日付データを読み込む時の落とし穴

先日とある書籍でデータ前処理技術について学んでいた時に奇妙な現象に遭遇したので検証した内容を記事にまとめておこうと思います。

実際に遭遇した現象

権利の関係上書籍に付いていたファイルをそのまま掲載出来ない為、別途説明用に作成したファイルを使って説明したいと思います。以下のファイルは2020年4月の日付を様々な表記方法で表記した一覧のExcelファイル(xlsxファイル)です。

2020年4月1日〜2020年4月30日までの日付が表記方法に従った書式で記載されています。データ分析の現場では綺麗な状態のデータが入手出来ることは稀で、表記方法がバラバラで有ることはしばしば起こり得ます¹。このデータをPandasで読み込ませてみます。PandasはPythonベースのライブラリとしてデータ分析の世界ではしばしば用いられます。Pandasにはread_excel関数という専用の関数が有る為、簡単にExcelファイルを読み込ませることが出来ます。読み込ませて表示させると下図の様になりました。

/区切り、-区切り、年月日表示のデータはタイムスタンプ形式で読み込まれました。残りの数値化、文字列化されているデータについてタイムスタンプに変換してみます。数値化、文字列化されているデータについては実際はシリアル値という数値となっています。Pandasでは読み込んだ時点で文字列になってしまっているので、数値に変換してその個数を算出してみます。

合計12と出力されました。上の図における数値化、文字列化したデータ数と一致します。それではこのデータを日付に変換してみましょう。下図の様にシリアル値を日付に変換します。シリアル値を日付データにする場合、シリアル値は通常コンピュータの日付の開始日(1970/01/01)²から何日経った日付かという数値である為、以下の様に変換します。

無事変換が終わったとホッとしたのも束の間。。。奇妙な結果になっています。最初のデータは2020-04-03の筈が、変換結果では2090-04-05となっています。70年と2日ズレてしまっています。他のデータも想定していた日付＋70年2日の結果になっています。最初は何故だろうと首を傾げました。調べてみた所、以下の2つの事が分かりました。以下の2点をPandasが吸収出来ず2日ズレる結果を生んでしまっているとのことです。

1. Excelではシリアル値の基準日が1900/01/00を基準³にしている。(つまり1900/01/01がシリアル値1となる)
2. Excelにはバグが有り、閏年では無い1900年が閏年(つまり2月29日が存在する)として扱われている。

• 参考: https://teratail.com/questions/186070

本記事では折角なのでこれらのことを1つずつ検証したいと思います。

検証1: シリアル値の基準値について

まず、Pandasのシリアル値の0点がUNIX時間の0点(1970/1/1)であることを確認します。結果0.0と出力されました。因みにExcelのシステム時刻の基準となっているとされる1900/1/1は-2208988800.0と出力されました。

続いてExcelで1970/1/1と1990/1/1のシリアル値を算出してみます。

1900/1/1が1となり、1970/1/1(UNIX時間)が25569となっています。つまり19世紀最後の年がExcelで言うところのシステム時刻の開始日ということになります。よって先述した2つの事実のうち1つ目は正しいということが分かりました。

検証2: Excelでは1900年が閏年扱いとなっている

そもそも閏年は4年に1回やって来る年だから4で割り切れる(1900も4で割り切れます)年だと思われがちですが、厳密に言うとそれは間違っています。本来の閏年の条件は400で割り切れる年または、100では割り切れずかつ4で割り切れる年を閏年とする⁴です。

閏年判定プログラム

def judge_leap_year(y):
    if y % 400 == 0 | (y % 100 != 0 & y % 4 == 0):
        print('{} is leap year'.format(y))
    else:
        print('{} is not leap year'.format(y))

条件から自明な様な気もしますが、念の為にプログラムで判定させてみると1900年は閏年ではないことが分かります。

検証方法: シリアル値から検証

Excelの関数には日付を入力すると閏年か判定してくれるという様な便利な物は有りません。。。そこでシリアル値を求めることによって検証したいと思います。まずPandasで1900/2/29のシリアル値を算出してみます。案の上エラー出力となりました。day is out of range for monthと有る様に想定されている範囲内に存在しない日付を与えてしまっている旨が表示されています。

続けてExcelでシリアル値を算出してみます。

何と!?60と出力されました。よって上述の通りExcelでは1900年を閏年として扱っていることが証明出来ました。暦上は存在しない日付を存在するものとして扱っていることが分かります。

2020/5/13追記:(補足) 1900/2/28と1900/3/1のシリアル値

記事を読んだ方から1900/2/28と1900/3/1のシリアル値のどちらかと重複⁵しているのではと思ったけど、検証したら違ってたというお話を頂きましたので筆者の方で検証した内容を追記しておきます。因みに先述の1899/12/31のシリアル値が算出出来ないという検証結果も合わせて掲載しています。

まとめを兼ねて今後の対策

データ分析の現場ではExcelファイル（xlsxファイル)を扱う場合が有ります。その時に日付データの表示がおかしいと思ったら以下のことを先ず思い出します。

1. Excelでは1900/1/0をシステム時刻の開始としており1900/1/1をシリアル値1としている。
2. Excelでは1900年を閏年として扱っている。これは明らかなExcelのバグ⁶です。

そして実際に日付を処理する時はシリアル値を数値変換した値から2を引きます。上述の変換処理コードは以下の様になります。

pd.to_timedelta(data.loc[flag_is_serial, '日付'].astype('float') - 2, unit='D') + pd.to_datetime('1900/1/1')

実行結果を下図に示します。正しい日付に変換されました。

今後Excelのバグが解消されることを願うばかりです。合わせてシステム時刻もUNIX時刻に合わせて貰えると大変嬉しいと思うのは筆者だけでは無いと思います。

2020/4/13追記: Excelの1900年閏年扱い問題について

記事を読んだ友人が追加情報を送ってくれました。Microsoftの公式情報です。

• 年がうるう年かどうかを判断する方法

このバグは昔のコンピュータの一部のアプリケーションでは閏年を4で割り切れる年(つまり4年に1回)としていたことを引き摺っていることが原因の様です。なかなか根深い問題です。

1. 今回用意した例は説明用のデータとして用意したため、現実にここまで表記が乱れた酷いデータが存在するかと問われると答えに窮してしまいます。 ↩

2. UNIX時間 Wikipedia, 日付⇒UNIX時間変換 ↩

3. 1899/12/31を基準(シリアル値0)にしているのではと思うかもしれませんが、Excel上で1899/12/31のシリアル値を算出することは出来ません。矢張り1900/1/0をシリアル値0としている様です。 ↩

4. 閏年 Wikipedia ↩

5. そもそも暦に存在しない日付なのでどちらかとシリアル値が等しいのではないかという意味 ↩

6. Excel97より前のバージョンでは。。。と後述のMS公式情報に有ることからこれはバグと言うよりExcelの長きに渡る歴史の力によって仕様となってしまっていると言った方が良いかもしれないですね。 ↩