Pandas：按payment和country分组并自定义将长DataFrame转换为宽表

Here's a clean, efficient way to achieve your desired transformation by combining groupby with value_counts and concatenation:

import pandas as pd

# Your original DataFrame
df = pd.DataFrame({
    'payment': ['visa','paypal','mc','visa','visa','visa'],
    'type': ['type1','type1','type2','type3','type2','type1'],
    'err': ['OK','OK','ERROR','OK','OK','OK'],
    'country': ['AR','DE','AU','US','FR','FR'],
    'source': ['source1','source1','source2','source2','source3','source2'],
})

# Step 1: Group by payment and country
grouped = df.groupby(['payment', 'country'])

# Step 2: Calculate base stats (total payments and error count)
base_stats = grouped.agg(
    number_payments=('payment', 'size'),
    num_errors=('err', lambda x: (x == 'ERROR').sum())
)

# Step 3: Calculate counts for each type, rename columns with prefix
type_counts = grouped['type'].value_counts().unstack(fill_value=0).add_prefix('num_')

# Step 4: Calculate counts for each source, rename columns with prefix
source_counts = grouped['source'].value_counts().unstack(fill_value=0).add_prefix('num_')

# Step 5: Combine all results into a single DataFrame
result = pd.concat([base_stats, type_counts, source_counts], axis=1).reset_index()

# Reorder columns to match your expected output (optional)
result = result[['payment', 'country', 'number_payments', 'num_errors', 
                 'num_type1', 'num_type2', 'num_type3', 
                 'num_source1', 'num_source2', 'num_source3']]

print(result)

Explanation: #

1. Grouping: We first group the DataFrame by payment and country to isolate each unique combination of these fields.
2. Base Statistics: Using agg(), we compute the total number of payments (via size()) and count errors by summing boolean values where err == 'ERROR'.
3. Categorical Counts: For type and source, value_counts() gives per-category counts, then unstack() converts these into columns (filling missing categories with 0). Adding the num_ prefix aligns column names with your requirements.
4. Combine Results: We concatenate all aggregated DataFrames and reorder columns to match your desired output structure.

This approach avoids messy pivot operations and leverages pandas' built-in functions for clean, efficient aggregation.

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