Pandas assign value to cell based on values of other cells in row

Question

Given the following data frame:

import pandas as pd
import numpy as np
DF = pd.DataFrame({'COL1': ['a','b','b'], 
                   'COL2' : [0,np.nan,1],})

DF

    COL1    COL2
0    a        0      
1    b       NaN     
2    b        1      

I want to be able to assign a new column COL3 that has a value of 2 for every row where COL1 is b and COL2 is not null.

The desired result is as follows:

    COL1    COL2    COL3
0    a        0      0
1    b       NaN     0
2    b        1      2

Thanks in advance!

Answer 1

Define a function to return your value based on other columns.

def value_handle (row):
    if row['COL1'] == 'b' and not pd.isnull(row['COL2']) :
        return 2
    else:
        return 0

Then call the new function when introducing the new column.

DF['COL3'] = DF.apply (lambda row: value_handle (row),axis=1)

Answer 2

This can be achieved using the apply method on the DataFrame. You'll need to pass in a function to apply to each row and set the axis to 1 to set it to the correct mode (apply for each row, instead of for each column).

Here's a working example:

def row_handler(row):
    if row['COL1'] == 'b' and not np.isnan(row['COL2']):
        return 2
    return 0

DF['COL3'] = DF.apply(row_handler, axis=1)

Which returns this:

>> print DF
  COL1  COL2  COL3
0    a     0     0
1    b   NaN     0
2    b     1     2

Answer 3

You can use numpy.where with isin and notnull:

DF['COL3'] = np.where((DF['COL1'].isin(['b'])) &(DF['COL2'].notnull()), 2, 0)
print DF 


  COL1  COL2  COL3
0    a     0     0
1    b   NaN     0
2    b     1     2