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                       -1   1   2
UserId date                               
1      2009-10-17 17:38:32.590   1   1   0
2      2009-10-19 00:37:23.067   1   1   1
3      2009-10-20 08:37:14.143   0   1   0
4      2009-10-21 18:07:51.247   0   1   0
5      2009-10-22 21:25:24.483   0   1   0