python pandas dataframe multiindex concat

Question

how can I concat two dataframes with the same multi index in the following example?

Dataframe1:

EOAN
                  Close
DateTime   Stock       
2021-02-27 EOAN   8.450
2021-03-06 EOAN   8.436
2021-03-13 EOAN   8.812
2021-03-20 EOAN   8.820
2021-03-24 EOAN   9.084

Dataframe2:

SAP
                   Close
DateTime   Stock        
2021-02-27 SAP    102.06
2021-03-06 SAP    101.78
2021-03-13 SAP    103.04
2021-03-20 SAP    103.60
2021-03-24 SAP    103.06
                       0      1

I get following result, when the code gets executed:

DateTime   Stock               
2021-02-27 EOAN      NaN  8.450
           SAP    102.06    NaN
2021-03-06 EOAN      NaN  8.436
           SAP    101.78    NaN
2021-03-13 EOAN      NaN  8.812
           SAP    103.04    NaN
2021-03-20 EOAN      NaN  8.820
           SAP    103.60    NaN
2021-03-24 EOAN      NaN  9.084
           SAP    103.06    NaN

I get the dataframe like this:

for stock in stocks:

    df = pandas.DataFrame(app.data, columns=['DateTime', 'Close'])
    df['DateTime'] = pandas.to_datetime(df['DateTime'], yearfirst=False)
    df['Stock'] = my_stock
    df = df.set_index(['DateTime', 'Stock'])
    app.data.clear()
    
    if df_all is None:
        df_all = df
    else:
        df_all = pandas.concat([df,df_all], axis = 1)

df_all.stack()
print(df_all)

What I try to get is the following result, that also works with more than two stocks:

DateTime   Stock   Close            
2021-02-27 EOAN    8.450  
           SAP    102.06
2021-03-06 EOAN    8.436  
           SAP    101.78
2021-03-13 EOAN    8.812  
           SAP    103.04
2021-03-20 EOAN    8.820 
           SAP    103.60    
2021-03-24 EOAN    9.084  
           SAP    103.06

Are you sure you want to concatenate along the columns, i.e., axis = 1? Maybe have a look at the documentation: pandas.pydata.org/docs/reference/api/pandas.concat.html — gofvonx
– gofvonx, Commented Mar 24, 2021 at 13:39
yes, it seems otherwise I get no multiindex, so that I can't stack them — lutz
– lutz, Commented Mar 24, 2021 at 13:41

gofvonx · Accepted Answer · 2021-03-24 13:44:50Z

2

Sample data:

df1 = pd.DataFrame.from_dict({'Close': {('2021-02-27', 'EOAN'): 8.45,
('2021-03-06', 'EOAN'): 8.436,
('2021-03-13', 'EOAN'): 8.812,
('2021-03-20', 'EOAN'): 8.82,
('2021-03-24', 'EOAN'): 9.084}})

df2 = pd.DataFrame({'Close': {('2021-02-27', 'SAP'): 102.06,
('2021-03-06', 'SAP'): 101.78,
('2021-03-13', 'SAP'): 103.04,
('2021-03-20', 'SAP'): 103.6,
('2021-03-24', 'SAP'): 103.06}})

Concatenating along the index will create a MultiIndex as the union of the indices of df1 and df2. To get the desired output you may want to use sort_index() after concatenation:

pd.concat([df1, df2], axis=0).sort_index()

answered Mar 24, 2021 at 13:44

gofvonx

1,4591 gold badge14 silver badges23 bronze badges

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

Collectives™ on Stack Overflow

python pandas dataframe multiindex concat

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related