remove rows from one table, where a field in a column matches that of same column in different table

brucezepplin Published at Dev

brucezepplin

Hi I have two dataframes (called table1 and table2 respectively):

ID    MONTH
--    -----

1     Jan  
2     May
3     May          
4     Jan


ID     TEST1   GNDR  
--     -----   ----   

1      90      M  
2      80      M  
3      70      F

where I want to remove from table1 any row where the ID matches the ID in table2, so that I am left with:

ID    MONTH
--    -----   

4     Jan

I just want to say where ID from table2 matches ID in table1, remove all rows relating to those IDs.

I can use the merge() function to obtain the rows where ID is common using

merge(table1,table2,by="ID")

and store the results in a dataframe, but I do not know how to delete the rows from table1 based on the result of the merge command.

Any help would be great.

akrun

You can use %in%

df1[!df1$ID %in% df2$ID,]
#  ID MONTH
#4  4   Jan

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at2021-02-14

Comments

0 comments

From Dev

remove rows that are same on one column but different on another from a data.table

From Dev

How to create a table where one column contains a same value for all rows with different id?(See Layout for clarity)

From Dev

Using date from one table in a where clause with a column in a different table

From Dev

Update column of one table from same table

From Dev

R data.table remove rows where one column is duplicated if another column is NA

From Dev

Create table from two rows, one column

From Dev

Multi-column update from SAME table, different rows, in Oracle, with not-null column: Receiving error 01407

From Dev

Copy rows from the same table and update with different ID column and another column

From Dev

SQL Select rows where all rows from linked table have the same value in column x

From Dev

MYSQL: select from table where a string in a column matches?

From Dev

Calculating the value of multiple rows in a column from one table into an other table

From Dev

Calculating the value of multiple rows in a column from one table into an other table

From Dev

Insert column data rows (all) in another column table where each row matches the common ID

From Dev

Update multiple column value from one column of the same table

From Dev

Update multiple column value from one column of the same table

From Dev

Select rows with same id but different value in another column in a table

From Dev

Comparing corresponding rows from the same column and same table

From Dev

Comparing corresponding rows from the same column and same table

From Dev

Union table where column matches defined varchar

From Dev

Reference a different column in that same table

From Dev

Using values from a field in one table as column name for field in another

From Dev

Insert distinct records from column in Table1 into column table 2 into row where they match on a different column

From Dev

compare in same table by one column

From Dev

Mysql rows from table A with ID from query where ID matches one condition true in table B

From Dev

Copy content of one table to another, where column content is different

From Dev

MySql get all rows where id = xxx and where the newest column in other table in row with same id is greater than one

From Dev

Need to retrieve rows from table where the following condition was not satisfied column A = column B and column B = column A

From Dev

Sqlalchemy single query for multiple rows from one column in one table

From Dev

SELECT one column twice from same table with two WHERE conditions SQL SERVER

Related Related

Article