[Python] How to merge multiple CSV/Excel files into a single file with Python inserting the...

Trying to merge >100 CSV files into a singular Excel file with the following conditions: Specific rows of the CSV files. (The data contained is from rows 1-250)

first column of the dataframe Excel file is the name of the CSV file.

1-100 CSVs. Data is contained in rows 1-250. Columns are mostly the same, some CSVs have additional column data.

Desired Output:

1 Excel sheet with all the CSV data starting in column 2 and column one being the name of the CSV file (e.g. data1.csv, data2.csv for the applicable rows).

Current output is the correct merge but column 1 is not populating.

# importing the required modules
import glob
import pandas as pd


df = pd.DataFrame()

# specifying the path to csv files
path = "C:/Users/Staylor/Documents/Python test"

# csv files in the path
file_list = glob.glob(path + "/*.csv")

# list of excel files we want to merge.
# pd.read_excel(file_path) reads the csv
# data into pandas dataframe.
excl_list = []

for file in file_list:
excl_list.append(pd.read_csv(file))
df['col1'] = file


# create a new dataframe to store the
# merged excel file.
excl_merged = pd.DataFrame()

for excl_file in excl_list:

# appends the data into the excl_merged
# dataframe.
excl_merged = excl_merged.append(
excl_file, ignore_index=True)

# exports the dataframe into excel file with
# specified name.
excl_merged.to_excel('total_python.xlsx', index=False)

Continue reading...