Selecting repeated values of a multi-index level with .loc

user_10184272 0
Viewed 458

First lets suppose I have a pandas dataframe with a single index. If I use .loc[] to select index 'A' twice, it will return a dataframe with index 'A' repeated twice:

df_1 = pd.DataFrame([1,2,3], index=['A','B','C'], columns=['Col_1'])
df_1

    Col_1
A   1
B   2
C   3

df_1.loc[['A','A','B']]

    Col_1
A   1
A   1
B   2

Now lets suppose we have a dataframe with a multi-index. If I use .loc[] to select index 'A' twice, it will return a dataframe with index 'A' included only once:

ix = pd.MultiIndex.from_product([['A', 'B', 'C'], ['foo', 'bar']], names=['Idx1', 'Idx2'])
data = np.arange(len(ix))
df_2 = pd.DataFrame(data, index=ix, columns=['Col_1'])
df_2

           Col_1
Idx1 Idx2       
A    foo       0
     bar       1
B    foo       2
     bar       3
C    foo       4
     bar       5

df_2.loc[['A','A','B']]


           Col_1
Idx1 Idx2   
A    foo       0
     bar       1
B    foo       2
     bar       3

Is there any way to select repeated values of a multi-index level using .loc?

multi-index pandas python
1 Answers

Pandas tries to keep the levels of a MultiIndex unique. When you use loc with a list that refers to values of the first level of the MultiIndex it will keep things unique. If you want something different, you'll need to be explicit and use tuples.

specific_index_values = (
    [('A', 'foo'), ('A', 'bar')] * 2 +
    [('B', 'foo'), ('B', 'bar')]
)

df_2.loc[specific_index_values, :]

           Col_1
Idx1 Idx2       
A    foo       0
     bar       1
     foo       0
     bar       1
B    foo       2
     bar       3

pandas.concat

I find this distasteful but...

pd.concat([df_2.loc[[x]] for x in ['A', 'A', 'B']])

           Col_1
Idx1 Idx2       
A    foo       0
     bar       1
     foo       0
     bar       1
B    foo       2
     bar       3
user_23366540
Related