All geek questions in one place

Extract hourly maximum / min / median timestamp in R

I have a data block with temperature measurements every 10 minutes. Measurements were taken at different locations (called "LCZs"), for each location the values ​​are in a different column.

This is part of my data frame: (it also contains missing NA values)

 Time `LCZ 3-2` `LCZ 3-10` `LCZ 6-1` `LCZ 6-9` `LCZ 9-4`


               <dttm>     <dbl>      <dbl>     <dbl>     <dbl>     <dbl>
 1 2017-08-26 17:00:00      27.5       27.5      27.5      27.0      27.0
 2 2017-08-26 17:10:00      27.5       27.0      27.5      27.0      27.0
 3 2017-08-26 17:20:00      27.5       27.0      27.0      27.0      27.0
 4 2017-08-26 17:30:00      27.0       26.5      27.0      26.5      26.5
 5 2017-08-26 17:40:00      26.5       26.5      26.5      26.5      26.5
 6 2017-08-26 17:50:00      26.5       26.0      26.5      26.0      26.5
 7 2017-08-26 18:00:00      26.5       26.0      26.5      26.5      26.5
 8 2017-08-26 18:10:00      27.0       26.0      26.5      26.5      26.0
 9 2017-08-26 18:20:00      26.5       26.5      26.5      26.5      26.0
10 2017-08-26 18:30:00      26.5       26.5      26.5      26.5      26.0

I want each location or column to calculate the hourly minimum / maximum / median temperature and, in addition, for the hourly minimum / max, I also use the time stamp from the source data, at which it was min / max, respectively.

Is this possible with R?

I have tried various functions already.

group_by min/max , . period.apply min/max/, . , aggregate() - .

R, .

, . - ?

+4
4

floor_date lubridate Time2 . , , round_date ceiling_date. gather tidyr .

library(dplyr)
library(tidyr)
library(lubridate)

dat2 <- dat %>%
  mutate(Time = ymd_hms(Time),
         Time2 = floor_date(Time, unit = "hour")) %>%
  gather(LCZ, Value, starts_with("LCZ")) %>%
  group_by(Time2, LCZ)

LCZ Time2.

dat3 <- dat2 %>%
  summarise(Min = min(Value, na.rm = TRUE),
            Max = max(Value, na.rm = TRUE),
            Median = median(Value, na.rm = TRUE)) %>%
  ungroup()
dat3
# # A tibble: 10 x 5
#    Time2               LCZ        Min   Max Median
#    <dttm>              <chr>    <dbl> <dbl>  <dbl>
#  1 2017-08-26 17:00:00 LCZ.3.10  26.0  27.5   26.8
#  2 2017-08-26 17:00:00 LCZ.3.2   26.5  27.5   27.2
#  3 2017-08-26 17:00:00 LCZ.6.1   26.5  27.5   27.0
#  4 2017-08-26 17:00:00 LCZ.6.9   26.0  27.0   26.8
#  5 2017-08-26 17:00:00 LCZ.9.4   26.5  27.0   26.8
#  6 2017-08-26 18:00:00 LCZ.3.10  26.0  26.5   26.2
#  7 2017-08-26 18:00:00 LCZ.3.2   26.5  27.0   26.5
#  8 2017-08-26 18:00:00 LCZ.6.1   26.5  26.5   26.5
#  9 2017-08-26 18:00:00 LCZ.6.9   26.5  26.5   26.5
# 10 2017-08-26 18:00:00 LCZ.9.4   26.0  26.5   26.0

, , , , . , .

dat4 <- dat2 %>%
  mutate(Min = (Value == min(Value, na.rm = TRUE)) + 0L,
         Max = (Value == max(Value, na.rm = TRUE)) + 0L,
         Median = (Value == median(Value, na.rm = TRUE)) + 0L) %>%
  ungroup()
dat4
# # A tibble: 50 x 7
#    Time                Time2               LCZ     Value   Min   Max Median
#    <dttm>              <dttm>              <chr>   <dbl> <int> <int>  <int>
#  1 2017-08-26 17:00:00 2017-08-26 17:00:00 LCZ.3.2  27.5     0     1      0
#  2 2017-08-26 17:10:00 2017-08-26 17:00:00 LCZ.3.2  27.5     0     1      0
#  3 2017-08-26 17:20:00 2017-08-26 17:00:00 LCZ.3.2  27.5     0     1      0
#  4 2017-08-26 17:30:00 2017-08-26 17:00:00 LCZ.3.2  27.0     0     0      0
#  5 2017-08-26 17:40:00 2017-08-26 17:00:00 LCZ.3.2  26.5     1     0      0
#  6 2017-08-26 17:50:00 2017-08-26 17:00:00 LCZ.3.2  26.5     1     0      0
#  7 2017-08-26 18:00:00 2017-08-26 18:00:00 LCZ.3.2  26.5     1     0      1
#  8 2017-08-26 18:10:00 2017-08-26 18:00:00 LCZ.3.2  27.0     0     1      0
#  9 2017-08-26 18:20:00 2017-08-26 18:00:00 LCZ.3.2  26.5     1     0      1
# 10 2017-08-26 18:30:00 2017-08-26 18:00:00 LCZ.3.2  26.5     1     0      1
# # ... with 40 more rows

DATA

dat <- read.table(text = "Time 'LCZ 3-2' 'LCZ 3-10' 'LCZ 6-1' 'LCZ 6-9' 'LCZ 9-4'
                  '2017-08-26 17:00:00'      27.5       27.5      27.5      27.0      27.0
                  '2017-08-26 17:10:00'      27.5       27.0      27.5      27.0      27.0
                  '2017-08-26 17:20:00'      27.5       27.0      27.0      27.0      27.0
                  '2017-08-26 17:30:00'      27.0       26.5      27.0      26.5      26.5
                  '2017-08-26 17:40:00'      26.5       26.5      26.5      26.5      26.5
                  '2017-08-26 17:50:00'      26.5       26.0      26.5      26.0      26.5
                  '2017-08-26 18:00:00'      26.5       26.0      26.5      26.5      26.5
                  '2017-08-26 18:10:00'      27.0       26.0      26.5      26.5      26.0
                  '2017-08-26 18:20:00'      26.5       26.5      26.5      26.5      26.0
                  '2017-08-26 18:30:00'      26.5       26.5      26.5      26.5      26.0",
                  header = TRUE, stringsAsFactors = FALSE)
+4

dplyr :

library(lubridate)

df %>%
  gather(Location, Temp, -Time) %>%
  group_by(Date = date(Time), HoD = hour(Time), Location) %>%
  mutate_at(.vars = "Temp", .funs = list(Min = min, Max = max, Median = median)) %>%
  filter(Temp == Min | Temp == Max) %>%
  arrange(Location, Time) %>%
  distinct(Temp, .keep_all = T) %>%
  mutate(MinMax = ifelse(Temp == Min, "MinTime", "MaxTime")) %>%
  dplyr::select(-Temp) %>%
  spread("MinMax", "Time")

:

NA, , , .

# A tibble: 10 x 8
# Groups:   Date, HoD, Location [10]
   Location Date         HoD   Min   Max Median MaxTime             MinTime            
   <chr>    <date>     <int> <dbl> <dbl>  <dbl> <chr>               <chr>              
 1 LCZ.3.10 2017-08-26    17  26.0  27.5   26.8 2017-08-26 17:00:00 2017-08-26 17:50:00
 2 LCZ.3.10 2017-08-26    18  26.0  26.5   26.2 2017-08-26 18:20:00 2017-08-26 18:00:00
 3 LCZ.3.2  2017-08-26    17  26.5  27.5   27.2 2017-08-26 17:00:00 2017-08-26 17:40:00
 4 LCZ.3.2  2017-08-26    18  26.5  27.0   26.5 2017-08-26 18:10:00 2017-08-26 18:00:00
 5 LCZ.6.1  2017-08-26    17  26.5  27.5   27.0 2017-08-26 17:00:00 2017-08-26 17:40:00
 6 LCZ.6.1  2017-08-26    18  26.5  26.5   26.5 NA                  2017-08-26 18:00:00
 7 LCZ.6.9  2017-08-26    17  26.0  27.0   26.8 2017-08-26 17:00:00 2017-08-26 17:50:00
 8 LCZ.6.9  2017-08-26    18  26.5  26.5   26.5 NA                  2017-08-26 18:00:00
 9 LCZ.9.4  2017-08-26    17  26.5  27.0   26.8 2017-08-26 17:00:00 2017-08-26 17:30:00
10 LCZ.9.4  2017-08-26    18  26.0  26.5   26.0 2017-08-26 18:00:00 2017-08-26 18:10:00
+3

tidyverse.

: floor ed Time.hour, ; .

res <- df %>%
    mutate(Time = as.POSIXct(Time, format = "%Y-%m-%d %H:%M:%S")) %>%  # Time as POSIXct
    gather(location, value, -Time) %>%
    mutate(Time.hour = format(Time, "%y-%m-%d %H")) %>%
    group_by(Time.hour, location) %>%
    summarise(min = min(value), max = max(value), median = median(value));
res;
## A tibble: 10 x 5
## Groups:   Time.hour [?]
#   Time.hour   location   min   max median
#   <chr>       <chr>    <dbl> <dbl>  <dbl>
# 1 17-08-26 17 LCZ.3.10  26.0  27.5   26.8
# 2 17-08-26 17 LCZ.3.2   26.5  27.5   27.2
# 3 17-08-26 17 LCZ.6.1   26.5  27.5   27.0
# 4 17-08-26 17 LCZ.6.9   26.0  27.0   26.8
# 5 17-08-26 17 LCZ.9.4   26.5  27.0   26.8
# 6 17-08-26 18 LCZ.3.10  26.0  26.5   26.2
# 7 17-08-26 18 LCZ.3.2   26.5  27.0   26.5
# 8 17-08-26 18 LCZ.6.1   26.5  26.5   26.5
# 9 17-08-26 18 LCZ.6.9   26.5  26.5   26.5
#10 17-08-26 18 LCZ.9.4   26.0  26.5   26.0

If necessary, convert to wide:

res %>%
    ungroup() %>%
    gather(what, val, min:median) %>%
    unite(key, what, location) %>%
    spread(key, val)
## A tibble: 2 x 16
#  Time.hour   max_LCZ.3.10 max_LCZ.3.2 max_LCZ.6.1 max_LCZ.6.9 max_LCZ.9.4
#  <chr>              <dbl>       <dbl>       <dbl>       <dbl>       <dbl>
#1 17-08-26 17         27.5        27.5        27.5        27.0        27.0
#2 17-08-26 18         26.5        27.0        26.5        26.5        26.5
## ... with 10 more variables: median_LCZ.3.10 <dbl>, median_LCZ.3.2 <dbl>,
##   median_LCZ.6.1 <dbl>, median_LCZ.6.9 <dbl>, median_LCZ.9.4 <dbl>,
##   min_LCZ.3.10 <dbl>, min_LCZ.3.2 <dbl>, min_LCZ.6.1 <dbl>,
##   min_LCZ.6.9 <dbl>, min_LCZ.9.4 <dbl>

Sample data

df <- read.table(text =
    "Time 'LCZ 3-2' 'LCZ 3-10' 'LCZ 6-1' 'LCZ 6-9' 'LCZ 9-4'
 1 '2017-08-26 17:00:00'      27.5       27.5      27.5      27.0      27.0
 2 '2017-08-26 17:10:00'      27.5       27.0      27.5      27.0      27.0
 3 '2017-08-26 17:20:00'      27.5       27.0      27.0      27.0      27.0
 4 '2017-08-26 17:30:00'      27.0       26.5      27.0      26.5      26.5
 5 '2017-08-26 17:40:00'      26.5       26.5      26.5      26.5      26.5
 6 '2017-08-26 17:50:00'      26.5       26.0      26.5      26.0      26.5
 7 '2017-08-26 18:00:00'      26.5       26.0      26.5      26.5      26.5
 8 '2017-08-26 18:10:00'      27.0       26.0      26.5      26.5      26.0
 9 '2017-08-26 18:20:00'      26.5       26.5      26.5      26.5      26.0
10 '2017-08-26 18:30:00'      26.5       26.5      26.5      26.5      26.0", header = T, row.names = 1)
+2
source

Not sure what format the OPresults will be presented in. One solution can be found using mutate_atas:

library(lubridate)
library(dplyr)

result <- df %>% mutate(Time = ymd_hms(Time)) %>%
  group_by(Hourly = format(Time, "%Y%m%d%H")) %>%
  mutate_at(vars(starts_with("LCZ")), funs(min = min, max = max, med = median )) %>%
  select(Time, Hourly, sort(names(select(.,-Time-Hourly))))

Result

result[,1:9]
# # A tibble: 10 x 9
# # Groups: Hourly [2]
#   Time                Hourly     LCZ3_02 LCZ3_02_max LCZ3_02_med LCZ3_10 LCZ3_10_max LCZ3_10_med LCZ3_10_min
#   <dttm>              <chr>        <dbl>       <dbl>       <dbl>   <dbl>       <dbl>       <dbl>       <dbl>
# 1 2017-08-26 17:00:00 2017082617    27.5        27.5        27.2    27.5        27.5        26.8        26.0
# 2 2017-08-26 17:10:00 2017082617    27.5        27.5        27.2    27.0        27.5        26.8        26.0
# 3 2017-08-26 17:20:00 2017082617    27.5        27.5        27.2    27.0        27.5        26.8        26.0
# 4 2017-08-26 17:30:00 2017082617    27.0        27.5        27.2    26.5        27.5        26.8        26.0
# 5 2017-08-26 17:40:00 2017082617    26.5        27.5        27.2    26.5        27.5        26.8        26.0
# 6 2017-08-26 17:50:00 2017082617    26.5        27.5        27.2    26.0        27.5        26.8        26.0
# 7 2017-08-26 18:00:00 2017082618    26.5        27.0        26.5    26.0        26.5        26.2        26.0
# 8 2017-08-26 18:10:00 2017082618    27.0        27.0        26.5    26.0        26.5        26.2        26.0
# 9 2017-08-26 18:20:00 2017082618    26.5        27.0        26.5    26.5        26.5        26.2        26.0
# 10 2017-08-26 18:30:00 2017082618    26.5        27.0        26.5    26.5        26.5        26.2        26.0

Data

df <- read.table(text =
"Time    LCZ3_02    LCZ3_10   LCZ6_01   LCZ6_09    LCZ9_04
1 '2017-08-26 17:00:00'      27.5       27.5      27.5      27.0      27.0
2 '2017-08-26 17:10:00'      27.5       27.0      27.5      27.0      27.0
3 '2017-08-26 17:20:00'      27.5       27.0      27.0      27.0      27.0
4 '2017-08-26 17:30:00'      27.0       26.5      27.0      26.5      26.5
5 '2017-08-26 17:40:00'      26.5       26.5      26.5      26.5      26.5
6 '2017-08-26 17:50:00'      26.5       26.0      26.5      26.0      26.5
7 '2017-08-26 18:00:00'      26.5       26.0      26.5      26.5      26.5
8 '2017-08-26 18:10:00'      27.0       26.0      26.5      26.5      26.0
9 '2017-08-26 18:20:00'      26.5       26.5      26.5      26.5      26.0
10 '2017-08-26 18:30:00'      26.5       26.5      26.5      26.5      26.0",
header = TRUE, stringsAsFactors = FALSE)
+2
source

Source: https://habr.com/ru/post/1695910/

More articles:


All Articles