Create columns from index values

Question

Let say I have my data shaped as in this example

idx = pd.MultiIndex.from_product([[1, 2, 3, 4, 5, 6], ['a', 'b', 'c']],
                                 names=['numbers', 'letters'])
col = ['Value']

df = pd.DataFrame(list(range(18)), idx, col)

print(df.unstack())

The output will be

            Value        
letters     a   b   c
numbers              
1           0   1   2
2           3   4   5
3           6   7   8
4           9  10  11
5          12  13  14
6          15  16  17

letters and numbers are indexes and Value is the only column

The question is how can I replace Value column with columns named as values of index letters?

So I would like to get such output

numbers     a   b   c         
1           0   1   2
2           3   4   5
3           6   7   8
4           9  10  11
5          12  13  14
6          15  16  17

where a, b and c are columns and numbers is the only index.

Appreciate your help.

BENY · Accepted Answer · 2019-04-05 15:18:13Z

4

The problem is caused by you are using unstack with DataFrame, not pd.Series

df.Value.unstack().rename_axis(None,1)
Out[151]: 
          a   b   c
numbers            
1         0   1   2
2         3   4   5
3         6   7   8
4         9  10  11
5        12  13  14
6        15  16  17

answered Apr 5, 2019 at 15:18

BENY

324k22 gold badges176 silver badges250 bronze badges

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

1 Comment

irriss Over a year ago

The problem is caused by you are using unstack with DataFrame, not pd.Series - that's the key! thank you

KenHBS · Accepted Answer · 2019-04-05 16:58:53Z

1

Wen-Ben's answer prevents you from running into a data frame with multiple column levels in the first place.

If you happened to be stuck with a multi-index column anyway, you can get rid of it by using .droplevel():

df = df.unstack()
df.columns = df.columns.droplevel()

df

Out[7]: 
letters   a   b   c
numbers            
1         0   1   2
2         3   4   5
3         6   7   8
4         9  10  11
5        12  13  14
6        15  16  17

answered Apr 5, 2019 at 16:58

KenHBS

7,2546 gold badges41 silver badges55 bronze badges

Collectives™ on Stack Overflow

Create columns from index values

2 Answers 2

1 Comment

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

Comments

Your Answer

Sign up or log in

Post as a guest

Related