Home > Blockchain >  Python DataFrame: How to find first empty row in Excel and output a Dataframe there using to_excel
Python DataFrame: How to find first empty row in Excel and output a Dataframe there using to_excel

Time:03-25

Lets say I have a single row Pandas Dataframe called df with the following structure:

A B C
Dog Sheep Lizard

No lets say I have an excel file called "Aged Data" with a sheet called Sheet1. On Sheet1 there is a table of data that looks like this:

A B C
Cat Zebra Lepard
Fish Bird Elephant

I would like to use the to_excel() function to find the file and then add the single row of data in df to the first blank row in the sheet in excel. The dataframe and the table in excel have the same column names and they are in the same order. The output should be a table in excel that looks like this:

A B C
Cat Zebra Lepard
Fish Bird Elephant
Dog Sheep Lizard

How could I add this functionality to the code below?

# Writing final table into Excel
destdir = os.path.join(r'\\filepath,'Aged Data.xlsx')

writer = pd.ExcelWriter(destdir, engine='xlsxwriter')
df.to_excel(writer, sheet_name = 'Sheet1')
writer.save()

CodePudding user response:

try this:

df # you already have it
df_excel = pd.read_excel('Aged Data.xlsx', sheet_name='Sheet1')

pd.concat([df, df_excel], axis=0).to_excel('output.xlsx')
  • Related