我有嵌套在产品 (prod_id( 中的卖家 (seller_id( 和一个随时间变化的二分类变量 (dich(。在任何给定时间点，每个产品只能有一个卖家 dich = 1。我想做的是让拥有 1 的卖家随着时间的推移具有 1 的值，直到发现另一个卖家的 dich = 1。数据按时间排序(降序( 如果您需要我澄清，请告诉我。谢谢！
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") )
测试
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
理想的结果：
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
一种方法是创建一个last_seller列来跟踪最后一个卖家的身份。 例如
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