How to merge pandas value_counts() to dataframe or use it to subset a dataframe

user_2476665 0
Viewed 33520

I used pandas df.value_counts() to find the number of occurrences of particular brands. I want to merge those value counts with the respective brands in the initial dataframe.

 df has many columns including one named 'brands'
 brands = df.brands.value_counts()

 brand1   143
 brand2   21
 brand3   101
 etc.

How do I merge the value counts with the original dataframe such that each brand's corresponding count is in a new column, say "brand_count"?

Is it possible to assign headers to these columns; the names function won't work with series and I was unable to convert it to a dataframe to possibly merge the data that way. But, value_counts outputs a Series of dtype int64 (brand names should be type string) which means I cannot do the following:

 df2 = pd.DataFrame({'brands': list(brands_all[0]), "brand_count":
 list(brands_all[1])})
 (merge with df)

Ultimately, I want to obtain this:

 col1  col2  col3  brands  brand_count ... col150
                   A        30
                   C        140
                   A        30
                   B        111
pandas python
5 Answers

i think the best way is to use map

df['brand_count']= df.brand.map(df.brand.value_counts())

this is so much faster than groupby method for example (factor 500 on a 15000 row df) and take only one line

user_109111690

Pandas DataFrame's merge and value_counts attributes are pretty fast, so I would combine the two.

df.merge(df['brand'].value_counts().to_frame(), how='left', left_on='brand',
         right_index=True, suffixes=('', 'x'))\
  .rename(columns={'brandx':'brand_count'})
user_82884970
Related