How to Transform Complex Excel Data with Pandas: A Step‑by‑Step Guide

1. Introduction

During the National Day holiday, a member of the Python Silver Group asked a question about handling data with Pandas. The goal was to transform an Excel file into a specific table format shown in the target screenshot.

2. Implementation

The initial solution used pd.concat to read all sheets, unstack the level, and rename columns with a lambda expression. The code was:

import pandas as pd

df = pd.concat(pd.read_excel(r"LT211120Y6_output(1).xlsx", sheet_name=None)).unstack(level=0)
df.columns = df.columns.map(lambda x: '{1}.{0}'.format(x[1].strip('day'), x[0]))
print(df)

The resulting table was close to the expected layout, as shown in the screenshot.

A refined version added conditional column handling and selected only the relevant columns:

import pandas as pd

df = pd.concat(pd.read_excel(r"LT211120Y6_output(1).xlsx", sheet_name=None)).unstack(level=0)
df.columns = df.columns.map(lambda x: '{1}{0}'.format(x[1].strip('day'), x[0]) if x[0] == '血糖时间' else x[0])
df = df[['血糖时间2', '血糖值']].rename(columns={'血糖时间2': '血糖时间'})
print(df)

The final output matched the desired table, successfully solving the fan's problem.

3. Conclusion

The article demonstrated a practical Pandas workflow for reshaping multi‑sheet Excel data, providing clear code snippets and visual results. It helped the community member achieve the required data format and illustrated how to adjust column names and filter rows effectively.