Stack and Pivot Dataframe in Python

Question

I have a wide dataframe that I want to stack and pivot and can't quite figure out how to do it.

Here is what I am starting with

testdf = pd.DataFrame({"Topic":["A","B","B","C","A"],
                       "Org":[1,1,2,3,5,],
                       "DE1":["a","c","d","e","f"],
                       "DE2":["b","c","a","d","h"],
                       "DE3":["a","c","b","e","f"] })

testdf
Out[40]: 
  DE1 DE2 DE3  Org Topic
0   a   b   a    1     A
1   c   c   c    1     B
2   d   a   b    2     B
3   e   d   e    3     C
4   f   h   f    5     A

What I would like to do is pivot the table so that the column values for Org are the Column names and the column values for each name are the matching values from D1,D2 and D3 and finally have Topic as the index. Is this even possible?

EDIT: As Randy C pointed out, if I use pivot I can get the following;

testdf.pivot(index = "Topic",columns = "Org")
Out[44]: 
       DE1                 DE2                 DE3               
Org      1    2    3    5    1    2    3    5    1    2    3    5
Topic                                                            
A        a  NaN  NaN    f    b  NaN  NaN    h    a  NaN  NaN    f
B        c    d  NaN  NaN    c    a  NaN  NaN    c    b  NaN  NaN
C      NaN  NaN    e  NaN  NaN  NaN    d  NaN  NaN  NaN    e  NaN

Which is close, but I would like to have it so that the DE values are "stacked" and not wide. The result would look like;

    Org      1    2    3    5    
Topic                                                            
A           a  NaN  NaN    f    
A           b  NaN  NaN    h   
A           a  NaN  NaN    f
B           c    d  NaN  NaN    
B           c    a  NaN  NaN   
B           c    b  NaN  NaN
C           NaN  NaN    e  NaN 
C           NaN  NaN    d  NaN  
C           NaN  NaN    e  NaN

erasmortg · Accepted Answer · 2015-10-16 14:35:31Z

3

Perhaps:

In[249]: testdf.pivot("Org","Topic").T
Out[249]: 
Org          1    2    3    5
    Topic                    
DE1 A        a  NaN  NaN    f
    B        c    d  NaN  NaN
    C      NaN  NaN    e  NaN
DE2 A        b  NaN  NaN    h
    B        c    a  NaN  NaN
    C      NaN  NaN    d  NaN
DE3 A        a  NaN  NaN    f
    B        c    b  NaN  NaN
    C      NaN  NaN    e  NaN

answered Oct 16, 2015 at 14:35

erasmortg

3,2881 gold badge20 silver badges34 bronze badges

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

Comments

Randy · Accepted Answer · 2015-10-16 14:17:32Z

1

It's not 100% clear to me what your desired output is, but as best I can understand it, .pivot() does seem to be at least close to what you're looking for:

In [8]: testdf.pivot("Topic", "Org")
Out[8]:
       DE1                 DE2                 DE3
Org      1    2    3    5    1    2    3    5    1    2    3    5
Topic
A        a  NaN  NaN    f    b  NaN  NaN    h    a  NaN  NaN    f
B        c    d  NaN  NaN    c    a  NaN  NaN    c    b  NaN  NaN
C      NaN  NaN    e  NaN  NaN  NaN    d  NaN  NaN  NaN    e  NaN

answered Oct 16, 2015 at 14:17

Randy

14.9k3 gold badges40 silver badges44 bronze badges

Collectives™ on Stack Overflow

Stack and Pivot Dataframe in Python

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related