Summarise all numerical values

General
1

Hi,
I have this simple df with different types of variables:

source <- data.frame(
          stringsAsFactors = FALSE,
               check.names = FALSE,
             AccountNumber = c(1, 2, 3, 4, 5),
                   RegDate = c("2022-11-18",
                               "2022-10-29","2022-10-28","2022-10-28",
                               "2022-09-30"),
                   Mileage = c(1069, 985, 75, 238, 2133),
          LastWorkshopDate = c("2022-12-22",
                               "2022-12-21","2022-11-10","2022-11-23",
                               "2022-11-24"),
                       CWI = c("C", "W", "W", "C", "W"),
                   RoTotal = c(162, 39.67, 38.89, 38.89, 38.89),
                `Vehicle Age` = c(34, 53, 13, 26, 55),
                  `Last Seen` = c(NA, 20, NA, NA, 40),
           `Not Seen Flag` = c(1, 1, 0, 1, 0),
                       Year = c(2021, 2022, 2022, 2022, 2022),
                     Month = c(12, 12, 11, 11, 11)
)

Now, I need to calculate means and counts for all numerical variables.
I don't want to list them like here:

library(dplyr)

result <- source %>%
  mutate(Year=as.character(Year)) %>%
  bind_rows(mutate(.data = .,
                   CWI = "Total")) %>%
  group_by(Year, CWI) %>%
  summarise_at(.vars = vars(ends_with(match = "Mileage"), ends_with(match = "RoTotal")),.funs = list(Sc = ~mean(.,na.rm=TRUE), Count = ~sum(!is.na(.))))

result

Is any way of specifying I need means for all available numerical variables apart from Month and Year?

2

You made Year character, so if we only calculate means on numerics that is naturally avoided; with Month being numeric though it should be suppressed. You can use across()

... your other code .. %>% 
summarise(across(where(is.numeric) & !Month,
                   .fns = list(Sc = ~mean(.,na.rm=TRUE),
                                Count = ~sum(!is.na(.)))))

Note; this post was edit so that .funs became .fns

3

Thank you but something is not right. I always have number of rows the same as number of records, regardless my grouping for example:

result <- source %>%
  mutate(Year=as.character(Year)) %>%
  bind_rows(mutate(.data = .,
                   Year = "Total")) %>%
    group_by(Year) %>%
  summarise(across(where(is.numeric) & !Month,
                   .funs = list(Sc = ~mean(.,na.rm=TRUE),
                                Count = ~sum(!is.na(.)))))

result

In this case I should have three rows (2021, 2022 and Total) with averages of all numerical values for these two years

4

you are right, when I edited your code into mine, I missed that the old .funs param should be .fns now ; try that

1 Like
5

This topic was automatically closed 7 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.