Pythonic way to apply a filter to a table

It is a rather complex question. I would start by pre-processing the filter table to have only one value per field by duplicating rows containing '|'. In order to limit the number of useless rows, I would first replace anything containing a '#' and other values with a single '#'.

Once this is done, it is possible to select the rows from the business table with a merge, provided merging on the columns containing no sharp.

Code could be:

# store the original column names
cols = filter.columns
# remove any alternate value if a # is already present:
tosimp = pd.DataFrame({col: filter[col].str.contains('#')&
                       filter[col].str.contains('\|')
                       for col in cols})

# add a column to store in a (hashable) tuple the columns with no '#'
filter['wild'] = filter.apply(lambda x: tuple(col for col in cols
                                             if x[col] != '#'), axis=1)

# now explode the fields containing a '|'
tosimp = pd.DataFrame({col: filter[col].str.contains('\|')
                       for col in filter.columns})

# again, store in a new column the columns containing a '|'
tosimp['wild'] = filter.apply(lambda x: tuple(col for col in cols
                                             if '|' in filter.loc[x.name, col]),
                              axis=1)

# compute a new filter table with one single value per field (or #)
# by grouping on tosimp['wild']
dfl = [filter[tosimp['wild'].astype(str)=='()']]
for k, df in filter[tosimp['wild'].astype(str)!='()'].groupby(tosimp['wild']):
    for ix, row in df.iterrows():
        tmp = pd.MultiIndex.from_product([df.loc[ix, col].split('|')
                                          for col in k], names=k).to_frame(None)
        l = len(tmp)
        dfl.append(pd.DataFrame({col: tmp[col]
                                 if col in k else [row[col]] * l
                                 for col in filter.columns}))

filter2 = pd.concat(dfl)

# Ok, we can now use that new filter table to filter the business table
result = pd.concat([data.merge(df, on=k, suffixes=('', '_y'),
                               right_index=True)[cols]
                    for k, df in filter2.groupby('wild')]).sort_index()

Limits:

• the pre-processing iterates on group by dataframes and uses a iterrows call: it can take some time on a large filter table
• the current algo does not handle at all a line containing '#' in all of its fields. If it is a possible use case, it must be searched before any other processing. Anyway in that case any row from the business table will be kept.

Explaination of the pd.concat(... line:

• [... for k, df in filter2.groupby('wild')]: split the filter dataframe in sub-dataframes each having a different wild values, that is a different set of non # fields
• data.merge(df, on=k, suffixes=('', '_y'), right_index=True): merge each sub filter dataframe with the data dataframe on the non # fields, that is select the rows from the data dataframe matching one of these filter rows. Keep the original index of the data dataframe
• ...[cols] keep only the relevant fields
• pd.concat(...) concat all those partial dataframes
• ... .sort_index() sort the concatenated dataframe according to its index which is by construction the index of the original data dataframe

My understanding of your problem is that you want the all the first matches for business,A/L with a Company that is specified (or any if # is used) in the corresponding filter.

I'm supposing that your intended result is a dataframe with just the first row of data. When your filter gets large, you can speed things up by using a join operation on the filter and keep only the first result.

# Split on | so that every option is represented in a single row
filter0 = filter.set_index(['business','A/L']).Company.str.split('|',expand=True).stack().reset_index().drop('level_2',axis=1).rename(columns={0:'Company'})

# The set of *all* rows in data which are caught by filters with a Company specification
r1 = data.merge(filter0[filter0.Company != '#'])

# The set of *all* rows in data which are caught by filters allowing for *any* Company
r2 = data.merge(filter0[filter0.Company == '#'].drop('Company', axis=1))

# r1 and r2 are not necessarily disjoint, and each one may have multiple rows that pass one filter
# Take the union, sort on the index to preserve the original ordering,
# then finally drop duplicates of business+A/L, keeping only the first entry
pd.concat([r1,r2]).drop_duplicates(subset=['business','A/L'], keep='first')

With regard to your case in handling multiple columns on the filter: A single row in your filter would essentially say something along the lines of,

"I want field1=foo AND field2=bar AND field3=baz1 OR field3=baz2 AND field4=qux1 OR field4=qux2."

The main idea is to expand this into a multiple rows composed of only AND conditinals, so in this case it would be to turn it into four rows of

field1=foo AND field2=bar AND field3=baz1 AND field4=qux1

field1=foo AND field2=bar AND field3=baz1 AND field4=qux2

field1=foo AND field2=bar AND field3=baz2 AND field4=qux1

field1=foo AND field2=bar AND field3=baz2 AND field4=qux2

In other words, use .split and .stack multiple times, once for each column with an OR condition. This may be slightly inefficient (you might get better speed and code readability using itertools.product somewhere), but your bottleneck is usually in the join operation, so this isn't too much of a worry as far as speed is concerned.