How can I add a row with the mean of each numeric column

user_12968928 0
Viewed 803

I have a dataframe summarized as pivot table and I want to add a row with the mean of each numeric column, and for the character column the row can be named as "mean"

the sample dataframe is below

dat <- c('2000-01-15','2003-01-15','2000-02-15',
         '2003-02-15','2000-04-15','2002-04-15',
         '2000-12-15','2002-12-15','2003-12-13', "2003-12-15",'2002-02-21','2002-01-25','2003-04-24')

df <- data.frame(date =as.Date(dat), id = c(1,2,3,4,5,6,7,8,9,10,11,12,13),
                 sales = c(134,211,2000,234,421,400,34,1233,1222,1034,8034,1234,2331))

df <- df %>% 
  mutate(year = format(date, "%Y"),
                    month = format(date, "%b")) %>% select(-date) %>%
  group_by(year,month) %>%
  summarise(revenue = sum(sales))

df2 <- df %>% pivot_wider(id_cols = year, names_from = month, values_from = revenue)

From here I want to do

rbind(df2, summarise_all(df2, mean))

However, the main complication is on how to return the mean of just the numeric column and return a character with non numeric column.

My desrired output should be

  year    Apr   Dec   Feb   Jan
  <chr> <dbl> <dbl> <dbl> <dbl>
1 2000    421    34  2000   134
2 2002    400  1233  8034  1234
3 2003   2331  2256   234   211
4 mean   1051. 1174. 3423.  526.
dplyr tidyverse pivot-table dataframe r
2 Answers

We may adorn_totals to add a sum row at the end, and then divide by the total number of rows - 1

library(dplyr)
library(janitor)
df2 %>% 
    adorn_totals(name = "mean") %>%
    mutate(across(where(is.numeric), 
          ~ replace(., n(), .[n()]/(n()-1)))) %>%
    as_tibble

-output

# A tibble: 4 x 5
  year    Apr   Dec   Feb   Jan
  <chr> <dbl> <dbl> <dbl> <dbl>
1 2000   421    34  2000   134 
2 2002   400  1233  8034  1234 
3 2003  2331  2256   234   211 
4 mean  1051. 1174. 3423.  526.

Or another option is to make use of summarise with across to concatenate (c()) a mean value at the end

df2 %>%
    ungroup %>% 
    summarise(year = c(year, 'mean'),
         across(where(is.numeric), ~ c(., mean(.))))

-output

# A tibble: 4 x 5
  year    Apr   Dec   Feb   Jan
  <chr> <dbl> <dbl> <dbl> <dbl>
1 2000   421    34  2000   134 
2 2002   400  1233  8034  1234 
3 2003  2331  2256   234   211 
4 mean  1051. 1174. 3423.  526.

Or use add_row from tibble

library(tibble)
df2 %>% 
   ungroup %>% 
   add_row(year = 'mean', !!! colMeans(.[-1]))
# A tibble: 4 x 5
  year    Apr   Dec   Feb   Jan
  <chr> <dbl> <dbl> <dbl> <dbl>
1 2000   421    34  2000   134 
2 2002   400  1233  8034  1234 
3 2003  2331  2256   234   211 
4 mean  1051. 1174. 3423.  526.
user_37322710

Base R option -

rbind(df2, data.frame(year = 'mean', t(colMeans(df2[-1]))))

#  year    Apr   Dec   Feb   Jan
#  <chr> <dbl> <dbl> <dbl> <dbl>
#1 2000   421    34  2000   134 
#2 2002   400  1233  8034  1234 
#3 2003  2331  2256   234   211 
#4 mean  1051. 1174. 3423.  526.

If you don't know the position of numeric columns and want to identify them dynamically, you may use -

cols <- sapply(df2, is.numeric)
rbind(df2, data.frame(year = 'mean', t(colMeans(df2[cols]))))
user_39629140
Related