楽屋

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: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10 Unnamed: 11 Unnamed: 12 Unnamed: 13 Unnamed: 14 Unnamed: 15 Unnamed: 16
0 Annual Visitor Arrivals from South Korea by Month NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
1 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
2 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN (単位:人 /Unit: Persons) NaN NaN
3 NaN 1 月 2 月 3 月 4 月 5 月 6 月 1~6月計 7 月 8 月 9 月 10 月 11 月 12 月 7~12月計 年計
4 Year NaN Jan. Feb. Mar. Apr. May. Jun. Jan.-Jun. Jul. Aug. Sep. Oct. Nov. Dec. Jul.-Dec. Total
5 NaN 1996 89392 73878 74250 78579 81678 73941 471718 98646 107339 73200 86693 78372 78394 522644 994362
6 平成8年 Change (%) 14.9899 19.0218 4.64231 13.223 26.4444 21.2028 16.2526 24.8399 26.2055 4.51768 10.2025 1.12777 1.02971 11.7083 13.8189
7 NaN Share (%) 8.98988 7.42969 7.4671 7.90245 8.21411 7.43602 47.4393 9.92053 10.7948 7.3615 8.71845 7.88164 7.88385 52.5607 100
8 NaN 1997 99749 76157 79014 82028 80260 77420 494628 98602 112517 76213 93679 78795 56137 515943 1010571
9 平成9年 Change (%) 11.586 3.08482 6.41616 4.38921 -1.73609 4.7051 4.85672 -0.0446039 4.82397 4.11612 8.05832 0.539734 -28.3912 -1.28213 1.63009
年 / 月別 訪日韓国人数 Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 10 Unnamed: 11 Unnamed: 12 Unnamed: 13 Unnamed: 14 Unnamed: 15 Unnamed: 16
62 NaN 2015 358093 321576 268156 304619 315389 251504 1819337 343799 390971 301645 370842 359845 415656 2182758 4002095
63 平成27年 Change (%) 40.1445 38.9085 39.6079 57.0217 61.5201 21.1554 42.5873 37.1132 55.5002 38.5669 48.5882 50.5445 53.4335 47.5468 45.2501
64 NaN Share (%) 8.94764 8.03519 6.70039 7.61149 7.8806 6.28431 45.4596 8.59048 9.76916 7.53718 9.2662 8.99142 10.386 54.5404 100
65 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN 出典:日本政府観光局(JNTO) NaN NaN NaN NaN NaN
66 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN Source: Japan National Tourism Organization NaN NaN NaN NaN NaN
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) とすればよいです。 以上の引数を指定してデータフレームを読み込むには以下のようにします。 これらの引数を指定して読み込んだデータフレームを表示すると、 かなり不要な行と列が整理できたことがわかります。
0 1 2 3 4 5 6 7 8 9 10 11 12
0 1996 89392.000000 73878.000000 74250.000000 78579.000000 81678.000000 73941.000000 98646.000000 107339.000000 73200.000000 86693.000000 78372.000000 78394.000000
1 Change (%) 14.989902 19.021765 4.642314 13.222962 26.444362 21.202833 24.839910 26.205453 4.517677 10.202499 1.127771 1.029706
2 Share (%) 8.989885 7.429689 7.467100 7.902454 8.214111 7.436024 9.920532 10.794761 7.361504 8.718455 7.881637 7.883849
3 1997 99749.000000 76157.000000 79014.000000 82028.000000 80260.000000 77420.000000 98602.000000 112517.000000 76213.000000 93679.000000 78795.000000 56137.000000
4 Change (%) 11.586048 3.084816 6.416162 4.389213 -1.736086 4.705103 -0.044604 4.823969 4.116120 8.058321 0.539734 -28.391203
5 Share (%) 9.870558 7.536037 7.818748 8.116995 7.942045 7.661015 9.757058 11.134002 7.541578 9.269908 7.797077 5.554978
6 1998 52159.000000 48880.000000 56435.000000 57860.000000 56500.000000 54050.000000 66296.000000 72800.000000 57894.000000 67886.000000 64625.000000 69060.000000
7 Change (%) -47.709751 -35.816800 -28.575949 -29.463110 -29.603788 -30.185998 -32.764041 -35.298666 -24.036582 -27.533385 -17.983375 23.020468
8 Share (%) 7.199856 6.747234 7.790101 7.986804 7.799074 7.460884 9.151281 10.049072 7.991497 9.370760 8.920622 9.532815
9 1999 77529.000000 66794.000000 71322.000000 74208.000000 69592.000000 72039.000000 91863.000000 101024.000000 75041.000000 84502.000000 78272.000000 80488.000000

pandasのメソッドによる整形

parse の引数だけで取り除けない行については、一度データフレームに読み込んでからpandasのデータフレームに関するメソッドを利用して整形します。 今は訪日者数の行だけが欲しいので、0列目が「Change (%)」や「Share (%)」の行は取り除いてしまいます。 これにはデータフレームに以下のような条件指定をして、1行目が「Change (%)」及び「Share (%)」の行を除けば、 結果として人数の行だけを取り出せます。 「Change (%)」及び「Share (%)」の行の要素が少数を含む数だったため、 同じ列に格納されていた人数の行の要素まで浮動小数点型になっています。 この浮動小数点型を整数型に直しておきます。 データフレームの複数の列を型変換するには、astype メソッドを利用します。 引数には、変換したい列名と型の対応が入ったディクショナリを指定します。 不要な行を取り除くことで行ラベルが行数と合わなくなってしまったため、 reset_index メソッドを使い行ラベルと行数を一致させておきます。 また、0列目が年の列になっているので、その列名を0からyearに変更しておきます。 列名の変更には rename メソッドを用います。 引数は astype と同じ様に、変更したい列名と変更後の列名が入ったディクショナリを指定してやります。 これまでの処理を全て行ったデータフレームを表示すると以下のようになります。 1列目に年、それ以外の列に各月の訪日者数が入った、 きれいなデータフレームにすることができました。
year 1 2 3 4 5 6 7 8 9 10 11 12
0 1996 89392 73878 74250 78579 81678 73941 98646 107339 73200 86693 78372 78394
1 1997 99749 76157 79014 82028 80260 77420 98602 112517 76213 93679 78795 56137
2 1998 52159 48880 56435 57860 56500 54050 66296 72800 57894 67886 64625 69060
3 1999 77529 66794 71322 74208 69592 72039 91863 101024 75041 84502 78272 80488
4 2000 93768 78040 80549 84484 81409 80410 106507 115395 78968 88714 84443 91703
5 2001 101442 82382 87947 89065 87559 85397 119751 122704 83032 87772 91353 95567
6 2002 117710 99506 96093 102053 98099 89291 129227 146748 89705 106350 100621 96432
7 2003 147238 112988 108871 87477 92072 109379 161273 184536 108468 127367 112975 106689
8 2004 164785 142718 112516 120427 115659 116269 160770 170182 113083 122877 128369 120817
9 2005 174775 148946 130963 122084 114151 133177 170420 193279 130269 146650 140442 142015
10 2006 192590 174239 149071 162657 161080 153706 197622 220332 156451 187601 177298 184678
11 2007 240350 202365 188721 190558 211355 190330 254234 271377 201286 222737 218488 208893
12 2008 271583 234876 187474 203812 229043 195661 237947 248154 159523 188804 117518 108002
13 2009 129756 106929 108350 113313 117897 104237 170240 190987 105470 131195 130371 178027
14 2010 232053 197784 169295 189582 201484 179088 236092 246882 193975 193829 197244 202508
15 2011 268368 231640 89121 63790 84014 103817 140053 147030 122436 132259 134009 141536
16 2012 173000 169025 150291 152323 157141 152131 189701 201764 145742 168150 183557 199950
17 2013 234456 234390 206946 204229 228670 211465 243992 215498 164499 158273 170901 182846
18 2014 255517 231502 192078 193998 195263 207588 250741 251428 217689 249577 239029 270903
19 2015 358093 321576 268156 304619 315389 251504 343799 390971 301645 370842 359845 415656

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

不要な行や列を取り除けたのでこれで終わりでもよいのですが、月の情報については縦持ちのデータの方が扱いやすいことも多いです。 横持ちのデータフレームを縦持ちにする簡潔な方法はないのかな?と思って少し調べてみましたが、 結局あまりスマートな方法を見つけられなかったので、ここではfor文のループを使った普通?な方法を用います。 以下の処理で、月について横持ちになっているデータフレームを一つの月の列を持つ縦持ちのデータフレームに変換できます。 シート名で指定されている国名の情報も、新しく「country」の列を追加して付与してやります。 国名の情報はシート名から抜き出せますが「’韓国(総数)’」の様に余分な「(総数)」の部分が付いているのでこれは取り除きます。 以下の様に正規表現を使えば、文字列の余分な部分を取り除けます。 以上の処理をして縦持ちに変換したデータフレームが以下になります。
year month country visitor
0 1996 1 韓国 89392
1 1997 1 韓国 99749
2 1998 1 韓国 52159
3 1999 1 韓国 77529
4 2000 1 韓国 93768
5 2001 1 韓国 101442
6 2002 1 韓国 117710
7 2003 1 韓国 147238
8 2004 1 韓国 164785
9 2005 1 韓国 174775
10 2006 1 韓国 192590
11 2007 1 韓国 240350
12 2008 1 韓国 271583
13 2009 1 韓国 129756
14 2010 1 韓国 232053
15 2011 1 韓国 268368
16 2012 1 韓国 173000
17 2013 1 韓国 234456
18 2014 1 韓国 255517
19 2015 1 韓国 358093
20 1996 2 韓国 73878
21 1997 2 韓国 76157
22 1998 2 韓国 48880
23 1999 2 韓国 66794
24 2000 2 韓国 78040
25 2001 2 韓国 82382
26 2002 2 韓国 99506
27 2003 2 韓国 112988
28 2004 2 韓国 142718
29 2005 2 韓国 148946
210 2006 11 韓国 177298
211 2007 11 韓国 218488
212 2008 11 韓国 117518
213 2009 11 韓国 130371
214 2010 11 韓国 197244
215 2011 11 韓国 134009
216 2012 11 韓国 183557
217 2013 11 韓国 170901
218 2014 11 韓国 239029
219 2015 11 韓国 359845
220 1996 12 韓国 78394
221 1997 12 韓国 56137
222 1998 12 韓国 69060
223 1999 12 韓国 80488
224 2000 12 韓国 91703
225 2001 12 韓国 95567
226 2002 12 韓国 96432
227 2003 12 韓国 106689
228 2004 12 韓国 120817
229 2005 12 韓国 142015
230 2006 12 韓国 184678
231 2007 12 韓国 208893
232 2008 12 韓国 108002
233 2009 12 韓国 178027
234 2010 12 韓国 202508
235 2011 12 韓国 141536
236 2012 12 韓国 199950
237 2013 12 韓国 182846
238 2014 12 韓国 270903
239 2015 12 韓国 415656

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

ここまで見てきた一つ目のシートへの処理を全てのシートに対して行い、さらに各シートのデータフレームを一つのデータフレームに結合します。 全てのシートに対してforでループを回せばよいので、以下のようにすればできます。 処理して全てのシートについて結合したデータフレームを表示します。 以下の様に一つのデータフレームでExcelのシートのデータを格納することができました。
year month country visitor
0 1996 1 韓国 89392
1 1997 1 韓国 99749
2 1998 1 韓国 52159
3 1999 1 韓国 77529
4 2000 1 韓国 93768
5 2001 1 韓国 101442
6 2002 1 韓国 117710
7 2003 1 韓国 147238
8 2004 1 韓国 164785
9 2005 1 韓国 174775
10 2006 1 韓国 192590
11 2007 1 韓国 240350
12 2008 1 韓国 271583
13 2009 1 韓国 129756
14 2010 1 韓国 232053
15 2011 1 韓国 268368
16 2012 1 韓国 173000
17 2013 1 韓国 234456
18 2014 1 韓国 255517
19 2015 1 韓国 358093
20 1996 2 韓国 73878
21 1997 2 韓国 76157
22 1998 2 韓国 48880
23 1999 2 韓国 66794
24 2000 2 韓国 78040
25 2001 2 韓国 82382
26 2002 2 韓国 99506
27 2003 2 韓国 112988
28 2004 2 韓国 142718
29 2005 2 韓国 148946
4770 2006 11 スペイン 2093
4771 2007 11 スペイン 2710
4772 2008 11 スペイン 3236
4773 2009 11 スペイン 2827
4774 2010 11 スペイン 2909
4775 2011 11 スペイン 1866
4776 2012 11 スペイン 2842
4777 2013 11 スペイン 3295
4778 2014 11 スペイン 4162
4779 2015 11 スペイン 5014
4780 1996 12 スペイン 560
4781 1997 12 スペイン 688
4782 1998 12 スペイン 661
4783 1999 12 スペイン 708
4784 2000 12 スペイン 684
4785 2001 12 スペイン 753
4786 2002 12 スペイン 1102
4787 2003 12 スペイン 938
4788 2004 12 スペイン 1203
4789 2005 12 スペイン 1270
4790 2006 12 スペイン 1766
4791 2007 12 スペイン 1915
4792 2008 12 スペイン 2398
4793 2009 12 スペイン 2964
4794 2010 12 スペイン 2199
4795 2011 12 スペイン 2435
4796 2012 12 スペイン 2315
4797 2013 12 スペイン 2876
4798 2014 12 スペイン 3337
4799 2015 12 スペイン 4871

フィリピン?

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

csvファイルとして出力

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

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

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