library(dplyr)
 library(readr)
 library(lubridate)
 library(ggplot2)
 library(scales)
 library(grid)
 library(gtable)

 race_dates <- read_fwf("equinox_marathon_dates.rst", skip=5, n_max=54,
                        fwf_positions(c(4, 6), c(9, 19), c("number", "race_date")))

 noaa <- src_postgres(host="localhost", dbname="noaa")
 # pivot <- tbl(noaa, build_sql("SELECT * FROM ghcnd_pivot
 #                               WHERE station_name = 'UNIVERSITY EXP STN'"))
 # pivot <- tbl(noaa, build_sql("SELECT * FROM ghcnd_pivot
 #                               WHERE station_name = 'COLLEGE OBSY'"))
 pivot <- tbl(noaa, build_sql("SELECT * FROM ghcnd_pivot
                               WHERE station_name = 'FAIRBANKS INTL AP'"))

 race_day_wx <- pivot %>%
     inner_join(race_dates, by=c("dte"="race_date"), copy=TRUE) %>%
     collect() %>%
     mutate(tmin_f=round((tmin_c*9/5.0)+32, 1), tmax_f=round((tmax_c*9/5.0)+32, 1),
            prcp_in=round(prcp_mm/25.4, 2),
            snow_in=round(snow_mm/25.4, 1), snwd_in=round(snow_mm/25.4, 1),
            awnd_mph=round(awnd_mps*2.2369, 1),
            wsf2_mph=round(wsf2_mps*2.2369), 1) %>%
     select(number, race_date, tmin_f, tmax_f, prcp_in, snow_in,
            snwd_in, awnd_mph, wsf2_mph)

 week_before_race_day_wx <- pivot %>%
     mutate(year=date_part("year", dte)) %>%
     inner_join(race_dates %>%
                    mutate(year=year(race_date)),
                copy=TRUE) %>%
     collect() %>%
     mutate(tmin_f=round((tmin_c*9/5.0)+32, 1), tmax_f=round((tmax_c*9/5.0)+32, 1),
            prcp_in=round(prcp_mm/25.4, 2),
            snow_in=round(snow_mm/25.4, 1), snwd_in=round(snow_mm/25.4, 1),
            awnd_mph=round(awnd_mps*2.2369, 1), wsf2_mph=round(wsf2_mps*2.2369, 1)) %>%
     select(number, year, race_date, dte, prcp_in, snow_in) %>%
     mutate(week_before=race_date-days(7)) %>%
     filter(dte<race_date, dte>=week_before) %>%
     group_by(number, year, race_date) %>%
     summarize(pweek_prcp_in=sum(prcp_in),
               pweek_snow_in=sum(snow_in))

 all_wx <- race_day_wx %>%
     inner_join(week_before_race_day_wx) %>%
     mutate(tavg_f=(tmin_f+tmax_f)/2.0,
            snow_label=ifelse(snow_in>0, '*', NA),
            pweek_snow_label=ifelse(pweek_snow_in>0, '*', NA)) %>%
     select(number, year, race_date, tmin_f, tmax_f, tavg_f,
            prcp_in, snow_in, snwd_in, awnd_mph, wsf2_mph,
            pweek_prcp_in, pweek_snow_in,
            snow_label, pweek_snow_label);

 write_csv(all_wx, "all_wx.csv")

 madis <- src_postgres(host="localhost", dbname="madis")

 pafa_fbsa <- tbl(madis,
                  build_sql("
   WITH pafa AS (
     SELECT dt_local, temp_f, wspd_mph
     FROM observations
     WHERE station_id = 'PAFA' AND date_part('month', dt_local) = 9),
   fbsa AS (
     SELECT dt_local, temp_f, wspd_mph
     FROM observations
     WHERE station_id = 'FBSA2' AND date_part('month', dt_local) = 9)
   SELECT pafa.dt_local, pafa.temp_f AS pafa_temp_f, pafa.wspd_mph as pafa_wspd_mph,
     fbsa.temp_f AS ester_dome_temp_f, fbsa.wspd_mph as ester_dome_wspd_mph
   FROM pafa
     INNER JOIN fbsa ON
       pafa.dt_local BETWEEN fbsa.dt_local - interval '15 minutes'
         AND fbsa.dt_local + interval '15 minutes'")) %>% collect()

 write_csv(pafa_fbsa, "pafa_fbsa.csv")

 ester_dome_temps <- lm(data=pafa_fbsa,
                        ester_dome_temp_f ~ pafa_temp_f)

 summary(ester_dome_temps)
 # Model and coefficients are significant, r2 = 0.794
 # intercept = -2.69737, slope = 0.94268

 all_wx_with_ed <- all_wx %>%
   mutate(ed_min_temp_f=round(ester_dome_temps$coefficients[1]+
                              tmin_f*ester_dome_temps$coefficients[2], 1),
          ed_max_temp_f=round(ester_dome_temps$coefficients[1]+
                              tmax_f*ester_dome_temps$coefficients[2], 1))

 make_gt <- function(outside, instruments, chamber, width, heights) {
     gt1 <- ggplot_gtable(ggplot_build(outside))
     gt2 <- ggplot_gtable(ggplot_build(instruments))
     gt3 <- ggplot_gtable(ggplot_build(chamber))
     max_width <- unit.pmax(gt1$widths[2:3], gt2$widths[2:3], gt3$widths[2:3])
     gt1$widths[2:3] <- max_width
     gt2$widths[2:3] <- max_width
     gt3$widths[2:3] <- max_width
     gt <- gtable(widths = unit(c(width), "in"), heights = unit(heights, "in"))
     gt <- gtable_add_grob(gt, gt1, 1, 1)
     gt <- gtable_add_grob(gt, gt2, 2, 1)
     gt <- gtable_add_grob(gt, gt3, 3, 1)

     gt
 }

temps <- ggplot(data=all_wx_with_ed, aes(x=year, ymin=tmin_f, ymax=tmax_f, y=tavg_f)) +
   # geom_abline(intercept=32, slope=0, color="blue", alpha=0.25) +
   geom_rect(data=all_wx_with_ed %>% head(n=1),
            aes(xmin=-Inf, xmax=Inf, ymin=-Inf, ymax=32),
            fill="darkcyan", alpha=0.25) +
   geom_abline(aes(slope=0,
                  intercept=mean(all_wx_with_ed$tmin_f)),
               color="darkorange", alpha=0.50, linetype=2) +
   geom_abline(aes(slope=0,
                  intercept=mean(all_wx_with_ed$tmax_f)),
               color="darkorange", alpha=0.50, linetype=2) +
   geom_abline(aes(slope=0,
                  intercept=mean(all_wx_with_ed$ed_min_temp_f)),
               color="darkorange", alpha=0.50, linetype=1) +
   geom_abline(aes(slope=0,
                  intercept=mean(all_wx_with_ed$ed_max_temp_f)),
               color="darkorange", alpha=0.50, linetype=1) +
   geom_linerange(aes(ymin=ed_min_temp_f, ymax=ed_max_temp_f)) +
   # geom_smooth(method="lm", se=FALSE) +
   geom_linerange(size=3, color="grey30") +
   scale_x_continuous(name="", limits=c(1963, 2015), breaks=seq(1963, 2015, 2)) +
   scale_y_continuous(name="Temperature (deg F)", breaks=pretty_breaks(n=10)) +
   theme_bw() +
   theme(plot.margin=unit(c(1, 1, 0, 0.5), 'lines')) +  # t, r, b, l
   theme(axis.text.x=element_blank(), axis.title.x=element_blank(),
         axis.ticks.x=element_blank(), panel.grid.minor.x=element_blank()) +
   ggtitle("Weather during and in the week prior to the Equinox Marathon
            Fairbanks Airport Station")

 prcp <- ggplot(data=all_wx, aes(x=year, y=prcp_in)) +
     geom_bar(stat="identity") +
     geom_text(aes(y=prcp_in+0.025, label=snow_label)) +
     scale_x_continuous(name="", limits=c(1963, 2015), breaks=seq(1963, 2015)) +
     scale_y_continuous(name="Precipitation (inches)", breaks=pretty_breaks(n=5)) +
     theme_bw() +
     theme(plot.margin=unit(c(0, 1, 0, 0.5), 'lines')) +  # t, r, b, l
     theme(axis.text.x=element_blank(), axis.title.x=element_blank(),
           axis.ticks.x=element_blank(), panel.grid.minor.x=element_blank())

 pweek_prcp <- ggplot(data=all_wx, aes(x=year, y=pweek_prcp_in)) +
     geom_bar(stat="identity") +
     geom_text(aes(y=pweek_prcp_in+0.1, label=pweek_snow_label)) +
     scale_x_continuous(name="", limits=c(1963, 2015), breaks=seq(1963, 2015)) +
     scale_y_continuous(name="Pre-week precip (inches)", breaks=pretty_breaks(n=5)) +
     theme_bw() +
     theme(plot.margin=unit(c(0, 1, 0.5, 0.5), 'lines'),
           axis.text.x=element_text(angle=45, hjust=1, vjust=1),
           panel.grid.minor.x=element_blank())

 rescale <- 0.75
 full_plot <- make_gt(temps, prcp, pweek_prcp,
                      16*rescale,
                      c(7.5*rescale, 2.5*rescale, 3.0*rescale))
 pdf("equinox_weather_grid.pdf", height=13*rescale, width=16*rescale)
 grid.newpage()
 grid.draw(full_plot)
 dev.off()

 fai_ed_temps <- ggplot(data=pafa_fbsa, aes(x=pafa_temp_f, y=ester_dome_temp_f)) +
   geom_rect(data=pafa_fbsa %>% head(n=1),
               aes(xmin=-Inf, ymin=-Inf, xmax=(32+2.69737)/0.94268, ymax=32),
               color="black", fill="darkcyan", alpha=0.25) +
   geom_point(position=position_jitter()) +
   geom_smooth(method="lm", se=FALSE) +
   scale_x_continuous(name="Fairbanks Airport Temperature (degrees F)") +
   scale_y_continuous(name="Ester Dome Temperature (degrees F)") +
   theme_bw() +
   ggtitle("Relationship between Fairbanks Airport and Ester Dome Temperatures
           September, 2008-2013")

 pdf("pafa_fbsa_sept_temps.pdf", height=10.5, width=10.5)
 print(fai_ed_temps)
 dev.off()

 fai_ed_wspds <- ggplot(data=pafa_fbsa, aes(x=pafa_wspd_mph, y=ester_dome_wspd_mph)) +
   geom_point(position=position_jitter()) +
   geom_smooth(method="lm", se=FALSE) +
   scale_x_continuous(name="Fairbanks Airport Wind Speed (MPH)") +
   scale_y_continuous(name="Ester Dome Wind (MPH)") +
   theme_bw() +
   ggtitle("Relationship between Fairbanks Airport and Ester Dome Wind Speeds
           September, 2008-2013")

 pdf("pafa_fbsa_sept_wspds.pdf", height=10.5, width=10.5)
 print(fai_ed_wspds)
 dev.off()

import cv2

def get_histogram(image, bins):
   """ calculate a 3d RGB histogram from an image """
   if os.path.exists(image):
      imgarray = cv2.imread(image)

      hist = cv2.calcHist([imgarray], [0, 1, 2], None,
                           [bins, bins, bins],
                           [0, 256, 0, 256, 0, 256])
      hist = cv2.normalize(hist, hist)

      return hist.flatten()
   else:
      return None

def chi2_distance(a, b, eps=1e-10):
   """ distance between two histograms (a, b) """
   d = 0.5 * np.sum([((x - y) ** 2) / (x + y + eps)
                     for (x, y) in zip(a, b)])

   return d

CREATE OR REPLACE FUNCTION chi_square_distance(a numeric[], b numeric[])
RETURNS numeric AS $_$
   DECLARE
      sum numeric := 0.0;
      i integer;
   BEGIN
      FOR i IN 1 .. array_upper(a, 1)
      LOOP
            IF a[i]+b[i] > 0 THEN
               sum = sum + (a[i]-b[i])^2 / (a[i]+b[i]);
            END IF;
      END LOOP;

      RETURN sum/2.0;
   END;
$_$
LANGUAGE plpgsql;

#include <postgres.h>
#include <fmgr.h>
#include <utils/array.h>
#include <utils/lsyscache.h>

/* From intarray contrib header */
#define ARRPTR(x) ( (float8 *) ARR_DATA_PTR(x) )

PG_MODULE_MAGIC;

PG_FUNCTION_INFO_V1(chi_square_distance);
Datum chi_square_distance(PG_FUNCTION_ARGS);

Datum chi_square_distance(PG_FUNCTION_ARGS) {
   ArrayType *a, *b;
   float8 *da, *db;

   float8 sum = 0.0;
   int i, n;

   da = ARRPTR(a);
   db = ARRPTR(b);

   // Generate the sums.
   for (i = 0; i < n; i++) {
      if (*da - *db) {
            sum = sum + ((*da - *db) * (*da - *db) / (*da + *db));
      }
      da++;
      db++;
   }

   sum = sum / 2.0;

   PG_RETURN_FLOAT8(sum);
}

library(rvest)       # scraping data from the web
library(dplyr)       # manipulation, filtering, grouping into tables
library(stringr)     # string functions
library(tidyr)       # creating columns from rows
library(RPostgreSQL) # dump final result to a PostgreSQL database

hop_varieties <- read_html("http://ychhops.com/varieties")

hop_page_links <- hop_varieties %>%
    html_nodes("a.card__name") %>%
    html_attr("href")

fixed_links <- sapply(hop_page_links,
                     FUN=function(x) sub('ych.craft.dev',
                                         'ychhops.com', x)) %>%
    as.vector()

get_hop_stats <- function(p) {
    hop_page <- read_html(p)

    hop_name <- hop_page %>%
        html_nodes('h1[itemprop="name"]') %>%
        html_text()

    type <- hop_page %>%
        html_nodes('div.hop-profile__data div[itemprop="additionalProperty"]') %>%
        html_text()
    type <- (str_split(type, ' '))[[1]][2]

    region <- hop_page %>%
        html_nodes('div.hop-profile__data h5') %>%
        html_text()

    description <- hop_page %>%
        html_nodes('div.hop-profile__profile p[itemprop="description"]') %>%
        html_text()

    aroma_profiles <- hop_page %>%
        html_nodes('div.hop-profile__profile h3.headline a[itemprop="category"]') %>%
        html_text()

    aroma_profiles <- sapply(aroma_profiles,
                             FUN=function(x) sub(',$', '', x)) %>%
        as.vector() %>%
        paste(collapse="@")

    composition_keys <- hop_page %>%
        html_nodes('div.hop-composition__item') %>%
        html_text()

    composition_keys <- sapply(composition_keys,
                               FUN=function(x)
                                   tolower(gsub('[ -]', '_', x))) %>%
        as.vector() %>%
        paste(collapse="@")

    composition_values <- hop_page %>%
        html_nodes('div.hop-composition__value') %>%
        html_text() %>%
        paste(collapse="@")

    hop <- data.frame('hop'=hop_name, 'type'=type, 'region'=region,
                      'description'=description,
                      'aroma_profiles'=aroma_profiles,
                      'composition_keys'=composition_keys,
                      'composition_values'=composition_values)

}

all_hops <- do.call(rbind,
                    lapply(fixed_links, get_hop_stats)) %>% tbl_df() %>%
    arrange(hop) %>%
    mutate(id=row_number())

hops <- all_hops %>%
    arrange(hop) %>%
    mutate(description=gsub('\\r', '', description),
           keys=str_split(composition_keys, "@"),
           values=str_split(composition_values, "@")) %>%
    unnest(keys, values) %>%
    spread(keys, values) %>%
    select(id, hop, type, region, alpha_acid, beta_acid, co_humulone, total_oil, description)

kable(hops %>% select(id, hop, type, region, alpha_acid) %>% head())

hops_aromas <- all_hops %>%
    select(id, hop, aroma_profiles) %>%
    mutate(aroma=str_split(aroma_profiles, "@")) %>%
    unnest(aroma) %>%
    select(id, hop, aroma)

save(list=c("hops", "hops_aromas"),
     file="ych_hops.rdata")

beer <- src_postgres(host="dryas.swingleydev.com", dbname="beer",
                     port=5434, user="cswingle")

dbWriteTable(beer$con, "ych_hops", hops %>% data.frame(), row.names=FALSE)
dbWriteTable(beer$con, "ych_hops_aromas", hops_aromas %>% data.frame(), row.names=FALSE)

CREATE VIEW ych_basic_hop_data AS
SELECT ych_hops.id, ych_hops.hop, array_agg(aroma) AS aromas, type,
    numrange(
        regexp_replace(alpha_acid, '([0-9.]+).*', E'\\1')::numeric,
        regexp_replace(alpha_acid, '.*- ([0-9.]+)%', E'\\1')::numeric,
        '[]') AS alpha_acid_percent, description
FROM ych_hops
    INNER JOIN ych_hops_aromas USING(id)
GROUP BY ych_hops.id, ych_hops.hop, type, alpha_acid, description
ORDER BY hop;

SELECT hop, region, type, aromas, alpha_acid_percent
FROM ych_basic_hop_data
WHERE type = 'Aroma' AND region = 'Pacific Northwest' AND 'Spicy' = ANY(aromas)
AND 'Citrus' != ALL(aromas) ORDER BY alpha_acid_percent;

     hop    |      region       | type  |            aromas            | alpha_acid_percent
 -----------+-------------------+-------+------------------------------+--------------------
  Crystal   | Pacific Northwest | Aroma | {Floral,Spicy}               | [3,6]
  Hallertau | Pacific Northwest | Aroma | {Floral,Spicy,Herbal}        | [3.5,6.5]
  Tettnang  | Pacific Northwest | Aroma | {Earthy,Floral,Spicy,Herbal} | [4,6]
  Mt. Hood  | Pacific Northwest | Aroma | {Spicy,Herbal}               | [4,6.5]
  Santiam   | Pacific Northwest | Aroma | {Floral,Spicy,Herbal}        | [6,8.5]
  Ultra     | Pacific Northwest | Aroma | {Floral,Spicy}               | [9.2,9.7]