Python: How To Get Count Of Values Based On Datetime

I have written the following code which creates two dataframes nq and cmnt. nq contains the UserId and corresponding time of Badge Attainment date. cmnt contains OwnerUserId and th

Solution 1:

Probably you want a cross-merge, filter and then a crosstab:

# merge the two dataframes
merged = (nq.merge(cmnt, left_on='UserId', 
         right_on='OwnerUserId',
         how='left')
)

# extract the date difference between `date` and `CreationDate`
merged['date_diff'] = merged['date'].dt.normalize() - merged['CreationDate'].dt.normalize()
merged['date_diff'] = (merged['date_diff'] / pd.to_timedelta('1D')).astype(int)

# filter the comments within the range
merged = merged[merged['date_diff'].between(-7,7)]

# crosstab
pd.crosstab([merged['UserId'],merged['date']], merged['date_diff'])

Output:

date_diff-112UserIddate12009-10-17 17:38:32.590   11022009-10-19 00:37:23.067   11132009-10-20 08:37:14.143   01042009-10-21 18:07:51.247   01052009-10-22 21:25:24.483   010