Don't drop, just take the rows where EPS is not NA:
df = df[df['EPS'].notna()]
Don't drop, just take the rows where EPS is not NA:
df = df[df['EPS'].notna()]
This question is already resolved, but...
...also consider the solution suggested by Wouter in his original comment. The ability to handle missing data, including dropna(), is built into pandas explicitly. Aside from potentially improved performance over doing it manually, these functions also come with a variety of options which may be useful.
In [24]: df = pd.DataFrame(np.random.randn(10,3))
In [25]: df.iloc[::2,0] = np.nan; df.iloc[::4,1] = np.nan; df.iloc[::3,2] = np.nan;
In [26]: df
Out[26]:
0 1 2
0 NaN NaN NaN
1 2.677677 -1.466923 -0.750366
2 NaN 0.798002 -0.906038
3 0.672201 0.964789 NaN
4 NaN NaN 0.050742
5 -1.250970 0.030561 -2.678622
6 NaN 1.036043 NaN
7 0.049896 -0.308003 0.823295
8 NaN NaN 0.637482
9 -0.310130 0.078891 NaN
In [27]: df.dropna() #drop all rows that have any NaN values
Out[27]:
0 1 2
1 2.677677 -1.466923 -0.750366
5 -1.250970 0.030561 -2.678622
7 0.049896 -0.308003 0.823295
In [28]: df.dropna(how='all') #drop only if ALL columns are NaN
Out[28]:
0 1 2
1 2.677677 -1.466923 -0.750366
2 NaN 0.798002 -0.906038
3 0.672201 0.964789 NaN
4 NaN NaN 0.050742
5 -1.250970 0.030561 -2.678622
6 NaN 1.036043 NaN
7 0.049896 -0.308003 0.823295
8 NaN NaN 0.637482
9 -0.310130 0.078891 NaN
In [29]: df.dropna(thresh=2) #Drop row if it does not have at least two values that are **not** NaN
Out[29]:
0 1 2
1 2.677677 -1.466923 -0.750366
2 NaN 0.798002 -0.906038
3 0.672201 0.964789 NaN
5 -1.250970 0.030561 -2.678622
7 0.049896 -0.308003 0.823295
9 -0.310130 0.078891 NaN
In [30]: df.dropna(subset=[1]) #Drop only if NaN in specific column (as asked in the question)
Out[30]:
0 1 2
1 2.677677 -1.466923 -0.750366
2 NaN 0.798002 -0.906038
3 0.672201 0.964789 NaN
5 -1.250970 0.030561 -2.678622
6 NaN 1.036043 NaN
7 0.049896 -0.308003 0.823295
9 -0.310130 0.078891 NaN
There are also other options (See docs at http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.dropna.html), including dropping columns instead of rows.
Pretty handy!
Dataframe:
values_1 values_2
0 700.0 NaN
1 NaN 150.0
2 500.0 350.0
3 NaN 400.0
4 1200.0 5000.0
You can try this:
df = df.dropna()
df = df.reset_index(drop=True)
Outputs:
values_1 values_2
0 500.0 350.0
1 1200.0 5000.0
Given numpy array as
import numpy as np
input = np.array([[1,1,0,np.nan], [1,1,1,30], [1,0,1,np.nan]])
try
output = input[~np.isnan(input).any(axis=1)]
gives output as
[[ 1. 1. 1. 30.]]
The complete command is this:
df.dropna(axis = 0, how = 'all', inplace = True)
you must add inplace = True argument, if you want the dataframe to be actually updated. Alternatively, you would have to type:
df = df.dropna(axis = 0, how = 'all')
but that's less pythonic IMHO.
This should do it:
df.dropna(axis = 0, how = 'all')
Basically the way to do this is determine the number of cols, set the minimum number of non-nan values and drop the rows that don't meet this criteria:
df.dropna(thresh=(len(df) - 7))
See the docs
The optional thresh argument of df.dropna lets you give it the minimum number of non-NA values in order to keep the row.
df.dropna(thresh=df.shape[1]-7)
import numpy as np
a = np.array([
[1, 0, 0],
[0, np.nan, 0],
[0, 0, 0],
[np.nan, np.nan, np.nan],
[2, 3, 4]
])
mask = np.all(np.isnan(a) | np.equal(a, 0), axis=1)
a[~mask]
This will remove all rows which are all zeros, or all nans:
mask = np.all(np.isnan(arr), axis=1) | np.all(arr == 0, axis=1)
arr = arr[~mask]
And this will remove all rows which are all either zeros or nans:
mask = np.all(np.isnan(arr) | arr == 0, axis=1)
arr = arr[~mask]
I think what you're doing is taking one column from a DataFrame, removing all the NaNs from it, but then adding that column to the same DataFrame again - where any missing values from the index will be filled by NaNs again.
Do you want to remove that row from the entire DataFrame? If yes, try df.dropna(subset=["col1"])
Marko Knöbl explains it well, problem is that you assign the dropped Series back, you can also try
df = df[df['col1'].notna()]
Well if the dataset is not too large I would suggest using pandas to clean the data. So you would need to first do
Python2
python2 -m pip install pandas
Python3
python3 -m pip install pandas
If you already have anaconda installed you can skip the above step. Next you could go through an IDE (like jupyter) or through the shell type the following commands
import pandas as pd
df = pd.read_csv("filename", dtype=str)
#or if excel file
#df = pd.read_excel("filename", dtype=str)
df = df[pd.notnull(df['Gender'])]
Then you would want to save your result in a file with
df.to_csv("newfile");
If you are working on Python Dataframe, Please try this.
import pandas as pd
df = df[np.isfinite(df['Gender'])]
Or you can use notnull instead of isfinite.
import pandas as pd
df = df[pd.notnull(df['Gender'])]
>>> a = np.array([[1,2,3], [4,5,np.nan], [7,8,9]])
array([[ 1., 2., 3.],
[ 4., 5., nan],
[ 7., 8., 9.]])
>>> a[~np.isnan(a).any(axis=1)]
array([[ 1., 2., 3.],
[ 7., 8., 9.]])
and reassign this to a.
Explanation: np.isnan(a) returns a similar array with True where NaN, False elsewhere. .any(axis=1) reduces an m*n array to n with an logical or operation on the whole rows, ~ inverts True/False and a[ ] chooses just the rows from the original array, which have True within the brackets.
You can also use a masked array via np.ma.fix_invalid to create a mask and filter out "bad" values (such as NaN, inf).
arr = np.array([
[0, 1, np.inf],
[2.2, 3.3, 4.],
[np.nan, 5.5, 6],
[7.8, -np.inf, 9.9],
[10, 11, 12]
])
new_arr = arr[~np.ma.fix_invalid(arr).mask.any(axis=1)]
# array([[ 2.2, 3.3, 4. ],
# [10. , 11. , 12. ]])
If the array contains strings such as 'NA', then np.where may be useful to "mask" these values and then filter them out.
arr = np.array([
[0, 1, 'N/A'],
[2.2, 3.3, 4.],
[np.nan, 5.5, 6],
[7.8, 'NA', 9.9],
[10, 11, 12]
], dtype=object)
tmp = np.where(np.isin(arr, ['NA', 'N/A']), np.nan, arr).astype(float)
new_arr = tmp[~np.isnan(tmp).any(axis=1)]
# array([[ 2.2, 3.3, 4. ],
# [10. , 11. , 12. ]])