Python Pandas : Change value based on category's highest frequency

user_13091251 0
Viewed 125

I discovered a categorization error in a dataset I was exploring.

The dataframe looks like this:

df1 = pd.DataFrame({
                     'product': ['ABRICOTS', 'ABRICOTS', 'ABRICOTS', 'ABRICOTS', 'AILS','AILS', 'ANANAS', 'ANANAS', 'ANANAS','ANANAS','ANANAS','ANANAS','ANANAS'], 
                     'family': ['Fruit','Fruit','Fruit', 'Vegetable', 'Vegetable','Vegetable', 'Fruit', 'Fruit','Fruit','Fruit','Cereal','Cereal','Animal Product']
                    })

enter image description here

Each product is supposed to belong to only 1 family category, but using the formula below revealed that I have products with different categories:

df1.groupby(['product','family']).size().rename('Freq').reset_index()

enter image description here

Now what I would like to do, is to use a formula to change for each product the family(ies) with the lowest frequency number to the one with the highest in my original dataframe. The expected result would look like this:

enter image description here

pandas dataframe python
3 Answers

First use Dataframe.groupby to group the dataframe on product then use the aggregation function Series.value_counts to calculate the freq of each family category belonging to the product, next create a mapping series m by using groupby at level=0 on this series s with the aggregation function nlargest to get the family associated with maximum occurrence for that product, finally use Series.map to map the product to its corresponding family:

s = df1.groupby('product')['family'].value_counts()
m = s.groupby(level=0, group_keys=False).nlargest(1).reset_index(level=1, name='freq')
df1['family'] = df1['product'].map(m['family'])

Explanation:

# print(s)
product   family        
ABRICOTS  Fruit             3
          Vegetable         1
AILS      Vegetable         2
ANANAS    Fruit             4
          Cereal            2
          Animal Product    1
Name: family, dtype: int64

# print(m)
            family  freq
product                  
ABRICOTS      Fruit     3
AILS      Vegetable     2
ANANAS        Fruit     4

# print(df1)
     product     family
0   ABRICOTS      Fruit
1   ABRICOTS      Fruit
2   ABRICOTS      Fruit
3   ABRICOTS      Fruit
4       AILS  Vegetable
5       AILS  Vegetable
6     ANANAS      Fruit
7     ANANAS      Fruit
8     ANANAS      Fruit
9     ANANAS      Fruit
10    ANANAS      Fruit
11    ANANAS      Fruit
12    ANANAS      Fruit
user_128331660

Hope this helps,

df1 = pd.DataFrame({
                     'product': ['ABRICOTS', 'ABRICOTS', 'ABRICOTS', 'ABRICOTS', 'AILS','AILS', 'ANANAS', 'ANANAS', 'ANANAS','ANANAS','ANANAS','ANANAS','ANANAS'], 
                     'family': ['Fruit','Fruit','Fruit', 'Vegetable', 'Vegetable','Vegetable', 'Fruit', 'Fruit','Fruit','Fruit','Cereal','Cereal','Animal Product']
                    })

Sorting and drop the rows.

d=df1.groupby(['product','family']).size().rename('Freq').reset_index().\
             sort_values(['product','Freq'],ascending=False).\
             drop_duplicates('product')[['product','family']]\
             .set_index('product')['family'].to_dict()

Get this dict
enter image description here

Use map function

df1['family'] = df1['product'].map(d)

Required output
enter image description here

Thanks.

user_59629380

You can use pandas groupby and transform functions in combination with the series' mode method:

df1["family"] = df1.groupby("product")["family"].transform(lambda s: s.mode()[0])

Unfortunately we need wrap the mode method in a lambda function here as the mode method returns an array (there might be multiple top candidates) and single values are required. Hence the s.mode()[0]. You could also use s.mode().squeeze() which will break if there's ambiguity.

user_48000860
Related