-1

I have two dataframes:

marks<-data.frame("student" =c("stud1","stud2","stud3") ,"sub1" =c(25,75,43), "sub2" = c(43,99,45),"sub3" = c(32,53,45), stringsAsFactors = FALSE)

grades<-data.frame("grade" =c("F","B","A") ,"sub1" =c(50,75,85), "sub2" =c(35,75,85)),"sub3" =c(32,75,85), stringsAsFactors = FALSE)

(sample in image format as well) enter image description here

I need to compare each mark in Marks df and get corresponding grades from Grades df. The grade definition is different for different subjects. I have tried using lapply function and cut:

 (marks<-sapply(marks, function(x) cut(x, 
                        breaks=c(0,50,75,85),
                        labels=c("F","B","A"),include.lowest = TRUE, right = TRUE,na.rm = TRUE))

This works without difficulty if grade boundaries are fixed. But when they change dynamically (based on subject column) I could not do this.

The expected output is:

gradedmarks<-data.frame("student" =c("stud1","stud2","stud3") ,"sub1" =c("F","B","F"), "sub2" = c("F","A","F"),"sub3" = c("F","F","F"), stringsAsFactors = FALSE)

Any quick way to achieve this in R?

Please note this is NOT the duplicate of this(Looping through multiple if_else statements). This is about using cut function with dynamic values depending on the column.

doctshind s
  • 380
  • 3
  • 13
  • Please revise the question so there is sufficient information for users to understand the problem. Right now it is confusing. The Grades def shown appears to be incorrect, and you have not explain what "change dynamically" means – Robert Wilson Jul 23 '20 at 09:05
  • Probably easy to do with a rolling or non-equi join. You should provide example input in a usable format and not as pictures. Also show expected output. Your `grades df` seems to be missing some grades. – Roland Jul 23 '20 at 09:07
  • @Roland I have provided table in the usable format. Thanks – doctshind s Jul 23 '20 at 09:43
  • The question is still somewhat confusing. You say you want to get the grades from the Grades df, but then what you have tried does not use the Grades df. You have also still provided no real indication of what you mean by grades changing dynamically – Robert Wilson Jul 23 '20 at 10:10
  • @RobertWilson A grade is calculated based on the boundary which is usually defined with same boundary for all subjects. This I have tried and working. But my requirement is different grade boundary for different subject. This is what I meant dynamical. Anyway I got the solution thanks for your great input – doctshind s Jul 23 '20 at 10:33

2 Answers2

2

Using a data.table non-equi join:

library(data.table)
setDT(marks)
setDT(grades)

#reshape to long format 
marks <- melt(marks, id.vars = "student")
grades <- melt(grades, id.vars = "grade")

#non.equi join
marks[grades, grade := i.grade, on = c("variable", "value >= value")]

#fill "F" for low marks
marks[is.na(grade), grade := "F"]

#reshape to wide format
dcast(marks, student ~ variable, value.var = "grade")
#  student sub1 sub2 sub3
#1   stud1    F    F    F
#2   stud2    B    A    F
#3   stud3    F    F    F

Study the data.table documentation to understand data.table syntax. There are some excellent vignettes.

Roland
  • 127,288
  • 10
  • 191
  • 288
1

You can use Map to change the grades dynamically :

marks[-1] <- Map(function(x, y) cut(x, breaks=c(0, y),
                   labels=grades$grade,include.lowest = TRUE, right = TRUE),
             marks[-1], grades[-1])

You might need to adjust settings of cut function based on your requirement. Also this requires subjects to be in same order in marks and grades dataframe.

Ronak Shah
  • 377,200
  • 20
  • 156
  • 213