how to delete rows of a df based on the column of integers in another df

Question

I want to remove rows in df1 which have common numbers in the "0" columns of both df's

df1 = pd.read_csv('Displacement.txt', sep="\s", header=None)
df2= pd.read_csv('overconstraint.txt', header=None)

df1 (>6million rows) looks like

    0   1   2   3   4   5   6
0   1   ,   1   ,   1.0 ,   1.8800E-01
1   2   ,   1   ,   1.0 ,   1.8491E-01

df2 (>1000 rows) looks like

    0
0   1119933
1   1119933
2   1122062

How to do this?

jezrael · Accepted Answer · 2021-07-06T05:28:24.950

1

You need specify column instead DataFrame by Series.isin:

test=df1[~df1[0].isin(df2[0])]

#in some pandas versions is necessary convert Series to list
test=df1[~df1[0].isin(df2[0].astype(int).tolist())]

Solution with casting to integers:

test=df1[~df1[0].astype(int).isin(df2[0].astype(int))]

#in some pandas versions is necessary convert Series to list
test=df1[~df1[0].astype(int).isin(df2[0].astype(int).tolist())]

edited Jul 06 '21 at 05:28

answered Jul 02 '21 at 13:04

jezrael

729,927
78
1,141
1,090

The name of my real data column is "0" in both df's so I used test=df1[~df1[0].isin(df2[0])] but it doesnt work. test returns the same as df1 – user9106985 Jul 02 '21 at 13:20
@user9106985 - Try `df1[~df1['0'].isin(df2['0'])]` – jezrael Jul 02 '21 at 13:21
@user9106985 - If still not working `df1[~df1['0'].astype(int).isin(df2['0'].astype(int)]` – jezrael Jul 02 '21 at 13:22
@user9106985 - Or `df1[~df1['0'].isin(df2['0'].tolist())]` is another idea. – jezrael Jul 02 '21 at 13:22
I have edited my original post to shown a snapshot of real data. – user9106985 Jul 02 '21 at 13:34

Corralien · Answer 2 · 2021-07-02T13:05:28.693

1

Make the difference between ref columns:

>>> df1[~df1['ref'].isin(df2['ref'])]
   ref  value1
0    2      76
1    4      23
5   12      78
6   14      34

edited Jul 02 '21 at 13:05

answered Jul 02 '21 at 13:04

Corralien

70,617
7
16
36

11 second later... :) – jezrael Jul 02 '21 at 13:05
Your `04:22`, my answer `04:11` ;) – jezrael Jul 02 '21 at 13:06
Due to jet lag :-) – Corralien Jul 02 '21 at 13:06
Hmmm, `jet leg` ? Data flow under ocean I guess ;) – jezrael Jul 02 '21 at 13:07
https://stackoverflow.com/questions/50779617/pandas-pd-series-isin-performance-with-set-versus-array – jezrael Jul 02 '21 at 13:12

how to delete rows of a df based on the column of integers in another df

2 Answers2