[R] Joining tables with different order and matched values

[Jim Lemon]

Hi Abo,
This is essentially the same as your other problem. Notice that this
solution will only work if the values in dt1 and dt2 are character,
not factor and that I have replaced the space in "Drug name" with an
underscore. R will automatically substitute a period when such a name
is read in.

dt1<-read.table(text="Drug_name indications
 Ibuprofen Pain
 Simvastatin hyperlipidemia
 losartan hypertension",header=TRUE,stringsAsFactors=FALSE)
dt2<-read.table(text="Drug_name indications
 Simvastatin
 losartan
 Ibuprofen
 Metformin ",fill=TRUE,header=TRUE,stringsAsFactors=FALSE)
# this gets all the values you want, but not in the correct format
dt3<-merge(dt1,dt2,by="Drug_name",all=TRUE)
# set up a new "indications" field
dt3$indications<-NA
for(i in 1:length(unique(dt3$Drug_name))) {
 all_ind<-c(dt3$indications.x[i],dt3$indications.y[i])
 notNA<-which(!is.na(all_ind))
 if(any(notNA)) dt3$indications[i]<-all_ind[notNA][1]
}
dt3<-dt3[,c("Drug_name","indications")]
dt3

Jim

Hi All ..,

I have 2 tables and I'm trying to have some information from the 1st table
to appear in the second table with different order.

For Example, let's say this is my 1st table :-


Drug name           indications
 Ibuprofen                Pain
 Simvastatin            hyperlipidemia
losartan                   hypertension


my 2nd table is in different order for the 1st column :-

Drug name       indications

Simvastatin
losartan
Ibuprofen
Metformin

I wish to see the indication of each drug in my 2nd table subsisted from
the information in my 1st table so the final table
would be like this

Drug name       indications

Simvastatin     hyperlipidemia
losartan           hypertension
Ibuprofen       pain
Metformin    N/A