Inner join

Data samenvoegen met pandas

Aaren Stubberfield

Instructor

Voor de duidelijkheid

Bril die naar een oogtestkaart kijkt

Tabellen = DataFrames

Mergen = Joins

¹ Foto door David Travis op Unsplash

Dataset uit het Chicago Data Portal

Skyline van Chicago

¹ Foto door Pedro Lastra op Unsplash

Datasets voor het voorbeeld

Kaart van Chicago-‘wards’

¹ Wijkafbeelding door Alissapump, eigen werk, CC BY-SA 3.0

De ward-data

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)

Censusdata

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)

Tabellen samenvoegen

  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

Inner join

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 van inner join

Achtervoegsels

print(wards_census.columns)

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

Achtervoegsels

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)

Laten we oefenen!

Data samenvoegen met pandas