Find the corresponding time for min and max for a day

0
votes

Below is what my data looks like. I need to find the max and min temp for each day as well as the corresponding temperature. 

   Temp          date             time
280.9876771 01-01-79    03:00:00
291.9695498 01-01-79    06:00:00
294.9583426 01-01-79    09:00:00
290.2357847 01-01-79    12:00:00
286.2944531 01-01-79    15:00:00
282.9282138 01-01-79    18:00:00
280.326689  01-01-79    21:00:00
279.2551605 02-01-79    00:00:00
281.3981824 02-01-79    03:00:00
293.076125  02-01-79    06:00:00
295.8072204 02-01-79    09:00:00

This code I tried for min and max temp for daily.

library(xts)
read.csv("hourly1.csv", header = T) -> hourly1
xts(hourly1$Temp, as.Date(hourly1$date)) -> temp_date1
apply.daily(temp_date1, min) -> mintemp1_date
apply.daily(temp_date1, max) -> maxtemp1_date

I need help regarding how to find the time of day for min and max temp

5
r

5 Answers

2
votes
library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

dataset <- read.table(text = 'Temp          date             time
280.9876771 01-01-79    03:00:00
291.9695498 01-01-79    06:00:00
294.9583426 01-01-79    09:00:00
290.2357847 01-01-79    12:00:00
286.2944531 01-01-79    15:00:00
282.9282138 01-01-79    18:00:00
280.326689  01-01-79    21:00:00
279.2551605 02-01-79    00:00:00
281.3981824 02-01-79    03:00:00
293.076125  02-01-79    06:00:00
295.8072204 02-01-79    09:00:00',
                      header = TRUE,
                      stringsAsFactors = FALSE)

dataset %>%
  group_by(date) %>%
  summarise(min_temp = min(Temp),
            min_temp_time = time[which.min(x = Temp)],
            max_temp = max(Temp),
            max_temp_time = time[which.max(x = Temp)])
#> # A tibble: 2 x 5
#>   date     min_temp min_temp_time max_temp max_temp_time
#>   <chr>       <dbl> <chr>            <dbl> <chr>        
#> 1 01-01-79     280. 21:00:00          295. 09:00:00     
#> 2 02-01-79     279. 00:00:00          296. 09:00:00

Created on 2019-06-15 by the reprex package (v0.3.0)

Hope this helps.

0
votes

Try the dplyr package. 

df <- structure(list(Temp = c(280.9876771, 291.9695498, 294.9583426, 
                              290.2357847, 286.2944531, 282.9282138, 280.326689, 279.2551605, 
                              281.3981824, 293.076125, 295.8072204), 
                     date = c("01-01-79", "01-01-79", 
                              "01-01-79", "01-01-79", "01-01-79", "01-01-79", "01-01-79", "02-01-79", 
                              "02-01-79", "02-01-79", "02-01-79"), 
                     time = c("03:00:00", "06:00:00", "09:00:00", "12:00:00", "15:00:00", "18:00:00", "21:00:00", "00:00:00", 
                               "03:00:00", "06:00:00", "09:00:00")), 
                row.names = c(NA, -11L), 
                class = c("data.frame"))

library(dplyr)
#> 
#> Attaching package: 'dplyr'
#> The following objects are masked from 'package:stats':
#> 
#>     filter, lag
#> The following objects are masked from 'package:base':
#> 
#>     intersect, setdiff, setequal, union

df %>% 
  group_by(date)%>%
  slice(which.max(Temp))
#> # A tibble: 2 x 3
#> # Groups:   date [2]
#>    Temp date     time    
#>   <dbl> <chr>    <chr>   
#> 1  295. 01-01-79 09:00:00
#> 2  296. 02-01-79 09:00:00

df %>% 
  group_by(date)%>%
  slice(which.min(Temp))
#> # A tibble: 2 x 3
#> # Groups:   date [2]
#>    Temp date     time    
#>   <dbl> <chr>    <chr>   
#> 1  280. 01-01-79 21:00:00
#> 2  279. 02-01-79 00:00:00

Created on 2019-06-15 by the reprex package (v0.3.0)

0
votes

a data.table + lubridate solution

# load libraries
library(data.table)
library(lubridate)

# load data
dt <- fread("   Temp          date             time
280.9876771 01-01-79    03:00:00
291.9695498 01-01-79    06:00:00
294.9583426 01-01-79    09:00:00
290.2357847 01-01-79    12:00:00
286.2944531 01-01-79    15:00:00
282.9282138 01-01-79    18:00:00
280.326689  01-01-79    21:00:00
279.2551605 02-01-79    00:00:00
281.3981824 02-01-79    03:00:00
293.076125  02-01-79    06:00:00
295.8072204 02-01-79    09:00:00")

# Convert date - time values to real dates:
dt[, date2 := dmy_hms(paste(date, time, sep = " "))]

# find the date - time for max temp:
dt[, date2[which(Temp == max(Temp))], by = floor_date(date2, "days")]

# find the date - time for min temp:
dt[, date2[which(Temp == min(Temp))], by = floor_date(date2, "days")]
0
votes
Thank You Guys for the help. But I have 116881 entries.
So I tried the index command in R. This fetched me the corresponding id. 

index(hourly1)[hourly1$Temp %in% maxtemp1_date] -> max_id
index(hourly1)[hourly1$Temp %in% mintemp1_date] -> min_id

Then I used the vlookup command in Excel to get the desired solution.
0
votes

In data.table

dt[, x.value.min := frollapply(x = x, n = 2, min, fill = NA, align = "right", na.rm =TRUE), by = ID]