I want to remove rows from my dataframe that contains a string value for a float dtype column. For example if I have an amount field, I want to remove all rows in the dataframe that contains a value of "NA" in the amount field.
So far I've tried the following -
to_drop = ['NA']
data = data[~data['gross'].isin(to_drop)]
and
data = data[data.gross.str != 'NA']
I get "an only use .str accessor with string values, which use np.object_ dtype in pandas".
What is the correct way to do this ?
If NA is missing value (NaN) need notnull or dropna with specify columns for check NaNs:
data = pd.DataFrame({'gross':[np.nan,3,5],
'a':[2,3,4]})
print (data)
a gross
0 2 NaN
1 3 3.0
2 4 5.0
data1 = data[data.gross.notnull()]
print (data1)
a gross
1 3 3.0
2 4 5.0
data1 = data.dropna(subset=['gross'])
print (data1)
a gross
1 3 3.0
2 4 5.0
Or if mixed values - numeric with strings first cast all values to str or compare numpy array created by values:
data = pd.DataFrame({'gross':['NA',3,5,'NA'],
'a':[2,3,4,8]})
print (data)
a gross
0 2 NA
1 3 3
2 4 5
3 8 NA
data2 = data[data.gross.astype(str) != 'NA']
print (data2)
a gross
1 3 3
2 4 5
data2 = data[data.gross.values != 'NA']
print (data2)
a gross
1 3 3
2 4 5
IIUC:
data['gross'] = data.gross.replace('NA',np.nan)
data = data.dropna()
Or
data[~data.gross.replace('NA',np.nan).isnull()]
Replace string 'NA' with NaN then use dropna axis=1 to drop those rows.
