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

Question

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)

Shivam Gaur · Accepted Answer · 2018-02-15 10:29:57Z

3

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()

edited Feb 15, 2018 at 10:29

answered Feb 14, 2018 at 7:40

Shivam Gaur

1,06210 silver badges17 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Prajna Hegde Over a year ago

In sheet_frames = [pd.read_excel(x, sheetname=sheetname) for x in excel_names] line, sheetname should be sheet_name.. It worked for me.. Thank you..:)

Prajna Hegde Over a year ago

how do we convert the same to csv?

Shivam Gaur Over a year ago

I see, sheetname has been deprecated since pandas 0.21, should use sheet_name now. Going to edit the answer. pandas.pydata.org/pandas-docs/version/0.22/generated/…

jezrael · Accepted Answer · 2018-02-14 11:48:00Z

2

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()

edited Feb 14, 2018 at 11:48

answered Feb 14, 2018 at 7:49

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

5 Comments

Prajna Hegde Over a year ago

It is combining both the sheets but in second sheet it is giving lot of gap between one file data to another

Prajna Hegde Over a year ago

I guess there is still something wrong with x.sheet_names

jezrael Over a year ago

I test it in pandas 0.22.0 and it working nice. What is error?

Prajna Hegde Over a year ago

Yeah, It is working... Sorry for the inconvenience.. By the way how to check the pandas version?

jezrael Over a year ago

print (pd.show_versions()) is nice, because all versions.

Collectives™ on Stack Overflow

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

2 Answers 2

First combine the first and the second sheet separately

Use pd.ExcelWriter

3 Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

First combine the first and the second sheet separately

Use pd.ExcelWriter

3 Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related