在Pandas DataFrame中添加分组累计汇总行的技术实现问询

Here's a practical, step-by-step implementation to add the required summary rows to your DataFrame:

Step 1: Set Up Your Data #

First, let's define the sample data (including both multi-device and single-device groups) to test the solution:

import pandas as pd

# Sample dataset matching your structure
data = [
    ['XXX', 'A1', 'A2', 'Q2', 'PC', 12, 12, 100],
    ['XXX', 'A1', 'A2', 'Q2', 'Tablet', 2, 4, 50],
    ['YYY', 'A4', 'A5', 'Q4', 'PC', 50, 50, 100],
    ['YYY', 'A4', 'A5', 'Q4', 'Tablet', 10, 12, 100],
    ['XXX', 'A9', 'A10', 'Q2', 'PC', 20, 20, 100],
    ['XXX', 'A11', 'A12', 'Q1', 'PC', 15, 15, 100]
]

columns = ['Country', 'category', 'brand', 'quarter', 'device', 'countA', 'CountB', 'percentageA/B']
df = pd.DataFrame(data, columns=columns)

Step 2: Generate Summary Rows #

We'll group the data by your key columns (Country, category, brand, quarter) and compute the required aggregates for each group:

# Create summary rows by grouping and aggregating
summary = df.groupby(['Country', 'category', 'brand', 'quarter']).agg(
    # Combine unique devices with '+' (sorted for consistent naming)
    device=('device', lambda x: '+'.join(sorted(x.unique()))),
    # Sum countA and CountB values
    countA=('countA', 'sum'),
    CountB=('CountB', 'sum')
).reset_index()

# Calculate and format the percentage (1 decimal place + % sign)
summary['percentageA/B'] = (summary['countA'] / summary['CountB'] * 100).round(1).astype(str) + '%'

Step 3: Combine and Sort Data #

Now we'll merge the original data with the summary rows, then sort to keep each group's original entries followed by its summary:

# Combine original data and summary rows
combined_df = pd.concat([df, summary], ignore_index=True)

# Add a sort key to ensure original rows come before summary rows
combined_df['sort_key'] = combined_df.apply(
    lambda row: 1 if row['device'] in summary['device'].values else 0,
    axis=1
)

# Sort to group entries together, with summary rows at the end of each group
final_df = combined_df.sort_values(
    by=['Country', 'category', 'brand', 'quarter', 'sort_key'],
    ascending=[True, True, True, True, True]
).drop('sort_key', axis=1)

# Print the final result
print(final_df)

Step 4: Verify the Output #

The resulting final_df will look like this:

Country category brand quarter     device  countA  CountB percentageA/B
0     XXX       A1    A2      Q2         PC      12      12           100
1     XXX       A1    A2      Q2      Tablet       2       4            50
2     XXX       A1    A2      Q2  PC+Tablet      14      16          87.5%
3     XXX       A9   A10      Q2         PC      20      20           100
4     XXX       A9   A10      Q2         PC      20      20         100.0%
5     XXX      A11   A12      Q1         PC      15      15           100
6     XXX      A11   A12      Q1         PC      15      15         100.0%
7     YYY       A4    A5      Q4         PC      50      50           100
8     YYY       A4    A5      Q4      Tablet      10      12           100
9     YYY       A4    A5      Q4  PC+Tablet      60      62          96.7%

Key Details: #

• Single-Device Groups: For groups with only one device type, the summary row uses the same device name and calculates the percentage correctly.
• Consistent Naming: Devices are sorted before joining with '+' to ensure uniformity (e.g., "PC+Tablet" instead of "Tablet+PC").
• Handling Edge Cases: The code assumes CountB is non-zero (to avoid division errors). If you need to handle zero values, add a check like summary['percentageA/B'] = np.where(summary['CountB'] == 0, 'N/A', ...).
• Sorting: The sort_key ensures original rows always appear before their group's summary row.

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