CODEWITHSUNDEEP
pythonpandasreplacepandas-groupby
promote
promote

Reputation: 13

Pandas Groupby replace values in decremental order

how can i replace the values in a column in a decremental order with maximum value of the second column is retained and other values are decremented by one from this value for a particular group in pandas?

I have a dataframe with 2 columns A and B

Input :

A B

210 2
210 1
210 5
210 3
145 1
145 3
145 3
145 6

desired output:


A B

210 2
210 3
210 4
210 5
145 3
145 4
145 5
145 6

Upvotes: 1

Views: 119

Answers (2)

ansev
ansev

Reputation: 30930

Use groupby.cumcount and then you can add the difference between the maximum and the group size using groupby.transform:

groups = df.groupby('A').B
df['B']=( groups.cumcount()
                .add(1)
                .add(groups.transform('max')
                           .sub(groups.transform('size')) )
        )
print(df)

Output

     A  B
0  210  2
1  210  3
2  210  4
3  210  5
4  145  3
5  145  4
6  145  5
7  145  6

Time comparision

%%timeit
groups = df.groupby('A').B
df['B']=( groups.cumcount()
                .add(1)
                .add(groups.transform('max')
                           .sub(groups.transform('size')))
        )
#3.33 ms ± 66 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

%%timeit
def custom_f(grp):
  m = grp.max()
  return np.arange(m - grp.shape[0]+1 , m+1)
df['B'] = df[['A','B']].groupby('A').transform(custom_f)
#9.18 ms ± 890 µs per loop (mean ± std. dev. of 7 runs, 100 loops each)

Upvotes: 1

thushv89
thushv89

Reputation: 11333

You can do the following. Basically, we are creating a range for each group and the range goes from max - num_rows + 1 to m.

def custom_f(grp):
  m = grp.max()
  return np.arange(m - grp.shape[0]+1 , m+1)
df['B'] = df[['A','B']].groupby('A').transform(custom_f)

Upvotes: 0

Related Questions