按日期对Python DataFrame进行滚动求和
我需要回溯若干天的交易金额总和。10天可以作为起点。
我有几笔备用金交易，涉及几个人，我想对一段滚动日期区间内的支出进行求和。问题在于：交易必须按 "EMPLOYEE ID"、"ACCOUNT EMAIL ADDRESS"、'ACCOUNT NUMBER'、'MERCHANT NAME' 分组。雇员可能有多个账户。我们关心的是按商户的支出。
同一天可能有相同的交易。
测试数据看起来是这样的：
TRANSACTION DATE,EMPLOYEE ID,ACCOUNT EMAIL ADDRESS,ACCOUNT NUMBER,MERCHANT NAME,EXPENSE DESCRIPTION,TRANSACTION ID,TRANSACTION AMOUNT
2025-12-02,52846,[email protected],846,HARBOR FREIGHT,electrician tool,5454344019001,131.18
2025-12-10,52846,[email protected],456,Lowes,Pipe Insulation and fitting,5459405088001,34.79
2025-12-20,52846,[email protected],846,nino's pizza,lunch,5459405089001,98.98
2025-12-31,52846,[email protected],456,Lowes,nuts and bolts for maintenance,5471834423001,56.47
2026-01-01,52846,[email protected],846,nino's pizza,lunch,5471834424001,139
2026-01-06,52846,[email protected],456,Lowes,Cable Cutter Pliers,5347075493001,83.98
2026-01-27,52846,[email protected],456,local hardware,paint,5347075494001,242.37
2026-01-29,52846,[email protected],456,local hardware,Pipe Insulation and fitting,5357832073001,15.62
2026-02-02,52846,[email protected],846,corner store,OFFICE CLOCK,5361456559001,29.44
2026-02-05,52846,[email protected],846,corner store,office supplies,5386878553001,716.2
2026-02-09,52846,[email protected],846,pepe's pizza,lunch,5401420274001,642.2
2026-02-11,52846,[email protected],456,nino's pizza,lunch,5403179700001,29.15
2026-03-03,52846,[email protected],456,nino's pizza,lunch,5414434974001,105.9
2026-04-10,52846,[email protected],456,nino's pizza,lunch,5414434975001,191.27
2025-12-02,77400,[email protected],707,Duffy's Lumber,Nails,5350647122001,154.7
2025-12-10,77400,[email protected],400,Duffy's Lumber,screws,5353050810001,316.62
2025-12-23,77400,[email protected],707,Duffy's Lumber,Fittings for compressor,5356156974001,170.02
2025-12-31,77400,[email protected],707,Duffy's Lumber,fuses,5359620179001,62.84
2026-01-03,77400,[email protected],707,Duffy's Lumber,Nails,5372912596001,366.8
2026-01-06,77400,[email protected],400,HARBOR FREIGHT,bolts,5390241877001,748
2026-01-27,77400,[email protected],400,HARBOR FREIGHT,Cable Cutter Pliers,5401420255001,200
2026-01-29,77400,[email protected],707,HARBOR FREIGHT,bolts,5409512783001,47.49
2026-02-02,77400,[email protected],707,WB Mason,whiteboard calendar & desk calendar,5423349484001,34.91
2026-02-05,77400,[email protected],400,WB Mason,OFFICE CHAIR,5425031381001,48.99
2026-02-09,77400,[email protected],400,HARBOR FREIGHT,electrician tool,5425031382001,152.9
2026-02-11,77400,[email protected],707,WB Mason,mouse pad,5451761817001,34.03
2026-04-10,77400,[email protected],400,Duffy's Lumber,sump pump,5451761818001,25.73
2026-04-15,77400,[email protected],400,Duffy's Lumber,Insulation pipe and wrap,5473621360001,189.99
2025-12-03,11608,[email protected],116,corner store,spare keys,5403179702001,14.25
2025-12-10,11608,[email protected],608,corner store,coffee donuts,5403179703001,256.9
2025-12-20,11608,[email protected],116,corner store,coffee and donuts for all hands meeting,5403179704001,45.98
2025-12-20,11608,[email protected],116,corner store,Bottled Water,5403179705001,139.98
2025-12-31,11608,[email protected],608,corner store,Batteries,5405062431001,24.12
2026-01-03,11608,[email protected],116,WB Mason,envelopes,5406965833001,67.9
2026-01-05,11608,[email protected],608,Staples,whiteboard calendar & desk calendar,5406965834001,73.93
2026-01-06,11608,[email protected],608,Staples,office supplies,5412746497001,327
2026-01-06,11608,[email protected],608,Staples,office supplies,5416288516001,92.98
2026-01-25,11608,[email protected],116,nino's pizza,lunch for crew,5416288517001,43.75
2026-01-29,11608,[email protected],608,Lowes,electrician tool,5416288518001,54.99
2026-02-02,11608,[email protected],116,nino's pizza,meatball hero,5416288519001,39.4
2026-02-05,11608,[email protected],608,Lowes,nuts and bolts for maintenance,5416288520001,68.03
2026-02-09,11608,[email protected],116,Lowes,Cable Cutter Pliers,5416288521001,75.04
2026-02-11,11608,[email protected],608,nino's pizza,large pie,5416288522001,41.96
2026-03-03,11608,[email protected],116,Duffy's Lumber,tape measure,5416288523001,142.52
2026-02-15,11608,[email protected],608,Lowes,3/4 plywood,5416288524001,59.99
对于交易，按 "EMPLOYEE ID"、"ACCOUNT EMAIL ADDRESS"、'ACCOUNT NUMBER'、'MERCHANT NAME' 进行分组。我希望对前十天的交易金额求和。
代码看起来是这样的：
DIRECTORY = 'C:\\Users\\xxx\\OneDrive\\dev\\P-Card\\test_MockTrans\\'
P_CARD_TRANS = "pCardMockTrans.csv"
P_CARD_TRANS_ROLLING = "pCardMockTranswRolling.xlsx"
P_CARD_TRANS_FILE = os.path.join(DIRECTORY, P_CARD_TRANS)
P_CARD_TRANS_ROLLING_FILE = os.path.join(DIRECTORY, P_CARD_TRANS_ROLLING)
df_pCardTrans = pd.read_csv(P_CARD_TRANS_FILE)
df_pCardTrans.set_index('TRANSACTION DATE' , inplace=True)
df_pCardTrans.sort_index()
df_pCardTrans['ACCOUNT NUMBER'] = df_pCardTrans['ACCOUNT NUMBER'] .astype('Int64')
print ( df_pCardTrans.dtypes )
df_pCardTrans["sumTransAmt"] = df_pCardTrans['TRANSACTION AMOUNT'].groupby([ "EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER' , 'MERCHANT NAME'] ).rolling(window = '10D').sum()
df_pCardTrans["sumTransAmt"] = df_pCardTrans['TRANSACTION AMOUNT'].groupby([ "EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER' , 'MERCHANT NAME'] ).rolling(window = '10D').sum().reset_index(level=0, drop=True)
df_pCardTrans.to_excel( P_CARD_TRANS_ROLLING_FILE )
我尝试了这段代码的多次迭代，但都失败了。
我得到错误 *** KeyError: 'EMPLOYEE ID' 和重复错误。我不理解原因。
解决方案
我发现了一些问题，在此描述。
This df_pCardTrans['TRANSACTION AMOUNT'].groupby() gets single column and runs groupby only on this column - so it can't find other columns and this gives KeyError: 'EMPLOYEE ID'
你必须在完整数据框上运行 groupby()：df_pCardTrans.groupby()
Later you may need to use ["TRANSACTION AMOUNT"] to calculate sum() only for this column.
稍后你可能需要使用 ["TRANSACTION AMOUNT"] 来仅对这一列计算 sum()。
Other problem makes window="10D" because index (column TRANSACTION DATE) has strings and it needs to convert them to objects datetime。
另一个问题导致 window="10D"，因为索引（列 TRANSACTION DATE）包含字符串，需要把它们转换为对象 datetime。
df_pCardTrans["TRANSACTION DATE"] = pd.to_datetime(df_pCardTrans["TRANSACTION DATE"])
Finall problem is that groupby() creates multindex and it has problem to assign it to other data in dataframe. Even reseting index doesn't helps.
最终的问题是 groupby() 会创建多级索引（MultiIndex），它在将其分配给数据框中的其他数据时会出现问题。即使重置索引也无济于事。
You may have to assign it to separate variable, and display it to see what to do next with this result.
你可能需要把它分配给一个单独的变量，并显示它以查看接下来对这个结果该如何处理。
Full code used for tests:
import os
import pandas as pd
# DIRECTORY = "C:\\Users\\xxx\\OneDrive\\dev\\P-Card\\test_MockTrans\\"
DIRECTORY = "."
P_CARD_TRANS = "pCardMockTrans.csv"
# P_CARD_TRANS_ROLLING = "pCardMockTranswRolling.xlsx"
P_CARD_TRANS_FILE = os.path.join(DIRECTORY, P_CARD_TRANS)
# P_CARD_TRANS_ROLLING_FILE = os.path.join(DIRECTORY, P_CARD_TRANS_ROLLING)
df_pCardTrans = pd.read_csv(P_CARD_TRANS_FILE)
print(f"{len(df_pCardTrans)=}")
df_pCardTrans["TRANSACTION DATE"] = pd.to_datetime(df_pCardTrans["TRANSACTION DATE"])
df_pCardTrans.set_index("TRANSACTION DATE", inplace=True)
df_pCardTrans.sort_index()
print(df_pCardTrans.index)
df_pCardTrans["ACCOUNT NUMBER"] = df_pCardTrans["ACCOUNT NUMBER"].astype("Int64")
print(df_pCardTrans.dtypes)
result = (
df_pCardTrans.groupby(
[
"EMPLOYEE ID",
"ACCOUNT EMAIL ADDRESS",
"ACCOUNT NUMBER",
"MERCHANT NAME",
]
)
.rolling("10D")["TRANSACTION AMOUNT"]
.sum()
)
print('--- result ---')
print(result)
print('--- result.reset_index(level=0, drop=True) ---')
print(result.reset_index(level=0, drop=True))
print('--- result.reset_index(drop=True) ---')
print(result.reset_index(drop=True))
# df_pCardTrans.to_excel(P_CARD_TRANS_ROLLING_FILE)
result:
EMPLOYEE ID ACCOUNT EMAIL ADDRESS ACCOUNT NUMBER MERCHANT NAME TRANSACTION DATE
11608 [email protected] 116 Duffy's Lumber 2026-03-03 142.52
Lowes 2026-02-09 75.04
WB Mason 2026-01-03 67.90
corner store 2025-12-03 14.25
2025-12-20 45.98
2025-12-20 185.96
nino's pizza 2026-01-25 43.75
2026-02-02 83.15
608 Lowes 2026-01-29 54.99
2026-02-05 123.02
2026-02-15 59.99
Staples 2026-01-05 73.93
2026-01-06 400.93
2026-01-06 493.91
corner store 2025-12-10 256.90
2025-12-31 24.12
nino's pizza 2026-02-11 41.96
52846 [email protected] 456 Lowes 2025-12-10 34.79
2025-12-31 56.47
2026-01-06 140.45
local hardware 2026-01-27 242.37
2026-01-29 257.99
nino's pizza 2026-02-11 29.15
2026-03-03 105.90
2026-04-10 191.27
846 HARBOR FREIGHT 2025-12-02 131.18
corner store 2026-02-02 29.44
2026-02-05 745.64
nino's pizza 2025-12-20 98.98
2026-01-01 139.00
pepe's pizza 2026-02-09 642.20
77400 [email protected] 400 Duffy's Lumber 2025-12-10 316.62
2026-04-10 25.73
2026-04-15 215.72
HARBOR FREIGHT 2026-01-06 748.00
2026-01-27 200.00
2026-02-09 152.90
WB Mason 2026-02-05 48.99
707 Duffy's Lumber 2025-12-02 154.70
2025-12-23 170.02
2025-12-31 232.86
2026-01-03 429.64
HARBOR FREIGHT 2026-01-29 47.49
WB Mason 2026-02-02 34.91
2026-02-11 68.94
Name: TRANSACTION AMOUNT, dtype: float64
result.reset_index(level=0, drop=True):
ACCOUNT EMAIL ADDRESS ACCOUNT NUMBER MERCHANT NAME TRANSACTION DATE
[email protected] 116 Duffy's Lumber 2026-03-03 142.52
Lowes 2026-02-09 75.04
WB Mason 2026-01-03 67.90
corner store 2025-12-03 14.25
2025-12-20 45.98
2025-12-20 185.96
nino's pizza 2026-01-25 43.75
2026-02-02 83.15
608 Lowes 2026-01-29 54.99
2026-02-05 123.02
2026-02-15 59.99
Staples 2026-01-05 73.93
2026-01-06 400.93
2026-01-06 493.91
corner store 2025-12-10 256.90
2025-12-31 24.12
nino's pizza 2026-02-11 41.96
[email protected] 456 Lowes 2025-12-10 34.79
2025-12-31 56.47
2026-01-06 140.45
local hardware 2026-01-27 242.37
2026-01-29 257.99
nino's pizza 2026-02-11 29.15
2026-03-03 105.90
2026-04-10 191.27
846 HARBOR FREIGHT 2025-12-02 131.18
corner store 2026-02-02 29.44
2026-02-05 745.64
nino's pizza 2025-12-20 98.98
2026-01-01 139.00
pepe's pizza 2026-02-09 642.20
[email protected] 400 Duffy's Lumber 2025-12-10 316.62
2026-04-10 25.73
2026-04-15 215.72
HARBOR FREIGHT 2026-01-06 748.00
2026-01-27 200.00
2026-02-09 152.90
WB Mason 2026-02-05 48.99
707 Duffy's Lumber 2025-12-02 154.70
2025-12-23 170.02
2025-12-31 232.86
2026-01-03 429.64
HARBOR FREIGHT 2026-01-29 47.49
WB Mason 2026-02-02 34.91
2026-02-11 68.94
Name: TRANSACTION AMOUNT, dtype: float64
result.reset_index(drop=True):
0 142.52
1 75.04
2 67.90
3 14.25
4 45.98
5 185.96
6 43.75
7 83.15
8 54.99
9 123.02
10 59.99
11 73.93
12 400.93
13 493.91
14 256.90
15 24.12
16 41.96
17 34.79
18 56.47
19 140.45
20 242.37
21 257.99
22 29.15
23 105.90
24 191.27
25 131.18
26 29.44
27 745.64
28 98.98
29 139.00
30 642.20
31 316.62
32 25.73
33 215.72
34 748.00
35 200.00
36 152.90
37 48.99
38 154.70
39 170.02
40 232.86
41 429.64
42 47.49
43 34.91
44 68.94
Name: TRANSACTION AMOUNT, dtype: float64
站内所有文章版权归属LeftHeroAI导航站，无授权禁止任何主体转载、抄袭、复制内容，亦不得私自架设镜像站点。一经侵权，本站将通过法律途径追责。
相关文章
汇集全球数千款 AI 工具，每日持续更新，配套 AI 开发学习资源，轻松用 AI 提升工作效率。快捷键 Ctrl+D/⌘+D 一键收藏本站。