Finding mean of variable across each month/year 在每个月/年中查找变量的均值

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

Finding mean of variable across each month/year

问题

我有一个类似于这样的数据集：

  1. > dput(df)
  2. structure(list(Date = c("3/23/21", "4/11/22", "6/30/22"), Banana_wasted = c(4L, 
  3. 2L, 5L), Apple_wasted = c(6L, 0L, 3L), Orange_wasted = c(1L, 
  4. 4L, 1L), Banana_ordered = c(5L, 7L, 7L), Apple_Ordered = c(9L, 
  5. 8L, 9L), Orange_ordered = c(5L, 6L, 6L), Banana_eaten = c(5L, 
  6. 5L, 6L), Apple_eaten = c(7L, 7L, 4L), Orange_eaten = c(8L, 8L, 
  7. 8L)), class = "data.frame", row names = c(NA, -3L))

我想要计算每个月/年水果浪费的百分比（与订购了多少水果有关）。
应该是：
（Banana_wasted+Apple_wasted+Orange_wasted）/（Banana_ordered + Apple_ordered+ Orange_ordered）

因此，对于 3/21，应该是：
(4+6+1/5+9+5)*100 = 57.9%

我想要为一年中的每个月都这样做。

英文:

I have a dataset that looks similar to this:

  1. > dput(df)
  2. structure(list(Date = c("3/23/21", "4/11/22", "6/30/22"), Banana_wasted = c(4L, 
  3. 2L, 5L), Apple_wasted = c(6L, 0L, 3L), Orange_wasted = c(1L, 
  4. 4L, 1L), Banana_ordered = c(5L, 7L, 7L), Apple_Ordered = c(9L, 
  5. 8L, 9L), Orange_ordered = c(5L, 6L, 6L), Banana_eaten = c(5L, 
  6. 5L, 6L), Apple_eaten = c(7L, 7L, 4L), Orange_eaten = c(8L, 8L, 
  7. 8L)), class = "data.frame", row.names = c(NA, -3L))

I want to find the % of fruit wasted per month/year (in relation to how many fruits were ordered).
it should be:
(Banana_wasted+Apple_wasted+Orange_wasted) / (Banana_ordered + Apple_ordered+ Orange_ordered)

So, for 3/21, it should be:
(4+6+1/5+9+5)*100 = 57.9%

I would like to do this for every month of the year.

答案1

得分: 2

  1. library(tidyverse)
  3. df %>%
  4.   group_by(Date = floor_date(mdy(Date), "month")) %>%
  5.   summarise(
  6.     wasted = sum(across(contains("wasted"))) / sum(across(contains("ordered"))),
  7.     wasted_eaten = sum(across(contains("wasted"))) / sum(across(contains("eaten")))
  8.   )
  10. # A tibble: 3 x 3
  11.   Date       wasted wasted_eaten
  12.   <date>      <dbl>        <dbl>
  13. 1 2021-03-01  0.579        0.579
  14. 2 2022-04-01  0.286        0.314
  15. 3 2022-06-01  0.409        0.523
英文: 
  1. library(tidyverse)
  3. df %&gt;%
  4.   group_by(Date = floor_date(mdy(Date), &quot;month&quot;)) %&gt;%
  5.   summarise(
  6.     wasted = sum(across(contains(&quot;wasted&quot;))) / sum(across(contains(&quot;ordered&quot;))),
  7.     wasted_eaten = sum(across(contains(&quot;wasted&quot;))) / sum(across(contains(&quot;eaten&quot;)))
  8.   )
  10. # A tibble: 3 x 3
  11.   Date       wasted wasted_eaten
  12.   &lt;date&gt;      &lt;dbl&gt;        &lt;dbl&gt;
  13. 1 2021-03-01  0.579        0.579
  14. 2 2022-04-01  0.286        0.314
  15. 3 2022-06-01  0.409        0.523

答案2

得分: 1

  1. 库(dplyr)
  2. 库(lubridate)
  5. df %&gt;% 
  6.   变异(日期 = as.Date(日期, format = &quot;%m/%d/%y&quot;),
  7.          浪费百分比 = (香蕉浪费 + 苹果浪费 + 橙子浪费) / (香蕉订购 + 苹果订购 + 橙子订购) * 100) %&gt;% 
  8.   分组依据(年份 = year(日期), 月份 = month(日期)) %&gt;% 
  9.   汇总(平均浪费百分比 = mean(浪费百分比))
  12. #&gt; # A tibble: 3 &#215; 3
  13. #&gt; # Groups:   year [2]
  14. #&gt;    year month avg_pct_wasted
  15. #&gt;   &lt;dbl&gt; &lt;dbl&gt;          &lt;dbl&gt;
  16. #&gt; 1  2021     3           57.9
  17. #&gt; 2  2022     4           28.6
  18. #&gt; 3  2022     6           40.9
英文: 
  1. library(dplyr)
  2. library(lubridate)
  5. df %&gt;% 
  6.   mutate(Date = as.Date(Date, format = &quot;%m/%d/%y&quot;),
  7.          pct_wasted = (Banana_wasted + Apple_wasted + Orange_wasted) / (Banana_ordered + Apple_Ordered + Orange_ordered) * 100) %&gt;% 
  8.   group_by(year = year(Date), month = month(Date)) %&gt;% 
  9.   summarize(avg_pct_wasted = mean(pct_wasted))
  12. #&gt; # A tibble: 3 &#215; 3
  13. #&gt; # Groups:   year [2]
  14. #&gt;    year month avg_pct_wasted
  15. #&gt;   &lt;dbl&gt; &lt;dbl&gt;          &lt;dbl&gt;
  16. #&gt; 1  2021     3           57.9
  17. #&gt; 2  2022     4           28.6
  18. #&gt; 3  2022     6           40.9

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

答案3

得分: 0

以下是翻译好的代码部分：

  1. library(dplyr)
  2. library(tidyr)
  3. library(lubridate)
  5. dat %>%
  6.   rename(Apple_ordered = Apple_Ordered) %>%
  7.   pivot_longer(
  8.     Banana_wasted:Orange_eaten,
  9.     names_to = c("水果", ".value"),
  10.     names_sep = "_"
  11.   ) %>%
  12.   group_by(month = floor_date(mdy(Date), "month")) %>%
  13.   summarize(pct_wasted = sum(wasted) / sum(ordered)) %>%
  14.   ungroup()
  16. # # 一个数据框: 3 × 2
  17. #   月份          百分比浪费
  18. #   <日期>           <dbl>
  19. # 1 2021-03-01      0.579
  20. # 2 2022-04-01      0.286
  21. # 3 2022-06-01      0.409
  1. library(scales)
  3. dat %>%
  4.   rename(Apple_ordered = Apple_Ordered) %>%
  5.   pivot_longer(
  6.     Banana_wasted:Orange_eaten,
  7.     names_to = c("水果", ".value"),
  8.     names_sep = "_"
  9.   ) %>%
  10.   group_by(month = strftime(mdy(Date), "%B %Y")) %>%
  11.   summarize(pct_wasted = percent(sum(wasted) / sum(ordered), accuracy = 0.1)) %>%
  12.   ungroup()
  14. # # 一个数据框: 3 × 2
  15. #   月份          百分比浪费
  16. #   <字符>         <字符>     
  17. # 1 April 2022  28.6%     
  18. # 2 June 2022   40.9%     
  19. # 3 March 2021  57.9%
英文:

Pivot longer to get single wasted and ordered columns across all fruits; use lubridate::floor_date() and mdy() to get months from Date; group by month; then sum and divide to get your percentages:

  1. library(dplyr)
  2. library(tidyr)
  3. library(lubridate)
  5. dat %&gt;% 
  6.   rename(Apple_ordered = Apple_Ordered) %&gt;% # for consistent capitalization
  7.   pivot_longer(
  8.     Banana_wasted:Orange_eaten,
  9.     names_to = c(&quot;Fruit&quot;, &quot;.value&quot;),
  10.     names_sep = &quot;_&quot;
  11.   ) %&gt;% 
  12.   group_by(month = floor_date(mdy(Date), &quot;month&quot;)) %&gt;% 
  13.   summarize(pct_wasted = sum(wasted) / sum(ordered)) %&gt;% 
  14.   ungroup()
  16. # # A tibble: 3 &#215; 2
  17. #   month      pct_wasted
  18. #   &lt;date&gt;          &lt;dbl&gt;
  19. # 1 2021-03-01      0.579
  20. # 2 2022-04-01      0.286
  21. # 3 2022-06-01      0.409

If you prefer character labels, use strftime() instead of floor_date(), and scales::percent() for the percentages:

  1. library(scales)
  3. dat %&gt;% 
  4.   rename(Apple_ordered = Apple_Ordered) %&gt;% 
  5.   pivot_longer(
  6.     Banana_wasted:Orange_eaten,
  7.     names_to = c(&quot;Fruit&quot;, &quot;.value&quot;),
  8.     names_sep = &quot;_&quot;
  9.   ) %&gt;% 
  10.   group_by(month = strftime(mdy(Date), &quot;%B %Y&quot;)) %&gt;% 
  11.   summarize(pct_wasted = percent(sum(wasted) / sum(ordered), accuracy = 0.1)) %&gt;% 
  12.   ungroup()
  14. # # A tibble: 3 &#215; 2
  15. #   month      pct_wasted
  16. #   &lt;chr&gt;      &lt;chr&gt;     
  17. # 1 April 2022 28.6%     
  18. # 2 June 2022  40.9%     
  19. # 3 March 2021 57.9%

huangapple
  • 本文由 发表于 2023年2月7日 02:01:10
  • 转载请务必保留本文链接：https://go.coder-hub.com/75364960.html
  • r
匿名

发表评论

匿名网友

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

确定