[Python] Multiple groupings and aggregation after taking subsequent dates values to check a...

I have a dataframe where I want to return the number (proportion) of patients that have had a subsequent follow up after diagnosis of diseaseacross departments.

Example; a patient can have diag_yn across multiple departments but I want to check has the patient had follow up like before but for each department.

So, Patient 1 would be recorded twice in example, 1 for Radiology and again for Respiratory as it had a follow up appt for both departments.

Original DF

dept | patient_id | app_date | diag_date | diag_yn |
|------------|------------|------------|------------|-----------|
Radiology | 1 | 2024-01-11 | NaT | NaN |
Radiology | 1 | 2024-03-14 | 2024-03-14 | 1 |
Radiology | 1 | 2024-04-09 | NaT | NaN |
Radiology | 1 | 2024-09-09 | NaT | NaN |
Respiratory | 1 | 2024-02-11 | NaT | NaN |
Respiratory | 1 | 2024-04-14 | 2024-04-14 | 1 |
Respiratory | 1 | 2024-06-09 | NaT | NaN |
Respiratory | 1 | 2024-09-09 | NaT | NaN |
Respiratory | 2 | 2024-01-11 | NaT | NaN |
Respiratory | 2 | 2024-03-14 | 2024-03-14 | 1 |
Respiratory | 2 | 2024-04-09 | NaT | NaN |
Respiratory | 2 | 2024-09-09 | NaT | NaN |


Desired Output

dept | patient_with_fup | count |
Radiology |------------------|-------|
| 1 | 1 |
| 0 | 0 |
Respiratory |------------------|-------|
| 1 | 2 |
| 0 | 0 |


Have been provided the following code but before the dept column was added to the DF.

(df.groupby('patient_id')
.apply(lambda g: g['app_date'].gt(next(iter(g['diag_date'].dropna()),
pd.NaT)).any(),
include_groups=False)
.astype(int).value_counts()
.reindex([0, 1], fill_value=0).rename_axis('fup_yn')
.reset_index()
)

Continue reading...