我有一個日期框架，每行中的每個主題都有多個測量日期，還有另一個資料框架，每行中的同一主題有多個訪問日期（也包括一些 NA）。
我想要的是提取與某個主題的訪問日期匹配的測量日期，并標記不符合訪問日期的測量日期（例如，使用“FALSE”或-99），并保持 NA 的原樣.
set.seed(1)
# Dataframe with measure dates
df1 <- rbind.data.frame(sort(sample(seq(as.Date("2018-01-01"), as.Date("2019-01-01"), by = "day"), 10)),
c(sort(sample(seq(as.Date("2018-06-01"), as.Date("2019-06-01"), by = "day"), 8)), NA, NA),
c(sort(sample(seq(as.Date("2019-06-01"), as.Date("2020-06-01"), by = "day"), 6)), rep(NA, 4)))
names(df1) <- paste("MEASUREDATE", 1:10, sep = "")
myfun <- function(x) as.Date(x, format = "%Y-%m-%d", origin = "1970-01-01")
df1 <- data.frame(lapply(df1, myfun))
df1
# Dataframe with visit dates
df2 <- rbind.data.frame(as.numeric(df1[1, 2:7]), as.numeric(c(df1[2, 4:6], NA, NA, NA)), as.numeric(c(df1[3, 1:2], rep(NA, 4))))
df2 <- data.frame(lapply(df2, myfun))
names(df2) <- paste("VISIT", 1:6, sep = "")
df2
所以新資料框的第一行是這樣的：
# New dataframe
df3 <- df1[1, ]
df3[1] <- FALSE
df3[8:10] <- FALSE
df3
你知道如何解決這個問題嗎？很感謝任何形式的幫助。
uj5u.com熱心網友回復：
一種可能性是使用長格式的兩個資料幀。在這里，我轉df1長，然后left_join轉df2（也在將其轉換為長格式之后）。對于匹配的日期，名稱 fromdf2將出現（而其他日期將出現NA），然后我們可以使用此資訊將日期資料轉換為NA如果不匹配。然后，我洗掉了name.y包含訪問編號的列，并僅保留唯一值。然后，我們可以轉向更廣泛的格式。
library(tidyverse)
df1 %>%
mutate(row = row_number()) %>%
pivot_longer(-row) %>%
left_join(.,
df2 %>% mutate(row = row_number()) %>%
pivot_longer(-row),
by = c("row", "value")) %>%
mutate(value = case_when(is.na(name.y)
~ as.Date(NA),
TRUE ~ value)) %>%
select(-name.y) %>%
distinct() %>%
pivot_wider(names_from = "name.x", values_from = "value") %>%
select(-row)
輸出
MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5 MEASUREDATE6 MEASUREDATE7 MEASUREDATE8 MEASUREDATE9 MEASUREDATE10
<date> <date> <date> <date> <date> <date> <date> <date> <date> <date>
1 NA 2018-05-09 2018-06-16 2018-07-06 2018-09-27 2018-10-04 2018-10-26 NA NA NA
2 NA NA NA 2018-11-12 2018-12-30 2019-01-03 NA NA NA NA
3 2019-08-28 2020-03-15 NA NA NA NA NA NA NA NA
更新
如果要區分FALSE和NA，那么我們需要先轉換date為character。然后，我們可以在 中設定一些附加條件case_when。
df1 %>%
mutate(row = row_number()) %>%
pivot_longer(-row) %>%
left_join(.,
df2 %>% mutate(row = row_number()) %>%
pivot_longer(-row),
by = c("row", "value")) %>%
mutate(across(everything(), ~as.character(.))) %>%
mutate(value = case_when(is.na(name.y) & !is.na(value) ~ "FALSE",
!is.na(name.y) & !is.na(value) ~ value,
TRUE ~ "NA")) %>%
select(-name.y) %>%
distinct() %>%
pivot_wider(names_from = "name.x", values_from = "value") %>%
select(-row)
輸出
MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5 MEASUREDATE6 MEASUREDATE7 MEASUREDATE8 MEASUREDATE9 MEASUREDATE10
<chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr> <chr>
1 FALSE 2018-05-09 2018-06-16 2018-07-06 2018-09-27 2018-10-04 2018-10-26 FALSE FALSE FALSE
2 FALSE FALSE FALSE 2018-11-12 2018-12-30 2019-01-03 FALSE FALSE NA NA
3 2019-08-28 2020-03-15 FALSE FALSE FALSE FALSE NA NA NA NA
資料
df1 <- structure(
list(
MEASUREDATE1 = structure(c(17616, 17719, 18136), class = "Date"),
MEASUREDATE2 = structure(c(17660, 17761, 18336), class = "Date"),
MEASUREDATE3 = structure(c(17698, 17787, 18337), class = "Date"),
MEASUREDATE4 = structure(c(17718, 17847, 18373), class = "Date"),
MEASUREDATE5 = structure(c(17801, 17895, 18387), class = "Date"),
MEASUREDATE6 = structure(c(17808, 17899, 18409), class = "Date"),
MEASUREDATE7 = structure(c(17830, 17945, NA), class = "Date"),
MEASUREDATE8 = structure(c(17838, 18011, NA), class = "Date"),
MEASUREDATE9 = structure(c(17855, NA, NA), class = "Date"),
MEASUREDATE10 = structure(c(17861, NA, NA), class = "Date")
),
class = "data.frame",
row.names = c(NA,-3L)
)
df2 <-
structure(
list(
VISIT1 = structure(c(17660, 17847, 18136), class = "Date"),
VISIT2 = structure(c(17698, 17895, 18336), class = "Date"),
VISIT3 = structure(c(17718, 17899, NA), class = "Date"),
VISIT4 = structure(c(17801, NA, NA), class = "Date"),
VISIT5 = structure(c(17808, NA, NA), class = "Date"),
VISIT6 = structure(c(17830, NA, NA), class = "Date")
),
class = "data.frame",
row.names = c(NA,-3L)
)
uj5u.com熱心網友回復：
我認為最干凈的方法是采取@Andrew Gillreath-Brown 的回答提供的長長的路線。但是，如果您愿意，我們也可以簡單地應用于資料幀的行（如果nrow(df1) == nrow(df2)）。
dfl <- lapply(
1:nrow(df1),
\(i) {
measures <- as.Date(unlist(df1[i,]), origin = "1970-01-01")
visits <- as.Date(unlist(df2[i,]), origin = "1970-01-01")
measures[!(measures %in% visits)] <- NA
measures
}
)
dfl
#> [[1]]
#> MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5
#> NA "2018-05-09" "2018-06-16" "2018-07-06" "2018-09-27"
#> MEASUREDATE6 MEASUREDATE7 MEASUREDATE8 MEASUREDATE9 MEASUREDATE10
#> "2018-10-04" "2018-10-26" NA NA NA
#>
#> [[2]]
#> MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5
#> NA NA NA "2018-11-12" "2018-12-30"
#> MEASUREDATE6 MEASUREDATE7 MEASUREDATE8 MEASUREDATE9 MEASUREDATE10
#> "2019-01-03" NA NA NA NA
#>
#> [[3]]
#> MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5
#> "2019-08-28" "2020-03-15" NA NA NA
#> MEASUREDATE6 MEASUREDATE7 MEASUREDATE8 MEASUREDATE9 MEASUREDATE10
#> NA NA NA NA NA
然后為方便起見可以只系結在一起得到你的df3（或只是purrr::map_dfr在上面使用）。
dplyr::bind_rows(dfl)
#> # A tibble: 3 × 10
#> MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5 MEASUREDATE6
#> <date> <date> <date> <date> <date> <date>
#> 1 NA 2018-05-09 2018-06-16 2018-07-06 2018-09-27 2018-10-04
#> 2 NA NA NA 2018-11-12 2018-12-30 2019-01-03
#> 3 2019-08-28 2020-03-15 NA NA NA NA
#> # … with 4 more variables: MEASUREDATE7 <date>, MEASUREDATE8 <date>,
#> # MEASUREDATE9 <date>, MEASUREDATE10 <date>
更新
@Andrew Gillreath-Brown 指出您想保留FALSE和NA分開。如果您想保持FALSE和NA值分開，那么只需先使用此方法將字串轉換為字符。
dfl2 <- lapply(
1:nrow(df1),
\(i) {
measures <- as.character(as.Date(unlist(df1[i,]), origin = "1970-01-01"))
visits <- as.character(as.Date(unlist(df2[i,]), origin = "1970-01-01"))
measures[!(measures %in% visits)] <- "FALSE"
measures
}
)
dplyr::bind_rows(dfl2)
#> # A tibble: 3 × 10
#> MEASUREDATE1 MEASUREDATE2 MEASUREDATE3 MEASUREDATE4 MEASUREDATE5 MEASUREDATE6
#> <chr> <chr> <chr> <chr> <chr> <chr>
#> 1 FALSE 2018-05-09 2018-06-16 2018-07-06 2018-09-27 2018-10-04
#> 2 FALSE FALSE FALSE 2018-11-12 2018-12-30 2019-01-03
#> 3 2019-08-28 2020-03-15 FALSE FALSE FALSE FALSE
#> # … with 4 more variables: MEASUREDATE7 <chr>, MEASUREDATE8 <chr>,
#> # MEASUREDATE9 <chr>, MEASUREDATE10 <chr>
轉載請註明出處，本文鏈接：https://www.uj5u.com/shujuku/431168.html
下一篇：VBA訪問使用通配符獲取檔案名