I have 10 csv files and want to save all the files as 10 worksheets of 1 xlsx file.

data1.csv,data2.csv,.......,data10.csv.

Attempt

import glob
import numpy as np
import pandas as pd

all_datasets = pd.DataFrame()
for x in glob.glob("*.csv"):
    df = pd.read_csv(x)

# I want to export the corresponding csv files as 10 worksheets of 1 xlsx 

#initialze the excel writer
writer = pd.ExcelWriter('all_datasets_combinedworksheets.xlsx', engine='xlsxwriter')


frames = {'sheetName_1': df1, 'sheetName_2': df2,
        'sheetName_3': df3,'sheetName_4': df4}

for sheet, frame in  frames.iteritems(): # .use .items for python 3.X
    frame.to_excel(writer, sheet_name = sheet)

#critical last step
writer.save()

I'm open to other approach, please share your code, thanks in advance

CodePudding user response：

1. You're overwriting the variable df in every iteration of your loop.
2. You don't have any variables called df1, df2 etc. but you're trying to use them to create your frames dictionary.
3. You never use all_datasets.

Try:

import os
frames = {f: pd.read_csv(f) for f in os.listdir(path) if f.endswith(".csv")}
writer = pd.ExcelWriter('all_datasets_combinedworksheets.xlsx', engine='xlsxwriter')

for sheet, frame in frames.items():
    frame.to_excel(writer,sheet_name=sheet)

writer.save()