I have the following dataframe:
df =
id date_diag date_medication medication_code
1 01-01-2000 03-01-2000 A
2 01-01-2000 02-01-2000 A
3 01-01-2000 04-01-2000 B
4 01-01-2000 05-01-2000 B
I would like to create a table with the count of times a given medication was given after the date of the diagnoses:
df =
medication day1 day2 day3 day4 day5 day6 day7
A 0 1 1 0 0 0 0
B 0 0 0 1 1 0 0
here is one way to do it
# create a temp fields, Seq to count the day of medication
# and days difference b/w medication and diag
# pivot
# add prefix to column
# and do cleanup
out=(df.assign(seq=1,
days=(pd.to_datetime(df['date_medication'], dayfirst=True).sub(pd.to_datetime(df['date_diag'], dayfirst=True))).dt.days + 1)
.pivot(index='medication_code', columns='days', values='seq')
.fillna(0)
.add_prefix('day')
.reset_index()
.rename_axis(columns=None)
)
out
medication_code day2 day3 day4 day5
0 A 1.0 1.0 0.0 0.0
1 B 0.0 0.0 1.0 1.0
alternately,
df['days']=pd.to_datetime(df['date_medication'], dayfirst=True).sub(
pd.to_datetime(df['date_diag'], dayfirst=True)).dt.days + 1
out=pd.crosstab(df['medication_code'], df['days']).add_prefix('day').reset_index().rename_axis(columns=None)
out
medication_code day2 day3 day4 day5
0 A 1 1 0 0
1 B 0 0 1 1
