This is a test version of Biostars. For the public version, visit https://www.biostars.org.
Remove entries from table based on another table and output new table in R -- Edit.

Hello,

I am trying to remove values from one table from another and produce a new table with data of that of the full data set minus the one table.

I have tried the following in several iterations and am still having trouble. This seems like it should be easy to find but its turning out to not be.

So I have two tables both with the same layout with 36 columns. All labels are the same.

t1 has 19136 obs and pauci has 1495 obs end results should be a table t2 with 17641 obs

Not sure as to why this isnt working. Its only returning a table with the pauci results, I want the other portion.

My current code

t2 <- pauci[!(t1 %in% pauci),]

EDIT=========================== Ok so I am still having a problem, new code below....

t2 <- pauci[!(t1$Id %in% pauci$Id),]

What is happening is it is removing all values that are not in the pauci df and filling it with the pauci table and na values, image below. Any idea whats going on here?

enter image description here

I just want to remove the pauci table from the t1 table and create t2 with those results.

r
• 1,624 views • link
written by rshipman • rshipman

Hello boomshackle!

We believe that this post does not fit the main topic of this site.

Answered

For this reason we have closed your question. This allows us to keep the site focused on the topics that the community can help with.

If you disagree please tell us why in a reply below, we'll be happy to talk about it.

Cheers!

• 0 views • link
written by rshipman • rshipman

Closing the post is not the appropriate way of indicating that you have received a satisfactory answer. That is done by "accepting" the correct answer(s) (use the green check mark against the answer that is correct.

Closing a post is generally used by mods to flag posts that are inappropriate for the main subject of this site.

• 0 views • link
written by GenoMax GenoMax

But how is possible that a normal user can close the post? I was absolutely confused :=0

• 0 views • link
written by Santosh Anand Santosh Anand

Wait did I write this, I am confused? Why did it say I wrote this?

I asked this question because people in the lab I work in recommended the site and people here have helped me in the past with this same project so I guess I do not see the problem? This is a bioinformatics form right? My first post got criticized for using excel and they told me to use R, now that I did that I am getting the same message again? What should I post here?

• 0 views • link
written by rshipman • rshipman

Tagging: Istvan Albert

boomshackle : I am not sure how you managed to choose close this post which is what generates that message you see above. Perhaps @Istvan (site admin) can throw some light on this. I tried to open the "closed" post but that does not appear to be working.

• 0 views • link
written by GenoMax GenoMax

Whatever happened, it all good. Figured out the problem so the post is closed.

• 0 views • link
written by rshipman • rshipman

At least "accept" the answer that worked from the ones below (use green check mark) that will provide closure to this thread (aside from the "closed' problem).

• 0 views • link
written by GenoMax GenoMax

2 answers

The %in% operator expects vectors on both sides. So you need to use the name of a column which uniquely determines the rows of both tables - something like 

t2 <- pauci[!(t1$name %in% pauci$name),]
• 0 views • link
written by Santosh Anand Santosh Anand

Thank you so Much! I knew it was a simple syntax error :)

Edit - nvm :(

• 0 views • link
written by rshipman • rshipman

Ok so slight problem, it worked but instead it is only showing values in the pauci df and filling the remaining records with NA. So the data set looks full but it is only filled with the values I want to remove from the main df.

Any idea as to why this is happening?

• 0 views • link
written by rshipman • rshipman

You need to reverse the order of operands of %in% operator

t2 <- pauci[!(pauci$name %in% t1$name), ]
• 0 views • link
written by Santosh Anand Santosh Anand

I have tried that but it returns a table with 0 obs. enter image description here

• 0 views • link
written by rshipman • rshipman

that's the right way to do - check if you are committing some naive error. Check especially that the pauci$name uniquely identifies the rows (i.e. no repeated entries in pauci$name)

• 0 views • link
written by Santosh Anand Santosh Anand

Thats what is not making sense to me, the Id is very unique to the entries. This error is holding up the rest of the script as it is blocking half the entries accessed? It has to be syntax in one way or another but I have not clue where. I have removed all duplicates and named to the extent you can see in the image below. Neither of them have NA values in them either.

Could it be something to do with the far left column without a title on the top?

enter image description here

t2 <- pauci[!(pauci$Id %in% t1$Id),]
• 0 views • link
written by rshipman • rshipman

could you upload pauci and t1 df somewhere?

save(pauci, t1, file="pauci_problem.rdata")

This will create "pauci_problem.rdata" in your working dir. Upload that please.

• 0 views • link
written by Santosh Anand Santosh Anand

wait.... I am just seeing that your t1 has 12060 rows, while pauci has only 1236. And you want to remove all rows of pauci from t1. That means, the subsetting has to be done from t1, not pauci..

t1[!(t1$name %in% pauci$name), ]

Note t1 data frame instead of pauci

• 0 views • link
written by Santosh Anand Santosh Anand

Yes close, thank you. I believe i got it to work with the following,

t2 <- t1[!(t1$Id %in% pauci$Id),]
• 0 views • link
updated by zx8754 zx8754 • written by rshipman • rshipman

Please mark this as an answer if the issue is resolved, so as to close this thread

• 0 views • link
written by Santosh Anand Santosh Anand

Log in to answer this question.

More posts like this