R code for midterm grade report

#require(xlsx)
rm(list=ls())

list.files()
tb = read.csv("some grades.csv")
empty.columns= NULL
for (j in 8:length(tb[1,])){
  #for( i in 1:length(tb[,1])){
  #  if( tb[i,j]=='-') {tb[i,j]=NA } 
  #}
 tb[,j] = as.numeric( tb[,j])
 tb[is.na(tb[,j]),j] = 0
 if( max(tb[,j])==0 ) { empty.columns = c(empty.columns, j)}
}
str(tb) 
tb2 = tb[, - empty.columns]
#tb2 = tb2[, -"Course.total"]
#tb2 = tb2[, - grep('Spring', names(tb2))]
#tb2 = tb2[, - grep('spring', names(tb2))]
tb2 = tb2[, -grep("Quiz.Retake..Fall.2014.Exam.1..part.2..online.part", names(tb2))]
names(tb2)
exam1 = c("Quiz.Exam1.Part1..Fall.2014" ,                                    
          "Assignment.exam1..part2..calculation.questions..fall.2014",
          "Quiz.Fall.2014.Exam.1..part.2..online.part"                )        
exam2= c("Quiz.Exam.2..closed.book.section..Fall.2014..Thursday",             
        "Quiz.Exam2..open.book.section..Fall.2014..Tuesday")

report= tb2[,1:2]
report$Exam1 = apply( tb2[,exam1], 1, sum)
report$Exam2 = apply( tb2[,exam2], 1, sum)

practical = "Assignment.Practical.Exam..microscope.and.morphology..Sep.29..2014"
report$practical = tb2[,practical]/20

scale = c("Quiz.Lab.assignment..Scale.of.Microbes",                    
"Quiz.scale.of.microbes..lab.report")
report$scale= apply( tb2[, scale],1, max)

misc= c("Quiz.DePaepeTaddei.Reading.Assignment" ,                                           
        "Quiz.Lab.assignment..E.coli.genome.studies")

tb2[1:5, misc]
report$misc= apply( tb2[, misc],1, sum)
report$ch1 = tb2[, grep("Chapter.1",names(tb2))]
report$ch2 = tb2[, grep("chapter.2",names(tb2))]
report$ch3= apply( tb2[, grep("Chapter.3",names(tb2))],1, max)
report$ch4= apply( tb2[, grep("Chapter.4",names(tb2))],1, max)
report$ch5= apply( tb2[, grep("Chapter.5",names(tb2))],1, max)
report$ch5= apply( tb2[, grep("ch6",names(tb2))],1, max)
report$ch7= apply( tb2[, grep("ch7",names(tb2))],1, max)
report$ch8= apply( tb2[, grep("ch8",names(tb2))],1, max)
names(tb2)[grep("Quiz", names(tb2))]

assignAndLab =c("scale","misc","ch1","ch2","ch3","ch4","ch5","ch7","ch8")
report$assignAndLab = apply( report[,assignAndLab], 1, sum)
maxS = apply( report[, assignAndLab], 2, max)
report$assignAndLab = 15*report$assignAndLab / sum(maxS)

report$highExam = apply(report[,c("Exam1","Exam2")], 1, max)
head(report)

midterm= c("highExam","practical","assignAndLab")
report$midterm= apply(report[,midterm], 1, sum) *100/70
head(report[,midterm])
hist( report$midterm )

grade2letter = function(x){
  if(x>94){    ret='A'
  }else if (x >90) {    ret='A-'
  }else if (x >87 ){    ret = 'B+'
  }else if (x > 84){    ret = 'B'
  }else if (x >80){    ret = 'B-'
  }else if (x > 76){  ret = 'C+'  
  }else if (x > 70){ ret = 'C'  
  }else if (x > 67){ ret = 'C-'
  }else if (x > 64){ ret = 'D+'
  }else if (x > 60){ ret = 'D'
  }else {   ret = 'F'
  }
  return (ret)
}
grade2letter(70); grade2letter(88)
report$letter = lapply(report$midterm,  grade2letter)