Filling column with condition

Question

I have a dataframe with 2 columns:

          Col1          Col2
1          NaN         Someval1
2           Y          Someval2
3           N          Someval3
4          NaN           NaN
5          NaN         Someval4

I would like to fill NaN with the conditons:

If Col1 has NaN and Col2 has a Someval1 that is in list 1 then fillna with Y
If Col1 has NaN and Col2 has a Someval4 that is in list 2 then fillna with N
If Col1 has NaN and Col2 has a NaN that is in list 2 then fillna with N

Any suggestions ? (don't know if it's possible)

Many Thanks !

Answer 1

I think you need mask, for conditions isnull and isin:

L1 = ['Someval1','Someval8']
L2 = ['Someval4','Someval9', np.nan]
m1 = df['Col1'].isnull()
m2 = df['Col2'].isin(L1)
m3 = df['Col2'].isin(L2)

df['Col1'] = df['Col1'].mask(m1 & m2, 'Y')
df['Col1'] = df['Col1'].mask(m1 & m3, 'N')

print (df)
  Col1      Col2
1    Y  Someval1
2    Y  Someval2
3    N  Someval3
4    N       NaN
5    N  Someval4

Another solution with numpy.where:

df['Col1'] = np.where(m1 & m2, 'Y',
             np.where(m1 & m3, 'N', df['Col1']))

print (df)
  Col1      Col2
1    Y  Someval1
2    Y  Someval2
3    N  Someval3
4    N       NaN
5    N  Someval4

---

Another solution with one condition and fillna:

L1 = ['Someval1','Someval8']
L2 = ['Someval4','Someval9', np.nan]

df['Col1'] = df['Col1'].mask(df['Col2'].isin(L1), df['Col1'].fillna('Y'))
df['Col1'] = df['Col1'].mask(df['Col2'].isin(L2), df['Col1'].fillna('N'))
print (df)
  Col1      Col2
1    Y  Someval1
2    Y  Someval2
3    N  Someval3
4    N       NaN
5    N  Someval4