Transform dataframe based on three first rows in pandas

user_7217040 0
Viewed 62

I have a data frame like this (but much bigger) and I am trying to use transform to get the max based ONLY on the first 3 rows of each group.

     df10 = pd.DataFrame({
      'Price': [1,2,3,4,5,10,20,30,40,50],
      'Stock': ['AAPL', 'AAPL', 'AAPL', 'AAPL', 'AAPL', 'IBM','IBM','IBM','IBM','IBM']
     })

This syntax works for the entire column

df10['max_top_3']=df10.groupby("Stock").Price.transform('max')

But I want the 'max_top_3' column to show 3 and 30 respectively for AAPL and IBM >> which is the max number of the first 3 entries in that column

I tried something like this and it gave an error

df10['max_top_3']=df10.groupby("Stock").Price.head(3).transform('max')
pandas-groupby pandas transformation
3 Answers

You can chain the head in transform with a lambda:

df10.groupby("Stock").Price.transform(lambda x: x.head(3).max())

0     3
1     3
2     3
3     3
4     3
5    30
6    30
7    30
8    30
9    30
Name: Price, dtype: int64
user_98406370

I will do

df10.merge(df10.groupby('Stock').head(3).groupby('Stock',as_index=False).Price.max(),on='Stock')
Out[179]: 
   Price_x Stock  Price_y
0        1  AAPL        3
1        2  AAPL        3
2        3  AAPL        3
3        4  AAPL        3
4        5  AAPL        3
5       10   IBM       30
6       20   IBM       30
7       30   IBM       30
8       40   IBM       30
9       50   IBM       30
user_79645270

Sort the dataframe(unnecessary in your case, since the data is already sorted), groupby on Stock, then get the 3rd row, using transform and nth, since data is sorted in descending order :

df10["max_3"] = (df10
                 .sort_values(["Price", "Stock"])
                 .groupby("Stock")
                 .Price
                 .transform("nth", 2)
                 )

df10


Price   Stock   max_3
0   1   AAPL    3
1   2   AAPL    3
2   3   AAPL    3
3   4   AAPL    3
4   5   AAPL    3
5   10  IBM     30
6   20  IBM     30
7   30  IBM     30
8   40  IBM     30
9   50  IBM     30
user_71757130
Related