如何在Pandas DataFrame中按分组（level [0,1]）及自定义条件实现累积除法并添加计算行？

Hey there! Let's solve this problem efficiently using pandas' groupby.apply method—this avoids messy loops and keeps the code clean and maintainable. Here's how to do it step by step:

Step 1: Define a Function to Process Each Group #

We'll create a function that takes each (year, country) group, extracts population values for each age group, computes the required ratios, and appends these as new rows to the group.

import pandas as pd

# Original DataFrame
d = { 
    'year': [2019,2019,2019,2020,2020,2020], 
    'age group': ['(0-14)','(14-50)','(50+)','(0-14)','(14-50)','(50+)'], 
    'con': ['UK','UK','UK','US','US','US'], 
    'population': [10,20,300,400,1000,2000]
}
df = pd.DataFrame(data=d)

def add_calculated_rows(group):
    # Extract population values for each age group from the current group
    child_pop = group.loc[group['age group'] == '(0-14)', 'population'].iloc[0]
    young_pop = group.loc[group['age group'] == '(14-50)', 'population'].iloc[0]
    old_pop = group.loc[group['age group'] == '(50+)', 'population'].iloc[0]
    
    # Calculate the three required ratios
    calculations = {
        'young vs child': young_pop / child_pop,
        'old vs young': old_pop / young_pop,
        'unemployed vs working': (child_pop + old_pop) / young_pop
    }
    
    # Create a new DataFrame with the calculated rows
    new_rows = pd.DataFrame({
        'year': group['year'].iloc[0],
        'age group': list(calculations.keys()),
        'con': group['con'].iloc[0],
        'population': list(calculations.values())
    })
    
    # Combine the original group with the new calculated rows
    return pd.concat([group, new_rows], ignore_index=True)

Step 2: Apply the Function to Each Group #

Use groupby on year and con, then apply our function to each group. The group_keys=False parameter ensures we don't add extra index columns from the grouping.

result_df = df.groupby(['year', 'con'], group_keys=False).apply(add_calculated_rows)

Step 3: View the Result #

Printing result_df will show you the original rows plus the three calculated rows for each (year, country) pair:

year               age group  con  population
0   2019                (0-14)   UK        10.00
1   2019               (14-50)   UK        20.00
2   2019                  (50+)   UK       300.00
3   2019        young vs child   UK         2.00
4   2019          old vs young   UK        15.00
5   2019  unemployed vs working   UK        15.50
6   2020                (0-14)   US       400.00
7   2020               (14-50)   US      1000.00
8   2020                  (50+)   US      2000.00
9   2020        young vs child   US         2.50
10  2020          old vs young   US         2.00
11  2020  unemployed vs working   US         2.40

Why This Works #

• Groupby Apply: Processes each (year, country) group independently, so we never mix data across different groups.
• Clean Extraction: Uses .loc to fetch exact population values for each age group in the current group.
• Concatenation: Adds new calculated rows directly to the original group, preserving all original data while including the required metrics.

This approach avoids the pitfalls of manual loops or incorrect shift/cumdiv operations you tried earlier, and it's easy to modify if you need to add more calculations later.

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