wwhy wwhy - 1 month ago 7
Python Question

use pandas to dynamic drop rows

import pandas as pd

inp = [{'c1':10, 'c2':100},{'c1':12,'c2':110},{'c1':13,'c2':120},{'c1':15,'c2':130},{'c1':16,'c2':140},{'c1':17,'c2':150},{'c1':18,'c2':160},{'c1':19,'c2':170},{'c1':20,'c2':180},{'c1':25,'c2':190}]
df = pd.DataFrame(inp)

for i,row in df.iterrows():
df.drop(df[df['c1']==row['c1']+1].index,inplace=True)
df.drop(df[df['c1']==row['c1']+2].index,inplace=True)
df.drop(df[df['c1']==row['c1']+3].index,inplace=True)


I hope my code to contain rows when c1=10,15,19,25, but my code only contain rows when c1=10,25.

My goal is to drop rows when c1=10+1,10+2,10+3, then my code should judge whether c1=14 exists. If c1=14 exists, my code should drop rows when c1=14+1, 14+2,14+3. If c1=14 doesn't exist, my code should find the next c1, for example 15, then drop rows when c1=15+1,15+2,15+3.

The output should be
c1 c2
0 10 100
3 15 130
7 19 170
9 25 190


but the output of my code is

c1 c2
0 10 100
9 25 190

Answer

Quick and dirty example would be:

to_delete = []
for id,value in enumerate(df.c1.values):
    if id>0:
        if max(to_delete) < value:
            to_delete.append(value+1)
            to_delete.append(value+2)
            to_delete.append(value+3)
    else:
        to_delete.append(value+1)
        to_delete.append(value+2)
        to_delete.append(value+3) 

df_new = df[~df.c1.isin(to_delete)]

That will leave the c1 values [10,15,19,25] as you have requested.

Comments