Python Pandas - Combine CSVs and add filename

Question

I'm trying to combine CSV files in a folder to analyze them. Also, I want to append the filename of each as a column so I can figure out which data came from which file. I've looked at the similar questions and none of them have worked for me.

Here's the code I'm using. There are 24 CSV files in this folder and since combining CSV files later would be easy using cat so even a method to tell me how I could append the filename in each file would be perfect. Any help would be great.

import pandas as pd
import os
import glob
import csv
path=r'/home/videept/Downloads/A_DeviceMotion_data/A_DeviceMotion_data/dws_1/'
with open('output.csv', 'w', newline='') as f_output:
    csv_output = csv.writer(f_output)

    for filename in glob.glob(os.path.join(path,"*.csv")):
        with open(filename, newline='') as f_input:
            csv_input = csv.reader(f_input)

            for row in csv_input:
                row.insert(0, filename)
                csv_output.writerow(row)

When I'm doing this the cell goes on an infinite loop and no new file is even created. I'm not sure how I can see the progress of what's going on so any idea on that would also be great. Thanks :)

Answer 1

I would do it this way (provided your are using Python 3.4+):

import pandas as pd
from pathlib import Path

source_files = sorted(Path('path_to_source_directory').glob('*.csv'))

dataframes = []
for file in source_files:
    df = pd.read_csv(file) # additional arguments up to your needs
    df['source'] = file.name
    dataframes.append(df)

df_all = pd.concat(dataframes)

This way, every row has a column represents its source file for easy filtering and analysis.