はじめに

公的機関が出しているパブリックデータは、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

 

 

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

 

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)

 

 

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

 

 

 

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

不要な行や列を取り除けたのでこれで終わりでもよいのですが、今の各国の訪問者数が月別で横持ちになっているデータを縦持ちにすると分析しやすいデータになります。これは分析の前処理としても比較的多く行われている処理だと思います。 横持ちのデータフレームを縦持ちにする簡潔な方法として、本稿では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

 

 

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]
       #２つのデータを結合して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]
       #２つのデータを結合して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

 

 

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/機械学習のプロジェクトを行っております。
詳細につきましてはこちら

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