how to select rows with float64 nan?

user_10400141 0
Viewed 2392

I have a dataframe from excel which has several NaNs in rows.I`d like to replace rows whose values are all NaNs by another baseline row.

The original dataframe is like this:

                    Country Name  Years  tariff1_1  tariff1_2  tariff1_3  
830                 Hungary       2004   9.540313   6.287314  13.098201   
831                 Hungary       2005   9.540789   6.281724  13.124401 
832                 Hungary       2006   NaN        NaN       NaN 
833                 Hungary       2007   NaN        NaN       NaN 
834                 eu            2005   8.55       5.7       11.4
835                 eu            2006   8.46       5.9       11.6
836                 eu            2007   8.56       5.3       11.9

so if the tariffs for Hungary of a specific year are all NaNs,this row should be replaced by the eu data, according to the exact year.

The ideal result is :

                    Country Name  Years  tariff1_1  tariff1_2  tariff1_3  
830                 Hungary       2004   9.540313   6.287314  13.098201   
831                 Hungary       2005   9.540789   6.281724  13.124401 
832                 Hungary       2006   8.46       5.9       11.6 
833                 Hungary       2007   8.56       5.3       11.9
834                 eu            2005   8.55       5.7       11.4
835                 eu            2006   8.46       5.9       11.6
836                 eu            2007   8.56       5.3       11.9

I looked into the type of the NaN in a specific row ('Hungary',2006) and it turns to be 'float64'.So it turns out as ufunc 'isnan' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe'' after I use np.isnan.

So I adopted math.isnan. But it doesn`t seem to detect the NaN in my test row:

test=df.loc[(df['Country Name'] == 'Hungary') & (df['Years']== 2006)]

test.iloc[:,4]
Out[293]: 
832   NaN
Name: tariff1_3, dtype: float64

math.isnan(any(test))
Out[294]:False

np.isnan(any(test))
Out[295]:ufunc 'isnan' not supported for the input types, and the inputs could not be safely coerced to any supported types according to the casting rule ''safe''

Here are my original lines.

 Eu=['Austria','Belgium','Curacao','Denmark','Finland','France','Germany']

 for country in Eu:
        for year in range(2001,2012)
            if math.isnan(all(df.loc[(df['Country Name'] == country) & (df['Years'] == year)])):
                df.loc[(df['Country Name'] == country) & (df['Years'] == year)]=df.loc[(df['Country Name'] == 'eu') & (df['Years'] == year)]

Thanks !

pandas dataframe python-3.x python
2 Answers

If need convert only NaNs rows:

print (df)
    Country Name  Years  tariff1_1  tariff1_2  tariff1_3
830      Hungary   2004   9.540313   6.287314  13.098201
831      Hungary   2005        NaN   6.281724  13.124401
832      Hungary   2006        NaN        NaN        NaN
833      Hungary   2007        NaN        NaN        NaN
834           eu   2005   8.550000   5.700000  11.400000
835           eu   2006   8.460000   5.900000  11.600000
836           eu   2007   8.560000   5.300000  11.900000

Eu=['Austria','Belgium','Curacao','Denmark','Finland','France','Germany','Hungary']

#all columns without specified in list
cols = df.columns.difference(['Country Name','Years'])
#eu DataFrame for repalce missing rows
eu = df[df['Country Name'] == 'eu'].drop('Country Name', 1).set_index('Years')
print (eu)
       tariff1_1  tariff1_2  tariff1_3
Years                                 
2005        8.55        5.7       11.4
2006        8.46        5.9       11.6
2007        8.56        5.3       11.9

#filter only Eu countries and all missing values with columns cols 
mask = df['Country Name'].isin(Eu) & df[cols].isnull().all(axis=1)

#for filtered rows replace missing rows by fillna 
df.loc[mask, cols] = pd.DataFrame(df[mask].set_index('Years')
                                          .drop('Country Name', 1).fillna(eu).values,
                                  index=df.index[mask], columns=cols)
print (df)
    Country Name  Years  tariff1_1  tariff1_2  tariff1_3
830      Hungary   2004   9.540313   6.287314  13.098201
831      Hungary   2005        NaN   6.281724  13.124401
832      Hungary   2006   8.460000   5.900000  11.600000
833      Hungary   2007   8.560000   5.300000  11.900000
834           eu   2005   8.550000   5.700000  11.400000
835           eu   2006   8.460000   5.900000  11.600000
836           eu   2007   8.560000   5.300000  11.900000
user_29010020

You can try :

df.isnull().values.any()

For your case:

test.isnull().values.any()
user_57048630
Related