CODEWITHSUNDEEP
rdataframedplyr
Kreitz Gigs
Kreitz Gigs

Reputation: 379

coding status change in binary variable over time based on two nested id variables in R

I have sellers (seller_id) who are nested within products (prod_id) and a dichotomous variable (dich) that changes over time. There can only be one seller with dich = 1 per product at any given time point. What I want to do is have the seller who has 1 to have the value of 1 over time until another seller is found to have dich = 1. The data are sorted by time (descending) Please let me know if you need me to clarify. Thanks!

test <- data.frame('prod_id'= c("shoe", "shoe", "shoe", "shoe", "shoe", "shoe", "boat", "boat","boat","boat","boat","boat"), 
               'seller_id'= c("a", "a", "b", "c", "c", "a", "a","a", "b", "b", "c", "b"), 
               'Dich'= c(1, 0, 0, 1, 0, 0, 0, 0, 1, 0, 0, 0), 
               'time'= c("10/25/2017 9:40", "11/8/2017 9:36", "11/14/2017 21:02", "11/29/2017 23:20", "12/5/2017 20:30",
                "12/10/2017 17:38", "12/26/2017 8:00", "1/27/2018 6:26", "4/10/2018 5:40",
                "4/24/2018 20:16", "5/18/2018 21:52", "8/9/2018 9:52") )

test

    prod_id seller_id Dich            time
1     shoe         a    1  10/25/2017 9:40
2     shoe         a    0   11/8/2017 9:36
3     shoe         b    0 11/14/2017 21:02
4     shoe         c    1 11/29/2017 23:20
5     shoe         c    0  12/5/2017 20:30
6     shoe         a    0 12/10/2017 17:38
7     boat         a    0  12/26/2017 8:00
8     boat         a    0   1/27/2018 6:26
9     boat         b    1   4/10/2018 5:40
10    boat         b    0  4/24/2018 20:16
11    boat         c    0  5/18/2018 21:52
12    boat         b    0    8/9/2018 9:52

Ideal outcome:

     prod_id  seller_id Dich          time
1     shoe         a    1  10/25/2017 9:40
2     shoe         a    1   11/8/2017 9:36
3     shoe         b    0 11/14/2017 21:02
4     shoe         c    1 11/29/2017 23:20
5     shoe         c    1  12/5/2017 20:30
6     shoe         a    0 12/10/2017 17:38
7     boat         a    0  12/26/2017 8:00
8     boat         a    0   1/27/2018 6:26
9     boat         b    1   4/10/2018 5:40
10    boat         b    1  4/24/2018 20:16
11    boat         c    0  5/18/2018 21:52
12    boat         b    1    8/9/2018 9:52

Upvotes: 0

Views: 102

Answers (1)

Weihuang Wong
Weihuang Wong

Reputation: 13118

One approach is to create a last_seller column to track the identify of the last seller. E.g.

library(dplyr)
library(tidyr)

test %>%
  group_by(prod_id) %>%
  mutate(seller_id = as.character(seller_id), 
         last_seller = ifelse(Dich == 1, seller_id, NA)) %>%
  fill(last_seller) %>%
  mutate(Dich1 = ifelse((seller_id != last_seller) | is.na(last_seller), 0, 1))
#    prod_id seller_id  Dich time             last_seller Dich1
#    <fct>   <chr>     <dbl> <fct>            <chr>       <dbl>
#  1 boat    a             0 12/26/2017 8:00  NA              0
#  2 boat    a             0 1/27/2018 6:26   NA              0
#  3 boat    b             1 4/10/2018 5:40   b               1
#  4 boat    b             0 4/24/2018 20:16  b               1
#  5 boat    c             0 5/18/2018 21:52  b               0
#  6 boat    b             0 8/9/2018 9:52    b               1
#  7 shoe    a             1 10/25/2017 9:40  a               1
#  8 shoe    a             0 11/8/2017 9:36   a               1
#  9 shoe    b             0 11/14/2017 21:02 a               0
# 10 shoe    c             1 11/29/2017 23:20 c               1
# 11 shoe    c             0 12/5/2017 20:30  c               1
# 12 shoe    a             0 12/10/2017 17:38 c               0

Upvotes: 2

Related Questions