CODEWITHSUNDEEP
pythonpandaspandas-groupby
Amit
Amit

Reputation: 327

Group by : condition and action on 2 different variable

I want to get the distance from peak week and create a new variable ,this peak week is bases on sales.

for example ID "ppr5007288231" has peak week 3 than in new variable week 3 would be 0 and previous week and later week would be increasing by 1.

Note : data has multiple IDs

        Id        week  sales    output
0   ppr5007288231   1   3750.84   2
1   ppr5007288231   2   4562.75   1
2   ppr5007288231   3   12649.65  0
3   ppr5007288231   4   4926.81   1
4   ppr5007288231   5   5226.09   2
5   ppr5007288231   6   6672.11   3
6   ppr5007288231   7   6833.04   4
7   ppr5007288231   8   6759.29   5
8   ppr5007288232   1   7021.55   1
9   ppr5007288232   2   9031.93   0
10  ppr5007288232   3   6240.72   1

Upvotes: 0

Views: 63

Answers (1)

Quang Hoang
Quang Hoang

Reputation: 150785

Try with idxmax and cumcount:

groups = df.groupby('Id')

# enumerate the rows within groups
enum = groups['sales'].transform('cumcount')

# record the locations of max
max_loc = groups['sales'].transform('idxmax')
df['out1'] = (enum - enum.loc[max_loc].values).abs()

Output:

               Id  week     sales  output  out1
0   ppr5007288231     1   3750.84       2     2
1   ppr5007288231     2   4562.75       1     1
2   ppr5007288231     3  12649.65       0     0
3   ppr5007288231     4   4926.81       1     1
4   ppr5007288231     5   5226.09       2     2
5   ppr5007288231     6   6672.11       3     3
6   ppr5007288231     7   6833.04       4     4
7   ppr5007288231     8   6759.29       5     5
8   ppr5007288232     1   7021.55       1     1
9   ppr5007288232     2   9031.93       0     0
10  ppr5007288232     3   6240.72       1     1

Upvotes: 2

Related Questions