内部結合

pandas によるデータの結合

Aaren Stubberfield

Instructor

わかりやすくするため

視力検査表を見ている眼鏡

Tables = DataFrames

マージ = 結合

¹ UnsplashのDavid Travisによる写真

Chicagoデータポータルのデータセット

シカゴのスカイライン画像

¹ UnsplashのPedro Lastraによる写真

例としてのデータセット

シカゴ区の地図

¹ Ward画像 By Alissapump, Own work, CC BY-SA 3.0

病棟データ

wards = pd.read_csv('Ward_Offices.csv')
print(wards.head())
print(wards.shape)

  ward  alderman         address          zip
0 1     Proco "Joe" ...  2058 NORTH W...  60647
1 2     Brian Hopkins    1400 NORTH  ...  60622
2 3     Pat Dowell       5046 SOUTH S...  60609
3 4     William D. B...  435 EAST 35T...  60616
4 5     Leslie A. Ha...  2325 EAST 71...  60649
(50, 4)

国勢調査データ

census = pd.read_csv('Ward_Census.csv')
print(census.head())
print(census.shape)

  ward  pop_2000  pop_2010  change  address          zip
0 1     52951     56149     6%      2765 WEST SA...  60647
1 2     54361     55805     3%      WM WASTE MAN...  60622
2 3     40385     53039     31%     17 EAST 38TH...  60653
3 4     51953     54589     5%      31ST ST HARB...  60653
4 5     55302     51455     -7%     JACKSON PARK...  60637
(50, 6)

テーブルの結合

  ward  alderman         address          zip
0 1     Proco "Joe" ...  2058 NORTH W...  60647
1 2     Brian Hopkins    1400 NORTH  ...  60622
2 3     Pat Dowell       5046 SOUTH S...  60609
3 4     William D. B...  435 EAST 35T...  60616
4 5     Leslie A. Ha...  2325 EAST 71...  60649

  ward  pop_2000  pop_2010  change  address          zip
0 1     52951     56149     6%      2765 WEST SA...  60647
1 2     54361     55805     3%      WM WASTE MAN...  60622
2 3     40385     53039     31%     17 EAST 38TH...  60653
3 4     51953     54589     5%      31ST ST HARB...  60653
4 5     55302     51455     -7%     JACKSON PARK...  60637

内部結合

wards_census = wards.merge(census, on='ward')
print(wards_census.head(4))

  ward  alderman         address_x        zip_x  pop_2000  pop_2010  change  address_y        zip_y
0 1     Proco "Joe" ...  2058 NORTH W...  60647  52951     56149     6%      2765 WEST SA...  60647
1 2     Brian Hopkins    1400 NORTH  ...  60622  54361     55805     3%      WM WASTE MAN...  60622
2 3     Pat Dowell       5046 SOUTH S...  60609  40385     53039     31%     17 EAST 38TH...  60653
3 4     William D. B...  435 EAST 35T...  60616  51953     54589     5%      31ST ST HARB...  60653

print(wards_census.shape)

(50, 9)

内部結合

Inner join venn diagram

接尾辞

print(wards_census.columns)

Index(['ward', 'alderman', 'address_x', 'zip_x', 'pop_2000', 'pop_2010', 'change',
       'address_y', 'zip_y'],
      dtype='object')

接尾辞

wards_census = wards.merge(census, on='ward', suffixes=('_ward','_cen'))
print(wards_census.head())
print(wards_census.shape)

  ward  alderman         address_ward     zip_ward  pop_2000  pop_2010  change  address_cen      zip_cen
0 1     Proco "Joe" ...  2058 NORTH W...  60647     52951     56149     6%      2765 WEST SA...  60647   
1 2     Brian Hopkins    1400 NORTH  ...  60622     54361     55805     3%      WM WASTE MAN...  60622   
2 3     Pat Dowell       5046 SOUTH S...  60609     40385     53039     31%     17 EAST 38TH...  60653   
3 4     William D. B...  435 EAST 35T...  60616     51953     54589     5%      31ST ST HARB...  60653   
4 5     Leslie A. Ha...  2325 EAST 71...  60649     55302     51455     -7%     JACKSON PARK...  60637  
(50, 9)

練習しましょう！

pandas によるデータの結合