Pandas表格透视与逆透视实现：自定义转换及回退方法问询

Hey there! Let's work through both the pivot to get your desired df1 structure and the unpivot to convert it back to the original df. I'll also explain why your initial code didn't work.

Your initial attempts only focused on the Manager column and ignored the Day dimension, which is why they didn't produce the structure you wanted. We need to pivot using both metrics (Manager/Store_Opener) and Day as the column split. Here's how to do it:

import pandas as pd

# Your original DataFrame
df = pd.DataFrame({
    'Store_ID': [1]*21, 
    'Week_ID': [1]*7 + [2]*7 + [3]*7, 
    'Day': ['Mo','Tu','We','Th','Fr','Sa','Su']*3, 
    'Manager': ['Kev','Kev','Nash','Kev','Kev','Nash','Kev','Kev','Nash','Kev','Kev','Nash','Kev','Kev','Nash','Kev','Kev','Nash','Kev','Kev','Nash'], 
    'Store_Opener': ['Jev','Jev','Oash','Kev','Kev','Nash','Jev','Jev','Oash','Kev','Kev','Nash','Jev','Jev','Oash','Kev','Kev','Nash','Kev','Kev','Nash'] 
})

# Step 1: Pivot with multi-value columns
pivoted = df.pivot(
    index=['Store_ID', 'Week_ID'],
    columns='Day',
    values=['Manager', 'Store_Opener']
)

# Step 2: Rename columns to match your `Day_X_Y` format
pivoted.columns = [f'Day_{day}_{metric}' for metric, day in pivoted.columns]

# Step 3: Reset index to get flat columns, fill missing values with '?' if needed
df1 = pivoted.reset_index().fillna('?')

print(df1)

This will produce exactly the structure you defined for df1 (note: your sample df1 had ? for some Day_We_* columns, but the original data has valid values—this code will keep those values, or you can adjust the fillna if you intentionally want placeholders).

To unpivot df1, we need to melt the wide columns back into a long format, split the column names to extract Day and the metric (Manager/Store_Opener), then reshape again. Here's the code:

# Step 1: Melt all metric columns into a single 'Day_Role' column
unmelted = df1.melt(
    id_vars=['Store_ID', 'Week_ID'],
    var_name='Day_Role',
    value_name='Value'
)

# Step 2: Split the 'Day_Role' column to separate Day and metric (Manager/Store_Opener)
unmelted[_, 'Day', 'Role'] = unmelted['Day_Role'].str.split('_', expand=True)

# Step 3: Drop the unused 'Prefix' column, then pivot to restore original columns
original_df = unmelted.drop(_, axis=1).pivot(
    index=['Store_ID', 'Week_ID', 'Day'],
    columns='Role',
    values='Value'
).reset_index()

# Step 4: Reorder columns to match the original df
original_df = original_df[['Store_ID', 'Week_ID', 'Day', 'Manager', 'Store_Opener']]

print(original_df)

This will give you back a DataFrame identical to your original df (assuming you didn't modify values like replacing valid data with ?).

Your lines:

df.set_index(['Store_ID', 'Week_ID'])['Manager'].unstack()
df.pivot_table(index='Store_ID', columns='Week_ID', values='Manager')

Had two key issues:

• They only processed the Manager column, ignoring Store_Opener entirely.
• They didn't incorporate the Day dimension—instead, they tried to aggregate/arrange data by Store and Week alone, which doesn't match the Day_X_Y column structure you needed.

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