Pandasで汚いExcel/CSVデータを綺麗にする 〜 頻出の文字列・数値データクレンジング方法
目次
こんにちは。
最近PythonでExcelやCSVデータを集計することが多いです。
今回はその中でも特に苦戦した&頻繁に出会う文字列や数値データの整形方法（いわゆるクレンジング）をまとめようと思います。
Excelは視覚的・直感的にデータを見るのには便利ですが、データが大量になると扱うのはとても大変になります。
そして、いざプログラムで扱おうとすると、そのデータの汚さに苦戦するものです。
ここではどのような汚いデータによく出くわすのか、そしてその解決方法をまとめていきます。
pandasの基本操作
こちらの記事(pandasのデータ処理でよく使う便利構文まとめ)にまとめてあります。
CSVファイルの読み込み
日本語も扱えて一番メジャー(多分)なエンコードがutf-8です。
import pandas as pd
df = pd.read_csv('filename.csv', encoding='utf-8'))
Excelファイルの読み込み Openpyxl
ExcelファイルをPandasで扱う方法も軽くご紹介します。
openpyxlか、pandasのread_excelを使います。私は前者が好み。
インストール
pip install openpyxl
シートの読み込み
import openpyxl as xl
wb = xl.load_workbook("filename.xlsx")
ws = wb.worksheets[0]
セルの読み込み
c = ws.cell(row=1, column=1).value
行・列番号はよしなに。主にfor文で範囲指定してDataFrameに読み込んで使っています。
for文よりも最初にワークブック全体を読み込むので、そこで時間がかかりますね。
【参考】Openpyxl公式ドキュメント
openpyxl - A Python library to read/write Excel 2010 xlsx/xlsm files
何コレ → u\3000
u\3000は全角スペースです。
よく見ますね。
Excelで開いて見ると普通の文字列なのに、pythonで読みこむとよくついてるアレです。
何これ・・・って思いますが、まず使うことはないので予め削除しましょう。
DataFrameであれば以下のコードで除外できます。
df['columns'].str.replace('\u3000','')
文字列のカラムは.str.replace(置換前,置換後)で置換します。''は文字列なしなので削除できます。
全角スペースが続いている\u3000\u3000みたいなのもよくありますが、上のコードで一発で全部消えます。
それにしても、コレ(全角スペース)をたくさんExcelに入力した人の感性を疑いますね...
人が見抜きにくい 半角と全角
一見、同じ文字列のはずなのに何故結合できない...!? 私のpythonはバグってる！
なんてときは大体半角と全角が混ざっていたりします。
よくあるのは全角カッコと半角カッコですね。（）と()の違い。
これは本当に気づかない。単体で並べると気づきますが、文字の後についていると...?
文字) or 文字） (どっちだ??)
どっちでも良いのですが、この場合も上のreplaceメソッドを使ってどちらかに統一しましょう。
プログラムしかわからない 数値と文字列の混在
ExcelだとうまくいくけどPythonだとうまくいかない...
特にデータの重複削除や、検索で出くわす現象です。
それってもしかして「ある数値」を探してませんか...?
そして、それって果たして本当に「数値」でしょうか...????
実はExcel上は同じ数値として扱っていても、Pythonでは同じ数値ではないということがよくあります。
何を言っているのかわからないかもしれませんが、要はこういうことです。
Excelである列(number)にフィルタをかけて見て、100という一種類しかなかったとしても、Pythonでそのカラムに含まれるデータの種類をuniqueを調べると...
df['number'].unique().tolist()
# 結果
[100, 100.0 , '100', '100.0']
こんなに種類が出てきたりします。^^;
これらはプログラム上では同じ100としては扱われないのです。小数点を含むfloat型やシングルクォーテーションで囲まれたstring型が混ざっています。
pandasはobject型といって複数のデータ型を含むデータを保持できます。何かよくわからないけど、とりあえず何かの型だろう、という感じで、Excelを読み込むとこのobject型になることが多いです。
上のnumberというカラムも、object型になっていたようですね。
なので、読み込んだ後は必ず意図したデータ型になっているかを確認してから操作しましょう。
カラムごとのデータ型の確認は以下でできます。
df.dtypes
数値として扱いたいなら数値に、文字列として扱いたいなら文字列に変換してあげましょう。
データ型の変換はastype()メソッドで行います。
df['number'].astype(int)
あれ、なんかエラーがでる...
'100.0'が直接intに変換できないからですね。そんなときは一回floatを経由しましょう。
df['number'].astype(float).astype(int)
無事に整数型の100になりました。
何かうまく行かないときに超便利なメソッド
さて、上でも使いましたが、何かうまくいかないときはまずカラムにどんなデータが入っているかを確認しましょう。
df.['columns'].unique().tolist()
.unique()だけで良いのですが、なんか見にくいのでリスト型に変換します。それが.tolist()です。
リストで吐き出されると大量にあってもある程度見やすくなります。便利。
まとめ
以上、特に頻繁に出くわすExcelの汚いデータと、それに対応する文字列・数字列の整形方法をまとめてみました。
まずはカラムに含まれる文字におかしいものがないか、.unique().tolist()でチェックしましょう。
文字の置換は.str.replace(置換前、置換後)で。
カラムの型は.dtypesで確認して、型変換は.astype()で扱いたい型に予め統一しましょう。
今後もExcelデータの扱いに苦戦したら記事にまとめていこうと思います。
参考
私がPythonの学習に使用した本はこちらです。Pythonの基礎から体系的に学習したい方におすすめです。
pandasの章のあとには機械学習や可視化の方法もまとめてあり、データサイエンスのスキルが幅広く習得できるようになっています。
オライリーの本は学術書っぽい硬派な感じで、個人的にはとても好きです。
