Python Pandas Multiindex Slicing/Indexing to obtain duplicate data

Question

I'm new to python and I'm working with pandas dataframes with multiple indices. I want to take one dataframe and slice/combine/index it with another dataframe. The first looks like this:

    a
Out[123]: 
     col1  col2 col3 col4
     lion tiger bear ohmy
row1    1     5    1    2
row2    2     6    2    3
row3    3     7    3    4
row4    4     8    4    5

The second is:

    b
Out[124]: 
    col group
0  col2     A
1  col3     B
2  col3     C
3  col4     D
4  col4     A

And I would like to generate the following (which I would then like to group by the second level of the index - A, B, C, D):

    d
Out[125]: 
     col2 col3    col4   
        A    B  C    A  D
row1    5    1  1    2  2
row2    6    2  2    3  3
row3    7    3  3    4  4
row4    8    4  4    5  5

Everytime I try to index the first dataframe with the second (a[b['col']]) I get the following error: NotImplementedError: Index._join_level on non-unique index is not implemented

Answer 1

You can try

s=df2.groupby('col').count()

s1=df.loc[:,s.index.tolist()]


Out=df2.merge(s1.T.reset_index(),left_on='col',right_on='level_0').drop(['level_0','level_1'],1).set_index(['col','group']).T

Out
Out[404]: 
col   col2 col3    col4   
group    A    B  C    D  A
row1     5    1  1    2  2
row2     6    2  2    3  3
row3     7    3  3    4  4
row4     8    4  4    5  5