Using Python/Pandas to transpose a dataframe with multiple column groups

Question

I have tried a bunch of different ways to do this in Python, and I'm sure i'm missing something. My data is like this:

Date | Stock 1 | Stock 2 | Stock 3 
11-30-86    2.45    0.47021 1.45879  
12-31-86    -2.57   1.09626 -2.67784  
01-31-87    13.5    9.33911 11.14448  
02-28-87    3.99    4.63777 5.96181

What I'd like to do is transpose the data BY stock name (column name) and have it rendered like this:

Stock Name | Date | Return
Stock 1 11-30-86    2.45  
Stock 2 11-30-86    0.47021  
Stock 3 11-30-86    1.45879  
Stock 1 12-31-86    -2.57  
Stock 2 12-31-86    1.09626  
Stock 3 12-31-86    -2.67784  
Stock 1 01-31-87    13.5  
Stock 2 01-31-87    9.33911  
Stock 3 01-31-87    11.14448  
Stock 1 02-28-87    3.99  
Stock 2 02-28-87    4.63777  
Stock 3 02-28-87    5.96181

I've trying different permutations of Pandas pivot/unpivot and transpose but I can't seem to get this working - I only ever get the entire table pivoted, where I want to use the different column names are BY groups and get the resulting single column.

What am I missing?

Please include the relevant code and examples of how it is failing within your question. You have described the problem well, but we're looking for code on Stack Overflow. Please review How to create a Minimal, Complete, and Verifiable example — Stefan Crain
– Stefan Crain, Commented Apr 5, 2018 at 17:09
Sorry @StefanCrain - my bad here, I will do better in future. — rocksteady
– rocksteady, Commented Apr 9, 2018 at 13:16

Scott Boston · Accepted Answer · 2018-04-05 17:01:43Z

1

Try using melt:

df.melt(id_vars='Date', var_name='Stock Name', value_name='Return')

Output:

        Date Stock Name    Return
0   11-30-86    Stock 1   2.45000
1   12-31-86    Stock 1  -2.57000
2   01-31-87    Stock 1  13.50000
3   02-28-87    Stock 1   3.99000
4   11-30-86    Stock 2   0.47021
5   12-31-86    Stock 2   1.09626
6   01-31-87    Stock 2   9.33911
7   02-28-87    Stock 2   4.63777
8   11-30-86    Stock 3   1.45879
9   12-31-86    Stock 3  -2.67784
10  01-31-87    Stock 3  11.14448
11  02-28-87    Stock 3   5.96181

answered Apr 5, 2018 at 17:01

Scott Boston

154k15 gold badges160 silver badges207 bronze badges

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

1 Comment

rocksteady Over a year ago

Thanks a ton @Scott_Boston. This is perfect!

BENY · Accepted Answer · 2018-04-05 17:02:01Z

1

There are two ways melt and stack

df.melt('Date')
Out[1303]: 
        Date variable     value
0   11-30-86   Stock1   2.45000
1   12-31-86   Stock1  -2.57000
2   01-31-87   Stock1  13.50000
3   02-28-87   Stock1   3.99000
4   11-30-86   Stock2   0.47021
5   12-31-86   Stock2   1.09626
6   01-31-87   Stock2   9.33911
7   02-28-87   Stock2   4.63777
8   11-30-86   Stock3   1.45879
9   12-31-86   Stock3  -2.67784
10  01-31-87   Stock3  11.14448
11  02-28-87   Stock3   5.96181
df.set_index('Date').stack().reset_index()
Out[1304]: 
        Date level_1         0
0   11-30-86  Stock1   2.45000
1   11-30-86  Stock2   0.47021
2   11-30-86  Stock3   1.45879
3   12-31-86  Stock1  -2.57000
4   12-31-86  Stock2   1.09626
5   12-31-86  Stock3  -2.67784
6   01-31-87  Stock1  13.50000
7   01-31-87  Stock2   9.33911
8   01-31-87  Stock3  11.14448
9   02-28-87  Stock1   3.99000
10  02-28-87  Stock2   4.63777
11  02-28-87  Stock3   5.96181

answered Apr 5, 2018 at 17:02

BENY

324k22 gold badges176 silver badges250 bronze badges

1 Comment

rocksteady Over a year ago

BOOM! That's the ticket! Nice and elegant - thank you @Wen! I am used to lingo from working with SAS, so I was stuck googling/searching for transpose and somehow didn't find df.melt

Collectives™ on Stack Overflow

Using Python/Pandas to transpose a dataframe with multiple column groups

2 Answers 2

1 Comment

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

1 Comment

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related