如何基于Pandas DataFrame的cumsum值按占比规则划分分类？

Here's how to implement your classification rules efficiently:

1. Set Up the DataFrame and Calculate Thresholds #

First, recreate your DataFrame (or use your existing one) and compute the 95% and 97.5% thresholds from the sum column:

import pandas as pd
import numpy as np

# Your data structure
data = {
    'RoomCode': ['AGA']*9,
    'Notes': ['2A2K', '2A1K', '2A4K', '2A5K', '2A7K', '2A8K', '2A9K', '2A10K', '2A11K'],
    'Qty': [14323, 4810, 4180, 3759, 1472, 783, 571, 243, 139],
    'sum': [30613]*9,
    'cumsum': [14323, 19133, 23313, 27072, 28544, 29327, 29898, 30141, 30280]
}
df = pd.DataFrame(data, index=[4302,4301,4303,4306,4307,4304,4311,4310,4312])

# Calculate threshold values
total_sum = df['sum'].iloc[0]
threshold_95 = total_sum * 0.95  # 29082.35
threshold_975 = total_sum * 0.975  # 29847.675

2. Apply Classification Rules #

Use np.select() to map each row to the correct class based on its cumsum value. This method is more efficient than apply() for larger datasets:

# Define conditions for each class
conditions = [
    df['cumsum'] <= threshold_95,
    (df['cumsum'] > threshold_95) & (df['cumsum'] <= threshold_975),
    df['cumsum'] > threshold_975
]

# Corresponding class labels
class_labels = ['Class 1', 'Class 2', 'Class 3']

# Add the new Class column to the DataFrame
df['Class'] = np.select(conditions, class_labels)

3. Final Result #

After running the code, your DataFrame will include the new Class column:

RoomCode   Notes    Qty    sum  cumsum    Class
4302       AGA   2A2K  14323  30613   14323  Class 1
4301       AGA   2A1K   4810  30613   19133  Class 1
4303       AGA   2A4K   4180  30613   23313  Class 1
4306       AGA   2A5K   3759  30613   27072  Class 1
4307       AGA   2A7K   1472  30613   28544  Class 1
4304       AGA   2A8K    783  30613   29327  Class 2
4311       AGA   2A9K    571  30613   29898  Class 3
4310       AGA  2A10K    243  30613   30141  Class 3
4312       AGA  2A11K    139  30613   30280  Class 3

Quick Note #

You mentioned index 4311 should be part of Class 2, but its cumsum (29898) exceeds the 97.5% threshold (29847.675). If you want to include this row in Class 2, adjust the upper threshold (e.g., use total_sum * 0.98 instead of 0.975).

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