Pandas get index of rows where multi index matches specific value

Question

I would like to understand how I can get the index of rows where a multi-index index matches a specific value ("Subtotal").

I have the following DataFrame:

df = pd.DataFrame.from_dict({
    'card_type': {0: 'American Express', 1: 'American Express', 2: 'American Express', 3: 'American Express', 4: 'American Express', 5: 'American Express', 6: 'American Express', 7: 'Mastercard', 8: 'Mastercard', 9: 'Mastercard'},
    'payment_status': {0: 'Checked Out', 1: 'Checked Out', 2: 'Subtotal', 3: 'Confirmed', 4: 'Confirmed', 5: 'Confirmed', 6: 'Subtotal', 7: 'Cancelled', 8: 'Cancelled', 9: 'Subtotal'},
    'revenue - sum': {0: 591.49, 1: 255.52, 2: 1602.02, 3: 189.05, 4: 350.0, 5: 110.53, 6: 4258.48,7: 28.5, 8: 578.55, 9: 4637.71},
    'revenue - min': {0: 0.0, 1: 0.0, 2: 0.0, 3: 0.0, 4: 0.0, 5: 0.0, 6: 0.0, 7: 0.0, 8: 0.0, 9: 0.0},
    'debit - sum': {0: 2, 1: 2, 2: 5, 3: 1, 4: 1, 5: 1, 6: 3, 7: 3, 8: 2, 9: 22}}).set_index(['card_type', 'payment_status'])

I need to understand what are the rows number where the word "Subtotal" is present on the most right index (second level/payment_status) of the Dataframe. I have used reset_index to achieve what I need:

df_index = df.reset_index()['payment_status']
subtotals_indexes = df_index[df_index == 'Subtotal'].index.tolist() // [2, 6, 9] -> the values I need to get

However, I am not sure if this is the right way or if there is a more inexpensive way how to achieve the same result. I have also tried with df[df.index.get_level_values("payment_status") == "Subtotal"] but it seems I won't be lucky since this is primarily useful to get an individual level of values from a MultiIndex.

Any way I can get around this? Thanks!

Answer 1

You can use DataFrame.xs - and specify the value of the second level you wish to extract, eg:

df.xs('Subtotal', level=1)

Result:

                  revenue - sum  revenue - min  debit - sum
card_type                                                  
American Express        1602.02            0.0            5
American Express        4258.48            0.0            3
Mastercard              4637.71            0.0           22