r - 加入数据集时如何删除重复观察
问题描述
我正在尝试计算trialnumber每个人（sq_id）的试验（ ）之间的时间量。我已经能够弄清楚如何计算试验之间的时间差 ( time_gap)，但我的输出包含所有这些不应该存在的重复行。
我的数据的一个子集可以在这里找到。出于可重复性的目的，我在export下面包含了数据集（称为 ）：
sq_id ageclass sex cohort year age grid trialnumber trialdate trialtime
6244 A F 2000 2005 5 AG 1 05/24/05 0:00
10212 A M 2006 2008 2 KL 1 05/04/08 6:13
10212 A M 2006 2010 4 KL 4 05/20/10 6:12
10212 A M 2006 2009 3 KL 2 06/10/09 6:14
10212 A M 2006 2009 3 KL 3 07/01/09 6:15
23052 J F 2017 2017 0 SU 2 08/02/17 11:00
23052 J F 2017 2017 0 SU 1 07/20/17 10:51
23080 J M 2017 2017 0 KL 2 07/29/17 10:20
23080 J M 2017 2017 0 KL 1 07/07/17 8:35
我做的第一件事是计算试验之间的时间，如下所示：
#adding time between trials to data
trialdate<-as.POSIXct(data$trialdate,format="%m/%d/%y")
data$datetime=as.POSIXct(paste(trialdate, data$trialtime),format= '%Y-%m-%d',usetz=FALSE)
#calculates time btw first trial and all other trials
timebtw <- data %>% group_by(sq_id) %>%
select(sq_id, trialnumber, datetime) %>%
mutate(time_gap = (datetime - nth(datetime, which.min((datetime)))), time_gap=time_gap/86400) #time_gap units are in seconds, changed to days
然后我将timebtw数据集加入我的原始数据集（称为export）：
new<-dplyr::left_join(export, timebtw, by = "sq_id")
我得到的输出如下所示：
> export
sq_id ageclass sex cohort year age grid trialnumber.x trialdate trialtime datetime time_gap trialnumber.y
6244 A F 2000 2005 5 AG 1 05/24/05 0:00 2005-05-24 0 secs 1
10212 A M 2006 2008 2 KL 1 05/04/08 6:13 2008-05-04 0 secs 1
10212 A M 2006 2008 2 KL 1 05/04/08 6:13 2008-05-04 746 secs 4
10212 A M 2006 2008 2 KL 1 05/04/08 6:13 2008-05-04 402 secs 2
10212 A M 2006 2008 2 KL 1 05/04/08 6:13 2008-05-04 423 secs 3
10212 A M 2006 2010 4 KL 4 05/20/10 6:12 2010-05-20 0 secs 1
10212 A M 2006 2010 4 KL 4 05/20/10 6:12 2010-05-20 746 secs 4
10212 A M 2006 2010 4 KL 4 05/20/10 6:12 2010-05-20 402 secs 2
10212 A M 2006 2010 4 KL 4 05/20/10 6:12 2010-05-20 423 secs 3
10212 A M 2006 2009 3 KL 2 06/10/09 6:14 2009-06-10 0 secs 1
10212 A M 2006 2009 3 KL 2 06/10/09 6:14 2009-06-10 746 secs 4
10212 A M 2006 2009 3 KL 2 06/10/09 6:14 2009-06-10 402 secs 2
10212 A M 2006 2009 3 KL 2 06/10/09 6:14 2009-06-10 423 secs 3
10212 A M 2006 2009 3 KL 3 07/01/09 6:15 2009-07-01 0 secs 1
10212 A M 2006 2009 3 KL 3 07/01/09 6:15 2009-07-01 746 secs 4
10212 A M 2006 2009 3 KL 3 07/01/09 6:15 2009-07-01 402 secs 2
10212 A M 2006 2009 3 KL 3 07/01/09 6:15 2009-07-01 423 secs 3
23052 J F 2017 2017 0 SU 2 08/02/17 11:00 2017-08-02 13 secs 2
23052 J F 2017 2017 0 SU 2 08/02/17 11:00 2017-08-02 0 secs 1
23052 J F 2017 2017 0 SU 1 07/20/17 10:51 2017-07-20 13 secs 2
23052 J F 2017 2017 0 SU 1 07/20/17 10:51 2017-07-20 0 secs 1
23080 J M 2017 2017 0 KL 2 07/29/17 10:20 2017-07-29 22 secs 2
23080 J M 2017 2017 0 KL 2 07/29/17 10:20 2017-07-29 0 secs 1
23080 J M 2017 2017 0 KL 1 07/07/17 8:35 2017-07-07 22 secs 2
23080 J M 2017 2017 0 KL 1 07/07/17 8:35 2017-07-07 0 secs 1
这是个问题。每个应该只有一个time_gap值trialnumber。
因此，例如，对于sq_id10212，输出应如下所示：
sq_id ageclass sex cohort year age grid trialnumber.x trialdate trialtime datetime time_gap trialnumber.y
10212 A M 2006 2008 2 KL 1 05/04/08 6:13 2008-05-04 0 secs 1
10212 A M 2006 2010 4 KL 4 05/20/10 6:12 2010-05-20 746 secs 4
10212 A M 2006 2009 3 KL 2 06/10/09 6:14 2009-06-10 402 secs 2
10212 A M 2006 2009 3 KL 3 07/01/09 6:15 2009-07-01 423 secs 3
我需要trialnumber.x和trialnumber.y列匹配，这样只有试验的行数（即sq_id6244 有 1 行、sq_id10212 4 行、sq_id23052 2 行和sq_id23080 2 行）。
有谁知道我可以如何修改我的代码来获得这个输出？
解决方案
library(dplyr)
dat <- readr::read_table(
"sq_id ageclass sex cohort year age grid trialnumber trialdate trialtime
6244 A F 2000 2005 5 AG 1 05/24/05 0:00
10212 A M 2006 2008 2 KL 1 05/04/08 6:13
10212 A M 2006 2010 4 KL 4 05/20/10 6:12
10212 A M 2006 2009 3 KL 2 06/10/09 6:14
10212 A M 2006 2009 3 KL 3 07/01/09 6:15
23052 J F 2017 2017 0 SU 2 08/02/17 11:00
23052 J F 2017 2017 0 SU 1 07/20/17 10:51
23080 J M 2017 2017 0 KL 2 07/29/17 10:20
23080 J M 2017 2017 0 KL 1 07/07/17 8:35")
glimpse(dat)
# Observations: 9
# Variables: 9
# $ sq_id <int> 6244, 10212, 10212, 10212, 10212, 23052, 23052, 23080, 23080
# $ ageclass <chr> "A", "A", "A", "A", "A", "J", "J", "J", "J"
# $ sex <chr> "F", "M", "M", "M", "M", "F", "F", "M", "M"
# $ `cohort year` <chr> "2000 2005", "2006 2008", "2006 2010", "2006 2009", "2006 2009", "2017 2017", "2017 2017",...
# $ age <int> 5, 2, 4, 3, 3, 0, 0, 0, 0
# $ grid <chr> "AG", "KL", "KL", "KL", "KL", "SU", "SU", "KL", "KL"
# $ trialnumber <int> 1, 1, 4, 2, 3, 2, 1, 2, 1
# $ trialdate <chr> "05/24/05", "05/04/08", "05/20/10", "06/10/09", "07/01/09", "08/02/17", "07/20/17", "07/29...
# $ trialtime <time> 00:00:00, 06:13:00, 06:12:00, 06:14:00, 06:15:00, 11:00:00, 10:51:00, 10:20:00, 08:35:00
似乎不需要单独计算时间间隔，因此无需加入：
dat %>%
mutate(trial_dt = lubridate::mdy_hms(paste(trialdate, trialtime))) %>%
group_by(sq_id) %>%
mutate(time_gap = difftime(trial_dt, min(trial_dt), units = "days"))
# # A tibble: 9 x 11
# # Groups: sq_id [4]
# sq_id ageclass sex `cohort year` age grid trialnumber trialdate trialtime trial_dt time_gap
# <int> <chr> <chr> <chr> <int> <chr> <int> <chr> <time> <dttm> <time>
# 1 6244 A F 2000 2005 5 AG 1 05/24/05 00:00 2005-05-24 00:00:00 0
# 2 10212 A M 2006 2008 2 KL 1 05/04/08 06:13 2008-05-04 06:13:00 0
# 3 10212 A M 2006 2010 4 KL 4 05/20/10 06:12 2010-05-20 06:12:00 745.999305555556
# 4 10212 A M 2006 2009 3 KL 2 06/10/09 06:14 2009-06-10 06:14:00 402.000694444444
# 5 10212 A M 2006 2009 3 KL 3 07/01/09 06:15 2009-07-01 06:15:00 423.001388888889
# 6 23052 J F 2017 2017 0 SU 2 08/02/17 11:00 2017-08-02 11:00:00 13.00625
# 7 23052 J F 2017 2017 0 SU 1 07/20/17 10:51 2017-07-20 10:51:00 0
# 8 23080 J M 2017 2017 0 KL 2 07/29/17 10:20 2017-07-29 10:20:00 22.0729166666667
# 9 23080 J M 2017 2017 0 KL 1 07/07/17 08:35 2017-07-07 08:35:00 0
推荐阅读
- gitlab - 在 gitlab 中对 RDF 存储进行版本控制的可能方法
- r - Spatstat 中的样方
- xquery - 使用两个索引的 MarkLogic 光学查询不返回任何结果
- jquery - 如何将 JQuery UI 导入 Blazor WebAssembly？
- c# - 我正在尝试将以下 Visual Basic 代码转换为 C# 代码
- python - 如何将所有数字绘制在循环之外，否则就像一个数字？
- javascript - UnhandledPromiseRejectionWarning：错误：评估失败：ReferenceError：未定义选项
- html - 子元素大小增加时 CSS 扩展过渡
- r - R 中是否有应用类型函数来从向量生成不同大小的样本？
- postgresql - Postgres 10 - 在多模式数据库上定位模式功能