Handling NaN Values in Pandas with Conditional Statement

Question

I'm working with some data where the customer postcode data is invalid. As a result I'm not able to map the CountryISOCode to their postcode resulting in a NaN. However, I have noticed that for all CountryISOCodes with NaN, the CurrencyCode can provide me with enough to fix the problem for now.

I've gone to various Stackoverflow articles but I cannot find the solution to my problem. I've tried...

def func(row):
    if row['CountryISOCode'] == np.nan & row['Currency'] == 'EUR':
        return 'IRE'
elif row['CountryISOCode'] == np.nan & row['Currency'] == 'GBP':
    return 'GBR'
else:
    return row['CountryISOCode']

df['CountryISOCode'] = df.apply(func, axis=1)

and some other methods but to no avail...

Below I have provided a replication of the data I'm working with

import pandas as pd
import numpy as np

data = [
    ['Steve', 'Invalid Postcode', 'GBP', np.nan ],
    ['Robyn', 'Invalid Postcode', 'EUR', np.nan],
    ['James', 'Valid Postcode', 'GBP', 'GBR'],
    ['Halo', 'Invalid Postcode', 'EUR', np.nan],
    ['Jesus', 'Valid Postcode', 'GBP', 'GBR']
    ]

df = pd.DataFrame(columns=["Name", "PostCode", "CurrencyCode", "CountryISOCode"], data=data)

Essentially if I was working with SQL my code would be as follows.

IF countryISOCode IS NULL 
    AND currency = ‘GBP’ 
THEN CountryISOCode =  ‘GBR’
ELSE
IF countryISOCode IS NULL 
    AND currency = ‘EUR 
THEN CountryISOCode =  ‘IRE’
ELSE countryISOCode 
END

Any ideas?

Answer 1

You can use fillna with a dictionary specifying mappings for when currency code is helpful:

cmap = {'GBP': 'GBR', 'EUR': 'IRE'}
df['CountryISOCode'] = df['CountryISOCode'].fillna(df['CurrencyCode'].map(cmap))

print(df)

    Name          PostCode CurrencyCode CountryISOCode
0  Steve  Invalid Postcode          GBP            GBR
1  Robyn  Invalid Postcode          EUR            IRE
2  James    Valid Postcode          GBP            GBR
3   Halo  Invalid Postcode          EUR            IRE
4  Jesus    Valid Postcode          GBP            GBR

Answer 2

You could use np.select for this, which allows you to choose from a list depending on the result of a list of conditions:

m1 = df.CountryISOCode.isna()
m2 = df.CurrencyCode.eq('GBP')
m3 = df.CurrencyCode.eq('EUR')
df.loc[:,'CountryISOCode'] = np.select([m1&m2, m1&m3], ['GBP','IRE'], 
                                       default=df.CountryISOCode)

 Name          PostCode CurrencyCode CountryISOCode
0  Steve  Invalid Postcode          GBP            GBP
1  Robyn  Invalid Postcode          EUR            IRE
2  James    Valid Postcode          GBP            GBR
3   Halo  Invalid Postcode          EUR            IRE
4  Jesus    Valid Postcode          GBP            GBR

Answer 3

I am adding this answer as it adds value to the original question. The reason the comparison statements weren't working is because np.nan == np.nan will not work. You can check for the identity of the NaN element but not equality. See in operator, float("NaN") and np.nan for more detail. With that said, this is how you can transform the original code to make it work as expected.

import pandas as pd                                                                                                                                    
import numpy as np

raw_data = [
    ['Steve', 'Invalid Postcode', 'GBP', np.nan ],
    ['Robyn', 'Invalid Postcode', 'EUR', np.nan],
    ['James', 'Valid Postcode', 'GBP', 'GBR'],
    ['Halo', 'Invalid Postcode', 'EUR', np.nan],
    ['Jesus', 'Valid Postcode', 'GBP', 'GBR']
    ]

df = pd.DataFrame(columns=["Name", "PostCode", "Currency", "CountryISOCode"], data=raw_data)

def func(row):
    if row['CountryISOCode'] is np.nan and row['Currency'] == 'EUR':
        return 'IRE'
    elif row['CountryISOCode'] is np.nan and row['Currency'] == 'GBP':
        return 'GBR'
    else:
        return row['CountryISOCode']

df['CountryISOCode'] = df.apply(func, axis=1)

print(df)

However, the other answers are great also.