#This is file "gradebio233,20141214.R"
require(xlsx)
rm(list=ls())
list.files()
# tb = read.csv("201409-62376-01BIO233 Grades 20141209c.csv")
tb = read.csv("201409-62376-01BIO233 Grades 20141214-a.csv")
#The - signs have to be replaced with zeros in textwrangler
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)
examColumns = names(tb)[grep("xam", names(tb))]
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")
exam3=c("Quiz.Exam3..closed.book.section..Nov.20..2014",
"Quiz.Exam.3..open.book.section..Fall.2014" )
final = c( "Quiz.Final.Exam..Open.book.section..Fall2014..Dec.9..11am.13.00",
"Quiz.Closed.book.section.of.final.exam..Dec.9..2014..10.30am.12.30pm")
tb2[,final]
names(tb)[grep("inal", names(tb))]
report= tb2[,1:2]
report$Exam1 = apply( tb2[,exam1], 1, sum)
report$Exam2 = apply( tb2[,exam2], 1, sum)
report$Exam3 = apply( tb2[,exam3], 1, sum)
report$Final = apply( tb2[,final], 1, sum)
practical = names(tb2)[grep("ractical", names(tb2))]
report$ToTpractical = (tb2[,"Assignment.Practical.Exam..microscope.and.morphology..Sep.29..2014"]/10
+ tb2[,"Assignment.Streak.plate..practical.exam"])/4
### do find out assignments and chapter quiz
#scale lap report were posted twice
scale = c("Quiz.Lab.assignment..Scale.of.Microbes",
"Quiz.scale.of.microbes..lab.report")
report$scale= apply( tb2[, scale],1, max)
# chapter homework can be found with "Quiz" or "Chapter". The names should be consisteny!!!
names(tb2)[grep("Chapter", names(tb2))]
report$ch1 = tb2[, grep("Chapter.1",names(tb2))]
report$ch2 = tb2[, grep("Chapter\\.2",names(tb2))] ##.2 can match 32
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("Chapter.6",names(tb2))],1, max)
report$ch7= apply( tb2[, grep("Chapter.7",names(tb2))],1, max)
report$ch8= apply( tb2[, grep("Chapter.8",names(tb2))],1, max)
report$ch9= apply( tb2[, grep("Chapter9",names(tb2))],1, max)
report$ch10= apply( tb2[, grep("Chapter10",names(tb2))],1, max)
report$ch16= apply( tb2[, grep("Chapter16",names(tb2))],1, max)
report$ch32= apply( tb2[, grep("Chapter32",names(tb2))],1, max)
#misc assignment and lab reports, which can be quiz or assignments
names(tb2)[grep("ment", names(tb2))]
misc= c( "Assignment.Serial.dilution.lab.group.report" ,
"Quiz.DePaepeTaddei.Reading.Assignment" ,
"Assignment.Pictures.for.microbes.on.campus.by.groups" ,
"Quiz.Lab.assignment..Scale.of.Microbes" ,
"Quiz.Lab.assignment..E.coli.genome.studies" ,
"Assignment.Report.for.Gram.stain.lab..individual.report." ,
"Assignment.Homework.for.Dr..Wenzhi.Li.s.lecture..Individual.effort.",
"Assignment.homework.on.circulating.tumor.DNA" )
tb2[, "Assignment.homework.on.circulating.tumor.DNA" ] = tb2[, "Assignment.homework.on.circulating.tumor.DNA" ]/10
tb2[, "Assignment.Report.for.Gram.stain.lab..individual.report."] =tb2[, "Assignment.Report.for.Gram.stain.lab..individual.report."]/10
tb2[1:5, misc]
report$misc= apply( tb2[, misc],1, sum)
assignAndLab =c("scale","misc","ch1","ch2","ch3","ch4","ch5","ch7","ch8", "ch9","ch10","ch16","ch32")
report$ToTassignAndLab = apply( report[,assignAndLab], 1, sum)
maxS = apply( report[, assignAndLab], 2, max)
report$ToTassignAndLab = 15*report$ToTassignAndLab / sum(maxS)
## end of assignment and lab reports
#attendence
list.files()
att.tb= read.csv( "201409-62376-01BIO233_Attendances_2014129-1734.csv")
att.tb$ToTAttendence = apply( att.tb[, 6:33], 1, sum)
str(att.tb)
hist(att.tb$ToTAttendence, br=20)
report$ToTAttendence = att.tb$ToTAttendence[match(report$Last.name, att.tb$Last.name)]
report$ToTAttendence = report$ToTAttendence*5/ max(report$ToTAttendence)
# 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
head(report)
# bonus points, need to add R bonus points
names(tb2)[grep("onus", names(tb2))]
bonus = c("Assignment.Bonus.points.of.paper.presentations.and.volunteering" ,
"Assignment.Bonus.Problem.1..Flow.cytometer.data.analysis.1" ,
"Assignment.Bonus.problem.2..Cholera.data.simulation.in.R.1" )
report$bonus = apply( tb2[,bonus], 1, sum)
# oral
report$oral = tb2[,"Assignment.Oral.presentation.grades..fall.2014"]
#written report
report$written = tb2$WrittenReport
FinalGrades= c("ExamTot","ToTpractical","ToTassignAndLab", "ToTAttendence", 'bonus', 'oral', "written")
report[,FinalGrades]
report$FinalGrade= apply(report[,FinalGrades], 1, sum)
hist(report$FinalGrade, br=20)
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$FinalGrade, grade2letter)
write.xlsx(report, "bio233FinalGradesFall20141214-a.xlsx")
#generate a sorted report
report.sorted = report[order(report$FinalGrade),]
write.xlsx(report.sorted, "bio233FinalGradesFall20141214-a-sorted.xlsx")
No comments:
Post a Comment