How to progressively decrease a value from a column of a pandas dataframe

Question

Suppose I have a dataframe as listed below:

[1] df
    name   value
     a      116
     b      116
     c      116
     d      225
     e      225
     f      225
     g      225

Now I want the df to become

    name   value
     a      116
     b      115
     c      114
     d      225
     e      224
     f      223
     g      222

That is, wherever original df had same (fixed) values in a column in consecutive rows, it should decrease down progressively by 1. So, values in value column for names a,b,c go from 116 to 114. And for d,e,f,g go from 225 to 222.

Kindly advice.

Answer 1

Use GroupBy.cumcount for count consecutive values and subtract from column value:

#consecutive rows to Series g
g = df['value'].ne(df['value'].shift()).cumsum()
df['value'] = df['value'] - df.groupby(g).cumcount()
print (df)
  name  value
0    a    116
1    b    115
2    c    114
3    d    225
4    e    224
5    f    223
6    g    222

Answer 2

If consecutive values are unique, you can transform

df.groupby('value').value.transform(lambda k: k - k.reset_index().index)

0    116
1    115
2    114
3    225
4    224
5    223
6    222