如何调整Python Pandas DataFrame：列标题下移一行并重命名合并表头

Hey there! I get it—trying to reshape a DataFrame to match a specific Excel layout can feel tricky when the usual methods don’t click right away. Let’s break this down with common scenarios and concrete code examples, since you didn’t share the exact input/output table structures yet.

Common Reshaping Scenarios & Solutions #

Most Excel-style DataFrame transformations fall into two categories: wide-to-long (unpivoting columns into rows) or long-to-wide (pivoting rows into columns). Here’s how to handle both:

1. Wide Table → Long Table (Unpivot)

Suppose your input Excel table looks like this (wide format with subject columns):

And you want to convert it to this long-format output:

Use Pandas’ melt() function to unpivot:

import pandas as pd

# Load your Excel file into a DataFrame
df = pd.read_excel("your_input_file.xlsx")

# Reshape with melt: keep '姓名' as the identifier column, turn other columns into rows
output_df = df.melt(
    id_vars=["姓名"],  # Columns to keep as identifiers
    var_name="科目",   # Name for the new column holding original column names
    value_name="分数"  # Name for the new column holding the values
)

# Save the result back to Excel
output_df.to_excel("your_output_file.xlsx", index=False)

2. Long Table → Wide Table (Pivot)

If you’re going the opposite direction (long to wide), use pivot():

# Starting with the long-format DataFrame
df = pd.read_excel("your_input_file.xlsx")

# Pivot to wide format
output_df = df.pivot(
    index="姓名",    # Rows to keep as the index
    columns="科目",  # Columns to turn into new wide columns
    values="分数"    # Values to fill the new columns
).reset_index()

# Remove the extra column name index for cleaner Excel output
output_df.columns.name = None

# Save to Excel
output_df.to_excel("your_output_file.xlsx", index=False)

Need a More Tailored Solution? #

If your table has more complex structures—like multi-level headers, merged cells, or columns that need splitting/merging—share a small example of your input table and desired output table (with column names and sample data). That way, I can give you code that fits your exact use case!

内容的提问来源于stack exchange，提问作者PSarkar