How to read multiple csv files with specific name from a folder and merge them?

I am trying to read multiple files from a folder with specific name (1.car.csv, 2.car.csv and so on) and trying to add a new label after each iteration at right most of the dataset and merge all the csv files into one csv file. As the ".car.csv" is constant, I think I can use a for loop with .format(index) function to run over the csv files. All of the csv files has got same attributes.

Kindly help me!

• glob is used to get all files in the folder that match the pattern *.csv
• pd.read_csv is used to read each file as a DataFrame
  • index_col=None you are telling Pandas to not use any of the columns as the index, and instead to create a default index for the DataFrame.
  • header=0 you are telling Pandas to use the first row of the CSV file as the header row.
• pd.concat is used to merge all the DataFrames into a single DataFrame merged_df
  • axis=0 means that the concatenation should happen along the rows (vertically)
  • ignore_index=True the concatenation is performed such that the original indices of the individual DataFrames are discarded, and a new default index is created for the resulting DataFrame.

import glob
import pandas as pd

path = r'<path to folder containing csv files>'
all_files = glob.glob(path + "/*.csv")

lst = []

for filename in all_files:
    df = pd.read_csv(filename, index_col=None, header=0)
    lst.append(df)

merged_df = pd.concat(lst, axis=0, ignore_index=True)

This can be easily done with a CSV tool like miller:

mlr --csv cat --filename bla1.csv *.car.csv

This will concatenate the files (without repeating the header) and prepend the filename as the first column.

Using pathlib and pandas you can use .assign() to enter the new column and finally .concat() to concatenate all the files into one.

from pathlib import Path

import pandas as pd


input_path = Path("path/to/car/files/").glob("*car.csv")
output_path = "path/to/output"

pd.concat(
    (pd.read_csv(x).assign(new_label="new data") for x in input_path), ignore_index=True
).to_csv(f"{output_path}/final.csv", index=False)