Dataframe filtering with multiple conditions on different columns

Question

Let's say we have the following dataframe:

data = {'Item':['1', '2', '3', '4', '5'], 
'A':[142, 11, 50, 60, 12], 
'B':[55, 65, 130, 14, 69],
'C':[68, -18, 65, 16, 17],
'D':[60, 0, 150, 170, 130],
'E':[230, 200, 5, 10, 160]}

df = pd.DataFrame(data)

representing different items and the corresponding values related to some of their parameters (e.g. length, width, and so on). However, not all the reported values are acceptable, in fact each item has a different range of allowed values:

A and B go from -100 to +100

C goes from -70 to +70

D and E go from +100 to +300

So, as you can see, some items show values outside limits for just one parameter (e.g. item 2 is outside for parameter D), while others are outside for more than one parameter (e.g. item 1 is outside for both parameters A and D).

I need to analyze these data and get a table reporting: how many items are outside for just one parameter and the name of this parameter how many items are outside for more than one parameter and the name of those parameter To be clearer: I need a simple simple way to know how many items are failed and for which parameters. For example: four items out of five are failed, and 2 of them (item #1 and item#3) for two parameters (item #1 for A and D, item #3 for B and E), while items #2 and #4 are out for one parameter (item #2 for D, item #4 for E)

I have tried to define the following masks:

df_mask_1 = abs(df['A'])>=100
df_mask_2 = abs(df['B'])>=100
df_mask_3 = abs(df['C'])>=70
df_mask_4 = ((df['D']<=110) | (df['D']>=800))
df_mask_5 = ((df['E']<=110) | (df['E']>=800))

to get the filtered dataframe:

filtered_df = df[df_mask_1 & df_mask_2 & df_mask_3 & df_mask_4 & df_mask_5]

but what I obtain is just an empty dataframe. I have also tried with

filtered_df = df.loc[(df_mask_1) & (df_mask_2) & (df_mask_3) & (df_mask_4) & (df_mask_5)]

but the result does not change. Any suggestion?

Answer 1

Use a condition list then flat your dataframe with melt then keep rows where condition is False (~x) then unpivot your dataframe with groupby_apply:

condlist = [df['A'].between(-100, 100),
            df['B'].between(-100, 100),
            df['C'].between(-70, 70),
            df['D'].between(100, 300),
            df['E'].between(100, 300)]

df['Fail'] = pd.concat(condlist, axis=1).melt(ignore_index=False) \
               .loc[lambda x: ~x['value']].groupby(level=0)['variable'].apply(list)


# Output:
  Item    A    B   C    D    E    Fail
0    1  142   55  68   60  230  [A, D]
1    2   11   65 -18    0  200     [D]
2    3   50  130  65  150    5  [B, E]
3    4   60   14  16  170   10     [E]
4    5   12   69  17  130  160     NaN

Note: if your dataframe is large and you only need to display failed items, use : df[df['Fail'].notna()] to filter out your dataframe.

Note 2: variable and value are the default column names when you melt a dataframe.

Answer 2

The result from the example code is what it should be. The code filtered_df = df[df_mask_1 & df_mask_2 & df_mask_3 & df_mask_4 & df_mask_5] applies all masks to the data, hence df_mask_1 and df_mask_2 alone would result in empty table (as there are no items where A and B are both over 100).

If you are looking to create a table with information on how many items have parameters outside limits, I'd suggest doing according to Corralien's answer and summing per row to get the amount of limits broken.

Taking Corralien's answer, then

a = pd.concat([df['A'].between(-100, 100),
           df['B'].between(-100, 100),
           df['C'].between(-70, 70),
           df['D'].between(100, 300),
           df['E'].between(100, 300)], axis=1)

b = a.sum(axis=1)

where b gives per item the amount of limits "broken"

0    3
1    4
2    3
3    4
4    5

Answer 3

Another approach is to use the pandas.DataFrame.agg function like so:

import pandas as pd

# Defining a limit checker function
def limit_check(values, limit):
    return (abs(values) >= limit).sum()

# Applying different limits checks to columns
res = df.agg({
    'A': lambda x: limit_check(x, 100),
    'B': lambda x: limit_check(x, 100),
    'C': lambda x: limit_check(x, 70),
    'D': lambda x: limit_check(x, 800),
    'E': lambda x: limit_check(x, 800),
})
print(res)

For the sample data you provided it will result in

A    1
B    1
C    0
D    0
E    0
dtype: int64

Answer 4

Using the answer by Corralien and taking advantage of what's written by tiitinha, and considering the possibility to have some NaN values, here is how I put it all together:

df.replace([np.nan],  np.inf,inplace=True)

condlist = [df['A'].between(-100, 100) | df['A'] == np.inf,
            df['B'].between(-100, 100) | df['B'] == np.inf,
            df['C'].between(-70, 70) | df['C'] == np.inf,
            df['D'].between(100, 300) | df['D'] == np.inf,
            df['E'].between(100, 300) | df['E'] == np.inf]

To get the total number of failed parameters for each item:

bool_df = ~pd.concat(condlist, axis=1).astype('bool')

df['#Fails'] = bool_df.sum(axis=1)

To know who are the parameters out of limits, for each item:

df['Fail'] = pd.concat(condlist, axis=1).melt(ignore_index=False) \
               .loc[lambda x: ~x['value']].groupby(level=0)['variable'].apply(list)

In this way I get two columns with the wanted results.