基于status列值聚合非before标记行的数据集处理请求

开发者阿泽

2026-5-15

Got it, let's tackle this problem. You need to keep rows where status = 'before' completely as-is, while aggregating the sum of QT_REC for rows with other status values (grouped by CASHPOINT_ID, DT, and status). Here are two practical solutions depending on the tool you're using:

Solution 1: Using SQL

We can split the logic into two parts and combine them with UNION ALL: the unmodified "before" rows, and the aggregated rows for all other statuses.

-- Keep all rows where status is 'before' (no changes)
SELECT CASHPOINT_ID, DT, status, QT_REC
FROM your_table
WHERE status = 'before'

UNION ALL

-- Aggregate rows where status is NOT 'before' (sum QT_REC)
SELECT CASHPOINT_ID, DT, status, SUM(QT_REC) AS QT_REC
FROM your_table
WHERE status != 'before'
GROUP BY CASHPOINT_ID, DT, status

-- Optional: Sort to match your expected output order
ORDER BY DT, CASE WHEN status = 'before' THEN 1 ELSE 2 END;

Note: Most SQL dialects automatically ignore NULL/NA values in SUM(), which matches your expected result (for 2016-01-03, the NA row is excluded from the sum, leaving 16).

Solution 2: Using Python Pandas

Split the DataFrame into two subsets, aggregate the non-"before" group, then concatenate everything back together.

import pandas as pd

# Load your sample data
data = {
    'CASHPOINT_ID': ['N053360330']*6,
    'DT': ['2016-01-01']*2 + ['2016-01-02']*2 + ['2016-01-03']*2,
    'status': ['end_of_day', 'end_of_day', 'before', 'before', 'end_of_day', 'end_of_day'],
    'QT_REC': [5, 2, 9, pd.NA, 16, pd.NA]
}
df = pd.DataFrame(data)

# Split into two groups
before_rows = df[df['status'] == 'before']
non_before_rows = df[df['status'] != 'before']

# Aggregate the non-before group (sum QT_REC)
aggregated_non_before = non_before_rows.groupby(
    ['CASHPOINT_ID', 'DT', 'status'], 
    as_index=False
)['QT_REC'].sum()

# Combine and sort to match your desired output
final_result = pd.concat([aggregated_non_before, before_rows]) \
    .sort_values(by=['DT', 'status'], ascending=[True, False]) \
    .reset_index(drop=True)

print(final_result)

Running this code will output exactly the dataset you're expecting:

CASHPOINT_ID         DT     status  QT_REC
0   N053360330 2016-01-01  end_of_day     7.0
1   N053360330 2016-01-02      before     9.0
2   N053360330 2016-01-02      before     NaN
3   N053360330 2016-01-03  end_of_day    16.0

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