Pandas: Splitting (Exploding) a column into multiple rows

# Input data:
EmployeeId, City
001, Mumbai|Bangalore
002, Pune|Mumbai|Delhi
003, Mumbai|Bangalore
004, Mumbai|Pune
005, Bangalore
...
# Expected format
EmployeeId, City
001, Mumbai
001, Bangalore
002, Pune
002, Mumbai
002, Delhi
003, Mumbai
003, Bangalore
004, Mumbai
004, Pune
005, Bangalore
import pandas as pd# Import the data
df = pd.DataFrame({
'EmployeeId': ['001', '002', '003', '004', '005'],
'City': ['Mumbai|Bangalore', 'Pune|Mumbai|Delhi', 'Mumbai|Bangalore', 'Mumbai|Pune', 'Bangalore']
})
# Step 1
# We start with creating a new dataframe from the series with EmployeeId as the index
new_df = pd.DataFrame(df.City.str.split('|').tolist(), index=df.EmployeeId).stack()
# Step 2
# We now want to get rid of the secondary index
# To do this, we will make EmployeeId as a column (it can't be an index since the values will be duplicate)
new_df = new_df.reset_index([0, 'EmployeeId'])
# Step 3
# The final step is to set the column names as we want them
new_df.columns = ['EmployeeId', 'City']
# Result
EmployeeId City
0 001 Mumbai
1 001 Bangalore
2 002 Pune
3 002 Mumbai
4 002 Delhi
5 003 Mumbai
6 003 Bangalore
7 004 Mumbai
8 004 Pune
9 005 Bangalore

Explanation

Step 1 is the real trick here, the other 2 steps are more of cleaning exercises to get the data into correct format. In Step 1, we are asking Pandas to split the series into multiple values and the combine all of them into single column using the stack method. The output of Step 1 without stack looks like this:

0 	        1 	        2
EmployeeId
001 Mumbai Bangalore None
002 Pune Mumbai Delhi
003 Mumbai Bangalore None
004 Mumbai Pune None
005 Bangalore None None

Final Code Snippet

# Explode/Split column into multiple rows
new_df = pd.DataFrame(df.City.str.split('|').tolist(), index=df.EmployeeId).stack()
new_df = new_df.reset_index([0, 'EmployeeId'])
new_df.columns = ['EmployeeId', 'City']

Read more

--

--

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store