I am learning pandas and python.
I have this dataframe:
dfsupport = pd.DataFrame({'Date': ['8/12/2020','8/12/2020','13/1/2020','24/5/2020','31/10/2020','11/7/2020','11/7/2020','4/4/2020','1/2/2020'],
'Category': ['Table','Chair','Cushion','Table','Chair','Mats','Mats','Large','Large'],
'Sales': ['1 table','3chairs','8 cushions','3Tables','12 Chairs','12Mats','4Mats','13 Chairs and 2 Tables', '3 mats, 2 cushions 4@chairs'],
'Paid': ['Yes','Yes','Yes','Yes','No','Yes','Yes','No','Yes'],
'Amount': ['93.78','$51.99','44.99','38.24','£29.99','29 21 only','18','312.8','63.77' ]
})
which produces:
Date Category Sales Paid Amount
0 8/12/2020 Table 1 table Yes 93.78
1 8/12/2020 Chair 3chairs Yes 51.99
2 13/1/2020 Cushion 8 cushions Yes 44.99
3 24/5/2020 Table 3Tables Yes 38.24
4 31/10/2020 Chair 12 Chairs No 29.99
5 11/7/2020 Mats 12Mats Yes 29.21
6 11/7/2020 Mats 4Mats Yes 18
7 4/4/2020 Large 13 Chairs and 2 Tables No 312.8
8 1/2/2020 Large 3 mats, 2 cushions 4@chairs Yes 63.77
I want to find the date with the most sale, so I ran:
print("######\n",dfsupport['Date'].value_counts().max())
which gives:
2
What I would now like to do is to unpack that 2 and find out which dates that was for and also which "Sales" occurred in each of those instances.
I'm stuck and don't know how to print out those columns. Would appreciate some guidance.
Another possible solution, which uses pandas.DataFrame.groupby, pandas.DataFrame.transform and boolean indexing:
s = dfsupport.groupby('Date')['Date'].transform(len)
dfsupport[s.eq(s.max())]
Output:
Date Category Sales Paid Amount
0 8/12/2020 Table 1 table Yes 93.78
1 8/12/2020 Chair 3chairs Yes $51.99
5 11/7/2020 Mats 12Mats Yes 29 21 only
6 11/7/2020 Mats 4Mats Yes 18