Convert MultiIndex to data frame

Question

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:

Into this:

Thank you!

jezrael · Accepted Answer · 2019-08-08 13:17:53Z

2

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

edited Aug 8, 2019 at 13:17

answered Aug 8, 2019 at 12:33

jezrael

868k103 gold badges1.4k silver badges1.3k bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Mark Wang Over a year ago

I would use rename_axis('new') right after df instead rename as it requires the knowledge of level_2

jezrael Over a year ago

@MarkWang - yes, another solution, but need rename_axis(('subject','type', 'new'))

Mark Wang Over a year ago

How about just rename_axis new before unstack (or I’m not understanding the df correctly?)

Collectives™ on Stack Overflow

Convert MultiIndex to data frame

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related