Continuously subtract value from column using another column-CodePudding

I have a df that looks like this:

Category	Number	Constant
One	141.2	271.01
One	57.4	271.01
One	51.3	271.01
Two	24.69	27.29
Two	12.72	27.29
Two	10.37	27.29

What I want is something that can iterate through each row and calculate a new value of the constant given the previous value of the constant. The resulting dataframe should look something like this:

Category	Number	Constant
One	141.2	129.99
One	57.4	72.59
One	51.3	21.29
Two	24.69	2.6
Two	12.72	-10.12
Two	10.37	-20.49

Update: The calculation is Number-constant for the first calculation and then for the rest would be constant[n-1] - number[n]

Is there a way to do this without using a for loop?

CodePudding user response：

Use a groupby.cumsum to compute the cumulative sum and subtract this from "Constant":

df['Constant'] -= df.groupby('Category')['Number'].cumsum()

Alternatively, if you don't want an in place operation

df['New_Col'] = df['Constant'].sub(df.groupby('Category')['Number'].cumsum())

Output:

  Category  Number  Constant
0      One  141.20    129.81
1      One   57.40     72.41
2      One   51.30     21.11
3      Two   24.69      2.60
4      Two   12.72    -10.12
5      Two   10.37    -20.49

CodePudding user response：

Yes, use the function of pandas

apply

CodePudding user response：

Take the data into a pandas dataframe from csv if in csv

using

df = pandas.read_csv(<filepath>)
df['col_name'] = df['colname'].map(lambda x:fun1(x))
# fun1 can be used for your calculations as a function one liner lambda func