如何根据累积值创建分组

huangapple
go评论93阅读模式
英文:

How to create groups based on cumulative values

问题

I would like to set groups based on sum of columns

  1. ID Value Days
  2. A 0   5
  3. A 1200  20
  4. A 300   40 
  5. A 1000  67    
  6. A 1000  90
  7. A 1000  110
  8. A 500   115
  9. B 0     5
  10. B 500   10
  11. B 600   15 
  12. B 1000  25
  13. B 2000  50
  14. B 900   70
  15. B 1000  90

Output

  1. ID Group Days
  2. A Doses A 20
  3. A Doses B 90
  4. A Doses C 115
  5. B Doses A 15
  6. B Doses B 50
  7. B Doses C 70

I would like to sum values to create groups and retain the corresponding timestamps (days) when they occur.

  1. Output (creating 3 groups)
  2.             Values
  3. (Doses A) => 1000 
  4. (Doses B) => 3000 
  5. (Doses C) => 5000
英文:

I would like to set groups based on sum of columns

  1. ID Value Days
  2. A 0   5
  3. A 1200  20
  4. A 300   40 
  5. A 1000  67    
  6. A 1000  90
  7. A 1000  110
  8. A 500   115
  9. B 0     5
  10. B 500   10
  11. B 600   15 
  12. B 1000  25
  13. B 2000  50
  14. B 900   70
  15. B 1000  90

Output

  1. ID Group Days
  2. A Doses A 20
  3. A Doses B 90
  4. A Doses C 115
  5. B Doses A 15
  6. B Doses B 50
  7. B Doses C 70

I would like to sum values to create groups and retain the corresponding timestamps (days) when they occur.

  1. Output (creating 3 groups)
  2.             Values
  3. (Doses A) => 1000 
  4. (Doses B) => 3000 
  5. (Doses C) => 5000

答案1

得分: 2

  1. library(tidyverse)
  2. df %>%
  3.   mutate(Value = cumsum(Value),
  4.          Doses = (Value >= 1000) + (Value >= 3000) + (Value >= 5000), .by = ID) %>%
  5.   filter(Value >= 1000) %>%
  6.   slice_head(by = c(ID, Doses)) %>%
  7.   mutate(Doses = paste('Doses', c('A', 'B', 'C')[Doses])) %>%
  8.   select(ID, Doses, Days)
英文: 
  1. library(tidyverse)
  2. df %>%
  3.   mutate(Value = cumsum(Value),
  4.          Doses = (Value>=1000) + (Value>=3000) + (Value>=5000), .by=ID)%>%
  5.   filter(Value>=1000) %>%
  6.   slice_head(by = c(ID, Doses))%>%
  7.   mutate(Doses = paste('Doses', c('A', 'B', 'C')[Doses])) %>%
  8.   select(ID, Doses, Days)
  10. #> # A tibble: 6 × 3
  11. #>   ID    Doses    Days
  12. #>   <chr> <chr>   <dbl>
  13. #> 1 A     Doses A    20
  14. #> 2 A     Doses B    90
  15. #> 3 A     Doses C   115
  16. #> 4 B     Doses A    15
  17. #> 5 B     Doses B    50
  18. #> 6 B     Doses C    70

<sup>Created on 2023-06-28 with reprex v2.0.2</sup>

答案2

得分: 1

  1. 请尝试以下代码
  3. ```r
  4. library(dplyr)
  6. df %>% group_by(ID) %>% mutate(new=cumsum(Value),
  7.                               new2=case_when(
  8.                                 new>=5000 ~ 'C',
  9.                                 new>=3000 ~ 'B',
  10.                                 new>=1000 ~ 'A'
  11.                               )) %>%
  12.   filter(!is.na(new2)) %>%
  13.   group_by(ID,new2) %>%
  14.   slice_head(n=1) %>%
  15.   ungroup() %>%
  16.   select(-c(new,new2))
  18. # 输出
  20. # 一个 tibble: 6 × 3
  21.   ID    Value  Days
  22.   <chr> <dbl> <dbl>
  23. 1 A     1200    20
  24. 2 A     1000    90
  25. 3 A      500   115
  26. 4 B      600    15
  27. 5 B     2000    50
  28. 6 B      900    70
  2. <details>
  3. <summary>英文:</summary>
  5. Please try the below code 
  8. ```r
  9. library(dplyr)
  11. df %&gt;% group_by(ID) %&gt;% mutate(new=cumsum(Value),
  12.                               new2=case_when(
  13.                                 new&gt;=5000 ~ &#39;C&#39;,
  14.                                 new&gt;=3000 ~ &#39;B&#39;,
  15.                                 new&gt;=1000 ~ &#39;A&#39;
  16.                               )) %&gt;% 
  17.   filter(!is.na(new2)) %&gt;% 
  18.   group_by(ID,new2) %&gt;% 
  19.   slice_head(n=1) %&gt;% ungroup() %&gt;% 
  20.   select(-c(new,new2))
  22. # output 
  24. # A tibble: 6 &#215; 3
  25.   ID    Value  Days
  26.   &lt;chr&gt; &lt;dbl&gt; &lt;dbl&gt;
  27. 1 A      1200    20
  28. 2 A      1000    90
  29. 3 A       500   115
  30. 4 B       600    15
  31. 5 B      2000    50
  32. 6 B       900    70

huangapple
  • 本文由 发表于 2023年6月29日 05:51:17
  • 转载请务必保留本文链接：https://go.coder-hub.com/76576923.html
  • r
匿名

发表评论

匿名网友

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen:

确定