2

I have looked for answer to this but I have not been able to find one that will help me with a column that has numbers and strings. My data look like this

Name <- c("Doe, John","Doe, John","Doe, John", "Doe, Jane", "Doe, Jane","Doe, Jane","Parker, Peter","Parker, Peter","Parker, Peter", "Stark, Tony","Stark, Tony","Stark, Tony")
Accession <- c(123, 234, 345, 456, 567, 678, 789, 8910, 1023, 1134, 1567, 1769)
MRN <-c(55555, 55555, 55555, 66666, 66666, 66666, 77777, 77777, 77777, 88888, 88888, 88888)
Collected <-c("2022-01-05", "2022-01-06", "2022-01-07", "2022-01-08", "2022-01-09", "2022-01-10", "2022-01-11", "2022-01-12", "2022-01-13", "2022-01-14", "2022-01-15", "2022-01-16")
Result <-c(137, "Not Detected", 356, 1025, 1405, 538, "Not Detected", "Not Detected", "Not Detected", "Not Detected", 137, "Not Detected")

CV <- data.frame(Name, Accession, MRN, Collected, Result)

I have multiple observations on a group of people (sometimes up to 100 per person) I would like to begin counting from their first observation based on the date when they have a positive result and I would like to make this a new column Days_till_Pos, from that first positive result date I would then like to count the number of days until the results is "Not Detected" creating a new column Days_till_Neg.

I would like the data to look like this, where the results are grouped by MRN.

In this scenario if there is a number value in the Result column it would be considered positive, if it says "Not Detected" it would be considered negative. Also, If their first result is positive then the Days_till_Pos should say NA and if they never have a positive result then the Days_till_Neg column should say NA as well.

Name<- c("Doe, John","Doe, Jane","Parker, Peter", "Stark, Tony")
MRN<- c(55555, 66666, 77777, 88888)
Days_till_Pos<- c(NA, NA, NA, 1)
Days_till_Neg<- c(1,0,NA, 1)

CV1<- data.frame(Name, MRN, Days_till_Pos, Days_till_Neg)

Name                MRN          Days_till_Pos          Days_till_Neg
Doe,John            55555              NA                    1
Doe, Jane           66666              NA                    0
Parker, Peter       77777              NA                    NA 
Stark, Tony         88888              1                     1
jpsmith
  • 11,023
  • 5
  • 15
  • 36
T.McMillen
  • 115
  • 7
  • 1
    The only row in your expected output that makes sense to me is the fourth. `John` is positive immediately, shouldn't `Pos` be 0? Peter has no positives, why is `Neg` 1, shouldn't it be 0 or NA? – r2evans Jan 10 '23 at 14:36
  • @r2evans Yes you are correct the response should have been NA, I have updated the data and question to reflect that. Thank you! – T.McMillen Jan 10 '23 at 15:13

1 Answers1

1

I'm not entirely sure this is correct, but happy to modify further as needed.

The first mutate is to make sure you have Collected in date format.

After grouping by Name and MRN, you can use summarise for each patient.

For Days_till_Pos, check if the first Result is negative. If it is, then take the difference between the first collection date when positive, and the first collection date (which is known to be negative). Alternatively, if first result is positive, then just include NA.

For Days_till_Neg, check if all results are negative. If so, include NA. If not, then take the date for when the Result is negative, but the lag (previous date) result was positive. This would provide the date of transition from positive to negative. Then subtract the first date of a positive test.

Note that this assumes that patients do not oscillate multiple times between negative and positive tests.

library(tidyverse)

CV %>%
  mutate(Collected = as.Date(Collected)) %>%
  group_by(Name, MRN) %>%
  summarise(
    Days_till_Pos = ifelse(first(Result) == "Not Detected", 
                           Collected[Result != "Not Detected"][1] - Collected[1], 
                           NA),
    Days_till_Neg = ifelse(all(Result == "Not Detected"), 
                           NA, 
                           Collected[Result == "Not Detected" & lag(Result, default = "Not Detected") != "Not Detected"][1] - Collected[Result != "Not Detected"][1])
  )

Output

  Name            MRN Days_till_Pos Days_till_Neg
  <chr>         <dbl>         <dbl>         <dbl>
1 Doe, Jane     66666            NA            NA
2 Doe, John     55555            NA             1
3 Parker, Peter 77777            NA            NA
4 Stark, Tony   88888             1             1
Ben
  • 28,684
  • 5
  • 23
  • 45
  • thank you! What about if I want to know the total number of days a person is positive? This could include 20+ tests over one year in some instances. – T.McMillen Jan 13 '23 at 19:19
  • @T.McMillen Is it possible to go back/forth from positive to negative to positive multiple times? Or just one long 20 day continuous series of a positive test result? – Ben Jan 13 '23 at 19:21
  • It is possible for them to back/forth usually only once (pos/neg/pos or neg/pos/neg) – T.McMillen Jan 13 '23 at 19:23
  • @T.McMillen If pos/neg/pos - which days are counted? Just the first pos until negative (and ignore the second group of positive)? Or all positive? – Ben Jan 13 '23 at 19:24
  • Actually, it would be great if I could capture all three day counts. – T.McMillen Jan 13 '23 at 19:29
  • 1
    You can try adding to `summarise` something like: `Days_Positive = ifelse(all(Result != "Not Detected"), last(Collected) - first(Collected) + 1, last(Collected[Result != "Not Detected"]) - first(Collected[Result != "Not Detected" & lag(Result, default = "") == "Not Detected"]) + 1)` – Ben Jan 13 '23 at 19:39
  • In this case, if all results are numeric (none are "not detected"), then just take last date and subtract first date (add 1 to be inclusive)... – Ben Jan 13 '23 at 19:39
  • 1
    Otherwise, take the last date of a positive result and subtract the first date of a positive result (where the previous row as negative, transition from negative to positive) – Ben Jan 13 '23 at 19:40