[已解决]根据分组计算某列连续元素的首尾差

是 否 +2 论坛币

k人 参与回答

经管之家送您一份

应届毕业生专属福利!

求职就业群

赵安豆老师微信：zhaoandou666

经管之家联合CDA

送您一个全额奖学金名额~ !

立即领取

感谢您参与论坛问题回答

经管之家送您两个论坛币！

+2 论坛币

如下：a、b、c是原始数据，首尾差和编号是想获得的列。其中当a相同时，对b列连续TRUE对应的c列，计算首尾差。并且首尾差大于2时，进行编号。谢谢帮助！

a	b	c	首尾差	编号
2013051500_20	FALSE	123.8
2013051500_20	TRUE	120	3	1
2013051500_20	TRUE	124	3	1
2013051500_20	TRUE	125	3	1
2013051500_20	TRUE	123	3	1
2013051500_20	FALSE	125.05
2013051500_20	FALSE	125.3
2013051500_20	TRUE	125	5	2
2013051500_20	TRUE	130	5	2
2013051500_62	FALSE	103.9
2013051500_62	FALSE	103.8
2013051500_64	TRUE	103.8	0
2013051500_64	FALSE	103.87
2013051500_64	FALSE	103.93
2013051500_64	FALSE	104.05
2013051500_64	FALSE	104.14
2013051500_64	FALSE	104.3
2013051500_80	TRUE	102	4	3
2013051500_80	TRUE	106	4	3
2013051500_80	TRUE	106	4	3

扫码加我 拉你入群

请注明：姓名-公司-职位

以便审核进群资格，未注明则拒绝

分享0 收藏0 回帖

关键词：原始数据 LSE TRU Rue ABC

1. library(tidyverse)
   
2. df %>%
   
3.     group_by(a) %>%
   
4.     mutate(flag1 = b != lag(b) | is.na(lag(b)),
   
5.            flag1 = cumsum(flag1)) %>%
   
6.     group_by(a, flag1) %>%
   
7.     mutate(shouweicha = if_else(all(b), c[n()] - c[1], NA_real_)) %>%
   
8.     ungroup() %>%
   
9.     select(-flag1) %>%
   
10.     mutate(bianhao = shouweicha > 2 & is.na(lag(shouweicha)),
    
11.            bianhao = replace_na(bianhao, 0),
    
12.            bianhao = cumsum(bianhao),
    
13.            bianhao = if_else(!is.na(shouweicha) & shouweicha > 2, bianhao, NA_real_))

复制代码

cheetahfly 发表于 2020-5-7 13:02

感谢专家！输出结果正是想要的！

cheetahfly 发表于 2020-5-7 13:02

大神，之前没注意，后面发现编号有重复，两个3和两个4，请帮忙看看。

a	b	c	flag1	shouweicha	bianhao
2016060203_3	FALSE	115.8	4	NA	NA
2016060203_3	TRUE	116.05	5	3.25	3
2016060203_3	TRUE	116.3	5	3.25	3
2016060203_3	TRUE	119.24	5	3.25	3
2016060203_3	TRUE	119.3	5	3.25	3
2016060203_3	FALSE	119.55	6	NA	NA
2016060203_3	TRUE	119.8	7	0.35	NA
2016060203_3	TRUE	120.05	7	0.35	NA
2016060203_3	TRUE	120.15	7	0.35	NA
2016060203_3	FALSE	120.3	8	NA	NA
2016060203_3	FALSE	120.35	8	NA	NA
2016060203_3	FALSE	120.55	8	NA	NA
2016060203_3	FALSE	121.3	8	NA	NA
2016060203_3	TRUE	121.55	9	0.25	NA
2016060203_3	TRUE	121.71	9	0.25	NA
2016060203_3	TRUE	121.8	9	0.25	NA
2016060204_0	TRUE	108.8	1	6.25	3
2016060204_0	TRUE	109.02	1	6.25	3
2016060204_0	TRUE	109.05	1	6.25	3
2016060204_0	TRUE	115.05	1	6.25	3
2016060204_0	FALSE	115.3	2	NA	NA
2016060204_0	FALSE	115.55	2	NA	NA
2016060204_0	FALSE	115.8	2	NA	NA
2016060204_0	FALSE	115.8	2	NA	NA
2016060204_0	TRUE	116.05	3	4	4
2016060204_0	TRUE	116.3	3	4	4
2016060204_0	TRUE	120.05	3	4	4
2016060204_0	TRUE	120.05	3	4	4
2016060204_0	FALSE	120.2	4	NA	NA
2016060204_0	FALSE	120.3	4	NA	NA
2016060204_0	FALSE	121.05	4	NA	NA
2016060204_0	FALSE	121.3	4	NA	NA
2016060204_0	TRUE	121.55	5	0.25	NA
2016060204_0	TRUE	121.8	5	0.25	NA
2016060205_0	TRUE	108.8	1	6.25	4
2016060205_0	TRUE	108.87	1	6.25	4
2016060205_0	TRUE	114.8	1	6.25	4
2016060205_0	TRUE	115.05	1	6.25	4
2016060205_0	FALSE	115.3	2	NA	NA

cheetahfly 发表于 2020-5-7 13:02

网速不好，发重了，不好意思

cheetahfly 发表于 2020-5-7 13:02

网速不好，发重了，不好意思

1. library(tidyverse)
   
2. 
   
3. tmp1 <- df %>%
   
4.     group_by(a) %>%
   
5.     mutate(flag1 = b != lag(b) | is.na(lag(b)),
   
6.            flag1 = cumsum(flag1)) %>%
   
7.     group_by(a, flag1) %>%
   
8.     mutate(shouweicha = if_else(all(b), c[n()] - c[1], NA_real_)) %>%
   
9.     ungroup() %>%
   
10.     select(-flag1)
    
11. 
    
12. tmp2 <- tmp1 %>%
    
13.     filter(shouweicha > 2) %>%
    
14.     select(a, b, shouweicha) %>%
    
15.     distinct() %>%
    
16.     arrange(a) %>%
    
17.     mutate(bianhao = row_number())
    
18. 
    
19. result <- tmp1 %>%
    
20.     left_join(tmp2, by = c("a", "b", "shouweicha"))
    
21. 
    
22. result

复制代码

cheetahfly 发表于 2020-5-25 10:19

谢谢！结果是正确的