我有一个每 10 分钟测量一次温度的数据帧。测量在不同的位置(称为"LCZ")进行，每个位置的值在不同的列中。
这是我的数据帧的一部分：(它还包含缺失值 NA)
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
我希望每个位置或列计算每小时最小/最大/中位数温度，此外，每小时最小/最大还计算分别发生最小/最大值的原始数据的时间戳。
这在R上可能吗？
我已经尝试了各种功能。
group_by允许我计算每列的最小值/最大值，但没有时间戳。period.apply还允许我计算最小值/最大值/中位数，但仅限于一列。aggregate()也没有导致任何成功。
我正在学习 R，但没有接近这个问题的解决方案。
这个网站帮助我解决了各种问题，但我真的被困在这个问题上。有人可以帮忙吗？提前致谢
我们可以使用润滑包中的floor_date创建一个新列Time2以显示每小时的信息。如果这不是您要定义每小时分组的方式，您也可以尝试round_date或ceiling_date。之后，我们可以使用 tidyr 包中的gather将数据框从宽格式转换为长格式。
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
数据
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)
这是一种使用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
这是一个tidyverse的解决方案。
说明：我们创建了一个新的小时floor时间列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
如果需要，请转换为宽：
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>
示例数据
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)
不太确定OP希望以哪种格式呈现结果。使用mutate_at可以找到一种解决方案，如下所示：
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[,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
数据
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)