r - How to get the means of all columns but one?

Question

I have a data like below :

> dplyr::tbl_df(sbp)

Country X1980       X1981       X1982       X1983       X1984       X1985
Albania 132.9270    133.0296    133.1459    133.1868    133.2048    133.2577        
Algeria 132.4093    132.1710    131.9649    131.7835    131.6161    131.4345        
Andorra 140.8585    140.1076    139.3727    138.6457    137.9525    137.3192    

I want to get mean of values for each year for all countries and add a row like World to the end of the dataframe, so that I can plot the change of the mean value through years, in that format.

I tried using gather() so that I have a data with three columns only, like Country-year-value. However I can not think of a way to calculate the mean for the world.

Country year    sbp
Albania X1980   132.9270        
Algeria X1980   132.4093        
Andorra X1980   140.8585    

Can you please advise?

Answer 1

This is a great use case for apply, no transformations from your original format necessary:

1 means to calculate across rows, and we select columns 2:6

df1$mean <- apply(df1[,2:6], 1, mean)

  Country    X1980    X1981    X1982    X1983    X1984    X1985     mean
1 Albania 132.9270 133.0296 133.1459 133.1868 133.2048 133.2577 133.0988
2 Algeria 132.4093 132.1710 131.9649 131.7835 131.6161 131.4345 131.9890
3 Andorra 140.8585 140.1076 139.3727 138.6457 137.9525 137.3192 139.3874

You don't really want to add a summary row to your primary table, that's how you might do it in Excel, but in R it's better practice to calculate it separately.

To get the means for each year, we can also use apply, this time using 2 in the apply function to calculate down columns:

apply(df1[,2:6], 2, mean)


   X1980    X1981    X1982    X1983    X1984 
135.3983 135.1027 134.8278 134.5387 134.2578