How to reset the row value based on the timestamp?

Question

I am trying to build a simple battery simulation program. Battery charging algorithm is as simple as stated below (t - time instance):

BatteryCharge[t] = BatteryCharge[t-1]-consumption[t]+solar generation[t]

But the battery needs to be completely discharged by a certain period of the day (say, by the end of the day 06:00:00), meaning the batteryCharge column needs to be reset to zero and the discharged amount of electricity should be stored in the other column called "Throwaway electricity[MWh]". When the battery doesn't have sufficient charge (batteryCharge < 0), it imports electricity. The dataframe looks as shown below:

datetime                   Consumption[MWh]  Solar_Gene[MWh]  BatteryCharge[MWh]                                             
2018-04-02 00:00:00              0.25           0.00               17.25   
2018-04-02 00:30:00              0.24           0.00               17.01   
2018-04-02 01:00:00              0.24           0.00               16.77   
2018-04-02 01:30:00              0.26           0.00               16.52   
2018-04-02 02:00:00              0.28           0.00               16.23   
2018-04-02 02:30:00              0.32           0.00               15.92   
2018-04-02 03:00:00              0.37           0.00               15.55   
2018-04-02 03:30:00              0.41           0.00               15.14   
2018-04-02 04:00:00              0.43           0.00               14.71   
2018-04-02 04:30:00              0.43           0.00               14.28   
2018-04-02 05:00:00              0.41           0.00               13.87   
2018-04-02 05:30:00              0.37           0.00               13.50   
2018-04-02 06:00:00              0.22           0.02                0.00   
2018-04-02 06:30:00              0.13           0.10                0.00   
2018-04-02 07:00:00              0.07           0.26                0.19   
2018-04-02 07:30:00              0.04           0.48                0.64   
2018-04-02 08:00:00              0.02           0.72                1.33   
2018-04-02 08:30:00              0.01           0.95                2.27   
2018-04-02 09:00:00              0.01           1.16                3.42   
2018-04-02 09:30:00              0.01           1.33                4.75   
2018-04-02 10:00:00              0.01           1.47                6.21   
2018-04-02 10:30:00              0.01           1.57                7.78   
2018-04-02 11:00:00              0.01           1.63                9.40   
2018-04-02 11:30:00              0.01           1.65               11.04   
2018-04-02 12:00:00              0.01           1.62               12.66   
2018-04-02 12:30:00              0.01           1.55               14.19   
2018-04-02 13:00:00              0.01           1.40               15.59   
2018-04-02 13:30:00              0.01           1.26               16.84   
2018-04-02 14:00:00              0.01           1.15               17.99   
2018-04-02 14:30:00              0.01           1.02               19.00   
2018-04-02 15:00:00              0.01           0.85               19.84   
2018-04-02 15:30:00              0.01           0.64               20.47   
2018-04-02 16:00:00              0.02           0.41               20.86   
2018-04-02 16:30:00              0.04           0.20               21.02   
2018-04-02 17:00:00              0.10           0.05               20.97   
2018-04-02 17:30:00              0.21           0.00               20.76   
2018-04-02 18:00:00              0.29           0.00               20.47   
2018-04-02 18:30:00              0.32           0.00               20.16   
2018-04-02 19:00:00              0.32           0.00               19.84   
2018-04-02 19:30:00              0.33           0.00               19.51   
2018-04-02 20:00:00              0.32           0.00               19.19   
2018-04-02 20:30:00              0.33           0.00               18.86   
2018-04-02 21:00:00              0.32           0.00               18.53   
2018-04-02 21:30:00              0.32           0.00               18.21   
2018-04-02 22:00:00              0.31           0.00               17.90   
2018-04-02 22:30:00              0.30           0.00               17.61   
2018-04-02 23:00:00              0.28           0.00               17.33   
2018-04-02 23:30:00              0.26           0.00               17.06   

                     Import[MWh]  Throwaway[MWh]  
datetime                                          
2018-04-02 00:00:00         0.00               0  
2018-04-02 00:30:00         0.00               0  
2018-04-02 01:00:00         0.00               0  
2018-04-02 01:30:00         0.00               0  
2018-04-02 02:00:00         0.00               0  
2018-04-02 02:30:00         0.00               0  
2018-04-02 03:00:00         0.00               0  
2018-04-02 03:30:00         0.00               0  
2018-04-02 04:00:00         0.00               0  
2018-04-02 04:30:00         0.00               0  
2018-04-02 05:00:00         0.00               0  
2018-04-02 05:30:00         0.00               0  
2018-04-02 06:00:00         0.00               0  
2018-04-02 06:30:00        -0.03               0  
2018-04-02 07:00:00         0.00               0  
2018-04-02 07:30:00         0.00               0  
2018-04-02 08:00:00         0.00               0  
2018-04-02 08:30:00         0.00               0  
2018-04-02 09:00:00         0.00               0  
2018-04-02 09:30:00         0.00               0  
2018-04-02 10:00:00         0.00               0  
2018-04-02 10:30:00         0.00               0  
2018-04-02 11:00:00         0.00               0  
2018-04-02 11:30:00         0.00               0  
2018-04-02 12:00:00         0.00               0  
2018-04-02 12:30:00         0.00               0  
2018-04-02 13:00:00         0.00               0  
2018-04-02 13:30:00         0.00               0  
2018-04-02 14:00:00         0.00               0  
2018-04-02 14:30:00         0.00               0  
2018-04-02 15:00:00         0.00               0  
2018-04-02 15:30:00         0.00               0  
2018-04-02 16:00:00         0.00               0  
2018-04-02 16:30:00         0.00               0  
2018-04-02 17:00:00         0.00               0  
2018-04-02 17:30:00         0.00               0  
2018-04-02 18:00:00         0.00               0  
2018-04-02 18:30:00         0.00               0  
2018-04-02 19:00:00         0.00               0  
2018-04-02 19:30:00         0.00               0  
2018-04-02 20:00:00         0.00               0  
2018-04-02 20:30:00         0.00               0  
2018-04-02 21:00:00         0.00               0  
2018-04-02 21:30:00         0.00               0  
2018-04-02 22:00:00         0.00               0  
2018-04-02 22:30:00         0.00               0  
2018-04-02 23:00:00         0.00               0  
2018-04-02 23:30:00         0.00               0

Following is my version of the program and somehow works but I have two problems:

My program doesn't specify the time to discharge as a timestamp (discharge everything by 06:00:00 ) but rather used the nth row method.
The Throwaway electricity[MWh] values are not being recorded in the specified column.

for t in range (1,len(df)):
    print(t)
    # Battery charge[t] = Battery charge[t-1]-consumption[t]+solar generation[t]
    df.loc[df.index[t], 'BatteryCharge[MWh]'] = df.loc[df.index[t-1], 'BatteryCharge[MWh]']-df.loc[df.index[t], 'Consumption[MWh]']+df.loc[df.index[t], 'Solar_Gene[MWh]']
    print(df.loc[df.index[t], 'BatteryCharge[MWh]'] )

    if t%48==12: # for specifying the discharge time(06:00:00). 
        if  df.loc[df.index[t], 'BatteryCharge[MWh]'] > 0:
            df.loc[df.index[t], 'BatteryCharge[MWh]'] == df.loc[df.index[t], 'Throwaway[MWh]']
            df.loc[df.index[t], 'BatteryCharge[MWh]'] =  0
            print("The amount of a throwaway electricity is " + f"{df.loc[df.index[t], 'Throwaway[MWh]']}")

        # if SOC < 0, consumption amount will be imported from the grid
    if  df.loc[df.index[t], 'BatteryCharge[MWh]'] < 0:
        df.loc[df.index[t],'Import[MWh]'] = df.loc[df.index[t], 'Consumption[MWh]']
        df.loc[df.index[t], 'BatteryCharge[MWh]'] = 0

How to specify the time of discharge by actually using the datetime index instead of using modulo (%) method? Utilizing the datetime index is essential for me since the battery discharge needs to be tried in several scenarios such as daily, weekly, and monthly etc.
Why is the Throwaway [MWh] value not being recorded?

what is Import[MWh] and how its behave? First df is input data and second df is you output? — Zaraki Kenpachi
@ZarakiKenpachi, both input and output are shown in one df (top and bottom should be considered one dataframe). Consumption[MWh] and Solar_Gene[MWh] are the input. BatteryCharge, Import, and Throwaway columns are the output. Import[MWh] happens when the BatteryCharge goes below zero and there is no solar generation. — Amarbold Altangerel
then Import[MWh] is calculated when BatteryCharge[MWh] < Consumption[MWh] and is equal to its difference? Throwaway[MWh] is equal to BatteryCharge[MWh] on 6h? — Zaraki Kenpachi
@ZarakiKenpachi yes, Throwaway[MWh] should be equal to BatteryCharge[MWh] and BatteryCharge[MWh] should then reset after that. — Amarbold Altangerel

Zaraki Kenpachi Zaraki Kenpachi · Accepted Answer · 2020-02-26T13:30:55

For the data specified like below:

                     Consumption[MWh]  Solar_Gene[MWh]  BatteryCharge[MWh]
datetime                                                                  
2018-04-02 00:00:00              0.25             0.00               17.25
2018-04-02 00:30:00              0.24             0.00               17.01

where datetime is at index use:

df['Import[MWh]'] = df.apply(lambda x : 0 if x['BatteryCharge[MWh]'] >= x['Consumption[MWh]'] else x['BatteryCharge[MWh]'] - x['Consumption[MWh]'], axis=1)
df['minutes'] = df.index.hour*60 + df.index.minute
df['Throwaway[MWh]'] = df.apply(lambda x : 0 if x['minutes'] != 330 else x['BatteryCharge[MWh]'], axis=1)

Output:

                     Consumption[MWh]  Solar_Gene[MWh]  BatteryCharge[MWh]  Import[MWh]  minutes  Throwaway[MWh]
datetime                                                                                                        
2018-04-02 00:00:00              0.25             0.00               17.25         0.00        0             0.0
2018-04-02 00:30:00              0.24             0.00               17.01         0.00       30             0.0
2018-04-02 01:00:00              0.24             0.00               16.77         0.00       60             0.0
2018-04-02 01:30:00              0.26             0.00               16.52         0.00       90             0.0
2018-04-02 02:00:00              0.28             0.00               16.23         0.00      120             0.0
2018-04-02 02:30:00              0.32             0.00               15.92         0.00      150             0.0
2018-04-02 03:00:00              0.37             0.00               15.55         0.00      180             0.0
2018-04-02 03:30:00              0.41             0.00               15.14         0.00      210             0.0
2018-04-02 04:00:00              0.43             0.00               14.71         0.00      240             0.0
2018-04-02 04:30:00              0.43             0.00               14.28         0.00      270             0.0
2018-04-02 05:00:00              0.41             0.00               13.87         0.00      300             0.0
2018-04-02 05:30:00              0.37             0.00               13.50         0.00      330            13.5
2018-04-02 06:00:00              0.22             0.02                0.00        -0.22      360             0.0
2018-04-02 06:30:00              0.13             0.10                0.00        -0.13      390             0.0
2018-04-02 07:00:00              0.07             0.26                0.19         0.00      420             0.0
2018-04-02 07:30:00              0.04             0.48                0.64         0.00      450             0.0

How to reset the row value based on the timestamp?

1 Answers