I have a table in below format:
df = pd.DataFrame({'Date':['20191101','20191101','20191101','20191101','20191102','20191102','20191102','20191102'
,'20191103','20191103','20191103','20191103'],
'User':['James','Kevin','Kevin','Corrado','James','Kevin','Corrado','Corrado','James','Kevin','Corrado','Corrado'],
'Department':['A','B','B','C','A','B','C','C','A','B','C','C']})
df
And the table is in this result:
df1
Date Department User
20191101 A James
20191101 B Kevin
20191101 B Kevin
20191101 C Corrado
20191102 A James
20191102 B Kevin
20191102 C Corrado
20191102 C Corrado
20191103 A James
20191103 B Kevin
20191103 C Corrado
20191103 C Corrado
My desired result table using above one to count is:
df2
Date Department User Count
20191101 A James 1
20191101 B Kevin 2
20191101 C Corrado 1
20191102 A James 1
20191102 B Kevin 1
20191102 C Corrado 2
20191103 A James 1
20191103 B Kevin 1
20191103 C Corrado 2
And I would like to build this second table df2, which counts the frequency of a user in that specific department on that specific date in a data frame format which I could directly refer to and plot charts using matplotlib. This df2 should also maintain the date, department and user, and add just 1 more column as count.
Is there any way to do such operation? Highly appreciated.
