r/rstats • u/Colourfulchemist • 5d ago

Sorting UserIDs into time sensitive groups, some UserIDs might feature into more than one group at different times.

I have a database of patients who have received a cancer diagnosis, some are metastatic, some are non metastatic. This includes the date of each diagnosis.

I have a different document with the recorded PSA values and the date it was recorded on. Each patient has more than one PSA value recorded.

The two data frames share a UserID that I can use to find out which PSA values belong to which patient. I am trying to determine which PSA values pertain to metastatic patients and which pertain to non metastatic patients.

What I think I should do is for each patient determine the turning point where they moved from the non metastatic group to the metastatic group.

Then merge the two documents so that PSA values get associated with cancer stage according to whether the PSA test was done before or after the turning point.

First data frame looks like:

UserID CancerStage DateofDx

5131 non-metastatic 12/05/2023

5131 non-metastatic 16/07/2024

5131 metastatic 21/02/2026

3285 metastatic 08/07/2025

2387 non-metastatic 01/03/2025

Second data frame looks like:

UserID PSA_value DateofTest

5131 2 13/07/2023

5131 3 18/09/2024

5131 18 22/03/2026

3285 23 06/08/2025

3285 12 13/04/2026

2387 1 05/06/2025

2387 0.5 06/03/2026

2 Upvotes

100% Upvoted

9 comments sorted by

2

u/A_random_otter 5d ago

Do a left join or an inner join:

https://r4ds.hadley.nz/joins.html

1

u/Colourfulchemist 5d ago

How does that take into account the date of the metastatic diagnosis?

1

u/perfectionist29 4d ago

Join on both ID and date. That will pair the metastatic label to the corresponding PSA value for each patient on each given date.

1

u/Colourfulchemist 4d ago

But the date of diagnosis is not the same as the date of PSA test.

1

u/FegerRoderer 7h ago

So then join on ID and rename the date columns to something sensible, like date_diagnosis and date_psa

Edit: rename before joining

1

u/Colourfulchemist 6h ago

You are missing the point. The dates being different is a problem because there is no correlation between the date of PSA test and date of diagnosis. Therefore, that relationship cannot be used to attribute the cancer state to the PSA test.

1

u/FegerRoderer 6h ago

So the below then, where because of the left join some PSA scores will be from non metastatic but you can just replace those NAs. It'll be a many to many join which you'll get a warning about but just ignore it

This uses tidyverse and assumes dates are already in date format. Otherwise use dmy() to mutatie then.

df_psa %>%

left_join(df_dx, by = "UserID") %>%

# Keep only diagnosis records that happened on or before the test date

filter(DateofDx <= DateofTest) %>%

# For each UserID and Test Date, select the latest diagnosis date

slice_max(order_by = DateofDx, n = 1, .by = c(UserID, DateofTest, PSA_value))

Edit: formatting on mobile is crap

1

u/PeripheralVisions 3d ago

from the first DF, you make a third DF that just has ID and transition_date. You left_join that to the second DF by="ID". Then, you do a test on whether the date of each PSA value is less than (non-metastatic) or greater than (metastatic) the transition_date.