Pandas re-indexing with Multiindex on Index AND Columns

Question

I have a long dataframe with these columns in this format:

id  gender  size    region_a_count  region_b_count  item_group
 x   m       x            x                x         x
 x   f       x            x                x         x
 x   f       x            x                x         x
 x   m       x            x                x         x
 x   f       x            x                x         x
 x   m       x            x                x         x

Each combination of gender, size,item_group and region is unique. So there is no need to perform an aggregation.

I want to reindex the index and columns to this type of output, with the cell values staying the same

                    region_a         region_b        region_c   
                    m        f       m        f      m         f
      (index of size)
item_group  1                       
            2                       
            3                       
item_group  1                       
            2                       
            3

That is, with index as well as columns having a multiindex. I was able to perform the part for the index by calling:

df.groupby(["item_group","size"])

But the column problem still remains.

How can I create the column multiindex from the existing dataframe?

Hi @jezrael, Its difficult to paste them nicely formatted. They are a mix of numbers and strings. They aren't relevant though, considering that each combination is unique :) — Dominique Paul
– Dominique Paul, Commented Oct 24, 2018 at 13:04
So solution working for you? If not, be free change x to a, b, c or 1, 2, 3 and also add it to expected output. — jezrael
– jezrael, Commented Oct 24, 2018 at 13:05
@jezrael Sorry, didnt see that you are the same person who answered the question. Yes, solution worked! Thanks :) — Dominique Paul
– Dominique Paul, Commented Oct 24, 2018 at 13:20

jezrael · Accepted Answer · 2018-10-24 13:20:59Z

1

I believe you need set_index with unstack:

df1 = df.set_index(["item_group","size", "id","gender"]).unstack()

edited Oct 24, 2018 at 13:20

answered Oct 24, 2018 at 12:40

jezrael

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

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

Collectives™ on Stack Overflow

Pandas re-indexing with Multiindex on Index AND Columns

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related