Python: Create DataFrame with hierarchical columns and add columns

Question

I have a DataFrame with a multiindex as follows:

df:
                       open   close 
date         Symbol
2022-01-01   SPY       100    102
             TSLA      232    245
2022-01-02   SPY       103    100
             TSLA      222    220
             AAPL      143    147

I want to convert this into a DataFrame with hierarchical columns and add another column df['delta']=df['open']-df['close'] as follows:

df2:
           SPY           TSLA          AAPL
           Open  Close   Open  Close   Open  Close 
date
2022-01-01 100   102     232   245     nan   nan    nan
2022-01-02 103   100     222   220     143   147     -4

EDIT: After I get the shape in df2, I want to calculate a third column called delta to get the following:

df:
           SPY                 TSLA                AAPL
           Open  Close  delta  Open  Close  delta  Open  Close  delta
date
2022-01-01 100   102    -2     232   245    -13    nan   nan    nan
2022-01-02 103   100     3     222   220      2    143   147     -4

How can this be done? I tried pivoting the DataFrame but it did not work.

please post the df definition as a code

Naveed
– Naveed

2022-09-14 19:02:40 +00:00
Commented Sep 14, 2022 at 19:02 — Naveed
– Naveed, Commented Sep 14, 2022 at 19:02

Quang Hoang · Accepted Answer · 2022-09-14 19:02:50Z

3

You should be able to do with:

(df.assign(delta=lambda x: x['open'] - x['close'])
   .stack()
   .unstack(level=[1,2])
)

Output:

Symbol        SPY                TSLA                AAPL             
             open  close delta   open  close delta   open  close delta
date                                                                  
2022-01-01  100.0  102.0  -2.0  232.0  245.0 -13.0    NaN    NaN   NaN
2022-01-02  103.0  100.0   3.0  222.0  220.0   2.0  143.0  147.0  -4.0

answered Sep 14, 2022 at 19:02

Quang Hoang

151k11 gold badges64 silver badges86 bronze badges

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

1 Comment

MathMan 99 Over a year ago

That works perfectly, but I was not very clear the first time around and edited my question. So after I get the hierarchical DataFrame, I am forward filing my data, and then after that I want to calculate the delta column. How can your answer above be edited?

Collectives™ on Stack Overflow

Python: Create DataFrame with hierarchical columns and add columns

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related