CODEWITHSUNDEEP
pythonpandas
Prajna Hegde
Prajna Hegde

Reputation: 740

How to combine multiple excel files having multiple equal number of sheets in each excel files

I am able to combine multiple excel files having one sheet currently. I want to combine multiple sheets having two different sheets in each excel file with giving name to each sheets How can I achieve this?

Here below is my current code for combining single sheet in multiple excel files without giving sheet name to Combined excel file

import pandas as pd

# filenames
excel_names = ["xlsx1.xlsx", "xlsx2.xlsx", "xlsx3.xlsx"]

# read them in
excels = [pd.ExcelFile(name) for name in excel_names]

# turn them into dataframes
frames = [x.parse(x.sheet_names[0], header=None,index_col=None) for x in excels]

    # delete the first row for all frames except the first
    # i.e. remove the header row -- assumes it's the first
    frames[1:] = [df[1:] for df in frames[1:]]

    # concatenate them..
    combined = pd.concat(frames)

    # write it out
    combined.to_excel("c.xlsx", header=False, index=False)

Upvotes: 4

Views: 2347

Answers (2)

Shivam Gaur
Shivam Gaur

Reputation: 1062

First combine the first and the second sheet separately

import pandas as pd

# filenames
excel_names = ["xlsx1.xlsx", "xlsx2.xlsx", "xlsx3.xlsx"]

def combine_excel_to_dfs(excel_names, sheet_name):
    sheet_frames = [pd.read_excel(x, sheet_name=sheet_name) for x in excel_names]
    combined_df = pd.concat(sheet_frames).reset_index(drop=True)

    return combined_df

df_first = combine_excel_to_dfs(excel_names, 0)
df_second = combine_excel_to_dfs(excel_names, 1)

Use pd.ExcelWriter

And write these sheets to the same excel file: 

# Create a Pandas Excel writer using XlsxWriter as the engine.
writer = pd.ExcelWriter('two_sheets_combined.xlsx', engine='xlsxwriter')

# Write each dataframe to a different worksheet.
df_first.to_excel(writer, sheet_name='Sheet1')
df_second.to_excel(writer, sheet_name='Sheet2')

# Close the Pandas Excel writer and output the Excel file.
writer.save()

Upvotes: 3

jezrael
jezrael

Reputation: 862406

You can use:

#number of sheets
N = 2
#get all sheets to nested lists
frames = [[x.parse(y, index_col=None) for y in x.sheet_names] for x in excels]
#print (frames)

#combine firt dataframe from first list with first df with second list...
combined = [pd.concat([x[i] for x in frames], ignore_index=True) for i in range(N)]
#print (combined)

#write to file
writer = pd.ExcelWriter('c.xlsx', engine='xlsxwriter')
for i, x in enumerate(combined):
    x.to_excel(writer, sheet_name='Sheet{}'.format(i + 1))
writer.save()

Upvotes: 2

Related Questions