-1

I'm trying to compute the cumulative sum in python based on a two different conditions.

As you can see in the attached image, Calculation column would take the same value as the Number column as long as the Cat1 and Cat2 column doesn't change.

Once Cat1 column changes, we should reset the Number column.

Calculation column stays the same as the Number column, Once Cat2 column changes with the same value of Cat1 column, the Calculation column will take the last value of the Number column and add it to the next one.

Example of data below:

Cat1    Cat2    Number  CALCULATION
a   orange  1   1
a   orange  2   2
a   orange  3   3
a   orange  4   4
a   orange  5   5
a   orange  6   6
a   orange  7   7
a   orange  8   8
a   orange  9   9
a   orange  10  10
a   orange  11  11
a   orange  12  12
a   orange  13  13
b   purple  1   1
b   purple  2   2
b   purple  3   3
b   purple  4   4
b   purple  5   5
b   purple  6   6
b   purple  7   7
b   purple  8   8
b   silver  1   9
b   silver  2   10
b   silver  3   11
b   silver  4   12
b   silver  5   13
b   silver  6   14
b   silver  7   15

enter image description here

enter image description here

4.Pi.n
  • 1,151
  • 6
  • 15
GusRo
  • 7
  • 4

1 Answers1

2

Are you looking for:

import pandas as pd
df = pd.DataFrame({'Cat1': ['a','a','a','a','a','a','a','a','a','a','a',  'a','a','b','b','b','b','b','b','b','b','b','b','b','b','b','b','b'],
 'Cat2': ['orange','orange','orange','orange','orange','orange','orange',  'orange','orange','orange','orange','orange','orange','purple','purple',  'purple','purple','purple','purple','purple','purple','silver','silver','silver',  'silver','silver','silver','silver']})

df['Number'] = df.groupby(['Cat1', 'Cat2']).cumcount()+1
df['CALCULATION'] = df.groupby('Cat1').cumcount()+1
Chris
  • 15,819
  • 3
  • 24
  • 37
  • Hello, thank you for your solution. However, maybe it was a bad Example putting the "Number" column to increase by one all the time, but this is not the case. It is always increasing but not necessarily by one. – GusRo Jan 17 '21 at 01:21