gpt4 book ai didi

python - Pandas 通过键+日期时间和间隔合并两个数据框

转载 作者:行者123 更新时间:2023-12-05 03:29:08 25 4
gpt4 key购买 nike

我需要按时间间隔对交易记录进行排序,数据来自两个数据文件,但我不知道该怎么做。有一些示例和我的预期输出。

tier_history_data:

code,date_start,date_expiry,tier
1,"2020-01-01 15:15:15","2020-12-31 15:15:15",A
1,"2020-05-23 08:24:57","2021-05-22 08:24:57",C
2,"2020-03-01 10:47:15","2021-02-27 10:47:15",B
2,"2020-09-17 23:14:23","2021-09-16 23:14:23",C
3,"2020-05-01 20:26:19","2021-04-30 20:26:19",C
3,"2020-08-31 12:46:02","2021-08-30 12:46:02",B

交易数据:

code,transaction_datetime,amount
1,"2020-01-02 13:45:05",20
1,"2020-06-22 12:34:41",230
2,"2020-11-12 15:47:35",50
3,"2020-09-03 18:20:34",10

预期输出:

code,tramsaction_datetime,amount,tier
1,"2020-01-02 13:45:05",20,A
1,"2020-06-22 12:34:41",230,C
2,"2020-11-12 15:47:35",50,C
3,"2020-09-03 18:20:34",10,B

提前致谢

最佳答案

看起来您想要合并数据。由于您的间隔是不相交的,因此这是 merge_asof 的完美用例。 .

首先确保具有 datetime 类型并且数据在合并日期排序:

df1['date_start'] = pd.to_datetime(df1['date_start'])
df1['date_expiry'] = pd.to_datetime(df1['date_expiry'])
df2['transaction_datetime'] = pd.to_datetime(df2['transaction_datetime'])

df1 = df1.sort_values(by='date_start')
df2 = df2.sort_values(by='transaction_datetime')

然后执行合并:

df3 = (
pd.merge_asof(df2, df1, by='code',
left_on='transaction_datetime',
right_on='date_start',
)
.sort_values(by='code')
.drop(['date_start', 'date_expiry'], axis=1)
)

输出:

   code transaction_datetime  amount tier
0 1 2020-01-02 13:45:05 20 A
1 1 2020-06-22 12:34:41 230 C
3 2 2020-11-12 15:47:35 50 C
2 3 2020-09-03 18:20:34 10 B

关于python - Pandas 通过键+日期时间和间隔合并两个数据框,我们在Stack Overflow上找到一个类似的问题: https://stackoverflow.com/questions/71074981/

25 4 0
Copyright 2021 - 2024 cfsdn All Rights Reserved 蜀ICP备2022000587号
广告合作:1813099741@qq.com 6ren.com