How to Split Excel Cells into Multiple Rows with pandas explode()

Introduction

Hello, I am a Python enthusiast. While working through a case study in a Python automation book, I needed to split a column of data into multiple rows, as shown in the original screenshot.

The goal is to achieve the layout shown in the second image.

Idea

Initially, some suggested using Excel's Text‑to‑Columns feature, but that only splits the column without propagating other row data. A quicker workaround would be copy‑paste, but a more programmatic solution is preferable.

Solution

Two approaches are presented.

1. Using the openpyxl library as described in the referenced book.

2. Using pandas' explode() function, which expands list‑like entries into separate rows.

import pandas as pd
df = pd.read_excel('keywords.xlsx')
# ['序号', '年份', '来源出版物名称', '索引关键字']
df.columns
# Split the '索引关键字' column by ';'
df.loc[:, ['索引关键字']] = df['索引关键字'].str.split(';')
# Expand the list into separate rows
df_exploded = df.explode(column='索引关键字')
print(df_exploded.head())

The resulting output matches the desired layout.

Testing with multiple rows also produces the correct transformation.

Both approaches satisfy the requirement.

Conclusion

This short tutorial shows how to handle Excel data that needs to be split into multiple rows by leveraging pandas' explode() function, providing a quick and reliable solution for similar data‑processing tasks.