Transforming Dataframe columns into Dataframe of rows

Question

I have following DataFrame:

data = {'year': [2010, 2010, 2011, 2012, 2011, 2012, 2010, 2011, 2012, 2013],
        'store_number': ['1944', '1945', '1946', '1947', '1948', '1949', '1947', '1948', '1949', '1947'],
        'retailer_name': ['Walmart','Walmart', 'CRV', 'CRV', 'CRV', 'Walmart', 'Walmart', 'CRV', 'CRV', 'CRV'],
        'product': ['a', 'b', 'a', 'a', 'b', 'a', 'b', 'a', 'a', 'c'],
        'amount': [5, 5, 8, 6, 1, 5, 10, 6, 12, 11]}

stores = pd.DataFrame(data, columns=['retailer_name', 'store_number', 'year', 'product', 'amount'])
stores.set_index(['retailer_name', 'store_number', 'year', 'product'], inplace=True)

stores.groupby(level=[0, 1, 2, 3]).sum()

I want to transform following Dataframe:

                                         amount
retailer_name store_number year product        
CRV           1946         2011 a             8
              1947         2012 a             6
                           2013 c            11
              1948         2011 a             6
                                b             1
              1949         2012 a            12
Walmart       1944         2010 a             5
              1945         2010 b             5
              1947         2010 b            10
              1949         2012 a             5

into dataframe of rows:

retailer_name store_number year a b c
CRV           1946         2011 8 0 0
CRV           1947         2012 6 0 0
etc...

The products are known ahead. Any idea how to do so ?

score 7 · Accepted Answer · 2016-05-19 20:55:18Z

7

Please see below for solution. Thanks to EdChum for corrections to original post.

Without reset_index()

stores.groupby(level=[0, 1, 2, 3]).sum().unstack().fillna(0)




                 amount        
product                              a   b   c
retailer_name store_number year               
CRV           1946         2011      8   0   0
              1947         2012      6   0   0
                           2013      0   0  11
              1948         2011      6   1   0
              1949         2012     12   0   0
Walmart       1944         2010      5   0   0
              1945         2010      0   5   0
              1947         2010      0  10   0
              1949         2012      5   0   0

With reset_index()

stores.groupby(level=[0, 1, 2, 3]).sum().unstack().reset_index().fillna(0)



  retailer_name store_number  year amount        
product                                       a   b   c
0                 CRV         1946  2011      8   0   0
1                 CRV         1947  2012      6   0   0
2                 CRV         1947  2013      0   0  11
3                 CRV         1948  2011      6   1   0
4                 CRV         1949  2012     12   0   0
5             Walmart         1944  2010      5   0   0
6             Walmart         1945  2010      0   5   0
7             Walmart         1947  2010      0  10   0
8             Walmart         1949  2012      5   0   0

edited May 19, 2016 at 20:55

answered May 19, 2016 at 20:46

user4974662

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

5 Comments

EdChum Over a year ago

This is probably the correct answer but you probably need to call reset_index() and fillna(0) and also post text rather than an image stores.groupby(level=[0, 1, 2, 3]).sum().unstack().fillna(0).reset_index()

EdChum Over a year ago

Because the OP has shown the retailer_name repeating so it looks like they want to restore the indeices

EdChum Over a year ago

@NightWalker stores.groupby(level=[0, 1, 2, 3])['amount'].sum().unstack().fillna(0).reset_index() basically you select using [] on the groupby` object

Night Walker Over a year ago

@pyNoob how I can specify that I am using 'amount' to fill the values ?

user4974662 Over a year ago

@NightWalker sorry, just seeing your question now. Alexander answered it below though. Index slice

Alexander · Accepted Answer · 2016-05-19 21:20:21Z

7

Unstack product from the index and fill NaN values with zero.

df = stores.groupby(level=[0, 1, 2, 3]).sum().unstack('product')
mask = pd.IndexSlice['amount', :]
df.loc[:, mask] = df.loc[:, mask].fillna(0)

>>> df
                                amount        
product                              a   b   c
retailer_name store_number year               
CRV           1946         2011      8   0   0
              1947         2012      6   0   0
                           2013      0   0  11
              1948         2011      6   1   0
              1949         2012     12   0   0
Walmart       1944         2010      5   0   0
              1945         2010      0   5   0
              1947         2010      0  10   0
              1949         2012      5   0   0

edited May 19, 2016 at 21:20

answered May 19, 2016 at 20:56

Alexander

111k32 gold badges212 silver badges208 bronze badges

Collectives™ on Stack Overflow

Transforming Dataframe columns into Dataframe of rows

2 Answers 2

5 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

5 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related