How to average multiple variables by date in R?

Question

I need to find a way of getting a daily average for temperature and pressure, and keep id and city name. I give example data but my data is more complex and it requires process more columns (precipitation etc.) and rows (more cities and time).

Example data:

 id      city temperature pressure                time
    1  1  new_york          15     1000 2015-01-01 06:30:00
    2  1  new_york          16     1003 2015-01-01 18:30:00
    3  3    london          13      980 2015-01-01 07:00:00
    4  3    london          12      998 2015-01-01 20:30:00
    5  5 barcelona          30     1013 2015-01-01 08:00:00
    6  5 barcelona          32     1015 2015-01-01 12:00:00

I want to get:

  id      city temperature pressure       time
1  1  new_york        15.5   1001.5 2015-01-01
2  3    london        12.5    989.0 2015-02-10
3  5 barcelona        31.0   1014.0 2015-04-08

Code to make example data:

data <- data.frame("id" = c(1,1, 3,3,5,5),
                   "city" = c("new_york", "new_york", "london", "london", "barcelona", "barcelona"),
                   "temperature" = c(15, 16, 13, 12, 30, 32),
                   "pressure" =  c(1000, 1003, 980, 998, 1013, 1015),
                   "time" = c("2015-01-01 06:30:00","2015-01-01 18:30:00",
                              "2015-02-10 07:00:00", "2015-02-10 20:30:00",
                              "2015-04-08 08:00:00", "2015-04-08 12:00:00"),stringsAsFactors = FALSE)

Aggregating data is one of the most common questions asked in R. Please do [research](https://meta.stackoverflow.com/questions/261592/how-much-research-effort-is-expected-of-stack-overflow-users) before you ask. — Parfait, Oct 19 '20 at 14:06

score 2 · Accepted Answer · answered Oct 19 '20 at 13:50

Try this

library(dplyr)
data %>% group_by(id, city, time = as.Date(time)) %>% summarise(across(c(temperature, pressure), mean))

Output

# A tibble: 3 x 5
# Groups:   id, city [3]
     id city      time       temperature pressure
  <dbl> <chr>     <date>           <dbl>    <dbl>
1     1 new_york  2015-01-01        15.5    1002.
2     3 london    2015-02-10        12.5     989 
3     5 barcelona 2015-04-08        31      1014

How to average multiple variables by date in R?

1 Answers1