phaser phaser - 3 months ago 18
R Question

R: averaging 15 min data to hourly for two columns of data in data frame

My data looks like this (with several other columns that I removed for simplicity.)

Index Date Time Humid Temp id
93 4/3/16 12:00:00 AM 63.8 46.7 RSOSW
94 4/3/16 12:15:00 AM 60.3 47.8 RSOSW
95 4/3/16 12:30:00 AM 64.4 46.2 RSOSW
96 4/3/16 12:45:00 AM 60.4 46.8 RSOSW
97 4/3/16 1:00:00 AM 61.3 46.6 RSOSW
98 4/3/16 1:15:00 AM 68.5 44.3 RSOSW
99 4/3/16 1:30:00 AM 70.5 43.4 RSOSW
100 4/3/16 1:45:00 AM 75.1 41.8 RSOSW
101 4/3/16 2:00:00 AM 74.9 41.3 RSOSW
102 4/3/16 2:15:00 AM 73.6 41.1 RSOSW
103 4/3/16 2:30:00 AM 72.8 41.2 RSOSW
104 4/3/16 2:45:00 AM 71.1 41.2 RSOSW
93 4/3/16 12:00:00 AM 64.9 47.8 RSOSE
94 4/3/16 12:15:00 AM 61.2 48.9 RSOSE
95 4/3/16 12:30:00 AM 63.3 45.3 RSOSE
96 4/3/16 12:45:00 AM 62.6 42.3 RSOSE
97 4/3/16 1:00:00 AM 60.9 49.9 RSOSE
98 4/3/16 1:15:00 AM 67.3 45.3 RSOSE
99 4/3/16 1:30:00 AM 72.1 42.1 RSOSE
100 4/3/16 1:45:00 AM 79.0 40.5 RSOSE
101 4/3/16 2:00:00 AM 73.4 42.3 RSOSE
102 4/3/16 2:15:00 AM 73.6 40.1 RSOSE
103 4/3/16 2:30:00 AM 71.9 46.5 RSOSE
104 4/3/16 2:45:00 AM 70.6 45.4 RSOSE


I would like to get the hourly mean Temp and Humidity by id. The result I am looking for is: (I would like to keep the other removed-for-simplicity columns of the data with each record.)

Date Hour Humid Temp id
4/3/16 00 62.225 46.875 RSOSW
4/3/16 01 68.85 44.025 RSOSW
4/3/16 02 73.1 41.2 RSOSW
4/3/16 00 63 46.075 RSOSE
4/3/16 01 69.825 44.45 RSOSE
4/3/16 02 72.375 43.575 RSOSE


Update

Index Date Time Humid Temp serialnum id farm location
93 4/3/16 12:00:00 AM 63.8 46.7 1310014696 RSOSW_16 River School Outside
94 4/3/16 12:15:00 AM 60.3 47.8 1310014696 RSOSW_16 River School Outside
95 4/3/16 12:30:00 AM 64.4 46.2 1310014696 RSOSW_16 River School Outside
96 4/3/16 12:45:00 AM 60.4 46.8 1310014696 RSOSW_16 River School Outside
97 4/3/16 1:00:00 AM 61.3 46.6 1310014696 RSOSW_16 River School Outside
98 4/3/16 1:15:00 AM 68.5 44.3 1310014696 RSOSW_16 River School Outside


serialnum, id, farm and location are all characters.

Thanks in advance.

Answer
library(lubridate)
df[,2] <- mdy_hms(df[,2])

df %>% mutate(hour = hour(df[,2])) %>% 
  group_by(id, hour) %>% summarise_at(vars(Humid, Temp), mean)

Results are as follows

Source: local data frame [6 x 4]
Groups: id [?]

      id  hour  Humid   Temp
  <fctr> <int>  <dbl>  <dbl>
1  RSOSE     0 63.000 46.075
2  RSOSE     1 69.825 44.450
3  RSOSE     2 72.375 43.575
4  RSOSW     0 62.225 46.875
5  RSOSW     1 68.850 44.025
6  RSOSW     2 73.100 41.200

If you want to keep the columns as they are and replace the values with the means you calculated you can do

df %>% mutate(hour = hour(df[,2])) %>% 
  group_by(id, hour) %>% mutate_at(vars(Humid, Temp), mean) %>% head

And it will result in

Source: local data frame [6 x 6]
Groups: id, hour [2]

Index            datetime  Humid   Temp     id  hour
<int>              <time>  <dbl>  <dbl> <fctr> <int>
  1    93 2016-04-03 00:00:00 62.225 46.875  RSOSW     0
2    94 2016-04-03 00:15:00 62.225 46.875  RSOSW     0
3    95 2016-04-03 00:30:00 62.225 46.875  RSOSW     0
4    96 2016-04-03 00:45:00 62.225 46.875  RSOSW     0
5    97 2016-04-03 01:00:00 68.850 44.025  RSOSW     1
6    98 2016-04-03 01:15:00 68.850 44.025  RSOSW     1

Cleaning your Data (please post dput next time)

df <- read.table(text =
                 "93 4/3/16 12:00:00 AM  63.8 46.7 RSOSW
                 94 4/3/16 12:15:00 AM  60.3 47.8 RSOSW
                 95 4/3/16 12:30:00 AM  64.4 46.2 RSOSW
                 96 4/3/16 12:45:00 AM  60.4 46.8 RSOSW
                 97 4/3/16  1:00:00 AM  61.3 46.6 RSOSW
                 98 4/3/16  1:15:00 AM  68.5 44.3 RSOSW
                 99 4/3/16  1:30:00 AM  70.5 43.4 RSOSW
                 100 4/3/16  1:45:00 AM  75.1 41.8 RSOSW
                 101 4/3/16  2:00:00 AM  74.9 41.3 RSOSW
                 102 4/3/16  2:15:00 AM  73.6 41.1 RSOSW
                 103 4/3/16  2:30:00 AM  72.8 41.2 RSOSW
                 104 4/3/16  2:45:00 AM  71.1 41.2 RSOSW
                 93 4/3/16 12:00:00 AM  64.9 47.8 RSOSE
                 94 4/3/16 12:15:00 AM  61.2 48.9 RSOSE
                 95 4/3/16 12:30:00 AM  63.3 45.3 RSOSE
                 96 4/3/16 12:45:00 AM  62.6 42.3 RSOSE
                 97 4/3/16  1:00:00 AM  60.9 49.9 RSOSE
                 98 4/3/16  1:15:00 AM  67.3 45.3 RSOSE
                 99 4/3/16  1:30:00 AM  72.1 42.1 RSOSE
                 100 4/3/16  1:45:00 AM  79.0 40.5 RSOSE
                 101 4/3/16  2:00:00 AM  73.4 42.3 RSOSE
                 102 4/3/16  2:15:00 AM  73.6 40.1 RSOSE
                 103 4/3/16  2:30:00 AM  71.9 46.5 RSOSE
                 104 4/3/16  2:45:00 AM  70.6 45.4 RSOSE")

df[,2] <- paste(df[,2], df[,3], df[,4])
df <- df[,c(-3,-4)]

names(df) <- c("Index", "datetime", "Humid", "Temp", "id")