Combining Datasets In Pandas

A: COMBINING DATASETS: CONCAT AND APPEND

We will start with basic examples of concatenation of Series and DataFrames objects, with the pd.concat function; later we will dive into more sophisticated in-memory merge and join implemented in Pandas.

import numpy as np
import pandas as pd 

Function to construct DataFrame

We will first define a function that will be used to make the DataFrame from letters and numbers, with fewer keystrokes and help us keep the code clean:

def make_df(columns, indices):
    data = {c: [f"{c}{i}" for i in indices]
           for c in columns}
    return pd.DataFrame(data, indices)

# understanding the components of function
data = {c: [f"{c}{i}" for i in range(3)]
           for c in 'ABC'}
data

{'A': ['A0', 'A1', 'A2'], 'B': ['B0', 'B1', 'B2'], 'C': ['C0', 'C1', 'C2']}

# example to run function
example_df = make_df('ABC', [1,2,3])
print(example_df)

    A   B   C
1  A1  B1  C1
2  A2  B2  C2
3  A3  B3  C3

1. SIMPLE CONCATENATION WITH pd.concat

Pandas has a function, pd.concat(), which has a similar syntax to np.concatenate but contains a number of options Syntax for Pandas concat function:

pd.concat(objs, paramters)

Parameter	Default
axis=	0
join=	‘outer’
join_axes=	None
ignore_index=	False
keys=	None
levels=	None
names=	None
verify_integrity=	False
copy	True

1.1. Concatenating Series

# concatenating along columns (stacking one on top of other), 
# because default value of keyword argument, index=0 
ser1 = pd.Series(['A','B','C'], index=[1,2,3])
ser2 = pd.Series(['X','Y','Z'], index=[4,5,6])

pd.concat([ser1,ser2])

1    A
2    B
3    C
4    X
5    Y
6    Z
dtype: object

1.2. Concatenating DataFrame

→ In this first example, for both DataFrame objects that we are going to concatenate, column names are the same i.e, A,B and C and indices are different. Because we are going to use index=0 default value which will concatenate along columns:

# concatenating along columns (stacking one on top of other),
# because default value of keyword argument, index=0 
df1 = make_df('ABC',[1,2,3])
df2 = make_df('ABC',[4,5,6])

print(df1)
print(df2)
print(pd.concat([df1,df2]))

    A   B   C
1  A1  B1  C1
2  A2  B2  C2
3  A3  B3  C3
    A   B   C
4  A4  B4  C4
5  A5  B5  C5
6  A6  B6  C6
    A   B   C
1  A1  B1  C1
2  A2  B2  C2
3  A3  B3  C3
4  A4  B4  C4
5  A5  B5  C5
6  A6  B6  C6

→ In this second example, for both DataFrame objects that we are going to concatenate, column names are the different and indices are same (1,2). Because we are going to use index=1 which will concatenate along rows:

# concatenating along rows
# keyword argument, axis=1
df3 = make_df(['A','B'], [1,2])
df4 = make_df(['C','D'], [1,2])

print(df3); print(df4); print(pd.concat([df3,df4], axis=1))

    A   B
1  A1  B1
2  A2  B2
    C   D
1  C1  D1
2  C2  D2
    A   B   C   D
1  A1  B1  C1  D1
2  A2  B2  C2  D2

1.3. Duplicate Indices

One important difference between np.concatenate and pd.concat is that Pandas concatenation preserves indices, even if the result will have duplicate indices

# making two series with same index
sr3 = pd.Series(['A','B'], index=[1,2])
sr4 = pd.Series(['A','B'], index=[1,2])

pd.concat([sr3,sr4])

1    A
2    B
1    A
2    B
dtype: object

pd.concat() gives us a few ways to handle the repeated indices issue

a) Handling duplicate indices through varify_integrity

varify_integrity=True checks whether the new concatenated axis contains duplicates. If yes, it will raise the ValueEror

pd.concat([sr3,sr4], verify_integrity=True)

ValueError: Indexes have overlapping values: Int64Index([1, 2], dtype='int64')

b) Using keys Argument

We can use keys=[] kwarg to form Multi-index Series or DataFrame

pd.concat([sr3,sr4], keys=['y','z'])

y  1    A
   2    B
z  1    A
   2    B
dtype: object

1.4. Concatenation with “Join”

In the examples above, we discussed cases of concatenating DataFrames with shared column names. In practice, data from different sources might have different sets of column names, and pd.concat offers several options to handle this.

# look what happens when we concat the two df with not identical columns
df5 = make_df('ABC',[1,2]) 
df6 = make_df('BCD', [3,4])

pd.concat([df5,df6], sort=False) 
#remember the default value of kwarg, join=outer

     A   B   C    D
1   A1  B1  C1  NaN
2   A2  B2  C2  NaN
3  NaN  B3  C3   D3
4  NaN  B4  C4   D4

join='inner': joins intersection of columns in the DataFrames

print(pd.concat([df5,df6], join='inner'))

    B   C
1  B1  C1
2  B2  C2
3  B3  C3
4  B4  C4

1.5. The append() method

Series and DataFrame objects have an .append() method that can accomplish the same behaviour like .concat() but in fewer keystrokes Unlike the append() and extend() methods of Python lists, the append() method in Pandas does not modify the original object

print(df1.append(df2))

    A   B   C
1  A1  B1  C1
2  A2  B2  C2
3  A3  B3  C3
4  A4  B4  C4
5  A5  B5  C5
6  A6  B6  C6

B: COMBINING DATASETS: MERGE AND JOIN

We will very briefly discuss pd.join, but the discussion is mainly focused on pd.merge function

A note on Relational Algebra

pd.merge() works in a manner that is considered to be a subset of what is known as relational algebra — formal set of rules for manipulating relational data. Pandas implements several of these fundamental building blocks in the pd.merge() function and the related join() method of Series and DataFrames.

1. CATEGORIES OF MERGE

The pd.merge() function implements a number of types of joins: the one-to-one, many-to-one, and many-to-many joins — the type of join performed depends on the form of the input data

1.1. One-to-one Merge

# creating two DataFrames with one identical column name
df11 = pd.DataFrame({'employee': ['Bob', 'Jake', 'Lisa', 'Sue'],
                      'group': ['Accounting', 'Engineering', 'Engineering', 'HR']})
                    
df12 = pd.DataFrame({'employee': ['Lisa', 'Bob', 'Jake', 'Sue'],
                     'hire_date': [2004, 2008, 2012, 2014]})

print(df11)
print(df12)

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

When we merge these two DataFrame object, df11 and df12, the pd.merge() function recognizes that each DataFrame has a common employeecolumn, and automatically joins using this column as a key

df13 = pd.merge(df11,df12)
print(df13)

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

1.2. Many-to-one Merge

It is type of Join in which one of the two matching key columns contains duplicate entries. For the many-to-one case, the resulting DataFrame will preserve those duplicate entries as appropriate:

df14 = pd.DataFrame({'group': ['Accounting', 'Engineering', 'HR'],
                     'supervisor': ['Carly', 'Guido', 'Steve']})
print(df13)
print(df14)

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

When we merge these two DataFrame object, df13 and df14, the pd.merge() function recognizes the common column group and automatically joins using this column as a key. However, group column under df13 contain 4 values (3 unique), while under df14 contain 3 values (all 3 unique) In this case, the many-to-one use same supervisor value for both Engineering row

print(pd.merge(df13,df14))

  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

1.3. Many-to-many Merge

If the key column in both the left and right DataFrame contains duplicates, then the result is a many-to-many merge. Example makes understanding it easier:

df15 = pd.DataFrame({'group': ['Accounting', 'Accounting', 'Engineering', 'Engineering', 'HR', 'HR'],
                     'skills': ['math', 'spreadsheets', 'coding', 'linux', 'spreadsheets', 'organization']})

print(df11); print(df15)

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

When we merge these two DataFrame objects, df11 and df15, the pd.merge() function recognizes the common column group and automatically joins using this column as a key. However, there are duplicates values under group for both DataFrame object so the join would be many-to-many join. Bob is related to accounting group but accounting has two skills, so both will have their own row. Same logic goes for Jake, Lisa under engineering

print(pd.merge(df11,df15))

  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

2. SPECIFICATION OF MERGE KEY

Often the column names will not match so nicely as we have seen in above examples, but pd.merge() provides a variety of options to handle this scenario and explicitly tell to merge which column/key.

2.1. The on Keyword Argument

Explicitly specify the name of the key column using the on= keyword argument, which takes a column name or a list of column names

print(pd.merge(df11,df12, on='employee'))

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

2.2. The left_on and right_on Keyword Argument

For example, we may have a dataset in which the employee name is labeled as “name” rather than “employee” but the values are same inside i.e, just the column label is different. In such case, we can specify which column to merge left_on= and which to merge on right_on= Example will make it clearer:

df16 = pd.DataFrame({'name': ['Bob', 'Jake', 'Lisa', 'Sue'],
                     'salary': [70000, 80000, 120000, 90000]})
print(df16)

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

df17 = pd.merge(df11,df16,  left_on='employee', right_on='name')
print(df17)

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

2.3. drop method

What to do if we want to delete a specified label from an index or column? use .drop() method. There are two ways to implement this:

• Specify the label names and axis (0 if along row, 1 if along column)

• Directly specify the columns or index name to remove the column or row, respectively.

This is the example of the first case — specifying the label name and axis:

print(df17.drop('name', axis=1))

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

2.4. The left_index and right_index

Sometimes, rather than merging on a column, we would instead like to merge on an index. left_index= and right_index= keyword arguments, takes in boolean value of True or False

.set_index() method will set the provided column as the index of a DataFrame

# first let set_index of df11 and df12 so we can merge on rows
df11a = df11.set_index('employee')
df12a = df12.set_index('employee')

print(df11a)
print(df12a)

                group
employee             
Bob        Accounting
Jake      Engineering
Lisa      Engineering
Sue                HR
          hire_date
employee           
Lisa           2004
Bob            2008
Jake           2012
Sue            2014

print(pd.merge(df11a, df12a, right_index=True, left_index=True))

                group  hire_date
employee                        
Bob        Accounting       2008
Jake      Engineering       2012
Lisa      Engineering       2004
Sue                HR       2014

2.5. Join method

To join by indices we can also use .join method, which by-default merges by indices. Here is an example:

print(df11a.join(df12a))

                group  hire_date
employee                        
Bob        Accounting       2008
Jake      Engineering       2012
Lisa      Engineering       2004
Sue                HR       2014

2.6. Mix Merge with Indices and Columns

print(df11a)
print(df16)

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

We would like to merge DataFrame df11a and df16 However, the common key is index, employee from df11a and column, name from df16 Therefore, we will use left_index=True for df11a and right_on='name' for df16

print(pd.merge(df11a, df16, 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

3. SPECIFYING how TO MERGE

The how keyword argument defines the type of merge to perform, which takes one of these four values:

• how=inner which is default value and keeps the intersection of keys. It preserve the keys order of the DataFrame mentioned on the left

• how=outer keeps the union of the keys

• how=left keeps the keys of DataFrame mentioned on the left side

• how=right keeps the keys of DataFrame mentioned on the right side

# starting by defining dataframes

df18 = pd.DataFrame({'name': ['Parker', 'Piper', 'Mia'],
                    'food': ['chicken', 'rice', 'bread']}, columns=['name', 'food'])

df19 = pd.DataFrame({'name': ['Mia', 'Justin'], 
                     'drink': ['soda', 'water']}, columns=['name', 'drink'])

print(df18)
print(df19)

    name      food
0  Parker   chicken
1  Piper    rice
2  Mia      bread
    name     drink
0  Mia      soda
1 Justin    water

3.1. how='inner'

print(pd.merge(df18, df19))

   name   food drink
0  Mia  bread  soda

Let change the order of frames in the pd.merge()

print(pd.merge(df19, df18))

   name drink   food
0  Mia  soda  bread

3.2. how='outer'

print(pd.merge(df18,df19, how='outer'))

     name    food     drink
0  Parker   chicken   NaN
1  Piper    rice      NaN
2  Mia      bread     soda
3  Justin   NaN       water

3.3. how=‘left’

print(pd.merge(df18,df19, how='left'))

    name   food       drink
0  Parker   chicken   NaN
1  Piper    rice      NaN
2  Mia      bread     soda

3.4. how=‘right’

print(pd.merge(df18,df19, how='right'))

   name      food   drink
0  Mia       bread  soda
1  Justin    NaN    water

4. OVERLAPPING COLUMN NAMES

We may end up in a situation where our two input DataFrames have two or more same labeled column. We can use on= to specify column name to merge on.

df20 = pd.DataFrame({'name': ['A','B','C'],
                    'rank': [1,2,3]})
df21 = pd.DataFrame({'name':['C','A','B'],
                    'rank':[1,2,3]})

print(pd.merge(df20,df21, on='name'))

  name  rank_x  rank_y
0    A       1       2
1    B       2       3
2    C       3       1

Instead of appending the column names with _x and _y we can give our own keywords, using argument suffixes=[]

print(pd.merge(df20,df21, on='name', suffixes=['_bio','_stats']))

  name  rank_bio  rank_stats
0    A         1           2
1    B         2           3
2    C         3           1