How can I get the most popular item in a group in pandas?

user_8759685 0
Viewed 5053

I have a Pandas Dataframe containing cars for sale and I'd like to get the most popular for each brand, however I seem unable to do this.

I have a pandas dataframe with some columns (e.g: vehicle type, price, mileage, year, brand, model, etc) and for each car brand, I'd like to check which model occurs the most. I've tried to use a groupby, like this: 

popular_models = dataset.groupby('brand').model.value_counts().groupby(level=0).nlargest(1)

But it returns a Pandas Series in which some of the data I want is stored in the indices and it also adds one repeated column that is not making any sense to me.

I'd like to get a a DataFrame containing 3 columns, like this:

(https://imgur.com/a/BkKBrv9)

However, I'm getting a pandas series like this:

(https://imgur.com/a/u8CSXY4)

Can someone please help me figure this out?

pandas reshape series python
3 Answers

You have to groupby both objects you want to keep, then count the one you want to find the occurrences of. Here is the example input file:

Brand   Model
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Acura   RDX
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
Beach   Baby
BMW     320i
BMW     320i
BMW     320i
BMW     320i
BMW     320i
BMW     320i
BMW     320i
BMW     550i
BMW     550i
BMW     550i
BMW     550i
BMW     550i
BMW     550i
BMW     550i
Cadillac        Escalade
Cadillac        Escalade
Cadillac        Escalade
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo
Chana   Cargo

The simple pandas one liner:

df = pd.read_table('fun.txt', header=0)
print(df.groupby(['Brand','Model'])['Model'].agg(['count']))

And the output:

                   count
Brand    Model
Acura    RDX          10
BMW      320i          7
         550i          7
Beach    Baby         10
Cadillac Escalade      3
Chana    Cargo        12

If you want to sort the values by frequency (largest to smallest) and keep only the largest change the one-liner to:

groupby_df = (df.groupby(['Brand','Model'])['Model'].agg(['count']).sort_values(by='count', ascending=False).reset_index().drop_duplicates('Brand', keep='first'))

to get:

      Brand     Model  count
0     Chana     Cargo     12
1     Acura       RDX     10
2     Beach      Baby     10
3       BMW      320i      7
5  Cadillac  Escalade      3
user_84682640

One solution is to sort and then drop duplicates after a groupby operation:

df = pd.DataFrame({'Brand': ['B1'] * 5 + ['B2'] * 5,
                   'Model': ['M1', 'M2', 'M1', 'M2', 'M3',
                             'N1', 'N1', 'N2', 'N3', 'N1']})

df['Count'] = df.groupby(['Brand', 'Model'])['Model'].transform('count')

res = df.sort_values('Count', ascending=False)\
        .drop_duplicates('Brand')

print(res)

#   Brand Model  Count
# 5    B2    N1      3
# 0    B1    M1      2

Note this removes duplicate groupwise top counts.

user_92095460

Here is one approach.

  1. Setup the DataFrameGroupBy object:

    df.groupby(["Brand", "Model"])

  2. Use the GroupBy size function to compute sizes of each subgroup (returns as a Series):

    df.groupby(["Brand", "Model"]).size()

  3. Convert back into DataFrame while naming the column containing the values computed by size:

    df.groupby(["Brand", "Model"]).size().reset_index(name="Count")

  4. Sort the DataFrame in descending order by the Count of subgroup items:

    df.groupby(["Brand", "Model"]).size().reset_index(name="Count").sort_values(by="Count", ascending=False)

  5. Drop duplicate Brand values keeping the first entry in the DataFrame:

    df.groupby(["Brand", "Model"]).size().reset_index(name="Count").sort_values(by="Count", ascending=False).drop_duplicates("Brand", keep="first")

user_2248550
Related