Finding most common values with Pandas GroupBy and value_counts

Question

I am working with two columns in a table.

+-------------+--------------------------------------------------------------+
|  Area Name  |                       Code Description                       |
+-------------+--------------------------------------------------------------+
| N Hollywood | VIOLATION OF RESTRAINING ORDER                               |
| N Hollywood | CRIMINAL THREATS - NO WEAPON DISPLAYED                       |
| N Hollywood | CRIMINAL THREATS - NO WEAPON DISPLAYED                       |
| N Hollywood | ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT               |
| Southeast   | ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT               |
| West Valley | CRIMINAL THREATS - NO WEAPON DISPLAYED                       |
| West Valley | CRIMINAL THREATS - NO WEAPON DISPLAYED                       |
| 77th Street | RAPE, FORCIBLE                                               |
| Foothill    | CRM AGNST CHLD (13 OR UNDER) (14-15 & SUSP 10 YRS OLDER)0060 |
| N Hollywood | VANDALISM - FELONY ($400 & OVER, ALL CHURCH VANDALISMS) 0114 |
+-------------+--------------------------------------------------------------+

I'm using the Groupby and value_counts to find Code Descriptions by Area Name.

df.groupby(['Area Name'])['Code Description'].value_counts()

Is there a way to view only the top 'n' values per Area Name? If I append .nlargest(3) to the code above it only returns result for one Area Name.

+---------------------------------------------------------------------------------+
| Wilshire     SHOPLIFTING-GRAND THEFT ($950.01 & OVER)                         7 |
+---------------------------------------------------------------------------------+

Question was reopened, because here is count TOP N values, in another question MOST COMMON value, so answer from this cannot be used here. — jezrael, Oct 26 '19 at 16:35

score 4 · Accepted Answer · answered May 29 '18 at 21:20

Use head in each group from the results of value_counts:

df.groupby('Area Name')['Code Description'].apply(lambda x: x.value_counts().head(3))

Output:

Area Name                                                                
77th Street  RAPE, FORCIBLE                                                  1
Foothill     CRM AGNST CHLD (13 OR UNDER) (14-15 & SUSP 10 YRS OLDER)0060    1
N Hollywood  CRIMINAL THREATS - NO WEAPON DISPLAYED                          2
             VIOLATION OF RESTRAINING ORDER                                  1
             ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT                  1
Southeast    ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT                  1
West Valley  CRIMINAL THREATS - NO WEAPON DISPLAYED                          2
Name: Code Description, dtype: int64

score 2 · Answer 2 · answered May 29 '18 at 21:23

You can perform a double groupby:

s = df.groupby('Area Name')['Code Description'].value_counts()
res = s.groupby('Area Name').nlargest(3).reset_index(level=1, drop=True)

print(res)

Area Name    Code Description                                            
77th Street  RAPE, FORCIBLE                                                  1
Foothill     CRM AGNST CHLD (13 OR UNDER) (14-15 & SUSP 10 YRS OLDER)0060    1
N Hollywood  CRIMINAL THREATS - NO WEAPON DISPLAYED                          2
             ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT                  1
             VANDALISM - FELONY ($400 & OVER, ALL CHURCH VANDALISMS) 0114    1
Southeast    ASSAULT WITH DEADLY WEAPON, AGGRAVATED ASSAULT                  1
West Valley  CRIMINAL THREATS - NO WEAPON DISPLAYED                          2
Name: Code Description, dtype: int64

Finding most common values with Pandas GroupBy and value_counts

2 Answers2