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>I see here two solutions.
<em>The first is based on the suggestion, that you have always continuous pairs of entries in your dataset</em> - that if any entry has a pair, this pair comes after this entry. Then you should loop over your dataframe with step size = 2:</p>

<pre><code>for i in range(0,x,2):
  your action
</code></pre>

<p>And in this loop you can compare your two entries and remove the one that has a negative value.</p>

<p><em>My second proposition is a little bit complex.</em></p>

<p>First you should copy and lag (shift by specific number of rows) all columns. This can be done with following function (applied only on NoDemande, for doing so to every column use a loop):</p>

<pre><code>df.NoDemande = df.NoDemande.shift(-1)
</code></pre>

<p>It will look like:</p>

<pre><code>  NoDemande      NoDemande_lagged

0 42191000823    42191000822
1 42191000822    42191000822 
2 42191000822    42191000823
3 42191000823    42191000834
</code></pre>

<p>Then compare the two values in the same row in NoDemande and NoDemande_lagged columns. If the number from 42191000822 is greater or smaller by 1 than the value in NoDemande, then compare <strong>Fait</strong> and <strong>Fait_lagged</strong> and choose the more positive value, which you should paste in the new column <strong>Fait_selected</strong>. The same you should do with other columns, so that every column will have a lagged copy and a selected copy. Afterwards you should remove your next row, because you have already compared it with the previous one.
At the end you should delete your original and lagged colums and leave only the "_selected".</p>

<p>Sorry for a complex Explanation, hope, that this will help you anyway. If you are familiar with RapidMiner, I can explain how to do this there, it will be easier. And I gave you some ideas for various concepts that can help you to solve your Problem.</p>

python pandas dataframe duplicates