dashboard.qmd

---
title: "openwashdata analytics"
format: 
  dashboard:
    scrolling: true
    css: style/dashboard.css
    theme:
      light: [litera, style/theme.scss]
    highlight-style: a11y-dark
    code-copy: true
    code-overflow: wrap
    toc: true
    mainfont: "Atkinson Hyperlegible"
website:
  output-dir: docs
---

# About {scrolling="true"}

The data science for open wash data (ds4owd) course is held by the [openwashdata team](https://openwashdata.org/about) at the [Global Health Engineering Group](https://ghe.ethz.ch/) of [ETH Zurich](ethz.ch) 

The aim of the course is empower WASH professionals to engage with tools and workflows for open data and code. This course is: 

- free 
- provides participants with a certificate for successful completion
- using exclusively tools that are free and open source
- offers 1:1 coding support between lectures and beyond the course

You can find all details about this course and how to sign-up for it [here](https://ds4owd-001.github.io/website/)

The aim of this website is to measure the impact of ds4owd and present collected data to encourage open data sharing practices.

# Website Analytics {scrolling="true"}

## The ds4owd [website](https://ds4owd-001.github.io/website/) provides an overview of the course and access to all necessary resources Data generated from visits to the website is presented here.

```{r}
library(tidyverse)
library(plotly)
library(ggplot2)
library(ggthemes)
library(rnaturalearth)
library(rnaturalearthdata)
library(jsonlite)
library(showtext)
library(RPostgres)
```

```{r}
HOST <- Sys.getenv("DB_HOST")
PORT <- Sys.getenv("DB_PORT")
DBNAME <- Sys.getenv("DB_NAME")
user <- Sys.getenv("DB_USER")
password <- Sys.getenv("DB_PASSWORD")
con <- dbConnect(Postgres(), host = HOST, port = PORT, dbname = DBNAME, user = user, password = password)
# Load datasets
ds4owd_timeseries <- dbFetch(dbSendQuery(con, 'SELECT * FROM "ds4owd-001_timeseries_data";'))
ds4owd_country <-  dbFetch(dbSendQuery(con, 'SELECT * FROM "ds4owd-001_country_data";'))
ds4owd_source <-  dbFetch(dbSendQuery(con, 'SELECT * FROM "ds4owd-001_source_data";'))
postsurvey <- dbFetch(dbSendQuery(con, "SELECT * FROM postsurvey;"))
presurvey <- dbFetch(dbSendQuery(con, "SELECT * FROM presurvey;"))
locations <- dbFetch(dbSendQuery(con, "SELECT * FROM locations;"))
positcloud <- dbFetch(dbSendQuery(con, 'SELECT * FROM "pscloud";'))
```

```{r}
calculate_descriptive_stats <- function(data) {
  data <- data %>% filter(visit_duration != 0)
  total_unique_visitors <- as.integer(sum(data$visitors, na.rm = TRUE))
  total_pageviews <- as.integer(sum(data$pageviews, na.rm = TRUE))
  total_visits <- as.integer(sum(data$visits, na.rm = TRUE))
  mean_visit_duration <- mean(data$visit_duration, na.rm = TRUE) / 60
  bounce_rate <- mean(data$bounce, na.rm = TRUE)

  data.frame(
    Metric = c("tot_unique_visitors", "tot_pageviews", "tot_visits", "mean_minutes", "bounce"),
    Value = c(round(total_unique_visitors, 0), round(total_pageviews, 0), round(total_visits, 0), round(mean_visit_duration, 2), round(bounce_rate, 2))
  ) %>% pivot_wider(names_from = Metric, values_from = Value)
}

group_data <- function(data, group_by) {
  if (group_by == "Week") {
    data %>%
      mutate(week = floor_date(date, unit = "week")) %>%
      group_by(week) %>%
      summarize(visitors = sum(visitors), .groups = 'drop') %>%
      rename(date = week)
  } else if (group_by == "Month") {
    data %>%
      mutate(month = floor_date(date, unit = "month")) %>%
      group_by(month) %>%
      summarize(visitors = sum(visitors), .groups = 'drop') %>%
      rename(date = month)
  } else {
    data
  }
}

group_by <- "Day"  # Change this to "Day" or "Month" as needed

# Group the data based on user input
grouped_data <- group_data(ds4owd_timeseries, "Day")

stats <- calculate_descriptive_stats(ds4owd_timeseries)
```

## Overview

## This presents the overall website activity at a glance.

```{r}
#| content: valuebox
#| title : "Total Unique Visitors"
list(
  icon = "people",
  color = "primary",
  value = stats$tot_unique_visitors
)
```

```{r}
#| content: valuebox
#| title : "Total Page Views"
list(
  icon = "eye",
  color = "primary",
  value = stats$tot_pageviews
)
```

```{r}
#| content: valuebox
#| title : "Total Visits"
list(
  icon = "activity",
  color = "primary",
  value = stats$tot_visits
)
```

```{r}
#| content: valuebox
#| title : "Visit Duration (mins)"
list(
  icon = "clock",
  color = "primary",
  value = stats$mean_minutes
)

```


## Website Activity

## The graph below showcases a time series of daily visits to the website along with the top sources. Direct visits are the most common source of traffic. 

```{r}
#| fig-width: 5 
#| fig-height: 2
#| title: "Daily Visits"
ds4owd_timeseries$date <- as.Date(ds4owd_timeseries$date)
timeseries_plot <- ggplot(data = ds4owd_timeseries, aes(x = date, y = visits)) +
  geom_line() +  
  theme_minimal() + 
  labs(title = "Daily Visits", x="Date", y="Visits") +
  ylim(0, max(ds4owd_timeseries$visits + 50)) +
  scale_x_date(date_breaks = "3 months", date_labels = "%b %Y")  # Tick every 3 months, formatted as "Month Year"

# Convert to a plotly object for interactivity
ggplotly(timeseries_plot)
```

```{r}
#| title: "Top 10 Sources"
sources_table <- ds4owd_source %>% 
  arrange(desc(visits)) %>% 
  head(10) %>% 
  select(source, visits) %>% 
  rename("Source" = source, "Visits" = visits) %>% 
  knitr::kable()
sources_table
```

## Visits by Country

```{r}
#| fig-width: 14
#| fig-height: 10
#| title: "Worldwide Visits"
# Take country and visitors columns
ds4owd_country_vists <- ds4owd_country %>% select(country, visits)

world <- ne_countries(scale = "medium", returnclass = "sf")

# Add visits column to world data. Set to 0 if no data available in ds4owd_country
world_data <- world %>%
  mutate(visits = ifelse(is.na(ds4owd_country_vists$visits[match(iso_a2, ds4owd_country$country)]), 0, ds4owd_country_vists$visits[match(iso_a2, ds4owd_country$country)]))

# Set 0 values to NA
world_data$visits[world_data$visits == 0] <- NA

# Create the plot using ggplot
country_plot <- ggplot(world_data) +
  geom_sf(aes(fill = visits)) +
  scale_fill_gradient(low = "lightblue", high = "darkblue", na.value = "grey90") +
  labs(title = "",
       fill = "Visits") +
  theme_minimal() + 
  theme(axis.text = element_blank(),
        axis.title = element_blank(),
        axis.ticks = element_blank(),
        panel.grid = element_blank())

ggplotly(country_plot)
```

# Course Surveys {scrolling="true"}

**Data Science For Open Wash Data Course Survey**

```{r}
presurvey <- presurvey %>% mutate(prog_exp_r_num = case_when(
  prog_exp_r == "I have none." ~ 1,
  prog_exp_r == "I have written a few lines now and again." ~ 2,
  prog_exp_r == "I have written programs for my own use that are a couple of pages long." ~ 3,
  prog_exp_r == "I have written and maintained larger pieces of software." ~ 4
  )
)

avg_pre_r_comp <- mean(as.integer(presurvey$prog_exp_r_num), na.rm = TRUE)
```

## Locations

```{r}
#| fig-width: 8
#| fig-height: 6
#| title: "Where do Participants Come From?"
countries <- locations %>% select(iso_country) %>% 
  group_by(iso_country) %>% 
  summarise(participants = n())

world <- ne_countries(scale = "medium", returnclass = "sf")

# Add visits column to world data. Set to 0 if no data available in countries
world_data <- world %>%
  mutate(participants = ifelse(is.na(countries$participants[match(adm0_iso, countries$iso_country)]), 0, countries$participants[match(adm0_iso, countries$iso_country)]))

# Set 0 values to NA
world_data$participants[world_data$participants == 0] <- NA

# Create the plot using ggplot
country_plot <- ggplot(world_data) +
  geom_sf(aes(fill = participants)) +
  scale_fill_gradient(low = "lightblue", high = "darkblue", na.value = "grey90") +
  labs(title = "",
       fill = "Participants") +
  theme_minimal() + 
  theme(axis.text = element_blank(),
        axis.title = element_blank(),
        axis.ticks = element_blank(),
        panel.grid = element_blank())

ggplotly(country_plot)
```

## Overview Text

What do they think of the course?

## Overview

```{r}
# Completion Rate
completion_rate <- postsurvey %>% filter(course_completion  %in% c(7,8,9,10)) %>% nrow() / nrow(postsurvey) * 100

# Overall average rating for the course
avg_course_rating <- mean(as.integer(postsurvey$rating_overall), na.rm = TRUE)

# Calculate participants where course met or exceeded expectations
exceeded_expectations <- postsurvey %>%
  filter(expectations %in% c("Yes, it exceeded my expectations", "Yes, it met my expectations")) %>%
  nrow() / nrow(postsurvey) * 100

# Rating for the structure of the course
avg_structure_rating <- mean(as.integer(postsurvey$rating_structure), na.rm = TRUE)

# Average instructor competency rating
avg_ins_rating_comp <- mean(as.integer(postsurvey$rating_ins_comp), na.rm = TRUE)

# Average competency rating for r and version control
avg_r_comp <- mean(as.integer(postsurvey$rating_self_r_comp), na.rm = TRUE)
avg_vc_comp <- mean(as.integer(postsurvey$rating_self_vc_comp), na.rm = TRUE)
avg_conf_skill <- mean(as.integer(postsurvey$conf_skill_app), na.rm = TRUE)


# Cases where learning objectives were achieved
learning_objectives_achieved <- postsurvey %>%
  filter(learning_objectives %in% c("Yes")) %>%
  nrow() / nrow(postsurvey) * 100
```

```{r}
#| content: valuebox
#| title : "Completion Rate"
list(
  icon = "bookmark-check",
  color = "success",
  value = paste0(completion_rate,"%")
)
```

```{r}
#| content: valuebox
#| title : "Overall Course Rating"

list(
  icon = "star-fill",
  color = "success",
  value = paste0(avg_course_rating," / 4")
)
```

```{r}
#| content: valuebox
#| title: "Instructor Rating"
list(
  icon = "star-fill",
  color = "success",
  value = paste0(avg_ins_rating_comp, " / 4")
)
```

```{r}
#| content: valuebox
#| title: "Course Structure Rating"
list(
  icon = "star-fill",
  color = "success",
  value = paste0(avg_structure_rating, " / 4")
)
```

## Expectations - Title

How did Participants Benefit?

## Expectations

```{r}
expectations <- postsurvey %>% 
  select(expectations) %>%
  # change the values to No, Yes, Exceeded Expectations 
  mutate(expectations = case_when(
    expectations == "Yes, it met my expectations" ~ "Met Expectations",
    expectations == "Yes, it exceeded my expectations" ~ "Exceeded Expectations",
    TRUE ~ "Did not meet Expectations"
  ))
# Table of each expectation value and its count 
expectations_table <- expectations %>% 
  group_by(expectations) %>% 
  summarise(count = n()) %>%
  arrange(desc(count))
```

### Column - Expectations {width="37.5%"}

```{r}
#| fig-width: 5
#| fig-height: 3
#| cap-location: "top"
#| title: "Did the course meet expectations?"

# Create a bar plot of the expectations
expectation_plot <- ggplot(expectations_table, aes(x = reorder(expectations, count, decreasing=TRUE), y = count)) +
  geom_bar(stat = "identity", fill="white", color="black") +
  labs(title = "", x = "", y = "") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 0, hjust = 0.5)) + 
  ylim(0,max(expectations_table$count) + 3)

# Convert to an interactive plot
expectation_plot
```

### Column - Skill Evolution {width="37.5%"}

```{r}
#| fig-width: 5
#| fig-height: 3
#| cap-location: "top"
#| title: "How did R programming skills evolve?" 

# Line plot with two dots that goes from average pre-survey r competency to post rurvey r competency
r_comp_plot <- ggplot() +
  geom_point(aes(x = 1, y = avg_pre_r_comp), color = "blue", size = 3) +
  geom_point(aes(x = 2, y = avg_r_comp), color = "red", size = 3) +
  geom_line(aes(x = c(1, 2), y = c(avg_pre_r_comp, avg_r_comp)), color = "black") +
  geom_segment(aes(x = 1.5, xend = 1.5, y = 1, yend = 4), linetype = "dashed", color = "gray") + 
  geom_text(aes(x = 1, y = avg_pre_r_comp, label = round(avg_pre_r_comp, 1)), 
            vjust = -1, color = "blue") +  # Label for before
  geom_text(aes(x = 2, y = avg_r_comp, label = round(avg_r_comp, 1)), 
            vjust = -1, color = "red") +  # Label for after
  labs(title = "", x = "", y = "") +
  theme_minimal() +
  theme(axis.text.x = element_blank(),
        axis.title.x = element_blank(),
        axis.ticks.x = element_blank(),
        panel.grid = element_blank()) + 
  ylim(1, 4)

r_comp_plot
```

### Column - Skill ratings {width="25%"}

How did they feel after the course?

```{r}
#| content: valuebox
#| title: "Confidence in R skills"

list(
  icon = "arrow-up-right-circle-fill",
  color = "info",
  value = paste0(avg_r_comp, " / 4")
)
```

```{r}
#| content: valuebox
#| title: "Confidence in Version Control"

list(
  icon = "arrow-up-right-circle-fill",
  color = "info",
  value = paste0(avg_vc_comp, " / 4")
)
```

```{r}
#| content: valuebox
#| title: "Confidence in Applying Skills"
list(
  icon = "arrow-up-right-circle-fill",
  color = "info",
  value = paste0(avg_conf_skill, " / 4")
)
```

# Posit Cloud {scrolling="true"}

```{r}
# Daily Compute Usage
positcloud_daily <- positcloud %>% 
  filter(active_projects > 0) %>%
  group_by (from) %>% 
  summarise(mean_compute = mean(compute, na.rm = TRUE))
positcloud_daily$from <- as.Date(positcloud_daily$from)
```

As a part of the course, learners were introduced to Posit cloud to encourage open data sharing practices and provide a platform for sharing their work.

## Overview

```{r}
#| content: valuebox
#| title : "Total Active Users"
list(
  icon = "people",
  color = "primary",
  value = unique(positcloud$user_id) %>% length()
)
```

```{r}
#| content: valuebox
#| title : "Total Active Projects"
list(
  icon = "activity",
  color = "primary",
  value = positcloud %>% group_by(user_id) %>% 
    summarize(act_project = max(active_projects)) %>% 
    select(act_project) %>%
    sum()
  )

```

```{r}
#| content: valuebox
#| title : "Total Compute Hours Logged"
list(
  icon = "clock",
  color = "primary",
  value = round(sum(positcloud$compute),0)
)
```

## Daily Usage Plot

```{r}
#| fig-width: 5
#| fig-height: 3
#| title: "Daily Posit Cloud Compute Usage (Hours)"
#| cap-location: "top"

positcloud_plot <- ggplot(positcloud_daily, aes(x = from, y = mean_compute, group=1)) +
  geom_line() +
  labs(title = "", x = "", y = "") +
  theme_minimal() +
  theme(axis.text.x = element_text(angle = 0, hjust = 1)) + 
  ylim(0, max(positcloud_daily$mean_compute)+1) + 
  scale_x_date(date_labels = "%Y-%m", date_breaks = "3 months")
ggplotly(positcloud_plot)
```