Easily check whether an objective was recorded in other variable?

user_4806121 0
Viewed 86

I have customer id, product_id1 and product_id2. The data have customers' purchasing information and sorted by customer and time so 1st row of each customer is the oldest record.
product_id1 includes purchased items product_id2 includes items that I want to know whether each of item is purchased before (check from the product_id1).

For each item in product_id2 for each customer, I want to create dummy variable that indicated whether each of item in product_id2 is purchased in the past
In other words, a value in product_id2 at nth row has been shown at least one time in 1~(n-1)th rows in product_id1, the purchased_before is 1 otherwise 0.

So I want to create "purchased_before" column.

I can do it by using for loop but is there any efficient way?

Data is as below, 

customer id      product_id1   product_id2     purchased_before
    1             112             113                 0
    1             115             114                 0
    1             113             113                 0
    1             114             113                 1
    1             115             114                 1
    ....
    2             112             115                 0
    2             115             112                 1
    2             113             113                 0
data.table dplyr plyr dataframe r
2 Answers

This can solved using a non-equi join and aggregating while joining:

library(data.table)
setDT(DT)[
  # add "time variable", i.e., row id to identify earlier purchases
  , rn := .I][
    # create new column with ...
    , cnt_of_earlier_purchases := 
      # ... the result of the non-equi join aggregate
      DT[DT, on = .(customer_id, product_id1 = product_id2, rn < rn), .N, by = .EACHI]$N][]

   customer_id product_id1 product_id2 rn cnt_of_earlier_purchases
1:           1         112         113  1                        0
2:           1         115         114  2                        0
3:           1         113         113  3                        0
4:           1         114         113  4                        1
5:           1         115         114  5                        1
6:           2         112         115  6                        0
7:           2         115         112  7                        1
8:           2         112         113  8                        0
9:           2         115         112  9                        2

The new column contains the count of purchases before the actual purchase.

Note that a modified sample data set with multiple purchases has been used to demonstrate the effect of counting the purchases.

Alternatively, a logical value can be appended instead of the count:

setDT(DT)[, rn := .I][
  , purchased_before := 
    DT[DT, on = .(customer_id, product_id1 = product_id2, rn < rn), .N, by = .EACHI]$N > 0][]

   customer_id product_id1 product_id2 rn purchased_before
1:           1         112         113  1            FALSE
2:           1         115         114  2            FALSE
3:           1         113         113  3            FALSE
4:           1         114         113  4             TRUE
5:           1         115         114  5             TRUE
6:           2         112         115  6            FALSE
7:           2         115         112  7             TRUE
8:           2         112         113  8            FALSE
9:           2         115         112  9             TRUE

Data

library(data.table)
DT <- fread(
"customer_id      product_id1   product_id2     purchased_before
    1             112             113                 0
    1             115             114                 0
    1             113             113                 0
    1             114             113                 1
    1             115             114                 1
    2             112             115                 0
    2             115             112                 1
    2             112             113                 0
    2             115             112                 0", select = 1:3)
user_38170040

Try the following:

dplyr:

df %>%
    group_by(customer_id) %>%
    mutate(purchased_before = sapply(row_number(), function(x) {
               ifelse(product_id2[x] %in% product_id1[1:(x-1)], 1, 0)
           })
    )

base R:

do.call(rbind, lapply(split(df, df$customer_id), function(x) {
    x$purchased_before <- sapply(seq_len(nrow(x)), function(y) {
        ifelse(x$product_id2[y] %in% x$product_id1[1:(y-1)], 1, 0)
    })
    x
}))

The main thing here is looping through the row numbers in the product_id2 column and using those row numbers to access the product_id2 value at a given index, along with product_id1 values from 1 until that given index. Once you have those values, you can carry out a simple match operator inside an ifelse. If there is a match, you assign 1; or 0 otherwise.

I hope this proves useful.

user_31354170
Related