mmenschig mmenschig - 4 months ago 14
Python Question

Pivot pandas dataframe with dates and showing counts per date

I have the following pandas DataFrame: (currently ~500 rows):



merged_verified =

Last Verified Verified by
0 2016-07-11 John Doe
1 2016-07-11 John Doe
2 2016-07-12 John Doe
3 2016-07-11 Mary Smith
4 2016-07-12 Mary Smith


I am attempting to
pivot_table()
it to receive the following:


Last Verified 2016-07-11 2016-07-12
Verified by
John Doe 2 1
Mary Smith 1 1


Currently I'm running

merged_verified = merged_verified.pivot_table(index=['Verified by'], values=['Last Verified'], aggfunc='count')


which gives me close to what I need, but not exactly:


Last Verified
Verified by
John Doe 3
Mary Smith 2


I've tried a variety of things with the parameters, but none of it worked. The result above is the closest I've come to what I need. I read somewhere I would need to add an additional column that uses dummy values (1's) that I can then add but that seems counter-intuitive for a what I believe to be simple DataFrame layout.

Answer

You can add parameter columns and aggragate by len:

merged_verified = merged_verified.pivot_table(index=['Verified by'], 
                                              columns=['Last Verified'], 
                                              values=['Last Verified'], 
                                              aggfunc=len)
print (merged_verified)
Last         2016-07-11  2016-07-12
Verified by                        
Doe                   2           1
Smith                 1           1

Or you also omit values:

merged_verified = merged_verified.pivot_table(index=['Verified by'], 
                                              columns=['Last Verified'], 
                                              aggfunc=len)
print (merged_verified)
Last Verified  2016-07-11  2016-07-12
Verified by                          
John Doe                2           1
Mary Smith              1           1