如何用Pandas生成列汇总行并计算单元格相对行总计占比？

Hey DirkLX, let's work through your Pandas questions one by one:

1. Regenerating TS_M1_ALL and TS_M2_ALL Column Totals #

It makes sense you tried pivot() and groupby(), but for calculating row-level or grouped totals, direct column summation is usually simpler. Here's how to do it based on two common scenarios:

Scenario 1: Calculate totals per row (each row's own TS_M1/TS_M2 sum) #

If your DataFrame has columns like TS_M1_c1, TS_M1_c2, TS_M2_c1, TS_M2_c2 alongside H1, you can auto-filter relevant columns and sum them row-wise:

import pandas as pd

# Example DataFrame matching your structure
df = pd.DataFrame({
    'H1': ['Jan-17', 'Feb-17'],
    'TS_M1_c1': [10, 12],
    'TS_M1_c2': [20, 18],
    'TS_M2_c1': [15, 16],
    'TS_M2_c2': [25, 24]
})

# Generate TS_M1_ALL: sum all columns starting with "TS_M1_"
ts_m1_cols = [col for col in df.columns if col.startswith('TS_M1_')]
df['TS_M1_ALL'] = df[ts_m1_cols].sum(axis=1)

# Generate TS_M2_ALL: sum all columns starting with "TS_M2_"
ts_m2_cols = [col for col in df.columns if col.startswith('TS_M2_')]
df['TS_M2_ALL'] = df[ts_m2_cols].sum(axis=1)

This will add two new columns with the row-wise totals for each TS category.

Scenario 2: Calculate totals grouped by H1 #

If you need to aggregate totals for each unique H1 value (e.g., all Jan-17 entries summed together), use groupby() with transform() to keep the totals aligned with your original rows:

df['TS_M1_ALL'] = df.groupby('H1')[ts_m1_cols].transform('sum')
df['TS_M2_ALL'] = df.groupby('H1')[ts_m2_cols].transform('sum')

This ensures every row in the same H1 group gets the same total value.

2. Calculating Percentage of Row Total #

To find what percentage a cell is of its corresponding row total (like TS_M1_c1 vs TS_M1_ALL for Jan-17), just divide the cell value by the total and multiply by 100. You can do this for individual columns or batch-process all TS sub-columns:

Individual Column Example #

# Calculate percentage for TS_M1_c1 relative to TS_M1_ALL
df['TS_M1_c1_pct'] = (df['TS_M1_c1'] / df['TS_M1_ALL'] * 100).round(2)

For Jan-17, this would give 33.33 (since 10/30 * 100 ≈ 33.33%).

Batch Process All TS Sub-Columns #

If you want percentages for all TS_M1_* and TS_M2_* columns:

# Process TS_M1 columns
for col in ts_m1_cols:
    df[f'{col}_pct'] = (df[col] / df['TS_M1_ALL'] * 100).round(2)

# Process TS_M2 columns
for col in ts_m2_cols:
    df[f'{col}_pct'] = (df[col] / df['TS_M2_ALL'] * 100).round(2)

This will add percentage columns for every sub-category, making it easy to compare proportions.

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