MultiIndex column-wise from existing pandas dataframe columns

Question

I am trying to reindex my pandas dataframe to a column-wise MultiIndex. Most answers I've explored seem to answer only row wise. My current df looks as such:

  ticker calendardate     eps     price      ps       revenue
0   ABNB   2019-12-31   -2.59       NaN     NaN          4.80
1   ABNB   2020-12-31  -16.12    146.80  25.962          3.37
2   AMZN   2019-12-31   23.46   1847.84   3.266          2.80
3   AMZN   2020-12-31   42.64   3256.93   4.233          3.86

I want a MultiIndex based upon calendardate so that my output looks as such:

  ticker        eps           price           ps           revenue
            2019   2020    2019    2020   2019    2020    2019  2020
0   ABNB   -2.59 -16.12     NaN  146.80    NaN  25.962    4.80  3.37
1   AMZN   23.46  42.64 1847.84 3256.93  3.266   4.233    2.80  3.86

Any help would be appreciated. Thanks

Shubham Sharma · Accepted Answer · 2021-03-24 15:44:37Z

2

We can use str.split to split the column calenderdate around the delimiter - then use str[0] to select the year portion of splitted column, now set the index of dataframe to column ticker along with extracted year followed by unstack to reshape.

y = df['calendardate'].str.split('-', n=1).str[0]
df.drop('calendardate', 1).set_index(['ticker', y]).unstack()

If the dtype of column calendardate is datetime then we can instead use:

y = df['calendardate'].dt.year
df.drop('calendardate', 1).set_index(['ticker', y]).unstack()

               eps             price           ps              revenue      
calendardate   2019   2020     2019     2020   2019    2020    2019  2020
ticker                                                                   
ABNB          -2.59 -16.12      NaN   146.80    NaN  25.962     4.8  3.37
AMZN          23.46  42.64  1847.84  3256.93  3.266   4.233     2.8  3.86

edited Mar 24, 2021 at 15:44

answered Mar 24, 2021 at 15:37

Shubham Sharma

71.8k6 gold badges26 silver badges58 bronze badges

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

Collectives™ on Stack Overflow

MultiIndex column-wise from existing pandas dataframe columns

1 Answer 1

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Related