CODEWITHSUNDEEP
pythonpandas
Jio
Jio

Reputation: 608

Increment column value based on value of another column

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.

Upvotes: 3

Views: 1352

Answers (2)

Cameron Riddell
Cameron Riddell

Reputation: 13407

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

Upvotes: 3

ThePyGuy
ThePyGuy

Reputation: 18406

Getting the cumcount is fine, assign it to a new column, then decrease the values of cumcount for the records where count is less than cumcount, finally add the value and cumcount and assign it to value1

df['cumcount'] = df.groupby('ID')['Value'].cumcount() + 1
while(df['cumcount']>df['counter']).any():
    df.loc[df['cumcount'] > df['counter'], 'cumcount'] = df['cumcount'] - df['counter']

df['value1'] = df['Value'] + df['cumcount']

OUTPUT:

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

Upvotes: 0

Related Questions