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