如何在Pandas多级列透视表中添加单独SUM列计算OPEN/CLOSE总和

Hey there! Let's figure out how to add those monthly Total columns to your pivot table. Here's a step-by-step breakdown with code examples tailored to your existing setup:

First, let's recap your original pivot table code for reference:

import pandas as pd

# Assuming your raw data is stored in a DataFrame called `df`
pivot_bad_pod_theft_df = pd.pivot_table(
    df,
    index=['DELIVERY_DEPOT'],
    columns=['Month1', 'BAD_OPEN_OR_CLOSE_STATUS'],
    values=['CON_NUMBER'],
    aggfunc={'CON_NUMBER': len},
    margins=True,
    fill_value=''
)

Your pivot table has a multi-level column structure: the first level is the month (e.g., Dec-17), and the second level is the status (OPEN, CLOSE, plus All from the margins=True setting). We need to add a Total column under each month that sums the OPEN and CLOSE values.

Method 1: Groupby Column Levels (Efficient & Clean) #

This method leverages pandas' groupby functionality on column levels to calculate totals in one go, then merges the results back into your pivot table.

# Calculate monthly totals: sum OPEN and CLOSE for each month
# Replace empty strings with 0 first to avoid NaN in calculations
month_totals = pivot_bad_pod_theft_df.replace('', 0).groupby(level=0, axis=1).apply(
    lambda x: x[(x.columns.get_level_values(1) == 'OPEN')] + x[(x.columns.get_level_values(1) == 'CLOSE')]
)

# Rename the second column level to 'Total'
month_totals.columns = month_totals.columns.set_levels(['Total'], level=1)

# Merge the original pivot table with the new totals
combined_df = pd.concat([pivot_bad_pod_theft_df, month_totals], axis=1)

# Reorder columns so each month's OPEN → CLOSE → Total are grouped together
# We use a sort key to ensure 'Total' comes after OPEN/CLOSE
combined_df = combined_df.reindex(
    columns=sorted(
        combined_df.columns,
        key=lambda col: (col[0], col[1] if col[1] != 'Total' else 'z')
    )
)

# Replace 0s back with empty strings to match original fill_value
combined_df = combined_df.replace(0, '')

Method 2: Iterate Through Months (Intuitive & Customizable) #

If you prefer a more explicit approach, you can loop through each month, calculate the total, and insert the column directly into your pivot table:

# Get a list of unique months (exclude the 'All' margin column)
unique_months = list({col[0] for col in pivot_bad_pod_theft_df.columns if col[0] != 'All'})

for month in unique_months:
    # Get the OPEN and CLOSE columns for the current month, convert empty strings to 0
    open_vals = pivot_bad_pod_theft_df[(month, 'OPEN')].replace('', 0)
    close_vals = pivot_bad_pod_theft_df[(month, 'CLOSE')].replace('', 0)
    
    # Calculate total and convert back to empty string if sum is 0
    monthly_total = open_vals + close_vals
    monthly_total = monthly_total.where(monthly_total != 0, '')
    
    # Find the position to insert the Total column (right after CLOSE)
    close_col_pos = pivot_bad_pod_theft_df.columns.get_loc((month, 'CLOSE'))
    pivot_bad_pod_theft_df.insert(close_col_pos + 1, (month, 'Total'), monthly_total)

Both methods will give you the desired output: each month will have OPEN, CLOSE, and Total columns, with the total being the sum of the two status values.

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