Pandas Delete rows from dataframe based on condition
<p>Consider this code:</p>

<pre><code>from StringIO import StringIO
import pandas as pd

txt = """a, RR
10, 1asas
20, 1asasas
30,
40, asas
50, ayty
60, 2asas
80, 3asas"""
frame = pd.read_csv(StringIO(txt), skipinitialspace=True)

print frame,"\n\n\n"

l=[]
for i,j in frame[~ frame['RR'].str.startswith("1", na=True)]['RR'].iteritems():
    if j.startswith(('2','3')):
         if frame[frame['RR'].str.startswith("1", na=False)]['RR'].str.match("1"+j[1:], as_indexer = True).any():
            l.append(i)
    else:
        if frame[frame['RR'].str.startswith("1", na=False)]['RR'].str.match("1"+j, as_indexer = True).any():
            l.append(i)
 frame = frame.drop(frame.index[l])
 print frame
</code></pre>

<p>What i am doing here is,</p>

<p>1)Loop through dataframes to drop any <code>RR</code> which already has <code>1RR</code> in dataframe</p>

<p>2)If <code>RR</code> has <code>2 or 3</code> at start , then drop if that <code>RR</code> has <code>1RR[1:]</code> in dataframe.</p>

<p>3)If <code>RR</code> startswith <code>1</code> or is <code>NaN</code> do not touch it.</p>

<p>The code is working fine but this <code>dataframe</code> will have upto 1 million entries and i dont think this code is optimsed.As i have just started with <code>pandas</code> i have limited knowledge.
Is there any way we can achieve this without <code>iteration</code>.Does <code>pandas</code> has any inbuilt utility to do this?</p>

<p><strong>First</strong>, keep all strings starting with <code>1</code> or <code>nan</code>:</p>

<pre><code>keep = frame['RR'].str.startswith("1", na=True)
keep1 = keep[keep]  # will be used at the end
</code></pre>

<p><strong>Second</strong>, keep strings starting with <code>2</code> or <code>3</code> that are not in the first dataframe <code>rr1</code>:</p>

<pre><code>rr1 = frame.loc[frame['RR'].str.startswith("1", na=False), 'RR']
keep2 = ~frame.loc[
            (frame['RR'].str.startswith("2")) | (frame['RR'].str.startswith("3")), 'RR'
        ].str.slice(1).isin(rr1.str.slice(1))
</code></pre>

<p><strong>Third</strong>, keep other strings that are not in <code>rr1</code> after adding a leading <code>1</code>:</p>

<pre><code>import numpy as np
keep3 = ~("1" + frame.loc[
            ~frame['RR'].str.slice(0,1).isin([np.nan, "1", "2", "3"]), 'RR'
        ]).isin(rr1)
</code></pre>

<p><strong>Finally</strong>, put everything together:</p>

<pre><code>frame[pd.concat([keep1, keep2, keep3]).sort_index()]
</code></pre>

python python-2.7 pandas