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

Question

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()

I have not checked it, but sounds like it should work stackoverflow.com/questions/38074678/… — NoobVB
– NoobVB, Commented Mar 24, 2022 at 21:08

Étienne Célèry · Accepted Answer · 2022-03-24 20:37:19Z

2

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')

answered Mar 24, 2022 at 20:37

Étienne Célèry

1741 silver badge7 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

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

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related