How To Groupby().transform() To Value_counts() In Pandas?

I am processing a pandas dataframe df1 with prices of items. Item Price Minimum Most_Common_Price 0 Coffee 1 1 2 1 Coffee 2 1 2 2 Coffee 2 1

Solution 1:

You could use groupby + transform with value_counts and idxmax.

df['Most_Common_Price'] = (
    df.groupby('Item')['Price'].transform(lambda x: x.value_counts().idxmax()))

df

     Item  Price  Minimum  Most_Common_Price
0  Coffee      1        1                  2
1  Coffee      2        1                  2
2  Coffee      2        1                  2
3     Tea      3        3                  4
4     Tea      4        3                  4
5     Tea      4        3                  4

---

An improvement involves the use of pd.Series.map,

# Thanks, Vaishali!df['Item'] = (df['Item'].map(df.groupby('Item')['Price']
                        .agg(lambda x: x.value_counts().idxmax()))
df

     Item  Price  Minimum  Most_Common_Price
0  Coffee      1        1                  2
1  Coffee      2        1                  2
2  Coffee      2        1                  2
3     Tea      3        3                  4
4     Tea      4        3                  4
5     Tea      4        3                  4

Solution 2:

A nice way is to use pd.Series.mode, if you want the most common element (i.e. the mode).

In [32]: df
Out[32]:
     Item  Price  Minimum
0  Coffee      1        1
1  Coffee      2        1
2  Coffee      2        1
3     Tea      3        3
4     Tea      4        3
5     Tea      4        3

In [33]: df['Most_Common_Price'] = df.groupby(["Item"])['Price'].transform(pd.Series.mode)

In [34]: df
Out[34]:
     Item  Price  Minimum  Most_Common_Price
0  Coffee      1        1                  2
1  Coffee      2        1                  2
2  Coffee      2        1                  2
3     Tea      3        3                  4
4     Tea      4        3                  4
5     Tea      4        3                  4

As @Wen noted, pd.Series.mode can returns a pd.Series of values, so just grab the first:

Out[67]:
     Item  Price  Minimum
0  Coffee      111  Coffee      212  Coffee      213     Tea      334     Tea      435     Tea      436     Tea      33

In [68]: df[df.Item =='Tea'].Price.mode()
Out[68]:
0314
dtype: int64

In [69]: df['Most_Common_Price'] = df.groupby(["Item"])['Price'].transform(lambda S: S.mode()[0])

In [70]: df
Out[70]:
     Item  Price  Minimum  Most_Common_Price
0  Coffee      1121  Coffee      2122  Coffee      2123     Tea      3334     Tea      4335     Tea      4336     Tea      333