How can I count a variable in R conditional on the value of another variable?

user_14101491 0
Viewed 90

I want to count occurrences of a variable in a dataframe by another variable, conditional on the value of a third variable. Here is my data:

Name Store        Purchase    Date
John CVS          Shampoo     1/1/2001
John CVS          Toothpaste  1/1/2001
John Whole Foods  Kombucha    1/1/2005
John Kroger       Ice Cream   1/1/2002
Jane CVS          Soap        1/1/2001
Jane Whole Foods  Crackers    1/1/2004

For each purchase, I want a count of how many previous purchases were made by the specified person, and how many previous shopping trips, like this:

Name Store        Purchase    Date     Prev_Purchase Prev_trip
John CVS          Shampoo     1/1/2001 0             0
John CVS          Toothpaste  1/1/2001 0             0
John Whole Foods  Kombucha    1/1/2005 3             2          
John Kroger       Ice Cream   1/1/2002 2             1
Jane CVS          Soap        1/1/2001 0             0
Jane Whole Foods  Crackers    1/1/2004 1             1

If I wanted the total number of purchases/trips for each person, I would use count or tapply--is there a way to adapt these functions so that the outputs are conditional on a third variable (date)?

dataframe count r
3 Answers

Maybe you can try the base R code using ave

transform(df,
  Prev_Purchase = ave(as.numeric(as.Date(Date, "%d/%m/%Y")), Name, FUN = function(x) sapply(x, function(p) sum(p > x))),
  Prev_trip = ave(as.numeric(as.Date(Date, "%d/%m/%Y")), Name, FUN = function(x) sapply(x, function(p) length(unique(x[p > x]))))
)

which gives

  Name       Store   Purchase     Date Prev_Purchase Prev_trip
1 John         CVS    Shampoo 1/1/2001             0         0
2 John         CVS Toothpaste 1/1/2001             0         0
3 John Whole Foods   Kombucha 1/1/2005             3         2
4 John      Kroger  Ice Cream 1/1/2002             2         1
5 Jane         CVS       Soap 1/1/2001             0         0
6 Jane Whole Foods   Crackers 1/1/2004             1         1

Data

df <- structure(list(Name = c("John", "John", "John", "John", "Jane", 
"Jane"), Store = c("CVS", "CVS", "Whole Foods", "Kroger", "CVS",
"Whole Foods"), Purchase = c("Shampoo", "Toothpaste", "Kombucha",
"Ice Cream", "Soap", "Crackers"), Date = c("1/1/2001", "1/1/2001", 
"1/1/2005", "1/1/2002", "1/1/2001", "1/1/2004")), class = "data.frame", row.names = c(NA,       
-6L))
user_121587570

I think it should solve your problem. If your data is huge it's better if you optimize this code chunk.

# load environment
library(lubridate)
# base function
AddInfo = function(name, date, df) {
  prev_purchase = sum(df$Name == name & df$Date < date)
  prev_trip = length(unique(filter(df, Name == name & Date < date)$Date))
  data = data.frame(
    Prev_purchase = prev_purchase,
    Prev_trip = prev_trip
  )
  return(data)
}
# define data frame
df = data.frame(
  Name = c(rep('John', 4), rep('Jane', 2)),
  Store = c('CVS', 'CVS', 'Whole Foods', 'Kroger', 'CVS', 'Whole Foods'),
  Purchase = c('Shampoo', 'Toothpaste', 'Kombucha', 'Ice Cream', 'Soap', 'Crackers'),
  Date = c('1/1/2001', '1/1/2001', '1/1/2005', '1/1/2002', '1/1/2001', '1/1/2004')
)
# transform date to POSIXct
df$Date = dmy(df$Date)
# apply function and bind the results
cols = mapply(AddInfo, df$Name, df$Date, MoreArgs = list(df), SIMPLIFY = FALSE)
cols = bind_rows(cols)
df = cbind(df, cols)

Here is the output:

  Name       Store   Purchase     Date Prev_purchase Prev_trip
1 John         CVS    Shampoo 1/1/2001             0         0
2 John         CVS Toothpaste 1/1/2001             0         0
3 John Whole Foods   Kombucha 1/1/2005             3         2
4 John      Kroger  Ice Cream 1/1/2002             2         1
5 Jane         CVS       Soap 1/1/2001             0         0
6 Jane Whole Foods   Crackers 1/1/2004             1         1
user_142023110

We could also use outer

library(dplyr)
library(lubridate)
df %>% 
   mutate(Date = dmy(Date)) %>%
   group_by(Name) %>% 
   mutate(Prev_Purchase = colSums(outer(Date, Date, FUN = "<")), 
    Prev_trip = colSums(outer(unique(Date), Date, FUN = "<")))
# A tibble: 6 x 6
# Groups:   Name [2]
#  Name  Store       Purchase   Date       Prev_Purchase Prev_trip
#  <chr> <chr>       <chr>      <date>             <dbl>     <dbl>
#1 John  CVS         Shampoo    2001-01-01             0         0
#2 John  CVS         Toothpaste 2001-01-01             0         0
#3 John  Whole Foods Kombucha   2005-01-01             3         2
#4 John  Kroger      Ice Cream  2002-01-01             2         1
#5 Jane  CVS         Soap       2001-01-01             0         0
#6 Jane  Whole Foods Crackers   2004-01-01             1         1
user_37322710
Related