prepare_state_vax_figures.rb

require 'open-uri'
require 'date'
require 'csv'

# Add daily change column
def add_daily_change_column(table, column_name, new_column_name=nil)
  new_column_name ||= column_name + "_change"
  table.by_col![new_column_name] = table.by_col[column_name].each_cons(2).map { |a, b| b - a }.prepend(0)
  table
end

# Add moving average column
def add_moving_average_column(table, column_name, window_size=7, new_column_name=nil)
  new_column_name ||= column_name + "_change"
  table.by_col![new_column_name] = table.by_col[column_name].each_cons(7).map { |a| a.sum / a.size }.prepend([0] * window_size).flatten
  table
end

# Source file
source_url  = "https://data.cdc.gov/api/views/unsk-b7fc/rows.csv"
table       = CSV.parse(URI.open(source_url), headers: true, converters: [:integer, :float])

# Convert the dates & and sort by dates
table.by_col!["Date"] = table.by_col["Date"].map { |c| Date.strptime(c, '%m/%d/%Y')}
table = CSV::Table.new(table.by_row!.sort_by { |r| r["Date"] })

# Split into tables with just states
locations = table.by_col["Location"].uniq.sort - ['AS','BP2','DD2','FM','GU','IH2','LTC','MH','MP','PR','RP','VA2','VI']
loc_columns = Hash.new()
locations.each do |l|
  loc_table = table.by_row.delete_if { |r| r["Location"] != l }
  loc_columns["Date"] = loc_table.by_col["Date"] if loc_columns.empty?
  loc_table = add_daily_change_column(loc_table, "Admin_Per_100K", "Daily_Admin_Per_100k")
  loc_table = add_moving_average_column(loc_table, "Daily_Admin_Per_100k")
  loc_columns[l] = loc_table.by_col["Daily_Admin_Per_100k_change"]
end

# Output
CSV.open("pivot.csv", "wb") do |csv|
  csv << loc_columns.keys
  loc_columns["Date"].count.times do |i|
    csv << loc_columns.values.map { |c| c[i] }
  end 
end