Substract one datetime column after a groupby with a time reference for each group from a second Pan-CodePudding

I have one dataframe df1 with one admissiontime for each id.

id     admissiontime    
1      2117-04-03 19:15:00  
2      2117-10-18 22:35:00  
3      2163-10-17 19:15:00  
4      2149-01-08 15:30:00  
5      2144-06-06 16:15:00

And an another dataframe df2 with several datetame for each id

id      datetime        
1       2135-07-28 07:50:00.000         
1       2135-07-28 07:50:00.000         
2       2135-07-28 07:57:15.900             
3       2135-07-28 07:57:15.900         
3       2135-07-28 07:57:15.900

I would like to substract for each id, datetimes with his specific admissiontime, in a column of the second dataframe.

I think I have to use d2.group.by('id')['datetime']- something but I struggle to connect with the df1.

CodePudding user response：

Use Series.sub with mapping by Series.map by another DataFrame:

 df1['admissiontime'] = pd.to_datetime(df1['admissiontime'])
 df2['datetime'] = pd.to_datetime(df2['datetime'])

df2['diff'] = df2['datetime'].sub(df2['id'].map(df1.set_index('id')['admissiontime']))