我有一個包含多個變數的資料框，其第一列如下所示：
Place <- c(rep("PlaceA",14),rep("PlaceB",15))
Group_Id <- c(rep("A1",5),rep("A1",6),rep("A2",3),rep("B1",6),rep("B2",4),rep("B2",5))
Time <- as.Date(c("2018-01-15","2018-02-03","2018-02-27","2018-03-10","2018-03-18","2019-02-02","2019-03-01","2019-03-15","2019-03-28","2019-04-05","2019-04-12","2018-02-01",
"2018-03-01","2018-04-07","2018-01-17","2018-01-27","2018-02-17","2018-03-03","2018-04-02","2018-04-25","2018-03-03","2018-03-18","2018-04-08","2018-04-20",
"2019-01-23","2019-02-09","2019-02-27","2019-03-12","2019-03-30"))
FollowUp <- c("start",paste("week",week(ymd(Time[2:5]))),"start",paste("week",week(ymd(Time[7:11]))),"start",paste("week",week(ymd(Time[13:14]))),"start",paste("week",week(ymd(Time[16:20]))),"start",paste("week",week(ymd(Time[22:24]))),"start",paste("week",week(ymd(Time[26:29]))))
exprmt <- c(rep(1,5),rep(2,6),rep(3,3),rep(4,6),rep(5,4),rep(6,5))
> df1
Place Group_Id Time exprmt FollowUp
1 PlaceA A1 2018-01-15 1 start
2 PlaceA A1 2018-02-03 1 week 5
3 PlaceA A1 2018-02-27 1 week 9
4 PlaceA A1 2018-03-10 1 week 10
5 PlaceA A1 2018-03-18 1 week 11
6 PlaceA A1 2019-02-02 2 start
7 PlaceA A1 2019-03-01 2 week 9
8 PlaceA A1 2019-03-15 2 week 11
9 PlaceA A1 2019-03-28 2 week 13
10 PlaceA A1 2019-04-05 2 week 14
11 PlaceA A1 2019-04-12 2 week 15
12 PlaceA A2 2018-02-01 3 start
13 PlaceA A2 2018-03-01 3 week 9
14 PlaceA A2 2018-04-07 3 week 14
15 PlaceB B1 2018-01-17 4 start
16 PlaceB B1 2018-01-27 4 week 4
17 PlaceB B1 2018-02-17 4 week 7
18 PlaceB B1 2018-03-03 4 week 9
19 PlaceB B1 2018-04-02 4 week 14
20 PlaceB B1 2018-04-25 4 week 17
21 PlaceB B2 2018-03-03 5 start
22 PlaceB B2 2018-03-18 5 week 11
23 PlaceB B2 2018-04-08 5 week 14
24 PlaceB B2 2018-04-20 5 week 16
25 PlaceB B2 2019-01-23 6 start
26 PlaceB B2 2019-02-09 6 week 6
27 PlaceB B2 2019-02-27 6 week 9
28 PlaceB B2 2019-03-12 6 week 11
29 PlaceB B2 2019-03-30 6 week 13
對于每個地方（在我的實際資料中超過 2 個），我有一個單獨的資料框，其中包含按小時計算的溫度記錄。例如：
set.seed(1032)
t <- c(seq.POSIXt(from = ISOdate(2018,01,01),to = ISOdate(2018,06,01), by = "hour"),seq.POSIXt(from = ISOdate(2019,01,01),to = ISOdate(2019,06,01), by = "hour"))
temp_A <- runif(length(t),min = 5, max = 25)
temp_B <- runif(length(t),min = 3, max = 32)
data_A <- data.frame(t,temp_A)
data_B <- data.frame(t,temp_B)
> head(data_A)
t temp_A
1 2018-01-01 12:00:00 14.24961
2 2018-01-01 13:00:00 21.64925
3 2018-01-01 14:00:00 21.77058
4 2018-01-01 15:00:00 13.31673
5 2018-01-01 16:00:00 16.10350
6 2018-01-01 17:00:00 17.64567
我需要df1在時間間隔內按 Place、group_Id 和 exrmt添加一列平均溫度：每個的第一個group_by應該是 NaN，而不是我需要每個時間間隔的平均值。知道對于每個地方，資料也在一個單獨的資料框中。
我試過這樣的事情，但它不起作用：
df1 <- df1 %>% group_by(Place,Group_Id,exprmt) %>% mutate(
temp = case_when(FollowUp == "start" & Place == "PlaceA" ~ NA,
FollowUp == FollowUp[c(2:n())] & Place == "PlaceA" ~ mean(temp_A[c(which(date(temp_A$t))==lag(Time,1):which(date(temp_A$t))==Time),2]),
)
)
我找到了有關如何計算多個資料幀（例如this或this）的平均值的資訊，但這不是我要找的。我想在沒有回圈的情況下做到這一點。我的預期結果是（等代表等等..）：
> df1
Place Group_Id Time exprmt FollowUp expected
1 PlaceA A1 2018-01-15 1 start NaN
2 PlaceA A1 2018-02-03 1 week 5 mean temp_A between 2018-01-15 and 2018-02-03
3 PlaceA A1 2018-02-27 1 week 9 mean temp_A between 2018-02-03 and 2018-02-27
4 PlaceA A1 2018-03-10 1 week 10 mean temp_A between 2018-02-27 and 2018-03-10
5 PlaceA A1 2018-03-18 1 week 11 mean temp_A between 2018-03-10 and 2018-03-18
6 PlaceA A1 2019-02-02 2 start NaN
7 PlaceA A1 2019-03-01 2 week 9 mean temp_A between 2019-02-02 and 2019-03-01
8 PlaceA A1 2019-03-15 2 week 11 etc
9 PlaceA A1 2019-03-28 2 week 13 etc
10 PlaceA A1 2019-04-05 2 week 14 etc
11 PlaceA A1 2019-04-12 2 week 15 etc
12 PlaceA A2 2018-02-01 3 start etc
13 PlaceA A2 2018-03-01 3 week 9 etc
14 PlaceA A2 2018-04-07 3 week 14 etc
15 PlaceB B1 2018-01-17 4 start NaN
16 PlaceB B1 2018-01-27 4 week 4 mean temp_B between 2018-01-17 and 2018-01-27
17 PlaceB B1 2018-02-17 4 week 7 etc
18 PlaceB B1 2018-03-03 4 week 9 etc
19 PlaceB B1 2018-04-02 4 week 14 etc
20 PlaceB B1 2018-04-25 4 week 17 etc
21 PlaceB B2 2018-03-03 5 start etc
22 PlaceB B2 2018-03-18 5 week 11 etc
23 PlaceB B2 2018-04-08 5 week 14 etc
24 PlaceB B2 2018-04-20 5 week 16 etc
25 PlaceB B2 2019-01-23 6 start etc
26 PlaceB B2 2019-02-09 6 week 6 etc
27 PlaceB B2 2019-02-27 6 week 9 etc
28 PlaceB B2 2019-03-12 6 week 11 etc
29 PlaceB B2 2019-03-30 6 week 13 etc
任何幫助將不勝感激！
uj5u.com熱心網友回復：
我建議一個詳細的分步解決方案（使用data.table和lubridate庫），可能有點學術性，但盡量不失去讀者。所以，請在下面找到一個reprex。
正品
1. 資料準備
library(data.table)
library(lubridate)
# Convert the dataframe 'df1' into data.table and add the dummy variable 'StartTime'
setDT(df1)[, StartTime := shift(Time,1), by = .(Place, Group_Id, exprmt)][]
setcolorder(df1, c("Place", "Group_Id", "FollowUp", "exprmt", "StartTime", "Time"))
# What df1 looks like:
df1
#> Place Group_Id FollowUp exprmt StartTime Time
#> 1: PlaceA A1 start 1 <NA> 2018-01-15
#> 2: PlaceA A1 week 5 1 2018-01-15 2018-02-03
#> 3: PlaceA A1 week 9 1 2018-02-03 2018-02-27
#> 4: PlaceA A1 week 10 1 2018-02-27 2018-03-10
#> 5: PlaceA A1 week 11 1 2018-03-10 2018-03-18
#> 6: PlaceA A1 start 2 <NA> 2019-02-02
#> 7: PlaceA A1 week 9 2 2019-02-02 2019-03-01
#> 8: PlaceA ....
# Convert 'StartTime' and 'Time' columns into class 'PosiXct'
sel_cols <- c("StartTime", "Time")
df1[, (sel_cols) := lapply(.SD, as.POSIXct, tz = "GMT"), .SDcols = sel_cols]
# Convert the dataframes 'data_A' and 'data_B' into data.tables
setDT(data_A)
setDT(data_B)
2. 加入
# Merge 'data_A' and 'data_B' on 't'
data_merge <- merge(data_A, data_B, by = 't')
# Join 'df1' and 'data_merge' with Time > t >= StartTime, and remove unnecessary columns
DF_join_1 <- df1[data_merge, on = .(StartTime <= t,Time > t)
][, `:=` (Place = NULL, Group_Id = NULL, FollowUp = NULL, exprmt = NULL, Time = NULL)
][]
# Join 'DF_join_1' and 'df1' on StartTime, then remove the dummy variable StartTime and reorder columns
DF_join_2 <- DF_join_1[df1, on = .(StartTime)
][, StartTime := NULL
][]
setcolorder(DF_join_2, c("Place", "Group_Id", "Time", "exprmt", "FollowUp", "temp_A", "temp_B"))
3. 添加一列“TEMP”
# Create a column 'temp' filled with 'temp_A' values when 'Place == PlaceA' and 'temp_B' values when 'Place == PlaceB'
DF_results <- DF_join_2[, temp := fcase(Place == "PlaceA", temp_A,
Place == "PlaceB", temp_B)
][, `:=` (temp_A = NULL, temp_B = NULL)
][]
4. 總結以獲得所需的輸出
# Summarize DF_results to get the mean of 'temp' by group in the 'expected' variable
DF_results[, .(expected = mean(temp, na.rm = TRUE)), by = .(Place, Group_Id, exprmt, Time, FollowUp)]
#> Place Group_Id exprmt Time FollowUp expected
#> 1: PlaceA A1 1 2018-01-15 start NaN
#> 2: PlaceA A1 1 2018-02-03 week 5 10.618465
#> 3: PlaceA A1 1 2018-02-27 week 9 15.997990
#> 4: PlaceA A1 1 2018-03-10 week 10 14.874170
#> 5: PlaceA A1 1 2018-03-18 week 11 8.005203
#> 6: PlaceA A1 2 2019-02-02 start NaN
#> 7: PlaceA A1 2 2019-03-01 week 9 17.768572
#> 8: PlaceA A1 2 2019-03-15 week 11 8.525002
#> 9: PlaceA A1 2 2019-03-28 week 13 20.948760
#> 10: PlaceA A1 2 2019-04-05 week 14 16.898529
#> 11: PlaceA A1 2 2019-04-12 week 15 7.172799
#> 12: PlaceA A2 3 2018-02-01 start NaN
#> 13: PlaceA A2 3 2018-03-01 week 9 17.521202
#> 14: PlaceA A2 3 2018-04-07 week 14 21.653708
#> 15: PlaceB B1 4 2018-01-17 start NaN
#> 16: PlaceB B1 4 2018-01-27 week 4 22.622165
#> 17: PlaceB B1 4 2018-02-17 week 7 22.462456
#> 18: PlaceB B1 4 2018-03-03 week 9 10.210829
#> 19: PlaceB B1 4 2018-04-02 week 14 19.731544
#> 20: PlaceB B1 4 2018-04-25 week 17 25.700109
#> 21: PlaceB B2 5 2018-03-03 start NaN
#> 22: PlaceB B2 5 2018-03-18 week 11 19.731544
#> 23: PlaceB B2 5 2018-04-08 week 14 16.757186
#> 24: PlaceB B2 5 2018-04-20 week 16 5.248006
#> 25: PlaceB B2 6 2019-01-23 start NaN
#> 26: PlaceB B2 6 2019-02-09 week 6 7.720195
#> 27: PlaceB B2 6 2019-02-27 week 9 13.185666
#> 28: PlaceB B2 6 2019-03-12 week 11 9.706857
#> 29: PlaceB B2 6 2019-03-30 week 13 10.022071
#> Place Group_Id exprmt Time FollowUp expected
由reprex 包(v2.0.1)于 2021 年 11 月 19 日創建
uj5u.com熱心網友回復：
與 2 個地方的溫度資料共享結果。您始終可以通過連接和創建單個資料物件（如果總位置較少）或使用 ifelse 陳述句來概括相同的內容。
library(data.table)
setDT(df1)
setDT(data_A) # converting to data.table
setDT(data_B) # converting to data.table
合并溫度以具有單個資料物件
data_AB <- merge(data_A, data_B, by = 't')
根據 Place、Group_Id、exprmt 創建 Time 變數的滯后列
df1[,':='(LAG_DATE = shift(Time, type = 'lag')), by = .(Place, Group_Id, exprmt)]
使用應用函式和用戶定義函式根據連續時間段對溫度資料進行子集化，并使用 data.table 功能和 lapply 來獲取這些子集的平均值
在這里，我假設 Place 列可以在某些條件下與溫度資料以某種方式連接/映射。就像示例中的共享 temp_A/temp_B 可以通過連接“temp_”和 Place 列的第 6 個字符來形成
df1[,':='(EXPECTED = apply(cbind(LAG_DATE, Time, Place), 1, function(x) {
x1 <- as.Date(as.numeric(x[1]), origin = '1970-01-01')
x2 <- as.Date(as.numeric(x[2]), origin = '1970-01-01')
Place <- as.character(x[3])
Mean_Value <- ifelse(is.na(x1), NaN, data_AB[as.Date(t) >= x1 &
as.Date(t) <= x2, lapply(.SD, mean), .SDcols = paste('temp_', substr(Place, 6,
6), sep = '')])
return(as.numeric(Mean_Value))
}
))]
轉載請註明出處，本文鏈接：https://www.uj5u.com/net/360935.html
下一篇：基于切片更新熊貓資料框？