pandas merge関数を使って、表を拡張する
やりたいこと
例えばショッピングサイトで、ひとつの商品ページの中で、色やサイズのバリエーションを選んで注文出来たりすることがあります。この商品データを Excel とかで作ると、ほとんど共通のデータで、色やサイズや価格や Jancode といった部分的にちょっと違うデータの作成となります。そこで、共通部分のメインデータと、差分の部分だけのサブデータを作って、pandas の merge関数で合体させて拡張されたデータを作ってみようという話です。
※あくまでも、pandas の merge 関数の利用法のひとつの紹介が目的です。データも適当な小さい例ですのでご了承下さい。
具体的な入出力
- 各商品の代表のみを作ったメインデータです。
data_main.xlsx
'itemid' がショッピングサイトで1ページに表示される「ひとつの商品」の商品コードとなります。
'sku' は、色やサイズなどで商品を細かく区別する際の各商品のコードとなります。
-
バリエーションのサブデータで、必要な部分のみを作成しています。ここではカラーと sku だけがバリエーションになるとします。
data_sub.xlsx -
色々加工して、 merge を使って最終的に出力されたデータです。
data_merge.xlsx
サブデータの各 itemid に対して、メインデータの同じ itemid のデータを参照し、共通部分はメインデータと同じとし、サブデータにある部分だけ置き換えてメインデータに追加し、マージデータを作成します。
単純な merge
まず、単純に merge 関数を適用してみます。
エクセルシートを pandas の DataFrame として読みこみ、
merge 関数で itemid をキーとします。
ここで、メインデータの itemid がサブデータに無い場合でも、
出力には含めたいので、how='left' として左結合とします。
（下の出力の最後で、P004 はサブデータにありませんが表示されています。）
import os
import pandas as pd
メインファイル = 'data_main.xlsx'
サブファイル = 'data_sub.xlsx'
このフォルダ = os.path.dirname(__file__)
メインデータ = pd.read_excel(os.path.join(このフォルダ, メインファイル), header=0)
サブデータ = pd.read_excel(os.path.join(このフォルダ, サブファイル), header=0)
print('---- メインデータ ----')
print(メインデータ)
print()
print('---- サブデータ ----')
print(サブデータ)
print()
マージデータ = メインデータ.merge(サブデータ, how='left', on='itemid')
print('---- マージデータ ----')
print(マージデータ)
出力は・・・、
---- メインデータ ----
itemid sku name color price description
0 P001 490010 Leo Black 15000 いいね
1 P002 490020 Virgo White 10000 すてき
2 P003 490030 Libra White 20000 おすすめ
3 P004 490040 Scorpio Black 15000 よきかな
---- サブデータ ----
itemid sku color
0 P001 490011 Red
1 P001 490012 White
2 P001 490013 Blue
3 P002 490021 Red
4 P002 490022 Blue
5 P003 490031 Blue
6 P003 490032 Red
7 P003 490033 Yellow
---- マージデータ ----
itemid sku_x name color_x price description sku_y color_y
0 P001 490010 Leo Black 15000 いいね 490011.0 Red
1 P001 490010 Leo Black 15000 いいね 490012.0 White
2 P001 490010 Leo Black 15000 いいね 490013.0 Blue
3 P002 490020 Virgo White 10000 すてき 490021.0 Red
4 P002 490020 Virgo White 10000 すてき 490022.0 Blue
5 P003 490030 Libra White 20000 おすすめ 490031.0 Blue
6 P003 490030 Libra White 20000 おすすめ 490032.0 Red
7 P003 490030 Libra White 20000 おすすめ 490033.0 Yellow
8 P004 490040 Scorpio Black 15000 よきかな NaN NaN
思ってたのと違いますね。まぁ、そりゃそうです。
具体的に直したい所は、
- メインの商品データに、サブデータを追加するかたちにしたい
- sku、 color は同じ列にしたい
ということになります。
改修編
入力のエクセルファイルは変更せず、プログラム内で改修します。
改修ポイントは、下のようになります。
- メインデータから、共通部分（'name', 'price', 'description'）と、個別の商品データ（'sku', 'color'）をそれぞれ抜き出して「共通データ」「メインのサブデータ」の 2つの DataFrame を作ります。ここで、個別の商品データの列構造はサブデータと同じようにします。
- メインデータの個別商品データに、サブデータを追加した「連結データ」の DataFrame を作ります。
- 1, 2 のデータを merge で結合します。
それでは作ってみましょう。
import os
import numpy as np
import pandas as pd
メインファイル = 'data_main.xlsx'
サブファイル = 'data_sub.xlsx'
出力ファイル = 'data_merge.xlsx'
このフォルダ = os.path.dirname(__file__)
メインデータ = pd.read_excel(os.path.join(このフォルダ, メインファイル), header=0)
サブデータ = pd.read_excel(os.path.join(このフォルダ, サブファイル), header=0)
共通データ = メインデータ[['itemid', 'name', 'price', 'description']]
print('---- 共通データ ----')
print(共通データ)
print()
メインのサブデータ = メインデータ[['itemid', 'sku', 'color']]
print('---- メインのサブデータ ----')
print(メインのサブデータ)
print()
連結データ = pd.concat([メインのサブデータ, サブデータ])
print('---- 連結データ ----')
print(連結データ)
print()
マージデータ1 = 共通データ.merge(連結データ, how='left', on='itemid')
print('---- マージデータ1 ----')
print(マージデータ1)
print()
マージデータ2 = マージデータ1.reindex(columns=['itemid', 'sku', 'name', 'color', 'price', 'description']).sort_values('sku')
マージデータ2.to_excel(os.path.join(このフォルダ, 出力ファイル), index=False)
必須ではありませんが、エクセル出力前に、列の順番を入れ替えて sku の値で並び替えています。
出力は下のとおりです。エクセルファイルの出力は、この記事最初の方の画像のとおりです。
---- 共通データ ----
itemid name price description
0 P001 Leo 15000 いいね
1 P002 Virgo 10000 すてき
2 P003 Libra 20000 おすすめ
3 P004 Scorpio 15000 よきかな
---- メインのサブデータ ----
itemid sku color
0 P001 490010 Black
1 P002 490020 White
2 P003 490030 White
3 P004 490040 Black
---- 連結データ ----
itemid sku color
0 P001 490010 Black
1 P002 490020 White
2 P003 490030 White
3 P004 490040 Black
0 P001 490011 Red
1 P001 490012 White
2 P001 490013 Blue
3 P002 490021 Red
4 P002 490022 Blue
5 P003 490031 Blue
6 P003 490032 Red
7 P003 490033 Yellow
---- マージデータ1 ----
itemid name price description sku color
0 P001 Leo 15000 いいね 490010 Black
1 P001 Leo 15000 いいね 490011 Red
2 P001 Leo 15000 いいね 490012 White
3 P001 Leo 15000 いいね 490013 Blue
4 P002 Virgo 10000 すてき 490020 White
5 P002 Virgo 10000 すてき 490021 Red
6 P002 Virgo 10000 すてき 490022 Blue
7 P003 Libra 20000 おすすめ 490030 White
8 P003 Libra 20000 おすすめ 490031 Blue
9 P003 Libra 20000 おすすめ 490032 Red
10 P003 Libra 20000 おすすめ 490033 Yellow
11 P004 Scorpio 15000 よきかな 490040 Black
メインデータを分割し、個別商品データをサブデータと連結することで、目的のデータが出力されました。
最初から目的に合うように入力ファイルのフォーマットを変更することもひとつの方法ですが、実際の現場では他部署の都合もあり、変更することが難しい場合も多々あります。プログラム内でやりくりするのも、面倒くさいですが、それなりにやりがいや達成感もあります。
Discussion