reorder data in pandas pivot_table function

Question

I've a sample dataframe

region   headquarter      sales    base    %growth    month_year
   X      Los Angeles     1000     2000      30        202101
   X      Florida City    2000     2000      20        202101
   X      Los Angeles     5000     6000      70        202001
   X      Florida City    4000     4500      45        202001

I'm trying to pivot the data using

data = data.pivot_table(columns=['month_year'], values=['sales', 'base', '%growth'], index=['region', 'headquarter'])

print(data)

>                              %growth          base           sales
         |               |   202001  202101  202001 202101   202001 202101
   -----------------------------------------------------------------------
  region |  headquarter  |
     x   |  Los Angeles  |    70    30     6000   2000       5000    1000
         |  Florida City |    45    20     4000   2000       4500    2000

The values are not in order which I've mentioned in the above snippet.

How can I re-structure my data to (by also repeating the row labels)

>                            202001                 202101
                         Sales  base  %growth    Sales  base  %growth     
   region   headquarter
     X      Los Angeles   5000   6000    70       1000   2000     30
     X      Florida City  4500   4000    45       2000   2000     20

jezrael · Accepted Answer · 2021-04-09T10:14:39.090

2

Use DataFrame.swaplevel with DataFrame.reindex:

mux = pd.MultiIndex.from_product([data['month_year'].unique(), ['Sales','base','%growth']])
data = data.pivot_table(columns=['month_year'], 
                        values=['Sales', 'base', '%growth'], 
                        index=['headquarter']).swaplevel(1, 0, axis=1).reindex(mux, axis=1)

print(data)
             202101               202001              
              Sales  base %growth  Sales  base %growth
headquarter                                           
Florida City   2000  2000      20   4000  4500      45
Los Angeles    1000  2000      30   5000  6000      70

EDIT:

mux = pd.MultiIndex.from_product([data['month_year'].unique(), ['sales','base','%growth']])


data = data.pivot_table(columns=['month_year'], 
                        values=['sales', 'base', '%growth'], 
                        index=['region', 'headquarter']).swaplevel(1, 0, axis=1).reindex(mux, axis=1)


print (data)
                    202101               202001              
                     sales  base %growth  sales  base %growth
region headquarter                                           
X      Florida City   2000  2000      20   4000  4500      45
       Los Angeles    1000  2000      30   5000  6000      70

edited Apr 09 '21 at 10:14

answered Mar 02 '21 at 08:36

jezrael

822,522
95
1,334
1,252

Thanks @jezrael. How do I rectify the values order in the pivot. I'm trying to get `sales base %growth`, but the pivot keeps giving me `%growth base sales`? – astroboy1 Mar 02 '21 at 08:43
@astroboy1 - yop, please use last version of answer. – jezrael Mar 02 '21 at 08:44
I've tried this, but when I add multiple column to index (as in the edited question) in pivot, your method isn't working. – astroboy1 Apr 09 '21 at 09:59
@astroboy1 - How was edded multiple columns to Index? – jezrael Apr 09 '21 at 10:01
`data = data.pivot_table(columns=['month_year'], values=['sales', 'base', '%growth'], index=['region', 'headquarter']` I've also mentioned new column region in index here. – astroboy1 Apr 09 '21 at 10:02
@astroboy1 - Added to answer solution above and for me working perfectly. – jezrael Apr 09 '21 at 10:07
Is the data in the region column getting repeated in the end result data? Its not working for me. idk why. – astroboy1 Apr 09 '21 at 10:12
@astroboy1 - Sure, only not diplayed. Wrong copied data, so edited answer again. Check [this](https://stackoverflow.com/questions/65732745/difficult-problem-with-multi-index-in-dataframe-in-python-pandas/65732783#65732783) for explained more. – jezrael Apr 09 '21 at 10:15
I've also tried with the display cotent option, didn't work for me. So I have raised a question [here](https://stackoverflow.com/questions/66972016/how-to-repeat-row-labels-in-pandas-pivot-table-function-and-export-it-as-excel?noredirect=1#comment118443056_66972016) again. – astroboy1 Apr 09 '21 at 10:20
@astroboy1 - So problem is write MultiIndex to excel? It is really not easy like pointed anky. – jezrael Apr 09 '21 at 10:22
Yes getting the data in excel is the big deal here. I'm looking for other ways to do that. But couldn't able to. – astroboy1 Apr 09 '21 at 10:25
@astroboy1 - unfortuantely only default way exist or some kind of hack solutions, Try looking for write multiindex to excel – jezrael Apr 09 '21 at 10:26

reorder data in pandas pivot_table function

1 Answers1