How to filter columns in a multindex dataframe (pandas)

Question

I have the below dataframe:

    WBA                                   ...                  HD                    
                                Open       High        Low      Close  ...      l-pc       h-l      h-pc      l-pc
Datetime                                                               ...                                        
2020-06-08 09:30:00-04:00  45.490002  46.090000  45.490002  46.049999  ...       NaN  2.100006       NaN       NaN
2020-06-08 09:35:00-04:00  46.070000  46.330002  46.040001  46.330002  ...  0.009998  1.119904  0.402496  0.717407
2020-06-08 09:40:00-04:00  46.330002  46.660000  46.240002  46.610001  ...  0.090000  0.874893  0.359894  0.514999
2020-06-08 09:45:00-04:00  46.624100  46.950001  46.624100  46.880001  ...  0.014099  0.639999  0.349991  0.290009
2020-06-08 09:50:00-04:00  46.880001  46.990002  46.820000  46.919998  ...  0.060001  0.490005  0.169998  0.320007

this dataframe was obtained using the below code:

import yfinance as yf
import pandas as pd
import datetime as dt
end=dt.datetime.today()
start=end-dt.timedelta(59)
tickers=['WBA', 'HD']
ohlcv={}
df=pd.DataFrame
df = yf.download(tickers,group_by=tickers,start=start,end=end,interval='5m')
for i in tickers:
  df[i,"h-l"]=abs(df[i]['High']-df[i]['Low'])
  df[i,'h-pc']=abs (df[i]["High"]-df[i]['Adj Close'].shift(1))
  df[i,'l-pc']=abs(df[i]["Low"]-df[i]['Adj Close'].shift(1))

I am trying to apply this function for all the tickers mentioned in the "tickers" list:

  df['tr']=dff[['h-l','h-pc','l-pc']].max(axis=1)
  df['atr']=df['tr'].rolling(window=n, min_periods=n).mean()

for the tickers I need to find the "tr" and then using the tr i have to find the "atr" I am not able get the"tr"

Does this answer your question? [How to add a column in multilevel Dataframe using pandas and yfinance?](https://stackoverflow.com/questions/63262472/how-to-add-a-column-in-multilevel-dataframe-using-pandas-and-yfinance) — Trenton McKinney, Aug 06 '20 at 19:19

score 0 · Answer 1 · answered Aug 05 '20 at 12:07

Be systematic about accessing columns through tuples and it all just works.

import yfinance as yf
import pandas as pd
import datetime as dt
end=dt.datetime.today()
start=end-dt.timedelta(59)
tickers=['WBA', 'HD']
ohlcv={}

# df = yf.download(tickers,group_by=tickers,start=start,end=end,interval='5m')
dfc = df.copy()
for t in tickers:
    dfc[(t,"h-l")] = abs(dfc.loc[:,(t,'High')] - dfc.loc[:,(t,'Low')])
    dfc[(t,"h-pc")] = abs(dfc.loc[:,(t,'High')] - dfc.loc[:,(t,'Adj Close')].shift(1))
    dfc[(t,"l-pc")] = abs(dfc.loc[:,(t,'Low')] - dfc.loc[:,(t,'Adj Close')].shift(1))

# access all the new columns through tuples e.g ("WBA","h-l") ...
dfc["tr"] = dfc[[(t, c) for t in tickers for c in ['h-l','h-pc','l-pc']]].max(axis=1)

n=5
dfc["atr"] = dfc['tr'].rolling(window=n, min_periods=n).mean()

when I am trying to run it i am getting this error: File "pandas/_libs/index.pyx", line 704, in pandas._libs.index.BaseMultiIndexCodesEngine.get_loc KeyError: ('WBA', 'High') — raptorzee, Aug 06 '20 at 11:24
`print(f"pandas: {pd.__version__}, yfinance:{yf.__version__}")` `pandas: 1.1.0, yfinance:0.1.54`. what versions of these libraries are you running? — Rob Raymond, Aug 06 '20 at 15:10

How to filter columns in a multindex dataframe (pandas)

1 Answers1