append pandas dataframe to existing excel file with start at specific cell

Question

If I have a dataframe

   0    1     2
0  0.1  0.2   0.3
1  0.2  0.3   0.4
2  0.3  0.4   0.5
3  0.4  0.5   0.6

and I want to append it to my existing excel file, I will then write code

wb = load_workbook('test1.xlsx')

ws = wb['Sheet1']

for index, row in newdf.iterrows():
    cell = 'D%d'  % (index + 2)
    ws[cell] = row[0]

wb.save('test1.xlsx')

The problem with that code is, I only get the first column, since its iterating over the index. So for example this is excel sheet.

How to make iteration until all the column finish with start at D2 cell ? I ve tried iteritems also it did not work.

expected result should be 
A B C D   E    F
1
2     0.1 0.2  0.3
3     0.2 0.3  0.4
4     0.3 0.4  0.5
5     0.4 0.5  0.6

Timeless · Accepted Answer · 2023-01-22 23:28:09Z

2

Here is a proposition based on your code :

from openpyxl import load_workbook
from openpyxl.utils import get_column_letter

wb = load_workbook("test1.xlsx")
ws = wb["Sheet1"]

for i, row in df.iterrows():
    for j, value in enumerate(row):
        r, c = i + 2, get_column_letter(j + 4)
        cell_coordinates = f"{c}{r}"
        ws[cell_coordinates] = value

wb.save("test1.xlsx")

There is also this answer from @Charlie Clark that you can adapt to your needs.

Output :

answered Jan 22, 2023 at 23:28

Timeless

38.3k6 gold badges33 silver badges54 bronze badges

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

Collectives™ on Stack Overflow

append pandas dataframe to existing excel file with start at specific cell

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