Adding file name in a Column while merging multible csv files to pandas- Python

Question

I have multiple csv files in the same folder with all the same data columns,

20100104 080100;5369;5378.5;5365;5378;2368
20100104 080200;5378;5385;5377;5384.5;652
20100104 080300;5384.5;5391.5;5383;5390;457
20100104 080400;5390.5;5391;5387;5389.5;392

I want to merge the csv files into pandas and add a column with the file name to each line so I can track where it came from later. There seems to be similar threads but I haven't been able to adapt any of the solutions. This is what I have so far. The merge data into one data frame works but I'm stuck on the adding file name column,

import os
import glob
import pandas as pd


path = r'/filepath/'                    
all_files = glob.glob(os.path.join(path, "*.csv")) 
names = [os.path.basename(x) for x in glob.glob(path+'\*.csv')] 

list_ = []
for file_ in all_files:
    list_.append(pd.read_csv(file_,sep=';', parse_dates=[0], infer_datetime_format=True,header=None ))  

df = pd.concat(list_)

Jeremy Farrell · Accepted Answer · 2017-03-27 13:58:24Z

4

Instead of using a list just use DataFrame's append.

df = pd.DataFrame()
for file_ in all_files:
    file_df = pd.read_csv(file_,sep=';', parse_dates=[0], infer_datetime_format=True,header=None )
    file_df['file_name'] = file_
    df = df.append(file_df)

edited Mar 27, 2017 at 13:58

answered Mar 27, 2017 at 12:22

Jeremy Farrell

1,49115 silver badges16 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Trembles Over a year ago

Thanks for that. That was what I was looking for. Perfect!

Jeremy Farrell Over a year ago

I am glad my answer helped, please accept it as it solved your problem. If you thought it was a good answer also upvote it.

Trembles Over a year ago

Yeah, i did upvote it. But It's my first post here and apparently my votes don't count yet....

Jeremy Farrell Over a year ago

Right I should have realized that, here is a list of privileges did reference: stackoverflow.com/help/privileges.

Collectives™ on Stack Overflow

Adding file name in a Column while merging multible csv files to pandas- Python

1 Answer 1

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related