Count the most frequent value and manipulate it

Question

I have a dataframe as follow:

            User           Bought 

0           U296             PC
1           U300            Table
2           U296             PC
3           U296            Chair

I would like to create 2 columns, one displays the most bought item for a user and the second displays the number of times this item is been bought so I get at the end:

            User           Bought         Most_Bought          Times_bought

0           U296             PC               PC                    2
1           U300            Table            Table                  1
2           U296             PC               PC                    2
3           U296            Chair             PC                    2

I know that I should do something like a groupby and make use of mode() but I'm missing the final touch.

Thank you for help !

Answer 1

UPDATE:

In [330]: g = df.groupby('User')['Bought']

In [331]: vc = g.value_counts().to_frame(name='Times_bought').reset_index()

In [332]: df = df.merge(vc)

In [333]: df
Out[333]:
   User Bought  Times_bought Most_Bought
0  U296     PC             2          PC
1  U296     PC             2          PC
2  U300  Table             1       Table
3  U296  Chair             1          PC

In [334]: df['Most_Bought'] = df['User'].map(g.agg(lambda x: x.mode()[0]))

In [335]: df
Out[335]:
   User Bought  Times_bought Most_Bought
0  U296     PC             2          PC
1  U296     PC             2          PC
2  U300  Table             1       Table
3  U296  Chair             1          PC

---

Old answer:

IIUC:

In [222]: x = df.groupby('User')['Bought'] \
     ...:       .agg([lambda x: x.mode()[0], 'nunique']) \
     ...:       .rename(columns={'<lambda>':'Most_Bought','nunique':'Times_bought'})
     ...:

In [223]: df.merge(x, left_on='User', right_index=True)
Out[223]:
   User Bought Most_Bought  Times_bought
0  U296     PC          PC             2
2  U296     PC          PC             2
3  U296  Chair          PC             2
1  U300  Table       Table             1

preserving original order:

In [258]: df.merge(x, left_on='User', right_index=True).reindex(df.index)
Out[258]:
   User Bought Most_Bought  Times_bought
0  U296     PC          PC             2
1  U300  Table       Table             1
2  U296     PC          PC             2
3  U296  Chair          PC             2

Helper DF:

In [224]: x
Out[224]:
     Most_Bought  Times_bought
User
U296          PC             2
U300       Table             1

Answer 2

Take me long time to make it came true :) By using value_counts

df[['Most_Bought','Times_bought']]=df.groupby('User').Bought.transform(lambda x : [pd.Series(x).value_counts()\
                             .reset_index().loc[0].values]).apply(pd.Series)
df
Out[231]: 
   User Bought Most_Bought  Times_bought
0  U296     PC          PC             2
1  U300  Table       Table             1
2  U296     PC          PC             2
3  U296  Chair          PC             2