Multiple DataFrame To Excel

How can I export multiple dataframe to an one Excell sheet. I have 2 dataframes. Sometimes it can be more than 2 dataframes. cars = {'name': ['Audi','VW'], 'modell': ['A4','Gol

Solution 1:

Your question is not about writing to excel (this you know how to do) is about concatenating dataframes, you can use pandas.concat for that end.

In this example I create two sheets one with the datasets concatenated vertically, and the other with the datasets concatenated horizontally, as a bonus you know how to save multiple sheets to the same file.

cars = {'name': ['Audi','VW'],
    'modell': ["A4","Golf"]
    }

cars2 = {'name': ['BMW','MB'],
    'modell': ["3er","e-class"]
    }
## assuming that all your dataframes have the same set of columns
dataFrames = [pd.DataFrame(d) for d in [cars, cars2]]
with pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter') as writer:
  vertical = pd.concat(dataFrames, axis=0) # if the dataframes have the same columns
  horizontal = pd.concat(dataFrames, axis=1) # if the dataframes have the same indices
  
  vertical.to_excel(writer, sheet_name='Vertically')
  horizontal.to_excel(writer, sheet_name='Horizontally')

Solution 2:

Where do you want the dataframes to go?

This code will place them side by side on the same worksheet.

import pandas as pd

cars1 = {'name': ['Audi','VW'],
    'modell': ["A4","Golf"]
    }

cars2 = {'name': ['BMW','MB'],
    'modell': ["3er","e-class"]
    }
all_cars = [cars1, cars2]

writer = pd.ExcelWriter('pandas_simple.xlsx', engine='xlsxwriter')
startcol = 0

for car in all_cars:
  df = pd.DataFrame(car, columns= ['name', 'modell'])
  df.to_excel(writer, sheet_name='Sheet1', startcol=startcol, index=None)
  startcol += len(df.columns)
writer.save()