CODEWITHSUNDEEP
pythonpandasdataframemulti-index
Ali
Ali

Reputation: 466

merging dataframes to a multiindex dataframe

I have a dataframe like the following which indicates some cost in year 1:

    b1  b2
t1  100 110
t2  100 120
t3  150 180

I also have another dataframe which shows these costs multipliers over several years:

    y1  y2      y3
t1  1   1.1     1.2
t2  1   1.08    1.15

I want the output to be something like the following (Note that t3 is not in the above dataframe therefore it gets multipliers of 1):

t1  b1  y1  100
        y2  110
        y3  120
    b2  y1  110
        y2  121
        y3  132
t2  b1  y1  100
        y2  108
        y3  115
    b2  y1  120
        y2  129.6
        y3  138
t3  b1  y1  150
        y2  150
        y3  150
     b2 y1  180
        y2  180
        y3  180

How can I achieve this?

Thank you very much for your help in advance.

Upvotes: 0

Views: 63

Answers (1)

BENY
BENY

Reputation: 323226

Using merge after stack 

newdf=df1.stack().reset_index().merge(df2.stack().reset_index(),on='level_0')
newdf.assign(v=newdf['0_x']*newdf['0_y']).set_index(['level_0','level_1_x','level_1_y']).v
Out[133]: 
    level_0  level_1_x  level_1_y
    t1       b1         y1           100.0
                        y2           110.0
                        y3           120.0
             b2         y1           110.0
                        y2           121.0
                        y3           132.0
    t2       b1         y1           100.0
                        y2           108.0
                        y3           115.0
             b2         y1           120.0
                        y2           129.6
                        y3           138.0
    Name: v, dtype: float64

Upvotes: 1

Related Questions