Set up MultiIndex DataFrame from multiple CSV files in DateTime series

Question

I have a list of time series price data in CSV format that is read as follows:

asxList = ['ANZ', 'NAB', 'WBC']

for asxCode in asxList:
    ohlcData = pd.DataFrame.from_csv(asxCode+'.CSV', header=0)

Example output:

How do I assemble all the ohlcData in particular order, firstly by DateTime index, and secondly by the asxList ['ANZ', 'NAB', 'WBC'] index, then followed by the data columns?

You may use concat and set a multilevel index, first Date then your tick — rafaelc
– rafaelc, Commented May 4, 2018 at 0:39

DYZ · Accepted Answer · 2018-05-04 00:57:26Z

4

Create a list of dataframes, add a code column to each dataframe:

dfs = []
for asxCode in asxList:
    df = pd.DataFrame.from_csv(asxCode+'.CSV', header=0)
    df['code'] = asxCode
    dfs.append(df)

Concatenate the dataframes, add the code column to the index:

pd.concat(dfs).reset_index().set_index(['index', 'code'])

answered May 4, 2018 at 0:57

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

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

Comments

BENY · Accepted Answer · 2018-05-04 01:18:50Z

2

Almost same with Dyz, just using keys from concat

asxList = ['ANZ', 'NAB', 'WBC']
l=[]
for asxCode in asxList:
    l.append(pd.DataFrame.from_csv(asxCode+'.CSV', header=0))

pd.concat(l,keys=asxList)

answered May 4, 2018 at 1:18

BENY

324k22 gold badges176 silver badges250 bronze badges

2 Comments

DYZ Over a year ago

You can do pd.concat([pd.DataFrame.from_csv(asxCode+'.CSV', header=0) for asxCode in asxList], keys=asxList).

artDeco Over a year ago

Thanks Wen and the single-line code by DyZ is very Pythonic!

Collectives™ on Stack Overflow

Set up MultiIndex DataFrame from multiple CSV files in DateTime series

2 Answers 2

Comments

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related