How to get a date info out of a string and then capture the earliest results by subject by test

user_13631281 0
Viewed 30

I have a data.frame that looks like this:

enter image description here

I want t o build some variables to capture the earliest date for each test and its scores. I need to date infor out of variable Time. and then capture the date for each test. for test results, if there are two same test were done in the same date with score a and b, then record it as a+b. The result that I want is sth that looks like this:

enter image description here

I stuck at the first place which is getting the date info out of Time.

My codes are:

df1$date<- as.Date(df1$Time, format = "%Y-%B-%dT%H:%M")

It gave me NA for all of them. I plan to use group_by(ID, test) ... date==min(date), etc.

what should I do? Could anyone one give me some guidance on this?

Sample data can be build using codes :

df<-structure(list(ID = structure(c("C5-102", "C5-102", "C5-102", 
"C5-102", "C5-102", "C5-102", "C5-102", "C5-102", "C5-102", "C5-102", 
"C1-103", "C1-103", "C1-103", "C1-103", "C1-103", "C1-103", "C1-103", 
"C1-103", "C8-104", "C8-104", "C8-104", "C8-104", "C8-104", "C8-104"
), label = "Unique Subject Identifier", format.sas = "$"), score = c(73.8, 
100, 147.6, 73.8, 147.6, 73.8, 147.6, 1600, 1600, 1600, 64.5, 
129, 64.5, 129, 64.5, 129, 4302, 86, 62.7, 125.4, 62.7, 125.4, 
62.7, 125.4), test = c("A", "M", "A", "A", "A", "A", "A", "C", 
"C", "C", "A", "A", "A", "A", "A", "A", "C", "M", "A", "A", "A", 
"A", "A", "A"), Time = c("2016-03-16T10:22", "2016-03-25T01:23", 
"2016-03-16T11:10", "2016-03-17T12:12", "2016-03-17T12:52", "2016-03-18T12:05", 
"2016-03-18T12:53", "2016-03-21T11:00", "2016-03-22T11:55", "2016-03-23T12:34", 
"2016-08-09T14:13", "2016-08-09T14:49", "2016-08-10T13:30", "2016-08-10T14:30", 
"2016-08-12T13:35", "2016-08-12T14:20", "2016-08-14T13:00", "2016-08-18T01:00", 
"2016-08-11T14:38", "2016-08-11T15:42", "2016-08-12T14:22", "2016-08-12T15:26", 
"2016-08-13T14:31", "2016-08-13T15:45")), row.names = c(NA, -24L
), label = "EX                              ", class = c("tbl_df", 
"tbl", "data.frame"))

Many thanks and happy holiday!

r date
1 Answers

We can use pivot_wider after creating the columns grouped by 'ID' and 'test'

library(dplyr)
library(tidyr)
library(stringr)
out <- df %>%
   mutate(Date = as.Date(Time)) %>% 
   group_by(ID, test) %>%
   mutate(time = as.Date(max(Date)),
          level = str_c(score[Time == max(Date)], collapse="+")) %>% 
   ungroup %>% 
   pivot_wider(names_from = test, values_from = c(time, level))
user_37322710
Related