harald12345 harald12345 - 1 year ago 70
Python Question

Pandas - breaking the adding/subtracting of a cumsum() code in a pandas dataframe

I have a pandas df and with

df['Battery capacity'] = df['total_load'].cumsum() + 5200

I subtract the values from "total_load" with the values from "Battery_capacity".

enter image description here

So, now I would like to add something to my code that breaks the adding/subtracting at a certain value. For example I don't want any higher values than 5200. So let's say at 13:00:00 the adding up should stop at 5200.
How could I implement that in my code? Scott Boston proposed an if-statement, but how would you do that with my code
df['Battery capacity'] = df['total_load'].cumsum(if battery capacity = 5200, then stop adding) + 5200

Should I try to write a function?

Output should be something like that:

time total_load battery capacity
2016-06-01 12:00:00 2150 4487.7
2016-06-01 13:00:00 1200 5688 (but should stop at 5200)
2016-06-01 14:00:00 1980 5200 (don't actually add values now because we are still at 5200)

Answer Source

You can use np.clip to clip upper and lower bounds.

df['Battery capacity'] = np.clip(df['total_load'].cumsum() + 5200,-np.inf,5200)

Or as @jezrael points out Pandas Series has clip method:

df['Battery capacity'] = (df['total_load'].cumsum() + 5200).clip(-np.inf,5200)

Output:

                     Battery capacity  total_load
2016-01-01 00:00:00         4755.0000   -445.0000
2016-01-01 01:00:00         4375.0000   -380.0000
2016-01-01 02:00:00         4025.0000   -350.0000
2016-01-01 03:00:00         3685.0000   -340.0000
2016-01-01 04:00:00         2955.4500   -729.5500
2016-01-01 05:00:00         1870.4500  -1085.0000
2016-01-01 06:00:00          879.1500   -991.3000
2016-01-01 07:00:00        -2555.8333  -3434.9833
2016-01-01 08:00:00        -1952.7503    603.0830
2016-01-01 09:00:00         -864.7503   1088.0000
2016-01-01 10:00:00         1155.2497   2020.0000
2016-01-01 11:00:00         2336.2497   1181.0000
2016-01-01 12:00:00         4486.2497   2150.0000
2016-01-01 13:00:00         5200.0000   1200.8330
2016-01-01 14:00:00         5200.0000   1980.0000
2016-01-01 15:00:00         5200.0000   -221.2667

Now, if you didn't want the value to go below zero replace -np.inf with 0.

                     Battery capacity  total_load
2016-01-01 00:00:00         4755.0000   -445.0000
2016-01-01 01:00:00         4375.0000   -380.0000
2016-01-01 02:00:00         4025.0000   -350.0000
2016-01-01 03:00:00         3685.0000   -340.0000
2016-01-01 04:00:00         2955.4500   -729.5500
2016-01-01 05:00:00         1870.4500  -1085.0000
2016-01-01 06:00:00          879.1500   -991.3000
2016-01-01 07:00:00            0.0000  -3434.9833
2016-01-01 08:00:00            0.0000    603.0830
2016-01-01 09:00:00            0.0000   1088.0000
2016-01-01 10:00:00         1155.2497   2020.0000
2016-01-01 11:00:00         2336.2497   1181.0000
2016-01-01 12:00:00         4486.2497   2150.0000
2016-01-01 13:00:00         5200.0000   1200.8330
2016-01-01 14:00:00         5200.0000   1980.0000
2016-01-01 15:00:00         5200.0000   -221.2667
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download