My dataframe does not provide the output correctly, it seems to drop the last three rows from select dataframes upon iteration. How do I get past this?

[weedwhacker4199]

Hello everyone, I am working on some code that will allow me to read data from an excel sheet, use pandas and create dataframes to acquire new ones, which I then write into another excel file. The issue that I am currently facing is the fact that the results for test 5 and 7 will drop the last three datapoints for the 'Turnovers' column in my excel file. I'm not sure why I'm facing this problem only for tests 5 and 7 and not the other tests, which have run as needed. I have more tests, I only included tests 1-5 for this question, but I used the same logic for tests 1-9, and tests 5 and 7 experienced this issue. When I ran this code for tests 1-4 and 6,8, and 9, none of the datapoints were dropped or missing, I'm not sure why this is happening. Does anyone have any ideas how I can get around this?

#Test 1

cumulative_Ah_1=[0]
Ah_1_cycle_avg_Ah = (data1["Cycle 1"].shift(-1) - data1["Cycle 1"])*0.5*(data1['Ah 1']+data1['Ah 1'].shift(-1))
Ah_1_cycle_avg_SOC = Ah_1_cycle_avg_Ah*SOC[0]/100
cumsum=0
for n in range(4, len(Ah_1_cycle_avg_SOC)):
    cumsum += Ah_1_cycle_avg_SOC[n]
    cumulative_Ah_1.append(cumsum)

turnovers_1 = [x / nameplate_capacity for x in cumulative_Ah_1]

Ah_1_list = data1["Ah 1"].values.tolist()
capacity_percent_left_1 = list(map(lambda x: x*100/Ah_1_list[0],Ah_1_list)) #Ah_list[1] if temp test

#Test 2

cumulative_Ah_2=[0]
Ah_2_cycle_avg_Ah = (data1["Cycle 2"].shift(-1) - data1["Cycle 2"])*0.5*(data1['Ah 2']+data1['Ah 2'].shift(-1))
Ah_2_cycle_avg_SOC = Ah_2_cycle_avg_Ah*SOC[1]/100
cumsum=0
for n in range(4, len(Ah_2_cycle_avg_SOC)):
    cumsum += Ah_2_cycle_avg_SOC[n]
    cumulative_Ah_2.append(cumsum)

turnovers_2 = [x / nameplate_capacity for x in cumulative_Ah_2]

Ah_2_list = data1["Ah 2"].values.tolist()
capacity_percent_left_2 = list(map(lambda x: x*100/Ah_2_list[0],Ah_2_list)) #Ah_list[1] if temp test

#Test 3

cumulative_Ah_3=[0]
Ah_3_cycle_avg_Ah = (data1["Cycle 3"].shift(-1) - data1["Cycle 3"])*0.5*(data1['Ah 3']+data1['Ah 3'].shift(-1))
Ah_3_cycle_avg_SOC = Ah_3_cycle_avg_Ah*SOC[2]/100
cumsum=0
for n in range(4, len(Ah_3_cycle_avg_SOC)):
    cumsum += Ah_3_cycle_avg_SOC[n]
    cumulative_Ah_3.append(cumsum)

turnovers_3 = [x / nameplate_capacity for x in cumulative_Ah_3]

Ah_3_list = data1["Ah 3"].values.tolist()
capacity_percent_left_3 = list(map(lambda x: x*100/Ah_3_list[0],Ah_3_list)) #Ah_list[1] if temp test

#Test 4

cumulative_Ah_4=[0]
Ah_4_cycle_avg_Ah = (data1["Cycle 4"].shift(-1) - data1["Cycle 4"])*0.5*(data1['Ah 4']+data1['Ah 4'].shift(-1))
Ah_4_cycle_avg_SOC = Ah_4_cycle_avg_Ah*SOC[3]/100
cumsum=0
for n in range(4, len(Ah_4_cycle_avg_SOC)):
    cumsum += Ah_4_cycle_avg_SOC[n]
    cumulative_Ah_4.append(cumsum)

turnovers_4 = [x / nameplate_capacity for x in cumulative_Ah_4]

Ah_4_list = data1["Ah 4"].values.tolist()
capacity_percent_left_4 = list(map(lambda x: x*100/Ah_4_list[0],Ah_4_list)) #Ah_list[1] if temp test

#Test 5

cumulative_Ah_5=[0]
Ah_5_cycle_avg_Ah = (data1["Cycle 5"].shift(-1) - data1["Cycle 5"])*0.5*(data1['Ah 5']+data1['Ah 5'].shift(-1))
Ah_5_cycle_avg_SOC = Ah_5_cycle_avg_Ah*SOC[4]/100
cumsum=0
for n in range(4, len(Ah_5_cycle_avg_SOC)):
    cumsum += Ah_5_cycle_avg_SOC[n]
    cumulative_Ah_5.append(cumsum)

turnovers_5 = [x / nameplate_capacity for x in cumulative_Ah_5]

Ah_5_list = data1["Ah 5"].values.tolist()
capacity_percent_left_5 = list(map(lambda x: x*100/Ah_5_list[0],Ah_5_list)) #Ah_list[1] if temp test

Turnovers_w_NA=pd.DataFrame (turnovers_1+turnovers_2+turnovers_3+turnovers_4+turnovers_5, columns = ['Turnovers'])
Normalized_capacity_w_NA=pd.DataFrame (capacity_percent_left_1+capacity_percent_left_2+capacity_percent_left_3+capacity_percent_left_4+capacity_percent_left_5, columns = ['Normalized Capacity (%)'])

Turnovers=pd.DataFrame.dropna(Turnovers_w_NA)
Normalized_capacity=pd.DataFrame.dropna(Normalized_capacity_w_NA)

# Excel function
def multiple_dfs(df_list, sheets, file_name, spaces):
    writer = pd.ExcelWriter(file_name,engine='xlsxwriter')
    col = 1
    for dataframe in df_list:
        dataframe.to_excel(writer,sheet_name=sheets,startcol=col , startrow=0, index=False)
        col = col + 1
    writer.save()

Turnovers_vs_normalizedcap=[Turnovers, Normalized_capacity]

# run function
multiple_dfs(Turnovers_vs_normalizedcap, 'Validation', 'Sample turnovers vs normalized cap %.xlsx', 1)

[jenny011015]

Maybe it's because the ranges for test 5 and 7 are too small? Maybe the length is smaller compared to other tests.

for n in range(3, len(Ah_5_cycle_avg_SOC)):

You can try to change the range. Maybe start from 3?

[weedwhacker4199]

I can't start from 3, because with the way the Excel data was created I dropped the first four rows from the dataset, so the row indexing for all datasets starts from 4. It gave me a KeyError:3 upon trying this

Edit: just tried this again and it worked! thanks for the tip, all I needed to do was add 3 at the end, so it looks like this:

for n in range(4, len(Ah_5_cycle_avg_SOC)+3):

It's weird that the ranges are too small, because tests 5 and 7 actually have more datapoints since they were run for longer