How to groupby().transform() to value_counts() in pandas?

Question

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       2
3 Tea     3      3       4
4 Tea     4      3       4
5 Tea     4      3       4

I create Minimum using:

df1["Minimum"] = df1.groupby(["Item"])['Price'].transform(min)

How do I create Most_Common_Price?

df1["Minimum"] = df1.groupby(["Item"])['Price'].transform(value_counts()) # Doesn't work

In the moment, I use a multi-step approach:

for item in df1.Item.unique().tolist(): # Pseudocode
 df1 = df1[df1.Price == Item]           # Pseudocode
 df1.Price.value_counts().max()         # Pseudocode

which is overkill. There must be a more simple way, ideally in one line

How to groupby().transform() to value_counts() in pandas?

Answer 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