This is an example to show how to implement a lookup in R like an Excel vlookup.
Firstly, create a reference table containing names and test scores
This value [Names] creates a list of 30 names
Names<-c("ANES","GEORGE","CHRISTOPHER" ,"JESSICA","SAMANTHA","OSMAN","MICHAEL" ,"VANESSA","ALEXANDER","ISABELLE","MICHAEL","CHLOE","FEDJA","VIET-NHAN","LOUISE","PHU-THANH" ,"CELINA","JASON","KEVIN","VINCENT","AMY","KALLEM","KENNY","KEVIN","BRENT" ,"AMANDA","JARED","DAVID","FRED","ABBY" )
Names## [1] "ANES" "GEORGE" "CHRISTOPHER" "JESSICA" "SAMANTHA"
## [6] "OSMAN" "MICHAEL" "VANESSA" "ALEXANDER" "ISABELLE"
## [11] "MICHAEL" "CHLOE" "FEDJA" "VIET-NHAN" "LOUISE"
## [16] "PHU-THANH" "CELINA" "JASON" "KEVIN" "VINCENT"
## [21] "AMY" "KALLEM" "KENNY" "KEVIN" "BRENT"
## [26] "AMANDA" "JARED" "DAVID" "FRED" "ABBY"This value [TestScore] creates a random range of 30 test scores from 1 - 100
TestScore<-sample(1:100, 30, replace=TRUE)
TestScore## [1] 95 81 82 63 16 10 40 58 31 59 100 14 39 57 21 8 85 45 86
## [20] 82 16 35 81 43 61 77 29 85 21 18This creates a data frame by joining Names and TestScore into a table
students<-data.frame(Names,TestScore)
students## Names TestScore
## 1 ANES 95
## 2 GEORGE 81
## 3 CHRISTOPHER 82
## 4 JESSICA 63
## 5 SAMANTHA 16
## 6 OSMAN 10
## 7 MICHAEL 40
## 8 VANESSA 58
## 9 ALEXANDER 31
## 10 ISABELLE 59
## 11 MICHAEL 100
## 12 CHLOE 14
## 13 FEDJA 39
## 14 VIET-NHAN 57
## 15 LOUISE 21
## 16 PHU-THANH 8
## 17 CELINA 85
## 18 JASON 45
## 19 KEVIN 86
## 20 VINCENT 82
## 21 AMY 16
## 22 KALLEM 35
## 23 KENNY 81
## 24 KEVIN 43
## 25 BRENT 61
## 26 AMANDA 77
## 27 JARED 29
## 28 DAVID 85
## 29 FRED 21
## 30 ABBY 18=====================
Now we create a lookup table
This creates a range of numbers and grades on which to do a conditional lookup.
num<-c(0,9.9,19.9,29.9,39.9,49.9,59.9,69.9,79.9,89.9)
Labels<-c("0 to 9",
"10 to 19",
"20 to 29",
"30 to 39",
"40 to 49",
"50 to 59",
"60 to 69",
"70 to 79",
"80 to 89",
"Up to and including 100")
num## [1] 0.0 9.9 19.9 29.9 39.9 49.9 59.9 69.9 79.9 89.9grades<-c("Unsatisfactory",
"Unsatisfactory",
"Unsatisfactory",
"Unsatisfactory",
"Barely Acceptable",
"Acceptable",
"Good",
"Very Good",
"Excellent",
"Outstanding")
grades## [1] "Unsatisfactory" "Unsatisfactory" "Unsatisfactory"
## [4] "Unsatisfactory" "Barely Acceptable" "Acceptable"
## [7] "Good" "Very Good" "Excellent"
## [10] "Outstanding"lookup<-data.frame(num,Labels,grades)
lookup## num Labels grades
## 1 0.0 0 to 9 Unsatisfactory
## 2 9.9 10 to 19 Unsatisfactory
## 3 19.9 20 to 29 Unsatisfactory
## 4 29.9 30 to 39 Unsatisfactory
## 5 39.9 40 to 49 Barely Acceptable
## 6 49.9 50 to 59 Acceptable
## 7 59.9 60 to 69 Good
## 8 69.9 70 to 79 Very Good
## 9 79.9 80 to 89 Excellent
## 10 89.9 Up to and including 100 Outstanding=====================================================================
This uses the ‘cut’ function to use the lookup table to create values which corresponds to the grades in the students table
Grade<-cut(students$TestScore,c(lookup$num,100),lookup$grades)
Range<-cut(students$TestScore,c(lookup$num,100),lookup$Labels)The Range and Grade values are joined to the students table using ‘cbind’.
Results<-cbind(students,Range,Grade)View the results
Results## Names TestScore Range Grade
## 1 ANES 95 Up to and including 100 Outstanding
## 2 GEORGE 81 80 to 89 Excellent
## 3 CHRISTOPHER 82 80 to 89 Excellent
## 4 JESSICA 63 60 to 69 Good
## 5 SAMANTHA 16 10 to 19 Unsatisfactory
## 6 OSMAN 10 10 to 19 Unsatisfactory
## 7 MICHAEL 40 40 to 49 Barely Acceptable
## 8 VANESSA 58 50 to 59 Acceptable
## 9 ALEXANDER 31 30 to 39 Unsatisfactory
## 10 ISABELLE 59 50 to 59 Acceptable
## 11 MICHAEL 100 Up to and including 100 Outstanding
## 12 CHLOE 14 10 to 19 Unsatisfactory
## 13 FEDJA 39 30 to 39 Unsatisfactory
## 14 VIET-NHAN 57 50 to 59 Acceptable
## 15 LOUISE 21 20 to 29 Unsatisfactory
## 16 PHU-THANH 8 0 to 9 Unsatisfactory
## 17 CELINA 85 80 to 89 Excellent
## 18 JASON 45 40 to 49 Barely Acceptable
## 19 KEVIN 86 80 to 89 Excellent
## 20 VINCENT 82 80 to 89 Excellent
## 21 AMY 16 10 to 19 Unsatisfactory
## 22 KALLEM 35 30 to 39 Unsatisfactory
## 23 KENNY 81 80 to 89 Excellent
## 24 KEVIN 43 40 to 49 Barely Acceptable
## 25 BRENT 61 60 to 69 Good
## 26 AMANDA 77 70 to 79 Very Good
## 27 JARED 29 20 to 29 Unsatisfactory
## 28 DAVID 85 80 to 89 Excellent
## 29 FRED 21 20 to 29 Unsatisfactory
## 30 ABBY 18 10 to 19 Unsatisfactory