How to Reshape Complex Excel Tables with Pandas Pivot_table in Python

In this article, the author addresses a pandas question from a Python automation group: how to reshape a table containing status, ID, and name columns into a format where each status and ID appears as separate columns aligned with names.

Introduction

The problem was posted in a WeChat group, asking for a DataFrame transformation using pandas.

Implementation

The author first tried openpyxl and Excel formulas, then presented a solution using pandas pivot_table. The code is shown below:

# Use pivot_table to restructure
df_new = pd.pivot_table(df, index='名称', columns=df.groupby('名称').cumcount().add(1), values=['状态', '编号'], aggfunc='first')

# Rename columns
df_new.columns = [f'状态{i}' if '状态' in col else f'编号{i}' for col, i in df_new.columns]
columns = df_new.columns.tolist()
columns = [columns[i::4] for i in range(len(columns)-4)]
columns = sum(columns, [])
df = df_new[columns].reset_index()
print(df)

The resulting DataFrame matches the expected layout, as illustrated in the following screenshots.

The solution successfully resolved the user's issue.

Conclusion

The article demonstrates a practical pandas pivot_table technique for converting irregular Excel data into a structured format, providing the full code and visual results.