Combining Data

Combining Data

  • Combining data is a common task in data analysis.
  • You must add data from multiple sources to answer questions.

Why?

  • New Insights.
  • More Complete Picture.
  • Data in the real world rarely comes in a single table.

What are Joins?

  • Joins two tables together.

Keys

  • Primary key
    • Unique identifier for each row in a table.
  • Foreign key
    • A column in one table that is a primary key in another table.

Examples

ID Name AgeId
1 Bob 1
2 Sue 2
3 Joe 3
ID Age Number
1 25 10
2 30 20
3 35 30

Joined Table

ID Name AgeId Age Number
1 Bob 1 25 10
2 Sue 2 30 20
3 Joe 3 35 30

Types of Joins

  • Inner Join
    • only rows that have a match in both tables.
  • Left Join
    • all rows from the left table and the matched rows from the right table.
  • Right Join
    • all rows from the right table and the matched rows from the left table.
  • Outer Join
    • all rows from both tables.

Example: Inner Join

ID Name AgeId
1 Bob 1
2 Sue 2
3 Joe 3
4 Sam 5
5 Ann 7
ID Age Number
1 25 10
2 30 20
3 35 30
4 40 40
5 45 50
6 50 60 
import pandas as pd
df1 = pd.DataFrame({'ID': [1, 2, 3, 4, 5],
                    'Name': ['Bob', 'Sue', 'Joe', 'Sam', 'Ann'],
                    'AgeId': [1, 2, 3, 5, 7]})
df2 = pd.DataFrame({'ID': [1, 2, 3, 4, 5, 6],
                    'Age': [25, 30, 35, 40, 45, 50],
                    'Number': [10, 20, 30, 40, 50, 60]})
df1.merge(df2, left_on='AgeId', right_on='ID', how='inner')
ID_x Name AgeId ID_y Age Number
0 1 Bob 1 1 25 10
1 2 Sue 2 2 30 20
2 3 Joe 3 3 35 30
3 4 Sam 5 5 45 50

Example: Left Join

ID Name AgeId
1 Bob 1
2 Sue 2
3 Joe 3
4 Sam 5
5 Ann 7
ID Age Number
1 25 10
2 30 20
3 35 30
4 40 40
5 45 50 
import pandas as pd
df1 = pd.DataFrame({'ID': [1, 2, 3, 4, 5],
                    'Name': ['Bob', 'Sue', 'Joe', 'Sam', 'Ann'],
                    'AgeId': [1, 2, 3, 5, 7]})
df2 = pd.DataFrame({'ID': [1, 2, 3, 4, 5, 6],
                    'Age': [25, 30, 35, 40, 45, 50],
                    'Number': [10, 20, 30, 40, 50, 60]})
df1.merge(df2, left_on='AgeId', right_on='ID', how='left')
ID_x Name AgeId ID_y Age Number
0 1 Bob 1 1.0 25.0 10.0
1 2 Sue 2 2.0 30.0 20.0
2 3 Joe 3 3.0 35.0 30.0
3 4 Sam 5 5.0 45.0 50.0
4 5 Ann 7 NaN NaN NaN

Example: Right Join

ID Name AgeId
1 Bob 1
3 Joe 3
4 Sam 5
5 Ann 7
ID Age Number
1 25 10
3 35 30
4 40 40
5 45 50
6 50 60 
import pandas as pd
df1 = pd.DataFrame({'ID': [1, 3, 4, 5],
                    'Name': ['Bob', 'Joe', 'Sam', 'Ann'],
                    'AgeId': [1, 3, 5, 7]})
df2 = pd.DataFrame({'ID': [1, 3, 4, 5, 6],
                    'Age': [25, 35, 40, 45, 50],
                    'Number': [10, 30, 40, 50, 60]})
df1.merge(df2, left_on='AgeId', right_on='ID', how='right')
ID_x Name AgeId ID_y Age Number
0 1.0 Bob 1.0 1 25 10
1 3.0 Joe 3.0 3 35 30
2 NaN NaN NaN 4 40 40
3 4.0 Sam 5.0 5 45 50
4 NaN NaN NaN 6 50 60

Example: Outer Join

ID Name AgeId
1 Bob 1
4 Sam 5
5 Ann 7
ID Age Number
1 25 10
3 35 30
4 40 40
5 45 50
6 50 60 
import pandas as pd
df1 = pd.DataFrame({'ID': [1, 4, 5],
                    'Name': ['Bob', 'Sam', 'Ann'],
                    'AgeId': [1, 5, 7]})
df2 = pd.DataFrame({'ID': [1, 3, 4, 5, 6], 
                    'Age': [25, 35, 40, 45, 50],
                    'Number': [10, 30, 40, 50, 60]})
df1.merge(df2, left_on='AgeId', right_on='ID', how='outer')
ID_x Name AgeId ID_y Age Number
0 1.0 Bob 1.0 1.0 25.0 10.0
1 NaN NaN NaN 3.0 35.0 30.0
2 NaN NaN NaN 4.0 40.0 40.0
3 4.0 Sam 5.0 5.0 45.0 50.0
4 NaN NaN NaN 6.0 50.0 60.0
5 5.0 Ann 7.0 NaN NaN NaN