When I started working with pandas I noticed that there were so many ways how to subset, filter and join data with pandas. But I was lacking a systematic overview. How do the different approaches differ and when to use which?

In this blogpost we’ll look at different ways for subsetting, filtering and combining DataFrames.

Subsetting Data: Selecting subsets of rows and columns by labels and positions

.iloc stands for integer-location based indexing. Which means it needs integer input and refers to the position of a row or column you want to select. You can pass integers (0) to select a single row, list of integers ([0,4,9]) and slice objects (1:2,3:5). Here are some examples:

# rows
df.iloc[0] # first row of data frame 
df.iloc[1] # second row of data frame 
df.iloc[-1] # last row of data frame 
df.iloc[0:10] # first 10 rows of a data frame

# columns:
df.iloc[:,0] #first column
df.iloc[:,1] # second column 
df.iloc[:,-1] # last column of data frame 
df.iloc[:,0:10] # first 10 column of data frame 

.loc is label based. You can use a single label ('id'), a list of labels (['column_a', 'column_b']) or a slice object (row_a:row_c, column_b:column_d). Note that if you pass an integer value to .loc, it will be interpreted as a label.

If you want to select rows or columns that contain a certain string (e.g. a prefix or postfix) you can use filter(). You pass the string you are looking for to the parameters like or regex. The axis parameter determines whether you search in the index (axis = 0) or in the column names (axis = 1). You can filter all columns that contain the string foo with df.filter(like ='foo', axis = 1.

Here a brief overview about subsetting data

Compare iloc, loc and filter

All three ways above allow you to subset your data. However, sometimes one or the other may be easier to use or lead to less errors, e.g. if you delete columns from your DataFrame. To compare the 3 ways, let’s look at the diabetes dataset

from sklearn import datasets
import pandas as pd
diabetes = datasets.load_diabetes()
df = pd.DataFrame(diabetes.data, columns=diabetes.feature_names)
df.head()

(1) Select the age column

Note that iloc returns a Series, if you select only one column. Also, if your first column ever changes (e.g. because you reset the index), you will suddendly retrieve another column. If you need a specific column, I suggest you use loc or filter that pass the name of this column specifically.

(2) Select the colums s1 to s6

Again, if the positions of the column change, iloc will lead to errors. If your columns follow a pattern rather than a position or a name, I’ll suggest you use filter as it is less error prone.

Filtering Data: Selecting subsets of rows based on conditions

There are multiple ways how to filter data with pandas. We’ll look at how boolean indexing works and how to filter with the query() function.

If you are familiar with SQL, query might be the most intuitive way for you. You just pass the condition as a string to the query function. The condition is almost the same code as you would put in your WHERE-condition in a SQL-query. Just the operators are just slightly different

So you could for example use df.query("column_a == 'foo' & column_b == 1")

Boolean Masking is another powerful way to access data. The idea is to use a boolean mask to index the DataFrame. This boolean mask is a vector of true and false values for each row in the DataFrame.

new_df = df[<some boolean mask>]

Only the rows with true values will be returned. Of course, you could pass this vector directly

my_mask = [True, False, True, False, True, False, True, False, True, False, True, False, True]
df[my_mask]  

or you create it with conditions.

df[df.<colname> == 'foo']

Compare query vs. boolean masking

Lets compare the two approaches this time using the boston dataset.

boston = datasets.load_boston()
df = pd.DataFrame(boston.data, columns=boston.feature_names)
df.head()

Here are three example on how to achieve the same things with both approaches.

One could argue, that query is more readable and requieres less typing as you do not have to repead the name of the DataFrame in the condition.

Combining Data: joins

Pandas has three functions for joining tables join,merge and concat. join and merge are similar to joining tables in SQL with a (left, right etc….) join. join() will attempt to do a left join on indices keeping all columns while merge will perform an inner join on the common columns. Both can combine two tables. concat is the most flexible option, which allows you to join rows and columns of multiple DataFrames. Your datasets are just stitched together along an axis — either the row axis or column axis- ignoring the index. So you can use concat for joining and for the union of DataFrames.

Compare join vs. concat vs. merge

Let’s create some datasets, what would happen if we combine those datasets with their default parameters. Note that the indices differ!

A = pd.DataFrame({'ID': [1, 2, 3, 4],
                    'Name': ['Tom', 'Lisa', 'Sara', 'John'],
                    'Age': ['10', '11', '12', '11']},
                   index=[0, 1, 2, 3])


B = pd.DataFrame({'ID': [1,3,4,6],
                  'Gender': ['m', 'f', 'm', 'm']},
                  index=[0, 1, 5, 6])

join

A.join(B)

will throw and error columns overlap but no suffix specified: Index(['ID'], dtype='object'). Since we have the ID Column in both datasets, we have to specify a suffix.

A.join(B, lsuffix = "a")


   	IDa	 Name	Age	ID 		Gender
0	1	 Tom	10	1.0 	m
1	2	 Lisa	11	3.0 	f
2	3	 Sara	12	NaN		NaN
3	4	 John	11	NaN		NaN

Note that join does a left join on the index and completely ignores the ID column.

merge

Merge per default performs an inner join on the common columns. It does not care about the index.

A.merge(B)


   	ID 	Name	Age	Gender
0	1	Tom		10	m
1	3	Sara	12	f
2	4	John	11	m

Using the how parameter, you can determine the type of join

	ID		Name	Age		Gender
0	1		Tom	    10		m
1	2		Lisa	11		NaN
2	3		Sara	12		f
3	4		John	11		m

concat

Per default, concat will create a union of the datasets, ignoring the index. It just unions both DataFrames and creates NaNs for columns that are missing in one of the datasets.

pd.concat([A, B])

	ID	Name		Age		Gender
0	1	Tom	    	10		NaN
1	2	Lisa		11		NaN
2	3	Sara		12		NaN
3	4	John		11		NaN
0	1	NaN	    	NaN		m
1	3	NaN  		NaN		f
5	4	NaN	    	NaN		m
6	6	NaN 		NaN		m

I typically use merge for joining DataFrames in a SQL-like way and concat for stitching DataFrames together or SQL-like unions.

Conclusion

We have seen several ways how to subset, filter and join DataFrames. Some of the functions will get you to the same results, while others have small differences you need to be aware of.