Pandas dataframe - Using np.clip() to set boundaires and subtracting values from a specific value in a column

Question

I have a pandas df and with df['Battery capacity'] = np.clip(df['total_load'].cumsum() + 5200,-np.inf,5200) I compute the values from "total_load" with the values from "Battery_capacity".

The problem which I cannot solve is, that for example at 14:00:00 "Battery capacity" is at 5200 but at 15:00:00 it doesn't substract the -221 from 5200 because it subtract from the value which is adding up in the background. I set the bounds to 5200 with np.clip()

So what I would like to have is something like:

time                total_load   battery capacity
2016-06-01 14:00:00   1980        5200
2016-06-01 15:00:00   -221        4979 (start subtracting from 5200) 
2016-06-01 16:00:00   -14.5       4964.5

How would I implement that into my code?

Thanks!

Please look at [How to Ask a Pandas Question](https://stackoverflow.com/a/20159305/4909087). — cs95, Aug 17 '17 at 17:10

score 1 · Accepted Answer · answered Aug 17 '17 at 17:53

The problem is that np.clip is computed after np.cumsum so by the time the sum gets to time 14:00:00 the sum is much greater than 5200 hence no subtraction.

I think looping may be the simplest way, however someone else may provide a better vectorized solution

val = 0
for index,row in df.iterrows():
    val = min(row['total_load']+val,5200)
    df.loc[index,'Battery capacity'] = val

Pandas dataframe - Using np.clip() to set boundaires and subtracting values from a specific value in a column

1 Answers1