python - Removing duplicate rows under conditions in Pandas
<p>I have a DataFrame like this:</p>

<pre><code>  NoDemande   NoUsager  Sens  IdVehiculeUtilise  Fait  HeurePrevue  HeureDebutTrajet
0 42191000823  001208    +         246VÃ©h         1    08:20:04     08:22:26 
1 42191000822  001208    +         246VÃ©h         1    08:20:04     08:18:56 
2 42191000822  001208    -         246VÃ©h        -99   09:05:03     08:56:26 
3 42191000823  001208    -         246VÃ©h         1    09:05:03     08:56:26 
4 42191000834  001208    +         246VÃ©h         1    16:50:04     16:39:26 
5 42191000834  001208    -         246VÃ©h         1    17:45:03     17:25:10 
6 42192000761  001208    +         246VÃ©h        -1    08:20:04     08:15:07 
7 42192000762  001208    +         246VÃ©h         1    08:20:04     08:18:27 
8 42192000762  001208    -         246VÃ©h        -99   09:05:03     08:58:29 
9 42192000761  001208    -         246VÃ©h        -11   09:05:03     08:58:29 
</code></pre>

<p>I get this data frame from<code>df[df.duplicated(['NoUsager','NoDemande'],keep=False)]</code>which ensure my rows being in pair. I want to drop a pair of rows when <code>NoDemande</code> are continuous numbers (like 42191000822 and 42191000823, 42192000761 and 42192000762) and the column <code>HeurePrevue</code> are the same, which means the records are recorded twice. I have to delete a pair and I'd like to preseve the one with more positive numbers in column <code>Fait</code>(at least one greater than 0)</p>

<p>So my result should look like:</p>

<pre><code>  NoDemande   NoUsager  Sens  IdVehiculeUtilise  Fait  HeurePrevue  HeureDebutTrajet
0 42191000823  001208    +         246VÃ©h         1    08:20:04     08:22:26 
3 42191000823  001208    -         246VÃ©h         1    09:05:03     08:56:26 
4 42191000834  001208    +         246VÃ©h         1    16:50:04     16:39:26 
5 42191000834  001208    -         246VÃ©h         1    17:45:03     17:25:10 
7 42192000762  001208    +         246VÃ©h         1    08:20:04     08:18:27 
8 42192000762  001208    -         246VÃ©h        -99   09:05:03     08:58:29 
</code></pre>

<p>I know it's something about <code>OR</code> logic but I have no idea how to realize it.</p>

<p>Any help will be appreciated~</p>

<p>This is a long-winded solution, there might be shorter ones. <code>frame0</code> is the exact frame you posted above.</p>

<p>First take the data, sort it by <code>NoDemande</code>, split it and recombine it so you have two pairings in the same row. Makes things a lot easier:</p>

<pre><code>frame0.HeurePrevue = pd.to_datetime(frame0.HeurePrevue)
frame0 = frame0.sort_values('NoDemande').reset_index(drop=True)
frameA = frame0.iloc[::2].reset_index(drop=True)
frameB = frame0.iloc[1::2].reset_index(drop=True)
frame1 = pd.concat([frameA,frameB],axis=1,join='inner')
frame1.columns = [u'NoDemande1', u'NoUsager1', u'Sens1', u'IdVehiculeUtilise1', u'Fait1',\
                  u'HeurePrevue1', u'HeureDebutTrajet1', u'NoDemande2', u'NoUsager2', u'Sens2',\
                  u'IdVehiculeUtilise2', u'Fait2', u'HeurePrevue2', u'HeureDebutTrajet2']
frame1 = frame1[[u'NoDemande1', u'Fait1',u'HeurePrevue1', u'NoDemande2',u'Fait2',\
                 u'HeurePrevue2']]
</code></pre>

<p>Next do some comparisons to see if in a given row the row ABOVE that row is a duplicate or not:</p>

<pre><code>frame2 = frame1[['NoDemande1','NoDemande2','HeurePrevue1','HeurePrevue2']].diff()
frame2['lastColumnsPartner'] = (frame2.NoDemande1 == 1) &amp; (frame2.NoDemande2 == 1) &amp;\
                               (frame2.HeurePrevue1 == pd.Timedelta(0)) &amp;\
                               (frame2.HeurePrevue2 == pd.Timedelta(0))
frame2 = frame2['lastColumnsPartner'].to_frame()
frame1 = pd.merge(frame1,frame2,left_index=True,right_index=True)
</code></pre>

<p>Now check the values of <code>Fait</code>:</p>

<pre><code>frame1['Fait1Pos'] = 0
frame1['Fait2Pos'] = 0
frame1.ix[frame1.Fait1&gt;0,'Fait1Pos'] = 1
frame1.ix[frame1.Fait2&gt;0,'Fait2Pos'] = 1
frame1['FaitPos'] = frame1.Fait1Pos+frame1.Fait2Pos
frame1['FaitBool'] = (frame1.Fait1 &gt; 0)|(frame1.Fait2 &gt; 0)
</code></pre>

<p>Iterate over all rows and use the boolean <code>lastColumnsPartner</code> to create a new index which identifies duplicate rows:</p>

<pre><code>frame1['newIndex'] = 0
j = -1
for i,row in frame1.iterrows():
  if frame1.ix[i,'lastColumnsPartner'] == False:
    j+=1
  frame1.ix[i,'newIndex'] = j
</code></pre>

<p>Take only rows with at least one positive value in <code>Fait</code> (<code>FaitBool</code>), sort by number of positive values of <code>Fait</code> (<code>FaitPos</code>), drop duplicates (<code>newIndex</code>) to keep only the highest value of <code>Fait</code>, then return <code>NoDemande</code>.</p>

<pre><code>tokeep = frame1[frame1.FaitBool][['NoDemande1','newIndex','FaitPos']]\
 .sort_values('FaitPos',ascending=False).drop_duplicates('newIndex')['NoDemande1']
</code></pre>

<p>Finally use boolean indexing on the initial frame to filter everything.</p>

<pre><code>frame0 = frame0[frame0.NoDemande.isin(tokeep)]
</code></pre>

<p>I can't say for sure if it works for all cases, it works for your example. Also there is probably room for improvement.</p>

python pandas dataframe duplicates