Count Frequency Of Occurrence In Column

I'm trying to count value occurrences in a data frame which contains datetime column ('%Y-%m-%d %H:%M:%S'). Data: Date Employee Operation Ord

Solution 1:

I think it is best to create groups based on the day, the user and the Operation.For this you can use groupby + dt.date. Then you can count the occurrences of each group using groupby.count. You can use unstack to generate a data frame like the one I show you below:

#df=df.reset_index() #only if date is the index#df['Date']=pd.to_datetime(df['Date']) # If Date not is datetime
new_df=df.groupby([df['Date'].dt.date,'Employee','Operation'])['Operation'].count().unstack(fill_value=0)
print(new_df)

---

OperationApprovedCreatedDeletedEditedRejectedDateEmployee2001-01-01 User1101112001-01-02 User101000

Also you can use dt.year to group by year instead of by day:

new_df=df.groupby([df['Date'].dt.year,'Employee','Operation'])['Operation'].count().unstack(fill_value=0)
print(new_df)
Operation      Approved  Created  Deleted  Edited  Rejected
Date Employee                                              
2001 User1            1        1        1       1         1

or by year and month:

new_df=df.groupby([df['Date'].dt.year,df['Date'].dt.month,'Employee','Operation'])['Operation'].count().unstack(fill_value=0)
print(new_df)
Operation           Approved  Created  Deleted  Edited  Rejected
Date Date Employee                                              
2001 1    User1            1        1        1       1         1

---

Details

1. DataFrame for the example:

dfDateEmployeeOperationOrder02001-01-01 08:32:17    User1Approved#0004512001-01-01 08:36:23    User1Edited#0004522001-01-01 08:41:04    User1Rejected#0004632001-01-01 08:42:56    User1Deleted#0004642001-01-02 09:01:11    User1Created#00047

2. If Date is not a column you should use df=df.reset_index(). To convert Date to datetime use df['Date']=pd.to_datetime(df['date'])