Count the number of rows matching the criteria in another table - R PROGming

Question

I have two tables, one with property listings and the other with contacts made for the property (that is, someone is interested in the property that they will “link” to the owner).

An example of the lists table below:

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

An example of "contacts" below:

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

Problem 1. I need to count the number of contacts made for the property during the "x" days of listing. The result should be a new column added to the "lists" of C # contacts:

Example ('x' = 30 days)

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

I did this with a for loop; for live data terribly slow:

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

2. # vs "x" - . .

Answer 1

dplyr :

left_join(contacts, listings, by = c("id" = "id")) %>%
filter(abs(listing_date - contact_date) < 30) %>%
group_by(id) %>% summarise(cnt = n()) %>%
right_join(listings)

:

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

, , .

Answer 2

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

Answer 3

, id , . :

listings$id <- as.numeric(as.character(listings$id))
contacts$id <- as.numeric(as.character(contacts$id))

" " contacts data.frame. data.frame( , 30 ), .

contacts$ngs <- contacts$contact_date - listings$listing_date[match(contacts$id, listings$id)]
a <- aggregate(ngs ~ id, data = contacts, FUN = function(x) sum(x <= 30))
merge(listings, a)
#     id city listing_date ngs
# 1 2175    B   2015-03-14   3
# 2 4176    B   2015-04-07   0
# 3 6174    A   2015-03-01   2
# 4 9176    B   2015-03-30   1
# 5 9177    A   2015-04-18   1

Answer 4

dplyr. :

all.data <- merge(contacts,listings,by = "id")

:

number.of.days <- 30

(group_by), , (), / ().

result <- all.data %>% group_by(id) %>% filter(contact_date > listing_date + number.of.days) %>% summarise(count.of.contacts = length(id))

, , , , dplyr .

EDIT:

, . ,

result <- all.data %>% group_by(id,city,listing_date) %>% summarise(ngs = length(id[which(contact_date < listing_date + number.of.days)]))

I do not think that null results can be transmitted reasonably through the filter stage (of course, the goal is usually the opposite). I'm not too sure what effect the “what” component will have on processing time, it will probably be slower than using the “filter” function, but it may not matter.

Answer 5

Or:

indx <- match(contacts$id, listings$id)
days_since <- contacts$contact_date - listings$listing_date[indx]
n <- with(contacts[days_since <= 30, ], tapply(id, id, length))
n[is.na(n)] <- 0
listings$n <- n[match(listings$id, names(n))]

It is similar to Thomas's answer, but uses tapplyand matchinstead of aggregateand merge.