Pythonのデータ解析のライブラリであるpandasを利用してExcelから読み出したデータを結合する方法を確認します。

通常データ解析を行おうと思ったとき社内にある複数のファイルを使ったり、シート毎に登録されたデータを結合する必要が出てきます。その場合に対応できるようにpandasのconcatとmergeを使って結合方法を確認しておきます。

データを結合する(concat)

pandasのconcatメソッドを使えば、2つのファイルに分かれた2つのテーブルを結合することができます。結合は単純で2019年のテーブルの下に2018年のテーブルを追加するといったものです。

2つだけではなく2つ以上でも可能で、ファイルではなくシートに分かれていても手動でデータフレームを作成しても結合は可能です。

結合のために以下の2つのファイルを用意しました。一つは2019年の月ごとの注文数と売上です。もう一つは2018年の月ごとの注文数と売上です。

2019年のデータ
2019年のデータ
2018年のデータ
2018年のデータ

pandasでのExcelデータの読み込みについては下記を参考にしてください。

read_excelメソッドでファイルを読み込みconcatメソッドで2つのEXCELから読み込んだデータフレームを指定して結合しています。


import pandas as pd

df1 = pd.read_excel('2019年売上.xlsx')
df2 = pd.read_excel('2018年売上.xlsx')

df_concat = pd.concat([df1,df2])

print(df_concat)

実行すると結合したテーブルを取得することができます。


$ py test.py
         月  注文数    売上
0  2018年1月   10  1000
1  2018年2月   20  2000
2  2018年3月   30  3000
3  2018年4月   25  2500
4  2018年5月   10  1000
0  2019年1月   10  1000
1  2019年2月   20  2000
2  2019年3月   30  3000
3  2019年4月   25  2500
4  2019年5月   10  1000

結合したテーブルをprint関数で表示させましたが、結合したデータを再度Excelで見たいという要望は多いかと思いますので、結合したデータをファイルに保存します。保存にはto_excelメソッドを使用します。


import pandas as pd

df1 = pd.read_excel('2019年売上.xlsx')
df2 = pd.read_excel('2018年売上.xlsx')

df_concat = pd.concat([df1,df2])

df_concat.to_excel('2019-2018年売上.xlsx',sheet_name="2018-2019")

下記が実行後作成したExcelファイルを開いた図です。結合したテーブルを書き出すことができましたが、A列に各テーブルのindexが表示されています。indexは必要ないので削除する必要があります。

EXCELファイルに書き出す
EXCELファイルに書き出す

indexを削除するためにはindex=Falseを設定する必要があります。


df_concat.to_excel('2019-2018年売上.xlsx',sheet_name="2018-2019",index=False)

index=Falseを設定するとindexが書き込まれなくなり期待したファイルを作成することができました。

indexはスキップして書き出した結果
indexをスキップして書き出した結果

[commen]開いた時のフォントが異なるので見栄えが異なりますが見、フォントを変更すれば使用したファイルと同じように見えます。[/comment]

今回は列名が月、注文数、売上とどちらのファイルでも一致していましたが、もし一致していない場合は、concatでsort=Falseを設定する必要があります。実行結果は下記のようになります。


df_concat = pd.concat([df1,df2],sort=False)
列名が異なっている場合のconcat
列名が異なっている場合のconcat

データを結合する(merge)

今度は2つのテーブルに共通する列を元に結合を行います。その場合はconcatではなくmergeを利用します。一つはお問い合わせ一覧で日付、Email、お問い合わせの3つの列から構成されています。もう一つは注文一覧で日付、Email、名前、購入金額の4つの列で構成されています。Emailの共通の列を使って結合を行います。

2つのテーブルは下記の通りです。

お問い合わせリスト
お問い合わせ一覧
注文一覧
注文一覧

Inner joinによる結合

Inner Joinではどちらのテーブルにも持っているEmailアドレスの行だけ取り出すことができます。


import pandas as pd

df1 = pd.read_excel('お問い合わせ一覧.xlsx')
df2 = pd.read_excel('注文一覧.xlsx')

df_inner_join = pd.merge(df1,df2,on='Email',how='inner')

print(df_inner_join)

mergeメソッドではdf1とdf2のデータフレームを指定し、onには結合するためのkeyになるEmail、howは結合の方法であるinnerを指定しています。

実行するとEmailがどちらの表にも存在する3行の情報が表示されます。johedoeが2回表示されているのはお問い合わせを2回行っているためです。


$ py test.py
   日付_x           Email    お問い合わせ内容  日付_y   名前  購入金額
0 2019-09-01  johndoe@example.com    サイズ確認  2019/9/3  ジョン  1000
1 2019-09-01  johndoe@example.com    サイズ確認  2019/9/5  ジョン  3000
2 2019-09-02        kerry@top.gov   カタログ依頼  2019/9/4  ケリー  1000

どちらもテーブルにも存在した日付列は日付_xと日付_yとして表示されます。

Outer Joinによる結合

Outer JoinではすべてのEmailの行が表示されますが、どちらの表にも存在する行については1つの行として表示されます。

inner joinとのプログラム上での違いはhowをouterに設定する箇所のみです。


import pandas as pd

df1 = pd.read_excel('お問い合わせ一覧.xlsx')
df2 = pd.read_excel('注文一覧.xlsx')

df_outer_join = pd.merge(df1,df2,on='Email',how='outer')

print(df_outer_join)

実行すると8行が表示されます。Emailがどちらにもなかった行については各列にNaNが入っています。NaNはNo a Numberの略で値がないことを意味しています。お問い合わせ一覧のみにあったEmailの行は注文一覧の列に関して値がないのでNanとなり、注文一覧のみにあったEmailの行はお問い合わせ一覧の列に関しては値がないのでNanとなります。


$ py test.py
    日付_x            Email  お問い合わせ内容     日付_y   名前    購入金額
0 2019-09-01  johndoe@example.com    サイズ確認  2019/9/3  ジョン  1000.0
1 2019-09-01  johndoe@example.com    サイズ確認  2019/9/5  ジョン  3000.0
2 2019-09-01     kevin@test.co.jp    到着日確認       NaN  NaN     NaN
3 2019-09-01     mark@abc_xyz.net     見積依頼       NaN  NaN     NaN
4 2019-09-01      luke@ttytty.biz   サンプル依頼       NaN  NaN     NaN
5 2019-09-02        kerry@top.gov   カタログ依頼  2019/9/4  ケリー  1000.0
6        NaT     tonny@hotmail.co      NaN  2019/9/1  トニー  2000.0
7        NaT      alice@gmail.com      NaN  2019/9/2  アリス  5000.0

Left Joinによる結合

Left Joinではmergeの先に指定したデータフレームのEmailはすべて表示され、後に指定されたデータフレームについては先に指定したデータフレームと一致したEmailがある場合のみ表示されます。

今回は先にお問い合わせ一覧のデータフレームを指定するのでお問い合わせ一覧のEmailの行はすべて表示されます。


import pandas as pd

df1 = pd.read_excel('お問い合わせ一覧.xlsx')
df2 = pd.read_excel('注文一覧.xlsx')

df_left_join = pd.merge(df1,df2,on='Email',how='left')

print(df_left_join)

実行するとお問い合わせ一覧のEmailの行はすべて表示され、注文一覧のEmailの行についてはお問い合わせ一覧にあるもののみ表示されています。


$ py test.py
  日付_x           Email   お問い合わせ内容      日付_y   名前    購入金額
0 2019-09-01  johndoe@example.com    サイズ確認  2019/9/3  ジョン  1000.0
1 2019-09-01  johndoe@example.com    サイズ確認  2019/9/5  ジョン  3000.0
2 2019-09-01     kevin@test.co.jp    到着日確認       NaN  NaN     NaN
3 2019-09-01     mark@abc_xyz.net     見積依頼       NaN  NaN     NaN
4 2019-09-01      luke@ttytty.biz   サンプル依頼       NaN  NaN     NaN
5 2019-09-02        kerry@top.gov   カタログ依頼  2019/9/4  ケリー  1000.0

Right Joinによる結合

Right JoinはLeft Joinの逆で、mergeの後に指定したデータフレームのEmailはすべて表示され、先に指定されたデータフレームについては後に指定したデータフレームと一致したEmailがある場合のみ表示されます。

後に指定した注文一覧のEmailの行はすべて表示されます。howをrightに指定します。


import pandas as pd

df1 = pd.read_excel('お問い合わせ一覧.xlsx')
df2 = pd.read_excel('注文一覧.xlsx')

df_right_join = pd.merge(df1,df2,on='Email',how='right'')

print(df_right_join)

実行すると注文一覧のEmailの行はすべて表示され、お問い合わせのEmailの行については注文一覧にあるもののみ表示されています。


$ py test.py
    日付_x             Email  お問い合わせ内容      日付_y   名前  購入金額
0 2019-09-01  johndoe@example.com    サイズ確認  2019/9/3  ジョン  1000
1 2019-09-01  johndoe@example.com    サイズ確認  2019/9/5  ジョン  3000
2 2019-09-02        kerry@top.gov   カタログ依頼  2019/9/4  ケリー  1000
3        NaT     tonny@hotmail.co      NaN  2019/9/1  トニー  2000
4        NaT      alice@gmail.com      NaN  2019/9/2  アリス  5000