Python实现：按Pin、Site、Department分组的DataFrame累计和相除

Hey there! Let's tackle this problem step by step. The goal is to calculate the ratio of row-wise cumulative sums for each Period column between the two DataFrames, aligned by Pin, Site, and Department—regardless of their original order. We'll also format the result as the fraction expressions you specified, not just numerical values.

Step 1: Prepare the Sample DataFrames #

First, let's recreate the properly formatted sample data you provided:

import pandas as pd

# DataFrame 1
df1 = pd.DataFrame({
    'Pin': [1001, 1003, 1002],
    'Site': ['L', 'L', 'R'],
    'Department': [42, 42, 45],
    'Period1': [1, 4, 4],
    'Period2': [0, 4, 5],
    'Period3': [2, 3, 2],
    'Period4': [3, 4, 4]
})

# DataFrame 2
df2 = pd.DataFrame({
    'Pin': [1002, 1003, 1001],
    'Site': ['R', 'L', 'L'],
    'Department': [45, 42, 42],
    'Period1': [5, 4, 1],
    'Period2': [6, 5, 2],
    'Period3': [5, 6, 4],
    'Period4': [5, 8, 5]
})

Step 2: Align Rows Using Multi-Index #

Set Pin, Site, Department as the index for both DataFrames. This ensures we match the correct rows even if their original order doesn't line up:

# Set multi-index for alignment
df1_indexed = df1.set_index(['Pin', 'Site', 'Department'])
df2_indexed = df2.set_index(['Pin', 'Site', 'Department'])

# Sort index to match your desired output order (cleaner for readability)
df1_indexed = df1_indexed.sort_index()
df2_indexed = df2_indexed.sort_index()

Step 3: Identify Period Columns (Scalable for Future Months) #

We'll dynamically detect all Period columns so the code works even when more are added later:

period_cols = [col for col in df1.columns if 'Period' in col]

Step 4: Build Cumulative Sum Expressions #

Instead of just calculating numerical cumulative sums, we'll construct the string expressions you want (like (1+0)/(1+2)):

def build_cumulative_expr(df):
    expr_df = pd.DataFrame(index=df.index)
    for col in period_cols:
        # Get all Period columns up to the current one
        current_cols = period_cols[:period_cols.index(col)+1]
        # Join values as strings with '+'
        sum_str = df[current_cols].astype(str).agg('+'.join, axis=1)
        # Wrap in parentheses if there are multiple terms
        expr_df[col] = sum_str.apply(lambda x: f'({x})' if '+' in x else x)
    return expr_df

# Generate expressions for both DataFrames
df1_expr = build_cumulative_expr(df1_indexed)
df2_expr = build_cumulative_expr(df2_indexed)

# Combine into fraction strings
result_expr = df1_expr + '/' + df2_expr

Step 5: Format the Final Result #

Reset the index to bring back Pin, Site, Department as columns, matching your desired output structure:

final_result = result_expr.reset_index()
final_result = final_result[['Pin', 'Site', 'Department'] + period_cols]

print(final_result)

Final Output #

You'll get exactly the formatted result you're looking for:

Pin Site Department    Period1      Period2        Period3          Period4
0  1001    L         42        1/1  (1+0)/(1+2)  (1+0+2)/(1+2+4)  (1+0+2+3)/(1+2+4+5)
1  1002    R         45        4/5  (4+5)/(5+6)  (4+5+2)/(5+6+5)  (4+5+2+4)/(5+6+5+5)
2  1003    L         42        4/4  (4+4)/(4+5)  (4+4+3)/(4+5+6)  (4+4+3+4)/(4+5+6+8)

Key Notes #

• Scalability: Automatically handles new Period columns (e.g., Period5, Period6) as long as they follow the PeriodN naming pattern.
• Alignment: Multi-indexing guarantees rows are matched correctly even if Pin order differs between the two DataFrames.
• Readability: The expression builder handles both single-term (e.g., 1/1) and multi-term fractions neatly.

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