.Rhistory

#Now need to collapse these 33 dimensions into 3!!
# Read in conversion table
mathRosetta<-read.csv("data/wkshtCollapsingMathStandards.csv")
ccMath0$dimension<-sapply(ccMath0$dimension0,function(x) {
row<-match(x,mathRosetta$dimension0)
mathRosetta$dimension[row]
})
ccMath<-ccMath0 %>% mutate(dim=gsub("[a-z |,]","",dimension))%>% mutate(orig_dimension=dimension0,orig_dim=dim0) %>%  select("grade","dimension","dim","code","statement","link","orig_dimension","orig_dim","code2")
write.csv(ccMath,"./data/formatted_CommonCoreMath.csv",row.names = F)
######
### English
ccELA<-data.frame(
GradeJumble=xml_find_all(ccELA.xml,".//LearningStandardItem//GradeLevel")%>%  xml_parent()%>% xml_text(),
Standard=xml_find_all(ccELA.xml,".//StatementCode")%>% xml_text(),
Statement=xml_find_all(ccELA.xml,".//Statement")%>% xml_text(),
Link=xml_find_all(ccELA.xml,".//RefURI")%>% xml_text()
)
ccELA$Grade=ccELA$GradeJumble
for (i in 1:length(badstrings))
{
ccELA$Grade<-gsub(badstrings[i],goodstrings[i],ccELA$Grade)
}
ccELA<-ccELA %>% mutate(grade=Grade,code=gsub("CCSS.ELA-Literacy.","",Standard,fixed=T),statement=Statement,link=Link)
#Need to create a new vector where CCRA comes after subject, so they sort into their proper subcategories
ccELA$code2 <- sapply(ccELA$code,function(x) {
if(length(grep("CCRA",x,fixed=T))==0){x}else{paste0(gsub("CCRA.","",x,fixed=T),".CCRA")}},USE.NAMES = F)
ccELA<-ccELA %>% mutate(dim0=gsub("\\..*$","",code2))%>% filter(dim0!="")
unique(ccELA$dim0)
#Add longer text for acronyms
ccELA$dimension0<-sapply(ccELA$dim0, function(x) ifelse(x=="",NA, switch(x,"L"="Language","R"="Reading", "RF"="Reading: Foundational Skills","RH"="Reading: History/Social Studies","RI"="Reading: Informational Text","RL"="Reading: Literature","RST"="Reading: Science & Technical Subjects","SL"="Speaking & Listening","W"="Writing","WHST"="Writing in History, Science & Technical Subjects"))) %>% unlist()
unique(ccELA$dimension0)
ccELA$dimension=case_when(
ccELA$dim0%in% c("R", "RF","RH","RI","RL","RST")~"Reading",
ccELA$dim0%in% c("L","SL") ~ "Language, Speaking & Listening",
ccELA$dim0%in% c("W","WHST") ~"Writing"
)
ccELA.out <- ccELA %>% mutate(dim=gsub("[a-z |,]","",dimension)) %>%filter(dim0!="CCRA")%>% mutate(orig_dimension=dimension0,orig_dim=dim0) %>%  select("grade","dimension","dim","code","statement","link","orig_dimension","orig_dim")
ccELA.out
#apparently we got some duplicated standards somewhere. Let's get those outta here
ccELA.out$code[which(duplicated(ccELA.out$code))]
ccELA.out0 <- ccELA %>% mutate(dim=gsub("[a-z |,]","",dimension)) %>%filter(dim0!="CCRA")%>% mutate(orig_dimension=dimension0,orig_dim=dim0) %>%  select("grade","dimension","dim","code","statement","link","orig_dimension","orig_dim")
#apparently we got some duplicated standards somewhere. Let's get those outta here
ccELA.out0$code[which(duplicated(ccELA.out0$code))]
ccELA.out0[-which(duplicated(ccELA.out0$code)),]
#test
ccELA.out$code[which(duplicated(ccELA.out$code))]#no duplicated entries in final output
#apparently we got some duplicated standards somewhere. Let's get those outta here
ccELA.out0$code[which(duplicated(ccELA.out0$code))]#duplicated entries
ccELA.out<-ccELA.out0[-which(duplicated(ccELA.out0$code)),]
#test
ccELA.out$code[which(duplicated(ccELA.out$code))]#no duplicated entries in final output
write.csv(ccELA.out,"data/formatted_CommonCoreELA.csv",row.names = F)
require(openxlsx);require(tidyverse);require(stringi);require(stringr)
c3<-read.csv("data/formatted_c3SocialStudies.csv")
ela<-read.csv("data/formatted_CommonCoreELA.csv")
math<-read.csv("data/formatted_CommonCoreMath.csv")
sci<-read.csv("data/formatted_ngss.csv",quote="\"")
sdg<-read.csv("data/SDG-targets.csv")
names(sdg)[c(2,3)]<-c("code","statement")
sdg$subcat=sapply(sdg$goal,function(x) switch(x,"1"="No Poverty","2"="Zero Hunger","3"="Good Health and Well-Being","4"="Quality Education","5"="Gender Equality","6"="Clean Water and Sanitation","7"="Affordable and Clean Energy","8"="Decent Work and Economic Growth","9"="Industry, Innovation, and Infrastructure","10"="Reduced Inequalities","11"="Sustainable Cities and Communities","12"="Responsible Consumption and Production","13"="Climate Action","14"="Life Below Water","15"="Life on Land","16"="Peace, Justice, and Strong Institutions","17"="Partnerships for the Goals"))
c3$subject<-"Social Studies"
c3$set<-"C3"
ela$subject<-"ELA"
ela$set<-"Common Core ELA"
math$subject <- "Math"
math$set<-"Common Core Math"
sci$subject <- "Science"
sci$set<-"NGSS"
sdg$set <- "SDGs"
sdg$subject<-NA
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1385]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.</sup>","\\.")
allSubj$statement[1385] #it worked
#example:
allSubj$statement[1500]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>","^")
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("</sup>","")
allSubj$statement[1500] #successfully changed "s<sup>2</sup>" to "s^2"
#Remaining problem--all other HTML tags...
#start with italics, which we can change to markdown
allSubj$statement[1587] # i and em tags need change to *text*
allSubj$statement<-allSubj$statement %>% stringr::str_replace_all(pattern="<[\\/?i|\\/?em]*>","*")
#check if it worked:
#show example:
allSubj$statement[1587]
View(allSubj)
#example:
allSubj$statement[1655]
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1655]
#example:
allSubj$statement[1655]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.</sup>","\\.")
allSubj$statement[1385] #it worked
allSubj$statement[1655] #it worked
#example:
allSubj$statement[1590]
which allSubj$code=="K.OA.A.1"
which() allSubj$code=="K.OA.A.1")
which( allSubj$code=="K.OA.A.1")
#example:
allSubj$statement[1290]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.</sup>","\\.")
allSubj$statement[1290] #it worked
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1290]
which( allSubj$code=="1.OA.B.3")
allSubj$statement[1313]
allSubj$statement %>% stringr::str_replace_all(".*(<sup>1</sup>).*","\\1"
)
allSubj$statement[1313] %>% stringr::str_replace_all(".*(<sup>1</sup>).*","\\1")
allSubj$statement[1313] %>% stringr::str_replace_all(".*(<sup>1</sup>).*","")
allSubj$statement[1313] %>% gsub(".*(<sup>1</sup>).*","")
allSubj$statement[1313] %>% gsub(".*<sup>1</sup>.*","")
allSubj$statement[1313] %>% gsub("(.*)<sup>1</sup>(.*)","\\1\\2")
allSubj$statement[1313]
#example:
allSubj$statement[1290]
allSubj$statement[1290] %>% gsub(".*(<sup>1</sup>).*","")
allSubj$statement[1290] %>% gsub("(.*)<sup>1</sup>(.*)","\\1\\2")
allSubj$statement[1290] %>% gsub("(.*)<sup>1</sup>(.*)","\\1")
allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\/sup>","\\.")
allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
#example:
allSubj$statement[1290]
allSubj$statement[1313]
allSubj$statement[1313] %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1313] %>% stringr::str_replace_all("\\.<sup>.</sup>","\\.")
allSubj$statement[1313] %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1290] %>% stringr::str_replace_all(".*(<sup>1<\\/sup>).*","")
allSubj$statement[1290] %>% stringr::str_replace_all(".*<sup>1<\\/sup>.*","")
allSubj$statement[1290]
allSubj$statement[1290] %>% stringr::str_replace_all(".*<sup>1<\\/sup>.*","")
allSubj$statement[1290] %>% stringr::str_replace_all(".*<sup>1<\\/sup>.*","\\1")
allSubj$statement[1290] %>% stringr::str_replace_all(".*(<sup>1<\\/sup>).*","\\1")
allSubj$statement[1290] %>% stringr::str_extract(".*[^<sup>1<\\/sup>].*")
allSubj$statement[1290] %>% stringr::str_replace_all("<sup>1<\\/sup>]","")
allSubj$statement[1290] %>% stringr::str_replace_all("<sup>1</sup>]","")
allSubj$statement[1290] %>% stringr::str_replace_all("<sup>1</sup>","")
#example:
allSubj$statement[1290]
allSubj$statement[1313]
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj$statement[1313] # example of footnote at end of sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>1</sup>","") #no reason to exponent 1 except as a footnote
allSubj$statement[1290] #it worked
allSubj$statement[1313]
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>1</sup>","") #no reason to exponent 1 except as a footnote
allSubj$statement[1290] #it worked
allSubj$statement[1313] # example of footnote at end of sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1313]
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>1</sup>","") #no reason to exponent 1 except as a footnote
allSubj$statement[1290] #it worked
allSubj$statement[1313] # example of footnote at end of sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1313]
#example:
allSubj$statement[1655]
which(allSubj$code=="HSA-REI.B.4b")
#example:
allSubj$statement[1603]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>","^")
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("</sup>","")
allSubj$statement[1603] #successfully changed "s<sup>2</sup>" to "s^2"
#Remaining problem--all other HTML tags...
#start with italics, which we can change to markdown
allSubj$statement[1587] # i and em tags need change to *text*
#Remaining problem--all other HTML tags...
#start with italics, which we can change to markdown
allSubj$statement[1603] # i and em tags need change to *text*
allSubj$statement<-allSubj$statement %>% stringr::str_replace_all(pattern="<[\\/?i|\\/?em]*>","*")
#check if it worked:
#show example:
allSubj$statement[1603]
#Are there other HTML tags? Remove dem
allSubj$statement<-allSubj$statement %>% stringr::str_remove_all(pattern="<[^>]*>")
#check
sum(sapply(list(c3,ela,math,sci,sdg),nrow))
nrow(allSubj)
head(allStandards)
head(allSubj)
write.csv(allSubj,"data/allStandards.csv",row.names=F)
# Make Excel version
write.xlsx(allSubj,"data/allStandards.xlsx",row.names=F,freezePane=list(firstActiveRow=2,firstActiveCol=6))
?write.xlsx
# Make Excel version
write.xlsx(allSubj,"data/allStandards.xlsx",row.names=F,freezePane=list(firstRow=T))
nrow(allSubj)
allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension, dplyr::)
#Reorganize column order
allSubj_final<-allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension)
allSubj_final
require(openxlsx);require(tidyverse);require(stringi);require(stringr);require(XLConnect)
write.csv(allSubj_final,"allStandards.csv",row.names=F)
# Make Excel version
write.xlsx(allSubj_final,"allStandards.xlsx",row.names=F,freezePane=list(firstRow=T))
source('~/mrw_synced/R/Nonshiny Github/standardX/scripts/oneScriptToBindThem.R')
?loadWorkbook
#Load template
teachingMat<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#save without overwriting formatting
XLConnect::setStyleAction(teachingMat,XLConnect::XLC$"STYLE_ACTION.NONE")
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
message("align_to_all_subject_standards.xlsx Cleared and updated")
message("@ File 'align_to_all_subject_standards.xlsx' Cleared and updated")
message("@File 'align_to_all_subject_standards.xlsx' Cleared and updated")
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#save backup
XLConnect::saveWorkbook(alignToStndz,"data/align_to_all_subject_standards_OLD.xlsx")
message("  @Old version of file saved at 'data/align_to_all_subject_standards_OLD.xlsx'")
message("  *Old version of file saved at 'data/align_to_all_subject_standards_OLD.xlsx'")
#Clear old data and update "align to all subjects template"
deleteRange<-c(3,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
View(alignToStndz)
XLConnect::writeWorksheet(alignToStndz,sheet="Sheet1",startRow = 2,startCol=2,header=F)
XLConnect::writeWorksheet(allSubj_final,sheet="Sheet1",startRow = 2,startCol=2,header=F)
allSubj_final
XLConnect::writeWorksheet(as.data.frame(allSubj_final),sheet="Sheet1",startRow = 2,startCol=2,header=F)
View(allSubj_final)
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 2,startCol=2,header=F)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
message("  @File 'align_to_all_subject_standards.xlsx' Cleared and updated")
View(allSubj_final)
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#Clear old data and update "align to all subjects template"
deleteRange<-c(3,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
#Clear old data and update "align to all subjects template"
deleteRange<-c(2,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 2,startCol=2,header=F)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
names(allSubj_final
)
names(alignToStndz)
#Reorganize column order
allSubj_final<-allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension,subcategory)
names(alignToStndz)
names(allSubj_final)
require(openxlsx);require(tidyverse);require(stringi);require(stringr);require(XLConnect)
c3<-read.csv("data/formatted_c3SocialStudies.csv")
ela<-read.csv("data/formatted_CommonCoreELA.csv")
math<-read.csv("data/formatted_CommonCoreMath.csv")
sci<-read.csv("data/formatted_ngss.csv",quote="\"")
sdg<-read.csv("data/SDG-targets.csv")
names(sdg)[c(2,3)]<-c("code","statement")
sdg$subcat=sapply(sdg$goal,function(x) switch(x,"1"="No Poverty","2"="Zero Hunger","3"="Good Health and Well-Being","4"="Quality Education","5"="Gender Equality","6"="Clean Water and Sanitation","7"="Affordable and Clean Energy","8"="Decent Work and Economic Growth","9"="Industry, Innovation, and Infrastructure","10"="Reduced Inequalities","11"="Sustainable Cities and Communities","12"="Responsible Consumption and Production","13"="Climate Action","14"="Life Below Water","15"="Life on Land","16"="Peace, Justice, and Strong Institutions","17"="Partnerships for the Goals"))
# sdg$dim<-gsub("[a-z]| |-|,","",sdg$subcat)
c3$subject<-"Social Studies"
c3$set<-"C3"
ela$subject<-"ELA"
ela$set<-"Common Core ELA"
math$subject <- "Math"
math$set<-"Common Core Math"
sci$subject <- "Science"
sci$set<-"NGSS"
sdg$set <- "SDGs"
sdg$subject<-NA
#NGSS statement codes have weird apostrophes and long em dash that caused probs ’ b4 I saved as csv w/ utf-8! This code is left over from those weird import issues
# test<-sci$statement[66]
# grep("\xd5",test,fixed=T,useBytes=T)
# test2 <- stri_replace_all(test,"'",fixed="\xd5")
# grep("\xd5",test2,fixed=T,useBytes=T)
# sapply(sci$statement,function(x) length(grep("\xd5",x,fixed=T,useBytes=T))) %>% sum()
# sci<-sci %>% apply(.,c(1,2),function(x) stri_replace_all(x,"'",fixed="\xd5")) %>% as_tibble()
# sapply(sci$statement,function(x) length(grep("\xd5",x,fixed=T,useBytes=T))) %>% sum()
#
# #now fix directional quotes “”
# sci$statement %>% sapply(.,function(x) stri_detect(x,fixed="\xd1")) %>% which() %>% unname()
# sapply(sci$statement,function(x) length(grep("\xd2|\xd3",x,fixed=F,useBytes=T))) %>% sum()
# sci<-sci %>% apply(.,c(1,2),function(x) stri_replace_all(x,"\"",regex="\xd2|xd3")) %>% as_tibble()
# sapply(sci$statement,function(x) length(grep("\xd2|\xd3",x,fixed=F,useBytes=T))) %>% sum()
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
######
#remove HTML tags! (these are in common core standards, adding italics and whatnot,
#but they're problematic and don't render in javascript)
#special problem is Common Core has footnotes (i.e. <sup>1</sup> referring to source 1)
#also sometimes <sup> refers to an exponent, which we want to keep...so first delete footnotes
#which fortunately always happen at the end of a "."
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>1</sup>","") #no reason to exponent 1 except as a footnote
allSubj$statement[1290] #it worked
allSubj$statement[1313] # example of footnote at end of sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1313]
#Still remaining problem of exponents that we actually want to keep
#example:
allSubj$statement[1603]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>","^")
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("</sup>","")
allSubj$statement[1603] #successfully changed "s<sup>2</sup>" to "s^2"
#Remaining problem--all other HTML tags...
#start with italics, which we can change to markdown
allSubj$statement[1603] # i and em tags need change to *text*
allSubj$statement<-allSubj$statement %>% stringr::str_replace_all(pattern="<[\\/?i|\\/?em]*>","*")
#check if it worked:
#show example:
allSubj$statement[1603]
#it did; but still leaving in special character codes like SQRT, i.e. "&radic;"
#Are there other HTML tags? Remove dem
allSubj$statement<-allSubj$statement %>% stringr::str_remove_all(pattern="<[^>]*>")
head(allSubj)
tail(allSubj)
#check
sum(sapply(list(c3,ela,math,sci,sdg),nrow))
nrow(allSubj)
#Reorganize column order
allSubj_final<-allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension,subcategory)
write.csv(allSubj_final,"allStandards.csv",row.names=F)
# Make Excel version
write.xlsx(allSubj_final,"allStandards.xlsx",row.names=F,freezePane=list(firstRow=T))
#save without overwriting formatting
XLConnect::setStyleAction(teachingMat,XLConnect::XLC$"STYLE_ACTION.NONE")
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#save backup
XLConnect::saveWorkbook(alignToStndz,"data/align_to_all_subject_standards_OLD.xlsx")
message("  *Old version of file saved at 'data/align_to_all_subject_standards_OLD.xlsx\n'")
#Clear old data and update "align to all subjects template"
deleteRange<-c(2,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 2,startCol=2,header=F)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
message("  @File 'align_to_all_subject_standards.xlsx' Cleared and updated")
##############
# Sustainable Development Goals
#############
download.file("https://raw.githubusercontent.com/datapopalliance/SDGs/master/SDG-targets.csv",destfile = "data/SDG-targets.csv")
download.file("https://raw.githubusercontent.com/datapopalliance/SDGs/master/SDG-indicators_proposed-2016-03-24.csv",destfile = "data/SDG-indicators_proposed-2016-03-24.csv")
sdg<-read.csv("data/SDG-targets.csv")
sdg_indicators<-read.csv("data/SDG-indicators_proposed-2016-03-24.csv")
?recode
sdg$subcategory=sapply(sdg$goal,function(x) switch(x,"1"="No Poverty","2"="Zero Hunger","3"="Good Health and Well-Being","4"="Quality Education","5"="Gender Equality","6"="Clean Water and Sanitation","7"="Affordable and Clean Energy","8"="Decent Work and Economic Growth","9"="Industry, Innovation, and Infrastructure","10"="Reduced Inequalities","11"="Sustainable Cities and Communities","12"="Responsible Consumption and Production","13"="Climate Action","14"="Life Below Water","15"="Life on Land","16"="Peace, Justice, and Strong Institutions","17"="Partnerships for the Goals"))
# sdg$dim<-gsub("[a-z]| |-|,","",sdg$subcat)
sdg$code2<-sdg$goal
rm(sdg$goal)
sdg<-sdg %>% select(-goal)
sdg
source('~/mrw_synced/R/Nonshiny Github/standardX/scripts/oneScriptToBindThem.R')
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)
full_join(sdg)
sdg
tibble(sdg)
# sdg$dim<-gsub("[a-z]| |-|,","",sdg$subcat)
sdg$code2<-as.character(sdg$goal)
sdg<-sdg %>% select(-goal)
sdg<-read.csv("data/SDG-targets.csv")
names(sdg)[c(2,3)]<-c("code","statement")
sdg$subcategory=sapply(sdg$goal,function(x) switch(x,"1"="No Poverty","2"="Zero Hunger","3"="Good Health and Well-Being","4"="Quality Education","5"="Gender Equality","6"="Clean Water and Sanitation","7"="Affordable and Clean Energy","8"="Decent Work and Economic Growth","9"="Industry, Innovation, and Infrastructure","10"="Reduced Inequalities","11"="Sustainable Cities and Communities","12"="Responsible Consumption and Production","13"="Climate Action","14"="Life Below Water","15"="Life on Land","16"="Peace, Justice, and Strong Institutions","17"="Partnerships for the Goals"))
# sdg$dim<-gsub("[a-z]| |-|,","",sdg$subcat)
sdg$code2<-as.character(sdg$goal)
sdg<-sdg %>% select(-goal)
c3$subject<-"Social Studies"
c3$set<-"C3"
ela$subject<-"ELA"
ela$set<-"Common Core ELA"
math$subject <- "Math"
math$set<-"Common Core Math"
sci$subject <- "Science"
sci$set<-"NGSS"
sdg$set <- "SDGs"
sdg$subject<-NA
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
allSubj
sci<-sci %>% select(-subcat)
# Export data -------------------------------------------------------------
######################
#Reorganize column order
allSubj_final<-allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension,subcategory,PEcode,performanceExpectation)
allSubj_final
?clearRange
# Save align to standards template ----------------------------------------
alignmentTemplate<-allSubj_final %>% mutate(ALIGN=NA) %>% relocate(ALIGN)
alignmentTemplate
?clearRange
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#Clear old data and update "align to all subjects template"
#deleteRange<-c(2,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearSheet(alignToStndz,sheet="Sheet1")
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 1,startCol=2,header=T)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
source('~/mrw_synced/R/Nonshiny Github/standardX/scripts/oneScriptToBindThem.R')
#save without overwriting formatting
XLConnect::setStyleAction(teachingMat,XLConnect::XLC$"STYLE_ACTION.NONE")
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#save without overwriting formatting
XLConnect::setStyleAction(teachingMat,XLConnect::XLC$"STYLE_ACTION.NONE")
require(openxlsx);require(tidyverse);require(stringi);require(stringr);require(XLConnect)
require(openxlsx);require(dplyr);require(stringr);require(XLConnect)# require(stringi);
require(openxlsx);require(dplyr);require(stringr);require(XLConnect)# require(stringi);
c3<-read.csv("data/formatted_c3SocialStudies.csv")
ela<-read.csv("data/formatted_CommonCoreELA.csv")
math<-read.csv("data/formatted_CommonCoreMath.csv")
sci<-read.csv("data/formatted_ngss.csv",quote="\"")
sdg<-read.csv("data/SDG-targets.csv")
names(sdg)[c(2,3)]<-c("code","statement")
sdg$subcategory=sapply(sdg$goal,function(x) switch(x,"1"="No Poverty","2"="Zero Hunger","3"="Good Health and Well-Being","4"="Quality Education","5"="Gender Equality","6"="Clean Water and Sanitation","7"="Affordable and Clean Energy","8"="Decent Work and Economic Growth","9"="Industry, Innovation, and Infrastructure","10"="Reduced Inequalities","11"="Sustainable Cities and Communities","12"="Responsible Consumption and Production","13"="Climate Action","14"="Life Below Water","15"="Life on Land","16"="Peace, Justice, and Strong Institutions","17"="Partnerships for the Goals"))
# sdg$dim<-gsub("[a-z]| |-|,","",sdg$subcat)
sdg$code2<-as.character(sdg$goal)
sdg<-sdg %>% select(-goal)
sci<-sci %>% select(-subcat)
c3$subject<-"Social Studies"
c3$set<-"C3"
ela$subject<-"ELA"
ela$set<-"Common Core ELA"
math$subject <- "Math"
math$set<-"Common Core Math"
sci$subject <- "Science"
sci$set<-"NGSS"
sdg$set <- "SDGs"
sdg$subject<-NA
allSubj<-full_join(c3,ela) %>% full_join(math) %>% full_join(sci) %>% full_join(sdg)%>% select(code,set,dim,grade,statement,everything())
#convert  #N/A to NA
allSubj<-apply(allSubj,c(1,2),function(x) ifelse(x=="#N/A",NA,x)) %>% as_tibble()
#example:
allSubj$statement[1290] #1 example of ^1 footnote in the middle of a sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>1</sup>","") #no reason to exponent 1 except as a footnote
allSubj$statement[1290] #it worked
allSubj$statement[1313] # example of footnote at end of sentence
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("\\.<sup>.<\\/sup>","\\.")
allSubj$statement[1313]
#example:
allSubj$statement[1603]
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("<sup>","^")
allSubj$statement <-allSubj$statement %>% stringr::str_replace_all("</sup>","")
allSubj$statement[1603] #successfully changed "s<sup>2</sup>" to "s^2"
#Remaining problem--all other HTML tags...
#start with italics, which we can change to markdown
allSubj$statement[1603] # i and em tags need change to *text*
allSubj$statement<-allSubj$statement %>% stringr::str_replace_all(pattern="<[\\/?i|\\/?em]*>","*")
#check if it worked:
#show example:
allSubj$statement[1603]
#Are there other HTML tags? Remove dem
allSubj$statement<-allSubj$statement %>% stringr::str_remove_all(pattern="<[^>]*>")
head(allSubj)
tail(allSubj)
#check
sum(sapply(list(c3,ela,math,sci,sdg),nrow))
nrow(allSubj)
# Export data -------------------------------------------------------------
######################
#Reorganize column order
allSubj_final<-allSubj %>% relocate(code,statement, set, dim, grade, subject,dimension,subcategory,PEcode,performanceExpectation)
# output CSV
write.csv(allSubj_final,"allStandards.csv",row.names=F)
# Make Excel version
write.xlsx(allSubj_final,"allStandards.xlsx",row.names=F,freezePane=list(firstRow=T))
# Save align to standards template ----------------------------------------
alignmentTemplate<-allSubj_final %>% mutate(ALIGN=NA) %>% relocate(ALIGN)
#save without overwriting formatting
XLConnect::setStyleAction(teachingMat,XLConnect::XLC$"STYLE_ACTION.NONE")
#Load template
alignToStndz<- XLConnect::loadWorkbook("align_to_all_subject_standards.xlsx")
#save without overwriting formatting
XLConnect::setStyleAction(alignToStndz,XLConnect::XLC$"STYLE_ACTION.NONE")
#save backup
XLConnect::saveWorkbook(alignToStndz,"data/align_to_all_subject_standards_OLD.xlsx")
message("  *Old version of file saved at 'data/align_to_all_subject_standards_OLD.xlsx\n'")
#Clear old data and update "align to all subjects template"
deleteRange<-c(2,1,nrow(allSubj_final)+1000,ncol(allSubj_final)+10) #added extra in case we ever simplify the data set...it all gets delted
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 1,startCol=2,header=T)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
e
XLConnect::clearRange(alignToStndz,sheet="Sheet1",coords=deleteRange)
XLConnect::writeWorksheet(alignToStndz,data=allSubj_final,sheet="Sheet1",startRow = 1,startCol=2,header=T)
XLConnect::saveWorkbook(alignToStndz,"align_to_all_subject_standards.xlsx")
XLConnect::saveWorkbook(alignToStndz,"align-to-all-subject-standards.xlsx")
jekyll build