1

Im trying to do the following: Have a dataframe with columns "Cat" and "Amount". I want to create another column that as long as the values in "Cat" are the same it adds up the differences between the values i and i+1 in the "Amount" column. When the value in "Cat" changes it goes back to 0 and starts again.

Attached an example.

enter image description here

enter image description here

GusRo
  • 7
  • 4
  • hi, interesting, perhaps this might be of interest https://stackoverflow.com/questions/20995196/python-pandas-counting-and-summing-specific-conditions – IronMan Dec 28 '20 at 23:07
  • kindly provide a copy-paste dataframe, not pics. – sammywemmy Dec 28 '20 at 23:23
  • 1
    This is a little confusing. Is "Amount" the same as "Value"? Is "another column" the same as "Total by Cat"? Why is the first value for "Total By Cat" 1 for A but 0 for B and C? – nilo Dec 28 '20 at 23:37
  • Sorry I attached the wrong picture. YOu are right should be 0 for A. THe column name is Amount (changed if from value.) – GusRo Dec 29 '20 at 01:24

2 Answers2

1

You can try the following.Please ignore the value column and total by cat i was trying to replicate your image in excel but i guess the following code should help

cat['r']=cat.groupby('Cat').cumcount()

enter image description here

Rishabh
  • 178
  • 9
  • 1
    Also you can use cumsum() and create a new field in case if you want to sum amounts – Rishabh Dec 29 '20 at 00:19
  • Thank you very much Rishabh. I guess this would work but I would have to calculate the differences between the values. Ideally I want to calculate r0+(value1-value0) and so on – GusRo Dec 29 '20 at 01:40
  • Can you give some example? – Rishabh Dec 29 '20 at 10:06
  • I just added another picture. Hope is more clear that way. Trying to attach a database but not sure how to yet (kinda new here). Thank you – GusRo Dec 29 '20 at 15:33
  • Technically what ever is in the first column you want to subtract it with rest of the values and need a calculation – Rishabh Dec 30 '20 at 12:10
1

Try this as your final solution Final Results

cat['Col_count']=cat.groupby('Cat').cumcount()
cat['sum']=cat.groupby('Cat')['Amount'].cumsum()
cat['min_val']=np.where(cat['Col_count']==0,cat['sum'],0)
cat['min2']=cat.groupby('Cat')['min_val'].cumsum()
cat['Cal']=cat['Amount']-cat['min2']
cat
Rishabh
  • 178
  • 9