make new column using value_counts in dataframe pandas-CodePudding

i am using below formula to get value count froma. column in a dataframe:

new_data = df['item'].value_counts()

which give me below result

Apples                    3
Green bananas             2
Bananas                   1
Oranges                   1

what i want is to get output for every item count in in new column like the below excel example

Any help or guidance is appreciated. Thank you

CodePudding user response：

Try with transform

df['new'] = df.groupby('item')['item'].transform('count')

CodePudding user response：

Use Series.map for your solution:

new_data = df['item'].value_counts()
df['Occurence'] = df['item'].map(new_data)

One row solution:

df['Occurence'] = df['item'].map(df['item'].value_counts())

If there is multiple columns:

cols = ['item','item1']
for c in cols:
    df[f'Occurence_{c}'] = df[c].map(df[c].value_counts())

 df.loc[len(df), cols] = df[cols].sum()

Or:

df = df.join(df[cols].apply(lambda x: x.map(x.value_counts())).add_prefix('Occurence_'))

CodePudding user response：

Or by the index:

df.set_index('item').assign(count=df['item'].value_counts()).reset_index()

Since column assigning corresponds to the index, this way would do it.