Nested ifelse with grepl function in R

Question

i'm trying to lookup in 3 diff excel sheets(Data Frames) to add 3 new columns in the consolidated data frame.

df = Consolidated Data Frame where i'm trying to create 3 new columns

df2 = data frame(single column) where i'm looking up to get SheetName1 Column in df(SheetName1 column should result if df2 string found in any 5 columns from df else "NA"

df3 = ddata frame(single column) where i'm looking up to get Segregation & segregationRef Column in df(Segregation column should result if df3 string found in any 5 columns from df else "NA" segregationRef column should result "NA" if string found in segregation column. I tried the below but not getting the optimum result.

df <- data.frame(Section=c("sheet1", "sheet11", NA, "sheet15"),
                 Level =c("(Level 1)", "(Level 1)", "sheet1", "(Level 1)", NA),
                 SAC=c(NA, NA, "sheet5", NA), 
                 Name=c(NA, "rohan", "vaibhav", "suresh"), 
                 COLL MGR=c(NA, NA, NA, "suresh"))

df2 <- data.frame(sheetname=c("sheet1", "sheet2", "sheet3", "sheet4"))
df3 <- data.frame(segregation=c("john", "naren", "suresh", "rohan"))
df$Sheetname1 <- "NA"

for (j in 1:nrow(df2)) {
  for (k in 1:nrow(df)) {
    df$Sheetname1[k]= 
      ifelse(grepl(df2$Sheetname[j],
                   paste(df$`Level`[k], df$SAC[k]), 
                   ignore.case=T),
             df2$Sheetname[j], df$Sheetname1[k])
  }
}

df$Segregation <- NA

for(l in 1:nrow(df3)){
  for(m in 1:nrow(df)){
    df$Segregation[m]=
      ifelse(grepl(df3$Segregation[l],
                   paste(df$`Level`[m], df$Name[m], 
                         df$COLL MGR[m], 
                         df$`COLL MGR`[m], 
                         df$`Collateral Manager`[m]),
                   ignore.case=T),
             df3$Segregation[l],
             df$Segregation[m])
  }
}

Hi Satheesh, could you post an example of the data you are working with and the expected output you are looking for? It would be easier for you to explain what you need. — Rohit
Hi Rohit, thanks for your response. i can't post the data due to CDC, but i can explain you. i need a new column in my consolidated sheet(df) as sheetname1, if the sheet name(string) from df2$sheetname column presents in any of the 5 columns from df(Section Name, df$ProductArea, df$SOURCE_CODE) then pull the sheet name to sheetname1 else "NA". In the similar manner if df3$segregation string presents in any of the 5 columns (Section Name, ProductArea, SOURCE_CODE, COLL MGR, Collateral Manager) from df then that string should appear in df$segregation column else "NA" — Satheesh Miryala
I understand you can't post the actual data, but a dummy dataset with 3-5 rows with similar each would be helpful. It makes it easier for more people to experiment and find a solution. Please see : stackoverflow.com/help/mcve and look up the dput function — Rohit

Rohit Rohit · Accepted Answer · 2018-12-07T07:13:37

Try this. It should work. Note that it will just return the first match that it finds.

df <- data.frame(Section = c("sheet1", "sheet11", NA, "sheet15"),
                 Level =c("(Level 1)", "(Level 1)", "sheet1", "(Level 1)"), 
                 SAC = c(NA, NA, "sheet5", NA), 
                 Name = c(NA, "rohan", "vaibhav", "suresh"), 
                 `COLL MGR` = c(NA, NA, NA, "suresh") )
df2 = data.frame(sheetname = c("sheet1", "sheet2", "sheet3", "sheet4")); 

df3 = data.frame(segregation = c("john", "naren", "suresh", "rohan"))

sheetname1<-apply(df,1,function(x){
  if(any(x %in% df2$sheetname))
    return(x[x %in% df2$sheetname][1])
  else
    return(NA_character_)
})


segregation<-apply(df,1,function(x){
  if(any(x %in% df3$segregation))
    return(x[x %in% df3$segregation][1])
  else
    return(NA_character_)
})

cbind(df,sheetname1,segregation)
#output:
# Section     Level    SAC    Name COLL.MGR sheetname1 segregation
# 1  sheet1 (Level 1)   <NA>    <NA>     <NA>     sheet1        <NA>
# 2 sheet11 (Level 1)   <NA>   rohan     <NA>       <NA>       rohan
# 3    <NA>    sheet1 sheet5 vaibhav     <NA>     sheet1        <NA>
# 4 sheet15 (Level 1)   <NA>  suresh   suresh       <NA>      suresh

Nested ifelse with grepl function in R

1 Answers