DATA 607 Project 2 Beshkia Kvarnstrom

colnames(Test_Score_Data) <- c("ID", "NAME", "PHONE", "GENDER", "AGE", "TEST NO.", "TERM", "TEST SCORE")
colnames(Test_Score_Data) <- c("ID", "NAME", "PHONE", "GENDER", "AGE", "TEST NO.", "TERM", "TEST SCORE")
Test_Score_Data%>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

# Analyzing the test score to determine the minimum, maximum, mean and median test scores for each term by gender. From the analysis you can see that females did better than males overall.
Agg_TestScores <- Test_Score_Data %>% group_by(GENDER) %>% group_by(GENDER, TERM) %>%
            summarise(
                      "MIN TEST SCORE" = min(`TEST SCORE`), 
                      "MAX TEST SCORE" = max(`TEST SCORE`),
"MEAN TEST SCORE" = mean(`TEST SCORE`),              "MEDIAN TEST SCORE" = median(`TEST SCORE`),     
                     .groups = 'drop'
                    )

Agg_TestScores%>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

# The following plot compares the test scores for each gender and the terms taken. 
ggplot(data = Agg_TestScores) +
  geom_bar(mapping = aes(x = TERM, y = `MEAN TEST SCORE`, fill = GENDER), stat = 'identity') +
  facet_grid(~ GENDER) +
  theme(axis.text.x = element_text(angle = 70, hjust = 1, size=5)) +
  labs(title = 'AVERAGE TEST SCORES PER TERM', subtitle = "By Gender")

# This visualition displays the test scores per student for each term
ggplot(data = Test_Score_Data) +
  geom_bar(mapping = aes(x=NAME, y=`TEST SCORE`, fill = `TERM`), stat = 'identity') +
  facet_grid(~ `TEST NO.`) +
  theme(axis.text.x = element_text(angle = 70, hjust = 1, size=5)) +
  labs(title = 'STUDENT TEST SCORES' , subtitle = "By Term")

Int_Rate_Data <- read_csv('https://raw.githubusercontent.com/BeshkiaKvarnstrom/MSDS-DATA607/main/interest_rate_of_banks.csv', 
                          show_col_types = FALSE)

# Use the tidyr function gather() to unpivot data from a "wide" to a "long" format
Int_Rate_df <-gather(Int_Rate_Data, bank_name ,'interest_rate',2:8)

#Remove columns with interest rates NA value
Int_Rate_df <- Int_Rate_df[!apply(is.na(Int_Rate_df[3:3]),1,all), ]


Int_Rate_df%>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

Avg_Int_Rate_df <- Int_Rate_df %>% group_by(bank_name) %>% summarize(`average interest rate` = mean(`interest_rate`))

head(Avg_Int_Rate_df)  %>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

ggplot(data=Avg_Int_Rate_df, aes(x=bank_name,y=`average interest rate`, group=1)) +
  geom_line(arrow = arrow(), color = "purple",size = 1.2)+
  geom_point(color = "green", size = 3)+
  theme(axis.text.x=element_text(angle = 45, vjust = 0.5)) +
  scale_fill_brewer(palette="Paired") + 
  ggtitle("Average Interest Rate Per Bank") +
  theme(plot.title = element_text(hjust = 0.5)) +
  geom_text(aes(label=paste0(round(`average interest rate`,2))), vjust=-1, color="black", position = position_dodge(0.9), size=3.5) 

# The following plot compares the interest year across the years

ggplot(data = Avg_Int_Rate_df) +
  geom_bar(mapping = aes(x=bank_name,y=`average interest rate`, fill = bank_name), stat = 'identity') +
  theme(axis.text.x = element_text(angle = 70, hjust = 1, size=5)) +
  labs(title = 'AVERAGE INTEREST RATE')

# Each borough is used to separate the data in the file. This separation is identified by a row entry for each borough at the beginning of the respective borough's data set. We need to identify the row numbers for each borough.
row_b <- c('The Bronx', 'Brooklyn', 'Manhattan', 'Queens')

rownum <- c()

for(i in 1:length(row_b)){
  rownum[i] <- rownames(NYC_Subway_Data[which(NYC_Subway_Data$'2013' == row_b[i]),])
}

d = NULL
for(i in 1:length(rownum)){

  dat  <- NYC_Subway_Data[as.integer(rownum[i])+1:ifelse(!is.na(as.integer(rownum[i+1])-1),as.integer(rownum[i+1])-1,dim(NYC_Subway_Data)[1]),]

  k <- as.integer(rownum[i])+1
  l<-ifelse(!is.na(as.integer(rownum[i+1])-1),as.integer(rownum[i+1])-1,dim(NYC_Subway_Data)[1])         
  
 if (i == 1){
    Bronx <- NYC_Subway_Data[k:l,]
    Bronx['Borough'] <- row_b[1]
 } else if (i == 2) {
    Brooklyn <- NYC_Subway_Data[k:l,]
    Brooklyn['Borough'] <- row_b[2]
 }else if (i == 3) {
    Manhattan <- NYC_Subway_Data[k:l,]
    Manhattan['Borough'] <- row_b[3]
 } else if (i == 4) {
    Queens <- NYC_Subway_Data[k:l,]
    Queens['Borough'] <- row_b[4]
 }

}

# combined all sub datasets
NYC_Subway_Data2 <- rbind(Bronx, Brooklyn, Manhattan, Queens)
NYC_Subway_Data2%>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

NYC_Subway_Data2 <- NYC_Subway_Data2 %>%
  mutate('2013' = as.integer(str_remove_all(NYC_Subway_Data2$'2013', ',')),
         '2014' = as.integer(str_remove_all(NYC_Subway_Data2$'2014', ',')),
         '2015' = as.integer(str_remove_all(NYC_Subway_Data2$'2015', ',')),
         '2016' = as.integer(str_remove_all(NYC_Subway_Data2$'2016', ',')),
         '2017' = as.integer(str_remove_all(NYC_Subway_Data2$'2017', ',')),
         '2018' = as.integer(str_remove_all(NYC_Subway_Data2$'2018', ',')),
         '2017-2018 Change' = as.integer(str_remove_all(NYC_Subway_Data2$'2017-2018 Change', ',')),
         '2017-2018 Change2' = as.numeric(str_remove_all(NYC_Subway_Data2$'2017-2018 Change2', '%')),
         '2018 Rank' = as.integer(NYC_Subway_Data2$'2018 Rank')) %>%
select(Borough, colnames(NYC_Subway_Data2))%>%
na.omit()

NYC_Subway_Data2%>% 
head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>%  scroll_box(height = "300px", width = "100%")

# Use the tidyr function gather() to unpivot data from a "wide" to a "long" format
Subway_df <-gather(NYC_Subway_Data2, Year ,'total riders',3:11)

colnames(Subway_df) <- c('Borough', 'Subway Station', 'Year', 'Total Riders')
Subway_df%>% 
  head(20) %>%
  kable() %>% 
  kable_styling(bootstrap_options = "striped", font_size = 12, fixed_thead = T) %>% 
  scroll_box(height = "300px", width = "100%")

Subway_df <- na.omit(Subway_df)

sum_riders <- Subway_df %>%
  group_by(Year, Borough) %>%
  summarize(
    min_riders = min(`Total Riders`),
    avg_riders = mean(`Total Riders`),
    max_riders = max(`Total Riders`),
    .groups = "drop"
  )

ggplot(data = sum_riders, aes(x = Year, y = avg_riders, fill = Borough)) +
  geom_bar(stat = "identity", position = "stack") +
  labs(title = 'Average NYC Subway Ridership', subtitle = "By Borough (2013 - 2018)") +
 scale_y_continuous(labels = comma)+  # Format y-axis labels with commas
 
  xlab("Ridership Year") +  # Update x-axis label
  theme(axis.text.x = element_text(angle = 70, hjust = 1))