Thursday, December 10, 2015

bio233 final grade calculation in R

# Written Exams 50%
# Practical Exams 5%
# Assignments & Laboratory Reports 25%
# participation, Quizzes 5%
# Oral Presentations 15%
# bonus

require(xlsx)
rm(list=ls())
setwd("~/Dropbox/courses.student.research.dp/bio233.Fall2015_dpbx/course.management/grades/final-grade,fall15")
list.files()
tb = read.csv("201509-92253-01 Grades.csv")  #, colClasses=c(rep("character",4), rep("numeric",18)))
str(tb[,1:10])
names(tb)[1:10]
empty.columns= NULL
for (j in 7:length(tb[1,])){
  tb[,j] = as.numeric( tb[,j])
  if( max(tb[,j]) == 0 ) {
    print( mean(tb[,j]))
    print(paste(j, names(tb)[j]))
    empty.columns = c(empty.columns, j)
   }
}

names(tb)[ grep("bonus", names(tb))]

tb2 = tb[, - empty.columns]

tb2 = tb2[, -c(3:6)] #remove some character columns
names(tb2)[ grep( "not.count", names(tb2))] #remove does counted grades
tb3 = tb2[, - grep( "not.count", names(tb2)) ]

###
report = tb3[,1:2] #report table
###

# pick Exams
examColumns = names(tb3)[grep("Exam", names(tb3))]
examColumns[ grep("bonus", examColumns)]

exam1 = c(   "Quiz.Exam.1..Fall.2015..closed.book.part...Chapter.1.4...Real." ,                                                    
  "Assignment.Exam.1..Fall.2015.bonus.point.for.self.screen.recording.during.closed.book.exam..and.partial.credits..Real.",
  "Assignment.Exam.1..Fall.2015.bonus.point.for.self.screen.recording.during.open.book.exam..Real.",
  "Quiz.Exam.1..Fall.2015.Part.2..Real.",
  "Quiz.Exam.1..Fall.2015.Part.2..redo.for.makeup.points..Real.")      
exam2= c(  "Quiz.Exam.2..closed.book.part..Fall.2015..Real.",                                                                      
  "Assignment.bonus.point..self.recording.of.closed.book.exam..Exam.2..Fall.2015..Real.",                                
  "Assignment.bonus.point..self.recording.of.openbook..Exam.2..Fall.2015..Real.",                                        
  "Quiz.Exam.2.Open.book.part..Fall.2015..Real.")
exam3=c("Assignment.bonus.point..self.recording.of.closed.and.open.book..Exam.3.Fall.2015..Real.",                              
        "Quiz.Fall.2015.Exam.3..closed.book.part..ch8..10..12..28...Real." ,                                                    
        "Quiz.Fall.2015.Exam3.open.book.part..Real." )                      
final = c( "Quiz.Final.Exam..Fall.2015..closed.book.part..Real."  ,                                                                
  "Quiz.Final.Exam..open.book.part..Fall.2015..Real." ,                                                                  
  "Assignment.bonus.point..self.recording.of.closed.and.open.book..Final.Exam..Fall.2015..Real.")

#there is an extra column for Exam1 final grade. Do not double-count this again.
#"Exam.1.Fall.2015..Total.Grade..Real."                                                                                

report$Exam1 = apply( tb3[,exam1], 1, sum)
report$Exam2 = apply( tb3[,exam2], 1, sum)
report$Exam3 = apply( tb3[,exam3], 1, sum)
report$Final = apply( tb3[,final], 1, sum)

# take best 2 regular exam and the final
report$badExam = apply(report[,c("Exam1","Exam2", "Exam3")], 1, min)
report$ExamTot = (report$Exam1 + report$Exam2 + report$Exam3 + report$Final - report$badExam) / 3

# practical 5%
#"Assignment.Practical.Exam..microscope.and.morphogy..Fall.2015..Real."                                                
#"Assignment.Picture.of.streaked.plates..practical.exam..fall.2015..Real."                                              
practical = names(tb3)[grep("ractical", names(tb3))]
summary(tb3[practical])
report$PracticalTot = apply( tb3[,practical], 1, sum)*5/200

########
# assignments, quizs, lab reprts, 25%
tb3[1:2,grep("Assignment", names(tb3))]

#tb3[1:2,grep("Quiz", names(tb3))]
tb3[1:2,grep("v14", names(tb3))]
names(tb3)[grep("12", names(tb3))]
report$ch1 = tb3[, grep("v14Ch1",names(tb3))]
report$ch2= apply( tb3[, grep("v14Ch2",names(tb3))],1, max)
report$ch3= apply( tb3[, grep("v14..Ch3",names(tb3))],1, max)
report$ch4= apply( tb3[, grep("v14..Ch4",names(tb3))],1, max)
report$ch5= tb3[, grep("v14Ch5",names(tb3))]
report$ch6= apply( tb3[, grep("v14Ch6",names(tb3))],1, max)
report$ch7= apply( tb3[, grep("v14..Ch7",names(tb3))],1, max)
report$ch8= tb3[, grep("v14.Ch8",names(tb3))]
report$ch10= apply( tb3[, grep("v14..Ch10",names(tb3))],1, max)
report$ch12= apply( tb3[, grep("v14..Ch12",names(tb3))],1, max)
#report$ch12= tb3[, grep("v14..Ch12",names(tb3))]
report$ch28= apply( tb3[, grep("v14..Ch28",names(tb3))],1, max)

report$scale= apply( tb3[, grep("scale.of.microbes",names(tb3))],1, max)
report$DePaepe= apply( tb3[, grep("DePaepeTaddei",names(tb3))],1, max)
report$EcoliGenome= apply( tb3[, grep("E.coli.genome",names(tb3))],1, max)
report$video1 = tb3[, "Assignment.Homework..Video.recording.of.self.introduction..Real."]

misc = c("Assignment.Homework..Video.recording.of.self.introduction..Real.",
"Assignment.Lab.report..west.nile.virus..Individual.effort...Real.",
"Assignment.Serial.dilution.lab.group.report..Each.group.only.needs.to.submit.1.report...Real.",
"Quiz.Lab.report..myxoma.and.australian.rabbits..Real.",
"Assignment.Report.for.Gram.stain.lab..individual.report...Real.",
"Assignment.Milestone.check..googleDoc.serial.dilution.group.data.input..Real.",
"Assignment.Milestone.check..googleDoc.serial.dilution.group.data.input..for.plat.counting.results..Real.",
"Assignment.Ontime.submission.of.oral.presentation.topic.on.GoogleDoc..Real.")
tb3[1:2,misc]
report$misc = apply( tb3[, misc],1, sum)

names(report)
assignment.lab = c("ch1","ch2","ch3","ch4","ch5","ch6","ch7","ch8","ch10","ch12","ch28","scale","DePaepe","EcoliGenome","video1","misc")
summary(report[,assignment.lab])

report$assignment.lab = apply(report[, assignment.lab], 1, sum)
report$assignment.labTot = 25* report$assignment.lab / (ceiling(max(report$assignment.lab)/10)*10)

#participation 5
report$participation = tb3[,"Attendance.Attendance..Real."]

### bonus
report$bonus = tb3[,"Assignment.bio233.bonus.points..fall.2015..Real."]

#oral presentation
names(tb3)[grep("oral", names(tb3))]
report$oral = tb3[,"Assignment.Second.video.recording..oral.presentation.of.a.primary.research.paper.on.microbiology..individual.effort..Real."]
report$oral = report$oral * 15/ 100

names(report)[grep("Tot", names(report))]
# Final Grade
final.grades = c("ExamTot","PracticalTot","assignment.labTot", 'oral','participation', 'bonus')
report$TotalGrade = apply( report[,final.grades], 1, sum)
source("grade2letter.R")
report$letter = lapply(report$TotalGrade,  grade2letter)

write.xlsx(report, "bio233_fall2015_final_grades.xlsx")

#generate a sorted report
report.sorted = report[order(report$TotalGrade),]
write.xlsx(report.sorted, "bio233FinalGradesFall2015-sorted.xlsx")

#generate a student report file
studentReport = c("First.name","Last.name", final.grades, 'TotalGrade', 'letter' )
write.xlsx(report.sorted[,studentReport], "bio233FinalGradeFall2015_student_report.xlsx")
 


No comments:

Post a Comment