8  Combining DataFrames

Previously, we have seen how to analyze and manipulate data in a single DataFrame. However, you will often find data saved into different files and, therefore, you may need to deal with several different pandas DataFrames.
In this session we will explore different ways of combining DataFrames into a single DataFrame.

Let’s start loading the pandas library, reading two data sets into DataFrames, and having a quick look at the tabular data: surveys.csv and species.csv

import pandas as pd
surveys_df = pd.read_csv("../course_materials/data/surveys.csv", keep_default_na=False, na_values=[""])
species_df = pd.read_csv("../course_materials/data/species.csv", keep_default_na=False, na_values=[""])
print(surveys_df.info())
surveys_df.head()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 35549 entries, 0 to 35548
Data columns (total 9 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   record_id        35549 non-null  int64  
 1   month            35549 non-null  int64  
 2   day              35549 non-null  int64  
 3   year             35549 non-null  int64  
 4   plot_id          35549 non-null  int64  
 5   species_id       34786 non-null  object 
 6   sex              33038 non-null  object 
 7   hindfoot_length  31438 non-null  float64
 8   weight           32283 non-null  float64
dtypes: float64(2), int64(5), object(2)
memory usage: 2.4+ MB
None
record_id month day year plot_id species_id sex hindfoot_length weight
0 1 7 16 1977 2 NL M 32.0 NaN
1 2 7 16 1977 3 NL M 33.0 NaN
2 3 7 16 1977 2 DM F 37.0 NaN
3 4 7 16 1977 7 DM M 36.0 NaN
4 5 7 16 1977 3 DM M 35.0 NaN
print(species_df.info())
species_df.head()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 54 entries, 0 to 53
Data columns (total 4 columns):
 #   Column      Non-Null Count  Dtype 
---  ------      --------------  ----- 
 0   species_id  54 non-null     object
 1   genus       54 non-null     object
 2   species     54 non-null     object
 3   taxa        54 non-null     object
dtypes: object(4)
memory usage: 1.8+ KB
None
species_id genus species taxa
0 AB Amphispiza bilineata Bird
1 AH Ammospermophilus harrisi Rodent
2 AS Ammodramus savannarum Bird
3 BA Baiomys taylori Rodent
4 CB Campylorhynchus brunneicapillus Bird

We now have two DataFrames. The first, surveys_df, contains information on individuals of a species recorded in a survey, while the second, species_df, contains more detailed information on each species.

8.1 Concatenating DataFrames

The first way we will combine DataFrames is concatenation, i.e. simply putting DataFrames one after the other either vertically or horizontally.

Concatenation can be used if the DataFrames are similar, meaning that they either have the same rows or columns. We will see examples of this later.

To concatenate two DataFrames you will use the function pd.concat(), specifying as arguments the DataFrames to concatenate and axis=0 or axis=1 for vertical or horizontal concatenation, respectively.

We will only be looking at vertical concatenation, but it is good to be aware that there is more to be discovered beyond what we describe here.

Let us first obtain two small DataFrames from the larger surveys_df dataset.

# Subsetting DataFrames
surveys_df_sub_first10 = surveys_df.head(10)
surveys_df_sub_last10  = surveys_df.tail(10)

We now have two DataFrames, one with the first ten rows of the original dataset, and another with the last ten rows.

Now, let us do some vertical concatenation or stacking. In this case the two DataFrames are simply stacked ‘on top of’ each other (remember to specify axis=0).

Vertical stacking can be understood as combining two DataFrames that have different sets of the same type of data. In our example, it may be that one field researcher has registered the first ten entries, and another did the last ten, both using the same laboratory sheets. They both wrote down the same information (weight, species, and so on) of all different individuals. If we combine them, we have one list of twenty records, rather than two lists of ten.

# Stack the DataFrames on top of each other
vertical_stack = pd.concat([surveys_df_sub_first10, surveys_df_sub_last10], axis=0)
print(vertical_stack.info())
vertical_stack
<class 'pandas.core.frame.DataFrame'>
Int64Index: 20 entries, 0 to 35548
Data columns (total 9 columns):
 #   Column           Non-Null Count  Dtype  
---  ------           --------------  -----  
 0   record_id        20 non-null     int64  
 1   month            20 non-null     int64  
 2   day              20 non-null     int64  
 3   year             20 non-null     int64  
 4   plot_id          20 non-null     int64  
 5   species_id       19 non-null     object 
 6   sex              16 non-null     object 
 7   hindfoot_length  15 non-null     float64
 8   weight           6 non-null      float64
dtypes: float64(2), int64(5), object(2)
memory usage: 1.6+ KB
None
record_id month day year plot_id species_id sex hindfoot_length weight
0 1 7 16 1977 2 NL M 32.0 NaN
1 2 7 16 1977 3 NL M 33.0 NaN
2 3 7 16 1977 2 DM F 37.0 NaN
3 4 7 16 1977 7 DM M 36.0 NaN
4 5 7 16 1977 3 DM M 35.0 NaN
5 6 7 16 1977 1 PF M 14.0 NaN
6 7 7 16 1977 2 PE F NaN NaN
7 8 7 16 1977 1 DM M 37.0 NaN
8 9 7 16 1977 1 DM F 34.0 NaN
9 10 7 16 1977 6 PF F 20.0 NaN
35539 35540 12 31 2002 15 PB F 26.0 23.0
35540 35541 12 31 2002 15 PB F 24.0 31.0
35541 35542 12 31 2002 15 PB F 26.0 29.0
35542 35543 12 31 2002 15 PB F 27.0 34.0
35543 35544 12 31 2002 15 US NaN NaN NaN
35544 35545 12 31 2002 15 AH NaN NaN NaN
35545 35546 12 31 2002 15 AH NaN NaN NaN
35546 35547 12 31 2002 10 RM F 15.0 14.0
35547 35548 12 31 2002 7 DO M 36.0 51.0
35548 35549 12 31 2002 5 NaN NaN NaN NaN

The resulting DataFrame (vertical_stack) consists, as expected, of 20 rows. These are the result of the first and last 10 rows of our original DataFrame surveys_df.

You may have noticed that the last ten rows have very high index, not consecutive with the first ten rows. This is because concatenation preserves the indices of the two original DataFrames. If you want a brand new set of indices for your concateneted DataFrame, simply reset the indices using the method .reset_index(). Notice that this adds a column index to your DataFrame, that maintains the original index. If you pass drop=True into the function, you will avoid the addition of this column.

vertical_stack.reset_index()
index record_id month day year plot_id species_id sex hindfoot_length weight
0 0 1 7 16 1977 2 NL M 32.0 NaN
1 1 2 7 16 1977 3 NL M 33.0 NaN
2 2 3 7 16 1977 2 DM F 37.0 NaN
3 3 4 7 16 1977 7 DM M 36.0 NaN
4 4 5 7 16 1977 3 DM M 35.0 NaN
5 5 6 7 16 1977 1 PF M 14.0 NaN
6 6 7 7 16 1977 2 PE F NaN NaN
7 7 8 7 16 1977 1 DM M 37.0 NaN
8 8 9 7 16 1977 1 DM F 34.0 NaN
9 9 10 7 16 1977 6 PF F 20.0 NaN
10 35539 35540 12 31 2002 15 PB F 26.0 23.0
11 35540 35541 12 31 2002 15 PB F 24.0 31.0
12 35541 35542 12 31 2002 15 PB F 26.0 29.0
13 35542 35543 12 31 2002 15 PB F 27.0 34.0
14 35543 35544 12 31 2002 15 US NaN NaN NaN
15 35544 35545 12 31 2002 15 AH NaN NaN NaN
16 35545 35546 12 31 2002 15 AH NaN NaN NaN
17 35546 35547 12 31 2002 10 RM F 15.0 14.0
18 35547 35548 12 31 2002 7 DO M 36.0 51.0
19 35548 35549 12 31 2002 5 NaN NaN NaN NaN

8.2 Joining DataFrames

Concatenating DataFrames allows you to combine two entire DataFrames into a single one. In many cases, you want to combine only selected parts of two DataFrames.

You might, for example, want to merge rows of two DataFrames that have matching values in specific columns. The pandas function merge() performs an operation that you may know as a join if you worked with databases before. The join operation joins the content of two DataFrames in a particular way. There are different types of joins, but the workflow to perform a join operation is always the same:

  1. You identify a left and a right DataFrame, among the two you want to join;
  2. You identify in both your left and right DataFrame a column (or set of columns) to join on;
  3. You choose the type of join;
  4. You perform the join running the function pd.merge() with the specified inputs and options.

What it means for a DataFrame to be ‘left’ or ‘right’ depends on the type of join. The main thing to remember is that it matters which DataFrame you mention first when performing a join.

We will only be looking at what is know as an inner join. Investigating the other types of joins is left as an exercise.

Let’s see some join example using the first ten rows of our surveys_df DataFrame (surveys_df_sub_first10). This will be our left DataFrame, containing data on individual animals. Our right DataFrame will be the first 20 rows of species_df, containing some information on each species. Let us rename them for the purpose of the join:

left_df = surveys_df_sub_first10
right_df = species_df.head(20)
left_df
record_id month day year plot_id species_id sex hindfoot_length weight
0 1 7 16 1977 2 NL M 32.0 NaN
1 2 7 16 1977 3 NL M 33.0 NaN
2 3 7 16 1977 2 DM F 37.0 NaN
3 4 7 16 1977 7 DM M 36.0 NaN
4 5 7 16 1977 3 DM M 35.0 NaN
5 6 7 16 1977 1 PF M 14.0 NaN
6 7 7 16 1977 2 PE F NaN NaN
7 8 7 16 1977 1 DM M 37.0 NaN
8 9 7 16 1977 1 DM F 34.0 NaN
9 10 7 16 1977 6 PF F 20.0 NaN
right_df
species_id genus species taxa
0 AB Amphispiza bilineata Bird
1 AH Ammospermophilus harrisi Rodent
2 AS Ammodramus savannarum Bird
3 BA Baiomys taylori Rodent
4 CB Campylorhynchus brunneicapillus Bird
5 CM Calamospiza melanocorys Bird
6 CQ Callipepla squamata Bird
7 CS Crotalus scutalatus Reptile
8 CT Cnemidophorus tigris Reptile
9 CU Cnemidophorus uniparens Reptile
10 CV Crotalus viridis Reptile
11 DM Dipodomys merriami Rodent
12 DO Dipodomys ordii Rodent
13 DS Dipodomys spectabilis Rodent
14 DX Dipodomys sp. Rodent
15 EO Eumeces obsoletus Reptile
16 GS Gambelia silus Reptile
17 NL Neotoma albigula Rodent
18 NX Neotoma sp. Rodent
19 OL Onychomys leucogaster Rodent

The column we want to perform the join on is the one containing information about the species_id. Conveniently, this column has the same label in both DataFrames. Note that this is not always the case.

inner_join = pd.merge(left_df, right_df, left_on='species_id', right_on='species_id', how='inner')
inner_join
record_id month day year plot_id species_id sex hindfoot_length weight genus species taxa
0 1 7 16 1977 2 NL M 32.0 NaN Neotoma albigula Rodent
1 2 7 16 1977 3 NL M 33.0 NaN Neotoma albigula Rodent
2 3 7 16 1977 2 DM F 37.0 NaN Dipodomys merriami Rodent
3 4 7 16 1977 7 DM M 36.0 NaN Dipodomys merriami Rodent
4 5 7 16 1977 3 DM M 35.0 NaN Dipodomys merriami Rodent
5 8 7 16 1977 1 DM M 37.0 NaN Dipodomys merriami Rodent
6 9 7 16 1977 1 DM F 34.0 NaN Dipodomys merriami Rodent

As you may notice, the resulting DataFrame has only seven rows, while our original DataFrames had 10 and 20 rows, respectively. This is because an inner join selects only those rows where the value of the joined column occurs in both DataFrames (mathematically, an intersection).

Aside from the inner join, there are three more types of join that you can do using the merge() function: - An inner join selects only the rows that result from the combination of matching rows in both the original left and right DataFrames (intersection); - A left join selects all rows that were in the original left DataFrame, some of which may have been joined with a matching entry from the right DataFrame; - A right join selects all rows that were in the original right DataFrame, some of which may have been joined with a matching entry from the left DataFrame; - An outer join merges the two DataFrames and keeps all resulting rows.

To better understand how join works, it may be useful to look at the diagrams below:

  • Do you want to select only common information between the two DataFrames? Then you use an inner join;
  • Do you want to add information to your left DataFrame? Then you use a left join;
  • Do you want to add information to your right DataFrame? Then you use a right join;
  • Do you want to get all the information from the two DataFrames? Then you use an outer join.

Exercises 9 and 10

Now go to the Jupyter Dashboard in your internet browser and continue with exercise 9 and 10.

We will continue with Data visualization.