x、ID、date_timeの3つの列で構成されるデータフレームがあります。「x」列は変数xの記録であり、IDは何が記録されているかを示し、date_timeはいつ記録されているかを示します。以下のデータフレームの一部を参照してください。
このデータフレームから、「Measurement」、「ID」、「Date」、「x_4_10_day」、「Day_total」、「x_4_10_night」、「Night_total」の7つの列を持つ新しいデータフレームを計算したいと思います。
一意の測定ごとに行が必要です。これまでのところ、「Measurement」、「ID」、「Date」の列を正しく返すコードがあります。
df1$mydate = as.Date(df1$date_time, format = "%Y.%m.%d %H:%M:%S")
df1$tm <- as.numeric(df1$date_time)
df1$dts <- 86400*as.numeric(df1$mydate)
df2 <- df1 %>%
group_by(ID,mydate) %>%
transform(date = case_when(((dts-3600)<tm & tm<(dts+82800)) ~paste0(mydate), ((dts+82800)<=tm) ~paste0(mydate+1) )) %>%
select(ID,date) %>%
unique() %>%
group_by(ID) %>%
mutate(measurement = row_number())
しかし、私は最後のものを行う方法がわかりません。
期待される出力は次のとおりです。
dummy_output <- read.table(header=TRUE, text ="
ID Date Measurement x_4_10_day Day_total x_4_10_night Night_total
12 2020.03.02 1 30 40 0 0
12 2020.03.03 2 0 0 45 75
13 2020.05.09 1 90 90 0 0
")
どんな提案でも大歓迎です、ありがとう!
そして、ここにデータがあります:
structure(list(date_time = c("2020.03.02 22:00:17", "2020.03.02 22:05:17",
"2020.03.02 22:10:17", "2020.03.02 22:35:17", "2020.03.02 22:40:17",
"2020.03.02 22:45:17", "2020.03.02 22:50:17", "2020.03.02 22:55:17",
"2020.03.02 23:00:17", "2020.03.02 23:05:17", "2020.03.02 23:10:17",
"2020.03.02 23:15:17", "2020.03.02 23:20:17", "2020.03.02 23:25:17",
"2020.03.02 23:30:17", "2020.03.02 23:35:17", "2020.03.02 23:40:17",
"2020.03.02 23:45:17", "2020.03.02 23:50:17", "2020.03.02 23:55:17",
"2020.03.03 00:00:17", "2020.03.03 00:55:17", "2020.03.03 01:00:17",
"2020.03.03 01:05:17", "2020.03.03 01:10:17", "2020.03.03 01:15:17",
"2020.03.03 01:20:17", "2020.03.03 01:25:17", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32"), id = c(12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L,
13L, 13L, 13L, 13L, 13L), x = c("7.55", "4.55", "4.55", "12",
"12", "10", "10", "4.3", "", "", "4.3", "4.3", "4.3", "", "4.3",
"12", "12", "12", "2", "12", "12", "", "8", "3", "3", "2", "2",
"", "12", "10", "10", "4.3", "4.3", "4.3", "4.3", "4.3", "4.3",
"4.3", "4.3", "12", "12", "12", "12", "12", "12", "12")), row.names = c(NA,
46L), class = "data.frame")
id=14
データフレームに夜の値のみを追加しました。おそらくこれはあなたが探しているものです。期待値が要件に完全に準拠していないことに注意してください。
df11 <- structure(list(date_time = c("2020.03.02 22:00:17", "2020.03.02 22:05:17",
"2020.03.02 22:10:17", "2020.03.02 22:35:17", "2020.03.02 22:40:17",
"2020.03.02 22:45:17", "2020.03.02 22:50:17", "2020.03.02 22:55:17",
"2020.03.02 23:00:17", "2020.03.02 23:05:17", "2020.03.02 23:10:17",
"2020.03.02 23:15:17", "2020.03.02 23:20:17", "2020.03.02 23:25:17",
"2020.03.02 23:30:17", "2020.03.02 23:35:17", "2020.03.02 23:40:17",
"2020.03.02 23:45:17", "2020.03.02 23:50:17", "2020.03.02 23:55:17",
"2020.03.03 00:00:17", "2020.03.03 00:55:17", "2020.03.03 01:00:17",
"2020.03.03 01:05:17", "2020.03.03 01:10:17", "2020.03.03 01:15:17",
"2020.03.03 01:20:17", "2020.03.03 01:25:17", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.05.09 08:39:32", "2020.05.09 08:39:32",
"2020.03.02 23:45:17", "2020.03.02 23:50:17", "2020.03.02 23:55:17",
"2020.03.03 00:00:17", "2020.03.03 00:55:17", "2020.03.03 01:00:17"
),
x = c("7.55", "4.55", "4.55", "12",
"12", "10", "10", "4.3", "", "", "4.3", "4.3", "4.3", "", "4.3",
"12", "12", "12", "2", "12", "12", "", "8", "3", "3", "2", "2",
"", "12", "10", "10", "4.3", "4.3", "4.3", "4.3", "4.3", "4.3",
"4.3", "4.3", "12", "12", "12", "12", "12", "12", "12",
"12", "10", "10", "4.3", "4.3", "4.3"),
id = c(12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L, 12L,
13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L, 13L,
13L, 13L, 13L, 13L, 13L, 14L, 14L, 14L, 14L, 14L, 14L)),
row.names = c(NA, 52L), class = "data.frame")
df11$xn <- as.numeric(df11$x)
df1 <- df11 %>% transform(xmin = ifelse((xn<4 | xn>10 | is.na(xn)),0,5 ),
xmint = ifelse(is.na(xn),-5,5 ))
df1$dateTime = as_datetime(df1$date_time, format = "%Y.%m.%d %H:%M:%S")
df1$mydate = as.Date(df1$date_time, format = "%Y.%m.%d %H:%M:%S")
df1$tm <- as.numeric(df1$dateTime)
df1$dts <- 86400*as.numeric(df1$mydate)
df2 <- df1 %>% group_by(id,mydate) %>%
transform(date = case_when(((dts-3600)<tm & tm<(dts+82800) )~paste0(mydate),((dts+82800)<=tm)~paste0(mydate+1) )) %>%
transform(dayrnight = ifelse((tm>=(dts+25200) & tm<(dts+82800) ),'day','night' ) ) %>%
group_by(id,date,dayrnight) %>%
dplyr::summarise(x_4_10 = sum(xmin), total = sum(xmint)) %>%
pivot_wider(id_cols = c(id,date), names_from = dayrnight, values_from = c("x_4_10", "total")) %>%
mutate_if(is.numeric , replace_na, replace = 0) %>%
group_by(id) %>% mutate(measurement = row_number()) %>%
select(id,date,measurement,x_4_10_day,total_day,x_4_10_night,total_night)
> df2
# A tibble: 4 x 7
# Groups: id [3]
id date measurement x_4_10_day total_day x_4_10_night total_night
<int> <chr> <int> <dbl> <dbl> <dbl> <dbl>
1 12 2020-03-02 1 30 40 0 0
2 12 2020-03-03 2 0 0 25 50
3 13 2020-05-09 1 50 90 0 0
4 14 2020-03-03 1 0 0 25 30
この記事はインターネットから収集されたものであり、転載の際にはソースを示してください。
侵害の場合は、連絡してください[email protected]
コメントを追加