How to Split and Merge Excel Data with Pandas: A Step‑by‑Step Guide

Introduction

Hello, I'm Pi Pi. In a Python community I was asked a practical Pandas problem: two Excel files need to be combined—one file contains a column that should be split into 13 columns, and the other file provides a timestamp to be inserted into the resulting table.

Implementation

The instructor provided the following code to read the first Excel file, reshape the data, clean duplicates, set column headers, and display the result.

@哎呦喂  是豆子～ 这个先把获取的数据.xlsx中转换为13列。剩下的就是两个excel匹配的问题了。我要忙了，没时间往下写了

# 读取Excel文件
df = pd.read_excel('获取的数据.xlsx', index_col=0)
# 将数据转换为5列
df_new = pd.DataFrame(df['data'].values.reshape(-1, 13))
# 删除df_new中重复的行，仅保留第一个
df_new.drop_duplicates(keep='first', inplace=True)
# 把df_new的第0行设置为df_new的列名
df_new.columns = df_new.iloc[0]
# 删除第0行
df_new.drop(index=0, inplace=True)
# 打印结果
print(df_new)

Further discussion highlighted difficulties in inserting the timestamp column because the two tables lack a unique matching key; the data must be aligned sequentially, which can be error‑prone when handling multiple days.

Conclusion

The article demonstrates a concrete Pandas workflow for reshaping Excel data and outlines the challenges of merging time information without a natural key, offering a starting point for readers to adapt to their own datasets.