r - 一列中有多个日期:R
问题描述
好的,我已经解决了几个问题,我认为我在数据中看到了一个可以解决的模式(也就是说,我看到我可以区分 dmy 和 mdy)。
让我介绍一下数据集的背景。该数据集包含当前隔离期间旅行的人员的信息。因此,当我向下滚动数据集时,我看到大多数日期以 MDY 格式输入,而有些则以 dmy 格式输入。
所以对于这个数据集,(数据集包含直到八月的信息)。歧义可以通过以下方式解决:
- 日期不能是未来。因此,这解决了 08-12-2020 和 12-08-2020 之间的差异。另外,我只有到六月的数据。
- 日子比几个月快。如果我看到一个序列(无论它是 dmy 还是 mdy),如果我看到一个数字每隔几行发生变化(比如说 20),那么我知道正在变化的数字是一天而不是一个月。例子:
在这种情况下,我将如何正确分配日期?
解决方案
因此,如果我理解正确,您的数据是按时间顺序排列的,并且您对测量数据的时间范围有所了解。
鉴于此,这是我创建加扰数据然后重新解析它的尝试:
library(dplyr)
library(lubridate)
library(zoo) # for na.locf function
# create some scrambled data to work with
df <- tibble(
date_ground_truth = rep(seq(from = ymd('20190801'), to = ymd('20200730'), by = 1), each = 5)
) %>%
mutate(
date_inconsistent_chr = format(date_ground_truth, ifelse(runif(nrow(.), 0, 1) > 0.3, '%Y/%m/%d', '%Y/%d/%m'))
)
# providing here the date range in which your observations lie. I only know that it maxes end of July 2020, so my end result has some remaining unknowns at the start
daterange_known_min <- NA_Date_
daterange_known_max <- ymd('20200730')
# initiate a cleaned df - for any date where we have a day > 12, we know that it can only be one format of YMD/YDM
df_recleaned <- df %>%
mutate(
date_parsed_ymd = as.Date(date_inconsistent_chr, '%Y/%m/%d'), # try YMD
date_parsed_ydm = as.Date(date_inconsistent_chr, '%Y/%d/%m'), # try YDM
date_parsed_deducted = case_when( # write out the clear cut cases
day(date_parsed_ymd) > 12 ~ date_parsed_ymd,
day(date_parsed_ydm) > 12 ~ date_parsed_ydm,
date_parsed_ymd == date_parsed_ydm ~ date_parsed_ymd,
T ~ NA_Date_
)
)
# we will run over the data until we can not deduct any more new dates from what we've learnt so far:
new_guesses_possible <- T
while(new_guesses_possible) {
# how many dates did we already deduct?
num_deducted_dates_before <- df_recleaned %>% filter(!is.na(date_parsed_deducted)) %>% nrow()
# deduct more, based on our knowledge that the dates are chronological and within a certain time-frame
df_recleaned <- df_recleaned %>%
mutate(
earliest_possible_date = coalesce(na.locf(date_parsed_deducted, na.rm = F), daterange_known_min),
last_possible_date = coalesce(na.locf(date_parsed_deducted, fromLast = T, na.rm = F), daterange_known_max),
ymd_guess_in_range = coalesce(date_parsed_ymd >= earliest_possible_date & date_parsed_ymd <= last_possible_date, F),
ydm_guess_in_range = coalesce(date_parsed_ydm >= earliest_possible_date & date_parsed_ydm <= last_possible_date, F),
date_parsed_deducted = case_when(
# keep clear cases
!is.na(date_parsed_deducted) ~ date_parsed_deducted,
# if the ymd-guess falls between the last clear case and next clear case, take ymd
ymd_guess_in_range & !ydm_guess_in_range ~ date_parsed_ymd,
# same approach for ydm
ydm_guess_in_range & !ymd_guess_in_range ~ date_parsed_ydm,
# cover the cases where we don't know either the last or next clear parsed date
# if one of the parsed dates falls before the "first possible date", take the other one.
# (if no daterange_known_min is given, these rows will result in NA and not do anything...)
date_parsed_ymd < daterange_known_min ~ date_parsed_ydm,
date_parsed_ydm < daterange_known_min ~ date_parsed_ymd,
# inversely, if one parsed option falls after the "last possible date", ignore it.
date_parsed_ymd > daterange_known_max ~ date_parsed_ydm,
date_parsed_ydm > daterange_known_max ~ date_parsed_ymd
)
)
# how many dates did we now deduct?
num_deducted_dates_after <- df_recleaned %>% filter(!is.na(date_parsed_deducted)) %>% nrow()
# do we need to go on?
new_guesses_possible <- num_deducted_dates_after > 0 & num_deducted_dates_before != num_deducted_dates_after
}
# kick out all those extra columns :)
df_recleaned_final <- df_recleaned %>%
select(
-date_parsed_ymd, -date_parsed_ydm,
-earliest_possible_date, -last_possible_date,
-ymd_guess_in_range, -ydm_guess_in_range
)
在我的示例中,这会修复 2019 年 8 月第一周之后的所有日期
。如果您的数据中存在更长的空白,它可能会给您不同的结果。
推荐阅读
- python - 如何在 PyO3 中实现 python 运算符
- javascript - 从 html (js) 重定向到 angular 8
- javascript - 这可以在反应类中设置等待状态吗?
- spring - 带有 OKTA 的 Spring SAML SSO - 更改 Web 应用程序上下文时的 InResponseTo
- html - 基于AngularJs中的正则表达式在文本框中只允许一个数值
- c++ - 主函数体未检测到对重载可变参数模板函数 C++ 的调用
- javascript - HtmlWebpackPlugin - 错误中的错误:加载程序“[...]/html-webpack-plugin - /lib/loader.js!/[...]/src/index.html”没有返回 html
- android - Android 10 上的 EditText 自动填充错误
- vue.js - vuejs 允许使用嵌套模板标签吗?
- c# - 使用 asp.net core 2.1 在 angular 8 中下载文件总是会给出损坏的文件