[ How to return the top 10 frequent column values with pandas? ]

I am playing with a well known crime dataset. It looks like this:

Dates,Category,Descript,DayOfWeek,PdDistrict,Resolution,Address,X,Y,Time
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM OF VEHICLES",Wednesday,TENDERLOIN,NONE,TURK ST / JONES ST,-122.41241426358101,37.7830037964534,22:30:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM",Wednesday,NORTHERN,NONE,1500 Block of FILLMORE ST,-122.432743822617,37.7838424505847,20:45:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM",Wednesday,NORTHERN,NONE,1100 Block of FILLMORE ST,-122.431979576386,37.7800478529923,17:07:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM OF VEHICLES",Wednesday,TENDERLOIN,NONE,LEAVENWORTH ST / EDDY ST,-122.414242955907,37.783724025447796,17:00:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM OF VEHICLES",Wednesday,CENTRAL,NONE,CALIFORNIA ST / STOCKTON ST,-122.40753977435699,37.79224917725779,16:45:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM",Wednesday,BAYVIEW,NONE,100 Block of KISKA RD,-122.375989158092,37.7301576924252,16:00:00
2015-05-13,VANDALISM,"MALICIOUS MISCHIEF, VANDALISM OF VEHICLES",Wednesday,NORTHERN,"ARREST, BOOKED",300 Block of MCALLISTER ST,-122.417777932619,37.7803089893403,14:30:00
2015-05-13,NON-CRIMINAL,LOST PROPERTY,Wednesday,TENDERLOIN,NONE,300 Block of OFARRELL ST,-122.41050925879499,37.786043222299206,21:00:00
2015-05-13,LARCENY/THEFT,GRAND THEFT FROM LOCKED AUTO,Wednesday,NORTHERN,NONE,2000 Block of BUSH ST,-122.43101755702699,37.7873880712241,21:00:00
.....
2015-05-13,LARCENY/THEFT,GRAND THEFT FROM LOCKED AUTO,Wednesday,INGLESIDE,NONE,500 Block of COLLEGE AV,-122.42365634294501,37.7325564882065,21:00:00
2015-05-13,LARCENY/THEFT,ATTEMPTED THEFT FROM LOCKED VEHICLE,Wednesday,TARAVAL,NONE,19TH AV / SANTIAGO 

When I get the frequency count for Dates column I get 2011-01-01 650. In other words 650 crimes occurred in 2011-01-01 in the whole dataset. However, I would like to know how to return the top 10 categories (Category column) of that 650 crimes occurred in 2011-01-01. From the documentation I read about index selecting and slicing. Nevertheless I still do not figure out how to return such categories.

Answer 1

I think this does what you want, firstly construct a logic index with df.Dates == "2011-01-01" to filter rows on date 2011-01-01 and specify Category at the column index to select only the Category column, thus you get all the Category on 2011-01-01. Use the value_counts() function to make a frequency table for each category and sort by the frequency which by default is in ascending order, in order to get the most frequent categories, you can use the list [::-1] reverse index to reverse the frequency counts and use [:10] to pick up the first 10 elements which will be categories of top ten most frequent:

df.loc[df.Dates == "2011-01-01", "Category"].value_counts().sort_values()[::-1][:10]