Pandas remove rows which any string
<p>A very basic qs guys - thans vm for taking a look. I want to remove rows in <code>Col1</code> which contain any string - care about only numeric values in <code>Col1</code>.</p>

<p>Input:</p>

<pre><code>      Col1  Col2 Col3
0      123  48.0  ABC
1       45  85.0  DEF
2    A.789  66.0  PQR
3    RN.35   9.0  PQR
4      LMO  12.0  ABC
</code></pre>

<p>Output:</p>

<pre><code>      Col1  Col2 Col3
0    123.0  48.0  ABC
1     45.0  85.0  DEF
</code></pre>

<p>I tried </p>

<pre><code>test = input_[input_['Col1'].str.contains(r'ABCDEGGHIJKLMNOPQRSTUVWXYZ.')]
</code></pre>

<p>But see this error</p>

<blockquote>
  <p>ValueError: cannot index with vector containing NA / NaN values</p>
</blockquote>

<p>Could you:</p>

<ul>
<li>Give a short explanation as to why that's not working?</li>
<li>What would be the alternate solution pls?</li>
</ul>

<p>Another faster solution with <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a> and condition with <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.to_numeric.html" rel="nofollow"><code>to_numeric</code></a> where parameter <code>errors='coerce'</code> means if data are not numeric are converted to <code>NaN</code> - so you need find all not <code>NaN</code> data by <a href="http://pandas.pydata.org/pandas-docs/stable/generated/pandas.Series.notnull.html" rel="nofollow"><code>notnull</code></a>:</p>

<pre><code>print (pd.to_numeric(df.Col1, errors='coerce'))
0    123.0
1     45.0
2      NaN
3      NaN
4      NaN
Name: Col1, dtype: float64

print (pd.to_numeric(df.Col1, errors='coerce').notnull())
0     True
1     True
2    False
3    False
4    False
Name: Col1, dtype: bool

df = df[pd.to_numeric(df.Col1, errors='coerce').notnull()]
print (df)
  Col1  Col2 Col3
0  123  48.0  ABC
1   45  85.0  DEF
</code></pre>

<p><strong>Timings</strong>:</p>

<pre><code>#[100000 rows x 3 columns]    
df = pd.concat([df]*10000).reset_index(drop=True)

In [16]: %timeit (df.ix[df.Col1.map(lambda x: re.compile("[a-zA-Z]+").search(x) is None)])
10 loops, best of 3: 57.7 ms per loop

In [17]: %timeit (df[pd.to_numeric(df.Col1, errors='coerce').notnull()])
10 loops, best of 3: 22 ms per loop

In [18]: %timeit (df[~df['Col1'].astype(str).str.contains(r'[ABCDEGGHIJKLMNOPQRSTUVWXYZ.]', na=False)])
10 loops, best of 3: 38.8 ms per loop
</code></pre>

<hr>

<p>Your solution:</p>

<p>I think you need cast to <code>str</code> by <code>astype</code> and then add <code>[]</code> <a href="https://docs.python.org/2/library/re.html" rel="nofollow">used to indicate a set of characters</a> and last add parameter <code>na=False</code> because it seems some <code>NaN</code> value are in <code>col1</code> and then are converted to <code>False</code>:</p>

<pre><code>print (df['Col1'].astype(str).str.contains(r'[ABCDEGGHIJKLMNOPQRSTUVWXYZ.]', na=False))
0    False
1    False
2     True
3     True
4     True
Name: Col1, dtype: bool
</code></pre>

<p>Then need invert boolean mask by <code>~</code> and use <a href="http://pandas.pydata.org/pandas-docs/stable/indexing.html#boolean-indexing" rel="nofollow"><code>boolean indexing</code></a>:</p>

<pre><code>print (df[~df['Col1'].astype(str).str.contains(r'[ABCDEGGHIJKLMNOPQRSTUVWXYZ.]', na=False)])
  Col1  Col2 Col3
0  123  48.0  ABC
1   45  85.0  DEF
</code></pre>

python string pandas indexing numeric