Paython Pandas Excelデータで行・列結合(複数列)

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

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

1 データを結合する(concat)
2 データを結合する(merge)
3 複数列の結合

データを結合する(concat)

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

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

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

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

あわせて読みたい

Python 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は通常必要ないので削除する方法を確認しておきます。

表示されているindexを結合データを保存するEXCELに書き込まないためにはindexオプションを設定をindex=Falseと設定します。


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

index=Falseを設定するとindexの情報のEXCELデータへの書き込みがなくなり、期待したファイルを作成することができます。

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

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

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


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

データを結合する(merge)

concatは単純に2つのテーブルを結合していましたが、今度は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

複数列の結合

これまではテーブルの1つの列の結合を行ってきましたが、複数の行を使って結合を行いたい場合は下記のように配列を利用して行うことができます。下記の処理だと品番と色と数量が一致する行と一致しない行を確認することができます。


df_merge_file = pd.merge(df1,df2,on=["品番","色","数量"],how="outer",indicator=True)

データの結合はデータ分析をする際に頻繁に利用される機能です、特にmergetの列結合についてはそれぞれ(inner, outer, left, right)の処理をしっかりと理解してさまざまな条件で適切な処理方法を選択できるようにしておいてください。

Python Pandas Excelデータで行・列結合(複数列)