Python

Excel ファイルをPythonで読み込んで整理されたデータフレームにする

公的機関から出ているパブリックデータは、何故か(当然?)Excelファイル(xls, xmlsx)ということが多いです。 そのため、政府や行政が公開しているパブリックデータを使って分析する際には、 Excelファイルのデータを使うということがそこそこあるかと思います。 この記事では、そのようなExcelブックとしてまとめられているデータについて、 それをPythonで読み込んで、余計な行や列を取り除いたり成形したりして、Pandasのデータフレームとして扱いやすい感じにする方法についてまとめたいと思います。 以下のPandasのドキュメントを参考にしています。

pandasを使ったExcel ファイルの参照方法

Excel形式のファイル(xls, xlsx)をPythonで読み込むだけなら、ライブラリとしてpandasを呼び出して ExcelFile メソッドを用いればできます。 以下の様に読み込みたいExcel形式のファイル名を引数として指定すれば、ExcelfileオブジェクトとしてPythonから参照することができるようになります。

この参照したExcelfileオブジェクトに対して、読み込みたいシート名を指定してデータフレームとして読み込んだり、それぞれのシート名を取得したりできます。 ここでは、日本政府観光局(JNTO)のHPで公開されている以下のExcelファイルを例に取って、 Excelブック内の各シートをデータフレームとして読み込み、そのデータフレームの行と列を整理して分析しやすい形に整理する方法を見ていきます。 「年/月別 訪日外客数(重点20市場)」(ファイル名:stat2015_02-04-02.xls) このデータを選んだのは、一つのExcelブック内にシートがいくつもあり、また行や列ごとにデータの種類も違っていて、分析をするという観点ではそのままでは扱いづらく、必要な処理を色々見ていくのに適当かなと思ったという程度の理由です。 以下では「stat2015_02-04-02.xls」(ファイルの場所はカレントディレクトリを想定しています)をpandasのExcelFileメソッドで参照し、 そのブック内にあるシートの名称をsheet_namesメソッドでリストとして取得しています。

Excelシートをデータフレームとして読み込む

参照したExcelfileオブジェクトから、それぞれのシートをデータフレームとして取得するには parse メソッドを使います。 読み込むシートの指定は、そのシートの名前を引数に入力することで行います。 試しに「stat2015_02-04-02.xls」の一つ目のシートの中身をデータフレームとして読み込んでみます。 これは先程シート名を格納したリスト input_sheet_name の0番目の要素を入力にすることでできます。

その中身を確認してみると以下の様になります。 見ての通りただ読み込むだけでは、余分な行や列に大量のNaNが入っており、各行や列の内容も(ある程度規則性はありますが)バラバラです。 このデータフレームから、NaNとなっている要素や伸び率(Change(%))、各月の割合(Share(%))の行を取り除き、 月ごとの訪日者数のデータだけに整理していきます。

 年 / 月別 訪日韓国人数Unnamed: 1Unnamed: 2Unnamed: 3Unnamed: 4Unnamed: 5Unnamed: 6Unnamed: 7Unnamed: 8Unnamed: 9Unnamed: 10Unnamed: 11Unnamed: 12Unnamed: 13Unnamed: 14Unnamed: 15Unnamed: 16
0Annual Visitor Arrivals from South Korea by MonthNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
1NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN
2NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN(単位:人 /Unit: Persons)NaNNaN
3NaN1 月2 月3 月4 月5 月6 月1~6月計7 月8 月9 月10 月11 月12 月7~12月計年計
4YearNaNJan.Feb.Mar.Apr.May.Jun.Jan.-Jun.Jul.Aug.Sep.Oct.Nov.Dec.Jul.-Dec.Total
5NaN19968939273878742507857981678739414717189864610733973200866937837278394522644994362
6平成8年Change (%)14.989919.02184.6423113.22326.444421.202816.252624.839926.20554.5176810.20251.127771.0297111.708313.8189
7NaNShare (%)8.989887.429697.46717.902458.214117.4360247.43939.9205310.79487.36158.718457.881647.8838552.5607100
8NaN199799749761577901482028802607742049462898602112517762139367978795561375159431010571
9平成9年Change (%)11.5863.084826.416164.38921-1.736094.70514.85672-0.04460394.823974.116128.058320.539734-28.3912-1.282131.63009
 年 / 月別 訪日韓国人数Unnamed: 1Unnamed: 2Unnamed: 3Unnamed: 4Unnamed: 5Unnamed: 6Unnamed: 7Unnamed: 8Unnamed: 9Unnamed: 10Unnamed: 11Unnamed: 12Unnamed: 13Unnamed: 14Unnamed: 15Unnamed: 16
62NaN2015358093321576268156304619315389251504181933734379939097130164537084235984541565621827584002095
63平成27年Change (%)40.144538.908539.607957.021761.520121.155442.587337.113255.500238.566948.588250.544553.433547.546845.2501
64NaNShare (%)8.947648.035196.700397.611497.88066.2843145.45968.590489.769167.537189.26628.9914210.38654.5404100
65NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaN出典:日本政府観光局(JNTO)NaNNaNNaNNaNNaN
66NaNNaNNaNNaNNaNNaNNaNNaNNaNNaNNaNSource: Japan National Tourism OrganizationNaNNaNNaNNaNNaN

parseメソッドで読み込む際にオプションの引数を指定することで、読み込みたくない行や列をある程度取り除くことができます。 何も引数を指定せずに読み込んだデータフレームを見てやると、 始めの0~2行目はExcelシート上部の空白セルを読み込んでいるだけで(値がほぼ全てNaNになっている) データとしては必要がないということがわかります。 また3, 4行目は月(1 月, 2 月, … 及び Jan., Feb., …)の情報が入っており、 この2つの行もデータとしては必要ないため取り除いてしまいます。 先頭の何行まで読み込むのをスキップするかについては、skiprows を引数で指定すれば可能です。 今の場合 skiprows = 5 と指定して始めの5行の読み込みをスキップすればよいです。 また最後の2行の要素もほぼ全ての値がNaNであり、読み込む必要がないので skip_footer=2 を指定して読み込みをスキップします。

  • skiprows:先頭の何行を読み込まないか指定する引数
  • skip_footer:最後の何行を読み込まないか指定する引数

次に列ですが、今は各月ごとの訪日人数が欲しいので、 平成○年の列や1月~6月計の列、7月~12月計の列、年計の列も取り除いてしまいます。 読み込む列を指定するには parse_cols 引数を用います。

  • parse_cols:読み込む列を指定する引数

整数を指定するとその行数まで読み込み(parse_cols = 4 なら0列目から4列目まで読み込む)、 リストやタプルを指定するとその列番号の列だけを読み込みます(parse_cols = [1,3] なら1列目と3列目だけ読み込む)。 また parse_cols = “A:B, D:F” の様にExcelファイルの列名を指定して、その範囲の列を読み込むこともできます (この場合だとA, B, D, E, F列を読み込む)。 今の場合 parse_cols = “B:H,J:O” と指定すればよいことになります。 列名については names 引数で指定できます。

  • names:列名の指定

0列目は年、1列目から12列目は各月の訪日者数なので、 ここではひとまず列名として0~12までの数字を指定します。 これには range を用いて names = range(0,13) とすればよいです。 以上の引数を指定してデータフレームを読み込むには以下のようにします。

これらの引数を指定して読み込んだデータフレームを表示すると、 かなり不要な行と列が整理できたことがわかります。

 0123456789101112
0199689392.00000073878.00000074250.00000078579.00000081678.00000073941.00000098646.000000107339.00000073200.00000086693.00000078372.00000078394.000000
1Change (%)14.98990219.0217654.64231413.22296226.44436221.20283324.83991026.2054534.51767710.2024991.1277711.029706
2Share (%)8.9898857.4296897.4671007.9024548.2141117.4360249.92053210.7947617.3615048.7184557.8816377.883849
3199799749.00000076157.00000079014.00000082028.00000080260.00000077420.00000098602.000000112517.00000076213.00000093679.00000078795.00000056137.000000
4Change (%)11.5860483.0848166.4161624.389213-1.7360864.705103-0.0446044.8239694.1161208.0583210.539734-28.391203
5Share (%)9.8705587.5360377.8187488.1169957.9420457.6610159.75705811.1340027.5415789.2699087.7970775.554978
6199852159.00000048880.00000056435.00000057860.00000056500.00000054050.00000066296.00000072800.00000057894.00000067886.00000064625.00000069060.000000
7Change (%)-47.709751-35.816800-28.575949-29.463110-29.603788-30.185998-32.764041-35.298666-24.036582-27.533385-17.98337523.020468
8Share (%)7.1998566.7472347.7901017.9868047.7990747.4608849.15128110.0490727.9914979.3707608.9206229.532815
9199977529.00000066794.00000071322.00000074208.00000069592.00000072039.00000091863.000000101024.00000075041.00000084502.00000078272.00000080488.000000

pandasのメソッドによる整形

parse の引数だけで取り除けない行については、一度データフレームに読み込んでからpandasのデータフレームに関するメソッドを利用して整形します。 今は訪日者数の行だけが欲しいので、0列目が「Change (%)」や「Share (%)」の行は取り除いてしまいます。 これにはデータフレームに以下のような条件指定をして、1行目が「Change (%)」及び「Share (%)」の行を除けば、 結果として人数の行だけを取り出せます。

「Change (%)」及び「Share (%)」の行の要素が少数を含む数だったため、 同じ列に格納されていた人数の行の要素まで浮動小数点型になっています。 この浮動小数点型を整数型に直しておきます。 データフレームの複数の列を型変換するには、astype メソッドを利用します。 引数には、変換したい列名と型の対応が入ったディクショナリを指定します。

不要な行を取り除くことで行ラベルが行数と合わなくなってしまったため、 reset_index メソッドを使い行ラベルと行数を一致させておきます。

また、0列目が年の列になっているので、その列名を0からyearに変更しておきます。 列名の変更には rename メソッドを用います。 引数は astype と同じ様に、変更したい列名と変更後の列名が入ったディクショナリを指定してやります。

これまでの処理を全て行ったデータフレームを表示すると以下のようになります。 1列目に年、それ以外の列に各月の訪日者数が入った、 きれいなデータフレームにすることができました。

 year123456789101112
019968939273878742507857981678739419864610733973200866937837278394
119979974976157790148202880260774209860211251776213936797879556137
21998521594888056435578605650054050662967280057894678866462569060
319997752966794713227420869592720399186310102475041845027827280488
4200093768780408054984484814098041010650711539578968887148444391703
52001101442823828794789065875598539711975112270483032877729135395567
62002117710995069609310205398099892911292271467488970510635010062196432
720031472381129881088718747792072109379161273184536108468127367112975106689
82004164785142718112516120427115659116269160770170182113083122877128369120817
92005174775148946130963122084114151133177170420193279130269146650140442142015
102006192590174239149071162657161080153706197622220332156451187601177298184678
112007240350202365188721190558211355190330254234271377201286222737218488208893
122008271583234876187474203812229043195661237947248154159523188804117518108002
132009129756106929108350113313117897104237170240190987105470131195130371178027
142010232053197784169295189582201484179088236092246882193975193829197244202508
152011268368231640891216379084014103817140053147030122436132259134009141536
162012173000169025150291152323157141152131189701201764145742168150183557199950
172013234456234390206946204229228670211465243992215498164499158273170901182846
182014255517231502192078193998195263207588250741251428217689249577239029270903
192015358093321576268156304619315389251504343799390971301645370842359845415656

横持ちデータの縦持ちへの変換

不要な行や列を取り除けたのでこれで終わりでもよいのですが、月の情報については縦持ちのデータの方が扱いやすいことも多いです。 横持ちのデータフレームを縦持ちにする簡潔な方法はないのかな?と思って少し調べてみましたが、 結局あまりスマートな方法を見つけられなかったので、ここではfor文のループを使った普通?な方法を用います。 以下の処理で、月について横持ちになっているデータフレームを一つの月の列を持つ縦持ちのデータフレームに変換できます。

シート名で指定されている国名の情報も、新しく「country」の列を追加して付与してやります。 国名の情報はシート名から抜き出せますが「’韓国(総数)’」の様に余分な「(総数)」の部分が付いているのでこれは取り除きます。 以下の様に正規表現を使えば、文字列の余分な部分を取り除けます。

以上の処理をして縦持ちに変換したデータフレームが以下になります。

 yearmonthcountryvisitor
019961韓国89392
119971韓国99749
219981韓国52159
319991韓国77529
420001韓国93768
520011韓国101442
620021韓国117710
720031韓国147238
820041韓国164785
920051韓国174775
1020061韓国192590
1120071韓国240350
1220081韓国271583
1320091韓国129756
1420101韓国232053
1520111韓国268368
1620121韓国173000
1720131韓国234456
1820141韓国255517
1920151韓国358093
2019962韓国73878
2119972韓国76157
2219982韓国48880
2319992韓国66794
2420002韓国78040
2520012韓国82382
2620022韓国99506
2720032韓国112988
2820042韓国142718
2920052韓国148946
210200611韓国177298
211200711韓国218488
212200811韓国117518
213200911韓国130371
214201011韓国197244
215201111韓国134009
216201211韓国183557
217201311韓国170901
218201411韓国239029
219201511韓国359845
220199612韓国78394
221199712韓国56137
222199812韓国69060
223199912韓国80488
224200012韓国91703
225200112韓国95567
226200212韓国96432
227200312韓国106689
228200412韓国120817
229200512韓国142015
230200612韓国184678
231200712韓国208893
232200812韓国108002
233200912韓国178027
234201012韓国202508
235201112韓国141536
236201212韓国199950
237201312韓国182846
238201412韓国270903
239201512韓国415656

これらの処理を全てのシートに対して行い一つのデータフレームに結合する

ここまで見てきた一つ目のシートへの処理を全てのシートに対して行い、さらに各シートのデータフレームを一つのデータフレームに結合します。 全てのシートに対してforでループを回せばよいので、以下のようにすればできます。

処理して全てのシートについて結合したデータフレームを表示します。 以下の様に一つのデータフレームでExcelのシートのデータを格納することができました。

 yearmonthcountryvisitor
019961韓国89392
119971韓国99749
219981韓国52159
319991韓国77529
420001韓国93768
520011韓国101442
620021韓国117710
720031韓国147238
820041韓国164785
920051韓国174775
1020061韓国192590
1120071韓国240350
1220081韓国271583
1320091韓国129756
1420101韓国232053
1520111韓国268368
1620121韓国173000
1720131韓国234456
1820141韓国255517
1920151韓国358093
2019962韓国73878
2119972韓国76157
2219982韓国48880
2319992韓国66794
2420002韓国78040
2520012韓国82382
2620022韓国99506
2720032韓国112988
2820042韓国142718
2920052韓国148946
4770200611スペイン2093
4771200711スペイン2710
4772200811スペイン3236
4773200911スペイン2827
4774201011スペイン2909
4775201111スペイン1866
4776201211スペイン2842
4777201311スペイン3295
4778201411スペイン4162
4779201511スペイン5014
4780199612スペイン560
4781199712スペイン688
4782199812スペイン661
4783199912スペイン708
4784200012スペイン684
4785200112スペイン753
4786200212スペイン1102
4787200312スペイン938
4788200412スペイン1203
4789200512スペイン1270
4790200612スペイン1766
4791200712スペイン1915
4792200812スペイン2398
4793200912スペイン2964
4794201012スペイン2199
4795201112スペイン2435
4796201212スペイン2315
4797201312スペイン2876
4798201412スペイン3337
4799201512スペイン4871

フィリピン?

以上のコードでは「Change (%)」と「Share (%)」の行を取り除く際に「(%)」の括弧の部分を半角にしてさらにスペースも取り除いた 「Change(%)」と「Share(%)」についても取り除く様にしていしています。 これは実際にコードを動かしてみて、出力したファイルを目で見てから気づいたことなのですが、 実はここでインプットに使ったExcelファイルを見てみると、フィリピンのシートの2015年と2014年だけ「(%)」の括弧の部分が全角でなく 全て半角の「(%)」となっています(加えてChangeとShareとの間にあるスペースもない)。 そのため、「Change (%)」と「Share (%)」を除くという条件だとここだけ取り除けません。 このファイルが作られる過程で手作業が入った(もしくは基本手作業なのか…)、という悲しい事実が分かります。 こういうことはおそらくよくあるのでしょうね。 これも実際に出力されたデータを自分で確認して初めてわかったことなわけで、 結局最後に大事なのは、ちゃんと自分の目でデータを確認するってことなのでしょう。

csvファイルとして出力

最後にcsvファイルとして出力してみます。to_csvメソッドで出力できます。

他に使えそうなメソッドとか

今回は使いませんでしたが、他にデータフレームの整理で使えそうなメソッドを上げておきます。

このページをシェアする: