Two string columns with stored datapoints - access one by the other

Question

i have a dataframe with basically two columns and "dates" in one and "grade of disease" in the other.

They are coded as follows:

Date	Grade
2017-10-14=&=2018-01-20=&==&=	1=&=2=&==&=
2018-10-14=&=2019-01-20=&=2020-01-01=&=	2=&=3=&=4=&=

I look for a code to access the date from the first column that corresponds to the Grade in the second columns. Additionally it would be a great help to be able to extract the date, when Grade of disease was e.g. 2 for the first time.

I tried str_split(... sep = "=&=) and end up with a matrix with all different dates and grades.

I would need to extract the following:

1. For time-varying analysis

Follow_up_1	Grade_1	Follow_up_2	Grade_2	Follow_up_3	Grade_3
2017-10-14	1	2018-01-20	2	NA	NA
2018-10-14	2	2019-01-20	3	2020-01-01	4

2. Date of max_grade and max_grade

Date_max_grade	Max_grade
2018-01-20	2
2020-01-01	4

3. Extract the first date, where the corresponding grade is 3 or 4.

First_Date_3_or_4
NA
2019-01-20
2020-01-01

Thanks in advance,

Jan

Answer 1

data:

df <- tribble(
  ~Date,    ~Grade,
  "2017-10-14=&=2018-01-20=&==&=",  "1=&=2=&==&=",
    "2018-10-14=&=2019-01-20=&=2020-01-01=&=",  "2=&=3=&=4=&="
)

setup:

library(tidyverse)

df <- df %>% 
  mutate(
    across(everything(), ~str_split(.x, pattern = "=&=") %>% map(~.x[.x != ""]))
  ) %>%
  pmap(~data.frame(...)) %>%
  imap_dfr(~mutate(.x, group = .y, group_id = row_number())) %>%
  mutate(
    Grade = as.integer(as.character(Grade)),
    Date = as.Date(Date)
  )

1st question:

df %>%
  pivot_wider(
    id_cols = group,
    names_from = group_id,
    names_glue = "{.value}_{group_id}",
    values_from = c(Date, Grade)
  ) %>%
  select(-group)

2nd question:

df %>%
  group_by(group) %>%
  filter(Grade == max(Grade)) %>%
  ungroup() %>%
  select(Date, Grade)

3rd question: I'm not sure what exactly you want here

df %>%
  group_by(group, Grade) %>%
  filter(Grade %in% c(3, 4)) %>%
  ungroup() %>%
  select(-group_id) %>%
  right_join(data.frame(group = unique(df$group)), by = "group")