2

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 < B) && (B < C) = 2
Else = 3

whats the best way to do this?

jpp
  • 159,742
  • 34
  • 281
  • 339
Terry
  • 1,621
  • 4
  • 25
  • 45
  • 4
    With some code... where is yours? You will need to build some conditional masks and set values using either loc or numpy.select. – cs95 May 02 '18 at 17:14
  • Possible duplicate of [Pandas conditional creation of a series/dataframe column](https://stackoverflow.com/questions/19913659/pandas-conditional-creation-of-a-series-dataframe-column) – Georgy May 02 '18 at 17:33

3 Answers3

5

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.

jpp
  • 159,742
  • 34
  • 281
  • 339
1

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)
Ben.T
  • 29,160
  • 6
  • 32
  • 54
1

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:

enter image description here

Brian
  • 2,163
  • 1
  • 14
  • 26