1

My pd.DataFrame looks like the one as follows:

import pandas as pd

df = pd.DataFrame({"MCD":[1,2,-2,-3, 5],
                   "AAPL":[-0.3, -0.6, 0.6, 0.9, -1.5],
                   "GOOG":[-0.8, 1.6, 2.4, 3.6 , -4],
                   "MSFT":[-0.1, -0.4, 0.8, 1.1, -1.3]})
df
>>> 
    MCD  AAPL   GOOG    MSFT
 0   1   -0.3   -0.8    -0.1
 1   2   -0.6    1.6    -0.4
 2  -2    0.6    2.4     0.8
 3  -3    0.9    3.6     1.1
 4   5   -1.5   -4.0    -1.3

I want my dataframe to look like this by using the method .melt.

desired_df
>>>
    ticker   return
0    MCD       1
1    MCD       2
2    MCD      -2
3    MCD      -3
4    MCD       5
5    AAPL     -0.3
6    AAPL     -0.6
7    AAPL      0.6
....
18   MSFT      1.1
19   MSFT     -1.3

How can I turn my df to desired_df using the method .melt ?

p.s. One doesn't need to use .melt if there is a better way to answer my question. To me, .melt seems to be a good solution to answer my question so I just used it as an example method. :D

p.s2. My question was closed because someone claimed that there were similar questions. However, those questions do not answer my questions specifically. So I thought my question deserved to be not deleted.

Sunderam Dubey
  • 1
  • 11
  • 20
  • 40
Eiffelbear
  • 399
  • 1
  • 4
  • 23

2 Answers2

3

You can use melt like this:

df.melt(var_name='ticker', value_name='return')

Or, without melt, you can simply use stack, the rest is only formatting/renaming:

(df.rename_axis('ticker', axis=1)
   .stack()
   .rename('return')
   .reset_index(level=1)
   .reset_index(drop=True)
)

output:

   ticker  return
0     MCD     1.0
1    AAPL    -0.3
2    GOOG    -0.8
3    MSFT    -0.1
4     MCD     2.0
...
19   MSFT    -1.3
Ch3steR
  • 20,090
  • 4
  • 28
  • 58
mozway
  • 194,879
  • 13
  • 39
  • 75
1

Use T and melt:

>>> df.T.reset_index().melt('index', value_name='return').rename(columns={'index': 'tickers'}).drop('variable', axis=1)
   tickers  return
0      MCD     1.0
1     AAPL    -0.3
2     GOOG    -0.8
3     MSFT    -0.1
4      MCD     2.0
5     AAPL    -0.6
6     GOOG     1.6
7     MSFT    -0.4
8      MCD    -2.0
9     AAPL     0.6
10    GOOG     2.4
11    MSFT     0.8
12     MCD    -3.0
13    AAPL     0.9
14    GOOG     3.6
15    MSFT     1.1
16     MCD     5.0
17    AAPL    -1.5
18    GOOG    -4.0
19    MSFT    -1.3
>>>

Or you could use:

df.melt(var_name='ticker', value_name='return')
Ch3steR
  • 20,090
  • 4
  • 28
  • 58
U13-Forward
  • 69,221
  • 14
  • 89
  • 114
  • You do not need to use `.melt` if it is not the right method to solve my problem! – Eiffelbear Sep 12 '21 at 08:44
  • 1
    @Ch3steR Yeah, I wrote a cannonical about it [here](https://stackoverflow.com/questions/68961796/how-do-i-melt-a-pandas-dataframe) :P – U13-Forward Sep 12 '21 at 08:49
  • 1
    @U12-Forward Yeah it's covers melt pretty good. I'll add some more variations if I find interesting/frequently asked `melt` usage. – Ch3steR Sep 12 '21 at 08:52