Create date index while importing multiple csv from file names

Question

I have 250 csv files in a folder, i used the following code to import them to a single dataframe:

files = "~/*.csv"
df = pd.concat([pd.read_csv(f, dtype='str') for f in glob.glob(files)], ignore_index=True)

My problem is i dont have date info inside any of the files, the date is mentioned in the filename like "LSH_190207" which is 7-Feb-2019. Is there a way i can include this info in the dataframe while importing the files, preferably as index. Or at least create a new column that would contain the file names, so i can later split & format it into date column.

anky · Accepted Answer · 2019-02-10 06:32:04Z

3

Yes you can ,

Assuming the file list is

files = glob.glob('*.csv')
#['file1_LSH_190207_something.csv', 'file2_LSH_190208_something.csv']
#[f.split("_")[2] for f in files] gives ['190207', '190208']

This will create a date column with the value of date as string:

df= pd.concat([pd.read_csv(f, dtype='str').assign(date= f.split("_")[2]) for f in files],\
                                                                  ignore_index=True)

Sample Output:

   A  B  C    date
0  1  2  3  190207
1  4  5  6  190207
2  5  6  8  190208
3  9  1  3  190208

Post this you could do the below to convert the date in your own format:

pd.to_datetime(df['date']).dt.strftime('%d-%b-%Y')

0    07-Feb-2019
1    08-Feb-2019
2    09-Feb-2019

edited Feb 10, 2019 at 6:32

answered Feb 10, 2019 at 5:57

anky

75.3k11 gold badges46 silver badges76 bronze badges

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

2 Comments

Prasanjit Over a year ago

The import worked, but my file names ended with .CSV after the date info, so I used df.date = df.date.str.replace('.csv', '') to just keep the numbers. The date conversion didn't work, I used df.date = pd.to_datetime(df.date, format='%y%m%d')

anky Over a year ago

show me a sample date please, if 190207 just use pd.to_datetime()

jezrael · Accepted Answer · 2019-02-10 06:04:29Z

1

Create Datetimeindex in lins comprehension and pass it to parameter keys in concat, only necessary removing second level of MultiIndex by reset_index:

idx = pd.to_datetime([f.split("_")[2] for f in files])
print (idx)

df = pd.concat([pd.read_csv(f, dtype='str') for f in files], 
                keys = idx).reset_index(level=1, drop=True)

answered Feb 10, 2019 at 6:04

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Collectives™ on Stack Overflow

Create date index while importing multiple csv from file names

2 Answers 2

2 Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related