Creating a new row which is the average of all the columns

Question

I would like to add a new row at the bottom of my dataframe, which shows the average of value of each of the columns. For example

The result I would like would be:

A    B    C    D
1    2    3    4
2    3    NA   5
3    4    5    NA 
4    5    6    NA
10/4 14/4 14/3 9/2

I have many columns with missing values (NA), also many columns have a different amount of entries, and I have 900 columns.

Is it possible to do this?

Many Thanks

```{r} Error in colMeans(Excess_Fund_R) : 'x' must be numeric ``` Is this because my first column is the date? Thanks Darren — kg23, Aug 16 '20 at 09:36
@kg23 so what value should be in your date column in the tow with the averages? — Allan Cameron, Aug 16 '20 at 09:40
I would like no value in the date column if possible? I would like the average of each column, I don't mind if this is presented as a new row or new column — kg23, Aug 16 '20 at 09:47
Try `library(dplyr); df %>% bind_rows(summarise(df, across(A:D, mean, na.rm = TRUE)))` — arg0naut91, Aug 16 '20 at 09:55

score 1 · Accepted Answer · answered Aug 16 '20 at 09:56

It sounds like your data is more like this:

df <- structure(list(Date = c("2020-01-01", "2020-01-02", "2020-01-03", 
"2020-01-04"), A = 1:4, B = 2:5, C = 3:6, D = 4:7), class = "data.frame", 
row.names = c(NA, -4L))

df
#>         Date A B C D
#> 1 2020-01-01 1 2 3 4
#> 2 2020-01-02 2 3 4 5
#> 3 2020-01-03 3 4 5 6
#> 4 2020-01-04 4 5 6 7

In which case, you could do something like

rbind(df, sapply(df, function(x) if(is.numeric(x)) mean(x, na.rm = TRUE) else ""))
#>         Date   A   B   C   D
#> 1 2020-01-01   1   2   3   4
#> 2 2020-01-02   2   3   4   5
#> 3 2020-01-03   3   4   5   6
#> 4 2020-01-04   4   5   6   7
#> 5            2.5 3.5 4.5 5.5

^{Created on 2020-08-16 by the reprex package (v0.3.0)}

@kg23 Beware that all columns will be converted into the character type. Replacing `sapply()` with `lapply()` can fix it and be safer. — Darren Tsai, Aug 16 '20 at 10:06

score 0 · Answer 2 · answered Aug 16 '20 at 10:01

0

You can take mean of only numeric column :

library(dplyr)
bind_rows(df, df %>% summarise(across(where(is.numeric), mean, na.rm = TRUE)))

answered Aug 16 '20 at 10:01

Ronak Shah

377,200
20
156
213

Creating a new row which is the average of all the columns

2 Answers2