r - 如何计算列内的累积值

Question

我正在尝试计算几个年级之间的累积时间。

这是我原来的 df 的样子:

df = data.frame(id = c(1,1,1,1,2,2,2,2),
                group = c(0,0,0,0,1,1,1,1),
                grade = c(0,1,2,3,0,1,3,4),
                time = c(10,7,4,1,20,17,14,11))

这是我所期望的结果 df1:

df1 = df %>%
  pivot_wider(
    names_from = "grade",
    names_prefix = "grade_",
    values_from = "time") %>%
  replace(is.na(.), 0) %>%
  
  mutate(grade_1 = grade_1 + grade_2 + grade_3 + grade_4,
         grade_2 = grade_2 + grade_3 + grade_4,
         grade_3 = grade_3 + grade_4) %>%
  
  pivot_longer(
    cols = 3:7,
    names_to = "grade",
    names_prefix = "grade_",
    values_to = "time") 

我的方法有效，但我希望它更灵活。当我在df中有更多成绩时，我不需要手动添加grade_x = Grade_1 + Grade_2 + Grade_3 ...

谢谢!

Answer 1

一种选择是重新排列 grade 列，然后执行 cumsum 使其相反。但是，我们排除了最后一行，其中 grade == 0。然后，我们可以按所需的顺序重新排列并取消组合。

library(tidyverse)

results <- df %>%
  group_by(id) %>%
  arrange(id, desc(grade)) %>%
  mutate(time = ifelse(row_number()!=n(), cumsum(time), time)) %>%
  arrange(id, grade) %>%
  ungroup

输出

     id group grade  time
  <dbl> <dbl> <dbl> <dbl>
1     1     0     0    10
2     1     0     1    12
3     1     0     2     5
4     1     0     3     1
5     2     1     0    20
6     2     1     1    42
7     2     1     3    25
8     2     1     4    11

如果您需要每个组的行数与您想要的输出中的行数相同，那么您可以使用 complete:

df %>%
  tidyr::complete(id, grade) %>% 
  group_by(id) %>% 
  fill(group, .direction ="downup") %>% 
  replace(is.na(.), 0) %>% 
  arrange(id, desc(grade)) %>%
  mutate(time = ifelse(row_number()!=n(), cumsum(time), time)) %>%
  arrange(id, grade) %>%
  ungroup

输出

      id grade group  time
   <dbl> <dbl> <dbl> <dbl>
 1     1     0     0    10
 2     1     1     0    12
 3     1     2     0     5
 4     1     3     0     1
 5     1     4     0     0
 6     2     0     1    20
 7     2     1     1    42
 8     2     2     1    25
 9     2     3     1    25
10     2     4     1    11

或者如果你想来回旋转，那么你可以这样做:

output <- df %>%
  pivot_wider(
    names_from = "grade",
    names_prefix = "grade_",
    values_from = "time") %>%
  replace(is.na(.), 0) %>%
  select(id, group, grade_0, last_col():grade_1)

results2 <- output %>% 
  select(-c(id, group, grade_0)) %>% 
  rowwise()%>% 
  do(data.frame(t(cumsum(unlist(.))))) %>% 
  bind_cols(select(output, id, group, grade_0), .) %>% 
  pivot_longer(
    cols = 3:7,
    names_to = "grade",
    names_prefix = "grade_",
    values_to = "time")