Search code examples
pythonpandasif-statementhierarchycalculated-columns

How can I return the value of a column in a new column based on conditions with python


I have a dataframe with three columns

 a b c
[1,0,2] 
[0,3,2] 
[0,0,2] 

and need to create a fourth column based on a hierarchy as follows:

If column a has value then column d = column a

if column a has no value but b has then column d = column b

if column a and b have no value but c has then column d = column c

 a b c d
[1,0,2,1] 
[0,3,2,3] 
[0,0,2,2] 

I'm quite the beginner at python and have no clue where to start.

Edit: I have tried the following but they all will not return a value in column d if column a is empty or None

df['d'] = df['a']
df.loc[df['a'] == 0, 'd'] = df['b']
df.loc[~df['a'].astype('bool') &  ~df['b'].astype('bool'), 'd'] = df['c']
df['d'] = df['a']
df.loc[df['a'] == None, 'd'] = df['b']
df.loc[~df['a'].astype('bool') &  ~df['b'].astype('bool'), 'd'] = df['c']
df['d']=np.where(df.a!=0, df.a,\
                                          np.where(df.b!=0,\
                                                   df.b, df.c)

Solution

  • A simple one-liner would be,

    df['d'] = df.replace(0, np.nan).bfill(axis=1)['a'].astype(int)
    

    Step by step visualization

    Convert no value to NaN

         a    b  c
    0  1.0  NaN  2
    1  NaN  3.0  2
    2  NaN  NaN  2
    

    Now backward fill the values along rows

         a    b    c
    0  1.0  2.0  2.0
    1  3.0  3.0  2.0
    2  2.0  2.0  2.0
    

    Now select the required column, i.e 'a' and create a new column 'd'

    Output

       a  b  c  d
    0  1  0  2  1
    1  0  3  2  3
    2  0  0  2  2