How to write CSV files into XLSX using Python Pandas?

Question

I have several .csv files and I want to write them into one .xlsx file as spreadsheets.

I've loaded these .csv files into Pandas.DataFrame using following code:

df1 = pandas.read_csv('my_file1.csv')
df2 = pandas.read_csv('my_file2.csv')
......
df5 = pandas.read_csv('my_file5.csv')

But I couldn't find any functions in Pandas that can write these DataFrames into one .xlsx file as separated spreadsheets.

Can anyone help me with this?

Solution

With recent enough pandas use DataFrame.to_excel() with an existing ExcelWriter object and pass sheet names:

from pandas.io.excel import ExcelWriter
import pandas

csv_files = ['my_file1.csv', 'my_file2.csv', ..., 'my_file5.csv']

with ExcelWriter('my_excel.xlsx') as ew:
    for csv_file in csv_files:
        pandas.read_csv(csv_file).to_excel(ew, sheet_name=csv_file)