Creating pandas DataFrame from multiple csv files with date index

Question

I have multiple csv files with measurement data, that I would need to merge into one Pandas DataFrame with the date/time as index. I have tried using pd.concat, however, this only adds the csv files together and does not 'sort' them correctly.

Example file 1:

    NAME;DATE;VALUE;UNIT
    SO.DA.TT.401.3;01-01-2015 00:00:00;14,9;°C
    SO.DA.TT.401.3;01-01-2015 00:10:00;14,9;°C

Example file 2:

    NAME;DATE;VALUE;UNIT
    SO.DA.TT.401.3;16-06-2014 20:50:00;51,9;°C
    SO.DA.TT.401.3;16-06-2014 21:00:00;51,8;°C

How can I import the files such that they are sorted both by index and name into one DataFrame??

jezrael · Accepted Answer · 2017-09-04 11:05:45Z

1

You need list comprehension with glob which return filenames, then use parameters index_col, parse_dates for set second column to DatetimeIndex and parameter decimal for convert VALUE column to numeric.

Last concat list of DataFrames and if necessary sort_index:

files = glob.glob('files/*.csv')
#second column convert to datetimeindex
dfs = [pd.read_csv(fp, sep=';',index_col=[1], parse_dates=[1], decimal=',') for fp in files]
df = pd.concat(dfs).sort_index()
print (df)

                               NAME  VALUE UNIT
DATE                                           
2014-06-16 20:50:00  SO.DA.TT.401.3   51.9   °C
2014-06-16 21:00:00  SO.DA.TT.401.3   51.8   °C
2015-01-01 00:00:00  SO.DA.TT.401.3   14.9   °C
2015-01-01 00:10:00  SO.DA.TT.401.3   14.9   °C

edited Sep 4, 2017 at 11:05

answered Sep 4, 2017 at 10:32

jezrael

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

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

2 Comments

Adam R. Jensen Over a year ago

What if I need the DataFrame to be in the form: Date SO.DA.TT.401.1 SO.DA.TT.401.2 .... 2014-06-16 51.9 60.8 .... i.e. each csv file is a column in the DataFrame? This would make data accessing much easier as I currenlty have to do df[df['NAME']=='SO.DA'] to access each sensor (I don't care abou the unit)

jezrael Over a year ago

Do you think df = pd.concat(dfs).sort_index().reset_index().set_index('NAME') ?

Collectives™ on Stack Overflow

Creating pandas DataFrame from multiple csv files with date index

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related