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

https://stackoverflow.com/questions/52289386/loading-multiple-csv-files-of-a-folder-into-one-dataframe this helps? — PV8, Sep 23 '19 at 11:23
Can you put an example of the columns in the different csv and in the expected output pls — Mayeul sgc, Sep 23 '19 at 11:23

score 2 · Accepted Answer · answered Sep 23 '19 at 11:57

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 '19 at 11:57

jezrael

822,522
95
1,334
1,252

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). – Ben Dec 30 '22 at 11:27

Load Multiple CSV files into one DataFrame with multilevel

1 Answers1