Building a pandas condition query using a loop

Question

I am having an object filters which gives me conditions to be applied to a dataframe as shown below:

"filters": [
    {
        "dimension" : "dimension1",
        "operator" : "IN",
        "value": ["value1", "value2", "value3"],
        "conjunction": None
    },

    {
        "dimension" : "dimension2",
        "operator" : "NOT IN",
        "value": ["value1", "value2", "value3"],
        "conjunction": "OR"
    },

    {
        "dimension" : "dimension3",
        "operator" : ">=",
        "value": ["value1", "value2", "value3"],
        "conjunction": None
    },

    {
        "dimension" : "dimension4",
        "operator" : "==",
        "value": ["value1", "value2", "value3"],
        "conjunction": "AND"
    },

    {
        "dimension" : "dimension5",
        "operator" : "<=",
        "value": ["value1", "value2", "value3"],
        "conjunction": None
    },

    {
        "dimension" : "dimension6",
        "operator" : ">",
        "value": ["value1", "value2", "value3"],
        "conjunction": "OR"
    },
    ]

Here is the grammar by which I used to build the SQL Query:

for eachFilter in filters:
        conditionString = ""
        dimension = eachFilter["dimension"]
        operator = eachFilter["dimension"]
        value = eachFilter["dimension"]
        conjunction = eachFilter["dimension"]

        if len(eachFilter["value"]) == 1:
            value = value[0]
            if operator != "IN" or operator != "NOT IN":
                conditionString += f' {dimension} {operator} {value} {conjunction}'
            else:
                conditionString += f' {dimension} {operator} {value} ({conjunction})'

        else:

            value = ", ".join(value)
            if operator != "IN" or operator != "NOT IN":
                conditionString += f' {dimension} {operator} {value} {conjunction}'
            else:
                conditionString += f' {dimension} {operator} {value} ({conjunction})'

But when it comes to pandas I can't use such queries so wanted to know if there's a good way to loop these filter conditions based on the conditions given in filters. Note that these are the only conditions I will be operating through.

In case of None as conjunction it should have the conjunction as "AND".

Please provide sample data in a [reproducible way](https://stackoverflow.com/questions/20109391). Otherwise people won't be able to test. Please also exemplify the expected output explicitly. — Bill Huang, Nov 23 '20 at 10:57

score 0 · Answer 1 · answered Nov 23 '20 at 13:05

I have used eval function to create nested eval statements for pandas conditional filtering and then used it at the end to evaluate them all as shown below:

for eachFilter in filtersArray:
            valueString = ""
            values = eachFilter[self.queryBuilderMap["FILTERS_MAP"]["VALUE"]]
            dimension = eachFilter[self.queryBuilderMap["FILTERS_MAP"]["DIMENSION"]]
            conjunction = self.defineConjunction(eachFilter[self.queryBuilderMap["FILTERS_MAP"]["CONJUNCTION"]])

            if filterCheck==len(filtersArray) - 1:
                conjunction = ""



            if (eachFilter[self.queryBuilderMap["FILTERS_MAP"]["OPERATOR"]]).lower() == "in":



                for eachValue in values:
                    valueString += f"(df['{dimension}'] == {eachValue}) {conjunction} "
                
                evalString += valueString

            elif (eachFilter[self.queryBuilderMap["FILTERS_MAP"]["OPERATOR"]]).lower() == "not in":


                for eachValue in values:
                    valueString += f"(df['{dimension}'] != {eachValue}) {conjunction} "
                
                evalString += valueString

            else:


                for eachValue in values:
                    valueString += f"(df['{dimension}'] {eachFilter[self.queryBuilderMap['FILTERS_MAP']['OPERATOR']]} {eachValue}) {conjunction} "
                
                evalString += valueString

            filterCheck += 1
            print(valueString)
        #print(evalString)
        df = eval(f'df.loc[{evalString}]')
        #print(df.keys())

        
        return df

Here filtermap is the dictionary key value pair:

"FILTERS_MAP": {
        "DIMENSION": "dimension",
        "OPERATOR": "operator",
        "VALUE": "value",
        "CONJUNCTION": "conjunction",
        "WRAPPER": "wrapper"
    }

Building a pandas condition query using a loop

1 Answers1