Pandas Dataframe Reshape with Multiple Index

Question

Working to understand reshape functions in Pandas. So far I can work with a reshape on a dataframe with a simple structure such as

d = {'id1': [1,1,1,2,2,2], 'id2': [1,1,1,1,1,1], 'value': [1,2,3,4,5,6], 'type':['A','B','C','A','B','C']}
tab = pd.DataFrame(data=d)      
tab.pivot(index = 'id1', columns = "type", values = "value")

However, in the situation that looks more like this I'm having some challenges understanding the multi index schema. Conceptually what I would want returned in this case is a new df with 3 rows. The indices are both id1 and id2 such that row 1 would be the values for the unique combination of id1=1 and id2=1 and then row 2 would be id1=1 and id2=2 and last row 3 would be id1=2 and id2=1.

d = {'id1': [1,1,1,1,1,1,2,2,2], 'id2': [1,1,1,2,2,2,1,1,1], 'value': [1,2,3,4,5,6,7,8,9], 'type':['A','B','C','A','B','C','A','B','C']}
tab = pd.DataFrame(data=d)      
tab.pivot(index = 'id1', columns = "type", values = "value")

Quang Hoang · Accepted Answer · 2020-07-30 11:43:11Z

2

It appears as though a simpler option is to use pivot_table function in pandas:

d = {'id1': [1,1,1,1,1,1,2,2,2], 'id2': [1,1,1,2,2,2,1,1,1], 'value': [1,2,3,4,5,6,7,8,9], 'type':['A','B','C','A','B','C','A','B','C']}
tab4 = pd.DataFrame(data=d)
tab4.pivot_table(
        values='value', 
        index=['id1', 'id2'], 
        columns='type')

yielding the following, which I believe is what you are after:

type     A  B  C
id1 id2         
1   1    1  2  3
    2    4  5  6
2   1    7  8  9

This was found through this question earlier post on the subject.

NOTE: this is my first answer on SO, so please let me know if I should be doing anything differently!

edited Jul 30, 2020 at 11:43

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

answered Jul 30, 2020 at 11:33

Daniel Ford

712 bronze badges

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

1 Comment

Quang Hoang Over a year ago

Good answer. I hope you don't mind me editing your answer. Generally (links to) pictures are discouraged.

Collectives™ on Stack Overflow

Pandas Dataframe Reshape with Multiple Index

1 Answer 1

1 Comment

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Linked

Related