Adding a new column based off a lookup table in R

Question

I have a table:

ID  pheno   x1   x2   x3 
ABC 1       .43 .634 .542
BCD 0       .542 .42 .43
LOP 1       .235 .46  .78

etc

I have another table, where every ID in this table is male

ID 
ABC 
LOP
etc

I want to match those IDs in the second table and then add a column to table 1 indicating whether or not the IDs appear in table 2

Output:

ID  pheno   x1   x2   x3  sex
ABC 1       .43 .634 .542 1
BCD 0       .542 .42 .43  0
LOP 1       .235 .46  .78 1

I have tried

newtable <- table1[ifelse(table1$ID %in% table2$V1, table1$SEX <- 1, table1$SEX <-0),]

But the sex column just outputs 0s for every line.

Your help would be much appreciated

Edo · Accepted Answer · 2021-05-05T14:47:54.083

1

Given that you need MALE==1 and FEMALE==2, this is a solution you could use:

table1$sex <- (!table1$ID %in% table2$ID) + 1
table1

#>    ID pheno    x1    x2    x3 sex
#> 1 ABC     1 0.430 0.634 0.542   1
#> 2 BCD     0 0.542 0.420 0.430   2
#> 3 LOP     1 0.235 0.460 0.780   1

Where:

table1 <- read.table(text = "ID  pheno   x1   x2   x3 
ABC 1       .43 .634 .542
BCD 0       .542 .42 .43
LOP 1       .235 .46  .78", header = TRUE)

table2 <- data.frame(ID = c("ABC","LOP"))

edited May 05 '21 at 14:47

answered May 05 '21 at 14:35

Edo

7,567
2
9
19

Brilliant thanks! This gives me a boolean TRUE/FALSE. How would I turn this into 1/2? – tacrolimus May 05 '21 at 14:42
1

check my lastest update. I've added a `+` in front – Edo May 05 '21 at 14:43
Amazing, thanks again. If I wanted 1/2 instead of 0/1 how would i do that? – tacrolimus May 05 '21 at 14:44
1

1 = MALE or 1 = FEMALE? – Edo May 05 '21 at 14:45
1 = MALE, many thanks. For now I've just done table1$sex[table1$sex == 0] <- 2 – tacrolimus May 05 '21 at 14:45
1

okay, check the update – Edo May 05 '21 at 14:48

Adding a new column based off a lookup table in R

1 Answers1