Turn column levels inside-out

Question

I have a pandas DataFrame which looks like this (code to create it is at the bottom of the question):

  col_1 col_2 foo_1       foo_2      
              col_3 col_4 col_3 col_4
0     1     4     2     8     5     7
1     3     1     6     3     8     9

I would like to turn the foo_1 and foo_2 columns "inside out", i.e. my expected output is:

   col_1  col_2                     col_3                     col_4
0      1      4  {'foo_1': 2, 'foo_2': 5}  {'foo_1': 8, 'foo_2': 7}
1      3      1  {'foo_1': 6, 'foo_2': 8}  {'foo_1': 3, 'foo_2': 9}

Is there an efficient (i.e. that does not involve writing a python loop that goes through each row one-by-one) way to do this in pandas?

---

Code to generate the starting DataFrame:

import pandas as pd

cols = pd.MultiIndex.from_tuples(
    [
        ("col_1", ""),
        ("col_2", ""),
        ("foo_1", "col_3"),
        ("foo_1", "col_4"),
        ("foo_2", "col_3"),
        ("foo_2", "col_4"),
    ]
)
df = pd.DataFrame([[1, 4, 2, 8, 5, 7], [3, 1, 6, 3, 8, 9]], columns=cols)

Code to generate expected output:

pd.DataFrame(
    [
        {
            "col_1": 1,
            "col_2": 4,
            "col_3": {"foo_1": 2, "foo_2": 5},
            "col_4": {"foo_1": 8, "foo_2": 7},
        },
        {
            "col_1": 3,
            "col_2": 1,
            "col_3": {"foo_1": 6, "foo_2": 8},
            "col_4": {"foo_1": 3, "foo_2": 9},
        },
    ]
)

Answer 1

Use DataFrame.filter + DataFrame.droplevel and aggregate the columns along axis=1 using dict, finally use DataFrame.drop to drop the MultiLevel columns:

df['col_3'] = df.filter(like='col_3').droplevel(1, 1).agg(dict, axis=1)
df['col_4'] = df.filter(like='col_4').droplevel(1, 1).agg(dict, axis=1)

df = df.drop(['foo_1', 'foo_2'], 1).droplevel(1, 1)

Result:

# print(df)

  col_1 col_2                     col_3                     col_4
0     1     4  {'foo_1': 2, 'foo_2': 5}  {'foo_1': 8, 'foo_2': 7}
1     3     1  {'foo_1': 6, 'foo_2': 8}  {'foo_1': 3, 'foo_2': 9}