Python_Consolidate_Excel_files.md

Template request | Bug report | Generate Data Product

Tags: #python #consolidate #files #productivity #snippet #operations #excel

Author: Florent Ravenel

The objective of this notebook is to consolidate multiple Excel files (.xlsx) into one.

Input

Import library

Import the necessary libraries: os and pandas

import os
import pandas as pd

Variables

# Output
excel_output = "concatenate.xlsx"

Model

Use a for loop to

• List all the files in the current directory with os.listdir().
• Filter files with the .endswith(‘.xlsx’) method.
• Make sure the files will be stored into a list called my_list and then combined with pd.concat()

Then

• Return a dataframe and name it df_concat.

files = os.listdir()
my_list = []
for file in files:
    if file.endswith(".xlsx"):
        df = pd.read_excel(file)
        my_list.append(df)

df_concat = pd.concat(my_list, axis=0)

Output

Export your dataframe to an Excel file.

df_concat.to_excel(excel_output, index=False)