用Python的 DataFrame按日期进行滚动计算求和并拼接一个字符串

编程语言 2026-07-08

我需要在一个滚动窗口内对交易金额求和，并把构成总和的交易ID连接起来。

完整代码在底部。

下面的语句返回预期的结果。

rollingSum = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION AMOUNT'].sum()

rollingCnt = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION ID'].count()

下面的代码中，我想把构成上述和的交易ID连接起来，并对其进行计数。

如下会抛出：TypeError: must be real number, not str

rollingTransId = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION ID STR'].apply(
    lambda x: ','.join(x.dropna().astype(str)))

# -*- coding: utf-8 -*-
"""
Created on Fri Jun  5 11:26:48 2026
@author: ne098406
"""
import io
import os
import pandas as pd

DATA = """
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
"""

DATE_COL = "TRANSACTION DATE"
DATE_FORMAT = "%Y-%m-%d"
GROUP_COLS = [
    "EMPLOYEE ID",
    "ACCOUNT EMAIL ADDRESS",
    "ACCOUNT NUMBER",
    "MERCHANT NAME",
]

df = pd.read_csv(io.StringIO(DATA), parse_dates=[DATE_COL], date_format=DATE_FORMAT)
df['TRANSACTION DATE'] = pd.to_datetime(df['TRANSACTION DATE'])
df.set_index('TRANSACTION DATE', inplace=True)
df.sort_index()
df['ACCOUNT NUMBER'] = df['ACCOUNT NUMBER'].astype('Int64')
df['TRANSACTION ID STR'] = df['TRANSACTION ID'].astype('string')

##  ## df = df.sort_values(GROUP_COLS + [DATE_COL])

print(df)

rollingSum = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION AMOUNT'].sum()

rollingCnt = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION ID'].count()

rollingTransId = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", 'ACCOUNT NUMBER', 'MERCHANT NAME']
).rolling(window='11D')['TRANSACTION ID STR'].apply(
    lambda x: ','.join(x.dropna().astype(str)))

解决方案

在前一个问题的评论中，我给出了答案
pandas - python dataframe rolling by date to get sum - Stack Overflow

似乎 rolling() 只允许 apply() 这样的函数，它只能生成数字，不能生成字符串。

你可以把 rolling 转换为 pandas.Series 并使用 apply()

all_windows = df.groupby(
                  ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
              ).rolling(window="11D")["TRANSACTION ID STR"]

all_windows = pd.Series(all_windows)

rollingTransId = all_windows.apply(lambda window: ",".join(window.dropna().astype(str)))

这对我来说用你提供的代码可行，我得到的是：

0                                 5416288523001
1                                 5416288521001
2                                 5406965833001
3                                 5403179702001
4                                 5403179704001
5                   5403179704001,5403179705001
6                                 5416288517001
7                   5416288517001,5416288519001
8                                 5416288518001
9                   5416288518001,5416288520001
10                  5416288520001,5416288524001
11                                5406965834001
12                  5406965834001,5412746497001
13    5406965834001,5412746497001,5416288516001
14                                5403179703001
15                                5405062431001
16                                5416288522001
17                                5459405088001
18                                5471834423001
19                  5471834423001,5347075493001
20                                5347075494001
21                  5347075494001,5357832073001
22                                5403179700001
23                                5414434974001
24                                5414434975001
25                                5454344019001
26                                5361456559001
27                  5361456559001,5386878553001
28                                5459405089001
29                                5471834424001
30                                5401420274001
31                                5353050810001
32                                5451761818001
33                  5451761818001,5473621360001
34                                5390241877001
35                                5401420255001
36                                5425031382001
37                                5425031381001
38                                5350647122001
39                                5356156974001
40                  5356156974001,5359620179001
41                  5359620179001,5372912596001
42                                5409512783001
43                                5423349484001
44                  5423349484001,5451761817001

最终你也可以使用普通的 for-循环来运行

all_windows = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
).rolling(window="11D")["TRANSACTION ID STR"]

rollingTransId = []

for window in all_windows:
    text = ",".join(window.dropna().astype(str))
    rollingTransId.append(text)

你甚至可以把它简化为列表推导式

all_windows = df.groupby(
    ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
).rolling(window="11D")["TRANSACTION ID STR"]

rollingTransId = [
    ",".join(window.dropna().astype(str)) for window in all_windows
]

我尝试用 DataFrame 代替 Series 做同样的事情，但它不起作用——我原本以为在前一个问题中它是可行的。

完整的可运行示例。

我测试了不同的方法：for-loop、列表推导式、Series、DataFrame（这不工作）

import io
import os
import pandas as pd

DATA = """
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
"""

DATE_COL = "TRANSACTION DATE"
DATE_FORMAT = "%Y-%m-%d"
GROUP_COLS = [
    "EMPLOYEE ID",
    "ACCOUNT EMAIL ADDRESS",
    "ACCOUNT NUMBER",
    "MERCHANT NAME",
]

df = pd.read_csv(io.StringIO(DATA), parse_dates=[DATE_COL], date_format=DATE_FORMAT)
df["TRANSACTION DATE"] = pd.to_datetime(df["TRANSACTION DATE"])
df.set_index("TRANSACTION DATE", inplace=True)
df.sort_index()
df["ACCOUNT NUMBER"] = df["ACCOUNT NUMBER"].astype("Int64")
df["TRANSACTION ID STR"] = df["TRANSACTION ID"].astype("string")

##  ## df = df.sort_values(GROUP_COLS + [DATE_COL])

print("--- df ---")
print(df)

rollingSum = (
    df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    )
    .rolling(window="11D")["TRANSACTION AMOUNT"]
    .sum()
)

rollingCnt = (
    df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    )
    .rolling(window="11D")["TRANSACTION ID"]
    .count()
)


def version_0(df):
    print("\n--- original version - doesn't work ---\n")

    try:
        rollingTransId = (
            df.groupby(
                [
                    "EMPLOYEE ID",
                    "ACCOUNT EMAIL ADDRESS",
                    "ACCOUNT NUMBER",
                    "MERCHANT NAME",
                ]
            )
            .rolling(window="11D")["TRANSACTION ID STR"]
            .apply(lambda x: ",".join(x.dropna().astype(str)))
        )

        print("--- rollingTransId ---")
        print(rollingTransId)
    except Exception as ex:
        print("Ex:", ex)


def version_1_a(df):
    print("\n--- for-loop ---\n")

    all_windows = df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    ).rolling(window="11D")["TRANSACTION ID STR"]

    rollingTransId = []

    for window in all_windows:
        text = ",".join(window.dropna().astype(str))
        rollingTransId.append(text)

    print("--- rollingTransId ---")
    print(rollingTransId)


def version_1_b(df):
    print("\n---  list comprehension ---\n")

    all_windows = df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    ).rolling(window="11D")["TRANSACTION ID STR"]

    rollingTransId = [
        ",".join(window.dropna().astype(str)) for window in all_windows
    ]

    print("--- rollingTransId ---")
    print(rollingTransId)


def version_2(df):
    print("\n--- Series ---\n")

    all_windows = df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    ).rolling(window="11D")["TRANSACTION ID STR"]

    all_windows = pd.Series(all_windows)

    rollingTransId = all_windows.apply(
        lambda window: ",".join(window.dropna().astype(str))
    )

    # rollingTransId = pd.Series(all_windows).apply(
    #     lambda window: ",".join(window.dropna().astype(str))
    # )

    print("--- rollingTransId ---")
    print(rollingTransId.to_list())


def version_3(df):
    print("\n--- DataFrame ---\n")

    all_windows = df.groupby(
        ["EMPLOYEE ID", "ACCOUNT EMAIL ADDRESS", "ACCOUNT NUMBER", "MERCHANT NAME"]
    ).rolling(window="11D")["TRANSACTION ID STR"]
    # print(f"{type(all_windows)=}")

    all_windows = pd.DataFrame(all_windows)

    rollingTransId = all_windows.apply(
        lambda window: ",".join(window.dropna().astype(str))
    )

    print("--- rollingTransId ---")
    print(rollingTransId)


# --- main ---

# version_0(df)  # original   # doesn't work
version_1_a(df)  # for-loop   # OK
version_1_b(df)  # for-loop   # OK
version_2(df)  # Series       # OK
# version_3(df)  # DataFrame  # doesn't work

Python