当ページのリンクには広告が含まれています。
目次
データサイエンス100本ノックの始め方は、以下の記事を参考にしていただければと思います。
>>データサイエンス100本ノックの始め方を確認する
第87問目：名寄せ（完全一致）
P-087: 顧客データフレーム（df_customer）では、異なる店舗での申込みなどにより同一顧客が複数登録されている。名前（customer_name）と郵便番号（postal_cd）が同じ顧客は同一顧客とみなし、1顧客1レコードとなるように名寄せした名寄顧客データフレーム（df_customer_u）を作成せよ。ただし、同一顧客に対しては売上金額合計が最も高いものを残すものとし、売上金額合計が同一もしくは売上実績の無い顧客については顧客ID（customer_id）の番号が小さいものを残すこととする。
名寄せとは、重複したデータを除く作業のことを言います。
まずはdf_customerの構造を確認してみましょう。
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd
0 CS021313000114 大野 あや子 1 女性 1981-04-29 37 259-1113 神奈川県伊勢原市粟窪********** S14021 20150905 0-00000000-0
1 CS037613000071 六角 雅彦 9 不明 1952-04-01 66 136-0076 東京都江東区南砂********** S13037 20150414 0-00000000-0
2 CS031415000172 宇多田 貴美子 1 女性 1976-10-04 42 151-0053 東京都渋谷区代々木********** S13031 20150529 D-20100325-C
3 CS028811000001 堀井 かおり 1 女性 1933-03-27 86 245-0016 神奈川県横浜市泉区和泉町********** S14028 20160115 0-00000000-0
4 CS001215000145 田崎 美紀 1 女性 1995-03-29 24 144-0055 東京都大田区仲六郷********** S13001 20170605 6-20090929-2
5 CS020401000016 宮下 達士 0 男性 1974-09-15 44 174-0065 東京都板橋区若木********** S13020 20150225 0-00000000-0
6 CS015414000103 奥野 陽子 1 女性 1977-08-09 41 136-0073 東京都江東区北砂********** S13015 20150722 B-20100609-B
7 CS029403000008 釈 人志 0 男性 1973-08-17 45 279-0003 千葉県浦安市海楽********** S12029 20150515 0-00000000-0
8 CS015804000004 松谷 米蔵 0 男性 1931-05-02 87 136-0073 東京都江東区北砂********** S13015 20150607 0-00000000-0
9 CS033513000180 安斎 遥 1 女性 1962-07-11 56 241-0823 神奈川県横浜市旭区善部町********** S14033 20150728 6-20080506-5
|
問題文より、顧客ごとの売上金額の合計を算出する必要があるため、df_receiptデータフレームの構造も確認します。
出力1
2
3
4
5
6
7
8
9
10
11
| sales_ymd sales_epoch store_cd receipt_no receipt_sub_no customer_id product_cd quantity amount
0 20181103 1541203200 S14006 112 1 CS006214000001 P070305012 1 158
1 20181118 1542499200 S13008 1132 2 CS008415000097 P070701017 1 81
2 20170712 1499817600 S14028 1102 1 CS028414000014 P060101005 1 170
3 20190205 1549324800 S14042 1132 1 ZZ000000000000 P050301001 1 25
4 20180821 1534809600 S14025 1102 2 CS025415000050 P060102007 1 90
5 20190605 1559692800 S13003 1112 1 CS003515000195 P050102002 1 138
6 20181205 1543968000 S14024 1102 2 CS024514000042 P080101005 1 30
7 20190922 1569110400 S14040 1102 1 CS040415000178 P070501004 1 128
8 20170504 1493856000 S13020 1112 2 ZZ000000000000 P071302010 1 770
9 20191010 1570665600 S14027 1102 1 CS027514000015 P071101003 1 680
|
今回の問題では、同一顧客に関しては売上金額の合計が最も高い売上合計額を残し、売上金額の合計が同一もしくは売上実績の無い顧客については顧客IDの番号が小さいものを残す処理をしなければなりません。
これを以下のような流れで処理します。
- 顧客ごとの売上金額合計を算出する
- 売上金額合計を、顧客データフレーム(df_customer)に追加し、売上金額合計を降順にソートする。ソートの際は顧客IDを照準にする。
- 降順にソートしたデータフレームに対して、最初の行を残し超副業を削除する。
まずは。1. 顧客ごとの売上金額合計を算出していきます。
1
2
| df_tmp = df_receipt.groupby('customer_id').agg({'amount':'sum'}).reset_index()
df_tmp.head(10)
|
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id amount
0 CS001113000004 1298
1 CS001114000005 626
2 CS001115000010 3044
3 CS001205000004 1988
4 CS001205000006 3337
5 CS001211000025 456
6 CS001212000027 448
7 CS001212000031 296
8 CS001212000046 228
9 CS001212000070 456
|
顧客データフレーム(df_customer)に売上金額の合計を追加し、売上金額の合計を降順にソートします。
ソートはsort_valuesメソッドを使用します。
sort_valuesについては、【解説】手を動かしながらソートと表連結を学ぶ | データサイエンス100本ノック【問17〜問20 回答】 - omathin blogを参照ください。
sort_valuesメソッドでは、ソートの基準にしたいカラム名をリストで指定します。
リストで複数のカラム名をした場合、最初に指定されたカラムが優先的にソートされます。
また、ascending=[True, False]のように指定すると、Trueは昇順、Falseは降順にすることができます。
少しわかりにくいかと思うので、簡単なデータフレームでを作成して上記の動作例を示します。
サンプルコード1
2
3
4
5
| import pandas as pd
columns = ['product', 'price']
data = [['pen', 300], ['apple', 150], ['apple', 170], ['pen', 320], ['book', 500], ['bag', 1200]]
df = pd.DataFrame(data, columns=columns)
df.sort_values(columns, ascending=[True, False])
|
出力1
2
3
4
5
6
7
| product price
2 apple 170
1 apple 150
5 bag 1200
4 book 500
3 pen 320
0 pen 300
|
このように、productカラムのデータはアルファベット順（昇順）、priceはproduct毎に価格が高い順（降順）になっていることがわかります。
本問では、amountを降順にし、顧客IDは昇順でソートしたいと思います。
1
2
| df_customer_u = pd.merge(df_customer, df_tmp, how='left', on='customer_id').sort_values(['amount', 'customer_id'], ascending=[False, True])
df_customer_u.head(10)
|
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd amount
16905 CS017415000097 福士 千夏 1 女性 1973-04-03 45 166-0014 東京都杉並区松ノ木********** S13017 20151209 F-20101006-F 23086.0
12692 CS015415000185 岩淵 はるみ 1 女性 1973-09-19 45 135-0043 東京都江東区塩浜********** S13015 20150322 F-20101014-F 20153.0
13550 CS031414000051 長澤 沙知絵 1 女性 1973-04-25 45 151-0064 東京都渋谷区上原********** S13031 20150823 F-20101009-F 19202.0
4808 CS028415000007 紺野 あい 1 女性 1969-07-28 49 246-0023 神奈川県横浜市瀬谷区阿久和東********** S14028 20151212 F-20100922-F 19127.0
14205 CS001605000009 安部 耕司 0 男性 1952-10-22 66 144-0035 東京都大田区南蒲田********** S13001 20160203 F-20101019-E 18925.0
14760 CS010214000010 高嶋 芽以 1 女性 1991-02-19 28 221-0004 神奈川県横浜市神奈川区西大口********** S14010 20141106 F-20100909-F 18585.0
15709 CS006515000023 竹村 はるみ 1 女性 1963-06-27 55 224-0032 神奈川県横浜市都筑区茅ケ崎中央********** S14006 20151217 F-20100831-F 18372.0
6353 CS016415000141 西谷 愛梨 1 女性 1974-05-06 44 184-0012 東京都小金井市中町********** S13016 20150117 F-20100611-F 18372.0
21458 CS011414000106 紺野 窈 1 女性 1972-11-11 46 223-0062 神奈川県横浜市港北区日吉本町********** S14011 20150921 F-20101028-F 18338.0
20336 CS038415000104 城戸 しほり 1 女性 1971-02-21 48 134-0084 東京都江戸川区東葛西********** S13038 20151119 F-20100922-F 17847.0
|
同一顧客に対しては売上金額合計が最も高いものを残すように削除します。
1
2
| df_customer_u.drop_duplicates(subset=['customer_name', 'postal_cd'], keep='first', inplace=True)
df_customer_u.head(10)
|
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd amount
16905 CS017415000097 福士 千夏 1 女性 1973-04-03 45 166-0014 東京都杉並区松ノ木********** S13017 20151209 F-20101006-F 23086.0
12692 CS015415000185 岩淵 はるみ 1 女性 1973-09-19 45 135-0043 東京都江東区塩浜********** S13015 20150322 F-20101014-F 20153.0
13550 CS031414000051 長澤 沙知絵 1 女性 1973-04-25 45 151-0064 東京都渋谷区上原********** S13031 20150823 F-20101009-F 19202.0
4808 CS028415000007 紺野 あい 1 女性 1969-07-28 49 246-0023 神奈川県横浜市瀬谷区阿久和東********** S14028 20151212 F-20100922-F 19127.0
14205 CS001605000009 安部 耕司 0 男性 1952-10-22 66 144-0035 東京都大田区南蒲田********** S13001 20160203 F-20101019-E 18925.0
14760 CS010214000010 高嶋 芽以 1 女性 1991-02-19 28 221-0004 神奈川県横浜市神奈川区西大口********** S14010 20141106 F-20100909-F 18585.0
15709 CS006515000023 竹村 はるみ 1 女性 1963-06-27 55 224-0032 神奈川県横浜市都筑区茅ケ崎中央********** S14006 20151217 F-20100831-F 18372.0
6353 CS016415000141 西谷 愛梨 1 女性 1974-05-06 44 184-0012 東京都小金井市中町********** S13016 20150117 F-20100611-F 18372.0
21458 CS011414000106 紺野 窈 1 女性 1972-11-11 46 223-0062 神奈川県横浜市港北区日吉本町********** S14011 20150921 F-20101028-F 18338.0
20336 CS038415000104 城戸 しほり 1 女性 1971-02-21 48 134-0084 東京都江戸川区東葛西********** S13038 20151119 F-20100922-F 17847.0
|
コレで完了しましたが、重複を削除した数がどの程度かを確認します。
1
| print('減少数：', len(df_customer)-len(df_customer_u))
|
第88問目: 名寄せ（変換データ作成）
P-088: 前設問で作成したデータを元に、顧客データフレームに統合名寄IDを付与したデータフレーム（df_customer_n）を作成せよ。ただし、統合名寄IDは以下の仕様で付与するものとする。
・重複していない顧客：顧客ID（customer_id）を設定
・重複している顧客：前設問で抽出したレコードの顧客IDを設定
本問では、前の問題で作成した名寄顧客データフレーム(df_customer_u)を顧客データフレーム(df_customer)に反映させます。
まずは、df_customerとdf_customer_uを内部結合します。df_customerとdf_customer_uで共通のカラムをまずは確認します。
1
2
3
4
| df_customer_columns = df_customer.columns
df_customer_u_columns = df_customer_u.columns
df_and = set(df_customer_columns) & set(df_customer_u_columns)
df_and
|
出力1
2
3
4
5
6
7
8
9
10
11
| {'address',
'age',
'application_date',
'application_store_cd',
'birth_day',
'customer_id',
'customer_name',
'gender',
'gender_cd',
'postal_cd',
'status_cd'}
|
共通するカラムが複数存在します。顧客情報に関連するデータを共通カラムとして指定して内部結合していきたいので、ここでは customer_name, postal_cdの２つを共通カラムとして指定し内部結合していきたいと思います。customer_idは問題文よりもともとのdf_customerに含まれているcustomer_idと、df_customer_uのcustomer_idを分けて結合し、df_customer_u側のcustomer_idがintegration_idにするため、共通カラムとして指定は行いません。
1
2
| df_customer_n = pd.merge(df_customer, df_customer_u[['customer_name', 'postal_cd', 'customer_id']], how='inner', on=['customer_name', 'postal_cd'])
df_customer_n.head(10)
|
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id_x customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd customer_id_y
0 CS021313000114 大野 あや子 1 女性 1981-04-29 37 259-1113 神奈川県伊勢原市粟窪********** S14021 20150905 0-00000000-0 CS021313000114
1 CS037613000071 六角 雅彦 9 不明 1952-04-01 66 136-0076 東京都江東区南砂********** S13037 20150414 0-00000000-0 CS037613000071
2 CS031415000172 宇多田 貴美子 1 女性 1976-10-04 42 151-0053 東京都渋谷区代々木********** S13031 20150529 D-20100325-C CS031415000172
3 CS028811000001 堀井 かおり 1 女性 1933-03-27 86 245-0016 神奈川県横浜市泉区和泉町********** S14028 20160115 0-00000000-0 CS028811000001
4 CS001215000145 田崎 美紀 1 女性 1995-03-29 24 144-0055 東京都大田区仲六郷********** S13001 20170605 6-20090929-2 CS001215000145
5 CS020401000016 宮下 達士 0 男性 1974-09-15 44 174-0065 東京都板橋区若木********** S13020 20150225 0-00000000-0 CS020401000016
6 CS015414000103 奥野 陽子 1 女性 1977-08-09 41 136-0073 東京都江東区北砂********** S13015 20150722 B-20100609-B CS015414000103
7 CS029403000008 釈 人志 0 男性 1973-08-17 45 279-0003 千葉県浦安市海楽********** S12029 20150515 0-00000000-0 CS029403000008
8 CS015804000004 松谷 米蔵 0 男性 1931-05-02 87 136-0073 東京都江東区北砂********** S13015 20150607 0-00000000-0 CS015804000004
9 CS033513000180 安斎 遥 1 女性 1962-07-11 56 241-0823 神奈川県横浜市旭区善部町********** S14033 20150728 6-20080506-5 CS033513000180
|
df_customer_nの構造とみると、customer_idが2つ存在しています。customer_id_yのほうが、df_customer_uで所持していたcustomer_id、すなわちintegration_idになります。
renameメソッドを用いてカラム名を変更していきます。引数inplaceをTrueにすると、元のpandas.DataFrameが変更されます。
renameメソッドの基礎に関しては、【解説】データサイエンス100本ノック【第1問〜第10問】 - omathin blog を参照ください。
1
2
| df_customer_n.rename(columns={'customer_id_x':'customer_id', 'customer_id_y':'integration_id'}, inplace=True)
df_customer_n.head(10)
|
出力1
2
3
4
5
6
7
8
9
10
11
| customer_id customer_name gender_cd gender birth_day age postal_cd address application_store_cd application_date status_cd integration_id
0 CS021313000114 大野 あや子 1 女性 1981-04-29 37 259-1113 神奈川県伊勢原市粟窪********** S14021 20150905 0-00000000-0 CS021313000114
1 CS037613000071 六角 雅彦 9 不明 1952-04-01 66 136-0076 東京都江東区南砂********** S13037 20150414 0-00000000-0 CS037613000071
2 CS031415000172 宇多田 貴美子 1 女性 1976-10-04 42 151-0053 東京都渋谷区代々木********** S13031 20150529 D-20100325-C CS031415000172
3 CS028811000001 堀井 かおり 1 女性 1933-03-27 86 245-0016 神奈川県横浜市泉区和泉町********** S14028 20160115 0-00000000-0 CS028811000001
4 CS001215000145 田崎 美紀 1 女性 1995-03-29 24 144-0055 東京都大田区仲六郷********** S13001 20170605 6-20090929-2 CS001215000145
5 CS020401000016 宮下 達士 0 男性 1974-09-15 44 174-0065 東京都板橋区若木********** S13020 20150225 0-00000000-0 CS020401000016
6 CS015414000103 奥野 陽子 1 女性 1977-08-09 41 136-0073 東京都江東区北砂********** S13015 20150722 B-20100609-B CS015414000103
7 CS029403000008 釈 人志 0 男性 1973-08-17 45 279-0003 千葉県浦安市海楽********** S12029 20150515 0-00000000-0 CS029403000008
8 CS015804000004 松谷 米蔵 0 男性 1931-05-02 87 136-0073 東京都江東区北砂********** S13015 20150607 0-00000000-0 CS015804000004
9 CS033513000180 安斎 遥 1 女性 1962-07-11 56 241-0823 神奈川県横浜市旭区善部町********** S14033 20150728 6-20080506-5 CS033513000180
|
最後にdf_customer_nにおけるcustomer_idとintegration_idの数の差分を確認します。
uniqueメソッドの復習は、【解説】手を動かしながらカウント(len関数、uniqueメソッド)を学ぶ | データサイエンス100本ノック【問21〜問22 回答】 - omathin blog
1
| print('ID数の差：', len(df_customer_n['customer_id'].unique()) - len(df_customer_n['integration_id'].unique()))
|
P-閑話: df_customer_1, df_customer_nは使わないので削除する。
1
2
| del df_customer_1
del df_customer_n
|
まとめ: 名寄せについて学びました。
本記事で紹介した方法を元にデータサイエンティストとしての知見を深めていただければと思います。
データサイエンティストに必要な知識は、TechAcademyのデータサイエンスコースでの学習がおすすめです。
無料体験可能なのでご確認ください。
