Multiple Logical Comparisons In Pandas Df

If I have the following pandas df A B C D 1 2 3 4 2 2 3 4 and I want to add a new column to be 1, 2 or 3 depending on, (A > B) && (B > C) = 1 (A &l

Solution 1:

You can use numpy.select to structure your multiple conditions. The final parameter represents default value.

conditions = [(df.A > df.B) & (df.B > df.C),
              (df.A < df.B) & (df.B < df.C)]

values = [1, 2]

df['E'] = np.select(conditions, values, 3)

There are several alternatives: nested numpy.where, sequential pd.DataFrame.loc, pd.DataFrame.apply. The main benefit of this solution is readability while remaining vectorised.

Solution 2:

you can use apply on df with your two conditions such as:

df['E'] = df.apply(lambda x: 1 if x.A > x.B and x.B > x.C else 2 if x.A < x.B and x.B < x.C else 3, axis=1)

Solution 3:

This can also be solved using indexing and fillna.

df.loc[(df['A'] > df['B'])
  &(df['B'] > df['C']), 'New_Col'] = 1

df.loc[(df['A'] < df['B'])
  &(df['B'] < df['C']), 'New_Col'] = 2

df['New_Col'] = df['New_Col'].fillna(3)

The first chunk of code is read like so: locate where A > B and B > C, if both of these conditions are true, set the column 'New_Col' equal to 1. The second chunk can be interpreted in the same way. If both the first and second chunk do no return a 1 or 2, then they will appear as null. Use the fillna() function to fill those nulls with a 3.

This will produce the following dataframe: