Concatenate all combinations of sub-level columns in a pandas DataFrame

Question

Given the following DataFrame:

cols = pd.MultiIndex.from_product([['A', 'B'], ['a', 'b']])
example = pd.DataFrame([[0, 1, 2, 3], [4, 5, 6, 7], [8, 9, 10, 11]], columns=cols)
example

        A       B
    a   b   a   b
0   0   1   2   3
1   4   5   6   7
2   8   9   10  11

I would like to end up with the following one:

    A   B
0   0   2
1   4   6
2   8   10
3   0   3
4   4   7
5   8   11
6   1   2
7   5   6
8   9   10
9   1   3
10  5   7
11  9   11

I used this code:

concatenated = pd.DataFrame([])
for A_sub_col in ('a', 'b'):
    for B_sub_col in ('a', 'b'):
        new_frame = example[[['A', A_sub_col], ['B', B_sub_col]]]
        new_frame.columns = ['A', 'B']
        concatenated = pd.concat([concatenated, new_frame])

However, I strongly suspect that there is a more straight-forward, idiomatic way to do that with Pandas. How would one go about it?

Answer 1

Here's an option using list comprehension:

pd.concat([
    example[[('A', i), ('B', j)]].droplevel(level=1, axis=1)
        for i in example['A'].columns
        for j in example['B'].columns
]).reset_index(drop=True)

Output:

    A   B
0   0   2
1   4   6
2   8  10
3   0   3
4   4   7
5   8  11
6   1   2
7   5   6
8   9  10
9   1   3
10  5   7
11  9  11

Answer 2

Here is one way. Not sure how more pythonic this is. It is definitely less readable :-) but on the other hand does not use explicit loops:

(example
    .apply(lambda c: [list(c)])
    .stack(level=1)
    .apply(lambda c:[list(c)])
    .explode('A')
    .explode('B')
    .apply(pd.Series.explode)
    .reset_index(drop = True)
)

to understand what's going on it would be helpful to do this one step at a time, but the end result is

    A   B
0   0   2
1   4   6
2   8   10
3   0   3
4   4   7
5   8   11
6   1   2
7   5   6
8   9   10
9   1   3
10  5   7
11  9   11