python - counting instances in previous year

Question

I have a very large data set of hospital admissions. For each admission I would like to know the number of admissions that took place within the previous year. My dataset is in a form of a dataframe including patient ID and hospitalization date. I would like to have an additional column stating the counts of admissions in the previous year - for example, column "previous year" in the table below.

patient_id	hospitalization_date	previous_year
1	Nov 2 2020	2
1	Dec 20 2019	1
1	Nov 30 2019	0
1	Jan 1 2015	0
2	April 17 2019	1
2	Nov 5 2018	0

question from:https://stackoverflow.com/questions/65938774/counting-instances-in-previous-year

Answer 1

Following would produce your desired result,

res = df.groupby(['patient_id',df['hospitalization_date'].dt.year]).sum().reset_index()
pd.merge(df,res,how='left', left_on= ['patient_id',df['hospitalization_date'].dt.year], right_on = ['patient_id','hospitalization_date'],suffixes=['_x','_sum'])[['patient_id','hospitalization_date_x','previous_year_x','previous_year_sum']]

Output:

patient_id  hospitalization_date_x  previous_year_x previous_year_sum
0   1   2020-11-02  2   2
1   1   2019-12-20  1   1
2   1   2019-11-30  0   1
3   1   2015-01-01  0   0
4   2   2019-04-17  1   1
5   2   2018-11-05  0   0