我正在处理一些客户邮政编码数据无效的数据。因此,我无法将国家ISOCode映射到他们的邮政编码,从而导致NaN。但是,我注意到,对于所有具有NaN的国家ISOCodes,货币代码可以为我提供足够的资源来解决问题。
我已经阅读了各种Stackoverflow文章,但我找不到解决问题的方法。我试过了...
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)
和其他一些方法,但无济于事...
下面我提供了我正在使用的数据的复制
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)
基本上,如果我使用 SQL,我的代码如下。
IF countryISOCode IS NULL
AND currency = ‘GBP’
THEN CountryISOCode = ‘GBR’
ELSE
IF countryISOCode IS NULL
AND currency = ‘EUR
THEN CountryISOCode = ‘IRE’
ELSE countryISOCode
END
有什么想法吗?
我添加这个答案,因为它为原始问题增加了价值。 比较语句不起作用的原因是np.nan == np.nan
不起作用。 您可以检查 NaN 元素的标识,但不能检查相等性。 有关更多详细信息,请参阅运算符 float("NaN") 和 np.nan。 话虽如此,这就是转换原始代码以使其按预期工作的方法。
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)
但是,其他答案也很棒。
您可以将fillna
与字典一起使用,该字典指定货币代码何时有用的映射:
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
您可以使用np.select
,它允许您根据条件列表的结果从列表中进行选择:
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
np.select()
用于多个条件和多个选择:
df['CountryISOCode']=np.select([(df.CurrencyCode=='GBP')&(df.CountryISOCode.isna()),
(df.CurrencyCode=='EUR')&df.CountryISOCode.isna()],['GBR','IRE'],
default=df.CountryISOCode)
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
虽然另一个使用 np.select
的答案有效,但我个人最喜欢的是使用 mask
:
df['CountryISOCode'] = df['CountryISOCode']
.mask(df['CountryISOCode'].isna() & df['Currency'].eq('GBP'), 'GBR')
.mask(df['CountryISOCode'].isna() & df['Currency'].eq('EUR'), 'IRE')