Predictive Hacks

Reshape Pandas Data Frames


We will provide some examples of how we can reshape Pandas data frames based on our needs. We want to provide a concrete and reproducible example and for that reason, we assume that we are dealing with the following scenario.

We have a data frame of three columns such as:

  • ID: The UserID
  • Type: The type of product.
  • Value: The value of the product, like ‘H’ for High, ‘M’ for Medium and ‘L’ for Low

The data are in a long format, where each case is one row. Let’s create the data frame:

Create the Pandas Data Frame

import pandas as pd

df = pd.DataFrame({'ID':[1,1,1,1,2,2,3,3,3,4],

Reshape Pandas Data Frames 1

Aggregate the Data by ID

Let’s say that we want to aggregate the data by ID by concatenating the text variables Type and Value respectively. We will use the lambda function and the join where our separator will be the | but it can be whatever you want.

# Aggregate the data by ID

df_agg = df.groupby('ID', as_index=False)[['Type','Value']].agg(lambda x: '|'.join(x))
Reshape Pandas Data Frames 2

As we can see now we have 4 rows, one per each ID.

Reshape to a Long Format

Our goal is to convert the “df_agg” to the initial one. We will need some steps to achieve this.

Convert the Columns to Lists

We will need to split the Type and Value columns and to transform them into lists.

df_agg['Type'] = df_agg['Type'].apply(lambda x: x.split("|"))
df_agg['Value'] = df_agg['Value'].apply(lambda x: x.split("|"))

Reshape Pandas Data Frames 3

Create a list of tuples from the two column lists

We know that the elements of each list appear in order. So, we need to do a mapping between the Type and the Value list element-wise. For that reason, we will use the zip function.

df_agg['Type_Value']= df_agg.apply(lambda x: list(zip(x.Type,x.Value)), axis=1)
Reshape Pandas Data Frames 4

Exlpode the list of tuples

Now, we will explode the Type_Value as follows:

df_agg = df_agg.explode('Type_Value')
Reshape Pandas Data Frames 5

Split the tuple into two different columns

Now we want to split the tuple into two different columns, where the first one is referred to the Type and the second one to the Value.

df_agg[['New_Type','New_Value']] = pd.DataFrame(df_agg['Type_Value'].tolist(), index=df_agg.index)
Reshape Pandas Data Frames 6

Now we will keep only the columns that we want and we will rename them.

df_agg = df_agg[['ID','New_Type', 'New_Value']].\
rename(columns={"New_Type": "Type", "New_Value": "Value"}).\

Reshape Pandas Data Frames 7

Check if the Data Frames are identical

As we can see we started with a long format, we reshaped the data, and then we converted it back to the initial format. Let’s verify if the initial data frame is the same as the last one that we created.


and we get True!

Share This Post

Share on facebook
Share on linkedin
Share on twitter
Share on email

Leave a Comment

Subscribe To Our Newsletter

Get updates and learn from the best

More To Explore


Document Splitting with LangChain

In this tutorial, we will talk about different ways of how to split the loaded documents into smaller chunks using