CODEWITHSUNDEEP
pythonpandasdataframemerge
Niccola Tartaglia
Niccola Tartaglia

Reputation: 1667

Merge two Pandas Dataframes

I have the following merging problem:

I have a time series of industry related data: weekly Profit Margins for 60 different industries over multiple years, which looks like this:

industry = pd.DataFrame({'Ind0': ['01', '02', '03', '04'],                   
'Ind1': ['11', '12', '13', '14'],
'Ind2': ['21', '22', '23', '24'],
'Ind3': ['31', '32', '33', '34']})

My 2nd dataframe consists of a few 1,000 stocks and their respective industries (each stock belongs to exactly one industry)

stocks = pd.DataFrame({'Stock0': ['Ind0'], 
'Stock1': ['Ind1'],
'Stock2': ['Ind2'],
'Stock3': ['Ind3'],
'Stock4': ['Ind0'],
'Stock5': ['Ind1']})

I would like to create a new dataframe that contains the industry time series for each stock coming from the correct industry that the stock belongs to, i.e. something like this:

result = pd.DataFrame({'Stock0': ['01', '02', '03', '04'],                   
'Stock1': ['11', '12', '13', '14'],
'Stock2': ['21', '22', '23', '24'],
'Stock3': ['31', '32', '33', '34'],
'Stock4': ['01', '02', '03', '04'],
'Stock5': ['11', '12', '13', '14']})

I have tried a number of merge/concatenate approaches without success. Any help is appreciated.

Upvotes: 0

Views: 410

Answers (1)

BENY
BENY

Reputation: 323376

Is this what you want?

stocks.T.merge(industry.T,left_on=0,right_index=True).drop(['key_0','0_x'],axis=1).rename(columns={'0_y':0}).T
Out[189]: 
  Stock0 Stock4 Stock1 Stock5 Stock2 Stock3
0     01     01     11     11     21     31
1     02     02     12     12     22     32
2     03     03     13     13     23     33
3     04     04     14     14     24     34

Upvotes: 2

Related Questions