pandas DataFrameで整形後Redshiftにロードする時はInt64を使おう

pandas DataFrameでnullを含む整数列を読み込むとfloatになってしまい、小数で出力されたファイルがRedshiftでのロードに失敗する事態の回避方法です。

#Python

#pandas

#Amazon Redshift

#AWS

平野重利

2020.04.20

この記事は公開されてから1年以上経過しています。情報が古い可能性がありますので、ご注意ください。

こんにちは、平野です。

久しぶりにブログ書く時間が確保できました。在宅で仕事しているとちょこちょこ家事が発生したりで、なかなかブログまで手が回らない...。と言っていても仕方ないので、定期的なアウトプットは再開して行きます！

閑話休題。 Redshiftへのデータロードする際にSTL_LOAD_ERRORが発生して何か失敗してるなぁ、と思ったら、 pandasの意外と面倒な仕様に当たっていて、解決までに結構苦労したのでブログにしておきます。

今回、既存システムの移行ということでpandasでのデータ整形に初めて触ったのですが、比較的あるあるなハマり方なのかな、ということで参考にして頂ければと思います。

状況

今回想定しているケースは以下のような状況です。

S3上のファイルをread_csvでpandas DataFrameに取り込む
DataFrameで値の整形などを行う（この記事ではこの内容には触れません）
整形後のDataFrameをto_csvでS3へ出力する
S3をRedshiftへロードする

不具合

STL_LOAD_ERRORが起きました。

select * from stl_load_errors

を実行してみると、int8のデータ型のカラムに12.0という数値を入れようとしたたエラーになっているようでした。

はじめは、数値の中に小数を含むものがあって、それでfloat型になっているのだろうと思いましたが、調べて見た所、そのような数値はありませんでした。

原因: DataFrameの整数型はnullを持てない

原因についてですが、「DataFrameの整数型はnull¹を持てない」ということが端的な理由でした。

当該のカラムの数値を確認してみると、小数はないものの、値が存在しない行が見つかりました。このようなファイルをread_csvでDataFrame作成をした場合の流れとしては以下のようになっているようです。

カラムのデータ型を推定する
出現する値は、整数 or nullである
nullを含む場合整数型にはできないので、floatにする

ということで、このカラムはfloatとして判断されてしまっていたようです。ここでfloat型として解釈されてしまうため、S3への出力ファイルにも12.0のような形式になってしまうようです。

対処法

float型の出力フォーマットで小数点以下を出さない

小数点以下の.0が含まれていることがロード失敗の原因ですので、小数点以下を表示させないようにしてto_csvすることで回避できます。

df.to_csv('output.csv', float_format='%.0f')

これで解決と思ったのですが、これだと、他のfloat型のカラムにも影響が出てしまいます。なので、この方法は他にfloatのカラムがないことが明確にわかっている時限定になってしまいます。

どちらにしても、次の方法で汎用的に対処できるのでこちらの方法は非推奨です

nullを許容する整数型`Int64`を使う

そもそもの原因は、整数型がnullを許容しないことだったので、 nullを許容する整数型があれば、それを使うのがベストです！で、調べて見た所、見つかりました！²

Nullable integer data type

こちらはpandasのバージョン0.24.0以降であれば使えるようです。

使い方としては、Int64（先頭が大文字）というエイリアスがあるので、

df['column_name'] = df['column_name'].astype('Int64')

のように、read_csvで作成したDataFramedfに対してカラムを指定してキャスト可能です。後続のデータベースで整数型に入れるカラムについてはこの処理を入れておくと安心できそうです。

確認プログラム

簡単なプログラムでpandasの部分だけ確認してみます。

import pandas as pd
import io

content = '''
a,b,c,d,e
1,2,3,4,5
11,12,,14,15
'''
string_io = io.StringIO(content)

# read_csvでそのまま読み込み
df = pd.read_csv(string_io)
print(df)
'''
    a   b    c   d   e
0   1   2  3.0   4   5
1  11  12  NaN  14  15
'''

# 'c'カラムをInt64にキャスト
df['c'] = df['c'].astype('Int64')
print(df)
'''
    a   b    c   d   e
0   1   2    3   4   5
1  11  12  NaN  14  15
'''

output = io.StringIO()
df.to_csv(output)
print(output.getvalue())
'''
,a,b,c,d,e
0,1,2,3,4,5
1,11,12,,14,15
'''

Int64にキャストすることで、to_csvしたファイルにも 3.0ではなく、3が出力されていることが確認できました。