Credential Data

### Read the WCCC Data file

WCCC<- read.csv("CC_Data.csv")
WCCC$CIP<- as.character(WCCC$CIP)

### Read the UW data from the multiple tab excel file
UW1617<- read_excel("UW_Data.xlsx", sheet = "2016-17")


UW1718<- read_excel("UW_Data.xlsx", sheet = "2017-18")
UW1718<- UW1718 %>%
  select(ID, Institution, TermCode, DegreeType, PROGRAM_DESC, CIP)

UW1819<- read_excel("UW_Data.xlsx", sheet = "2018-19")
UW1819<- UW1819 %>%
  select(ID, Institution, TermCode, DegreeType, PROGRAM_DESC, CIP)

UW1920<- read_excel("UW_Data.xlsx", sheet = "2019-20")
UW1920<- UW1920 %>%
  select(ID, Institution, TermCode, DegreeType, PROGRAM_DESC, CIP)

UW2021<- read_excel("UW_Data.xlsx", sheet = "2020-21")

UW2122<- read_excel("UW_Data.xlsx", sheet = "2021_22")


#Bind UW Data into one database

All_UW<- bind_rows(UW1617, UW1718, UW1819, UW1920, UW2021, UW2122)%>%
  select(ID, Institution, TermCode, DegreeType, PROGRAM_DESC, CIP)

### Create counts like WCCC file

UW_Count<- All_UW %>%
  group_by(Institution, TermCode, DegreeType, PROGRAM_DESC, CIP) %>%
  summarize(Freq=n())

UW_Count<- UW_Count %>%
  rename("NumberOfGraduates" = Freq)

### Bind UW data to WCCC data for all wyoming files

All_Wyoming<- bind_rows(WCCC, UW_Count)

### Code an Academic Year Variable
All_Wyoming<- All_Wyoming%>%
  subset(TermCode %in% c("16/FA", "17/SP", "17/SU", "17/FA", "18/SP", "18/SU", "18/FA", "19/SP", "19/SU", "19/FA", "20/SP", "20/SU", "20/FA", "21/SP", "21/SU", "21/FA", "22/SP", "22/SU")) %>%
 mutate(Year = case_when(
                         TermCode == "16/FA" ~ "2016-17",
                         TermCode == "17/SP" ~ "2016-17",
                         TermCode == "17/SU" ~ "2016-17",
  
                         TermCode == "17/FA" ~ "2017-18",
                         TermCode == "18/SP" ~ "2017-18",
                         TermCode == "18/SU" ~ "2017-18",
                         
                         TermCode == "18/FA" ~ "2018-19",
                         TermCode == "19/SP" ~ "2018-19",
                         TermCode == "19/SU" ~ "2018-19",
                         
                         TermCode == "19/FA" ~ "2019-20",
                         TermCode == "20/SP" ~ "2019-20",
                         TermCode == "20/SU" ~ "2019-20",
                         
                         TermCode == "20/FA" ~ "2020-21",
                         TermCode == "21/SP" ~ "2020-21",
                         TermCode == "21/SU" ~ "2020-21",
                         
                         TermCode == "21/FA" ~ "2021-22",
                         TermCode == "22/SP" ~ "2021-22",
                         TermCode == "22/SU" ~ "2021-22"))

### Simplify7 file to these variables 
All_Wyoming<- select(All_Wyoming, Institution, TermCode, Year, DegreeType, PROGRAM_DESC, CIP, NumberOfGraduates)


### Spread/pivot the data so each term is a different row
ByTerm<- select(All_Wyoming, Institution, TermCode,DegreeType, PROGRAM_DESC, CIP, NumberOfGraduates)
ByTerm<- spread(ByTerm, key=TermCode, value = NumberOfGraduates)

##Re-order the data set
ByTerm<- ByTerm %>%
  select(Institution, DegreeType, PROGRAM_DESC, CIP, "16/FA", "17/SP", "17/SU", "17/FA", "18/SP", "18/SU", "18/FA", "19/SP", "19/SU", "19/FA", "20/SP", "20/SU", "20/FA", "21/SP", "21/SU", "21/FA", "22/SP", "22/SU")

### Calculate cusom Sum Variables
ByTerm<- ByTerm %>%
  replace(is.na(.), 0)

ByTerm<- ByTerm %>%
  mutate("Total_16_17" = rowSums(across(5:7))) %>%
  mutate("Total_17_18" = rowSums(across(8:10))) %>%
  mutate("Total_18_19" = rowSums(across(11:13))) %>%
  mutate("Total_19_20" = rowSums(across(14:16))) %>%
  mutate("Total_20_21" = rowSums(across(17:19))) %>%
  mutate("Total_21_22"= rowSums(across(20:22))) %>%
  mutate("Grand_Tot" = rowSums(across(5:22)))

export<- list("termrow" = All_Wyoming, "termcolumn" = ByTerm)

write.xlsx(export, file="Wyoming_Degrees.xlsx")