Transforming Dataframe columns into Dataframe of rows
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 cretailer_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 0Walmart 1944 2010 5 0 0 1945 2010 0 5 0 1947 2010 0 10 0 1949 2012 5 0 0With reset_index()
stores.groupby(level=[0, 1, 2, 3]).sum().unstack().reset_index().fillna(0) retailer_name store_number year amount product a b c0 CRV 1946 2011 8 0 01 CRV 1947 2012 6 0 02 CRV 1947 2013 0 0 113 CRV 1948 2011 6 1 04 CRV 1949 2012 12 0 05 Walmart 1944 2010 5 0 06 Walmart 1945 2010 0 5 07 Walmart 1947 2010 0 10 08 Walmart 1949 2012 5 0 0
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 cretailer_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 0Walmart 1944 2010 5 0 0 1945 2010 0 5 0 1947 2010 0 10 0 1949 2012 5 0 0