Get mean of all columns of a dataframe R

user_13257034 0
Viewed 156

I have a data frame that consists of multiple columns. Each column represents a day in a year (I have 365 columns) and each row is the mean temperature of a specific city. I would like to get the mean of all columns, so I get a mean of temperature for the entire year. I would also like to get the mean of each month (that is mean of 01(January), 02(February), and so on), and also get the mean per quarter of a year.

My data looks something like this

data <- data.frame(City = c("London", "Stockholm", "Paris", "Prag", "Berlin", "Copenhagen"), 
                   20100101 = c(4, 5, 3, 4, 6, 7), 20100102 = c(2, 5, 8, 6, 1, 3), 
                   20100205 = c(4, 7, 6, 1, 3, 4), 20100305 = c(0, 3, 7, 9, 3, 2), 
                   20100525 = c(9, 8, 7, 6, 5, 4), 20100719 = c(9, 10, 5, 6, 7, 8), 
                   20101011 = c(15, 3, 5, 7, 8, 9), 20101112 = c(3, 7, 1, 1, 1, 1), 
                   20101212 = c(0, 0, 0, 5, 2, 1))

How can I extract the mean of the year, each month, and each quarter of a year?

mean multiple-columns dataframe r sum
2 Answers

It would be much easier to handle this if you get data in long format.

library(dplyr)

long_data <- data %>% 
             tidyr::pivot_longer(cols = -City) %>% 
             mutate(name = as.Date(name, '%Y%m%d'))

Once you have that you can get yearly, quarterly, monthly mean temperature easily for each city.

long_data %>%
  group_by(City) %>%
  summarise(year_mean = mean(value,na.rm = TRUE))

Monthly mean :

long_data %>%
  group_by(City, month = lubridate::month(name)) %>%
  #For quarter
  #group_by(City, quarter = quarter(name)) %>%
  summarise(month_mean = mean(value,na.rm = TRUE))
user_39629140

We could do this in base R with rowMeans and split.default

# // convert the date columns to `Date` class
dates <- as.Date(names(data)[-1], "%Y%m%d")
# // get the row wise mean of numeric columns (except the first column)
city_means <- rowMeans(data[-1])
names(city_means) <- data$City
 
# // split the data into list of data.frame based on the month
# // loop over the list with sapply and get the rowMeans
month_means <- sapply(split.default(data[-1], format(dates, "%b")),
      rowMeans, na.rm = TRUE)
row.names(month_means) <- data$City

# // split by year quarters and get the rowMeans for each list element
quarter_means <- sapply(split.default(data[-1], paste(format(dates, "%Y"), 
               quarters(dates))), rowMeans, na.rm = TRUE)
row.names(quarter_means) <- data$City

data

data <- structure(list(City = c("London", "Stockholm", "Paris", "Prag", 
"Berlin", "Copenhagen"), `20100101` = c(4, 5, 3, 4, 6, 7), `20100102` = c(2, 
5, 8, 6, 1, 3), `20100205` = c(4, 7, 6, 1, 3, 4), `20100305` = c(0, 
3, 7, 9, 3, 2), `20100525` = c(9, 8, 7, 6, 5, 4), `20100719` = c(9, 
10, 5, 6, 7, 8), `20101011` = c(15, 3, 5, 7, 8, 9), `20101112` = c(3, 
7, 1, 1, 1, 1), `20101212` = c(0, 0, 0, 5, 2, 1)), 
class = "data.frame", row.names = c(NA, 
-6L))
user_37322710
Related