如何使用Pandas透视数据框（将列值拆分为多列）实现文件数据展平？

Got it, let's walk through how to fix this compound header issue and get your data into a clean, usable format. Your input has a two-level header (Actual/Forecast on the first row, Jan/Feb on the second), so we need to first properly capture that hierarchy, then reshape the data to flatten it out.

Step 1: Create a MultiIndex for the Compound Header #

Right now, your initial DataFrame treats those first two rows as data, which isn't what we want. Let's instead use those first two rows to build a multi-level column index that preserves both header levels, then use the remaining rows as the actual data.

import pandas as pd

input = [['','','','Actual','Forecast'],['','','','Jan','Feb'],['A','B','Personal','10.00','1.00'],['A','B','Operational','20.00','2.00']]

# Extract the first two rows to build our multi-level header
header_rows = [input[0], input[1]]
multi_header = pd.MultiIndex.from_arrays(header_rows, names=['Category', 'Month'])

# Create the DataFrame using the remaining rows as data
df = pd.DataFrame(input[2:], columns=multi_header)

At this point, your columns will have two levels: Category (Actual/Forecast) and Month (Jan/Feb), while the first three columns have empty strings as their header values (since they didn't have compound headers).

Step 2: Clean Up the Non-Compound Column Names #

The first three columns (Div, cc, expense_type) don't need a multi-level header, so let's rename them to make things clearer:

# Rename the first three columns to their proper names (removing the empty header levels)
df.columns = [('Div', ''), ('cc', ''), ('expense_type', '')] + list(df.columns[3:])

Step 3: Flatten the Data with melt() #

Now we'll use Pandas' melt() function to convert the wide-format data into a long-format structure. This will "unpivot" the compound header columns into rows, keeping the Div, cc, and expense_type as identifier columns.

# Melt the DataFrame to flatten the compound headers
flattened_df = df.melt(
    id_vars=[('Div', ''), ('cc', ''), ('expense_type', '')],
    var_name=['Category', 'Month'],
    value_name='Amount'
)

# Clean up the column names to remove the empty secondary level
flattened_df.columns = ['Div', 'cc', 'expense_type', 'Category', 'Month', 'Amount']

# Optional: Convert the Amount column to a numeric type (since it's currently a string)
flattened_df['Amount'] = flattened_df['Amount'].astype(float)

Final Result #

If you print flattened_df, you'll get the clean, structured output you're looking for:

This structure is much easier to work with for filtering, aggregating, or visualizing your data later on.

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