EXCELPythonTableauTech Blogちゅらいと 

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

はじめに

公的機関が出しているパブリックデータは、Excelファイル(xls, xlsx)が含まれていることが多く、このようなデータを使って分析をする機会もあることと思います。 この記事では、そのようなExcelブック形式でまとめられているデータをPythonで読み込み、余計な行や列を取り除いたり成形して、pandasのデータフレームとして扱いやすくする方法についてまとめたいと思います。 以下のpandasのドキュメントを参考にしています。

pandas.ExcelFile.parse
https://pandas.pydata.org/docs/reference/api/pandas.ExcelFile.parse.html

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

今回は、Excel形式のファイル(xls, xlsx)をPythonで読み込むやり方として、ライブラリとしてpandasを呼び出して ExcelFile メソッドを用いる方法を紹介します。(pandasではread_excelも同じ用途で活用できます。こちらのpandasドキュメントを参考資料として紹介します)

https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.read_excel.html

Pandasでは 以下の様に読み込みたいExcel形式のファイル名を引数として指定すれば、ExcelfileオブジェクトとしてPythonから参照することができるようになります。

import pandas as pd
input_book = pd.ExcelFile('読み込むファイル名')


この参照したExcelfileオブジェクトに対して、読み込みたいシート名を指定してデータフレームとして読み込んだり、それぞれのシート名を取得することもできます。 ここでは、日本政府観光局(JNTO)のHPで公開されている以下のExcelファイルを例に取って、 Excelブック内の各シートをデータフレームとして読み込み、そのデータフレームの行と列を整理して分析しやすい形に整理する方法を見ていきます。

国籍/月別 訪日外客数(2003年~2021年)(ファイル名:since2003_visitor_arrivals.xlsx)
https://www.jnto.go.jp/jpn/statistics/visitor_trends/

このデータは一つのExcelブック内にシートが複数あり、シート毎にデータ構成が異なっていたりするなど、分析をしていく場合、そのままでは扱いにくいデータになっています。

以下では「since2003_visitor_arrivals.xlsx」(ファイルの保存場所はカレントディレクトリを想定しています)をpandasのExcelFileメソッドで参照し、 そのブック内にあるシートの名称をsheet_namesメソッドでリストとして取得しています。

#pandasを読み込む
import pandas as pd
#input file name
input_file_name = 'since2003_visitor_arrivals.xlsx'
#xls book Open (xls, xlsxのどちらでも可能)
input_book = pd.ExcelFile(input_file_name)
#sheet_namesメソッドでExcelブック内の各シートの名前をリストで取得できる
input_sheet_name = input_book.sheet_names
#lenでシートの総数を確認
num_sheet = len(input_sheet_name)
#シートの数とシートの名前のリストの表示
print ("Sheet の数:", num_sheet)
print (input_sheet_name)
Sheet の数: 19
['2021', '2020', '2019', '2018', '2017', '2016', '2015', '2014', '2013', '2012', '2011', '2010', '2009', '2008', '2007', '2006', '2005', '2004', '2003']

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

参照したExcelfileオブジェクトから、それぞれのシートをデータフレームとして取得するには parse メソッドを使います。 読み込むシートの指定は、そのシートの名前を引数として入力することで行います。

試しに「since2003_visitor_arrivals.xlsx」の3つ目のシート「2019」を選んでデータフレームとして読み込んでみます。 ファイルのシートは全部で19枚あり、先程シート名を格納したリスト input_sheet_name で0番から18番までのリストのインデックスが「2021」年から「2003」年までの各年度のシートにインデックスとして割り振られます。そのためシート「2019」のインデックスは input_sheet_name にて「2」で指定します。

#DataFrameとしてsheet1枚のデータ(2019)を読込み
input_sheet_df = input_book.parse(input_sheet_name[2])


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

#読み込んだシート名の確認
print("Sheet name:", input_sheet_name[2])
#読み込んだシートの先頭10行を表示
input_sheet_df.head(10)
Sheet name: 2019

 

  2019年訪日外客数
(総数)
Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 17 Unnamed: 18 Unnamed: 19 Unnamed: 20 Unnamed: 21 Unnamed: 22 Unnamed: 23 Unnamed: 24 Unnamed: 25 出典:日本政府観光局(JNTO)
0 NaN NaN NaN NaN 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 NaN 単位:人数(人)、伸率(%)
2 NaN 1月 伸率 2月 伸率 3月 伸率 4月 伸率 5月 9月 伸率 10月 伸率 11月 伸率 12月 伸率 累計 伸率
3 総数 2689339 7.51297 2604322 3.78692 2760136 5.83522 2926685 0.895192 2773091 2272883 5.2458 2496568 -5.45488 2441274 -0.386698 2526387 -4.00448 31882049 2.21273
4 アジア計 2366944 7.22851 2323258 3.38397 2287450 5.26783 2369734 -2.52024 2344872 1827278 0.563721 1959436 -10.5692 2016676 -3.72979 2121351 -7.18901 26819278 0.229315
5 韓国 779383 -3.03963 715804 1.05687 585586 -5.42801 566624 -11.2602 603394 201252 -58.0492 197281 -65.4606 205042 -65.1415 247959 -63.6192 5584597 -25.9234
6 中国 754421 19.313 723617 1.01685 691279 16.197 726132 6.25643 756365 819054 25.4794 730631 2.14972 750951 21.6604 710234 18.5529 9594394 14.4911
7 台湾 387498 10.5488 399829 -0.256699 402433 3.89711 403467 -14.1636 426537 376186 14.2929 413701 8.97393 392102 11.4227 348269 3.71631 4890602 2.80296
8 香港 154292 -3.87989 179324 0.471756 171430 -12.3797 194806 8.26766 189007 155927 23.5809 180562 6.54323 199702 19.4504 249642 19.187 2290792 3.75885
9 タイ 92649 12.1767 107845 31.491 147443 26.8731 164817 10.8781 107857 62057 14.0293 145333 23.2168 140265 36.2974 164936 28.5509 1318977 16.5009

 

#読み込んだシートの最後の5行を表示 
input_sheet_df.tail(5) 

 

  2019年訪日外客数
(総数)
Unnamed: 1 Unnamed: 2 Unnamed: 3 Unnamed: 4 Unnamed: 5 Unnamed: 6 Unnamed: 7 Unnamed: 8 Unnamed: 9 Unnamed: 17 Unnamed: 18 Unnamed: 19 Unnamed: 20 Unnamed: 21 Unnamed: 22 Unnamed: 23 Unnamed: 24 Unnamed: 25 出典:日本政府観光局(JNTO)
51 ニュージーランド 7920 16.4021 5068 0 5393 -4.41333 9010 17.0738 6222 11331 62.4516 15683 106.464 7003 31.7592 9554 25.8762 94115 28.5584
52 その他オセアニア 493 8.83002 239 -0.416667 419 21.0983 338 -8.15217 433 913 117.381 709 52.1459 504 18.5882 450 43.77 5832 19.5327
53 無国籍・その他 45 -27.4194 48 26.3158 60 -20 67 -5.6338 66 51 13.3333 74 21.3115 55 -3.50877 67 26.4151 728 8.17236
54 注1: 本資料を引用される際は、出典名を「日本政府観光局(JNTO)」と明示してください。 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN
55 注2: 表中の数値は、全て確定値である。 NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN NaN

5 rows × 27 columns

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

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

次に列ですが、今回は各月ごとの訪日人数が欲しいので、 伸率や累計の列も取り除いてしまいます。 読み込む列を指定するには 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列を読み込む)。 シート「2019」の場合、 parse_cols = “A:B,D,F,H,J,L,N,P,R,T,V,X” と指定すれば国名と国・地域別の1〜12月の訪問者数を取得できます。

列名については names 引数で指定します。

names:列名の指定

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

input_sheet_df = input_book.parse(input_sheet_name[2],
skiprows = 3,
skipfooter = 2,
parse_cols = "A:B,D,F,H,J,L,N,P,R,T,V,X",
names = range(0,13))


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

#先頭10行を表示
input_sheet_df.head(10)

 

  0 1 2 3 4 5 6 7 8 9 10 11 12
0 総数 2689339 2604322 2760136 2926685 2773091 2880041 2991189 2520134 2272883 2496568 2441274 2526387
1 アジア計 2366944 2323258 2287450 2369734 2344872 2483217 2563058 2156004 1827278 1959436 2016676 2121351
2 韓国 779383 715804 585586 566624 603394 611867 561675 308730 201252 197281 205042 247959
3 中国 754421 723617 691279 726132 756365 880651 1050420 1000639 819054 730631 750951 710234
4 台湾 387498 399829 402433 403467 426537 461085 459216 420279 376186 413701 392102 348269
5 香港 154292 179324 171430 194806 189007 209030 216810 190260 155927 180562 199702 249642
6 タイ 92649 107845 147443 164817 107857 62984 73202 49589 62057 145333 140265 164936
7 シンガポール 22676 26102 43687 36704 37650 47264 21716 19698 29147 41937 65295 100376
8 マレーシア 31399 36660 50615 46092 42629 30534 22957 19827 28778 48864 64987 78250
9 インドネシア 32477 24622 39609 39768 30107 49290 25215 16160 25021 34094 37213 59203

 

pandasのメソッドによる整形

parse の引数だけで取り除けない行については、一度データフレームに読み込んでからpandasのデータフレームに関するメソッドを利用して整形します。 もし国別の訪日者数の行だけが欲しい場合は、0列目が「総計」や「アジア計」などの行は取り除くこともできます。 これにはデータフレームに以下のような条件指定をすることで可能です。分析の目的や予定する分析内容によってはデータに残しても問題ない場合もあるので、実施するかどうかは適宜判断をしてください。今回はこれらをデータに残す前提で処理を行います。

input_sheet_df = input_sheet_df[input_sheet_df[0] != '総数']
input_sheet_df = input_sheet_df[input_sheet_df[0] != 'アジア計']


各国の訪日者数のデータ型は元々整数型ですが、データ型の変換法も理解しておきましょう。データフレームの複数の列を型変換するには、astype メソッドを利用します。 引数には、変換したい列名と型の対応が入ったディクショナリを指定します。

#型を整数型に変換
input_sheet_df = input_sheet_df.astype({1:int, 2:int, 3:int, 4:int, 5:int, 6:int, 7:int, 8:int, 9:int, 10:int, 11:int, 12:int})


不要な行を取り除くと、データフレームに付いていた行ラベルも一緒に取り除かれます。 reset_index メソッドを使えば行ラベルを0番から付け直してくれます。

#reset_indexで行ラベルを振り直す(drop = Trueを指定しないと、旧行ラベルが新しい列として追加される)
input_sheet_df = input_sheet_df.reset_index(drop=True)


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

#列名の振り直しはrenameメソッドで可能
input_sheet_df = input_sheet_df.rename(columns={0: 'country'})


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

input_sheet_df

 

  country 1 2 3 4 5 6 7 8 9 10 11 12
0 韓国 779383 715804 585586 566624 603394 611867 561675 308730 201252 197281 205042 247959
1 中国 754421 723617 691279 726132 756365 880651 1050420 1000639 819054 730631 750951 710234
2 台湾 387498 399829 402433 403467 426537 461085 459216 420279 376186 413701 392102 348269
3 香港 154292 179324 171430 194806 189007 209030 216810 190260 155927 180562 199702 249642
4 タイ 92649 107845 147443 164817 107857 62984 73202 49589 62057 145333 140265 164936
5 シンガポール 22676 26102 43687 36704 37650 47264 21716 19698 29147 41937 65295 100376
6 マレーシア 31399 36660 50615 46092 42629 30534 22957 19827 28778 48864 64987 78250
7 インドネシア 32477 24622 39609 39768 30107 49290 25215 16160 25021 34094 37213 59203
8 フィリピン 35987 35170 48277 69266 59578 46842 37771 31470 37758 64690 64763 81542
9 ベトナム 35375 39377 47881 55295 39900 35419 40762 43709 38325 46510 41892 30606
10 インド 12468 9071 17752 18376 19914 15359 13222 13308 15895 13929 14863 11739
11 マカオ 6608 7933 8045 6873 8538 9962 16358 17232 8076 7538 9309 14725
12 イスラエル 1107 1524 6032 7453 3164 1644 2221 2130 5402 7956 3728 1853
13 モンゴル 2893 2108 2989 2650 2166 2570 2203 3275 3102 2637 2473 2447
14 トルコ 1153 1105 2181 2483 1906 2736 2025 1924 1752 1855 2176 1428
15 その他アジア 16558 13167 22211 28928 16160 15980 17285 17774 19546 21918 21915 18142
16 ヨーロッパ計 92337 99499 186225 240955 164278 134028 178049 165158 188424 248153 165715 123708
17 英国 21554 23554 38610 44537 31642 25801 28928 26213 49580 68401 37709 27750
18 フランス 15320 17397 29408 46005 30863 21317 34634 30851 26530 39457 24290 20261
19 ドイツ 11358 13384 28659 27829 21552 15697 18593 17264 22768 26276 19525 13639
20 イタリア 6033 5897 14956 24062 12463 11357 13566 22804 13354 14731 12350 11196
21 ロシア 6316 5601 11701 13787 9691 8844 9005 8321 10454 14348 13142 8833
22 スペイン 4382 4533 8916 13858 9971 9762 15771 20009 11472 13739 10535 7295
23 スウェーデン 3509 3228 5461 6702 4171 5321 4364 2669 3664 5909 4843 3995
24 オランダ 3321 4139 7071 9476 7502 4930 10459 6059 6862 9113 6080 4467
25 スイス 2399 2568 5066 7983 4608 3145 6489 3529 4778 6514 3756 3073
26 ベルギー 1572 1849 3547 5258 3351 2553 5166 2850 3488 4424 3138 2049
27 フィンランド 1875 2213 2729 2604 3102 3054 2108 1580 1931 3309 2336 2596
28 ポーランド 1517 1591 4188 5972 3442 2721 3217 2945 3538 4017 3729 1657
29 デンマーク 1544 1843 3132 4215 2404 1980 4972 1645 2298 3917 3080 1863
30 ノルウェー 1537 1640 2335 3756 1537 2453 3320 1156 1828 1959 1898 1419
31 オーストリア 1382 1479 2498 3451 2179 1732 3005 2453 2640 2827 2288 1596
32 ポルトガル 1587 1571 2538 3527 2728 2577 2828 3312 2712 3389 2693 2887
33 アイルランド 1316 1333 1970 2481 1949 1887 1889 1696 9151 12024 2039 1652
34 その他ヨーロッパ 5815 5679 13440 15452 11123 8897 9735 9802 11376 13799 12284 7480
35 アフリカ計 2777 2337 3952 3933 2847 3852 3651 7790 7717 8657 4201 3325
36 北アメリカ計 130340 120332 221773 219055 199117 207718 196104 151865 163454 200133 190290 187376
37 米国 103191 92669 176564 170247 156962 175491 156865 117828 127190 153363 148993 144498
38 カナダ 22293 23883 37959 38897 35335 25402 29285 27568 28525 37667 33316 35132
39 メキシコ 3615 2889 5740 8222 5482 5676 8661 4800 6282 7385 6494 6499
40 その他北アメリカ 1241 891 1510 1689 1338 1149 1293 1669 1457 1718 1487 1247
41 南アメリカ計 7420 5883 10689 13089 9033 7794 8712 6797 13217 12160 8503 7903
42 ブラジル 2935 2122 5070 5726 4244 3363 4476 2957 3958 4656 3733 4335
43 その他南アメリカ 4485 3761 5619 7363 4789 4431 4236 3840 9259 7504 4770 3568
44 オセアニア計 89476 52965 49987 79852 52878 43360 41553 32459 72742 67955 55834 82657
45 豪州 81063 47658 44175 70504 46223 37283 34873 26951 60498 51563 48327 72653
46 ニュージーランド 7920 5068 5393 9010 6222 5541 6241 5149 11331 15683 7003 9554
47 その他オセアニア 493 239 419 338 433 536 439 359 913 709 504 450
48 無国籍・その他 45 48 60 67 66 72 62 61 51 74 55 67

 

 

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

不要な行や列を取り除けたのでこれで終わりでもよいのですが、今の各国の訪問者数が月別で横持ちになっているデータを縦持ちにすると分析しやすいデータになります。これは分析の前処理としても比較的多く行われている処理だと思います。 横持ちのデータフレームを縦持ちにする簡潔な方法として、本稿ではfor文のループを使った方法を用いることにしました。 以下の処理で、横持ちになっているデータフレームを縦持ちのデータフレームに変換できます。

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

# 変換用に、空のデータフレームを生成
input_trans_df = pd.DataFrame(index=[], columns=[])
for i_m in range(1, len(input_sheet_df.columns)) : 
#データフレームの列について 1列目(1月)-12列目(12月)までループを回す
#country列(0列目)とi_m月(i_m列目)の列を抜き出す
   input_visitor_df = input_sheet_df.iloc[0:,[0, i_m]]
#新たにmonthの列を追加し、その月の値 i_m をその列の成分に入力する
   input_visitor_df["month"] = i_m
#人数の列をvisitorにrename
   input_visitor_df = input_visitor_df.rename(columns = {i_m:"visitor"})
#以上で成形した各月のデータフレーム(input_df)を縦方向に結合していき、一つのデータフレームにしていく
   input_trans_df = pd.concat([input_trans_df,input_visitor_df])
#reset_indexで行ラベルを振り直す
input_trans_df = input_trans_df.reset_index(drop=True)


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

input_trans_df

 

  country visitor month
0 韓国 779383 1
1 中国 754421 1
2 台湾 387498 1
3 香港 154292 1
4 タイ 92649 1
5 シンガポール 22676 1
6 マレーシア 31399 1
7 インドネシア 32477 1
8 フィリピン 35987 1
9 ベトナム 35375 1
10 インド 12468 1
11 マカオ 6608 1
12 イスラエル 1107 1
13 モンゴル 2893 1
14 トルコ 1153 1
15 その他アジア 16558 1
16 ヨーロッパ計 92337 1
17 英国 21554 1
18 フランス 15320 1
19 ドイツ 11358 1
20 イタリア 6033 1
21 ロシア 6316 1
22 スペイン 4382 1
23 スウェーデン 3509 1
24 オランダ 3321 1
25 スイス 2399 1
26 ベルギー 1572 1
27 フィンランド 1875 1
28 ポーランド 1517 1
29 デンマーク 1544 1
558 ドイツ 13639 12
559 イタリア 11196 12
560 ロシア 8833 12
561 スペイン 7295 12
562 スウェーデン 3995 12
563 オランダ 4467 12
564 スイス 3073 12
565 ベルギー 2049 12
566 フィンランド 2596 12
567 ポーランド 1657 12
568 デンマーク 1863 12
569 ノルウェー 1419 12
570 オーストリア 1596 12
571 ポルトガル 2887 12
572 アイルランド 1652 12
573 その他ヨーロッパ 7480 12
574 アフリカ計 3325 12
575 北アメリカ計 187376 12
576 米国 144498 12
577 カナダ 35132 12
578 メキシコ 6499 12
579 その他北アメリカ 1247 12
580 南アメリカ計 7903 12
581 ブラジル 4335 12
582 その他南アメリカ 3568 12
583 オセアニア計 82657 12
584 豪州 72653 12
585 ニュージーランド 9554 12
586 その他オセアニア 450 12
587 無国籍・その他 67 12

 

588 rows × 3 columns

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

ここまで見てきた一つ目のシートへの処理を全てのシートに対して行い、さらに各シートのデータフレームを一つのデータフレームに結合します。

しかし今回の元データ()には取り扱い上の注意点があります。最終的に国名や地域名の列(country)を他の年度と共通の縦持ち仕様でまとめるため、以下に述べる追加の処理を行います。

EXCELの各シートは2003年から2021年まで年単位で各国から日本への訪問者数がまとめられていますが、2003年から2019年までのシートは共通の仕様のため、同じforループのスクリプトで、共通の処理を使って読み込みの処理ができます。しかし2020年と2021年のみ、国名カラムの扱いが異なります。これが追加処理を加える理由となります。

この2つの年度のシートのみ、国名のカラムがシートのAとB列が結合されており、中東地域の3カ国分類だけA列空白でB列に保持となっています。そのため、この2つの年度のシート読み込み用のスクリプトを追加しています。それから2021年データのみ、中東地域は6月まで、3カ国分類は4月まで(2021年8月現在)となっています。今回は読み込み可能な時点までのデータを反映させた出力とします(今後2021年データの追加アップデートの際はこのスクリプトも適宜変更して処理を実行してください)。

#データフレームの取扱、Excelファイルのデータフレームへの読み込み用
import pandas as pd
 
#正規表現モジュールの読み込み、文字列の国名データの取り扱いで必要な場合に使用
import re
 
#input file name
input_file_csv = 'since2003_visitor_arrivals.xlsx'
#xls book Open
input_book = pd.ExcelFile(input_file_csv)
#xlsファイルのシートの名前をリストとして取得
input_sheet_name = input_book.sheet_names
# シートの総数
num_sheet = len(input_sheet_name)
 
output_df = pd.DataFrame(index=[], columns=[])
for i_sheet in range(0, num_sheet) : #0から18まで、全部で19枚のシートを読み込み
  
   #シートを格納しているリストから読み込み
   #シートの読み込み順は2021, 2020 ,2019-2003の順番
   if i_sheet == 0: #「2021」のシート読み込み条件
       df_dummy = input_book.parse(input_sheet_name[0],
                             skiprows = 3,
                             skip_footer = 2,
                             parse_cols = "A:C,E,G,I,K,M,O,Q,S,U,W,Y",
                             names =  range(0,14))
       #2021年度の中東地域3カ国分のデータ抽出
       df_dummy1 = df_dummy.dropna(subset=[1])
       df_dummy1 = df_dummy1.dropna(how='all', axis=1)
       df_dummy1.columns = [0, 1, 2, 3, 4]
       #2021年度の中東地域3カ国分を除く国別データ抽出
       df_dummy2 = df_dummy.dropna(subset=[0])
       df_dummy2 = df_dummy2.dropna(how='all', axis=1)
       df_dummy2.columns = [0, 1, 2, 3, 4, 5, 6]
       #2つのデータを結合して2021年度の横持ちデータ作成
       input_sheet_df = pd.concat([df_dummy1, df_dummy2])
   elif i_sheet == 1: #「2020」のシート読み込み条件
       df_dummy = input_book.parse(input_sheet_name[1],
                             skiprows = 3,
                             skip_footer = 2,
                             parse_cols = "A:C,E,G,I,K,M,O,Q,S,U,W,Y",
                             names =  range(0,14))
       #2020年度の中東地域3カ国分のデータ抽出
       df_dummy1 = df_dummy.dropna(subset=[1])
       df_dummy1 = df_dummy1.dropna(how='all', axis=1)
       df_dummy1.columns = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ,12]
       #2020年度の中東地域3カ国分を除く国別データ抽出
       df_dummy2 = df_dummy.dropna(subset=[0])
       df_dummy2 = df_dummy2.dropna(how='all', axis=1)
       df_dummy2.columns = [0, 1, 2, 3, 4, 5, 6, 7, 8, 9, 10, 11 ,12]
       #2つのデータを結合して2020年度の横持ちデータ作成
       input_sheet_df = pd.concat([df_dummy1, df_dummy2])
   else:#2019年から2003年のシート読み込み条件
       input_sheet_df = input_book.parse(input_sheet_name[i_sheet],
                                     skiprows = 3,
                                     skip_footer = 2,
                                     parse_cols = "A:B,D,F,H,J,L,N,P,R,T,V,X",
                                     names =  range(0,13))
  
   #各シートのデータフレームの「国名」の列名を振り直す
   input_sheet_df = input_sheet_df.rename(columns={0: 'country'})
  
   ####縦持ち横持ち変換####
   # 変換用に、空のデータフレームを生成
   input_trans_df = pd.DataFrame(index=[], columns=[])
   for i_m in range(1, len(input_sheet_df.columns)) : #データフレームの列について 1列目(1月)-12列目(12月)までループを回す    
      
       #year列(0列目)とi_m月(i_m列目)の列を抜き出す
       input_visitor_df = input_sheet_df.iloc[0:,[0, i_m]]
 
       #新たにmonthの列を追加し、その月の値 i_m をその列の成分に入力する
       input_visitor_df["month"] = i_m
 
       #人数の列をvisitorにrename
       input_visitor_df = input_visitor_df.rename(columns = {i_m:"visitor"})
 
       #以上で成形した各月のデータフレーム(input_df)を縦方向に結合して、一つのデータフレームにしていく
       input_trans_df = pd.concat([input_trans_df,input_visitor_df])
  
   ####シート名になっている年度を表す列yearを追加####
   #新たにyearの列を追加し、値を入力
   input_trans_df["year"] = input_sheet_name[i_sheet]
   input_trans_df = input_trans_df.dropna(axis=0, how='any', thresh=None, subset=None, inplace=False)
  
   #以上でシートごとに処理したデータフレームを一つのデータフレームに結合
   output_df = pd.concat([output_df,input_trans_df])
 
#旅行者(visitor)を整数型に変換
output_df = output_df.astype({'visitor':int})
  
#行のラベルを振り直す
output_df = output_df.reset_index(drop=True)
 
#列の順を year, month, visitorの順にする
output_df = output_df.loc[:,['year','month', "country",'visitor']]

 

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

output_df

 

  year month country visitor
0 2021 1 イスラエル 23
1 2021 1 トルコ 38
2 2021 1 GCC6か国 64
3 2021 1 総数 46522
4 2021 1 アジア計 41741
5 2021 1 韓国 2535
6 2021 1 中国 10225
7 2021 1 台湾 592
8 2021 1 香港 161
9 2021 1 タイ 719
10 2021 1 シンガポール 85
11 2021 1 マレーシア 242
12 2021 1 インドネシア 922
13 2021 1 フィリピン 998
14 2021 1 ベトナム 20032
15 2021 1 インド 905
16 2021 1 中東地域 125
17 2021 1 マカオ 14
18 2021 1 モンゴル 4
19 2021 1 その他アジア 4182
20 2021 1 ヨーロッパ計 2539
21 2021 1 英国 256
22 2021 1 フランス 554
23 2021 1 ドイツ 351
24 2021 1 イタリア 153
25 2021 1 ロシア 204
26 2021 1 スペイン 174
27 2021 1 スウェーデン 73
28 2021 1 オランダ 63
29 2021 1 スイス 47
10176 2003 12 トルコ 406
10177 2003 12 ヨーロッパ計 46798
10178 2003 12 英国 14295
10179 2003 12 フランス 6455
10180 2003 12 ドイツ 5937
10181 2003 12 イタリア 3470
10182 2003 12 ロシア 3221
10183 2003 12 スペイン 938
10184 2003 12 スウェーデン 1628
10185 2003 12 オランダ 1800
10186 2003 12 スイス 1273
10187 2003 12 ベルギー 752
10188 2003 12 フィンランド 802
10189 2003 12 ポーランド 281
10190 2003 12 デンマーク 707
10191 2003 12 ノルウェー 523
10192 2003 12 オーストリア 615
10193 2003 12 ポルトガル 809
10194 2003 12 アイルランド 703
10195 2003 12 アフリカ計 1009
10196 2003 12 北アメリカ計 65628
10197 2003 12 米国 52449
10198 2003 12 カナダ 11871
10199 2003 12 メキシコ 982
10200 2003 12 南アメリカ計 3122
10201 2003 12 ブラジル 1039
10202 2003 12 オセアニア計 19153
10203 2003 12 豪州 16395
10204 2003 12 ニュージーランド 2527
10205 2003 12 無国籍・その他 81

 

10206 rows × 4 columns

やっぱり自分で確認することが大事

データを取得して、読み込みを繰り返しながら前処理で試行錯誤することも大事ですが、分析目的や課題に沿って実行する前処理と分析プロセスを整理しつつ、スクリプトの準備ができると作業を効率的に進めることができると思いませんか?

実際に出力されたデータを自分で確認しながら進めていこくとこで、初めて理解できることも多いと思います。 自分の目でデータと処理した結果を照らし合わせて確認しながら進めて行くことが非常に大事になるのではないでしょうか。

csvファイルとして出力

csvファイルとして処理結果を出力してみます。to_csvメソッドで出力できます。

#csvファイルとして出力してみる, Shift-JIS
#outpu file name
outputfile = 'visitor_by_month.csv'
print("Output file name is " + "\"" + outputfile + "\"")
  
#csv output
#引数encoding="shift-jis"で指定するとShift-JIS形式で出力
#引数encoding="utf-8"で指定するとutf-8形式で出力
output_df.to_csv(outputfile,  sep=",", header=True, index =False,
               encoding="shift-jis", line_terminator="\n" )
Output file name is "visitor_by_month.csv"

他に使えそうなメソッド

他にもEXCELデータを読み込んでからのデータフレーム整理で使えそうなメソッドを以下に上げておきます(一部本文のスクリプトにて使用済みのメソッドも整理のため掲載します)。

#欠損値NaNを落とす際にdropna()を使う
input_sheet_df = input_sheet_df.dropna()
#NaN補完
#fillna(補完したい数とか)で可能、空白を0人としてカウントしたい時など
input_sheet_df = input_sheet_df.fillna(0)
#ある特定の要素だけの変更はreplaceを使う
#今回のデータであれば、国名を正式名に変えたい場合など
#(例) 以下では国名が"米国"となっている要素だけを"アメリカ合衆国"に入れ替える
output_df = output_df.replace('米国', 'アメリカ合衆国')

 

DATUM STUDIOでは様々なAI/機械学習のプロジェクトを行っております。
詳細につきましてはこちら

詳細/サービスについてのお問い合わせはこちら

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



DATUM STUDIOは、クライアントの事業成長と経営課題解決を最適な形でサポートする、データ・ビジネスパートナーです。
データ分析の分野でお客様に最適なソリューションをご提供します。まずはご相談ください。