Load Multiple CSV files into one DataFrame with multilevel

Question

I want to load multiple CSV files into one dataframe. Each CSV contains stock data with 6 columns ( 'Open', 'High', 'Low', 'Close', 'Adj Close', 'Volume' ) . I managed to load the CSV files, but I'm missing the column name ( each ticker, from CSV ).

sp500 =  os.listdir(os.path.splitext(os.getcwd()+'/spy500')[0])

combined = pd.concat([pd.read_csv('spy500/'+i, parse_dates=True, index_col='Date') for i in sp500], axis=1)

output:

Open    | High  |Low    |Close| Adj Close   |Volume|    Open|   High|   Low Close|  Adj Close   |Volume

desire output:

AAPL                                            | GOOG                  
Open |High  |Low    |Close  |Adj Close  |Volume |Open   |High   |Low    |Close  |Adj Close  |Volume

the output is correct, the only thing I need is to add a multi level column: 5986 rows × 3030 columns

Can you put an example of the columns in the different csv and in the expected output pls — Mayeul sgc
– Mayeul sgc, Commented Sep 23, 2019 at 11:23

jezrael · Accepted Answer · 2019-09-23 11:57:24Z

2

Use dictionary comprehension:

comp = {i.split('.')[0]: 
        pd.read_csv('spy500/'+i, parse_dates=True, index_col='Date') for i in sp500}
combined = pd.concat(comp, axis=1)

answered Sep 23, 2019 at 11:57

jezrael

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

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

1 Comment

Ben Over a year ago

Can you suggest a solution like this that will do the same job but in parallel computation? I know that it is possible for example to use read_csv("/*.csv") which will read those files into a single dataframe using multiple cores (speaking about Dask specifically).

Collectives™ on Stack Overflow

Load Multiple CSV files into one DataFrame with multilevel

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related