0

I have a dataframe that I am trying to filter in many different ways. I need to assign the various columns and operators as variables being called from a list of combinations. I cannot find a way to insert the operator variables into the dataframe filter.

import itertools 
import operator
import pandas as pd

df = pd.DataFrame(columns = ['Open','High','Low','Close']
rollingList = [5,10,20,30,60,90,180,365]

for i in rollingList:
    df[str(i)+' rolling day high'] = df['High'].rolling(i).max()
    df[str(i)+' rolling day low'] = df['Low'].rolling(i).min()
    
columnListRolling = [i for i in df.columns if 'day high' in i]

possibleCols1 = ['Open','High','Low','Close']
opp1 = ['>','<']
possibleCols2 = ['Open','High','Low','Close']
possibleCols3 = ['Open','High','Low','Close']
opp4 = ['>','<']

combo = list(itertools.product(possibleCols1,opp1,possibleCols2,possibleCols3,opp4,columnListRolling))

# instead of  ['>','<'], I have also tried replacing with operator.gt/operator.lt

#a- always a column
#b-always either < or >
#c- always a column
#d- always a column
#e-always either < or >
#f- always a column

for a,b,c,d,e,f in combo:
  filter = df[(df[a] b df[c]) & (df[d] e df[f].shift(1))]
#I have also tried using .query with no success 
  filter2 = df.query('(a+b+c)&(d+e+f)')
# I am then iterating through each index of the filter df
notarobot
  • 27
  • 3
  • It is easier to help you if you share a sample of your input data and your expected output. – Michael Szczesny Dec 28 '20 at 04:42
  • Please read [mre] then provide one. [How to make good reproducible pandas examples](https://stackoverflow.com/questions/20109391/how-to-make-good-reproducible-pandas-examples) – wwii Dec 28 '20 at 04:45

2 Answers2

2

You can do this with query():

filter2 = df.query(f'(`{a}`{b}`{c}`) & (`{d}`{e}`{f}`)')
Nick ODell
  • 15,465
  • 3
  • 32
  • 66
  • what function does f serve? Also I understand that each of my columns need backticks `, but why does each need to be encased in a {}? Thank you so much! – notarobot Dec 28 '20 at 05:02
  • @notarobot - https://stackoverflow.com/questions/43123408/f-strings-vs-str-format, [https://docs.python.org/3/reference/lexical_analysis.html#formatted-string-literals](https://docs.python.org/3/reference/lexical_analysis.html#formatted-string-literals) – wwii Dec 28 '20 at 05:03
1

With

opp1=[operator.gt,operator.lt]
opp4=[operator.gt,operator.lt]

Use

filter = b(df[a],df[c]) & (e(df[d],df[f])).shift(1)
wwii
  • 23,232
  • 7
  • 37
  • 77