How to Unnest (Explode) a Column in a Pandas DataFrame, into Multiple Rows

Python Pandas Dataframe Pandas Explode

In this article, we will discuss how to unnest (explode) a column in a pandas DataFrame, into multiple rows. We will explore different methods to achieve this, including code snippets and examples.

Problem Description

Let's start by understanding the problem at hand. We have a DataFrame with one of the columns being an object with a list type cell. Here is an example:

            
import pandas as pd

df = pd.DataFrame({'A': [1, 2], 'B': [[1, 2], [1, 2]]})

Output:
   A       B
0  1  [1, 2]
1  2  [1, 2]

Our expected output is to unnest the 'B' column so that each element in the list becomes a separate row. The final DataFrame should look like this:

            
   A  B
0  1  1
1  1  2
3  2  1
4  2  2

Now, let's dive into the different ways to solve this problem.

Method 1: Explode

Starting from pandas version 0.25, you can use the 'explode' function to achieve this. The 'explode' function takes a column with a list and creates a new row for each element in the list. Here's how you can use 'explode' to solve our problem:

            
df_exp = df.explode('B')

Output:
   A  B
0  1  1
0  1  2
1  2  1
1  2  2

As you can see, the 'B' column has been exploded, creating separate rows for each element in the list.

Method 2: Apply + Explode

If you are using an older version of pandas that does not have the 'explode' function, you can still achieve the same result using the 'apply' method combined with 'explode'. Here's an example:

            
df_exp = df.assign(B=df['B'].apply(pd.Series)).explode('B')

Output:
   A  B
0  1  1
0  1  2
1  2  1
1  2  2

In this method, we first use the 'apply' method to split the list into separate columns using the 'pd.Series' function. Then, we use 'explode' to create separate rows for each element in the exploded column.

Method 3: Nested List Comprehension + Join

If you prefer a more concise and efficient solution, you can use nested list comprehension combined with the 'join' function. Here's an example:

            
df_exp = pd.DataFrame([[a, b] for a, blist in zip(df['A'], df['B']) for b in blist], columns=['A', 'B'])

Output:
   A  B
0  1  1
1  1  2
2  2  1
3  2  2

In this method, we iterate over each element in column 'A' and the corresponding list in column 'B', creating a new row for each combination using nested list comprehension.

Comparison of Methods

Now that we have explored different methods, let's compare their performance using the 'timeit' module:

            
import timeit

# Method 1: Explode
method1_time = timeit.timeit(lambda: df.explode('B'), number=1000)

# Method 2: Apply + Explode
method2_time = timeit.timeit(lambda: df.assign(B=df['B'].apply(pd.Series)).explode('B'), number=1000)

# Method 3: Nested List Comprehension + Join
method3_time = timeit.timeit(lambda: pd.DataFrame([[a, b] for a, blist in zip(df['A'], df['B']) for b in blist], columns=['A', 'B']), number=1000)

print("Method 1 Time: {} seconds".format(method1_time))
print("Method 2 Time: {} seconds".format(method2_time))
print("Method 3 Time: {} seconds".format(method3_time))

By running the above code, you can compare the time taken by each method. The results may vary depending on your system and the size of the DataFrame.

Conclusion

In this article, we discussed how to unnest (explode) a column in a pandas DataFrame, into multiple rows. We explored three different methods - 'explode', 'apply + explode', and nested list comprehension + join. Each method has its own advantages and it's recommended to choose the one that suits your requirements and the version of pandas you are using. By following these methods, you can easily transform your DataFrame and achieve the desired output.

Related Questions