Increment column value based on value of another column

user_3578056 0
Viewed 439

I have a df as below:


       ID  Value  counter  
    0  A   30      3
    1  A   30      3
    2  A   30      3
    3  A   30      3
    4  A   30      3
    5  A   30      3
    6  B   50       2
    7  B   50       2
    8  B   50       2
    9  B   50       2
   10  C   40      2
   11  C   40      2
   12  C   40      2
   13  C   40      2

I want to add a new column Value1. At every repetition of ID the value in column Value should be incremented and saved in column Value1. However, it should only be incremented by number given in column Counter then it should repeat. Below is the desired output.

   ID  Value  counter  Value1
0  A   30      3        31
1  A   30      3        32
2  A   30      3        33
3  A   30      3        31
4  A   30      3        32
5  A   30      3        33 
6  B   50      2        51
7  B   50      2        52
8  B   50      2        51
9  B   50      2        52
10  C   40      2        41
11  C   40      2        42
12  C   40      2        41
13  C   40      2        42

My attempt:

df['Value1'] = df['Value']
df['Value1'] += df.groupby('ID')['Value1'].cumcount() +1

But this does not consider the counter and just increments. How is it possible to repeat the increment after the counter value has reached.

pandas python
1 Answers

You'll need to use the modulo operator to reset your .cumcount by the value of df["counter"]

df["Value1"] = (
    df["Value"] + df.groupby("ID")["Value"].cumcount().mod(df["counter"]).add(1)
)

print(df)
   ID  Value  counter  Value1
0   A     30        3      31
1   A     30        3      32
2   A     30        3      33
3   A     30        3      31
4   A     30        3      32
5   A     30        3      33
6   B     50        2      51
7   B     50        2      52
8   B     50        2      51
9   B     50        2      52
10  C     40        2      41
11  C     40        2      42
12  C     40        2      41
13  C     40        2      42
user_142784480
Related