I have a pandas dataframe in which one of the columns has a few missing values.
The data frame consists of hundreds of rows, but in column 4, five of the values are ?.
I want to remove the rows in which values are ? in this column.
I have tried using something like
df = df[np.isfinite(df[:,4])]
To remove the rows for which the 4th column are equal to ?, you can select the data that are not equal to ?.
# Test data
df = DataFrame({
'col0': [0, 1, 2, 3, 4],
'col1': [0, 1, 2, 3, 4],
'col2': [0, 1, 2, 3, 4],
'col3': [0, 1, 2, 3, 4],
'col4': [0, 1, 2, '?', '?']})
df.loc[df.iloc[:, 4] != '?']
col0 col1 col2 col3 col4
0 0 0 0 0 0
1 1 1 1 1 1
2 2 2 2 2 2
If you want to eliminate the rows for which the 4th column contains ?, it's a bit trickier since you have to escape the ? character and provide a default value False for the boolean indexing to work and finally the boolean negation ~.
df.loc[~df.iloc[:,4].str.contains('\?', na = False)]
col0 col1 col2 col3 col4
0 0 0 0 0 0
1 1 1 1 1 1
2 2 2 2 2 2
If the column contains only numbers, you can also use the following method. Converting to numeric using the errors parameter coerce in order to produce NaN for values that cannot be converting. Then simply dropping the values using dropna.
df.iloc[] = pd.to_numeric(df.iloc[:,4], errors='coerce')
# Or if you want to apply the transformation to the entire DataFrame
# df = df.apply(pd.to_numeric, errors='coerce')
df.dropna(inplace=True)
col0 col1 col2 col3 col4
0 0 0 0 0 0.0
1 1 1 1 1 1.0
2 2 2 2 2 2.0
?(the string)? Do you want to remove the row if it contains any column as such?DataFrame.dropna()method achieve what you want to do?df[df.iloc[:,4].astype(str) != "?"]. That is, if column 4 means index 4. Otherwise, you may want to use index 3 for column 4.