I have a CSV file that records a battery's discharge amount and temperature every hour. The first 10 hour of each day (starting at 13:00:00) is discharge period, and the next 14 hours are charge period.
My goal is to calculate the sum Energy column for the first 10 hours of each day (EnergySum) and set the sum to 26 if it exceeds 26 (since it shows decrease from battery's full capacity). Afterwards, I create another variable based on this amount, that shows battery's current capacity level (Cap).
Then, I want to add a column (named as Charged) to df, set the first 10 rows for each day to zero, and for the next 14 hours, I want to read from Capacity, and add the capacity amount to the current capacity level. Charged column records the amount added to current capacity. There will be a row that exceeds the maximum capacity. For that row, I want to record the fraction required to reach full capacity. For the next rows, Charged value will be zero.
After this is done, a column named as Consumption will be made that does the same thing as Charged, but reads from Electricity column instead.
Here's a sample of my dataset:
data = {'Date/Time':
['04/09 13:00:00','04/09 14:00:00','04/09 15:00:00','04/09 16:00:00','04/09 17:00:00','04/09 18:00:00','04/09 19:00:00','04/09 20:00:00','04/09 21:00:00','04/09 22:00:00','04/09 23:00:00','04/09 24:00:00', '04/10 01:00:00','04/10 02:00:00','04/10 03:00:00','04/10 04:00:00','04/10 05:00:00','04/10 06:00:00','04/10 07:00:00','04/10 08:00:00','04/10 09:00:00','04/10 10:00:00','04/10 11:00:00','04/10 12:00:00'],
'Energy':
[1.129094859,1.678736662,1.936771353,1.815096405,1.449398946,0.785971775,0.234247622,0.003275094,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0,0],
'Temperature':
[25,26,26,26,26,25,24,24,23,22,22,21,21,20,19,19,19,19,19,20,20,20,21,21],
'Capacity':
[2.1,2,2,2,2,2.1,2.1,2.1,2.1,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2,2.2],
'Electricity':
[0.84,0.85,0.85,0.85,0.85,0.84,0.83,0.83,0.82,0.81,0.81,0.8,0.8,0.79,0.78,0.78,0.78,0.78,0.78,0.79,0.79,0.79,0.8,0.8]}
df = pd.DataFrame(data)
I managed to get the desired results for the first 24 hours using the code below. I used the help here (Method #2) for this.
EnergySum = df.Energy.iloc[0:10].sum() # Sum of battery discharge in the discharge period
if EnergySum > 26: # Stop recording when battery is fully depleted
EnergySum = 26
Cap = 26 - EnergySum # The battery's current capacity level
for row in range(len(df[0:24:])):
if row < 10 and row >= 0: # Battery won't be charged in discharge period, so
df.loc[row, 'Charged'] = 0 # the amounts for that period will be set to zero
else:
if 26 - Cap > df.loc[row, 'Capacity']: # For when maximum capacity is not reached
df.loc[row, 'Charged'] = df.loc[row, 'Capacity']
Cap = Cap + df.Charged[row]
print('Cap After:', Cap)
elif 26 - Cap < df.loc[row, 'Capacity']: # For the row that exceeds maximum capacity
print('Cap Before:', Cap)
Residual = Cap - 26
df.loc[row, 'Charged'] = - Residual
Cap = 26
else: # For rows after when maximum capacity is reached
df.loc[row, 'Charged'] = 0
df.loc[row, 'Consumption'] = (df.loc[row, 'Charged']/df.loc[row, 'Capacity'])*df.loc[row, 'Electricity'] # Adding Consumption column
The desired output looks like this:
Date/Time Energy Temperature Capacity Electricity Charged Consumption
0 04/09 13:00:00 1.129095 25 2.1 0.84 0.000000 0.000000
1 04/09 14:00:00 1.678737 26 2.0 0.85 0.000000 0.000000
2 04/09 15:00:00 1.936771 26 2.0 0.85 0.000000 0.000000
3 04/09 16:00:00 1.815096 26 2.0 0.85 0.000000 0.000000
4 04/09 17:00:00 1.449399 26 2.0 0.85 0.000000 0.000000
5 04/09 18:00:00 0.785972 25 2.1 0.84 0.000000 0.000000
6 04/09 19:00:00 0.234248 24 2.1 0.83 0.000000 0.000000
7 04/09 20:00:00 0.003275 24 2.1 0.83 0.000000 0.000000
8 04/09 21:00:00 0.000000 23 2.1 0.82 0.000000 0.000000
9 04/09 22:00:00 0.000000 22 2.2 0.81 0.000000 0.000000
10 04/09 23:00:00 0.000000 22 2.2 0.81 2.200000 0.810000
11 04/09 24:00:00 0.000000 21 2.2 0.80 2.200000 0.800000
12 04/10 01:00:00 0.000000 21 2.2 0.80 2.200000 0.800000
13 04/10 02:00:00 0.000000 20 2.2 0.79 2.200000 0.790000
14 04/10 03:00:00 0.000000 19 2.2 0.78 0.232593 0.082465
15 04/10 04:00:00 0.000000 19 2.2 0.78 0.000000 0.000000
16 04/10 05:00:00 0.000000 19 2.2 0.78 0.000000 0.000000
17 04/10 06:00:00 0.000000 19 2.2 0.78 0.000000 0.000000
18 04/10 07:00:00 0.000000 19 2.2 0.78 0.000000 0.000000
19 04/10 08:00:00 0.000000 20 2.2 0.79 0.000000 0.000000
20 04/10 09:00:00 0.000000 20 2.2 0.79 0.000000 0.000000
21 04/10 10:00:00 0.000000 20 2.2 0.79 0.000000 0.000000
22 04/10 11:00:00 0.000000 21 2.2 0.80 0.000000 0.000000
23 04/10 12:00:00 0.000000 21 2.2 0.80 0.000000 0.000000
But I'm facing difficulties applying this code to the entire DataFrame. I used method mentioned here for splitting my rows into chunks of 24 and came up with this:
def chunk(seq, size):
return (seq[pos:pos + size] for pos in range(0, len(seq), size))
for df_chunk in chunk(df, 24):
EnergySum = df.Energy.iloc[0:10].sum()
if EnergySum > 26:
EnergySum = 26
Cap = 26 - EnergySum
for row in range(len(df_chunk)):
if row < 10 and row >= 0:
df.loc[row, 'Charged'] = 0
else:
if 26 - Cap > df.loc[row, 'Capacity']:
df.loc[row, 'Charged'] = df.loc[row, 'Capacity']
Cap = Cap + df.Charged[row]
elif 26 - Cap < df.loc[row, 'Capacity']:
Residual = Cap - 26
df.loc[row, 'Charged'] = - Residual
Cap = 26
else:
df.loc[row, 'Charged'] = 0
df.loc[row, 'Consumption'] = (df.loc[row, 'Charged']/df.loc[row, 'Capacity'])*df.loc[row, 'Electricity']
This code does not give me any errors, but when I check the results, I see that the results are all wrong. I'm assuming this is because variables EnergySum and Cap are defined inside the loop, but I don't know how to fix this issue.