get non numerical rows in a column pandas python
Use boolean indexing
with mask created by to_numeric
+ isnull
Note: This solution does not find or filter numbers saved as strings: like '1' or '22'
print (pd.to_numeric(df['num'], errors='coerce'))
0 -1.48
1 1.70
2 -6.18
3 0.25
4 NaN
5 0.25
Name: num, dtype: float64
print (pd.to_numeric(df['num'], errors='coerce').isnull())
0 False
1 False
2 False
3 False
4 True
5 False
Name: num, dtype: bool
print (df[pd.to_numeric(df['num'], errors='coerce').isnull()])
N-D num unit
4 Q5 sum(d) UD
Another solution with isinstance
and apply
:
print (df[df['num'].apply(lambda x: isinstance(x, str))])
N-D num unit
4 Q5 sum(d) UD
Old topic, but if the numbers have been converted to 'str', type(x) == str is not working.
Instead, it's better to use isnumeric() or isdigit().
df = df[df['num'].apply(lambda x: not x.isnumeric())]
I tested all three approaches on my own dataframe with 200k+ rows, assuming numbers have been converted to 'str' by pd.read_csv().
def f1():
df[pd.to_numeric(df['num'], errors='coerce').isnull()]
def f2():
df[~df.num.str.match('^\-?(\d*\.?\d+|\d+\.?\d*)$')]
def f3():
df[df['num'].apply(lambda x: not x.isnumeric())]
I got following execution times by running each function 10 times.
timeit.timeit(f1, number=10)
1.04128568888882
timeit.timeit(f2, number=10)
1.959099448888992
timeit.timeit(f3, number=10)
0.48741375999998127
Conculsion: fastest method is isnumeric(), slowest is regular expression method.
=========================================
Edit: As @set92 commented, isnumeric() works for integer only. So the fastest applicable function is pd.to_numeric() to have a universal solutions works for any type of numerical values.
It is possible to define a isfloat() function in python; but it will be slower than internal functions, especially for big DataFrames.
tmp=['4.0','4','4.5','1','test']*200000
df=pd.DataFrame(data=tmp,columns=['num'])
def f1():
df[pd.to_numeric(df['num'], errors='coerce').isnull()]
def f2():
df[df['num'].apply(lambda x: not isfloat(x))]
def f3():
df[~df.num.str.match('^\-?(\d*\.?\d+|\d+\.?\d*)$')]
print('to_numeric:',timeit.timeit(f1, number=10))
print('isfloat:',timeit.timeit(f2, number=10))
print('regular exp:',timeit.timeit(f3, number=10))
Results:
to_numeric: 8.303612694763615
isfloat: 9.972200270603594
regular exp: 11.420604273894583