-1

I have created a formula in excel with iferror and vlookup(mentioned below).Can you please let me know how can I replicate the same in R

This lookup goes to main_df which matches with various column named E&F

So the condition is vlookup on column E with sheet1 if error then vlookup on F with sheet2 and if error then again vlookup upon E with sheet3.So basically every time I get a NA it should pick up only those values and do the next vlookup.

IFERROR(IFERROR(VLOOKUP($E2,'sheet1'!$K:$L,2,0),VLOOKUP($F2,'sheet2'!$A:$B,2,0)),VLOOKUP($E2,'sheet3'!$N:$O,2,0))

Edit:

   main_df
    Countries   City
    USA         Texas
    India       Mumbai
    China       Hunan
    Veitnam     Hue

Other DF

df_sheet1                            df_sheet2
    Countries Population          City      Population
    USA       1000000             Hunan     239857688
    India     118947759           Hue       667588

Desired output final_df

main_df
Countries   City      Population
USA         Texas     1000000
India       Mumbai    118947759
China       Hunan     239857688
Veitnam     Hue       667588
zx8754
  • 52,746
  • 12
  • 114
  • 209
Arkadeep Paul Choudhury
  • 41
  • 1
  • 9
  • 1
    Please provide reproducible example and expected output. – Sotos Jun 23 '16 at 07:52
  • Has you tried it yourself already? – Alex Jun 23 '16 at 07:52
  • Excel formula doesn't makes sense. – zx8754 Jun 23 '16 at 07:58
  • I have quoted the excel formula to make it clear..Have already explained what I am excatly looking for.The output I am looking for is all the NA should have matched values as per as the look up done. So letz say have df with country names and the rest 3 sheet1,sheet2,sheet3 have population different sheet has different country name and population number.So the desired output would be vlook up and get all the countries population in main df.Hope that explains – Arkadeep Paul Choudhury Jun 23 '16 at 08:03
  • 1
    Provide [reproducible example](http://stackoverflow.com/questions/5963269). sheet1, sheet2, sheet3 are 3 different dataframes? – zx8754 Jun 23 '16 at 08:07
  • Yes they are different df.I already gave the example... – Arkadeep Paul Choudhury Jun 23 '16 at 08:17
  • So basically `df <- c('USA','UK',India','China','Denmark','Canada','Turkey'......) df_sheet1 df_sheet2 df_sheet3 Countries Population Countries Population Countries Population USA 1000000 India 118947759 Turkey 567748 UK 9857688 China 239857688 Canada 667588` Desired out put `final_df countries population USA 1000000 India 18947759 UK 9857688 China 239857688 Turkey 567748 canada 667588` – Arkadeep Paul Choudhury Jun 23 '16 at 08:29
  • sorry for not having proper format – Arkadeep Paul Choudhury Jun 23 '16 at 08:32
  • You can edit your post. – zx8754 Jun 23 '16 at 08:37
  • Wouldn't `rbind` solve your problem here ? Just get your all data together and then merge it . – Pankaj Kaundal Jun 23 '16 at 09:26
  • not possible since I need to do next vlook up when only it get NA with first vlook up – Arkadeep Paul Choudhury Jun 23 '16 at 09:32
  • Possible duplicate of [Merging more than 2 dataframes in R by rownames](http://stackoverflow.com/questions/16666643/merging-more-than-2-dataframes-in-r-by-rownames) – Pankaj Kaundal Jun 23 '16 at 09:36

1 Answers1

1

i assume you have two dfs as:

> data1
  Countries Population
1       USA    1000000
2     India  118947759

> data2
  Countries Population
1    Turkey     567748
2        UK    9857688
3     China  239857688
4    Canada     667588

> tomerge
  Countries
1       USA
2        UK
3     India
4     China
5   Denmark
6    Canada
7    Turkey

Run this function i found on a question on SO

MyMerge <- function(x, y){
              df <- merge(x, y, by= "Countries", all.x= TRUE, all.y= TRUE)
              return(df)
            }

Then using Reduce

final <- Reduce(MyMerge,list(data1,data2,tomerge))


> final
  Countries Population.x Population.y
1     India    118947759           NA
2       USA      1000000           NA
3    Canada           NA       667588
4     China           NA    239857688
5    Turkey           NA       567748
6        UK           NA      9857688
7   Denmark           NA           NA
Pankaj Kaundal
  • 1,012
  • 3
  • 13
  • 25
  • The concern is that that the merge is from different df and not one...and hence the mention of df_sheet1,df_sheet2 and df_sheet3 and the main df has only counties – Arkadeep Paul Choudhury Jun 23 '16 at 09:10
  • some what solves it but again the concern is need to do lookup for multiple column from the main df(tomerge-df) but the function gives option to merge from only one column in the df 'countries' and assume I have 'region' also in the main df to map. – Arkadeep Paul Choudhury Jun 23 '16 at 10:37