如何按索引值拼接Pandas多层索引DataFrame行并转换指定DataFrame结构？

Got it, let's tackle your two Pandas problems one by one with practical examples and clear explanations.

First, let's break down two common interpretations of "concatenate rows by index value"—either merging rows under the same top-level index into a single row (wide-formatting) or joining values from those rows into a single entry. I'll cover both scenarios with sample code.

Example Setup #

Let's start with a sample multi-index DataFrame to work with:

import pandas as pd

# Create a multi-index DataFrame
multi_index = pd.MultiIndex.from_tuples(
    [('TeamA', 'Player1'), ('TeamA', 'Player2'), ('TeamB', 'Player1'), ('TeamB', 'Player2')],
    names=['Team', 'Player']
)
df = pd.DataFrame({'Score': [25, 30, 22, 28]}, index=multi_index)

This gives us:

Score
Team   Player         
TeamA  Player1      25
       Player2      30
TeamB  Player1      22
       Player2      28

Scenario 1: Merge Rows into a Single Row per Top-Level Index #

If you want to turn all rows under the same team into columns (wide-format), use unstack():

# Unstack the Player level to convert rows into columns
concatenated_df = df.unstack(level='Player')

Result:

Score     
Player  Player1 Player2
Team                 
TeamA        25      30
TeamB        22      28

Scenario 2: Join Values from Rows into a Single Entry #

If you need to concatenate the actual values (e.g., into a comma-separated string) for rows sharing the same index, use groupby() with a custom aggregation function:

# Group by Team index, join Score values into a single string
joined_scores_df = df.groupby(level='Team').agg(
    {'Score': lambda x: ', '.join(map(str, x))}
)

Result:

Score
Team              
TeamA      25, 30
TeamB      22, 28

Since you didn't share the exact original and target structures, I'll walk through the most common conversion scenarios with examples. If you have a specific structure in mind, just share sample input/output and I can refine this further!

Common Conversion Scenarios #

1. Long Format → Wide Format (Pivot)

Suppose your original DataFrame is in long (tidy) format:

df_long = pd.DataFrame({
    'Product': ['Laptop', 'Laptop', 'Phone', 'Phone'],
    'Feature': ['RAM', 'Storage', 'RAM', 'Storage'],
    'Value': ['16GB', '512GB', '8GB', '256GB']
})

To convert it to a wide format where each Feature becomes a column:

df_wide = df_long.pivot(index='Product', columns='Feature', values='Value').reset_index()

Result:

Feature  Product    RAM Storage
0         Laptop  16GB   512GB
1          Phone   8GB   256GB

2. Wide Format → Long Format (Melt)

If you need to go the other way (wide to long), use melt():

df_melted = df_wide.melt(
    id_vars='Product', 
    var_name='Feature', 
    value_name='Value'
)

This will revert back to the original df_long structure.

3. Convert to Multi-Index Structure

To add a multi-index to your DataFrame, use set_index() with multiple columns:

df_multi_index = df_long.set_index(['Product', 'Feature'])

Result:

Value
Product Feature           
Laptop  RAM          16GB
        Storage     512GB
Phone   RAM           8GB
        Storage     256GB

4. Reset Index to Flat Structure

If you have a multi-index and want to convert it to a flat DataFrame with regular columns:

df_flat = df_multi_index.reset_index()

This brings the index levels back as columns, returning to df_long.

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