Josh
Josh

Reputation: 1960

Stack and Pivot Dataframe in Python

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

Upvotes: 1

Views: 187

Answers (2)

erasmortg
erasmortg

Reputation: 3278

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

Upvotes: 3

Randy
Randy

Reputation: 14847

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

Upvotes: 1

Related Questions