0

I would need to create a new column as follows:

  • if the frequency of an item is greater or equal than 5 then set 'best seller';
  • if the frequency of an item is between 2 (inclusive) and 5 then set 'ok';
  • if the frequency of an item is lower than 2 then set 'bad'.

Suppose that my dataset looks like

Items          Date 
calzini      2020/02/23
cintura      2020/02/21
maglietta    2020/02/23
maglietta    2020/02/22
cappello     2020/02/23
jeans        2020/02/23
cappello     2020/02/22
maglietta    2020/02/22
maglietta    2020/02/22
jeans        2020/02/22
jeans        2020/02/23
maglietta    2020/02/23
jeans        2020/02/22
jeans        2020/02/23

I would like to have

Items         Category            
calzini        bad
cintura        bad
maglietta     best seller
maglietta     best seller
jeans         best seller
cappello       ok
jeans         best seller
cappello       ok
maglietta     best seller
maglietta     best seller
jeans         best seller
maglietta     best seller
jeans         best seller
jeans         best seller

I already determined the frequency of the items as follows:

sold_items=df.groupby(['Items'])['Date'].count().sort_values(ascending=False) # the items should be counted overall, not using a specific Date! It is about how many items were sold

I would like to ask you how to create a new column with those values.

Georgy
  • 12,464
  • 7
  • 65
  • 73
  • I think I am missing one step. I should associate to each item its frequency, then apply the if condition. –  Jun 26 '20 at 19:32
  • Please provide a [mcve], and clarify what exactly the issue is. – AMC Jun 26 '20 at 20:24
  • @AMC I think I provided data for reproduce the example. I already received many answers that helped me. –  Jun 26 '20 at 22:37
  • 1
    _I think I provided data for reproduce the example._ In a format which is far from convenient, and that doesn't make it a reproducible example. _I already received many answers that helped me._ I'm glad to hear that. – AMC Jun 26 '20 at 22:39

6 Answers6

3

The code below should work.

df['category'] = pd.cut(df['sold_items'],bins = [0,1,4,df['sold_items'].max()],labels = ['bad','ok','best seller'])
rhug123
  • 7,893
  • 1
  • 9
  • 24
2

You can use GroupBy.transform and use np.select

vals = df['Items'].groupby(df['Items']).transform('count')
condlist = [vals.ge(5), (vals.ge(2) & vals.lt(5)) , vals.le(2)]
choicelist = ['best seller', 'ok', 'bad']
df.assign(category =  np.select(condlist, choicelist))

        Items        Date     category
0     calzini  2020/02/23          bad
1     cintura  2020/02/21          bad
2   maglietta  2020/02/23  best seller
3   maglietta  2020/02/22  best seller
4    cappello  2020/02/23           ok
5       jeans  2020/02/23  best seller
6    cappello  2020/02/22           ok
7   maglietta  2020/02/22  best seller
8   maglietta  2020/02/22  best seller
9       jeans  2020/02/22  best seller
10      jeans  2020/02/23  best seller
11  maglietta  2020/02/23  best seller
12      jeans  2020/02/22  best seller
13      jeans  2020/02/23  best seller
Ch3steR
  • 20,090
  • 4
  • 28
  • 58
2

You can use cut on value_counts:

pd.cut(df['Items'].value_counts(),bins=[0,1,4,10])

maglietta    (4, 10]
jeans        (4, 10]
cappello      (1, 4]
calzini       (0, 1]
cintura       (0, 1]
Name: Items, dtype: category
Categories (3, interval[int64]): [(0, 1] < (1, 4] < (4, 10]]

So this cut does not include the lowest, hence round brackets on left, and includes highlist, square brackets on right. Now we convert these labels to what you need:

cats = pd.cut(df['Items'].value_counts(),bins=[0,1,4,10],labels=['bad','ok','best seller'])

And just map the values according to the category and assign it to a new column using .tonumpy() (thanks to @Ch3steR for pointing it out, see comments):

df['Category'] = cats[df['Items']].to_numpy()

df

    Items       Date        Category
0   calzini     2020/02/23  bad
1   cintura     2020/02/21  bad
2   maglietta   2020/02/23  best seller
3   maglietta   2020/02/22  best seller
4   cappello    2020/02/23  ok
5   jeans       2020/02/23  best seller
6   cappello    2020/02/22  ok
7   maglietta   2020/02/22  best seller
8   maglietta   2020/02/22  best seller
9   jeans       2020/02/22  best seller
10  jeans       2020/02/23  best seller
11  maglietta   2020/02/23  best seller
12  jeans       2020/02/22  best seller
13  jeans       2020/02/23  best seller

You can also use df['Category'] = df['Items'].map(cats)

StupidWolf
  • 45,075
  • 17
  • 40
  • 72
  • Really nice answer. +1 `df.values` shouldn't be used mentioned in the [docs](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.values.html), replace it with `.to_numpy()` or `df['Category'] = df['Items'].map(cats)` – Ch3steR Jun 26 '20 at 19:47
  • 1
    I see, wasn't aware of that! Thanks for pointing it out. – StupidWolf Jun 26 '20 at 19:49
  • 1
    Glad to have helped. Feel free to add them to the answer, comments might get deleted. And thanks for the credits, actually I did nothing than just point to correct approach you are being humble, no need to credit. ;) – Ch3steR Jun 26 '20 at 19:57
  • Seems very good, can you please share how you choosed bins from conditions 2-5,>5 and <2? bins=[0,1,4,10] – A.B Jun 26 '20 at 19:59
  • 1
    @A.B, it's the way pd.cut works, when you provide the breaks, one interval is defined as > LB (lower bound) and <= UB (upper bound). So for 2-4, it will be 1 (2>1) and 4 (<=4). For >5 you just need a right boundary that is greater than 5, can be 10,20 anything. Same thing for the left. – StupidWolf Jun 26 '20 at 20:05
0

Use groupby and transform. You'll also need to create a function to categorize your items:

def categorize(x):
    num = len(x)
    if num >= 5:
        return 'best seller'
    elif num >= 3:
        return 'ok'
    else:
        return 'bad'

df['category'] = df.groupby('Items').transform(categorize)
busybear
  • 10,194
  • 1
  • 25
  • 42
0

According to what you have defined, you dont have dependency of category on date(as assumed by the outpu).

You can simply use apply function on axis 1

def testfun(e):
  count = len(df[df["Items"] == e["Items"]])
  if(count>=5):
    return "best seller"
  if(count>=2 and count<5 ):
    return "ok"
  else:
    return "bad"    


df["count"] = df.apply(testfun,axis=1)

1   cintura bad
2   maglietta   best seller
3   maglietta   best seller
4   cappello    ok
5   jeans   best seller
6   cappello    ok
7   maglietta   best seller
8   maglietta   best seller
9   jeans   best seller
10  jeans   best seller
11  maglietta   best seller
12  jeans   best seller
13  jeans   best seller
A.B
  • 20,110
  • 3
  • 37
  • 71
  • 1
    `df.apply` over axis 1 is generally avoided as it's inefficient more [info here](https://stackoverflow.com/questions/54432583/when-should-i-ever-want-to-use-pandas-apply-in-my-code). – Ch3steR Jun 26 '20 at 19:44
  • @Ch3steR, didnt know it. Great, your answer is cleaner – A.B Jun 26 '20 at 19:47
  • 1
    Glad to have helped. @stupidWolf's answer is really nice tho. – Ch3steR Jun 26 '20 at 19:49
0

You can also replace the conditional values in value_counts and then map:

counts = df['Items'].value_counts()
counts = counts.replace(counts.values, ['best seller' if i >= 5 else ('ok' if i in [2,3,4] else 'bad') for i in counts])
df['category'] = df['Items'].map(counts)
Tom
  • 8,310
  • 2
  • 16
  • 36