How to merge multiple Excel files with Python code

This article mainly introduces "how to merge multiple Excel files with Python code". In daily operation, I believe many people have doubts about how to merge multiple Excel files with Python code. Xiaobian consulted all kinds of materials and sorted out simple and easy-to-use methods of operation. I hope it will be helpful to answer the doubts of "how to merge multiple Excel files with Python code". Next, please follow the editor to study!

Here are the contents of three Excel files:

The following is the generated summary table:

Step 1: install related packages and import

Only the OS module that comes with Python and the pandas package are used. If there is no pandas, install it:

Pip install pandas

Import after installation is complete:

Import osimport pandas as pd

Step 2: find all Excel files and read the contents

Excels = [pd.read_excel (fname) for fname in os.listdir ('. /') if 'xls' in fname]

Because Excel files have two suffixes: xls and xlsx, both contain xls, so it is determined whether the file is an Excel file by whether there is a xls in the file name.

Another thing is to pay attention to the file path. My code is in the same directory as the Excel file, so it is'. /'

Step 3: merge all Excel file contents

Df = pd.concat (excels)

This step should be the core step, thanks to the concat method in pandas, you can merge by row directly (the number of columns remains the same, the number of rows changes)

Step 4: generate the final summary file

Df.to_excel ('Summary .xlsx', index=False)

If index is True, there is an extra index value at the front of each row of data (the index value is the index of the row in the original Excel, not in the summary table)

At this point, the study on "how to merge multiple Excel files with Python code" is over. I hope to be able to solve your doubts. The collocation of theory and practice can better help you learn, go and try it! If you want to continue to learn more related knowledge, please continue to follow the website, the editor will continue to work hard to bring you more practical articles!