CODEWITHSUNDEEP
pythonpandasmulti-index
briba
briba

Reputation: 2987

Convert MultiIndex to data frame

How can I convert a MultiIndex to a "regular" data frame?

Let's say I have this one:

columns = pd.MultiIndex.from_product([['C1', 'C2'], ['CA', 'CO', 'MI']],
                                  names=['subject', 'type'])

data=np.array(list(string.ascii_lowercase))[:24].reshape((4, 6))

df = pd.DataFrame(
    columns=columns,
    data=data
)

And I want to convert to something like this (or anything with a similar idea):

columns = ['name', 'subject', 'type']
agents_data = [
    (0, 'C1', 'CA', 'a'),
    (0, 'C1', 'CO', 'b'),
    ...
    (2, 'C2', 'CA', 'p'),
]
return pd.DataFrame.from_records(agents_data, columns=columns)

Is there any way to do this? (or something similar)

Something like this:

enter image description here

Into this:

enter image description here

Thank you!

Upvotes: 1

Views: 224

Answers (1)

jezrael
jezrael

Reputation: 863531

Use DataFrame.unstack with DataFrame.reset_index and rename:

df1 = df.unstack().reset_index(name='val').rename(columns={'level_2':'new'})
print (df1)
   subject type  new val
0       C1   CA    0   a
1       C1   CA    1   g
2       C1   CA    2   m
3       C1   CA    3   s
4       C1   CO    0   b
5       C1   CO    1   h
6       C1   CO    2   n
7       C1   CO    3   t
8       C1   MI    0   c
9       C1   MI    1   i
10      C1   MI    2   o
11      C1   MI    3   u
12      C2   CA    0   d
13      C2   CA    1   j
14      C2   CA    2   p
15      C2   CA    3   v
16      C2   CO    0   e
17      C2   CO    1   k
18      C2   CO    2   q
19      C2   CO    3   w
20      C2   MI    0   f
21      C2   MI    1   l
22      C2   MI    2   r
23      C2   MI    3   x

Thank you, @Mark Wang for idea use DataFrame.rename_axis before unstack:

df1 = df.rename_axis('new').unstack().reset_index(name='val')
print (df1)
   subject type  new val
0       C1   CA    0   a
1       C1   CA    1   g
2       C1   CA    2   m
3       C1   CA    3   s
4       C1   CO    0   b
5       C1   CO    1   h
6       C1   CO    2   n
7       C1   CO    3   t
8       C1   MI    0   c
9       C1   MI    1   i
10      C1   MI    2   o
11      C1   MI    3   u
12      C2   CA    0   d
13      C2   CA    1   j
14      C2   CA    2   p
15      C2   CA    3   v
16      C2   CO    0   e
17      C2   CO    1   k
18      C2   CO    2   q
19      C2   CO    3   w
20      C2   MI    0   f
21      C2   MI    1   l
22      C2   MI    2   r
23      C2   MI    3   x

Upvotes: 2

Related Questions