Combine excel files

Devlife 0
Viewed 38

Can someone help how to get output in excel readable format? I am getting output as dataframe but #data is embedded a string in row number 2 and 3

import pandas as pd
import os
input_path = 'C:/Users/Admin/Downloads/Test/'

output_path = 'C:/Users/Admin/Downloads/Test/'
[enter image description here][1]
excel_file_list = os.listdir(input_path)

df = pd.DataFrame()

for file in excel_file_list:
    if file.endswith('.xlsx'):
        df1 = pd.read_excel(input_path+file, sheet_name=None)
        df = df.append(df1, ignore_index=True)enter image description here
        
writer = pd.ExcelWriter('combined.xlsx', engine='xlsxwriter')
for sheet_name in df.keys():
    df[sheet_name].to_excel(writer, sheet_name=sheet_name, index=False)

writer.save()
export-to-excel pandas excel python
1 Answers

Your issue may be in using sheet_name=None. If any of the files have multiple sheets, a dictionary will be returned by pd.read_excel() with {'sheet_name':dataframe} format.

To .append() with this, you can try something like this, using python's Dictionary.items() method:

def combotime(dfinput):
    df1 = pd.DataFrame()
    for k, v in dfinput.items():
        df1 = df1.append(dfin[k])
    return df1

EDIT: If you mean to keep the sheets separate as implied by your writer loop, do not use a pd.DataFrame() object like your df to add the dictionary items. Instead, add to an existing dictionary:

sheets = {}
sheets = sheets.update(df1) #df1 is your read_excel dictionary
for sheet in sheets.keys():
    sheets[sheet].to_excel(writer, sheet_name=sheet, index=Fasle)
saliustripe0
Related