按R中的组查找相邻列的行累积和的最大值

发布于04月16日

我有这个数据框架.我想找到的是每个组相邻且值=1的最大行累积和.例如，组1相邻有第1、2、3、4年，并且值加起来=4，而第6年=1，因此组1的最大值为4，等等.下面是数据帧的示例和所需的结果.谢谢你

df <- data.frame(group = c(1:6),
             Year1 = c('1','0','0','0','0','0'),
             Year2 = c('1','1','1','0','1','1'),
             Year3 = c('1','1','0','0','1','0'),
             Year4 = c('1','1','1','0','1','1'),
             Year5 = c('0','0','1','1','1','1'),
             Year6 = c('1','0','0','0','0','1'))

df
      group Year1 Year2 Year3 Year4 Year5 Year6
1     1     1     1     1     1     0     1
2     2     0     1     1     1     0     0
3     3     0     1     0     1     1     0
4     4     0     0     0     0     1     0
5     5     0     1     1     1     1     0
6     6     0     1     0     1     1     1
result <- data.frame(group=c(1:6), value = c(4, 3, 2, 1, 4, 3))

result
  group value
1     1     4
2     2     3
3     3     2
4     4     1
5     5     4
6     6     3

推荐答案

没有自定义功能的tidyverse解决方案:

library(tidyverse)
# ----------------

df %>%
  pivot_longer(starts_with("Year")) %>% 
  mutate(id = consecutive_id(group, value)) %>% 
  add_count(group, id) %>% 
  filter(value == 1) %>% 
  summarise(.by = c(group), value = max(n))

输出:

# A tibble: 6 × 2
  group value
  <int> <int>
1     1     4
2     2     3
3     3     2
4     4     1
5     5     4
6     6     3

如果您愿意，您可以从1和0中获取值:

df %>%
  pivot_longer(starts_with("Year")) %>% 
  mutate(id = consecutive_id(group, value)) %>% 
  add_count(group, id) %>% 
  summarise(.by = c(group, value), n_value = max(n)) %>% 
  pivot_wider(names_from = value, values_from = n_value, names_prefix = "val_")

# Output for both values 
# A tibble: 6 × 3
  group val_1 val_0
  <int> <int> <int>
1     1     4     1
2     2     3     2
3     3     2     1
4     4     1     4
5     5     4     1
6     6     3     1

或者，使用purrr::map_int和rle:

df$value <- map_int(
  df$group,
  \(x) max( rle(df[x, -1])$lengths[ rle(df[x, -1])$values == 1 ] ))

# Output
> df[, c("group", "value")]
  group value
1     1     4
2     2     3
3     3     2
4     4     1
5     5     4
6     6     3