How to combine dataframes based on index column name-CodePudding

Hello I am new to python and I have 2 dfs and a list of tickers and i would like to combine the 2 dfs based on a list of tickers. My second df had the tickers imported from an excel sheet and so the column names in the index are in a different order, I am not sure if that changes anything.

df1 looks like

df1

index	ABC	DEF	XYZ
avg	2	6	12
std	1	2	3
var	24	25	35
max	56	66	78

df 2

index	10	40	96
ticker	XYZ	ABC	DEF
Sector	Auto	Tech	Mining

I would like to combine them based on their ticker names in a third df with all the information so it looks something like this

df3

index	ABC	DEF	XYZ
avg	2	6	12
std	1	2	3
var	24	25	35
max	56	66	78
Sector	Tech	Mining	Auto

I have tried this

df3= pd.concat([df1,df2], ignore_index=True)

but it made a df where they were side by side instead of in one combine df. Any help would be appreciated.

CodePudding user response：

You need to set the index

df2 = df2.set_index('index').T.set_index('ticker').T
out = pd.concat([df1,df2])
         ABC     DEF   XYZ
index                     
avg        2       6    12
std        1       2     3
var       24      25    35
max       56      66    78
Sector  Tech  Mining  Auto