TallerPythonIntroCienciaDatos

This is an excerpt from the Python Data Science Handbook by Jake VanderPlas; Jupyter notebooks are available on GitHub.

The text is released under the CC-BY-NC-ND license, and code is released under the MIT license. If you find this content useful, please consider supporting the work by buying the book!

Combinando datos: *merge* y *join*

< Combinando datos: concatenación y anexión | Contenido | Agregaciones y agrupaciones >

Open in Colab

In [1]:
import pandas as pd
import numpy as np

class display(object):
    """Display HTML representation of multiple objects"""
    template = """<div style="float: left; padding: 10px;">
    <p style='font-family:"Courier New", Courier, monospace'>{0}</p>{1}
    </div>"""
    def __init__(self, *args):
        self.args = args
        
    def _repr_html_(self):
        return '\n'.join(self.template.format(a, eval(a)._repr_html_())
                         for a in self.args)
    
    def __repr__(self):
        return '\n\n'.join(a + '\n' + repr(eval(a))
                           for a in self.args)

Algebra Relacional

Categorias de uniones

Uniones uno-a-uno (one-to-one)

In [2]:
df1 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
df2 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                    'hire_date': [2004, 2008, 2012, 2014]})
display('df1', 'df2')
Out[2]:

df1

employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
3 Sue HR

df2

employee hire_date
0 Lisa 2004
1 Bob 2008
2 Jake 2012
3 Sue 2014
In [3]:
df3 = pd.merge(df1, df2)
df3
Out[3]:
employee group hire_date
0 Bob Accounting 2008
1 Jake Engineering 2012
2 Lisa Engineering 2004
3 Sue HR 2014

Uniones muchas-a-uno (many-to-one)

In [4]:
df4 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
                    'supervisor': ['Carly', 'Guido', 'Steve']})
display('df3', 'df4', 'pd.merge(df3, df4)')
Out[4]:

df3

employee group hire_date
0 Bob Accounting 2008
1 Jake Engineering 2012
2 Lisa Engineering 2004
3 Sue HR 2014

df4

group supervisor
0 Accounting Carly
1 Engineering Guido
2 HR Steve

pd.merge(df3, df4)

employee group hire_date supervisor
0 Bob Accounting 2008 Carly
1 Jake Engineering 2012 Guido
2 Lisa Engineering 2004 Guido
3 Sue HR 2014 Steve

Uniones muchas-a-muchas (many-to-many)

In [5]:
df5 = pd.DataFrame({'group': ['Accounting', 'Accounting',
                              'Engineering', 'Engineering', 'HR', 'HR'],
                    'skills': ['math', 'spreadsheets', 'coding', 'linux',
                               'spreadsheets', 'organization']})
display('df1', 'df5', "pd.merge(df1, df5)")
Out[5]:

df1

employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
3 Sue HR

df5

group skills
0 Accounting math
1 Accounting spreadsheets
2 Engineering coding
3 Engineering linux
4 HR spreadsheets
5 HR organization

pd.merge(df1, df5)

employee group skills
0 Bob Accounting math
1 Bob Accounting spreadsheets
2 Jake Engineering coding
3 Jake Engineering linux
4 Lisa Engineering coding
5 Lisa Engineering linux
6 Sue HR spreadsheets
7 Sue HR organization

Especificando la llave de unión en merge

La palabra reservada on

In [6]:
display('df1', 'df2', "pd.merge(df1, df2, on='employee')")
Out[6]:

df1

employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
3 Sue HR

df2

employee hire_date
0 Lisa 2004
1 Bob 2008
2 Jake 2012
3 Sue 2014

pd.merge(df1, df2, on='employee')

employee group hire_date
0 Bob Accounting 2008
1 Jake Engineering 2012
2 Lisa Engineering 2004
3 Sue HR 2014

Las palabras reservadas left_on y right_on

In [7]:
df3 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'salary': [70000, 80000, 120000, 90000]})
display('df1', 'df3', 'pd.merge(df1, df3, left_on="employee", right_on="name")')
Out[7]:

df1

employee group
0 Bob Accounting
1 Jake Engineering
2 Lisa Engineering
3 Sue HR

df3

name salary
0 Bob 70000
1 Jake 80000
2 Lisa 120000
3 Sue 90000

pd.merge(df1, df3, left_on="employee", right_on="name")

employee group name salary
0 Bob Accounting Bob 70000
1 Jake Engineering Jake 80000
2 Lisa Engineering Lisa 120000
3 Sue HR Sue 90000
In [8]:
pd.merge(df1, df3, left_on="employee", right_on="name").drop('name', axis=1)
Out[8]:
employee group salary
0 Bob Accounting 70000
1 Jake Engineering 80000
2 Lisa Engineering 120000
3 Sue HR 90000

Las palabras reservadas left_index y right_index

In [9]:
df1a = df1.set_index('employee')
df2a = df2.set_index('employee')
display('df1a', 'df2a')
Out[9]:

df1a

group
employee
Bob Accounting
Jake Engineering
Lisa Engineering
Sue HR

df2a

hire_date
employee
Lisa 2004
Bob 2008
Jake 2012
Sue 2014
In [10]:
display('df1a', 'df2a',
        "pd.merge(df1a, df2a, left_index=True, right_index=True)")
Out[10]:

df1a

group
employee
Bob Accounting
Jake Engineering
Lisa Engineering
Sue HR

df2a

hire_date
employee
Lisa 2004
Bob 2008
Jake 2012
Sue 2014

pd.merge(df1a, df2a, left_index=True, right_index=True)

group hire_date
employee
Lisa Engineering 2004
Bob Accounting 2008
Jake Engineering 2012
Sue HR 2014
In [11]:
display('df1a', 'df2a', 'df1a.join(df2a)')
Out[11]:

df1a

group
employee
Bob Accounting
Jake Engineering
Lisa Engineering
Sue HR

df2a

hire_date
employee
Lisa 2004
Bob 2008
Jake 2012
Sue 2014

df1a.join(df2a)

group hire_date
employee
Bob Accounting 2008
Jake Engineering 2012
Lisa Engineering 2004
Sue HR 2014
In [12]:
display('df1a', 'df3', "pd.merge(df1a, df3, left_index=True, right_on='name')")
Out[12]:

df1a

group
employee
Bob Accounting
Jake Engineering
Lisa Engineering
Sue HR

df3

name salary
0 Bob 70000
1 Jake 80000
2 Lisa 120000
3 Sue 90000

pd.merge(df1a, df3, left_index=True, right_on='name')

group name salary
0 Accounting Bob 70000
1 Engineering Jake 80000
2 Engineering Lisa 120000
3 HR Sue 90000

Aritmética de conjuntos para uniones

In [13]:
df6 = pd.DataFrame({'name': ['Peter', 'Paul', 'Mary'],
                    'food': ['fish', 'beans', 'bread']},
                   columns=['name', 'food'])
df7 = pd.DataFrame({'name': ['Mary', 'Joseph'],
                    'drink': ['wine', 'beer']},
                   columns=['name', 'drink'])
display('df6', 'df7', 'pd.merge(df6, df7)')
Out[13]:

df6

name food
0 Peter fish
1 Paul beans
2 Mary bread

df7

name drink
0 Mary wine
1 Joseph beer

pd.merge(df6, df7)

name food drink
0 Mary bread wine
In [14]:
pd.merge(df6, df7, how='inner')
Out[14]:
name food drink
0 Mary bread wine
In [15]:
display('df6', 'df7', "pd.merge(df6, df7, how='outer')")
Out[15]:

df6

name food
0 Peter fish
1 Paul beans
2 Mary bread

df7

name drink
0 Mary wine
1 Joseph beer

pd.merge(df6, df7, how='outer')

name food drink
0 Peter fish NaN
1 Paul beans NaN
2 Mary bread wine
3 Joseph NaN beer
In [16]:
display('df6', 'df7', "pd.merge(df6, df7, how='left')")
Out[16]:

df6

name food
0 Peter fish
1 Paul beans
2 Mary bread

df7

name drink
0 Mary wine
1 Joseph beer

pd.merge(df6, df7, how='left')

name food drink
0 Peter fish NaN
1 Paul beans NaN
2 Mary bread wine

Nombres de columnas traslapadas: la plabra reservada suffixes

In [17]:
df8 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [1, 2, 3, 4]})
df9 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                    'rank': [3, 1, 4, 2]})
display('df8', 'df9', 'pd.merge(df8, df9, on="name")')
Out[17]:

df8

name rank
0 Bob 1
1 Jake 2
2 Lisa 3
3 Sue 4

df9

name rank
0 Bob 3
1 Jake 1
2 Lisa 4
3 Sue 2

pd.merge(df8, df9, on="name")

name rank_x rank_y
0 Bob 1 3
1 Jake 2 1
2 Lisa 3 4
3 Sue 4 2
In [18]:
display('df8', 'df9', 'pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])')
Out[18]:

df8

name rank
0 Bob 1
1 Jake 2
2 Lisa 3
3 Sue 4

df9

name rank
0 Bob 3
1 Jake 1
2 Lisa 4
3 Sue 2

pd.merge(df8, df9, on="name", suffixes=["_L", "_R"])

name rank_L rank_R
0 Bob 1 3
1 Jake 2 1
2 Lisa 3 4
3 Sue 4 2

Ejemplo: datos sobre los EU

In [19]:
# Following are shell commands to download the data
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-population.csv
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-areas.csv
# !curl -O https://raw.githubusercontent.com/jakevdp/data-USstates/master/state-abbrevs.csv
In [20]:
pop = pd.read_csv('data/state-population.csv')
areas = pd.read_csv('data/state-areas.csv')
abbrevs = pd.read_csv('data/state-abbrevs.csv')

display('pop.head()', 'areas.head()', 'abbrevs.head()')
Out[20]:

pop.head()

state/region ages year population
0 AL under18 2012 1117489.0
1 AL total 2012 4817528.0
2 AL under18 2010 1130966.0
3 AL total 2010 4785570.0
4 AL under18 2011 1125763.0

areas.head()

state area (sq. mi)
0 Alabama 52423
1 Alaska 656425
2 Arizona 114006
3 Arkansas 53182
4 California 163707

abbrevs.head()

state abbreviation
0 Alabama AL
1 Alaska AK
2 Arizona AZ
3 Arkansas AR
4 California CA
In [21]:
merged = pd.merge(pop, abbrevs, how='outer',
                  left_on='state/region', right_on='abbreviation')
merged = merged.drop('abbreviation', 1) # drop duplicate info
merged.head()
Out[21]:
state/region ages year population state
0 AL under18 2012 1117489.0 Alabama
1 AL total 2012 4817528.0 Alabama
2 AL under18 2010 1130966.0 Alabama
3 AL total 2010 4785570.0 Alabama
4 AL under18 2011 1125763.0 Alabama
In [22]:
merged.isnull().any()
Out[22]:
state/region    False
ages            False
year            False
population       True
state            True
dtype: bool
In [23]:
merged[merged['population'].isnull()].head()
Out[23]:
state/region ages year population state
2448 PR under18 1990 NaN NaN
2449 PR total 1990 NaN NaN
2450 PR total 1991 NaN NaN
2451 PR under18 1991 NaN NaN
2452 PR total 1993 NaN NaN
In [24]:
merged.loc[merged['state'].isnull(), 'state/region'].unique()
Out[24]:
array(['PR', 'USA'], dtype=object)
In [25]:
merged.loc[merged['state/region'] == 'PR', 'state'] = 'Puerto Rico'
merged.loc[merged['state/region'] == 'USA', 'state'] = 'United States'
merged.isnull().any()
Out[25]:
state/region    False
ages            False
year            False
population       True
state           False
dtype: bool
In [26]:
final = pd.merge(merged, areas, on='state', how='left')
final.head()
Out[26]:
state/region ages year population state area (sq. mi)
0 AL under18 2012 1117489.0 Alabama 52423.0
1 AL total 2012 4817528.0 Alabama 52423.0
2 AL under18 2010 1130966.0 Alabama 52423.0
3 AL total 2010 4785570.0 Alabama 52423.0
4 AL under18 2011 1125763.0 Alabama 52423.0
In [27]:
final.isnull().any()
Out[27]:
state/region     False
ages             False
year             False
population        True
state            False
area (sq. mi)     True
dtype: bool
In [28]:
final['state'][final['area (sq. mi)'].isnull()].unique()
Out[28]:
array(['United States'], dtype=object)
In [29]:
final.dropna(inplace=True)
final.head()
Out[29]:
state/region ages year population state area (sq. mi)
0 AL under18 2012 1117489.0 Alabama 52423.0
1 AL total 2012 4817528.0 Alabama 52423.0
2 AL under18 2010 1130966.0 Alabama 52423.0
3 AL total 2010 4785570.0 Alabama 52423.0
4 AL under18 2011 1125763.0 Alabama 52423.0
In [30]:
data2010 = final.query("year == 2010 & ages == 'total'")
data2010.head()
Out[30]:
state/region ages year population state area (sq. mi)
3 AL total 2010 4785570.0 Alabama 52423.0
91 AK total 2010 713868.0 Alaska 656425.0
101 AZ total 2010 6408790.0 Arizona 114006.0
189 AR total 2010 2922280.0 Arkansas 53182.0
197 CA total 2010 37333601.0 California 163707.0
In [31]:
data2010.set_index('state', inplace=True)
density = data2010['population'] / data2010['area (sq. mi)']
In [32]:
density.sort_values(ascending=False, inplace=True)
density.head()
Out[32]:
state
District of Columbia    8898.897059
Puerto Rico             1058.665149
New Jersey              1009.253268
Rhode Island             681.339159
Connecticut              645.600649
dtype: float64
In [33]:
density.tail()
Out[33]:
state
South Dakota    10.583512
North Dakota     9.537565
Montana          6.736171
Wyoming          5.768079
Alaska           1.087509
dtype: float64

< Combinando datos: concatenación y anexión | Contenido | Agregaciones y agrupaciones >

Open in Colab