r - 计算另一个表中满足条件的行数 - R PROgramming

Question

我有两张 table ，一张是房产 list ，另一张是为房产建立的联系方式(即有人对房产感兴趣，他们将“联系”业主)。

下面的示例“列表”表:

listings <- data.frame(id = c("6174", "2175", "9176", "4176", "9177"), city = c("A", "B", "B", "B" ,"A"), listing_date = c("01/03/2015", "14/03/2015", "30/03/2015", "07/04/2015", "18/04/2015"))
listings$listing_date <- as.Date(listings$listing_date, "%d/%m/%Y")

listings
#    id city listing_date
#1 6174    A   01/03/2015
#2 2175    B   14/03/2015
#3 9176    B   30/03/2015
#4 4176    B   07/04/2015
#5 9177    A   18/04/2015

下面的示例“联系人”表:

contacts <- data.frame (id = c ("6174", "6174", "6174", "6174", "2175", "2175", "2175", "9176", "9176", "4176", "4176", "9177"), contact_date = c("13/03/2015","14/04/2015", "27/03/2015", "13/04/2015", "15/03/2015", "16/03/2015", "17/03/2015", "30/03/2015", "01/06/2015", "08/05/2015", "09/05/2015", "23/04/2015" ))

contacts$contact_date <- as.Date(contacts$contact_date, "%d/%m/%Y")
contacts
#     id contact_date
#1  6174   2015-03-13
#2  6174   2015-04-14
#3  6174   2015-03-27
#4  6174   2015-04-13
#5  2175   2015-03-15
#6  2175   2015-03-16
#7  2175   2015-03-17
#8  9176   2015-03-30
#9  9176   2015-06-01
#10 4176   2015-05-08
#11 4176   2015-05-09
#12 9177   2015-04-23

问题
1. 我需要计算在上市后“x”天内为某房产建立的联系人数。输出应该是添加到“列表”的新列，其中包含 # 个联系人:

样本('x' = 30 天)

listings
#    id city listing_date ngs
#1 6174    A   2015-03-01   2
#2 2175    B   2015-03-14   3
#3 9176    B   2015-03-30   1
#4 4176    B   2015-04-07   0
#5 9177    A   2015-04-18   1

我已经用 for 循环做到了这一点；实时数据非常慢:

n <- nrow(listings)
mat <- vector ("integer", n)
for (i in 1:n) {  
  mat[i] <- nrow (contacts[contacts$id==listings[i,"id"] & as.numeric (contacts$contact_date - listings[i,"listing_date"]) <=30,])
}
listings$ngs <- mat

我需要准备一个 #contacts vs days 的直方图，其中 'x' 作为变量 - 通过操纵函数。我想不出在操纵函数中完成所有这些操作的方法。

Answer 1

这是使用 data.table 的可能解决方案滚动连接

library(data.table)

# key `listings` by proper columns in order perform the binary join
setkey(setDT(listings), id, listing_date)

# Perform a binary rolling join while extracting matched icides and counting them
indx <- data.table(listings[contacts, roll = 30, which = TRUE])[, .N, by = V1]

# Joining back to `listings` by proper rows while assigning the counts by reference
listings[indx$V1, ngs := indx$N]
#      id city listing_date ngs
# 1: 2175    B   2015-03-14   3
# 2: 4176    B   2015-04-07  NA
# 3: 6174    A   2015-03-01   2
# 4: 9176    B   2015-03-30   1
# 5: 9177    A   2015-04-18   1