WaterDrain_merge_bulletproof.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For more information on our methodology, [see](http://graphics.chicagotribune.com/news/lake-michigan-drinking-water-rates/methods.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# load in libraries\n",
    "library(dplyr)\n",
    "library(ggplot2)\n",
    "\n",
    "# Helper functions\n",
    "\n",
    "formatbucks <- function(df,monetary) {\n",
    "  for (m in monetary) {\n",
    "    df[,m] <- as.numeric(gsub( \"[$,]\", \"\",df[,m]))\n",
    "  }\n",
    "  return(df)\n",
    "}\n",
    "\n",
    "asnum <- function(df,cols) {\n",
    "  for (c in cols) {\n",
    "    df[,c] <- as.numeric(as.character(df[,c]))\n",
    "  }\n",
    "  return(df)\n",
    "}\n",
    "\n",
    "nullify <- function(df, cols){\n",
    "  for (c in cols) {\n",
    "    df[,c] <- NULL\n",
    "  }\n",
    "  return(df)\n",
    "}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# We load in our equivalences file: it has 243 towns\n",
    "# It's a crosswalk file listing a town's NAME, FAC_ID, and PLACEID\n",
    "# Note: Leyden Township was added manually, from P5 table of the Census 2010\n",
    "map <- read.csv('facid_name_place.csv')\n",
    "map <- unique(map)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# LMO-II \n",
    "# 2016 lmo rows = 1538\n",
    "lmo <- read.csv('../lmo/LMO2016_COMPILED.csv')\n",
    "lmo$FAC_NAME <- lmo$FAC_ID\n",
    "lmo$FAC_ID <- lmo$GEOCODE # rename this\n",
    "lmo$GEOCODE <- NULL\n",
    "\n",
    "lmo$FAC_ID<-as.character(lmo$FAC_ID)\n",
    "lmo$countycode <- sapply(lmo[,\"FAC_ID\"], function(x) substr(x, 3, 5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# 2014 lmo rows = 1541\n",
    "lmo_14 <- read.csv('../lmo/LMO2014_COMPILED.csv')\n",
    "lmo_14$FAC_NAME <- lmo_14$FAC_ID\n",
    "lmo_14$FAC_ID <- lmo_14$GEOCODE\n",
    "lmo_14$GEOCODE <- NULL\n",
    "\n",
    "lmo_14$FAC_ID<-as.character(lmo_14$FAC_ID)\n",
    "lmo_14$countycode <- sapply(lmo_14[,\"FAC_ID\"], function(x) substr(x, 3, 5))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge, and keep all, lmos\n",
    "# rows = 1592\n",
    "lmos <- merge(lmo, lmo_14, by=\"FAC_ID\",all=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# turn these columns into numbers\n",
    "monetary <- c(\"COST_APPARENT_LOSSES\",\"COST_REAL_LOSS\")\n",
    "lmos <- formatbucks(lmos,monetary)\n",
    "\n",
    "# turn factors into numeric\n",
    "# NAs warning are due to words like \"NA\" and \"BLANK\". \n",
    "# The only fields we should be missing are for LMO-2 that IDNR could not locate (5)\n",
    "factors <- c(\"WATER_SUPPLIED_ADJUSTED_MGY\",\"WATER_SUPPLIED_ADJUSTED_MGD\",\"REAL_LOSSES_MGY\",\"REAL_LOSSES_MGD\",\"COST_REAL_LOSS\")\n",
    "lmos <- asnum(lmos, factors)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Get rid of redundant columns\n",
    "lmos$Name_F <-lmos$Name_F.x\n",
    "lmos$Name_F.x<- NULL\n",
    "lmos$Name_F.y<- NULL"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "227"
      ],
      "text/latex": [
       "227"
      ],
      "text/markdown": [
       "227"
      ],
      "text/plain": [
       "[1] 227"
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nrow(lmos[lmos$SOURCE==\"Lake Michigan\",])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Condense pipe, and capped costs\n",
    "lmos <- lmos %>% mutate(\n",
    "  ALL_60 = CAST_IRON_PIPES_PUMPS_.60 + ALL_OTHER_PIPES_PUMPS_.60,\n",
    "  ALL_4060 = CAST_IRON_PIPES_PUMPS_40_TO_60 + ALL_OTHER_PIPES_PUMPS_40_TO_60,\n",
    "  ALL_2040 = CAST_IRON_PIPES_PUMPS_20_TO_40 + ALL_OTHER_PIPES_PUMPS_20_TO_40,\n",
    "  ALL_20 = CAST_IRON_PIPES_PUMPS_..20 + ALL_OTHER_PIPES_PUMPS_..20,\n",
    "  TOTAL_PIPE_LENGTH = ALL_60+ALL_4060+ALL_2040+ALL_20,\n",
    "  ALL_60_P = (ALL_60/TOTAL_PIPE_LENGTH)*100,\n",
    "  ALL_4060_P = (ALL_4060/TOTAL_PIPE_LENGTH)*100,\n",
    "  ALL_2040_P = (ALL_2040/TOTAL_PIPE_LENGTH)*100,\n",
    "  ALL_20_P = (ALL_20/TOTAL_PIPE_LENGTH)*100)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Cap losses and costs, only with real loss figures, of which somtimes only MGD is available\n",
    "lmos <- lmos %>% mutate(\n",
    "  PREALLOSSES_MGY = (REAL_LOSSES_MGY/WATER_SUPPLIED_ADJUSTED_MGY)*100,\n",
    "  PREALLOSSES_MGD = (REAL_LOSSES_MGD/WATER_SUPPLIED_ADJUSTED_MGD)*100,\n",
    "\n",
    "  CAP_LOSSES_MGY = (WATER_SUPPLIED_ADJUSTED_MGY)*0.12, # get 12% of the water supplied\n",
    "  CAP_LOSSES_MGD = ((WATER_SUPPLIED_ADJUSTED_MGD*0.12)),\n",
    "\n",
    "  WATERCONSERVED_MGY = REAL_LOSSES_MGY - CAP_LOSSES_MGY, # what would be conserved if capped\n",
    "  WATERCONSERVED_MGD = (REAL_LOSSES_MGD - CAP_LOSSES_MGD),\n",
    "\n",
    "  CAP_COST_MGY = ( COST_REAL_LOSS  / REAL_LOSSES_MGY) * CAP_LOSSES_MGY,\n",
    "  CAP_COST_MGD = (( (COST_REAL_LOSS/365) / REAL_LOSSES_MGD) * CAP_LOSSES_MGD),\n",
    "\n",
    "  COSTCONSERVED_MGY = COST_REAL_LOSS - CAP_COST_MGY,\n",
    "  COSTCONSERVED_MGD = ((COST_REAL_LOSS/365) - CAP_COST_MGD)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# load in the water bills data\n",
    "rates <- read.csv('water_bills_lm.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# merge rates and lmos, keeping all records\n",
    "rates_lmo <- merge(rates, lmos, by=\"FAC_ID\", all.x=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "rates_lmo$FAC_NAME.y <- NULL\n",
    "rates_lmo$FAC_NAME <- rates$FAC_NAME.x\n",
    "\n",
    "rates_lmo <- merge(rates_lmo, map, by=\"FAC_ID\",all.x=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "##############################\n",
    "# Now we get the census info #\n",
    "##############################\n",
    "\n",
    "total00 <- read.csv('../census/dec00/SF3_P007.csv')\n",
    "total10 <- read.csv('../census/dec10/SF1_P5.csv')\n",
    "\n",
    "total00$PLACEID <- total00$GEO.id2\n",
    "total10$PLACEID <- total10$GEO.id2\n",
    "\n",
    "# totals\n",
    "\n",
    "total00$CENSUS00_total <- total00$VD01\n",
    "total10$CENSUS10_total <- total10$D001\n",
    "\n",
    "# clean the total figure\n",
    "total00$CENSUS00_total<-as.numeric(as.character(total00$CENSUS00_total))\n",
    "total10$CENSUS10_total <- gsub(\"\\\\s*\\\\([^\\\\)]+\\\\)\",\"\",as.character(total10$D001))\n",
    "total10$CENSUS10_total <-as.numeric(as.character(total10$CENSUS10_total))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "## RACE BY CENSUS 2010\n",
    "\n",
    "total10$white <- total10$D003\n",
    "total10$black <- total10$D004\n",
    "total10$other_nh <- total10$D005+total10$D006+total10$D007+total10$D008+total10$D009\n",
    "total10$hispanic <- total10$D010\n",
    "\n",
    "total10<-total10 %>% mutate(\n",
    "  percent_white = (white / CENSUS10_total)*100,\n",
    "  percent_black = (black / CENSUS10_total)*100,\n",
    "  percent_hispanic = (hispanic / CENSUS10_total)*100,\n",
    "  percent_other_nh = (other_nh / CENSUS10_total)*100\n",
    ")\n",
    "\n",
    "total10 <- total10 %>% mutate(\n",
    "  RACE_10CENSUS = \n",
    "    ifelse(percent_black > 50, \"black\"\n",
    "      ,ifelse(percent_white > 50, \"white\"\n",
    "        ,ifelse(percent_hispanic > 50, \"hispanic\",\"mixed\"\n",
    "        )\n",
    "      )\n",
    "    )\n",
    "  )"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "total00 <- total00 %>% select(CENSUS00_total, PLACEID)\n",
    "census10 <- total10 %>% select(CENSUS10_total,PLACEID,RACE_10CENSUS,percent_white,percent_black,percent_hispanic,percent_other_nh)\n",
    "\n",
    "census_totals <- merge(total00, census10, by='PLACEID', all=T)\n",
    "\n",
    "rates_cl <- merge(rates_lmo, census_totals, by=\"PLACEID\",all.x=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# we can also add median household income from the ACS 15 estimates \n",
    "# The comparable table for B19013 is in the 2000 census (P053), which is too old to use\n",
    "mhi <- read.csv('../census/acs/MHIB19013.csv')\n",
    "\n",
    "lmrmhi <- merge(rates_cl, mhi, by=\"PLACEID\", x.all=T)\n",
    "\n",
    "lmrmhi$MHI_2015 <- as.numeric(as.character(lmrmhi$MHI_2015))\n",
    "lmrmhi$MHI_2015_me <- as.numeric(as.character(lmrmhi$MHI_2015_me))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Calculate the RI, a measure of affordability.\n",
    "# Keep the upper and lower quartiles of the MHI number\n",
    "\n",
    "# percent margin of error\n",
    "lmrmhi$percent_me<-(lmrmhi$MHI_2015_me/lmrmhi$MHI_2015)*100\n",
    "\n",
    "lmrmhi$affordability<-(lmrmhi$gal5K_17*12 / lmrmhi$MHI_2015 )*100\n",
    "lmrmhi$aff_upper<-(lmrmhi$gal5K_17*12 / (lmrmhi$MHI_2015+lmrmhi$MHI_2015_me) )*100\n",
    "lmrmhi$aff_lower<-(lmrmhi$gal5K_17*12 / (lmrmhi$MHI_2015-lmrmhi$MHI_2015_me) )*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "181"
      ],
      "text/latex": [
       "181"
      ],
      "text/markdown": [
       "181"
      ],
      "text/plain": [
       "[1] 181"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "nrow(lmrmhi)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "########################\n",
    "## Write out to files ##\n",
    "########################\n",
    "\n",
    "# we clean up some leftovers from merging\n",
    "\n",
    "tonull <- c(\n",
    "  \"TYPE\",\"FAC_NAME.x\",\"countycode.x\",\n",
    "  \"countycode.y\",\"Name_F\",\"NAME\",\"PLACEID\",\"GEO.id\")\n",
    "\n",
    "lmrmhi <- nullify(lmrmhi, tonull)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# get rid of DUPLICATEs but not NA values in rows\n",
    "# also remove a duplicate for \"northlake\" that misspells the name\n",
    "# also remove willow springs duplicates\n",
    "\n",
    "data <- subset(lmrmhi, SERVICE_POPULATION!=\"DUPLICATE\" | is.na(SERVICE_POPULATION))\n",
    "data <- subset(data, BOUGHT_FROM!=\"DUPLICATE\" | is.na(SERVICE_POPULATION))\n",
    "data <- data %>% filter(!(FAC_NAME == 'north lake'))\n",
    "data <- unique(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# use MGD calculations when MGY is not available, for completeness\n",
    "# The LMO-2 forms say MGD to MGY transformation should use 365 days\n",
    "data <- transform(data, PREALLOSSES = ifelse(!is.na(PREALLOSSES_MGY), PREALLOSSES_MGY, PREALLOSSES_MGD))\n",
    "data <- transform(data, REAL_LOSSES = ifelse(!is.na(REAL_LOSSES_MGY), REAL_LOSSES_MGY, (REAL_LOSSES_MGD*365)))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# congrats, you have your file!\n",
    "unique(data[data$SOURCE==\"Lake Michigan\",]) %>% write.csv('lm-rates-lmo-1023.csv',row.names=F) # nrow=163"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "####################\n",
    "## Bulletproofing ##\n",
    "####################\n",
    "\n",
    "# PART 1: SAME LAKE, UNEQUAL RATES\n",
    "###################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "20.4387732258787"
      ],
      "text/latex": [
       "20.4387732258787"
      ],
      "text/markdown": [
       "20.4387732258787"
      ],
      "text/plain": [
       "[1] 20.43877"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# African-American residents’ median water bill is 20 percent higher \n",
    "# for the same amount of water than residents pay in \n",
    "# predominantly white communities\n",
    "\n",
    "# # get raw amt rate went up\n",
    "data$rate_diff <- data$gal5K_17 - data$gal5K_13\n",
    "\n",
    "# # get percent rate went up\n",
    "data$per_rate_diff <- (data$rate_diff / data$gal5K_13)*100\n",
    "\n",
    "black <- data[data$RACE_10CENSUS == \"black\",]\n",
    "white <- data[data$RACE_10CENSUS == \"white\",]\n",
    "\n",
    "((median(black$gal5K_17,na.rm=T) - median(white$gal5K_17,na.rm=T))/median(white$gal5K_17,na.rm=T))*100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "19.4"
      ],
      "text/latex": [
       "19.4"
      ],
      "text/markdown": [
       "19.4"
      ],
      "text/plain": [
       "[1] 19.4"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "85"
      ],
      "text/latex": [
       "85"
      ],
      "text/markdown": [
       "85"
      ],
      "text/plain": [
       "[1] 85"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "14.43"
      ],
      "text/latex": [
       "14.43"
      ],
      "text/markdown": [
       "14.43"
      ],
      "text/plain": [
       "[1] 14.43"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Ford Heights... People there pay nearly six times more for\n",
    "# water than residents of Highland Park\n",
    "# and four times more than Chicago residents\n",
    "data[which(data$FAC_NAME == \"chicago\"), ]$gal5K_17\n",
    "data[which(data$FAC_NAME == \"ford heights\"), ]$gal5K_17\n",
    "data[which(data$FAC_NAME == \"highland park\"), ]$gal5K_17"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "31.4852298998202"
      ],
      "text/latex": [
       "31.4852298998202"
      ],
      "text/markdown": [
       "31.4852298998202"
      ],
      "text/plain": [
       "[1] 31.48523"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Overall, towns with median household incomes in the bottom 10 percent \n",
    "# of the region pay 31 percent more a month for water than \n",
    "# towns with a median household income in the top 10 percent.\n",
    "\n",
    "q <- quantile(data$MHI_2015, prob = seq(0, 1, length = 11), type = 5)\n",
    "\n",
    "lowest <- mean(data[data$MHI_2015 <= q[2],]$gal5K_17,na.rm=TRUE) \n",
    "top <- mean(data[data$MHI_2015 >= q[10],]$gal5K_17,na.rm=TRUE) \n",
    "\n",
    "((lowest - top )/ top)*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>RACE_10CENSUS</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>indian head park</td><td>white           </td></tr>\n",
       "\t<tr><th scope=row>2</th><td>ford heights</td><td>black       </td></tr>\n",
       "\t<tr><th scope=row>3</th><td>bannockburn</td><td>white      </td></tr>\n",
       "\t<tr><th scope=row>4</th><td>clarendon hills</td><td>white          </td></tr>\n",
       "\t<tr><th scope=row>5</th><td>prospect heights</td><td>white           </td></tr>\n",
       "\t<tr><th scope=row>6</th><td>maywood</td><td>black  </td></tr>\n",
       "\t<tr><th scope=row>7</th><td>franklin park</td><td>white        </td></tr>\n",
       "\t<tr><th scope=row>8</th><td>glenwood</td><td>black   </td></tr>\n",
       "\t<tr><th scope=row>9</th><td>phoenix</td><td>black  </td></tr>\n",
       "\t<tr><th scope=row>10</th><td>dixmoor</td><td>black  </td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ll}\n",
       "  & FAC\\_NAME & RACE\\_10CENSUS\\\\\n",
       "\\hline\n",
       "\t1 & indian head park & white           \\\\\n",
       "\t2 & ford heights & black       \\\\\n",
       "\t3 & bannockburn & white      \\\\\n",
       "\t4 & clarendon hills & white          \\\\\n",
       "\t5 & prospect heights & white           \\\\\n",
       "\t6 & maywood & black  \\\\\n",
       "\t7 & franklin park & white        \\\\\n",
       "\t8 & glenwood & black   \\\\\n",
       "\t9 & phoenix & black  \\\\\n",
       "\t10 & dixmoor & black  \\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "           FAC_NAME RACE_10CENSUS\n",
       "1  indian head park         white\n",
       "2      ford heights         black\n",
       "3       bannockburn         white\n",
       "4   clarendon hills         white\n",
       "5  prospect heights         white\n",
       "6           maywood         black\n",
       "7     franklin park         white\n",
       "8          glenwood         black\n",
       "9           phoenix         black\n",
       "10          dixmoor         black"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "0.134969325153374"
      ],
      "text/latex": [
       "0.134969325153374"
      ],
      "text/markdown": [
       "0.134969325153374"
      ],
      "text/plain": [
       "[1] 0.1349693"
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Of the 10 towns with the highest water rates, five, or 50 percent,\n",
    "# are majority-black towns, while only 14 percent of communities \n",
    "# surveyed by the Tribune have majority-black populations.\n",
    "\n",
    "data %>% arrange(desc(gal5K_17)) %>% select(FAC_NAME, RACE_10CENSUS) %>% head(10)\n",
    "\n",
    "(nrow(black)) / (nrow(data))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>RACE_10CENSUS</th><th scope=col>affordability</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>ford heights    </td><td>black           </td><td>4.84560570071259</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>robbins         </td><td>black           </td><td>3.23452189379182</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>phoenix         </td><td>black           </td><td>2.59767972490921</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>dixmoor        </td><td>black          </td><td>2.1199653188303</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>maywood         </td><td>black           </td><td>2.05995555345406</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>markham         </td><td>black           </td><td>1.87494554642349</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>riverdale       </td><td>black           </td><td>1.68329153896164</td></tr>\n",
       "\t<tr><th scope=row>8</th><td>south chicago heights</td><td>mixed                </td><td>1.63910053893328     </td></tr>\n",
       "\t<tr><th scope=row>9</th><td>posen           </td><td>hispanic        </td><td>1.63855421686747</td></tr>\n",
       "\t<tr><th scope=row>10</th><td>prospect heights</td><td>white           </td><td>1.49663096904398</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lll}\n",
       "  & FAC\\_NAME & RACE\\_10CENSUS & affordability\\\\\n",
       "\\hline\n",
       "\t1 & ford heights     & black            & 4.84560570071259\\\\\n",
       "\t2 & robbins          & black            & 3.23452189379182\\\\\n",
       "\t3 & phoenix          & black            & 2.59767972490921\\\\\n",
       "\t4 & dixmoor         & black           & 2.1199653188303\\\\\n",
       "\t5 & maywood          & black            & 2.05995555345406\\\\\n",
       "\t6 & markham          & black            & 1.87494554642349\\\\\n",
       "\t7 & riverdale        & black            & 1.68329153896164\\\\\n",
       "\t8 & south chicago heights & mixed                 & 1.63910053893328     \\\\\n",
       "\t9 & posen            & hispanic         & 1.63855421686747\\\\\n",
       "\t10 & prospect heights & white            & 1.49663096904398\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "                FAC_NAME RACE_10CENSUS affordability\n",
       "1           ford heights         black      4.845606\n",
       "2                robbins         black      3.234522\n",
       "3                phoenix         black      2.597680\n",
       "4                dixmoor         black      2.119965\n",
       "5                maywood         black      2.059956\n",
       "6                markham         black      1.874946\n",
       "7              riverdale         black      1.683292\n",
       "8  south chicago heights         mixed      1.639101\n",
       "9                  posen      hispanic      1.638554\n",
       "10      prospect heights         white      1.496631"
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Residents in three towns that receive Lake Michigan water \n",
    "# are under that strain. All those communities are \n",
    "# predominantly African-American.\n",
    "\n",
    "data %>% arrange(desc(affordability)) %>% \n",
    "         select(FAC_NAME, RACE_10CENSUS, affordability) %>% head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "25780611700"
      ],
      "text/latex": [
       "25780611700"
      ],
      "text/markdown": [
       "25780611700"
      ],
      "text/plain": [
       "[1] 25780611700"
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# More than 25 billion gallons of Lake Michigan water are lost each year \n",
    "# after flowing through the crumbling system, at a cost of more than $44 million\n",
    "\n",
    "sum(data$REAL_LOSSES, na.rm=T)*1000000"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>PREALLOSSES</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>maywood         </td><td>38.7253005774924</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>hometown        </td><td>37.3359591488175</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>east hazel crest</td><td>33.9123853458995</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>posen           </td><td>33.1250290850347</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>burnham         </td><td>33.0749226953377</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>riverdale       </td><td>32.0194086087647</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>flossmoor       </td><td>31.3991922613254</td></tr>\n",
       "\t<tr><th scope=row>8</th><td>lyons           </td><td>31.3112748568483</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ll}\n",
       "  & FAC\\_NAME & PREALLOSSES\\\\\n",
       "\\hline\n",
       "\t1 & maywood          & 38.7253005774924\\\\\n",
       "\t2 & hometown         & 37.3359591488175\\\\\n",
       "\t3 & east hazel crest & 33.9123853458995\\\\\n",
       "\t4 & posen            & 33.1250290850347\\\\\n",
       "\t5 & burnham          & 33.0749226953377\\\\\n",
       "\t6 & riverdale        & 32.0194086087647\\\\\n",
       "\t7 & flossmoor        & 31.3991922613254\\\\\n",
       "\t8 & lyons            & 31.3112748568483\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "          FAC_NAME PREALLOSSES\n",
       "1          maywood    38.72530\n",
       "2         hometown    37.33596\n",
       "3 east hazel crest    33.91239\n",
       "4            posen    33.12503\n",
       "5          burnham    33.07492\n",
       "6        riverdale    32.01941\n",
       "7        flossmoor    31.39919\n",
       "8            lyons    31.31127"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Maywood, Hometown, East Hazel Crest, Burnham and Posen lost at least one-third \n",
    "# of their water last year — the most in the region, according to documents \n",
    "# they filed with IDNR. \n",
    "\n",
    "data %>% arrange(desc(PREALLOSSES)) %>% \n",
    "          select(FAC_NAME, PREALLOSSES) %>% head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>PREALLOSSES</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>maywood         </td><td>38.7253005774924</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>hometown        </td><td>37.3359591488175</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>east hazel crest</td><td>33.9123853458995</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>posen           </td><td>33.1250290850347</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>burnham         </td><td>33.0749226953377</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>riverdale       </td><td>32.0194086087647</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>flossmoor       </td><td>31.3991922613254</td></tr>\n",
       "\t<tr><th scope=row>8</th><td>lyons           </td><td>31.3112748568483</td></tr>\n",
       "\t<tr><th scope=row>9</th><td>berwyn          </td><td>26.9225443348135</td></tr>\n",
       "\t<tr><th scope=row>10</th><td>summit          </td><td>26.4800223583437</td></tr>\n",
       "\t<tr><th scope=row>11</th><td>bellwood       </td><td>26.158982831295</td></tr>\n",
       "\t<tr><th scope=row>12</th><td>justice         </td><td>26.0548475705859</td></tr>\n",
       "\t<tr><th scope=row>13</th><td>winthrop harbor </td><td>24.1949742588216</td></tr>\n",
       "\t<tr><th scope=row>14</th><td>brookfield      </td><td>23.8134855158559</td></tr>\n",
       "\t<tr><th scope=row>15</th><td>markham         </td><td>23.4598287476497</td></tr>\n",
       "\t<tr><th scope=row>16</th><td>kenilworth      </td><td>23.4363830004523</td></tr>\n",
       "\t<tr><th scope=row>17</th><td>dolton          </td><td>23.4106083411056</td></tr>\n",
       "\t<tr><th scope=row>18</th><td>melrose park    </td><td>22.1803922062804</td></tr>\n",
       "\t<tr><th scope=row>19</th><td>la grange      </td><td>21.019009953394</td></tr>\n",
       "\t<tr><th scope=row>20</th><td>elmwood park    </td><td>20.1234512429372</td></tr>\n",
       "\t<tr><th scope=row>21</th><td>matteson        </td><td>20.0310726877701</td></tr>\n",
       "\t<tr><th scope=row>22</th><td>vernon hills    </td><td>19.4094915348141</td></tr>\n",
       "\t<tr><th scope=row>23</th><td>blue island     </td><td>19.1572772427649</td></tr>\n",
       "\t<tr><th scope=row>24</th><td>worth           </td><td>19.1553276393789</td></tr>\n",
       "\t<tr><th scope=row>25</th><td>lincolnshire    </td><td>19.0828265079473</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|ll}\n",
       "  & FAC\\_NAME & PREALLOSSES\\\\\n",
       "\\hline\n",
       "\t1 & maywood          & 38.7253005774924\\\\\n",
       "\t2 & hometown         & 37.3359591488175\\\\\n",
       "\t3 & east hazel crest & 33.9123853458995\\\\\n",
       "\t4 & posen            & 33.1250290850347\\\\\n",
       "\t5 & burnham          & 33.0749226953377\\\\\n",
       "\t6 & riverdale        & 32.0194086087647\\\\\n",
       "\t7 & flossmoor        & 31.3991922613254\\\\\n",
       "\t8 & lyons            & 31.3112748568483\\\\\n",
       "\t9 & berwyn           & 26.9225443348135\\\\\n",
       "\t10 & summit           & 26.4800223583437\\\\\n",
       "\t11 & bellwood        & 26.158982831295\\\\\n",
       "\t12 & justice          & 26.0548475705859\\\\\n",
       "\t13 & winthrop harbor  & 24.1949742588216\\\\\n",
       "\t14 & brookfield       & 23.8134855158559\\\\\n",
       "\t15 & markham          & 23.4598287476497\\\\\n",
       "\t16 & kenilworth       & 23.4363830004523\\\\\n",
       "\t17 & dolton           & 23.4106083411056\\\\\n",
       "\t18 & melrose park     & 22.1803922062804\\\\\n",
       "\t19 & la grange       & 21.019009953394\\\\\n",
       "\t20 & elmwood park     & 20.1234512429372\\\\\n",
       "\t21 & matteson         & 20.0310726877701\\\\\n",
       "\t22 & vernon hills     & 19.4094915348141\\\\\n",
       "\t23 & blue island      & 19.1572772427649\\\\\n",
       "\t24 & worth            & 19.1553276393789\\\\\n",
       "\t25 & lincolnshire     & 19.0828265079473\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "           FAC_NAME PREALLOSSES\n",
       "1           maywood    38.72530\n",
       "2          hometown    37.33596\n",
       "3  east hazel crest    33.91239\n",
       "4             posen    33.12503\n",
       "5           burnham    33.07492\n",
       "6         riverdale    32.01941\n",
       "7         flossmoor    31.39919\n",
       "8             lyons    31.31127\n",
       "9            berwyn    26.92254\n",
       "10           summit    26.48002\n",
       "11         bellwood    26.15898\n",
       "12          justice    26.05485\n",
       "13  winthrop harbor    24.19497\n",
       "14       brookfield    23.81349\n",
       "15          markham    23.45983\n",
       "16       kenilworth    23.43638\n",
       "17           dolton    23.41061\n",
       "18     melrose park    22.18039\n",
       "19        la grange    21.01901\n",
       "20     elmwood park    20.12345\n",
       "21         matteson    20.03107\n",
       "22     vernon hills    19.40949\n",
       "23      blue island    19.15728\n",
       "24            worth    19.15533\n",
       "25     lincolnshire    19.08283"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Another 16 communities lost 20 to 32 percent of their \n",
    "# water, the documents show.\n",
    "\n",
    "data %>% arrange(desc(PREALLOSSES)) %>% \n",
    "         select(FAC_NAME, PREALLOSSES) %>% head(25)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 41,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "0.240740740740741"
      ],
      "text/latex": [
       "0.240740740740741"
      ],
      "text/markdown": [
       "0.240740740740741"
      ],
      "text/plain": [
       "[1] 0.2407407"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "5"
      ],
      "text/latex": [
       "5"
      ],
      "text/markdown": [
       "5"
      ],
      "text/plain": [
       "[1] 5"
      ]
     },
     "execution_count": 41,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# As Chicago raised rates, nearly 1 in 4 suburbs \n",
    "# in the past four years have quietly passed along \n",
    "# higher increases. In a handful of towns, \n",
    "# those increases were twice as high as Chicago’s.\n",
    " \n",
    "now <- data[which(data$FAC_NAME == \"chicago\"), ]$gal5K_17\n",
    "then <-data[which(data$FAC_NAME == \"chicago\"), ]$gal5K_13\n",
    "\n",
    "inc <- ((now-then)/then)*100 # 34.62873\n",
    "\n",
    "over <- data[data$per_rate_diff>inc,] %>% filter(!(per_rate_diff == 'NA'))\n",
    "nrow(over) / (nrow(data)-1) # -1 because we don't have harvey's data\n",
    "# 0.2407407\n",
    "twice_over <- data[data$per_rate_diff>(inc*2),]  %>% filter(!(per_rate_diff == 'NA'))\n",
    "\n",
    "nrow(twice_over) # 5 towns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "32.1513353115727"
      ],
      "text/latex": [
       "32.1513353115727"
      ],
      "text/markdown": [
       "32.1513353115727"
      ],
      "text/plain": [
       "[1] 32.15134"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The result was that between 2013 and 2017, the median cost of\n",
    "# Lake Michigan water for a typical household rose from \n",
    "# $34 to $45 per month, or 32 percent, the Tribune’s findings show.\n",
    "\n",
    "((median(data$gal5K_17, na.rm=T) - median(data$gal5K_13, na.rm=T))/median(data$gal5K_13, na.rm=T))*100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 43,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "45.8846529814272"
      ],
      "text/latex": [
       "45.8846529814272"
      ],
      "text/markdown": [
       "45.8846529814272"
      ],
      "text/plain": [
       "[1] 45.88465"
      ]
     },
     "execution_count": 43,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# In the 10 towns with the highest water rates, \n",
    "# the typical family’s water bill grew \n",
    "# to $75 from $51,\n",
    "# or by 45 percent.\n",
    "# Five of those towns are predominantly African-American.\n",
    "\n",
    "top10 <- data %>% arrange(desc(gal5K_17)) %>% head(10)\n",
    "((median(top10$gal5K_17, na.rm=T) - median(top10$gal5K_13, na.rm=T))/median(top10$gal5K_13, na.rm=T))*100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>per_rate_diff</th><th scope=col>pop_change</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>calumet city     </td><td>104.98687664042  </td><td>-5.00102585145671</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>phoenix          </td><td>100.0594000594   </td><td>-8.43822843822844</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>north riverside   </td><td>99.5033112582781  </td><td>-0.905985444824001</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>winthrop harbor </td><td>96.6717095310136</td><td>1.38345864661654</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>indian head park  </td><td>74.1293532338308  </td><td>-0.392259414225941</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>crestwood        </td><td>60.6995884773662 </td><td>-2.39771815669846</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>oak park         </td><td>59.6850393700788 </td><td>-1.22991394410174</td></tr>\n",
       "\t<tr><th scope=row>8</th><td>itasca          </td><td>58.7153652392947</td><td>4.62078142010403</td></tr>\n",
       "\t<tr><th scope=row>9</th><td>calumet park     </td><td>57.8947368421053 </td><td>-8.56576029875131</td></tr>\n",
       "\t<tr><th scope=row>10</th><td>norridge          </td><td>56.25             </td><td>-0.803267528931246</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lll}\n",
       "  & FAC\\_NAME & per\\_rate\\_diff & pop\\_change\\\\\n",
       "\\hline\n",
       "\t1 & calumet city      & 104.98687664042   & -5.00102585145671\\\\\n",
       "\t2 & phoenix           & 100.0594000594    & -8.43822843822844\\\\\n",
       "\t3 & north riverside    & 99.5033112582781   & -0.905985444824001\\\\\n",
       "\t4 & winthrop harbor  & 96.6717095310136 & 1.38345864661654\\\\\n",
       "\t5 & indian head park   & 74.1293532338308   & -0.392259414225941\\\\\n",
       "\t6 & crestwood         & 60.6995884773662  & -2.39771815669846\\\\\n",
       "\t7 & oak park          & 59.6850393700788  & -1.22991394410174\\\\\n",
       "\t8 & itasca           & 58.7153652392947 & 4.62078142010403\\\\\n",
       "\t9 & calumet park      & 57.8947368421053  & -8.56576029875131\\\\\n",
       "\t10 & norridge           & 56.25              & -0.803267528931246\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "           FAC_NAME per_rate_diff pop_change\n",
       "1      calumet city     104.98688 -5.0010259\n",
       "2           phoenix     100.05940 -8.4382284\n",
       "3   north riverside      99.50331 -0.9059854\n",
       "4   winthrop harbor      96.67171  1.3834586\n",
       "5  indian head park      74.12935 -0.3922594\n",
       "6         crestwood      60.69959 -2.3977182\n",
       "7          oak park      59.68504 -1.2299139\n",
       "8            itasca      58.71537  4.6207814\n",
       "9      calumet park      57.89474 -8.5657603\n",
       "10         norridge      56.25000 -0.8032675"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# many of those communities hit with the largest rate hikes \n",
    "# already were paying high water prices. \n",
    "# But those towns’ populations are dwindling. \n",
    "# Many of their businesses and industries have departed.\n",
    "\n",
    "data$pop_change <- ((data$CENSUS10_total - data$CENSUS00_total) / data$CENSUS00_total)*100\n",
    "\n",
    "data %>% arrange(desc(per_rate_diff)) %>% \n",
    "        select(FAC_NAME,per_rate_diff,pop_change) %>% head(10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "# Part 2: BILLIONS LOST, MILLIONS WASTED\n",
    "################################"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 47,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>PREALLOSSES</th><th scope=col>REAL_LOSSES_MGY</th><th scope=col>WATER_SUPPLIED_ADJUSTED_MGY</th><th scope=col>COST_REAL_LOSS</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>88</th><td>     38.7253</td><td>    367.4090</td><td>    948.7570</td><td>1660688.0000</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|llll}\n",
       "  & PREALLOSSES & REAL\\_LOSSES\\_MGY & WATER\\_SUPPLIED\\_ADJUSTED\\_MGY & COST\\_REAL\\_LOSS\\\\\n",
       "\\hline\n",
       "\t88 &      38.7253 &     367.4090 &     948.7570 & 1660688.0000\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "   PREALLOSSES REAL_LOSSES_MGY WATER_SUPPLIED_ADJUSTED_MGY COST_REAL_LOSS\n",
       "88     38.7253         367.409                     948.757        1660688"
      ]
     },
     "execution_count": 47,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Of the 946 million gallons that Maywood bought from Melrose Park \n",
    "# in 2016, 367 million gallons, or 38.7 percent, \n",
    "# never made it to taps, costing residents and businesses \n",
    "# in this cash-strapped village nearly $1.7 million\n",
    "\n",
    "data[which(data$FAC_NAME == \"maywood\"), ] %>% \n",
    "    select(PREALLOSSES,REAL_LOSSES_MGY,WATER_SUPPLIED_ADJUSTED_MGY,COST_REAL_LOSS)\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "9097690.01602041"
      ],
      "text/latex": [
       "9097690.01602041"
      ],
      "text/markdown": [
       "9097690.01602041"
      ],
      "text/plain": [
       "[1] 9097690"
      ]
     },
     "execution_count": 48,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# northeast Illinois would have saved nearly \n",
    "# $9.1 million if towns using Lake Michigan water \n",
    "# had been held to the state’s water loss standard of 12 percent.\n",
    "\n",
    "d<-data %>% filter(COSTCONSERVED_MGY>0)\n",
    "sum(d$COSTCONSERVED_MGY)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 49,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>COST_REAL_LOSS</th><th scope=col>PREALLOSSES</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>maywood         </td><td>1660688         </td><td>38.7253005774924</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>hometown        </td><td>163244          </td><td>37.3359591488175</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>east hazel crest</td><td>197602          </td><td>33.9123853458995</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>posen           </td><td>350584          </td><td>33.1250290850347</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>burnham         </td><td>257298          </td><td>33.0749226953377</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>riverdale       </td><td>585059          </td><td>32.0194086087647</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>flossmoor       </td><td>846481          </td><td>31.3991922613254</td></tr>\n",
       "\t<tr><th scope=row>8</th><td>lyons           </td><td>589545          </td><td>31.3112748568483</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lll}\n",
       "  & FAC\\_NAME & COST\\_REAL\\_LOSS & PREALLOSSES\\\\\n",
       "\\hline\n",
       "\t1 & maywood          & 1660688          & 38.7253005774924\\\\\n",
       "\t2 & hometown         & 163244           & 37.3359591488175\\\\\n",
       "\t3 & east hazel crest & 197602           & 33.9123853458995\\\\\n",
       "\t4 & posen            & 350584           & 33.1250290850347\\\\\n",
       "\t5 & burnham          & 257298           & 33.0749226953377\\\\\n",
       "\t6 & riverdale        & 585059           & 32.0194086087647\\\\\n",
       "\t7 & flossmoor        & 846481           & 31.3991922613254\\\\\n",
       "\t8 & lyons            & 589545           & 31.3112748568483\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "          FAC_NAME COST_REAL_LOSS PREALLOSSES\n",
       "1          maywood        1660688    38.72530\n",
       "2         hometown         163244    37.33596\n",
       "3 east hazel crest         197602    33.91239\n",
       "4            posen         350584    33.12503\n",
       "5          burnham         257298    33.07492\n",
       "6        riverdale         585059    32.01941\n",
       "7        flossmoor         846481    31.39919\n",
       "8            lyons         589545    31.31127"
      ]
     },
     "execution_count": 49,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Last year, eight towns surveyed by the Tribune — \n",
    "# Hometown, East Hazel Crest, Posen, Burnham, Riverdale, Flossmoor, \n",
    "# Lyons and Maywood — lost more than 30 percent of their water. \n",
    "\n",
    "data %>% arrange(desc(PREALLOSSES)) %>% \n",
    "      select(FAC_NAME, COST_REAL_LOSS, PREALLOSSES) %>% head(8)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 50,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "0.272151898734177"
      ],
      "text/latex": [
       "0.272151898734177"
      ],
      "text/markdown": [
       "0.272151898734177"
      ],
      "text/plain": [
       "[1] 0.2721519"
      ]
     },
     "execution_count": 50,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Over 1 in 4 towns in the region exceeded the 12 \n",
    "# percent standard set by the Illinois Department of Natural Resources.\n",
    "\n",
    "exceed <- data %>% filter(PREALLOSSES>12)\n",
    "\n",
    "nrow(exceed) / (nrow(data)-5) # 5 did not submit loss reports to IDNR\n",
    "# 0.2721519"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "17.5798635965452"
      ],
      "text/latex": [
       "17.5798635965452"
      ],
      "text/markdown": [
       "17.5798635965452"
      ],
      "text/plain": [
       "[1] 17.57986"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    },
    {
     "data": {
      "text/html": [
       "9.84426363462414"
      ],
      "text/latex": [
       "9.84426363462414"
      ],
      "text/markdown": [
       "9.84426363462414"
      ],
      "text/plain": [
       "[1] 9.844264"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Towns with majority-black populations lost an average \n",
    "# of 18 percent of their water, compared to the region’s \n",
    "# overall rate of 10 percent. \n",
    "# These towns pay some of the highest rates for water in the area. \n",
    "\n",
    "mean(black$PREALLOSSES,na.rm=T)\n",
    "mean(data$PREALLOSSES,na.rm=T)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<table>\n",
       "<thead><tr><th></th><th scope=col>FAC_NAME</th><th scope=col>COST_REAL_LOSS</th><th scope=col>PREALLOSSES</th></tr></thead>\n",
       "<tbody>\n",
       "\t<tr><th scope=row>1</th><td>maywood         </td><td>1660688         </td><td>38.7253005774924</td></tr>\n",
       "\t<tr><th scope=row>2</th><td>hometown        </td><td>163244          </td><td>37.3359591488175</td></tr>\n",
       "\t<tr><th scope=row>3</th><td>east hazel crest</td><td>197602          </td><td>33.9123853458995</td></tr>\n",
       "\t<tr><th scope=row>4</th><td>posen           </td><td>350584          </td><td>33.1250290850347</td></tr>\n",
       "\t<tr><th scope=row>5</th><td>burnham         </td><td>257298          </td><td>33.0749226953377</td></tr>\n",
       "\t<tr><th scope=row>6</th><td>riverdale       </td><td>585059          </td><td>32.0194086087647</td></tr>\n",
       "\t<tr><th scope=row>7</th><td>flossmoor       </td><td>846481          </td><td>31.3991922613254</td></tr>\n",
       "</tbody>\n",
       "</table>\n"
      ],
      "text/latex": [
       "\\begin{tabular}{r|lll}\n",
       "  & FAC\\_NAME & COST\\_REAL\\_LOSS & PREALLOSSES\\\\\n",
       "\\hline\n",
       "\t1 & maywood          & 1660688          & 38.7253005774924\\\\\n",
       "\t2 & hometown         & 163244           & 37.3359591488175\\\\\n",
       "\t3 & east hazel crest & 197602           & 33.9123853458995\\\\\n",
       "\t4 & posen            & 350584           & 33.1250290850347\\\\\n",
       "\t5 & burnham          & 257298           & 33.0749226953377\\\\\n",
       "\t6 & riverdale        & 585059           & 32.0194086087647\\\\\n",
       "\t7 & flossmoor        & 846481           & 31.3991922613254\\\\\n",
       "\\end{tabular}\n"
      ],
      "text/plain": [
       "          FAC_NAME COST_REAL_LOSS PREALLOSSES\n",
       "1          maywood        1660688    38.72530\n",
       "2         hometown         163244    37.33596\n",
       "3 east hazel crest         197602    33.91239\n",
       "4            posen         350584    33.12503\n",
       "5          burnham         257298    33.07492\n",
       "6        riverdale         585059    32.01941\n",
       "7        flossmoor         846481    31.39919"
      ]
     },
     "execution_count": 52,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# The losses exacted an insidious cost in town after town. In addition \n",
    "# to the $1.66 million that Maywood residents paid for all of its \n",
    "# wasted water in 2016, Hometown residents paid $163,000; Flossmoor,\n",
    "#  $846,000; East Hazel Crest, $198,000; Posen, $351,000; and Burnham, $257,000. \n",
    "\n",
    "data %>% arrange(desc(PREALLOSSES)) %>% \n",
    "      select(FAC_NAME, COST_REAL_LOSS, PREALLOSSES) %>% head(7)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "29.1627544675424"
      ],
      "text/latex": [
       "29.1627544675424"
      ],
      "text/markdown": [
       "29.1627544675424"
      ],
      "text/plain": [
       "[1] 29.16275"
      ]
     },
     "execution_count": 53,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Over a quarter of pipes in the Lake Michigan water \n",
    "# use system are more than 60 years old, according to \n",
    "# reports submitted to IDNR. Another quarter consists of pipes \n",
    "# under 20 years old. \n",
    "\n",
    "(sum(data$ALL_60,na.rm=T)/sum(data$TOTAL_PIPE_LENGTH,na.rm=T))*100"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "25.7124857125859"
      ],
      "text/latex": [
       "25.7124857125859"
      ],
      "text/markdown": [
       "25.7124857125859"
      ],
      "text/plain": [
       "[1] 25.71249"
      ]
     },
     "execution_count": 54,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(sum(data$ALL_20,na.rm=T)/sum(data$TOTAL_PIPE_LENGTH,na.rm=T))*100\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "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.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}