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

	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

	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

	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

	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