2

I have table (table 1) which contains a column with names of companies with a certain permit. I have another table (table 2) which contains all the information of companies being active. Now I would like to check whether the companies listed in table 2 are present in table 1.
So basically I want to compare the values of table 2 column company name to the values of table 1 column company name. Something like v-lookup. How can I most easily execute this in R?

Dave2e
  • 22,192
  • 18
  • 42
  • 50
  • 1
    have a look at `is.element` or `%in%` – Clemsang Aug 06 '19 at 13:03
  • 1
    `match`, `%in%` or `merge` will all work depending on the type of output you want. If you share a small reproducible sample of input and show the desired output we can help you see which is best. Without details like that, it's hard to be specific about an answer. See [this excellent FAQ](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) about making reproducible examples in R. – Gregor Thomas Aug 06 '19 at 13:04
  • Unfortunately, I can't use my real data as an example. The first row of the company table column company name needs to be checked in the entire column of the permit table. Then the second row of the company tabble column company name needs to be cheded in the eniire column of the permit table and so on. So i think i need to use a for loop or something., but match might work als well since all the values in the permit table are unique. –  Aug 06 '19 at 14:17

1 Answers1

0

It is not hard to make up some data that illustrate your question. That is what the FAQ shows you how to do:

set.seed(42)
table1 <- data.frame(company=sample(LETTERS, 10))
table2 <- data.frame(company=LETTERS)

table1$company is a vector of the companies with permits and table2$company is a vector of all of the companies. Now use %in% to find which companies in table2 are in table1:

intable1 <- table2$company %in% table1$company

intable1 is a logical vector which is TRUE of the table2$company is in table1$company. You can add this column to table2 as a logical vector or print the results:

table2[intable1, ]
#  [1] A D E G J O Q R V Z
# Levels: A B C D E F G H I J K L M N O P Q R S T U V W X Y Z

The company names are factors in this example. If you want them to be character strings, use

table1 <- data.frame(company=sample(LETTERS, 10), stringsAsFactors=FALSE)
table2 <- data.frame(company=LETTERS, stringsAsFactors=FALSE)
dcarlson
  • 10,936
  • 2
  • 15
  • 18
  • Hi thank you so much, for some reason it doesn't seem to work. I try to be more specific: in table 1 there is a column consisting of names and arent Unique, its possible that the company is present in table 1 multiple times; i want every row of the column table1.comp_name to be compared to the entire column of table2.permit_comp_name. Moreover i'm looking for partly matches as well, since the permit_comp_name of table 2 is a bit messy. Could you maibe help me out; i used the intable1 example, but it doens not seem to work :( –  Aug 07 '19 at 16:10
  • 1
    It should find multiples just fine, but you will have to check for partial matches some other way. The function table(company) will list every company name and how many times it occurs. That will let you spot partial matches. – dcarlson Aug 08 '19 at 03:23