Pandas equivalent of a SQL not exists subquery

user_9867838 0
Viewed 1066

I am trying to remove certain rows from a Pandas table. It is essentially a deduplication exercise:

I have a table

           id          sub_id1       sub_id2              date       
0           1           424755           101        2018-09-21      
1           2           424755           101        2018-09-21
2           3           424755           102        2018-09-21
3           4           678321           101        2018-09-21
4           5           678321           102        2018-09-22
5           6           424755           102        2018-09-22

I want to remove a row if there is another row that matches the sub_id1 and date, but has a lower sub_id2.

The SQL I would write for this would be

select * from table t
where not exists (select 1 from table
                  where sub_id1=t.sub_id1
                  and date=t.date
                  and sub_id2<t.sub_id2)

Resulting table would be

           id          sub_id1       sub_id2              date       
0           1           424755           101        2018-09-21      
1           2           424755           101        2018-09-21
2           4           678321           101        2018-09-21
3           5           678321           102        2018-09-22
4           6           424755           102        2018-09-22

Where id=3 is removed because there are rows that have the exact same sub_id1 and date column as it does, with a lower sub_id2.

Trying to figure this out using Pandas merges. Thanks!

pandas merge join sql python
2 Answers

This is more like a groupby problem + min value slice (I am using transform min here)

s=df.groupby(['sub_id1','date']).sub_id2.transform('min')
df[df.sub_id2==s]
Out[146]: 
   id  sub_id1  sub_id2        date
0   1   424755      101  2018-09-21
1   2   424755      101  2018-09-21
3   4   678321      101  2018-09-21
4   5   678321      102  2018-09-22
5   6   424755      102  2018-09-22

where sub_id1=t.sub_id1 and date=t.date : df.groupby(['sub_id1','date'])

sub_id2<t.sub_id2 : df.sub_id2==s

user_79645270

Overview: This code will group the dataset and find the minium composite key value

id=[1,2,3,4,5,6]
sub_id1=[424775,424775,424775,678321,678321,424755]
sub_id2=[101,101,102,101,102,102]
date=['2018-09-21','2018-09-21','2018-09-21','2018-09-21','2018-09-21','2018-09-21']

dict={
    'id':id,
    'sub_id1':sub_id1,
    'sub_id2':sub_id2,
    'date':date
}

df1=pd.DataFrame(dict,index=id)
df1['date']=pd.to_datetime(df1['date'])
df1.sort_values(['sub_id1','sub_id2'],inplace=True)
#print(df1.info())
#print(df1.keys)
query=df1[df1['date']=='2018-09-21']
#print(query)
#print(df1)
print(df1.groupby(['sub_id1','date'])['sub_id2'].min())
user_40011770
Related