dsacon2021_ballots.R

suppressPackageStartupMessages(library(data.table))
suppressPackageStartupMessages(library(janitor))
suppressPackageStartupMessages(library(lubridate))
suppressPackageStartupMessages(library(tidyr))
suppressPackageStartupMessages(library(dplyr))

# load and transform ------------------------------------------------------

vts <- 
  fread('data/Vote Archive - Public-Grid view.csv', encoding = 'UTF-8') |>
  clean_names() |> 
  mutate(
    timestamp = as_datetime(
      x = timestamp, 
      format = '%M/%d/%Y %I:%M%p', 
      tz = 'US/Pacific'
    ),
  # Extract the hour of vote for basic analysis
  hour_of_vote = floor_date(timestamp, '1 hour')
  )

# delegates ---------------------------------------------------------------

delegate <- 
  vts |> 
  # Delegate is a concatenation of name and chapter
  group_by(delegate) |> 
  summarize(
    tally_vote = n_distinct(motion_number),
    tally_yes = sum(vote_choice == 'Yes'),
    tally_no = sum(vote_choice == 'No')
  ) |> 
  ungroup() |> 
  separate( 
    col = delegate, 
    into = c('delegate_name', 'chapter'), 
    sep = ', ',
    remove = FALSE
  )

# motions -----------------------------------------------------------------

motion <-
  vts |> 
  group_by(motion_number, motion_title) |> 
  summarize(
    tally_vote = n_distinct(ballot_number),
    tally_yes = sum(vote_choice == 'Yes'),
    tally_no = sum(vote_choice == 'No'),
    timestamp_vote_first = min(timestamp),
    timestamp_vote_last = max(timestamp), 
    vote_duration = difftime(
      timestamp_vote_last, 
      timestamp_vote_first, 
      units = 'mins'
    ),
    .groups = 'drop'
  ) |> 
  ungroup()

# votes -------------------------------------------------------------------

vote <-
  vts |> 
  select(
    motion_number,
    delegate,
    vote_choice,
    timestamp
  )

# pivot wider -------------------------------------------------------------

# make the big table of all observed delegates and all votes

vts_all <- 
  vts |> 
  right_join(
    vts |> 
      select(delegate, motion_number, vote_choice) |> 
      expand(delegate, motion_number)
  )

vts_wide <-
  vts_all |> 
  select(delegate, motion_number, vote_choice) |> 
  pivot_wider(names_from = motion_number, values_from = vote_choice)

# pull sv dsa -------------------------------------------------------------

vts_sv <- 
  vts_wide |> 
  filter(grepl('Silicon Valley', delegate)) |> 
  mutate(delegate = gsub(', Silicon Valley', '', delegate))

# write result ------------------------------------------------------------

write.csv(vts, 'data/all.csv')
saveRDS(vts, 'data/all.RDS')

write.csv(vote, 'data/vote.csv')
saveRDS(vote, 'data/vote.RDS')

write.csv(vts_all, 'data/vote_complete.csv')
saveRDS(vts_all, 'data/vote_complete.RDS')

write.csv(vts_wide, 'data/vote_wide.csv')
saveRDS(vts_wide, 'data/vote_wide.RDS')

write.csv(vts_sv, 'data/vote_sv.csv')
saveRDS(vts_sv, 'data/vote_sv.RDS')

write.csv(motion, 'data/motion.csv')
saveRDS(motion, 'data/motion.RDS')

write.csv(delegate, 'data/delegate.csv')
saveRDS(delegate, 'data/delegate.RDS')