merging dataframes to a multiindex dataframe

Question

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.

BENY · Accepted Answer · 2018-11-19 02:46:09Z

1

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

answered Nov 19, 2018 at 2:46

BENY

324k22 gold badges176 silver badges250 bronze badges

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

2 Comments

Ali Over a year ago

Thank you @W-B. I slightly modified the question. Would you mind answering that part if possible. What I have in mind is to reindex df1 to include all df2 indexes and then do the process that you kindly suggested. Does that sound reasonable?

BENY Over a year ago

@Ali doing this before above code df2=df2.reindex(df1.index,fill_value=1)

Collectives™ on Stack Overflow

merging dataframes to a multiindex dataframe

1 Answer 1

2 Comments

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Related