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"
    # Fix for graph resizing
    options:
      height: auto
      width: auto
website:
  output-dir: docs
execute:
  freeze: auto  # Ensures outputs are cached for consistency
---

# 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)
library(wordcloud)
library(tm)
library(RColorBrewer)
```

```{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";'))
metadata <- dbFetch(dbSendQuery(con, 'SELECT * FROM "publishing_metadata";' ))
```

```{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 Text

This presents the overall website activity at a glance.

## Overview

```{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 Text 

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. 

## Website Activity

```{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 Text 

Our visits come from around the world with a majority from Switzerland, Malawi, and the United States of America.

## 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 = "#C292C2", high = "#511852", 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) |> layout(autosize = TRUE) # Fixes graph sizing issues when responsive graph doesn't fully load.
```

# 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 Text

Course participants joined us from all over the globe! We had the most participants from Nigeria, Malawi, India, and the United States of America.

## 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 = "#C292C2", high = "#511852", 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) |> layout(autosize = TRUE) # Fixes graph sizing issues when responsive graph doesn't fully load.
```

## Pre-Course Survey Text

Participants' experience ranged from no experience to significant programming expertise, with most participants tending towards the former. 

## Pre-Course Survey 

### Programming Experience

```{r}
# Bar chart of the prog_exp column from presurvey

# Mapping the original text to shorter versions
short_labels <- c(
  "I have none." = "No experience",
  "I have written a few lines now and again." = "Written a few lines",
  "I have written and maintained larger pieces of software." = "Maintained large code",
  "I have written programs for my own use that are a couple of pages long." = "Written multi-page programs"
)

# Apply the mapping and convert to factors
presurvey <- presurvey |> mutate(
  prog_exp = factor(recode(prog_exp, !!!short_labels), # Recode using the !!! operator. This is used to unquote the list which is otherwise interpreted as one argument.
                    levels = c("No experience", "Written a few lines", "Maintained large code", "Written multi-page programs")),
  prog_exp_r = factor(recode(prog_exp_r, !!!short_labels), 
                      levels = c("No experience", "Written a few lines", "Maintained large code", "Written multi-page programs"))
)

prog_exp_plot <- ggplot(presurvey, aes(x = prog_exp)) +
  geom_bar(fill = "#511852", color = "black") +
  theme_minimal() +
  labs(title = "Experience in Programming", 
       x = "", 
       y = "") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        axis.ticks.y = element_blank(),
        axis.text.y = element_blank())

ggplotly(prog_exp_plot) |> layout(autosize = TRUE)

```

### R Experience

```{r}

r_exp_plot <- ggplot(presurvey, aes(x = prog_exp_r)) +
  geom_bar(fill = "#511852", color = "black") +
  theme_minimal() +
  labs(title = "Experience with R", 
       x = "", 
       y = "") +
  theme(axis.text.x = element_text(angle = 45, hjust = 1),
        axis.ticks.y = element_blank(),
        axis.text.y = element_blank())

ggplotly(r_exp_plot) |> layout(autosize = TRUE)

```

## Overview Text

What do they think of the course? 

- Most participants completed the course and enjoyed it. 
- There were some suggestions to improve the structure 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")
)
```

## Overview - Sentiment - Text

What did they say about the course?

## Overview - Sentiment 

```{r, fig.width=10, fig.height=4}

comments <- postsurvey |> select(most_liked)
corpus <- Corpus(VectorSource(comments))
remove_words <- c("data", "just", "fact", "one", "coming", "room","using", "ive","canada", "verbs", "instead", "lars")

corpus <- tm_map(corpus, content_transformer(tolower))  # Convert to lowercase
corpus <- tm_map(corpus, removePunctuation)            # Remove punctuation
corpus <- tm_map(corpus, removeNumbers)                # Remove numbers
corpus <- tm_map(corpus, removeWords, stopwords("en")) # Remove common stopwords
corpus <- tm_map(corpus,removeWords,remove_words)

# Step 3: Create a term-document matrix
tdm <- TermDocumentMatrix(corpus)
tdm_matrix <- as.matrix(tdm)

# Get word frequencies
word_freq <- sort(rowSums(tdm_matrix), decreasing = TRUE)
word_freq_df <- data.frame(word = names(word_freq), freq = word_freq)

# Step 4: Generate the word cloud
set.seed(1234)
wordcloud(words = word_freq_df$word, 
          freq = word_freq_df$freq, 
          min.freq = 1, 
          max.words = 30, 
          random.order = FALSE, 
          rot.per = 0.25, 
          colors = brewer.pal(8, "Dark2"))
```

## Skill Ratings Text

After the course, we asked participants to tell us how confident they were about applying the skills they gained

## Skill ratings

```{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(daily_compute = 2 * sum(compute, na.rm = TRUE)) # Compute is half of total hours of usage (default)
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.

The space proved to be extremely useful with over 200 active projects from 138 participants, removing barriers to barriers to usage.

## 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 Hours Spent Using the Space"
list(
  icon = "clock",
  color = "primary",
  value = round(sum(positcloud$compute * 2),0)
)
```

## Daily Usage Plot Text

Daily usage spiked through the duration of the course, with up to 160 hours of usage per day. Since the end of the course, we see continued usage and the creation of new projects. 

## 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 = daily_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$daily_compute)+1) + 
  scale_x_date(date_labels = "%Y-%m", date_breaks = "3 months")
ggplotly(positcloud_plot) |> layout(autosize = TRUE)
```

# Metadata {scrolling="true"}

The openwashdata course led to the publication of datasets relating to WASH and SDGs. These datasets cover a wide variety of topics and were provided by course participants as a part of their final project. We publish these datasets in a standard format and provide open access to encourage open data practices. The datasets are published using the [washr package](https://cran.r-project.org/web/packages/washr/index.html) developed by the openwashdata team. The full list of datasets can be found [here](https://openwashdata.org/pages/gallery/data/)

```{r}
n_published <- metadata |> 
  filter(status == "published") |> 
  summarise(n_published = n()) |> 
  pull(n_published)

unique_maintainers_count <- metadata |> 
  summarise(unique_maintainers = n_distinct(maintainer)) |> 
  pull(unique_maintainers)

unique_locations_count <- metadata |> 
  mutate(location = if_else(location %in% c("Global", "Worldwide"), "Global", location)) |> 
  summarise(unique_locations = n_distinct(location)) |> 
  pull(unique_locations)

```


## Overview

```{r}
#| content: valuebox
#| title : "Published Datasets"
list(
  icon = "database",
  color = "primary",
  value = n_published
)
```

```{r}
#| content: valuebox
#| title : "Dataset maintainers"
list(
  icon = "person-heart",
  color = "primary",
  value = unique_maintainers_count
)
```

```{r}
#| content: valuebox
#| title : "Number of unique locations covered"
list(
  icon = "globe",
  color = "primary",
  value = unique_locations_count
)
```

## Plots Text

Our data comes from various sources and presents varying levels of difficulty characterised by initial "tidyness" of data. To learn more about tidy data see vist: https://r4ds.had.co.nz/tidy-data.html. While this is a small sample, academically published data is easier to locate.

## Plots Data Sources and Difficulty

```{r, fig.width=8, fig.height=6}

metadata <- metadata |> mutate(
  source = ifelse(is.na(source), "others", source),
  difficulty = ifelse(is.na(difficulty), "low", difficulty)
)

source_plot <- ggplot(metadata, aes(x = source, fill = difficulty)) +
  geom_bar(position = "dodge", width = 0.7) +
  labs(
    title = "Data Sources and Difficulty",
    x = "Source",
    y = "",
    fill = "Difficulty"
  ) +
  theme_minimal(base_size = 14) +
  theme(
    axis.text.x = element_text(angle = 45, hjust = 1),
    legend.position = "top",
    plot.title = element_text(face = "bold", size = 16)
  ) +
  scale_fill_brewer(palette = "Set2")

ggplotly(source_plot)

```

## Keywords Text

Our datasets cover several topics, they're primarily related to WASH.

## Keywords
```{r, fig.width=8, fig.height=8}

keyword_freq <- metadata |> 
  mutate(keyword = strsplit(keywords, ",\\s*")) |> 
  unnest(keyword) |> 
  filter(!is.na(keyword)) |>
  count(keyword, sort = TRUE) |>
  top_n(10, n)

keywords_plot <- ggplot(keyword_freq, aes(x = reorder(keyword, n), y = n)) +
  geom_bar(stat = "identity", fill = "#511852", width = 0.7) +
  labs(
    title = "Top 10 Keywords",
    x = "",
    y = ""
  ) +
  theme_minimal(base_size = 12) +
  theme(
    axis.text.y = element_text(size = 12),
    plot.title = element_text(face = "bold", size = 16)
  )

ggplotly(keywords_plot) |> 
  layout(autosize = TRUE, height = 600)


```