Flatten multi-index pandas dataframe where column names become values

Question

Let us say I have the following dataframe:

import pandas as pd
df = pd.DataFrame(data={'Status' : ['green','green','red','blue','red','yellow','black'],
 'Group' : ['A','A','B','C','A','B','C'],
 'City' : ['Toronto','Montreal','Vancouver','Toronto','Edmonton','Winnipeg','Windsor'],
 'Sales' : [13,6,16,8,4,3,1]})
df.drop('Status',axis=1,inplace=True)
ndf = pd.pivot_table(df,values=['Sales'],index=['City'],columns=['Group'],fill_value=0,margins=False)

The result looks like this:

In [321]: ndf
Out[321]:
          Sales
Group         A   B  C
City
Edmonton      4   0  0
Montreal      6   0  0
Toronto      13   0  8
Vancouver     0  16  0
Windsor       0   0  1
Winnipeg      0   3  0

How can I flatten it such that it becomes a single level data frame but with a column specifying the group?

I.e., the result should be:

City    group   sales
Edmonton    A   4
Edmonton    B   0
Edmonton    C   0
Montreal    A   6
Montreal    B   0
Montreal    C   0
Toronto     A   13
Toronto     B   0
Toronto     C   8
Vancouver   A   0
Vancouver   B   16
Vancouver   C   0
Windsor     A   0
Windsor     B   0
Windsor     C   1
Winnipeg    A   0
Winnipeg    B   3
Winnipeg    C   0

Zero · Accepted Answer · 2017-09-19 17:49:51Z

16

Use stack and reset_index

In [1260]: ndf.stack().reset_index()
Out[1260]:
         City Group  Sales
0    Edmonton     A      4
1    Edmonton     B      0
2    Edmonton     C      0
3    Montreal     A      6
4    Montreal     B      0
5    Montreal     C      0
6     Toronto     A     13
7     Toronto     B      0
8     Toronto     C      8
9   Vancouver     A      0
10  Vancouver     B     16
11  Vancouver     C      0
12    Windsor     A      0
13    Windsor     B      0
14    Windsor     C      1
15   Winnipeg     A      0
16   Winnipeg     B      3
17   Winnipeg     C      0

edited Sep 19, 2017 at 17:49

answered Sep 19, 2017 at 17:49

Zero

77.4k22 gold badges153 silver badges153 bronze badges

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

2 Comments

gold_cy Over a year ago

I think its time to start a new session =p

piRSquared Over a year ago

Don't do it... keep the faith

gold_cy · Accepted Answer · 2017-09-19 17:49:15Z

7

This is easily accomplished as such:

ndf.stack().reset_index()

         City Group  Sales
0    Edmonton     A      4
1    Edmonton     B      0
2    Edmonton     C      0
3    Montreal     A      6
4    Montreal     B      0
5    Montreal     C      0
6     Toronto     A     13
7     Toronto     B      0
8     Toronto     C      8
9   Vancouver     A      0
10  Vancouver     B     16
11  Vancouver     C      0
12    Windsor     A      0
13    Windsor     B      0
14    Windsor     C      1
15   Winnipeg     A      0
16   Winnipeg     B      3
17   Winnipeg     C      0

answered Sep 19, 2017 at 17:49

gold_cy

14.2k4 gold badges27 silver badges55 bronze badges

Collectives™ on Stack Overflow

Flatten multi-index pandas dataframe where column names become values

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related