CODEWITHSUNDEEP
pythonexcelsaveopenpyxlxlrd
vokiatik
vokiatik

Reputation: 13

how to append dataframes to different excel sheets in for loop

I have a problem that looks easy to solve at first, but now I'm confused because I still can't solve it.

I have this code that returns few different dataframes within for loop, I need to append different dfs on different excel sheets.

To make it clear:

Note: I don't want to use something like with open each time I need to save dataframe because there is a lot of them if it can be done otherwise.

What I already did:

Using openpyexcel:

#with pd.ExcelWriter("test.xlsx", engine="openpyxl", mode="a") as writer_test:
    for i in range(3):
        with pd.ExcelWriter("test.xlsx", engine="openpyxl", mode="a") as writer_test:
            a.to_excel(writer_test, sheet_name="name")
            b.to_excel(writer_test, sheet_name="b")
            writer_test.save()

Both ways it returns an Excel file with sheets name, b, name1, b1, name2, b2 with data separated.

Without specifying an engine:

#with pd.ExcelWriter("test.xlsx", mode="A") as writer_test:

for i in range(3): with pd.ExcelWriter("test.xlsx", mode="A") as writer_test: a.to_excel(writer_test, sheet_name="name") b.to_excel(writer_test, sheet_name="b") writer_test.save()

That just overwrites the data and returns only last iteration.

Another attempt with openpyexcel:

wb = load_workbook('test.xlsx', read_only=False)

for i in range(3):
    ws = ["mysheet1", "mysheet2"]
    for ws_name in ws:
        if ws_name in wb.get_sheet_names():
            ws_mysheet1 = wb["mysheet1"]
        else:
            ws_mysheet1 = wb.create_sheet() # insert at first position
            ws_mysheet1.title = ws_name
    ws_mysheet1 = wb["mysheet1"]
    ws_mysheet2 = wb["mysheet2"]
    ws_mysheet1.append(a)
    ws_mysheet2.append(b)

That does not allow me to save dataframe and returns an error

TypeError: Value must be a list, tuple, range or generator, or a dict. Supplied value is <class 'pandas.core.frame.DataFrame'>

And I also tried this:

for i in range(3):  
    #with open(writer_test, 'a') as f:
        #a.to_excel(writer_test, 'a')
        #b.to_excel(writer_test, 'b')
    if os.path.exists('test.xlsx'):
        append_write = 'a' # append if already exists
    else:
        append_write = 'w' # make a new file if not

    highscore = open('test.xlsx',append_write)
    highscore.write(a + '\n', 'a')
    highscore.write(a + '\n', 'b')
    highscore.close()

That results in another error:

TypeError: write() takes exactly one argument (2 given)

I tried a few other things but they don't work either. Would appreciate any help.

Upvotes: 0

Views: 1670

Answers (1)

Radu Pacurar
Radu Pacurar

Reputation: 61

import pandas as pd

df = pd.util.testing.makeDataFrame()
df = df.head()

file = 'output.xlsx'
sheet_name = 'test'

for i in range(3):
    if i == 0:
        df.to_excel(file, sheet_name=sheet_name, index=False)
    else:
        read_data = pd.read_excel(file,sheet_name=sheet_name)
        merge_data = pd.concat([df, read_data])
        merge_data.to_excel(file, sheet_name=sheet_name, index=False)

Upvotes: 1

Related Questions