Why cant I get transmute to average these two values?-CodePudding

I'm trying to create a transmute function of average productivity between two months, but I keep messing it up somehow. Not sure if its my bad math or bad dplyr usage. Here is the script, including the dput:

##################################################
#        Productivity Script for Stack           #
##################################################

# Loading Data:

work <- structure(list(Case_Number = 30:40, Year = c("2021", "2021", 
"2021", "2021", "2021", "2021", "2021", "2021", "2021", "2021", 
"2021"), Month_Number = c("9", "9", "9", "9", "10", "10", "10", 
"10", "10", "10", "10"), Month_Name = c("September", "September", 
"September", "September", "October", "October", "October", "October", 
"October", "October", "October"), Day_Number = c(27L, 28L, 29L, 
30L, 1L, 2L, 3L, 4L, 5L, 6L, 7L), Day_Name = c("Monday", "Tuesday", 
"Wednesday", "Thursday", "Friday", "Saturday", "Sunday", "Monday", 
"Tuesday", "Wednesday", "Thursday"), Time_Wake = c(600L, 730L, 
650L, 830L, 630L, 630L, 830L, 722L, 641L, 800L, 720L), Start_Work = c(950L, 
1108L, 1430L, 955L, 1313L, 1125L, 1636L, 1126L, 1027L, 1323L, 
1003L), End_Work = c(1504L, 1430L, 1933L, 1335L, 1330L, 2130L, 
1636L, 1600L, 1730L, 1800L, 1739L), Mins_Sleep = c(310L, 390L, 
350L, 450L, 390L, 390L, 510L, 452L, 310L, 360L, 500L), Workout_Y_N = c("Y", 
"N", "N", "N", "N", "Y", "Y", "Y", "N", "Y", "Y"), Workout_Before_After = c("Before", 
NA, NA, NA, NA, "Before", "Before", "Before", NA, "Before", "Before"
), Time_Workout = c(730L, NA, NA, NA, NA, 915L, 1000L, 815L, 
NA, 900L, 740L), Work_Environment = c("Office", "Office", "Office", 
"Office", "Home", "Home", "Home", "Office", "Office", "Office", 
"Office"), Coffee_Cups = c(0L, 0L, 0L, 2L, 0L, 1L, 2L, 3L, 2L, 
2L, 4L), Tea_Cups = c(4L, 4L, 4L, 2L, 1L, 0L, 2L, 0L, 0L, 4L, 
2L), Mins_Work = c(266L, 196L, 198L, 220L, 17L, 382L, 0L, 180L, 
343L, 207L, 263L), Weather = c(NA, NA, "Sunny", "Sunny", NA, 
NA, NA, NA, NA, NA, NA)), class = "data.frame", row.names = c(NA, 
-11L))

# Calculate Productivity Growth:

september_work <- work %>% 
  filter(Month_Name=="September") %>% 
  summarize(September_Mins = (sum(Mins_Work))) # calcs sep work

october_work <- work %>% 
  filter(Month_Name == "October") %>% 
  summarize(October_Mins = (sum(Mins_Work, na.rm = T))) # calcs oct work

work_growth <- ((october_work - september_work) / september_work) # calc growth
work_growth # gives percentage of productivity growth

# Create average of work between October and September:

work %>% 
  transmute(avg_month_work = sum(september_work   october_work)/2)

CodePudding user response：

Here is an all-in-one that should work with multiple months/years. It provides a rolling % difference between consecutive months and and a cumulative monthly average

work %>%
  #get sums by month
  group_by(Month_Name, Month_Number, Year) %>%
  summarize(monthly_mins = sum(Mins_Work)) %>%
  #put in order
  mutate(Month_Number = as.numeric(Month_Number),
         counter = 1) %>%
  arrange(Year, Month_Number) %>%
  ungroup() %>%
  #set up rolling pct change calc and cumulative average
  transmute(
    Year, 
    Month_Name,
    pct_change = (monthly_mins / lag(monthly_mins) - 1) * 100,
    avg_month_work = cumsum(monthly_mins) / cumsum(counter)
  )