【備忘録】Pythonでpandasを使ってExcelファイルを読み込んでcsvファイルを出力するまで【Windows PC】
データ加工といえばExcelによる手作業が一般的で、それ以外で言えばETLツールによる加工処理の記述もありますが、やはり自由度が高いのはExcel VBAやR, Pythonのようなスクリプト言語で書く方法です。
今回はデータ分析言語の花形であるPythonを使って、とりあえずさくっと環境構築を終えて、Excelファイルを読み込んで加工してcsvファイルとして出力するまでを書いてみたいと思います。
なおPC環境はWindows10ですので、ご留意くださいませ。
1.Anacondaをダウンロードする
手軽に環境構築できるPythonプラットフォームである『Anaconda』のインストーラーをダウンロードします。中央のタブから「Windows」を選び、特別な事情が無ければ速やかに最新のVer3.7を選び、続いて『Download』ボタンを押すと、ダウンロードが始まります。
https://www.anaconda.com/distribution/
※600MG程度となかなかの重さなので、ダウンロードには少し時間がかかるかもしれません。
2.Anacondaをインストールする
ダウンロードしたファイルを実行します。2019年5月時点でのファイル名は『Anaconda3-2019.03-Windows-x86_64.exe』でした。セットアップ画面では特別な事情が無ければ初期設定のまま「Next」ボタンを押して進んでよいですが、インストール先のディレクトリパスはスペース（空白）がない方が望ましいとのことなので、今回は『C:\Anaconda3』というディレクトリを作って指定してインストールしました。
3.ライブラリをインストールする
Anaconda（Python）でExcelを処理するには専用のライブラリが必要です。Excel処理系のライブラリは、pandas, xlwt, xlrd, openpyxl, XlsxWriterなど多数ありますので、ざっと特徴を並べてみます。
| 特徴 | |
|---|---|
| pandas |
|
| xlrd,xlwt, |
|
| openpyxl |
|
| XlsxWriter |
|
| pyxll |
※詳細は読み込んでいないので、開発者アカウントとユーザーアカウントが区別されずにともに課金されるかどうか、バージョン管理がユーザー間でどうなされるのか、などの実務的な論点は分かりません。 |
※他にも「pyvot」というライブラリもありましたが、長らく更新がないようなので割愛しました。
特徴をざっと見た感じ、計算だけするならpandas一択で、Excelファイルでリッチに出力する場合はopenpyxlやXlsxWriterを使うイメージでしょうか。pyxllは発想が奇抜すぎて興味を持ちましたが、この場では割愛します。
ということで、Pandasを入れてみたいと思います。またそれ以外にデータ分析系の最低限のライブラリとして、numpy, matplotlib, Jupiter Notebookは入れたいところです。
インストールするには、Anacondaの中に入っている『ANACONDA NAVIGATOR』を起動します。
左タブから『Environments』を選ぶと各種ライブラリが表示されるので、中央上のプルダウンにて『All』を選び、該当するライブラリが入っているかどうかを確認します。
・・・pandas, numpy, matplotlib, Jupyter Notebook全てのライブラリがインストール済みでしたので、このステップは不要でした。
4.Jupyterを起動する
インタラクティブかつスマートにPythonコードを実行できるJupyter Notebookを起動したいので、ANACONDA NAVIGATORの左タブから『Home』を選択し、Jupyter Notebookの『Launch』ボタンを押して実行します。
すると、ブラウザ上でこんな画面が立ち上がります。
続いて、画面の右上にある『New』ボタンを押して『Python 3』を選択すると、別タブにてコマンドラインが開きます。ここにスクリプトを書いて『Run』ボタンを押すと実行できるのですが、ここでは何度も同じ処理をする前提でスクリプトファイルを作って呼び出すこと形式にしたいと思います。
5.スクリプトファイルを作る
毎回コマンドラインからスクリプトを入力するのも手間なので、スクリプトファイルを作ります。別のエディタで書いても良いのですが、せっかくJupyter Notebookが入っているのでJupyter Notebookを使います。
さきほど押した『New』ボタンから『Text File』を押すと別タブにてテキストエディタが開きます。そのままだと文字を書いても黒文字ばかりでシンタックスハイライトが効いていないようですが、上タブの『File』から『Save』を押して拡張子を『py』にて保存をするとPythonスクリプトらしい色合いに変わります。
とりあえず、Excelファイルを開いてcsvとして出力する構文を書いてみます。サンプルデータとして、e-statから『年齢(5歳階級)、男女別人口(平成30年12月確定値、令和元年５月概算値）』のExcelファイルをダウンロードしまてみました。
xls形式という微妙なトラップがあったのはさておき、なかなかPythonで扱うには構造が汚い理想的なデータです。これを読み込んで、処理できるデータセットに変換してcsv形式にて出力するスクリプトを書いてみましょう。なお、データ処理するにあたって注意したい点は、
- 左表が万単位、右表が千単位と単位が異なるので、そのまま取り込むと計算ミスを誘発するので補正する。
- 1行目はヘッダー名ではないうえ、4～10行目を総合的に解釈してヘッダー名を生成する必要がある。ヘッダーの性質的に、性別と時期の掛け合わせになっているので、縦持ちに変換する際は2カラムに分割する。
- ところどころに小計行や空白行があるので除外する。
- 一番下にレコードとしては不要な注釈行があるので除外する。
- 1列目の年齢階級は、データとしては邪魔な空白が散見されたり単位があったりなかったり数字が全角だったり半角だったりするため、うまく補正する。
あたりでしょうか。。
とりあえず書いたコードがこちら。横持ちのcsvとして出力した後に、縦持ちに変換して再度出力するスクリプトです。
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 |
import pandas as pd import re #読み込む列番号の指定 useColList = [0,3,4,5,7,8,9,10,11,12] #ヘッダー名の指定 nameList = [ "age", "total_201905", "male_201905", "female_201905", "total_201812", "male_201812", "female_201812", "total-ja_201812", "male-ja_201812", "female-ja_201812" ] #Excelファイルの読み込み fileName = "05k2-1.xls" sheetName = "表" df = pd.read_excel(fileName, sheet_name=sheetName, header=None, names=nameList, skiprows=13, skipfooter=19, index_col=0, usecols=useColList ) #indexの補正 indexList = [] for iRow, row in df.iterrows(): buf = iRow.translate(str.maketrans({chr(0xFF01 + i): chr(0x21 + i) for i in range(94)})) buf = buf.replace(" ","") if '歳' not in buf: buf += "歳" indexList.append(buf) df.index = indexList #数値の桁数の補正 for iCol, column in df.iteritems(): df[iCol] = df[iCol].apply(lambda x : x * (10000 if "2019" in iCol else 1000)) #横持ちでcsv出力 df.to_csv("output_pivotted.csv", encoding="utf-8") #縦持ちのデータに変換 renameColumnMap = {'level_0':'age','level_1':'sex',0:'value'} unpivottedDf = df.stack().reset_index().rename(columns=renameColumnMap) tempList = unpivottedDf['sex'].apply(lambda x:x.split('_')[1]) unpivottedDf['sex'] = unpivottedDf['sex'].apply(lambda x:x.split('_')[0]) unpivottedDf['yyyymm'] = tempList reindexColumnList = ['age','sex','yyyymm','value'] unpivottedDf = unpivottedDf.reindex(columns=reindexColumnList) #縦持ちでcsv出力 unpivottedDf.to_csv("output_unpivotted.csv", header=True, index=False, encoding="utf-8") |
なお、マジックナンバーやハードコーディングな文字列が散見されますが、読み込むファイルもあまり綺麗なデータではないですし、どうせ別の年度のファイルを処理しようとしたら仕様が違って書き直さないといけない類のものと思われるので、とりあえずはこんな感じです。
jupyterに戻って早速実行してみます。『%run -i {fileName}』にてさくっと実行できます。
特に問題なく実行完了。デスクトップに戻ると2つのファイルができていました。UTF-8のcsv形式なので、Excelの『外部データの取り込み』のカンマ区切りにて機能で取り込んでみます。
こちらが横持ち。
そしてこちらが縦持ち。
無事変換できました。
以上、こんな感じで『Pythonでpandasを使ってExcelファイルを読み込んで加工してcsvファイルとして出力するまで』ということで書いてみました。コードの細かい説明は既にあるたくさんの記事に譲るとして、ここでは環境構築やライブラリ比較、データ解釈に寄せての備忘録としました。どこかの誰かの参考になれば、幸いです。
