时间戳

首页 > 解决方案 > 如何根据条件合并两个数据框？

r - 如何根据条件合并两个数据框？

问题描述

这是我的问题的后续问题：

这是我的交易数据

data 

id          from    to          date        amount  
<int>       <fctr>  <fctr>      <date>      <dbl>
19521       6644    6934        2005-01-01  700.0
19524       6753    8456        2005-01-01  600.0
19523       9242    9333        2005-01-01  1000.0
…           …       …           …           …
1055597     9866    9736        2010-12-31  278.9
1053519     9868    8644        2010-12-31  242.8
1052790     9869    8399        2010-12-31  372.2

现在，对于from列中的每个帐户，我想计算他们在进行交易时在过去 6 个月内收到的交易金额。去做这个：

df <- data # df is just a copy of "data"
setDT(df)[, total_trx_amount_received_in_last_6month := sapply(date, function(x) 
                         sum(amount[between(date, x-180, x)])), to] 

# since I want to merge "df" and "data" based on the columns "from" and "date", I change the name of the column "to" and make it "from"
df <- select(df, to,date,total_trx_amount_received_in_last_6month) %>% rename(from=to)

df

from    date        total_trx_amount_received_in_last_6month
<fctr>  <date>      <dbl>
7468    2005-01-04  700.0       
6213    2005-01-08  12032.0     
7517    2005-01-10  1000.0      
6143    2005-01-12  4976.0      
6254    2005-01-14  200.0       
6669    2005-01-20  200.0       
6934    2005-01-24  72160.0     
9240    2005-01-26  21061.0     
6374    2005-01-30  1000.0      
6143    2005-01-31  4989.4

现在我想将此新列添加total_trx_amount_received_in_last_6month到原始data. 因此，我应该合并这两个数据框data并按df列合并from，date但日期的匹配标准是一系列值，而不是单个值。例如帐户7468，如果原始data包含一笔交易7468并且交易日期属于间隔"2004-07-08"-"2005-01-04"（即最近 6 个月的期间，从 开始） ，则应将"2005-01-04"相应的值添加到700.0df$total_trx_amount_received_in_last_6monthdata$total_trx_amount_received_in_last_6month

我怎样才能做到这一点？

标签： rdataframedateaggregation

解决方案

没有足够的数据来测试这一点，但您可以加入两个数据框，并且replace total_trx_amount_received_in_last_6month两个日期之间的差异大于 180 天到NA.

library(dplyr)

data %>%
left_join(df, by = 'from') %>%
  mutate(total_trx_amount_received_in_last_6month = replace(
            total_trx_amount_received_in_last_6month, 
            (date.y - date.x) > 180, NA))

使用data.table，您可以执行以下操作：

library(data.table)
setDT(data)
df1 <- df[data, on = 'from']

df1[, total_trx_amount_received_in_last_6month := replace(
  total_trx_amount_received_in_last_6month, 
  (date - i.date) > 180, NA)]

推荐阅读