{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "baf3292d-c3ec-dbff-026c-228ceb396dbb"
      },
      "source": [
        "# Explore event distributions by region using the geo_location feature"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "f74f3ad2-5867-b5ae-549e-f20234ad7c8d"
      },
      "outputs": [],
      "source": [
        "library(readr)\n",
        "library(dplyr)\n",
        "library(data.table)\n",
        "library(googleVis)\n",
        "library(ggplot2)\n",
        "\n",
        "events <- fread('../input/events.csv', select=c('geo_location'))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "6d568de5-7264-3df3-94d5-66f05c13e7d1"
      },
      "source": [
        "## Split into Country, State/Province, and DMA"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "921aaad4-95c0-04f9-09f2-b9f20d6e40d1"
      },
      "outputs": [],
      "source": [
        "events$Country <- sapply(events$geo_location, function(x) strsplit(x, '>')[[1]][1])\n",
        "events$State <- sapply(events$geo_location, function(x) strsplit(x, '>')[[1]][2])\n",
        "events$DMA <- sapply(events$geo_location, function(x) strsplit(x, '>')[[1]][3])\n",
        "\n",
        "events$DMA2[is.na(events$DMA) & !is.na(as.numeric(events$State))] <- events$State[is.na(events$DMA) & !is.na(as.numeric(events$State))]\n",
        "events$State[is.na(events$DMA) & !is.na(as.numeric(events$State))] <- NA\n",
        "events$DMA[!is.na(events$DMA2)] <- events$DMA2[!is.na(events$DMA2)]\n",
        "events$DMA2 <- NULL"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "143e5236-0369-ec5d-a939-2c305d3074de"
      },
      "source": [
        "Some geo_locations like #5 have just a country and a DMA, no state or province. "
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "7daf0c0a-0337-a998-2b61-b2c3a35df04b"
      },
      "source": [
        "## Aggregate Tables\n",
        "Getting the event count by country, US State, US DMA, and Canadian Province"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "c3ac8aa7-2c4e-37a9-6900-1182d2ce70c0"
      },
      "outputs": [],
      "source": [
        "event_summary <- events %>% \n",
        "group_by(Country) %>% \n",
        "summarise(count = n())\n",
        "\n",
        "event_summary.US <- events %>%\n",
        "  filter(Country == 'US') %>%\n",
        "  group_by(State) %>% \n",
        "  summarise(count = n())\n",
        "\n",
        "event_summary.US_DMA <- events %>%\n",
        "  filter(Country == 'US') %>%\n",
        "  group_by(DMA) %>% \n",
        "  summarise(count = n())\n",
        "    \n",
        "event_summary.CA <- events %>%\n",
        "  filter(Country == 'CA') %>%\n",
        "  group_by(State) %>% \n",
        "  summarise(count = n())"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "20348323-c0c3-67cc-4002-0a96fdef14cb"
      },
      "source": [
        "Canadian abbreviations weren't working for me with googleVis, so I had to extend them to their full names.  Also, googleVis was able to make use of the US abbreviations and US DMAs, but not for other countries."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "74c3b5f3-7d46-4af6-b8d5-19b7181f5574"
      },
      "outputs": [],
      "source": [
        "event_summary.CA$State[event_summary.CA$State == 'ON'] <- 'Ontario'\n",
        "event_summary.CA$State[event_summary.CA$State == 'AB'] <- 'Alberta'\n",
        "event_summary.CA$State[event_summary.CA$State == 'BC'] <- 'British Columbia'\n",
        "event_summary.CA$State[event_summary.CA$State == 'MB'] <- 'Manitoba'\n",
        "event_summary.CA$State[event_summary.CA$State == 'NB'] <- 'New Brunswick'\n",
        "event_summary.CA$State[event_summary.CA$State == 'NL'] <- 'Newfoundland and Labrador'\n",
        "event_summary.CA$State[event_summary.CA$State == 'NS'] <- 'Nova Scotia'\n",
        "event_summary.CA$State[event_summary.CA$State == 'NT'] <- 'Northwest Territories'\n",
        "event_summary.CA$State[event_summary.CA$State == 'NU'] <- 'Nunavut'\n",
        "event_summary.CA$State[event_summary.CA$State == 'PE'] <- 'Prince Edward Island'\n",
        "event_summary.CA$State[event_summary.CA$State == 'QC'] <- 'Quebec'\n",
        "event_summary.CA$State[event_summary.CA$State == 'SK'] <- 'Saskatchewan'\n",
        "event_summary.CA$State[event_summary.CA$State == 'YT'] <- 'Yukon'"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "7f58fbbe-5000-4010-4a7f-4729ae01ffb3"
      },
      "outputs": [],
      "source": [
        "event_summary[order(-event_summary$count),][1:10,]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "dff1a264-75d1-7dc9-ec0e-03622b5f4967"
      },
      "outputs": [],
      "source": [
        "event_summary.US[order(-event_summary.US$count),][1:10,]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "4f0eb6de-4e84-0018-bedb-af0baf5dece2"
      },
      "outputs": [],
      "source": [
        "event_summary.US_DMA[order(-event_summary.US_DMA$count),][1:10,]"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "7c610c5f-2c29-851a-903c-fa6779ced9a7"
      },
      "outputs": [],
      "source": [
        "\n",
        "event_summary.CA[order(-event_summary.CA$count),]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "94357c20-f304-ab99-4424-b87cd2a0c7f8"
      },
      "source": [
        "## Plot GeoCharts"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "9bb2d427-8b76-f86a-9096-278a4b1e4990"
      },
      "outputs": [],
      "source": [
        "my_geo <- gvisGeoChart(event_summary %>% filter(Country != ''), \"Country\", \"count\",\n",
        "                       options=list(width=600, height=400))\n",
        "plot(my_geo)\n",
        "\n",
        "my_geo2 <- gvisGeoChart(event_summary.US %>% filter(State != ''), \"State\", \"count\",\n",
        "                       options=list(region=\"US\",\n",
        "                         displayMode=\"regions\", \n",
        "                         resolution=\"provinces\",\n",
        "                         width=600, height=400))\n",
        "plot(my_geo2)\n",
        "\n",
        "my_geo3 <- gvisGeoChart(event_summary.US_DMA %>% filter(DMA != ''), \"DMA\", \"count\",\n",
        "                        options=list(region=\"US\",\n",
        "                                     displayMode=\"Marker\",\n",
        "                                     resolution=\"metros\",\n",
        "                                     width=600, height=400))\n",
        "plot(my_geo3)\n",
        "\n",
        "my_geo4 <- gvisGeoChart(event_summary.CA %>% filter(State != ''), \"State\", \"count\",\n",
        "                        options=list(region=\"CA\",\n",
        "                                     displayMode=\"regions\",\n",
        "                                     resolution=\"provinces\",\n",
        "                                     width=600, height=400))\n",
        "plot(my_geo4)\n",
        "\n",
        "cat(my_geo$html$chart, file=\"count_by_country.html\")\n",
        "cat(my_geo2$html$chart, file=\"count_by_US_state.html\")\n",
        "cat(my_geo3$html$chart, file=\"count_by_US_DMA.html\")\n",
        "cat(my_geo4$html$chart, file=\"count_by_CA_province.html\")"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "cb88bb73-7fae-68b3-82eb-30199675c02d"
      },
      "source": [
        "### To view the GeoCharts click \"download file\" in the output tab. \n",
        "\n",
        "\n",
        "----------\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "_cell_guid": "82c849ae-8283-d4bf-8fe9-466de9f28ab3"
      },
      "source": [
        "\n",
        "----------\n",
        "###Some bargraphs for event frequency:"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "2934c216-d32e-3023-9ea8-c4d47fe8d757"
      },
      "outputs": [],
      "source": [
        "event_summary <- event_summary %>%\n",
        "  filter(Country != '') %>%\n",
        "  mutate(ratio = round(count / sum(count), 4))\n",
        "\n",
        "event_summary.US <- event_summary.US %>%\n",
        "  filter(State != '') %>%\n",
        "  mutate(ratio = round(count / sum(count), 4))\n",
        "\n",
        "event_summary.CA <- event_summary.CA %>%\n",
        "  filter(State != '') %>%\n",
        "  mutate(ratio = round(count / sum(count), 4))\n",
        "\n",
        "event_summary.US_DMA <- event_summary.US_DMA %>%\n",
        "  filter(DMA != '') %>%\n",
        "  mutate(ratio = round(count / sum(count), 4))\n",
        "\n",
        "event_summary <- event_summary %>%\n",
        "  top_n(20, ratio) %>%\n",
        "  mutate(Country = as.factor(Country)) %>%\n",
        "  arrange(-ratio)\n",
        "event_summary.US <- event_summary.US %>%\n",
        "  top_n(20, ratio) %>%\n",
        "  mutate(State = as.factor(State)) %>%\n",
        "  arrange(-ratio)\n",
        "event_summary.US_DMA <- event_summary.US_DMA %>%\n",
        "  top_n(20, ratio) %>%\n",
        "  mutate(DMA = as.factor(DMA)) %>%\n",
        "  arrange(-ratio)\n",
        "event_summary.CA <- event_summary.CA %>%\n",
        "  mutate(State = as.factor(State)) %>%\n",
        "  arrange(-ratio)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "d4424497-7d78-4e9e-3aed-503c0d8b3e24"
      },
      "outputs": [],
      "source": [
        "p1 <- ggplot(event_summary, aes(reorder(Country, ratio), ratio)) + geom_bar(stat='identity') + coord_flip() +\n",
        "  scale_y_continuous(labels = scales::percent_format()) +\n",
        "  geom_text(aes(y = ratio, label = sprintf(\"%1.2f%%\", 100*ratio)), vjust = .35, hjust=-.25) +\n",
        "  ylim(0, .90) +\n",
        "  labs(title = \"Top 20 Event Frequency by Country\", y = \"Percent\", x = \"Country\")\n",
        "p2 <- ggplot(event_summary.US, aes(reorder(State, ratio), ratio)) + geom_bar(stat='identity') + coord_flip() +\n",
        "  scale_y_continuous(labels = scales::percent_format()) +\n",
        "  geom_text(aes(y = ratio, label = sprintf(\"%1.2f%%\", 100*ratio)), vjust = .35, hjust=-.25) +\n",
        "  ylim(0, .175) +\n",
        "  labs(title = \"Top 20 Event Frequency by State (US)\", y = \"Percent\", x = \"State\")\n",
        "p3 <- ggplot(event_summary.US_DMA, aes(reorder(DMA, ratio), ratio)) + geom_bar(stat='identity') + coord_flip() +\n",
        "  scale_y_continuous(labels = scales::percent_format()) +\n",
        "  geom_text(aes(y = ratio, label = sprintf(\"%1.2f%%\", 100*ratio)), vjust = .35, hjust=-.25) +\n",
        "  ylim(0, .090) +  \n",
        "  labs(title = \"Top 20 Event Frequency by Designated Market Area (US)\", y = \"Percent\", x = \"Designated Market Area\")\n",
        "p4 <- ggplot(event_summary.CA, aes(reorder(State, ratio), ratio)) + geom_bar(stat='identity') + coord_flip() +\n",
        "  scale_y_continuous(labels = scales::percent_format()) +\n",
        "  geom_text(aes(y = ratio, label = sprintf(\"%1.2f%%\", 100*ratio)), vjust = .35, hjust=-.25) +\n",
        "  ylim(0, .525) +   \n",
        "  labs(title = \"Event Frequency by Province (Canada)\", y = \"Percent\", x = \"Province\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "_cell_guid": "26cda64f-123a-3ccc-efe8-271256268878"
      },
      "outputs": [],
      "source": [
        "p1;p2;p3;p4"
      ]
    }
  ],
  "metadata": {
    "_change_revision": 0,
    "_is_fork": false,
    "kernelspec": {
      "display_name": "R",
      "language": "R",
      "name": "ir"
    },
    "language_info": {
      "codemirror_mode": "r",
      "file_extension": ".r",
      "mimetype": "text/x-r-source",
      "name": "R",
      "pygments_lexer": "r",
      "version": "3.3.1"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}