Skip to content
Advertisement

How to combine dataframes based on index column name

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.

Advertisement

Answer

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
User contributions licensed under: CC BY-SA
2 People found this is helpful
Advertisement