如何转置并分组Pivot分组数据？附现有数据与尝试代码

Hey there! Let's sort out this transpose and grouping issue for you. From what I can tell, you've got a DataFrame grouped by Market Group with MTD/YTD performance metrics, and you want to restructure it to match the df.T transposed format—where metrics become rows and Market Groups become columns. Let's break down what went wrong with your initial code, then fix it properly.

Why Your Initial Code Didn't Work #

Let's quickly diagnose the issues:

• pd.DataFrame(df.values.reshape(-1,5)): This just reshapes the raw numerical values into a new DataFrame, but it completely discards column names, Market Group labels, and all contextual data. It's not a meaningful transpose.
• df.reset_index().pivot('Market Group', 'MTD-Total Revenue', 'YTD-Total Revenue'): The pivot parameters are misaligned here. You're using MTD-Total Revenue (which is all 0s) as column headers, which creates duplicate columns, and you're only extracting YTD-Total Revenue as values—ignoring all other critical metrics.

Correct Transpose & Grouping Method #

Here's the step-by-step solution to get the structure you want:

1. First, Confirm Your Data Structure

Assuming your original DataFrame has Market Group as a regular column (not an index), we'll start by setting it as the row index to preserve those labels during transpose.

2. Run the Correct Transpose

import pandas as pd

# (Optional: If your Market Group is already the index, skip this line)
df = df.set_index('Market Group')

# Perform the transpose to get metrics as rows, Market Groups as columns
transposed_df = df.T

# Preview the result
print(transposed_df.head())

This will give you exactly the df.T style output you're looking for:

• Each row represents a metric (e.g., MTD-Room Revenue, YTD-OCC%)
• Each column represents a Market Group (e.g., Aff, Air, BAR)

3. Bonus: Group MTD/YTD Metrics (Optional)

If you want to organize metrics by their MTD/YTD cycle for better readability, you can split the metric names into a multi-level index:

# Split metric names into [Cycle, Metric Type] (e.g., "MTD-Total Revenue" → ("MTD", "Total Revenue"))
transposed_df.index = transposed_df.index.str.split('-', expand=True)

# Sort the index to group all MTD metrics together, followed by YTD
transposed_df = transposed_df.sort_index(level=0)

print(transposed_df)

This will group all MTD metrics at the top, then YTD metrics below, making the data easier to scan.

Example Output Snippet #

After transposing, your data will look like this (abbreviated):

Market Group           Aff         Air        BAR         Cas  ...
MTD-Total Revenue      0.00        0.00       0.00        0.00
YTD-Total Revenue      0.00        0.00       0.00        0.00
MTD-Room Revenue  2026136.99  2809312.53  470866.23  4801710.10
YTD-Room Revenue 21546922.96 32534587.15 8341596.95 55466024.12
...

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