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-09 10:14:39Z

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 9, 2021 at 10:14

answered Mar 2, 2021 at 8:36

jezrael

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

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

12 Comments

astroboy1 Over a year ago

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?

jezrael Over a year ago

@astroboy1 - yop, please use last version of answer.

astroboy1 Over a year ago

I've tried this, but when I add multiple column to index (as in the edited question) in pivot, your method isn't working.

jezrael Over a year ago

@astroboy1 - How was edded multiple columns to Index?

astroboy1 Over a year ago

data = data.pivot_table(columns=['month_year'], values=['sales', 'base', '%growth'], index=['region', 'headquarter'] I've also mentioned new column region in index here.

|

Collectives™ on Stack Overflow

reorder data in pandas pivot_table function

1 Answer 1

12 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

12 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related