CODEWITHSUNDEEP
pythonpandasdataframe
Ben
Ben

Reputation: 21625

How do I apply this function to each group in my DataFrame

Relatively new to Pandas, coming from an R background. I have a DataFrame like so

import pandas as pd
import numpy as np

df = pd.DataFrame({'ProductID':[0,5,9,3,2,8], 'StoreID':[0,0,0,1,1,2]})

   ProductID  StoreID
0          0        0
1          5        0
2          9        0
3          3        1
4          2        1
5          8        2

For each StoreID, how do I label the rows of df as 1, 2, ... based on the ordered ProductID? Then, how do I normalize those ranks? In other words, How do I achieve the following

df['Product_Rank_Index'] = np.array([1,2,3,2,1,1])
df['Product_Rank_Index_Normalized'] = np.array([1/3, 2/3, 3/3, 2/2, 1/2, 1/1])

   ProductID  StoreID  Product_Rank_Index  Product_Rank_Index_Normalized
0          0        0                   1                       0.333333
1          5        0                   2                       0.666667
2          9        0                   3                       1.000000
3          3        1                   2                       1.000000
4          2        1                   1                       0.500000
5          8        2                   1                       1.000000

I've tried doing some things with df.groupby('StoreID') but couldn't get anything to work.

Upvotes: 0

Views: 86

Answers (3)

DevShark
DevShark

Reputation: 9112

Ben as you pointed out, you can do this:

 df.groupby('StoreID').ProductID.apply(lambda x: x.rank()/len(x))

Note that using transform achieves the same result but is better practice and faster (about twice as fast):

In [32]: %timeit df.groupby('StoreID').ProductID.apply(lambda x: x.rank()/len(x))
100 loops, best of 3: 2.26 ms per loop

In [31]: %timeit df.groupby('StoreID').ProductID.transform(lambda x: x.rank()/len(x))
1000 loops, best of 3: 1.3 ms per loop

Upvotes: 1

Patrick the Cat
Patrick the Cat

Reputation: 2158

You can first sort the values then group, like the following

>>> import pandas as pd
>>> df = pd.DataFrame({'ProductID':[0,5,9,3,2,8], 'StoreID':[0,0,0,1,1,2]})
>>> df.sort(['StoreID', 'ProductID'], inplace=True)
>>> df                
   ProductID  StoreID 
0          0        0 
1          5        0 
2          9        0 
4          2        1 
3          3        1 
5          8        2 
>>> df.groupby('StoreID').apply(lambda grp: range(1, len(grp)+1))
StoreID
0    [1, 2, 3]
1       [1, 2]
2          [1]
>>> df.groupby('StoreID').apply(lambda grp: range(1, len(grp)+1)).sum()
[1, 2, 3, 1, 2, 1]
>>> df['Rank'] = df.groupby('StoreID').apply(lambda grp: range(1, len(grp)+1)).sum()
>>> df
   ProductID  StoreID  Rank
0          0        0     1
1          5        0     2
2          9        0     3
4          2        1     1
3          3        1     2
5          8        2     1

Then you can normalize your rank your way..

Upvotes: 1

Ben
Ben

Reputation: 21625

Figured it out thanks to this answer.

df.groupby('StoreID').ProductID.apply(lambda x: x.rank()/len(x))

Upvotes: 2

Related Questions