[Python] Pandas error: IndexError string index out of range

I have been trying to append a Dataframe to an existing excel sheet using the below code snippet but I have been tucked with the below error.

my_data

Date Time Year Month Track # Dist Betfair_Grade Runners Win Trap
25/09/2021 09:27 2021 Sep Bulli R2 472m Mdn 8.00 2.00
25/09/2021 09:47 2021 Sep Bulli R3 400m Gr5 8.00 9.00
25/09/2021 10:53 2021 Sep Bulli R6 472m Gr5 7.00 5.00
25/09/2021 13:12 2021 Sep Canning R4 520m FFA 5.00 4.00
25/09/2021 11:59 2021 Sep Bulli R9 400m M1/2/3 6.00 8.00
25/09/2021 12:25 2021 Sep Bulli R10 472m Rest 6.00 1.00
25/09/2021 01:32 2021 Sep Richmond R8 324m Gr3 8.00 2.00
25/09/2021 01:49 2021 Sep Richmond R9 324m Gr5 8.00 1.00
25/09/2021 02:07 2021 Sep Richmond R10 324m Gr5 8.00 2.00
25/09/2021 02:02 2021 Sep Sheppar R8 450m Gr5 8.00 3.00


my_function

import os
import openpyxl
import pandas as pd
from openpyxl.utils.dataframe import dataframe_to_rows

file = r"file_test.xlsx"

# create excel file
if os.path.isfile(file): # if file already exists append to existing file
workbook = openpyxl.load_workbook(file) # load workbook if already exists
sheet = workbook['my_sheet_name'] # declare the active sheet

# append the dataframe results to the current excel file
for row in dataframe_to_rows(final_output_df, header = False, index = False):
sheet.append(row)
workbook.save(file) # save workbook
workbook.close() # close workbook
else: # create the excel file if doesn't already exist
with pd.ExcelWriter(path = file, engine = 'openpyxl') as writer:
final_output_df.to_excel(writer, index = False, sheet_name = 'my_sheet_name')


I tried to experiment with the above code on a new DataFrame, I don't get any errors. I can't tell what exactly is wrong.

df = pd.DataFrame({'A': [1,2,3,4,5], 'B': [2,3,4,5,6]})


I also checked the index for both columns.

df.index, RangeIndex(start=0, stop=5, step=1)
my_data.index, RangeIndex(start=0, stop=94, step=1)


Error traceback

---------------------------------------------------------------------------
IndexError Traceback (most recent call last)
<ipython-input-87-a6b187072154> in <module>
13
14 # append the dataframe results to the current excel file
---> 15 for row in dataframe_to_rows(final_output_df, header = False, index = False):
16 sheet.append(row)
17 workbook.save(file) # save workbook

~\Anaconda3\lib\site-packages\openpyxl\utils\dataframe.py in dataframe_to_rows(df, index, header)
52
53 for idx, v in enumerate(df.index):
---> 54 row = [data[j][idx] for j in range(ncols)]
55 if index:
56 row = [v] + row

~\Anaconda3\lib\site-packages\openpyxl\utils\dataframe.py in <listcomp>(.0)
52
53 for idx, v in enumerate(df.index):
---> 54 row = [data[j][idx] for j in range(ncols)]
55 if index:
56 row = [v] + row

IndexError: string index out of range

Continue reading...