notebooks/file-formats.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# File formats analysis"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "from pprint import pprint\n",
    "import seaborn\n",
    "from IPython import display\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Load the data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "requests = pd.read_csv(\"../data/muckrock-requests.csv\").set_index(\"id\")\n",
    "requests.index.name = \"request_id\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "files = pd.read_csv(\"../data/muckrock-files.csv\").set_index(\"request_id\").join(pd.DataFrame({\n",
    "    \"request_slug\": requests[\"slug\"],\n",
    "    \"jurisdiction_name\": requests[\"jurisdiction_name\"],\n",
    "    \"agency_name\": requests[\"agency_name\"]\n",
    "})).reset_index().set_index(\"id\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th>id</th>\n",
       "      <th>25875</th>\n",
       "      <th>79696</th>\n",
       "      <th>75728</th>\n",
       "      <th>28202</th>\n",
       "      <th>36161</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>request_id</th>\n",
       "      <td>12865</td>\n",
       "      <td>23578</td>\n",
       "      <td>17618</td>\n",
       "      <td>10772</td>\n",
       "      <td>333</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>access</th>\n",
       "      <td>public</td>\n",
       "      <td>public</td>\n",
       "      <td>public</td>\n",
       "      <td>public</td>\n",
       "      <td>private</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>communication_id</th>\n",
       "      <td>95090</td>\n",
       "      <td>232723</td>\n",
       "      <td>228147</td>\n",
       "      <td>101642</td>\n",
       "      <td>129903</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>date</th>\n",
       "      <td>2014-08-18T10:23:58.616069</td>\n",
       "      <td>2016-03-03T00:00:00</td>\n",
       "      <td>2016-02-25T07:12:07.582426</td>\n",
       "      <td>2014-09-26T00:00:00</td>\n",
       "      <td>2015-02-23T14:32:51.521385</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>description</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>doc_id</th>\n",
       "      <td>1276692-goatscapingapproval</td>\n",
       "      <td>2759302-REPREADatabase594-pdf</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1307548-d12-ebb-sp-800-90a-january-2012-pdf</td>\n",
       "      <td>1674382-2014-hqfo-00278_part1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ffile</th>\n",
       "      <td>https://d3gn0r3afghep.cloudfront.net/foia_file...</td>\n",
       "      <td>https://d3gn0r3afghep.cloudfront.net/foia_file...</td>\n",
       "      <td>https://d3gn0r3afghep.cloudfront.net/foia_file...</td>\n",
       "      <td>https://d3gn0r3afghep.cloudfront.net/foia_file...</td>\n",
       "      <td>https://d3gn0r3afghep.cloudfront.net/foia_file...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>pages</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>136</td>\n",
       "      <td>1139</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>source</th>\n",
       "      <td>Boston City Clerk</td>\n",
       "      <td>San Diego County Sheriff's Department</td>\n",
       "      <td>Federal Bureau of Investigation</td>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>Department of Homeland Security</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>title</th>\n",
       "      <td>goatscapingapproval</td>\n",
       "      <td>REPREADatabase594.pdf</td>\n",
       "      <td>~WRD000</td>\n",
       "      <td>D12 EBB - SP 800-90A (January 2012).pdf</td>\n",
       "      <td>2014-HQFO-00278_Part1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>agency_name</th>\n",
       "      <td>Boston City Clerk</td>\n",
       "      <td>San Diego County Sheriff's Department</td>\n",
       "      <td>Federal Bureau of Investigation</td>\n",
       "      <td>Department of Commerce</td>\n",
       "      <td>Department of Homeland Security</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>San Diego County, CA</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>United States of America</td>\n",
       "      <td>United States of America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>request_slug</th>\n",
       "      <td>all-goatscaping-performance-assessments</td>\n",
       "      <td>recorded-securus-phone-calls-andor-communicati...</td>\n",
       "      <td>jm-porup-fbi-files</td>\n",
       "      <td>dual-ec_nist</td>\n",
       "      <td>faith-based-czar-communication-dhs</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "id                                                             25875  \\\n",
       "request_id                                                     12865   \n",
       "access                                                        public   \n",
       "communication_id                                               95090   \n",
       "date                                      2014-08-18T10:23:58.616069   \n",
       "description                                                      NaN   \n",
       "doc_id                                   1276692-goatscapingapproval   \n",
       "ffile              https://d3gn0r3afghep.cloudfront.net/foia_file...   \n",
       "pages                                                              2   \n",
       "source                                             Boston City Clerk   \n",
       "title                                            goatscapingapproval   \n",
       "agency_name                                        Boston City Clerk   \n",
       "jurisdiction_name                                         Boston, MA   \n",
       "request_slug                 all-goatscaping-performance-assessments   \n",
       "\n",
       "id                                                             79696  \\\n",
       "request_id                                                     23578   \n",
       "access                                                        public   \n",
       "communication_id                                              232723   \n",
       "date                                             2016-03-03T00:00:00   \n",
       "description                                                      NaN   \n",
       "doc_id                                 2759302-REPREADatabase594-pdf   \n",
       "ffile              https://d3gn0r3afghep.cloudfront.net/foia_file...   \n",
       "pages                                                              1   \n",
       "source                         San Diego County Sheriff's Department   \n",
       "title                                          REPREADatabase594.pdf   \n",
       "agency_name                    San Diego County Sheriff's Department   \n",
       "jurisdiction_name                               San Diego County, CA   \n",
       "request_slug       recorded-securus-phone-calls-andor-communicati...   \n",
       "\n",
       "id                                                             75728  \\\n",
       "request_id                                                     17618   \n",
       "access                                                        public   \n",
       "communication_id                                              228147   \n",
       "date                                      2016-02-25T07:12:07.582426   \n",
       "description                                                      NaN   \n",
       "doc_id                                                           NaN   \n",
       "ffile              https://d3gn0r3afghep.cloudfront.net/foia_file...   \n",
       "pages                                                              0   \n",
       "source                               Federal Bureau of Investigation   \n",
       "title                                                        ~WRD000   \n",
       "agency_name                          Federal Bureau of Investigation   \n",
       "jurisdiction_name                           United States of America   \n",
       "request_slug                                      jm-porup-fbi-files   \n",
       "\n",
       "id                                                             28202  \\\n",
       "request_id                                                     10772   \n",
       "access                                                        public   \n",
       "communication_id                                              101642   \n",
       "date                                             2014-09-26T00:00:00   \n",
       "description                                                      NaN   \n",
       "doc_id                   1307548-d12-ebb-sp-800-90a-january-2012-pdf   \n",
       "ffile              https://d3gn0r3afghep.cloudfront.net/foia_file...   \n",
       "pages                                                            136   \n",
       "source                                        Department of Commerce   \n",
       "title                        D12 EBB - SP 800-90A (January 2012).pdf   \n",
       "agency_name                                   Department of Commerce   \n",
       "jurisdiction_name                           United States of America   \n",
       "request_slug                                            dual-ec_nist   \n",
       "\n",
       "id                                                             36161  \n",
       "request_id                                                       333  \n",
       "access                                                       private  \n",
       "communication_id                                              129903  \n",
       "date                                      2015-02-23T14:32:51.521385  \n",
       "description                                                      NaN  \n",
       "doc_id                                 1674382-2014-hqfo-00278_part1  \n",
       "ffile              https://d3gn0r3afghep.cloudfront.net/foia_file...  \n",
       "pages                                                           1139  \n",
       "source                               Department of Homeland Security  \n",
       "title                                          2014-HQFO-00278_Part1  \n",
       "agency_name                          Department of Homeland Security  \n",
       "jurisdiction_name                           United States of America  \n",
       "request_slug                      faith-based-czar-communication-dhs  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files.sample(5).T"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Extract file extensions"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "get_ext = lambda x: os.path.splitext(x)[1].lower()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "files[\"file_ext\"] = files[\"ffile\"].apply(get_ext)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Try to identify files that are most likely just letters\n",
    "\n",
    "Far from perfect."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "likely_letter_pat = (\n",
    "    r\"letter|acknowled|denial|denying|\"\n",
    "    r\"request denied|no responsive|initial response|interim response|\"\n",
    "    r\"request rejected|fix required|payment required\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "files[\"is_likely_letter\"] = (\n",
    "    files[\"description\"].str.contains(likely_letter_pat, case=False, na=False) |\n",
    "    files[\"title\"].str.contains(likely_letter_pat, case=False, na=False)\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "non_letters = files[files[\"is_likely_letter\"] == False]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "22% of files appear to be letters\n"
     ]
    }
   ],
   "source": [
    "print(\"{0:.0f}% of files appear to be letters\".format(files[\"is_likely_letter\"].mean() * 100))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "57% of requests appear to include letter files\n"
     ]
    }
   ],
   "source": [
    "print(\"{0:.0f}% of requests appear to include letter files\"\\\n",
    "      .format((files.groupby(\"request_id\")[\"is_likely_letter\"].sum() > 0).mean() * 100))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "NOTE: Those numbers seem too low..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What are the most common file formats?"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For all files:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       ".pdf     44953\n",
       ".jpg      7806\n",
       ".msg      2236\n",
       ".png      2093\n",
       ".eml      1420\n",
       ".xlsx     1099\n",
       ".docx     1005\n",
       ".doc       654\n",
       ".zip       480\n",
       ".gif       460\n",
       ".xls       408\n",
       "           402\n",
       ".txt       135\n",
       ".wav        97\n",
       ".mp4        67\n",
       "Name: file_ext, dtype: int64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files[\"file_ext\"].value_counts().head(15)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "For non-letters:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       ".pdf     31617\n",
       ".jpg      7803\n",
       ".msg      2219\n",
       ".png      2092\n",
       ".eml      1405\n",
       ".xlsx     1099\n",
       ".docx      741\n",
       ".doc       511\n",
       ".zip       479\n",
       ".gif       460\n",
       ".xls       407\n",
       "           395\n",
       ".txt       135\n",
       ".wav        97\n",
       ".mp4        67\n",
       "Name: file_ext, dtype: int64"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "non_letters[\"file_ext\"].value_counts().head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAngAAAGDCAYAAAC4Km19AAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3Xt4JGWV+PFviMqqM+AM9qKoa2ZmJwfQVVBZBVQu4h1W\nLorXXVlYQEXlhyCiIIiiCCIMCsriooIoooCroIgiKAJB5aqscOIlgiJiMAiMcpshvz+qIm2TyUxS\nfUl3vp/nyZOuqrerTx+KmTNvve9bfePj40iSJKl3rNXpACRJktRcFniSJEk9xgJPkiSpx1jgSZIk\n9RgLPEmSpB5jgSdJktRjHtHpACQ1X0T8Bvgn4OOZ+e5Jjv8T8BtgHKhl5liTPndLYL/MfPUUbT4H\nvLlh933ACPBl4IjMfLAZ8aypiPgAsH9mzm/Bub8PvHAVh/+QmRtExJuBz1L+t4iIEeDczHznGn7G\no4CjgYsy8xvNiFtSd7PAk3rTOPAgsBPwsAIPWGUBVtF/AYNr0O5XwBuAvnL70cAOwAeAecCBrQhu\nCuPlT6vOfSmwPw993wn3l7/PAzYH/jzDz3gi8E7gkhm+X1KPscCTetflwJYR8czMvK7h2GuA64Bn\ntD8sAO7JzJ807PtBRDwD2Dsi3puZKzsRWIv8eZLv+zeZ+SfgTxXO31g4SprjLPCk3nUtRc/OLhTF\nHAAR8RRgM+BQGgq8iNgJeC+wMTAGfB44fKLYiohBYBlFb9NaFEXkgZn5s/pbrxGxEtgmM6fbo3Q1\n8CJgITAaEY+l6NXbBXgC8DPgkMz8bvk5WwEXA1sDJwJLyjYHZuYPyja7Udz+fPzEreiIWBe4A9gt\nM09rDCIiHgG8H3gd8FTgr+Xn7JuZvyvbTNxS3hp4JvD+zPz4NL/vxOc9LMaG448BjqIozNcBfkRx\nK/zaiHgq8GuKnsKzylvC5wDHARtk5mjdeU4GNs3Mzcr4PwH8K0Xv6R3AyZn5oYbPfifwdopb/r8E\nPpiZX6k7/grgcIprZjlFb+QBmXnHTHIhqTmcZCH1trOBnRv2vRq4ArilfmdE7FW2vwLYkeIv/wOA\nz5XH+yj+8u6nKDReCzweOK889iHgWxS3X59HUaxN1yDwl8wcLc95AUXR+BGK2803Ad+KiBc3vO+r\nwOnld70D+HZEbFgem8nt12XAPuXnvhh4H0XheVxDu3cBX6PI6VRj3/oior/xp+746mI8F9i1jOPV\nwL3A9yNiEfB7iu/dBxwEvA04g+IW/a4TJ4iIR1IUyqfWnfdQ4DHl/pOBQyPi8Lr3HAYcA3wJ2B74\nDnBGROxSHl9Ccc38EHh5mY8dgBOm+C6S2sAePKm3nQ0cGBGDmTlc7nsNcGZ9o4hYi6JA+1LdwP4L\nI+Iu4NMRcTQwCvwzRU/VheX7bqYYSzcvM38dEaPAP011O7LuM+sLnH+kKEZeRVFcQVFQbAG8ZOLz\ngAsi4nKKwuu7de8/LjOPKs97EUWP1ruBPVYXxyqsRzHpYqIY+mFZML6hod3PM/PoNTjfK4EHGvaN\nR8RqJ7hExEuBbYAXZebF5b5vAz8HDs7M/4qIa8rmv8zMG8s23yrjPbEuhnkUxd+EW4EdM3OcIrfr\nAu+KiI8A/wC8BzgyMz9Qtr8wItYBPkpxbT0HeBRwVGbeVn7ucopeT0kdZIEn9bDM/ElE/Jaih+bI\nutuzrwG2q2u6IVADzmo4xZeBkyhmgX4aGAb+p+xB+xZwQWYeMoPQns7DC577gdOAifO9ALirrrir\nj+nY8vYtFD1ffytaMvP+iDgf2GoGcU2c4/UAEbEBEMBGwPOBtRubruEpfwj8Px4+Vm5NJlVsA/yF\nosicKIr7KHrTdpjifadR3LJ9ambeBLwR+E453m/CmWVxN+FrwH7As4H5FN/3Ww3F+LeB3ctbwz+m\n+O/2k4j4MvBNitm/bZ0FLenhLPCk3jdxm/ZIikLvx5l5S0TUt1lAUSjdVr8zM++KiPuAdTJzPCJe\nRDEmbkfgP4F7I+KkzNx/mjH9kuIWb1/5uX8FRjLz/ro2CxrjKd1Wvq9+SZPfN7QZLd8/IxGxBUVB\n+y8URdg1wD08vED74xqe8s7MvGb1zSa1EHgsD824nTA+yb5651Hcrn5dRHyaogevcXmayfLWR5G7\ndcvXl/Pw7/0g8MTMvCIitqUYt/l2ilv6t0XEgZn5hTX4bpJaxAJP6n1nA/uWPS67AF+ZpM0YxV/i\n69fvLG/ZrU05wzMzbwH2BPaMiOdRLIuyX0RckZlfnUZM965BwTPWGE/piXXHJ6wH/KFue30eKr4m\neqjqxxzPW9WHlrcgz6VYcmTHzBwp9x9FMZmi3e6kKGpfwTRmy2bmA2Wv2quBmymKwcZxgus1bK9P\nka/6wnVHGsZrTnxE+TlDwL9FxD9QjFM8EDglIi7MzFvXNF5JzeUkC6n3XU4x1uotwHN5+G1YKP6y\nvp3i1m2911H8hX9ZRGwSEbdFxCYAmXkFsBewgmKGJUAzlza5FJg/yYSK1wJX1vX29VGM1wOgLDRe\nDnyv3HVX+XuDunO8kFVPatiQogfr+Lribi3gJXRmOZJLKW6f/yUzr574AV5PcdsVVp330yhut74F\nOCsz72s4vn3D9s4UPZZXU8zUfQBYv+FzN6aYYdwXEXtHxEhE9GfmvZn5zfJYP3+fb0ltZg+e1OPK\nW6v/SzHD8SdlL1xjmwfL2ZOfiIg7gK9T9FZ9APhKZv68XDrkz8BpZdsxYDeK4uK88lR/Bp4cEdsB\nV1VcKuObFGO8To+IQyh6oXanGEPYOPbs6PJpDiMUCwo/muLJDlAsb3IfcHxEfJhiAsAh5b7J3Ajc\nTTGj9BEUs0zfBjyJYuJBu50LXAmcX+b9ZopeubcCe5dt7ix/bxcRv87MawEy88cRkRTjBw+d5NzP\nK5e3+VLZZh/gXeWyOLdHxCeAj0fEQor/FpsCRwBfy8zl5ZIsx1GM9fsURW/vwRQzqa9tZhIkTY89\neFJvalx242yKf9BNdnsWgMw8kWLW6dYUt/LeBnwMeFN5fAXFbcJfAp+iKOqWAq/IzInJBv9NcTvx\nXIrlRaaKb0rlQP2XUqzpdkT5HZ4EvDwzz284137AO8rvtxJ4/sR6dZl5J0XPZK2M663ld1q+is+9\ni6In63EUhe4yitu12wNrRcS/1n3umi6/Mt1lWv527jIPL6GYVHEUReH7fIo1/P6nbHM3xczWf+fv\nl0EBOB/47cS6gA1OoLhd/b8UvbX7ZObfljgpH3P3IYpb8edT5PhYivGXlP/d/41iFvRZwBcoxvW9\ntMcWqpa6Tt/4eKueziNJrVUudHwRsDQzf93peGajiLgeODszD2vYPwJ8ITMn69mT1OW8RSup2/mY\nrkmUixQ/C1hEsYixpDnEAk9St/M2xOReTXHrdI/Jxl1i3qSe5i1aSZKkHuMkC0mSpB7T9bdoV6xY\nOX7HHX/tdBhdacGCx2DuZs78VWP+Zs7cVWP+qjF/1dRq89sybrjre/Ae8Yj+1TfSpMxdNeavGvM3\nc+auGvNXjfnrDl1f4EmSJOnvWeBJkiT1mK4fgzc8PMzY2KQL0ms17rhjnrmrwPxVY/5mztxVY/6q\naVf+BgYW09/v7eCZ6vplUvr6Lhgv1vGUJEm9YYShoRpLliztdCBN165JFl3fg1cUd4OdDkKSJDWV\nvaxVOAZPkiSpx8yKAi8ifhER/1G+XjsizouIeyLi1E7HJkmS1G1m4y3alwHbAc8FftfhWCRJkrrO\nbCzwHgeMZuZ1nQ5EkiSpGzW9wIuIrYDTgaOBw4AVwAmZeURdmz2BgymKuWPr9h9WvoeIWAlsk5mX\nNDtGSZKkXtaqMXjrA28EtgX2Ag6IiL0BIuIlwCeB9wPPA54OLC7f9zHg/wG/B54AXN6i+CRJknpW\nqwq8fmC3zPxpZn4DWAbsXR77L+CMzPxCZt5Ybt8DkJl/Be4EHszM0cxc0aL4JEmSelarxuDdWRZv\nE64E3lO+3hj4zMSBzLwrIn7RojgkSVIXWrhwHrXa/E6H0bVaVeA19rz1AyvrthtXcX6gRXFIkqQu\nNDa2nNHRuzsdRtO1q2ht1S3a9SLiyXXbmwETs2KvL7cBiIjH4qMoJEmSmqZpPXgRsYCHeun6gFMi\n4l3AhsA7KCZbAJwAXBgRlwDfBw4BHtusOCRJkua6ZvbgnUMxmWLCBcClwHHAQZl5JkBmXgr8B/Bu\nirF5fwauaGIckiRJc1rf+Ph4U09YroN3UWb2N/XEq9DXNzzuHV5JknrJMENDy1myZGmnA2m6Wm1+\n4zyElpgVz6KVJElS81jgSZIk9ZimL5OSmT+gWBalTUba91GSJKkNRoBap4Poaq1aB69tMhcxNra8\n02F0pYUL55m7CsxfNeZv5sxdNeavmvbkr8bAwOLVN9MqNX2SRQeM9+JCiO1Qq83vyUUk28X8VWP+\nZs7cVWP+qjF/1TjJQpIkSTPS9bdoh4eH7WqfoTvu6L7bFAMDi+nvb+MQT0mSulDXF3gRI8CiTofR\nxeZ1OoBpGGFoiJ5cF0mSpGbq+gKvKO5c6Hju6K4eR0mSOqElBd7E0yyAR2bmg634DEmSJE2uVZMs\nLgOeaHEnSZLUfi3pwcvMFcAfW3FuSZIkTa2Vt2gvLs//FIolqd8IfAx4DHA6sF9mrizbvwQ4BlgC\n/AD4JTA/M/+zFfFJkiT1slaug9e4gvIhwKuBncqfIwAiYjHwdeBMYBPgJ8A+k7xfkiRJa6Cds2j3\nz8wrACLi/RS9ee8F/gu4KjM/XLY7LCJe3Ma4JEmSekq7nmQxDlxet30lsDAi1gf+haLXrt5Qm+KS\nJEnqOe3swVtR93riUQQry/2Nz2Vry3Pa1H0WLpxHrTa/02H8zWyKpRuZv5kzd9WYv2rM3+zXrgKv\nD9iUYvkUgM2AWzPz9oj4P2CrhvbPBn7VptjURcbGls+ah1z7wO1qzN/MmbtqzF815q+adhXHTS3w\nImIBRa/cZD4ZEXsCC4DDgU+U+08G9o+I9wJnUUzEeAHFTFpJkiRNU7PH4J0DLFvFsa8C3wS+CJyc\nmUcBZObNFEXdbsBPgc2BrwH3Nzk2SZKkOaGpPXiZuQ1ARLwIeDAzH4wIKCZZfDEzj2x8T0Q8Dbgl\nM6Nu33nArc2MTZIkaa5o+hi8iBgAtuPvC7SpJk0sAT4XEa8FhoGXANsCBzU7NkmSpLmgFZMsjga2\nAd5Tt2+VixZn5jci4hjgFKAGJLBrZl7fgtgkSZJ6XtMLvMzctWH7Jh5aFmVV7zkSeNjtW0mSJE1f\nO9fBa5GRTgegthmh6OSVJElT6foCL3MRY2PLOx1GV1q4cF6X5a7GwMDiTgchSdKs1/UF3uDgoAsu\nzpCLVUqS1Jva9SxaSZIktUnX9+ANDw/PmtuMAwOL6e+fcj6JJElSy3V9gRcxAizqdBjACENDsGTJ\n0k4HIkmS5riuL/CK4m6w00GUZkdPoiRJmtscgydJktRjLPAkSZJ6jAWeJElSj5nxGLyI2Ao4HXg/\nxWPG1gaOAK4CTgKeBJyVmbuX7Z8OnAg8G7gL+BLwnsxcGRF95Tn2KE+/DNgN2CMzL5lpjJIkSXNR\n1R689YFdgK0oCrSjgI8B/17+vCkiXlm2PR24AXgasCvwJmD38tj7yvavB7YDtmd2TI2VJEnqOlUL\nvH7ggMwcBj5dbp+QmVdm5teBnwMblm0HgNuB32bmpcDLgQvKY28FDs3MCzPzOuDNTYhNkiRpTmpG\nETVS/r6n/H1z3bF7KG7dAnwIOAi4LSJOA56YmTdHxHrABsCVE28qC8Y7mhCbJEnSnNOMdfBWNGw/\nOFmjzPx4RJwJvAp4JfC/EXEEcHzZpK/hLY3bs97ChfOo1eZ3Ooxp6bZ4ZxvzV435mzlzV435q8b8\nzX5tWeg4ItalmIBxVGaeCJwYEQcDb8jMD0bE7ykmX1xbtl8MPK4dsTXT2NhyRkfv7nQYa6xWm99V\n8c425q8a8zdz5q4a81eN+aumXcXxtAu8iFgArJzOezLzzoh4CfCkiHgf8EiKMXgTt2U/CXwgIm4C\nRil69cbLH0mSJE3DTMbgnUOxjMlkGguy+u1XAfOAK4AfAL8A3lkeOwY4C/gqcCFwHsWt3/tnEJ8k\nSdKcNu0evMzcpm6zv27/yvrtct8Wda9vBF6yitNuBxyRmfsBRMTjgaOBW6cbnyRJ0lzXljF4a2Bv\n4O0RcWC5/UHgR5l58xTvkSRJ0iRmy1pz+wAPAJcBl5f7du5cOJIkSd1rVvTgZeatWNBJkiQ1xawo\n8KoZWX2TthgBap0OQpIkqfsLvMxFjI0t73QYQI2BgcWdDkKSJKn7C7zBwUEXXJQkSaozWyZZSJIk\nqUm6vgdveHi447doBwYW09/fv/qGkiRJbdD1BV7ECLCogxGMMDQES5Ys7WAMkiRJD+n6Aq8o7gY7\nHMNsmOQhSZJUcAyeJElSj7HAkyRJ6jEWeJIkST2mKWPwImIr4HTgaOAwYAVwQmYeUR4/DNgQuB34\nd+B+4NjM/Gh5vA84EtijPOUyYDdgj8y8pBkxSpIkzRXN7MFbH3gjsC2wF3BAROxdd3xnisLuWRSF\n4EciYsPy2PsoCr/XA9sB29PZqbGSJEldq5kFXj+wW2b+NDO/QdELV1/g3QEckJm/zsxjgDHgOeWx\ntwKHZuaFmXkd8OYmxyZJkjRnNLOIujMzb6zbvhLYqG77N5k5Xrd9N/DIiFgP2KBsD0BmDlMUhJIk\nSZqmZq6Dt6Jhux9YWbd9/yTv6at7X98kx7rCwoXzqNXmdzqMGenWuGcL81eN+Zs5c1eN+avG/M1+\nzSzw1ouIJ2fm78rtzYDrVvemzLwzIn4PPBu4FiAiFgOPa2JsLTU2tpzR0bs7Hca01WrzuzLu2cL8\nVWP+Zs7cVWP+qjF/1bSrOK5U4EXEAh7qpesDTomId1HMmH0HxWSLNfFJ4AMRcRMwChwPjJc/kiRJ\nmoaqPXjnACPAqeX2BcClFOPrDsrMM6d4b33xdgzwBOCrFLdsjwI2Z/LbupIkSZpCpQIvM7eBv62D\nN56ZxwLHTtLucODwhn2L6za3A47IzP3K8z2eYimVW6vEJ0mSNBc1cwxeFXsDb4+IA8vtDwI/ysyb\nOxiTJElSV5ota83tAzwAXAZcXu7buXPhSJIkda+m9OBl5g8olkWZ6ftvxYJOkiSpKWbLLdoKRmbB\n59c6HIMkSdJDur7Ay1zE2NjyDkZQY2Bg8eqbSZIktUnXF3iDg4MuuChJklRntkyykCRJUpN0fQ/e\n8PBwS27RDgwspr9/xvNGJEmSOqbrC7yIEWBRk886wtAQLFmytMnnlSRJar2uL/CK4m6wBeft5MQN\nSZKkmZu1Y/Ai4sGI2LbTcUiSJHWbWVvgSZIkaWYs8CRJknpMU8fgRcTTgE8AmwO/A07KzGPLY4dR\nDJa7Hdgd+COwJ7AxcAjQBxyemZ9qZkySJElzTdN68CLiH4DzgcuBpwPvAPaNiH3qmu0C3AU8A7ga\nOAt4EbAV8N/AcRHxuGbFJEmSNBc18xbtG4A/Zeb7M/PXmXkB8H5gv7o2Y+XxEeBUYF1g38xM4Fjg\nkcCSJsYkSZI05zTzFu1GwNMjov65YWsBj4yIic/5Td2xewAy8+b6bWDtJsYkSZI05zSzwHsEcDGw\nN8V4unory98rmvh5LbVw4TxqtfmdDqPl5sJ3bCXzV435mzlzV435q8b8zX7NLPAS2Am4KTMfBIiI\nnYBXZOaeEdHEj2q9sbHljI7evfqGXaxWm9/z37GVzF815m/mzF015q8a81dNu4rjygVeRCyg6KE7\nHTgMOCUijgKeAnwK+FzVz5AkSdKaa8Yki3OAZZm5HHgZ8FTgKorC7nMUS6CsqfHyh7rfkiRJmoa+\n8fHurqP6+obHm/8s2mGGhpazZMnSJp93drGbvRrzV435mzlzV435q8b8VVOrzW+cp9ASPslCkiSp\nx1jgSZIk9RgLPEmSpB7T1GfRdsZIi85Za8F5JUmSWq/rC7zMRYyNLW/yWWsMDCxu8jklSZLao+sL\nvMHBQWfzSJIk1XEMniRJUo/p+h684eHhyrdoBwYW09/f36SIJEmSOqvrC7yIEWBRhTOMMDREzy9q\nLEmS5o6uL/CK4q7qkyyaPUlDkiSpc1pa4EXEVsDFwCMy88FWfpYkSZIK7Zhk0d0Pu5UkSeoyzqKV\nJEnqMZVv0UbE4cDbgaWZORYRmwOXAC+apO3bgP2BDYBh4H2Z+c2I+E/gJOBfMnM4IhYBPwPemplf\nqBqjJEnSXNKMHrwPA7cBH4mIRwKfAT6dmZfUN4qITYHjgH0pZkWcCZwZEetk5ueAHwKfKJufDFxk\ncSdJkjR9lQu8zLwf2AvYAzgVeDRw0CRNnwo8CNycmb8FjgR2BO4vj+8NvCAiTgeeVZ5TkiRJ09SU\nMXiZeSnweeC1wD6Z+ddJml0AXANcGxE/BT4E/Coz7y3P8SvgI8AbgIMz8w/NiE2SJGmuacoyKRHR\nBzwdWAlsA3y7sU1m3gNsERFbAtsDuwD7RMQLMvP6stkmwIryHCc1I7Y1sXDhPGq1+e36uFllrn7v\nZjF/1Zi/mTN31Zi/aszf7NesdfDeAQxQFG1nR8QZmXltfYOI2BbYMjM/BFwWEe8DbgReDlwfETsA\nrwBeCZwbEdtn5nlNim9KY2PLGR29ux0fNavUavPn5PduFvNXjfmbOXNXjfmrxvxV067ieMa3aCNi\nQUSsExFPobjd+u7MPJdi8sQpZa9evfuAQyNir4h4KvAq4MnAlRExHzgRODIzvwt8DPh0RMybaXyS\nJElzVZUxeOcAy4ATgGsy8/Ry/wHAYorlUP4mMy+jmEjxLuAGiiJuv8y8mGIm7r3A0WXzD1NMvvho\nhfgkSZLmpBnfos3MbVax/zZgQd2u/rpjnwU+O8l73tmwfS+wZKaxSZIkzWU+yUKSJKnHWOBJkiT1\nmGbNou2gkSa8v9aMQCRJkmaFri/wMhcxNra8whlqDAwsblo8kiRJndb1Bd7g4KDr8UiSJNVxDJ4k\nSVKPscCTJEnqMV1/i3Z4eHiNx+ANDCymv79/9Q0lSZK6WNcXeBEjwKI1aDnC0BAsWbK01SFJkiR1\nVNcXeEVxN7iGbavMtpUkSeoOlcbgRcRnIuJhjx6TJElS5zjJQpIkqcdY4EmSJPWYaY3Bi4gtgU8A\nGwLnA/cD99Yd3x44HNiI4hlgh2XmWeWxtYAPALsD84GLgb2BlcANwKcy87Cy7beAR2XmdhW+myRJ\n0py0xj14EfF44JvA94BNgKuA19Ud3xY4G/g88AzgM8CXIuI5ZZPDKYq7PYDNgEcDp2Xm7cD+wLsj\nYiAi3gg8v2wrSZKkaZpOD96uwJ8y88By+8iIeEXd8X2AczLzk+X2soh4LvBu4LXAXsBBmXkBQES8\nhaLYIzNPKwu7k4BnAQdm5s0z/VKSJElz2XQKvI2BnzbsuxJYt3y9EXByw/HLgT3L3r8acPXEgcwc\nAQ6pa/sWilu1V2fmSdOIS5IkSXWmuw5eX8P2A3Wv75mkfX/5c/8anHvjsu2GEfGEzPzDNGNbrYUL\n51GrzW/2abua+ajG/FVj/mbO3FVj/qoxf7PfdAq864EdImKtzHyw3Lcp8Nvy9Y3AcxveswWQmXlX\nRIyW7a8DiIilwA8pJmw8AJwIvB94Ufl6l+l/namNjS1ndPTuZp+2a9Vq881HBeavGvM3c+auGvNX\njfmrpl3F8WoLvIhYQDHT9csUs2A/GRHHA9sDWwNfKJseC1weEftSTMbYHtgReFl5/Hjg8Ij4HXAL\nsAy4MjP/HBHLKHoAjwG+DlwbETtl5tea8SUlSZLmkjWZRXsOsCwz/wy8hGISxDUUhdtnJhpl5lXA\nGyiWPvkZsBvwmsy8qGxyFEWR+EWKsXljwG7lLNu3Aftk5orMvAE4jqKQtA9YkiRpmvrGx8c7HUMl\nfX3D42v2LNphhoaWs2TJ0pbH1C3sZq/G/FVj/mbO3FVj/qoxf9XUavMb5zO0hE+ykCRJ6jEWeJIk\nST1musukzEIj02hXa2UgkiRJs0LXF3iZixgbW74GLWsMDCxueTySJEmd1vUF3uDgoIM9JUmS6jgG\nT5IkqcdY4EmSJPWYrr9FOzw8POUYvIGBxfT397cxIkmSpM7q+gIvYgRYtIqjIwwN4eLGkiRpTun6\nAq8o7qZ6ksWazLCVJEnqHS0bgxcRn4mIz7bq/JIkSZqckywkSZJ6jAWeJElSj2naGLyI2BL4BLAh\ncD5wP3Bv3fHtgcOBjSieG3ZYZp5VHlsL+ACwOzAfuBh4S2b+oVnxSZIkzRVN6cGLiMcD3wS+B2wC\nXAW8ru74tsDZwOeBZwCfAb4UEc8pmxxOUdztAWwGPBo4tRmxSZIkzTXN6sHbFfhTZh5Ybh8ZEa+o\nO74PcE5mfrLcXhYRzwXeDbwW2As4KDMvAIiIt1AUe5IkSZqmZhV4GwM/bdh3JbBu+Xoj4OSG45cD\ne5a9fzXg6okDmTkCHNKMwBYunEetNr8Zp+pJ5qYa81eN+Zs5c1eN+avG/M1+zVwHr69h+4G61/dM\n0r6//Lm/iTE8zNjYckZH727lR3StWm2+uanA/FVj/mbO3FVj/qoxf9W0qzhu1iza64FNy8kSEzat\ne30j8NyG92wBZGbeBYzWt4+IpRHxh4h4XJPikyRJmjMq9eBFxAJgJfBlilmwn4yI44Htga2BL5RN\njwUuj4h9KSZjbA/sCLysPH48cHhE/A64BVgGXJmZf64SnyRJ0lxUtQfvHGBZWYi9BHgWcA1F4faZ\niUaZeRXwBmBv4GfAbsBrMvOisslRFEXiFynG5o2VbSRJkjRNlXrwMnObutc/BTafou3ZFEulTHZs\nJfCe8keSJEkV+CQLSZKkHmOBJ0mS1GOauUxKh4ys5litXYFIkiTNCl1f4GUuYmxs+SqO1hgYWNzW\neCRJkjqt6wu8wcFBF1yUJEmq4xg8SZKkHmOBJ0mS1GO6/hbt8PDwKsfgDQwspr+/v80RSZIkdVbX\nF3gRI8CiSY6MMDQES5YsbXdIkiRJHdX1BV5R3A2u4tiqZtdKkiT1ro6MwYuIN0fEzZ34bEmSpF7X\nqUkWXwYn3ObcAAAWdklEQVQ27dBnS5Ik9bSO3KLNzPuA+zrx2ZIkSb2uZQVeRHwOeHO5OQ70la+3\nAhYDR2TmUyJiK+B04GjgMGAFcEJmHtGq2CRJknpZK2/RvhN4QvnzROBc4DLg8vL4eF3b9YE3AtsC\newEHRMTeLYxNkiSpZ7WswMvMuzPzj5n5R2BnYHPgtZm5cpLm/cBumfnTzPwGsAywwJMkSZqBlo/B\ni4jNgGOBHTPz96todmdm3li3fSXwnqqfvXDhPGq1+VVP09PMTzXmrxrzN3PmrhrzV435m/1aWuBF\nxHrAV4FjMvM7UzRd0bDdD0zW0zctY2PLGR29u+ppelatNt/8VGD+qjF/M2fuqjF/1Zi/atpVHLfs\nFm1E9AFfAn5JMXliKutFxJPrtjcDrmtVbJIkSb2s6T14EbGAovft3cC/AFsDtbLgg8kfL9EHnBIR\n7wI2BN5BMdlCkiRJ09SKW7TnACMUhd36wA0Nxw8HbmrYNw5cAFwK3A0clJlntiA2SZKkntf0Ai8z\nt1nDpqc2vO9YiskYkiRJqqBTjyqTJElSi1jgSZIk9ZiOPIu2Xmb+gGJZlBkamWJ/beanlSRJ6lId\nL/CqylzE2NhkE3NrDAwsbns8kiRJndb1Bd7g4KALLkqSJNVxDJ4kSVKP6foevOHh4VXcooWBgcX0\n91cY3idJktSFur7AixgBFk1yZIShIViyZGm7Q5IkSeqori/wiuJucBXHJu/ZkyRJ6mWzYgxeRLw5\nIm6u294pIm6JiLsiYuNOxiZJktRtZkWBB3wZ2LRu+1DgW8DTgBs7EpEkSVKXmhW3aDPzPuC+ul3r\nAldk5m87FJIkSVLXaluBFxHPBk4AnglcDXwPeGFmbhMRuwEfysynRMSDwDjwmYh4Y2Zu264YJUmS\nekFbbtFGxDrA+cBVwCbAGcB7KQo5yt8Tr58I3ALsB+zcjvgkSZJ6SbvG4L0O+CvwzswczswTgbMm\na5iZtwErgbsz889tik+SJKlntKvA+xfgmsx8sG7fUJs+W5IkaU5p1xi8FUBfw77G7aZbuHAetdr8\nVn9MVzM/1Zi/aszfzJm7asxfNeZv9mtXgfd/wI4R0ZeZE2PtntPqDx0bW87o6N2t/piuVavNNz8V\nmL9qzN/MmbtqzF815q+adhXHLS3wImIBxXi6M4CPAMdHxAnAC4HXApe28vMlSZLmolaPwTsHWJaZ\nfwF2ALYErgP+A/gCcP8q3je+iv2SJElajZb24GXmNgARMQA8IjOfPXGs7Mm7tWx3KnBq3fsWtzIu\nSZKkXtauMXjrAt+LiDcCP6EYf/cmiuVTJEmS1ERtWSYlM68D3gYcSfFs2Y8A+2Xmt9vx+ZIkSXNJ\n2x5VlpmfBT7brs+TJEmaq9pW4LXOyBT7a+0MRJIkaVbo+gIvcxFjY8snOVJjYMC5GpIkae7p+gJv\ncHDQBRclSZLqtOtZtJIkSWqTru/BGx4envQW7cDAYvr7+zsQkSRJUmd1fYEXMQIsatg7wtAQLFmy\ntBMhSZIkdVTXF3hFcTc4yf7JJl5IkiT1vo4VeBGxFXAxxSPMHuxUHJIkSb2m05Msxjv8+ZIkST2n\n0wWeJEmSmqylt2gj4nDg7cDSzByLiM2BS4AXTdL2bcD+wAZAAgdn5jdbGZ8kSVIvanUP3oeB24CP\nRMQjgc8An87MS+obRcSmwHHAvhQzJr4CnBkR67Q4PkmSpJ7T0gIvM+8H9gL2AE4FHg0cNEnTpwIP\nAjdn5m+BI4EdgftbGZ8kSVIvavkYvMy8FPg88Fpgn8z86yTNLgCuAa6NiJ8CHwJ+lZn3tjo+SZKk\nXtPyZVIiog94OrAS2Ab4dmObzLwH2CIitgS2B3YB9omIF2Tm9TP53IUL51GrzZ954HOEOarG/FVj\n/mbO3FVj/qoxf7NfO9bBewcwQFG0nR0RZ2TmtfUNImJbYMvM/BBwWUS8D7gReDkwowJvbGw5o6N3\nVwq819Vq881RBeavGvM3c+auGvNXjfmrpl3FcUsKvIhYQNFjty7F7dZ9MvPciDgTOCUintPwlvuA\nQyPiNorbtZsCTwaubEV8kiRJvaxVPXjnACPAesA1mXl6uf8Aip65/YGfTDTOzMsiYm/gQGAZcAuw\nX2Ze3KL4JEmSelZLCrzM3GYV+28DFtTt6q879lngs62IR5IkaS7xSRaSJEk9xgJPkiSpx1jgSZIk\n9Zh2LJPSYiOr2FdrdyCSJEmzQtcXeJmLGBtb3rC3xsDA4o7EI0mS1GldX+ANDg664KIkSVIdx+BJ\nkiT1mK7vwRseHp7kFi0MDCymv79/kndIkiT1tq4v8CJGgEUNe0cYGoIlS5Z2IiRJkqSO6voCryju\nBifZ//BePUmSpLnAMXiSJEk9xgJPkiSpx1jgSZIk9RgLPEmSpB5jgSdJktRjLPAkSZJ6jAWeJElS\nj+kbHx/vdAyV9PUNjz98HbxhMovn1EqSJM0ife34kB5Y6HhyY2PLGR29u9NhzGq12nxzVIH5q8b8\nzZy5q8b8VWP+qqnV5rflc7xFK0mS1GMs8CRJknqMBZ4kSVKPscCTJEnqMRZ4kiRJPcYCT5Ikqcf0\nwDIpI6vYV2t3IJIkSbNC1xd4mYsYG1vesLfGwMDijsQjSZLUaV1f4A0ODrrgoiRJUh3H4EmSJPWY\nri/whoeH+dWvfsHKlSs7HYokSdKs0PW3aCOKSRZDQ7BkydIORyNJktR5XV/gwaLyd+NEC0mSpLmp\nbbdoI2LriNh4DdvuEhH/2OqYJEmSelE7x+BdBDxhdY0i4p+ArwLzWh6RJElSD5qNkyzWAsY7HYQk\nSVK3assYvJiYCQHfjYjDgSXAFsDGmflARLwW+BzwTCApCrxfRMR/ZuZp7YhRkiSpV7SrB2+z8vdr\ngGOAdwHrAPtHxALgeOCQzPwF8K9l2+cBZ7YpPkmSpJ7RlgIvM28vX/45M/+amX+iKPLeR9Fz92vg\nuLLNaPn7T5l5XzvikyRJ6iUdG4OXmV8EfgTsAOyRmY67kyRJaoKOrYMXEY+hGIu3EtgauKHK+RYu\nnEetNr8Jkc0t5qwa81eN+Zs5c1eN+avG/M1+nVzo+AjgL8BewPER8fXM/D3FBIu+6Z5sbGw5o6N3\nNznE3larzTdnFZi/aszfzJm7asxfNeavmnYVxy29RRsRCyJinXJzOfC0iFgnIp4NvAN4W2Z+HrgO\n+HRdO4BNIuKxrYxPkiSpF7V6DN45wLLy9XHAR4HDgJOBMzLzh+WxtwEvi4hXZ+YY8Hngi8AeLY5P\nkiSp5/SNj3f33Ia+vuFxgKGh5SxZsrTT4XQVu9mrMX/VmL+ZM3fVmL9qzF81tdr8aQ9Dm4nZ+CQL\nSZIkVWCBJ0mS1GMs8CRJknpMJ5dJaZKJx9zWOhqFJEnSbNH1BV7mIsbGljMwsLjToUiSJM0KXV/g\nDQ4OOptHkiSpjmPwJEmSekzXF3jDw8OsXLmy02FIkiTNGl1f4EVczG9+8+tOhyFJkjRrdH2BB0/q\ndACSJEmzSg8UeJIkSapngSdJktRjLPAkSZJ6zIzXwYuIPmAU2D0zv1HuuxYYycydyu39gJdl5ksj\nYnPgKODZwDjwQ2B34A/Ab4HDMvOUuvP/H3BiZn5qpjFKkiTNRTPuwcvMceBCYGuAiHgc8DRgi7pm\nLwbOj4h5wHnAd4CNyv2LgYPL83wF2HniTRHxdGBpuV+SJEnTUPVJFhcA7yhfv4CiV26ziFgK/AZ4\nIfAu4LHAhzPz2LLtzRFxNrB5uf1l4JKImJ+ZdwOvBr6fmbdXjE+SJGnOqVrgfQc4uey92wq4COgD\nnk+xfsloZt4IEBGnlrdsNwE2Bp4JXAGQmT+OiN8BOwBfAl4DHFMxNkmSpDmpUoGXmbdExA0Uxd1W\nwAHA2hQF3j8D3waIiA2AK4GrKXr9Tga2B7asO92ZwC7lOL4lwDlrGsfChfOo1eZX+SpzlnmrxvxV\nY/5mztxVY/6qMX+zX9UePCh68f6NolfuRxQF3ieBu4AjyjY7AXdm5vYTb4qIfSl6+yacAQwB1wPf\nycw71zSAsbHljI7eXeU7zEm12nzzVoH5q8b8zZy5q8b8VWP+qmlXcTyjAi8iFgArM/Muih65c4Gr\nMvPeiLgMWASspJiEAfAn4EkRsR3wK2BXih686yfOmZnXR8RNwH7AW2b4fSRJkua8mc6iPQdYVr6+\nBFhR/iYz/wJcA1xevoZiNuwXKG7DXkkxg3ZXYMOIWLvuvGdQ9Op9fYZxSZIkzXl94+PjnY6hkr6+\n88aHhjZgyZKlnQ6l69jNXo35q8b8zZy5q8b8VWP+qqnV5vetvlV1PslCkiSpx1jgSZIk9ZgeKPBu\n6XQAkiRJs0ozlknpqMxtWGedf+x0GJIkSbNG1/fgDQ4O0t/f3+kwJEmSZo2uL/AkSZL09yzwJEmS\nekzXF3jDw8OsXLmy02FIkiTNGl1f4EVczG9+8+tOhyFJkjRrdH2BB0/qdACSJEmzyqwr8CLi4IgY\n6XQckiRJ3WpWFXgRsRFwCNDdD8iVJEnqoFlT4EVEH/A/wI87HYskSVI3m9GTLCJiK+B04P3AkcDa\nwBHAVcBJFAPjzsrM3cv2FwMXAy8GnlW22zszb6g77b7AX4AzgYNnEpckSZKq9eCtD+wCbEVR5B0F\nfAz49/LnTRHxyrr27wG+TFHg3QKcHxGPAoiIxcB7gb0rxCNJkiSqFXj9wAGZOQx8utw+ITOvzMyv\nAz8HNqxrf15mnpiZCewJLAReVh47GTgqM51cIUmSVFHVMXgTBdk95e+b647dQ3HrdsJlEy8yczkw\nDGwUEf8FrAscVx7uqxiTJEnSnDajMXh1VjRsPziNtv3ASuANwNOBuyJiIqZHRcRdwMaZ+bvVBbFw\n4TxqtflrHLQeYt6qMX/VmL+ZM3fVmL9qzN/sV7XAm45NJ15ExLrAPwPXAV8EHl3XblfgbRRj+36/\nJiceG1vO6OjdzYt0jqjV5pu3CsxfNeZv5sxdNeavGvNXTbuK42kVeBGxgKLXbSbeFBGXAFcCH6K4\nvfu9zPy7Xr+IGAVWOB5PkiRpZqY7Bu8cYNkqjjUuTjzesO9LwFuBn1D02L2isbiTJElSddPqwcvM\nbeo2++v2r6zfLvdt0fD2mzJzjzX4jFOAU6YTlyRJkh4ya55kIUmSpOZoV4Hns2UlSZLapC2zaDNz\n29ad/RZgg9adXpIkqcu0c5mUlsjchnXW+cdOhyFJkjRrdP0YvMHBQfr7+1ffUJIkaY7o+gJPkiRJ\nf88CT5IkqcdY4EmSJPUYCzxJkqQe0zc+7hJ1kiRJvcQePEmSpB5jgSdJktRjLPAkSZJ6jAWeJElS\nj7HAkyRJ6jEWeJIkST3mEZ0OYCYi4lHACcCrgXuB4zLzY52NqnMi4nXAl4BxoK/8/fXM3Dki/gn4\nH2AL4CZg/8z8dt17twGWAf8M/AjYMzN/VXf8HcCBwLrAWcA+mXlPW75Yi0XE2sCVwL6ZeVG5r2X5\n6rXrdhX5+29gT/7+WtwvMz9RHp/T+YuIxRTf//nAcuArwPsy836vvdVbTf689lYjIoLiezwPuB04\nMTOPKY95/a3GavI3666/bu3BOwZ4LrAtsDdwSETs2tmQOuppwDnAE8qfJwK7lce+AfwReA5wGnB2\nRDwVICKeDHy93P9s4LZym/L4zsAHgbcAWwObAR9v9Zdph7I4OQPYuOFQK/PVM9ftFPnbGNif4hqc\nuBZPLt8zp/MXEY8EzgPuofgL4o3AjsCHyyZee1NYg/x57U0hIh4BnA/8BngmsA/w/oh4fdnE628K\na5C/WXf9dd1CxxHxGIrK+ZWZeXG572DgpZn5wo4G1yERcTZwXWZ+sGH/tsC5QC0z/1ru+y4wlJmH\nRsQHga0n8hYRjwb+AOyUmRdFxA+A72fmYeXxLYELgfUmzteNImIjih5PgGcALy6/b8vyVX5WT1y3\nq8pfeex2YOfMvGSS9x0ObDNX81d+n+8BC+r+Zf56ij/I30hRvHjtrcJU+cvMDbz2plYWax8FdsvM\n+8p9ZwOjwJl4/U1pqvxl5ltm4/XXjT14zwQeBVxWt+9SYLOI6OtMSB23MZCT7H8ucE1DMXYpsHnd\n8b9djOUfmlcDm0fEWhT/ivhh3XuvoLitv2nzQu+IrSj+oticoit9Qivz1UvX7aT5i4j1gQVMfi1C\n0esyl/OXwCsahjiMA4+jyI3X3tQmyx/A48prbyFee6uUmTdl5uvripMtgRdQ/L/s9bcaq8jfC4EL\nZ+v1140F3hOBscy8v27fbRQJ+MfOhNQ55W2LJcAOEfGLiPhlRBxZ3rN/IvD7hrfcBjy5fD3V8ccB\n/1B/PDNXAn+qe39XysyTMvOAzLy34VAr89Uz1+0U+dsYWAkcERG/i4hrI+LNdcfndP4y8/aJnk6A\n8g/nt1P8S91rbzWmyN93Ka69FXjtrZGI+B1FwTEEnI3X37TU5e9yivw9jVl4/XVjgfcY4L6GfRPb\na7c5ltlgKdAP3AXsDBwAvAE4llXnaiJPUx1/TN32qt7fa1qZr7lw3W4EPAhcA7wMOAX474jYpTxu\n/v7eccAmwHvw2puJ4yh6Nw7Ca2+6/g14FfAsijx6/U3PRP6eTTFxYkNm4fXXjbNo7+XhX2piu2vH\nhc1UZv48IhZk5l3lrp+VXb5nUAzwXKfhLWvzUJ5Wlcvby2Os4niv5vleWpuvnr5uM/NTEfGFzLy7\n3HV9RCwF3krxr1zzV4qI4ykGVO+SmTdEhNfeNDTmD7jBa2/NZebVwNUR8VjgVIqCxOtvDdXlb3/g\n8xS5m3XXXzf24N0CLChntEx4AkVFO9aZkDqrrribcAPwSIou3yc0HHsCcGv5+pYpjv+J4sL72/GI\n6KcY9HkrvWmqfKzu+OryNSeu27o/4CbcADypfD3n8xcRfRHxOYqZcLtm5nnlIa+9NTBF/rz2ViMi\nNoiIHRp2/5ziVt+teP1NaTX5mz8br79uLPCuBe6nWKtnwguAqzLzwc6E1DkRsVNE/LHhP/6zgDso\nBmpuWs7YmfD8cj/l7+fXnesxFIM6hzJzHPhJ/XGKnD9A0Q3di64ANmlRvnr+uo2Ij0fEeQ27nwXc\nWL42f8XQiddRzJ77et1+r701M2n+vPbWyEbAORHx+Lp9z6FYGuVSWvd3Ra/nbxQ4eDZef113izYz\n74mI04BPRcR/UgxA3B/Yo7ORdcz3KQZ3nhwRRwKDwNHlzw8oFqw8NSI+AOxAMRtq9/K9nwUOiIj3\nAv8LHArcVDeQ+VPleX8G/BY4ETilm5dIWY2W5msOXLdfAy6KYsHObwKvAN5EsXYTzPH8RcTzgH0p\nxoxdXc68m+C1txqryZ/X3ur9APg/4PMRcQDFgrtHAkdQTBjw+pvaVPm7lll4/XVjDx7Auygq3u9R\nJOYDmXl2Z0PqjMy8A3gp8FTgKuAk4FOZeVRZ3f8bUKN44sCbgB0z8+byvTdRTMz4D4p8Pp5i4OjE\nuc+kuHg/DXwH+DHFJI5e8reFIMt8vYrW5asXr9v6/F1K0buyJ/AzijFSr8vMofL4XM/fLhT5OpJi\n+MTvKW7BTMye2xGvvalMlb/L8dqbUmauAF5J0SFwBcV3PTYzT2jD3xW9nr9Z+Wdf1y10LEmSpKl1\naw+eJEmSVsECT5IkqcdY4EmSJPUYCzxJkqQeY4EnSZLUYyzwJEmSeowFniRJUo+xwJMkSeoxFniS\nJEk95v8DZACCjXCyq/4AAAAASUVORK5CYII=\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x115c6feb8>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ax = non_letters[\"file_ext\"].value_counts().head(15)\\\n",
    "    .plot(kind=\"barh\", width=0.8, figsize=(10, 6), fontsize=14)\n",
    "seaborn.mpl.pyplot.gca().invert_yaxis()\n",
    "ax.set_title(\"Most Popular Filetypes\", fontsize=16)\n",
    "pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How many pages do PDF responses contains?\n",
    "\n",
    "When MuckRock gets a PDF, it counts the number of pages."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "image/png": "iVBORw0KGgoAAAANSUhEUgAAAlcAAAF+CAYAAACvcD/nAAAABHNCSVQICAgIfAhkiAAAAAlwSFlz\nAAALEgAACxIB0t1+/AAAIABJREFUeJzt3XuYXVWZ5/FvLsgQLBKiFW8gIiNv2/rANGorNBBAvOCN\n1nEEG6IgjsowIzQNakBocZSoCBoVZAJCULRJg9oidsRLIAR7xrtOM+CLIqJGWwpjYqBClFTNH2uX\nnC6SqiK1wtl18v08Tz2cs/c6Z7/7LFLnV2uvvfe04eFhJEmSVMf0bhcgSZLUSwxXkiRJFRmuJEmS\nKjJcSZIkVWS4kiRJqshwJUmSVNHMbhcgaetExI3AwaMWbwB+DFySmRc+4kVthYi4HHj9qMUbgTuB\nq4D3ZOZQ5W3u0bz/aPcDq4FrgLMy84Ga25W0fTBcSVPXMHAz8HfAtGbZo4HjgI9GxHBmXtSl2h6u\nO4C/4cH92Al4OfAuyj69bRtt9x3AjR3PdwVeBbydMrL/9m20XUk9zHAlTW1rM/PbnQsi4gbgOcB/\nB6ZKuNowej+AlRGxD/DmiFiYmZu2wXZ/kpnfGrXs+oj4c8pomuFK0sNmuJJ6TGYOR8QPKSM/AETE\n3sB7gfnAbOBXwCcy8z0dbfYAPgIcAqwHPgS8GPhFZr6haTMLeD/wX4BdgG8Cf5uZP+h4n9OBNwG7\nUQ6xLQXem5lbczuI7wHPB+YCAxExA/h7SvCZB9wCvD0zVzTbng/cALwFOIfyO+7ZmXnXw9zu+tEL\nIuK1wEJgb+CXwIcz82Md658LfAD4C+CPwNeB0zLz5836oabuY4GDKH1wXmb+r4732JkyWvefgccD\n/wq8MzO/Omr/DgbeBzyreZ9zM/MTHe8zZh9ERD9wPvBS4FHACuDkzPzZw/ycJG2GE9ql3vQ0mjlF\nzRf2jZRDXguAl1C++M+JiJc2bf4D5Qv2aZQAcDpwMvBXo973i8BrgDOAV1PmKN0YEXs273Ms8G7g\ng8ALgUsoYeGNW7kfewP3ZeZA8/xS4G8pwe9I4DZgeUQ8b9Tr3tZs85RxgtX0iJjR/MyMiHkRcUpT\n+6dGGkXE64FPU4LNyyhh5UMR8XfN+l2ALwG/oITaNwL7Af8wanuLKcHsr4HrgI9HxPHNe0wDrqd8\n/ucCrwTuAv45Il4w6n3+AbgaOAL4PrAkIv6seZ8x+6Dp6xuBA4CTKGHv8cBNETF7jM9K0gQ5ciVN\nbdOa0Rwo85WeAPw34D8BpzTLgzLJ/TWZuQYgIlZQ5hbNp4SCBcDuQGTmSChL4DsjG4qIFwGHAs/P\nzBuaZV8GbgXOpHx5/xVwZ8dozKqI+CNldGVMHfsBZVTqNZQA9eFm/Z9RgscJmXl50+4rEfFE4D3A\n4R2v/0hmfmm8bQLLeHCe14jVlHBybrPdaZRRv09l5slNm69FBMBZEXER8HTK6NpHM/ObzevuAQ4b\n9d7fyswTOmrfjfLZXU4JbQcAL8zMrzVtro+If2lq+WrH+3w4Mxc32/k+JYgdAfyI8fvg9ZQQ/YzM\n/HHzHl8Hfg78D8pnKWkSDFfS1PZSyiGoToPABcCFAJn5PWB+MzLzdMpo0H7ADsCOzWvmA7eMBKuR\n10VE5xl1hwL3Ub6sOwPdV3jwEOQqyhypb1POuLsuMy+YwH48czP78Qfgk8A7O2ocpoxUdW5/OfDe\niOj8fXb7BLYJZYTrBsrvwgXAG4DTM/OqjjZ7A0+kjCB1BsAvU0LYXwLfBdYA10XEVZTAuiIzV43a\n3mdGPf888MomIB4E/L4jWI24CrigGYGE8hl8c2RlZq6LiHuBkfXj9cEhlLD90479ub953fMxXEmT\nZriSprZVlBGqaZQv3XuBn46e/B0RZwKnUeZJ3QX8CyXMjIzaPBYY4KF+0/F4LuUL/A+j2gyPLMvM\nzzRf2CdRRnsWRcT/pYw2fXeM/fgJcFTHfgxSRl86t/WYZv3oUbDh5uexHc/vHmNbne5swifAtyLi\n0cCnImIgM7/esV0owWj0Yb5h4AmZeW9EHAScDbyOMnq4LiIWZeZ5He1/Per1I5/5rs3Pb3io31D2\nu69j2eCoNkM00zzG6IM3NPv6GMpI2+gwO8zEQ6mkMRiupKltXWZ+f6wGEfE6ygjLW4CrMnN9s7zz\ni3w1ZSL2aP2UQ00A6yhf9C/hoYfS/iQzP0UJKI+ljGj9PWUE6hljlHn/ePvRbH8I2B/Y3JmD91AO\ngU7GKZT9+0REPD0zNzTbhRKYRp/RCM3ctsy8DXhtM4J2EGXO2vsi4saOMyEfM+q1j2v+O0AZ+Xoc\nD/WE5r9rJroTm+mDd1HmkD2j2Z8fACfw0H7cONFtSNoyJ7RLve95lDP+LukIVvtRgtPIl+sq4JnN\nGYM0bZ4JPLXjfW5uXnNfZn5v5Ad4LXBM85qlEXE1QGbe08yNugx4coX9uLmpd/ao7R9Gmbg+6Qt+\nZuY6yhyoJ/PgZRh+BPwW2H3UdudSQuvsiHhpRAxExGMy84FmTtpbm3p379jEy0Zt8lXArZl5d7N/\nfZuZvH4U8J1Ro3hbtIU++AQP9sHNwJ7AXaP252TKYWZJk+TIldT7vk2Zg3MWsBL4c8o8pjXArKbN\nlZRQ8aWIOJvyu+E9lJGikaujf5EywX15RJxDmQD9auBE4M1Nmxspoz7vpUzAfnKz/rOT3YnM/GFE\nfA74dES8i3Km4KGUMxff39F0i6NqE3QZJRidFhGXZObqZnvnN5Pbv04JneeWsvJnEbGecljt8xHx\nfsoht1OA3/HvL1J6VET8mjJP7UjKqNKrmnVfAr4FXBkR76R8vm+gXLPs5R3vMd7+3cjYfTCyf1+L\niEWU/w/e1NTx8oe8m6SHzZEraWqbyLWjllLCx5spk7CPoUze/jRlVItm1OeFlC/0T1ImxH+EctmA\ne5s2Q02brzTv9yXgQOC4zLy0abOUEipeCfwzsIgyIfvECvsB5Srul1GurL6cMqrz9sx8Z0ebib7X\nZts1+3ka5Srx72+WXUg5rPpyyn6/i3Km4cua9b+lnK23kfL5fZZywsDzR87QbJxDOfz6Bcpn9+rM\nvLZjuy8CPkcJtp8FngQckZnLx6l7ZN7ZuH3QjF4eRAmnHwf+iRLAXpGZXx7j85I0QdOGh7fmun6S\neklzCHDPzPxix7I+ysTw06bKfQrbrLmI6Bsz87Ju1yJp2xr3sGBz8bzjKH8V7QTsS/mr58OUwwW3\nZOZJTduLgX2AizLzyubCehdm5oJtU76kSuYAX4iIkespzaZcrPP3lBEaSdIEjXtYMDOvyMxDM/Mw\nyrVc3ko53fiMzJxPucLxkRExF5iXmQdQzkKBcquIRduodkmVZObNlMOFR1CuHH45Zb7QQZl5Tzdr\n6yEeJpC2ExM+LBgRzwY+kJmHRcQvM3O3ZvkrgBdQzqy5inLPsc9TrrFySscVjSVJknrew5nQvpAy\niXO09ZRTowcpf/FeQZm0eSawOCIWR8T5EbHTZIuVJElquwldiqG5mefemXlTs2ioY3UfsBYgM5dQ\nbiC6P3AH5V5fK5t2x1BuurpZw8PDw9OmTfYMakmSpEfEFkPLRK9zdTDl2i4jvh8RBzdh6whgxaj2\np1LutH4iD96WYWfGMG3aNAYG1k+wHD1S+vv77JeWsU/ayX5pH/uknXqlX/r7+7a4bqLhKoCfdjw/\nDbgkInagXCvlmj81jDgKuDYzNzZXCV5GuVXF0Q+zbkmSpCmnTde5Gu6FJNtreuUvjF5in7ST/dI+\n9kk79Uq/9Pf3bfGwoFdolyRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioy\nXEmSJFVkuJIkSarIcCVJklSR4UqSJKmiid64eZtbvXo1q1ff0+0yqthtt93ZYYcdul2GJEnqgtaE\nq7ee/XHWDD2+22VM2h82/J6Fr3sez33Oc7pdiiRJ6oLWhKtZfbvyhxm7d7uMSbv/3jUw3O0qJElS\ntzjnSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklSR4UqSJKki\nw5UkSVJFhitJkqSKDFeSJEkVGa4kSZIqMlxJkiRVZLiSJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYr\nSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkUzJ9IoIt4BvKJp\n/zHgG8BSYAi4JTNPatpdDOwDXJSZV0bELsCFmblgG9QuSZLUOuOOXEXEfGD/zDwAOBTYC7gAOCMz\n5wPTI+LIiJgLzGvandC8fCGwaNuULkmS1D4TOSz4IuCWiPgn4NrmZ7/MXNWsXw4cDtwPzIyIHYEN\nEbEnMCszb90GdUuSJLXSRA4LPhZ4MvAy4KmUcNUZytYDszNzMCKuA64AzgHOBM6NiMXAA8A7M3ND\nzeIlSZLaZiLh6rfAbZn5AHB7RNwP7Naxvg9YC5CZS4AlEbE/cAdlRGtl0+4Y4NJahbfZnDmz6O/v\n63YZ1fTSvvQK+6Sd7Jf2sU/aqdf7ZSLh6mbgrcCHIuKJwM7A1yNifmauBI4AVox6zanAscCJlEnv\n05vXbRfWrh1kYGB9t8uoor+/r2f2pVfYJ+1kv7SPfdJOvdIvYwXEccNVZn4pIg6KiG8B0yiB6WfA\npRGxA3AbcM1I+4g4Crg2MzdGxNXAMmATcPRkdkKSJGkqmNClGDLzHZtZfMgW2i7reLwaOHCrKpMk\nSZqCvIioJElSRYYrSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJ\nUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSK\nDFeSJEkVGa4kSZIqMlxJkiRVZLiSJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJFRmu\nJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmS\nJFVkuJIkSarIcCVJklTRzIk0iojvAuuap3cC5wJLgSHglsw8qWl3MbAPcFFmXhkRuwAXZuaC2oVL\nkiS10bgjVxGxI0BmHtb8nABcAJyRmfOB6RFxZETMBeZl5gHACc3LFwKLtlHtkiRJrTORkat9gZ0j\n4npgBnAmsF9mrmrWLwdeAHwVmNmEsQ0RsScwKzNv3QZ1S5IktdJE5lwNAudl5ouAE4FPA9M61q8H\nZmfmIHAdcAVwDiWELY6IxRFxfkTsVLd0SZKk9plIuLqdEqjIzB8DvwUe17G+D1jbrF+SmUc373sH\ncDiwEvgGcEy9siVJktppIocFj6dMUj8pIp4I7AJ8JSLmZ+ZK4AhgxajXnAocSxnpGqKErZ2rVd1y\nc+bMor+/r9tlVNNL+9Ir7JN2sl/axz5pp17vl4mEq08Al0XETcAwcBxl9OrSiNgBuA24ZqRxRBwF\nXJuZGyPiamAZsAk4unLtrbV27SADA+u7XUYV/f19PbMvvcI+aSf7pX3sk3bqlX4ZKyCOG64y8wHg\ndZtZdcgW2i/reLwaOHDcCiVJknqEFxGVJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJ\nFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioy\nXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSKDFeSJEkVGa4kSZIqMlxJkiRVZLiS\nJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmS\nVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFU0cyKNImIe8B3gcGATsBQYAm7J\nzJOaNhcD+wAXZeaVEbELcGFmLtgWhUuSJLXRuCNXETETuBgYbBZdAJyRmfOB6RFxZETMBeZl5gHA\nCU27hcCibVCzJElSa03ksOAHgY8DvwKmAftl5qpm3XLKaNb9wMyI2BHYEBF7ArMy89ZtULMkSVJr\njRmuIuI44O7M/ColWI1+zXpgdmYOAtcBVwDnAGcCiyNicUScHxE7Va9ckiSphcabc3U8MBQRLwD2\nBT4J9Hes7wPWAmTmEmBJROwP3EEZ0VrZtDsGuLRi3a02Z84s+vv7ul1GNb20L73CPmkn+6V97JN2\n6vV+GTNcNfOqAIiIFcBbgPMi4uDMvAk4Algx6mWnAscCJ1ImvU8Hdq5ZdNutXTvIwMD6bpdRRX9/\nX8/sS6+wT9rJfmkf+6SdeqVfxgqIEzpbcJTTgEsiYgfgNuCakRURcRRwbWZujIirgWWUswuP3ort\nSJIkTTkTDleZeVjH00O20GZZx+PVwIFbXZkkSdIU5EVEJUmSKjJcSZIkVWS4kiRJqshwJUmSVJHh\nSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5Uk\nSVJFhitJkqSKDFeSJEkVGa4kSZIqMlxJkiRVZLiSJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKk\nigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZ\nriRJkioyXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSKDFeSJEkVzRyvQURMBy4B\nAhgC3gJsBJY2z2/JzJOathcD+wAXZeaVEbELcGFmLtg25UuSJLXLREauXg4MZ+aBwFnAucAFwBmZ\nOR+YHhFHRsRcYF5mHgCc0Lx2IbBoG9QtSZLUSuOGq8z8AvCm5ukewO+A/TJzVbNsOXA4cD8wMyJ2\nBDZExJ7ArMy8tX7ZkiRJ7TShOVeZORQRlwMfAT4DTOtYvR6YnZmDwHXAFcA5wJnA4ohYHBHnR8RO\ndUuXJElqn3HnXI3IzOMj4u3At4HOoNQHrG3aLAGWRMT+wB2UEa2VTbtjgEtrFN12c+bMor+/r9tl\nVNNL+9Ir7JN2sl/axz5pp17vl4lMaF8A7JaZiyiH/jYB34mI+Zm5EjgCWDHqZacCxwInUia9Twd2\nrll4m61dO8jAwPpul1FFf39fz+xLr7BP2sl+aR/7pJ16pV/GCogTGbm6BlgaESub9m8FfgRcGhE7\nALc1bQCIiKOAazNzY0RcDSyjBLKjt3oPJEmSpohxw1VmbgCO2syqQ7bQflnH49XAgVtbnCRJ0lTj\nRUQlSZIqMlxJkiRVZLiSJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJFRmuJEmSKjJc\nSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIk\nSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSKDFeSJEkVGa4kSZIqMlxJkiRVZLiSJEmqyHAlSZJU\nkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLD\nlSRJUkWGK0mSpIoMV5IkSRXNHGtlRMwELgOeAjwKeC9wK7AUGAJuycyTmrYXA/sAF2XmlRGxC3Bh\nZi7YZtVLkiS1zHgjV8cC92TmwcCLgY8BFwBnZOZ8YHpEHBkRc4F5mXkAcELz2oXAom1UtyRJUiuN\nF67+ETireTwDeADYLzNXNcuWA4cD9wMzI2JHYENE7AnMysxbt0HNkiRJrTVmuMrMwcy8LyL6gKuB\nM4FpHU3WA7MzcxC4DrgCOKdptzgiFkfE+RGx07YpX5IkqV3GnHMFEBG7A58DPpaZV0XEBzpW9wFr\nATJzCbAkIvYH7qCMaK1s2h0DXFqz8DabM2cW/f193S6jml7al15hn7ST/dI+9kk79Xq/jDeh/XHA\n9cBJmXlDs/j7EXFwZt4EHAGsGPWyUylztU6kTHqfDuxcteqWW7t2kIGB9d0uo4r+/r6e2ZdeYZ+0\nk/3SPvZJO/VKv4wVEMcbuVoIzAHOioizgWHgZOCjEbEDcBtwzUjjiDgKuDYzN0bE1cAyYBNw9KT2\nQJIkaYoYM1xl5inAKZtZdcgW2i/reLwaOHAyxUmSJE01XkRUkiSpIsOVJElSRYYrSZKkigxXkiRJ\nFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioy\nXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSKDFeSJEkVGa4kSZIqMlxJkiRVZLiS\nJEmqyHAlSZJUkeFKkiSpIsOVJElSRYYrSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJqshwJUmS\nVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklTRzIk0iojn\nAu/LzEMjYi9gKTAE3JKZJzVtLgb2AS7KzCsjYhfgwsxcsG1KlyRJap9xR64i4nTgEmDHZtEFwBmZ\nOR+YHhFHRsRcYF5mHgCc0LRbCCzaBjVLkiS11kQOC/4EeGXH82dl5qrm8XLgcOB+YGZE7AhsiIg9\ngVmZeWvVaiVJklpu3MOCmfn5iNijY9G0jsfrgdmZORgR1wFXAOcAZwLnRsRi4AHgnZm5oWLdrTU8\nNMTq1b/gjjvmdLuUKubO3bfbJUiSNKVMaM7VKEMdj/uAtQCZuQRYEhH7A3dQRrRWNu2OAS6dRJ1T\nxuDv7+ay6zcy6//c1+1SJm1w3d18atGj2Xvvvbtdikbp7+/rdgnaDPulfeyTdur1ftmacPW9iDg4\nM28CjgBWjFp/KnAscCIliE0Hdp5UlVPMrNnzePSuT+p2GdUMDKzvdgnq0N/fZ5+0kP3SPvZJO/VK\nv4wVELcmXJ0GXBIROwC3AdeMrIiIo4BrM3NjRFwNLAM2AUdvxXYkSZKmnAmFq8y8Czigefxj4JAt\ntFvW8Xg1cODkS5QkSZo6tmbkSpIk6WHbtGkTt99+O2vW3NvtUiatv3+/La4zXEmSpEfEz372U04+\n71pmzZ7X7VImZXDd3Xzzs4YrSZLUAr120tfmeG9BSZKkigxXkiRJFRmuJEmSKjJcSZIkVWS4kiRJ\nqshwJUmSVJHhSpIkqSLDlSRJUkWGK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklSR\n4UqSJKkiw5UkSVJFhitJkqSKZna7ALXX8NAQd955J2vW3NvtUqp4ylOeyowZM7pdhiSpxxmutEUb\n1g9w9pJ7mDV7XrdLmbTBdXez+PRXsNdeT+t2KZKkHme40phmzZ7Ho3d9UrfLkCRpynDOlSRJUkWG\nK0mSpIoMV5IkSRUZriRJkioyXEmSJFVkuJIkSarIcCVJklSR17nSdmF4aIif//yubpdRxdy5+3a7\nBEnSGAxX2i5sWD/A+cvuYdbsX3e7lEkZXHc3n1r0aHbd9QndLkWStAWGK203vNq8JOmR4JwrSZKk\nihy5kqaQ4aEh7rzzTtasubfbpVTxlKc8lRkzZnS7DEmqynAlTSEb1g9w9pJ7mDV7XrdLmbTBdXez\n+PRXsNdeT+t2KZJUleFKmmJ6Ze5YL53BCZ7FKelBhitJXdErZ3AC3Lf23/ifbx5g9uz+bpcyaR6q\nlSbPcCWpa3plFG5w3W84e8n/nvKHa+9b+2+cdvRf8OQn79HtUqpwNFHdslXhKiKmARcB+wL3A/8V\n+I/Au4G7MvM1TbuPAudl5s/rlCtJ7dQLQXFw3W84f9kPHU1smU2bNgHTmDFj6p/g30tTAcaytSNX\nfw3smJkHRMRfAuc3y18AvDsi9gWGgHUGK0maOnohJELvjCYC/PaXt7FT32N6Zl8es9vTu13GNre1\n4epA4MsAmfmtiHg2cAMwC9gJGATeBbylQo2SJD1svRQUe2lftgdbO8a4C7Cu4/kDwPuADwF3Ug4R\n3gz8TURcFBHPm1SVkiRJU8TWjlz9HujreD49M/8f8NqImA4sA94IXA68Gvgi8NKx3nDTfQMM/XHD\nVpbTIut/zuADT+x2FVVsWL8GmNbtMqrolX3plf0A96WNemU/wH1pq17Zl8F1d4+5fmvD1TeAlwHX\nNKNS/9qx7k3AUsqnN/IJzhrvDT9zyfum/qctSZK2e1sbrj4PvCAivtE8Px4gIvqA+Zn52ub5byiH\nBy+abKGSJElTwbTh4eFu1yBJktQzpv5FMyRJklrEcCVJklSR4UqSJKkiw5UkSVJFXb9x82buU/jG\nzPxpd6vafkXEc4H3ZeahEbEX5bIaQ8AtmXlSV4vbDkXETOAy4CnAo4D3Ardiv3RVcz2/S4Cg9MNb\ngI3YL10XEfOA7wCHA5uwT7oqIr7LgxcdvxM4l+2gT9owcvWn+xQCC4ELulzPdisiTqd8YezYLLoA\nOCMz5wPTI+LIrhW3/ToWuCczDwZeDHwM+6UNXg4MZ+aBwFmULwz7pcuaP0YuptyCDeyTroqIHQEy\n87Dm5wS2kz5pQ7jqvE/hN4Fnd7ec7dpPgFd2PH9WZq5qHi+n/CWoR9Y/Ur68AWZQbjW1n/3SXZn5\nBcoFkwH2AH6H/dIGHwQ+DvyKchFr+6S79gV2jojrI+JrzZGR7aJP2hCuHnKfwmbIXY+wzPw85ct7\nROdV89cDsx/ZipSZg5l5X3OB3quBM7FfWiEzhyLicuAjwGewX7oqIo4D7s7Mr/JgX3R+l9gnj7xB\n4LzMfBFwIvBptpN/J20IMZu7T+FQt4rRv9PZD33A2m4Vsj2LiN2BFcAVmXkV9ktrZObxwN7ApcBO\nHavsl0fe8ZQ7h9xAGTH5JNDfsd4+eeTdTglUZOaPgd8Cj+tY37N90oZw9Q3gJQCbuU+huut7EXFw\n8/gIYNVYjVVfRDwOuB54W2Ze0Sz+vv3SXRGxICIWNk/vp0yc/k5EzG+W2S+PsMycn5mHZuahwA+A\nBcBy/63BcL3hAAAAoElEQVR01fHA+QAR8UTKkaqvbA//Trp+tiBbuE+hWuE04JKI2AG4Dbimy/Vs\njxYCc4CzIuJsYBg4Gfio/dJV1wBLI2Il5ffoW4EfAZfaL63i77Du+gRwWUTcRPnddRxl9Krn/514\nb0FJkqSK2nBYUJIkqWcYriRJkioyXEmSJFVkuJIkSarIcCVJklSR4UqSJKkiw5UkSVJFhitJkqSK\n/j9FQQjdLJ5yiwAAAABJRU5ErkJggg==\n",
      "text/plain": [
       "<matplotlib.figure.Figure at 0x114e35b70>"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "ax = non_letters[\n",
    "    (non_letters[\"pages\"] > 0) &\n",
    "    (non_letters[\"is_likely_letter\"] == False)\n",
    "][\"pages\"]\\\n",
    "    .apply(lambda x: min(x, 55))\\\n",
    "    .hist(bins=range(0, 60, 5), normed=True, figsize=(10, 6))\n",
    "ax.set_xlim((0, 55))\n",
    "ax.set_title(\"Pages Per Response\", fontsize=16)\n",
    "ax.set_yticklabels([ \"{0:.0f}%\".format(y * 500) for y in ax.get_yticks() ])\n",
    "pass"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## What agencies have sent data in tabular data formats?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "def count_responses_by_ext(exts):\n",
    "    return pd.DataFrame({\n",
    "        \"n_responses\": files[\n",
    "            files[\"file_ext\"].isin(exts)\n",
    "        ].groupby([ \"jurisdiction_name\", \"agency_name\" ])[\"request_id\"].nunique()\\\n",
    "            .sort_values(ascending=False)\n",
    "    }).reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>n_responses</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Federal Trade Commission</td>\n",
       "      <td>37</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>Boston Police Department</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Seattle, WA</td>\n",
       "      <td>Seattle Police Department</td>\n",
       "      <td>9</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>George W Bush (43) Presidential Library</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Washington</td>\n",
       "      <td>Central Puget Sound Regional Transit Authority</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>U.S. Citizenship and Immigration Services</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>New York City, NY</td>\n",
       "      <td>Department of Health and Mental Hygiene</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Seattle, WA</td>\n",
       "      <td>Office of the Mayor</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>Department of Education</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Somerville, MA</td>\n",
       "      <td>Somerville City Clerk</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>Boston Housing Authority</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>Elections Division (Secretary of State)</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Florida</td>\n",
       "      <td>Department of Corrections</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Virginia</td>\n",
       "      <td>Department of Corrections</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>California</td>\n",
       "      <td>Department of Justice</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           jurisdiction_name                                     agency_name  \\\n",
       "0   United States of America                        Federal Trade Commission   \n",
       "1                 Boston, MA                        Boston Police Department   \n",
       "2                Seattle, WA                       Seattle Police Department   \n",
       "3   United States of America         George W Bush (43) Presidential Library   \n",
       "4                 Washington  Central Puget Sound Regional Transit Authority   \n",
       "5   United States of America       U.S. Citizenship and Immigration Services   \n",
       "6          New York City, NY         Department of Health and Mental Hygiene   \n",
       "7                Seattle, WA                             Office of the Mayor   \n",
       "8              Massachusetts                         Department of Education   \n",
       "9             Somerville, MA                           Somerville City Clerk   \n",
       "10                Boston, MA                        Boston Housing Authority   \n",
       "11             Massachusetts         Elections Division (Secretary of State)   \n",
       "12                   Florida                       Department of Corrections   \n",
       "13                  Virginia                       Department of Corrections   \n",
       "14                California                           Department of Justice   \n",
       "\n",
       "    n_responses  \n",
       "0            37  \n",
       "1            14  \n",
       "2             9  \n",
       "3             7  \n",
       "4             6  \n",
       "5             4  \n",
       "6             4  \n",
       "7             4  \n",
       "8             4  \n",
       "9             4  \n",
       "10            4  \n",
       "11            4  \n",
       "12            3  \n",
       "13            3  \n",
       "14            3  "
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_responses_by_ext([ \".csv\", \".tsv\", \".xls\", \".xlsx\" ]).head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>request_slug</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>9515</th>\n",
       "      <td>ftc-complaints-for-any-complaints-that-may-hav...</td>\n",
       "      <td>Campbell Media Group.xls</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9516</th>\n",
       "      <td>ftc-complaints-for-any-complaints-that-may-hav...</td>\n",
       "      <td>John Campbell.xls</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10325</th>\n",
       "      <td>ftc-fedex-complaints</td>\n",
       "      <td>Responsive Documents</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12012</th>\n",
       "      <td>fy2013-foia-log-federal-trade-commission</td>\n",
       "      <td>Request log 10.1.2012-12.31.2012 Redacted.xls</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12013</th>\n",
       "      <td>fy2013-foia-log-federal-trade-commission</td>\n",
       "      <td>Request log 9.26.2013-9.30.2013 Redacted.xls</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12955</th>\n",
       "      <td>ftc-complaints-nfl</td>\n",
       "      <td>Responsive Documents</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18355</th>\n",
       "      <td>ftc-complaints-for-uber</td>\n",
       "      <td>Uber_complaints.xls</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28075</th>\n",
       "      <td>ftc-complaints-for-western-union-et-al</td>\n",
       "      <td>Western Union MA redacted.xml</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            request_slug  \\\n",
       "id                                                         \n",
       "9515   ftc-complaints-for-any-complaints-that-may-hav...   \n",
       "9516   ftc-complaints-for-any-complaints-that-may-hav...   \n",
       "10325                               ftc-fedex-complaints   \n",
       "12012           fy2013-foia-log-federal-trade-commission   \n",
       "12013           fy2013-foia-log-federal-trade-commission   \n",
       "12955                                 ftc-complaints-nfl   \n",
       "18355                            ftc-complaints-for-uber   \n",
       "28075             ftc-complaints-for-western-union-et-al   \n",
       "\n",
       "                                               title description  \n",
       "id                                                                \n",
       "9515                        Campbell Media Group.xls              \n",
       "9516                               John Campbell.xls              \n",
       "10325                           Responsive Documents              \n",
       "12012  Request log 10.1.2012-12.31.2012 Redacted.xls              \n",
       "12013   Request log 9.26.2013-9.30.2013 Redacted.xls              \n",
       "12955                           Responsive Documents              \n",
       "18355                            Uber_complaints.xls              \n",
       "28075                  Western Union MA redacted.xml              "
      ]
     },
     "execution_count": 18,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files[\n",
    "    (files[\"file_ext\"] == \".xls\") &\n",
    "    (files[\"agency_name\"] == \"Federal Trade Commission\")\n",
    "][[ \"request_slug\", \"title\", \"description\"]].fillna(\"\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Generalize that code, so that we can look at any subset of extensions..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## How about .zip files?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>jurisdiction_name</th>\n",
       "      <th>agency_name</th>\n",
       "      <th>n_responses</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Immigration and Customs Enforcement</td>\n",
       "      <td>57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Department of Homeland Security</td>\n",
       "      <td>12</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>Mayor's Office</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Federal Bureau of Investigation</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Food and Drug Administration</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>National Park Service</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Boston, MA</td>\n",
       "      <td>Boston Redevelopment Authority</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Consumer Financial Protection Bureau</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>United States of America</td>\n",
       "      <td>Department of Transportation</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>New York City, NY</td>\n",
       "      <td>New York City Police Department</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "          jurisdiction_name                           agency_name  n_responses\n",
       "0  United States of America   Immigration and Customs Enforcement           57\n",
       "1  United States of America       Department of Homeland Security           12\n",
       "2                Boston, MA                        Mayor's Office            5\n",
       "3  United States of America       Federal Bureau of Investigation            4\n",
       "4  United States of America          Food and Drug Administration            4\n",
       "5  United States of America                 National Park Service            4\n",
       "6                Boston, MA        Boston Redevelopment Authority            3\n",
       "7  United States of America  Consumer Financial Protection Bureau            3\n",
       "8  United States of America          Department of Transportation            3\n",
       "9         New York City, NY       New York City Police Department            3"
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "count_responses_by_ext([ \".zip\" ]).head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Oooh what are in those .zip files... ?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>request_slug</th>\n",
       "      <th>title</th>\n",
       "      <th>description</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>74559</th>\n",
       "      <td>guns-and-ammunition-purchased-in-fy2013</td>\n",
       "      <td>2015-ICFO-04708_141795</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41462</th>\n",
       "      <td>muckrock-request-processing-instructions-immig...</td>\n",
       "      <td>2015-ICFO-00843_61647</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>36305</th>\n",
       "      <td>first-record-mentioning-the-marco-polo-task-force</td>\n",
       "      <td>2015-ICFO-00925_39854</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41463</th>\n",
       "      <td>fee-category-records-for-2014foia3508</td>\n",
       "      <td>2015-ICFO-02197_61654</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73557</th>\n",
       "      <td>ice-tpm-on-easton-amc-incident</td>\n",
       "      <td>2015-ICFO-00813_140344</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>42232</th>\n",
       "      <td>proposed-records-schedules-ice</td>\n",
       "      <td>2015-ICFO-01103_63226</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>28098</th>\n",
       "      <td>ices-statistics-on-deportation-through-the-287...</td>\n",
       "      <td>2014-ICFO-02026_11003</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39321</th>\n",
       "      <td>contracts-and-amendments-for-the-njs-elizabeth...</td>\n",
       "      <td>2014-ICFO-04417_53437</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37455</th>\n",
       "      <td>all-contracts-between-ice-and-geo-group-for-nj...</td>\n",
       "      <td>2014-ICFO-05862_45563</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>31355</th>\n",
       "      <td>documents-on-onion-routing</td>\n",
       "      <td>2015-ICFO-06250_21317</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41059</th>\n",
       "      <td>ice-contracts-for-eloy-detention-center</td>\n",
       "      <td>2015-ICFO-14496_59419</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60880</th>\n",
       "      <td>ice-contracts-for-florence-correctional-center</td>\n",
       "      <td>2015-ICFO-14486_125847</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>64558</th>\n",
       "      <td>ice-contractsmous-re-san-diego-correctional-fa...</td>\n",
       "      <td>2015-ICFO-14456_132116</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73009</th>\n",
       "      <td>ice-contractsmous-re-north-georgia-detention-c...</td>\n",
       "      <td>2015-ICFO-13508_139232</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73243</th>\n",
       "      <td>ice-contractsmous-re-stewart-detention-center</td>\n",
       "      <td>2015-ICFO-95472_139648</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53431</th>\n",
       "      <td>ice-contractsmous-re-torrance-county-detention...</td>\n",
       "      <td>2015-ICFO-16763_101419</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41847</th>\n",
       "      <td>ice-contractsmous-re-houston-processing-center</td>\n",
       "      <td>2015-ICFO-16784_62158</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41078</th>\n",
       "      <td>ice-contractsmous-re-t-don-hutto-residential-c...</td>\n",
       "      <td>2015-ICFO-16796_59544</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51563</th>\n",
       "      <td>operation-safeguard-ice</td>\n",
       "      <td>2015-ICFO-66842_94404</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57458</th>\n",
       "      <td>ice-contractsmous-re-adelanto-detention-facility</td>\n",
       "      <td>2015-ICFO-69562_122383</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59122</th>\n",
       "      <td>ice-contractsmous-re-adelanto-detention-facility</td>\n",
       "      <td>2015-ICFO-69562_123610</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57456</th>\n",
       "      <td>ice-contractsmous-re-denver-contract-detention...</td>\n",
       "      <td>2015-ICFO-68826_122378</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60095</th>\n",
       "      <td>ice-contractsmous-re-broward-transitional-center</td>\n",
       "      <td>2015-ICFO-69576_124580</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>49955</th>\n",
       "      <td>ice-contractsmous-re-central-texas-detention-f...</td>\n",
       "      <td>2015-ICFO-68919_88958</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60873</th>\n",
       "      <td>ice-contractsmous-re-joe-corley-detention-faci...</td>\n",
       "      <td>2015-ICFO-68925_125799</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38781</th>\n",
       "      <td>usmsice-agreementsmous-re-joe-corley-detention...</td>\n",
       "      <td>2015-ICFO-68931_52258</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>60879</th>\n",
       "      <td>ice-contractsmous-re-lasalle-detention-facility</td>\n",
       "      <td>2015-ICFO-69588_125805</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>57441</th>\n",
       "      <td>ice-contractsmous-re-northwest-detention-center</td>\n",
       "      <td>2015-ICFO-69594_122331</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38772</th>\n",
       "      <td>ice-contractsmous-re-val-verde-correctional-fa...</td>\n",
       "      <td>2015-ICFO-69596_52207</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>38536</th>\n",
       "      <td>ice-national-lpr-solicitation-retraction-and-r...</td>\n",
       "      <td>2015-ICFO-71418_51262</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>41255</th>\n",
       "      <td>artesia-ice-detention-center-complaints</td>\n",
       "      <td>2015-ICFO-76918_60307</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>51238</th>\n",
       "      <td>first-record-mentioning-the-silk-road-task-force</td>\n",
       "      <td>2015-ICFO-88321_93769</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>39931</th>\n",
       "      <td>secure-communities-statsreportspolicies-2008-p...</td>\n",
       "      <td>2015-ICFO-76060_55392</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>48426</th>\n",
       "      <td>ice-contractsmous-re-laredo-processing-center</td>\n",
       "      <td>2015-ICFO-84734_82433</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70848</th>\n",
       "      <td>ice-contractsmous-re-south-texas-family-reside...</td>\n",
       "      <td>2015-ICFO-84728_135787</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77135</th>\n",
       "      <td>adelanto-ca-contractsmous-w-ice-re-adelanto-de...</td>\n",
       "      <td>2015-ICFO-85376_146936</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56674</th>\n",
       "      <td>operation-safeguard-contract-and-moumoa</td>\n",
       "      <td>2015-ICFO-88977_119260</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>53397</th>\n",
       "      <td>sarah-saldana-schedule-december-23-2014-july-2015</td>\n",
       "      <td>2015-ICFO-89036_100525</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76666</th>\n",
       "      <td>solitary-confinement-policiesprocedures-and-re...</td>\n",
       "      <td>2015-ICFO-90730_145864</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76867</th>\n",
       "      <td>priority-enforcement-program-local-law-enforce...</td>\n",
       "      <td>2015-ICFO-91159_146575</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>54578</th>\n",
       "      <td>secure-communities-program-reviewsevaluations</td>\n",
       "      <td>2015-ICFO-91160_107736</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>52473</th>\n",
       "      <td>ice-contract-form-completion-procedures</td>\n",
       "      <td>2015-ICFO-91350_96889</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>70836</th>\n",
       "      <td>ice-contractsmousigsas-re-eloy-detention-center</td>\n",
       "      <td>2015-ICFO-94825_135767</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73541</th>\n",
       "      <td>ice-contractsmousigsas-re-west-tennessee-deten...</td>\n",
       "      <td>2015-ICFO-95159_140269</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>81072</th>\n",
       "      <td>ice-contractsmousigsas-re-laredo-processing-ce...</td>\n",
       "      <td>2015-ICFO-95353_147206</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75118</th>\n",
       "      <td>fearlibrarianfreedom</td>\n",
       "      <td>2015-ICFO-100157_142441</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>66911</th>\n",
       "      <td>ice-harris-corporation-contract-1</td>\n",
       "      <td>2016-ICFO-11541_134060</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71356</th>\n",
       "      <td>ice-harris-corporation-contract-3</td>\n",
       "      <td>2016-ICFO-11550_136699</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77133</th>\n",
       "      <td>ice-harris-corporation-contract-4</td>\n",
       "      <td>2016-ICFO-11570_146937</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>71358</th>\n",
       "      <td>ice-harris-corporation-contract-5</td>\n",
       "      <td>2016-ICFO-11575_136703</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>73244</th>\n",
       "      <td>ice-harris-corporation-contract-6</td>\n",
       "      <td>2016-ICFO-11579_139651</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75696</th>\n",
       "      <td>ice-harris-corporation-contract-1</td>\n",
       "      <td>2016-ICFO-15523_143566</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76019</th>\n",
       "      <td>ice-harris-corporation-contract-8</td>\n",
       "      <td>2016-ICFO-15528_144667</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>77134</th>\n",
       "      <td>ice-harris-corporation-contract-9</td>\n",
       "      <td>2016-ICFO-15531_146939</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76018</th>\n",
       "      <td>ice-harris-corporation-contract-10</td>\n",
       "      <td>2016-ICFO-15534_144665</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>83900</th>\n",
       "      <td>ice-harris-corporation-contract-13</td>\n",
       "      <td>2016-ICFO-15543_151713</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>76017</th>\n",
       "      <td>ice-harris-corporation-contract-14</td>\n",
       "      <td>2016-ICFO-15547_144663</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>75501</th>\n",
       "      <td>ice-digital-receiver-technology-contract</td>\n",
       "      <td>2016-ICFO-15513_143128</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            request_slug  \\\n",
       "id                                                         \n",
       "74559            guns-and-ammunition-purchased-in-fy2013   \n",
       "41462  muckrock-request-processing-instructions-immig...   \n",
       "36305  first-record-mentioning-the-marco-polo-task-force   \n",
       "41463              fee-category-records-for-2014foia3508   \n",
       "73557                     ice-tpm-on-easton-amc-incident   \n",
       "42232                     proposed-records-schedules-ice   \n",
       "28098  ices-statistics-on-deportation-through-the-287...   \n",
       "39321  contracts-and-amendments-for-the-njs-elizabeth...   \n",
       "37455  all-contracts-between-ice-and-geo-group-for-nj...   \n",
       "31355                         documents-on-onion-routing   \n",
       "41059            ice-contracts-for-eloy-detention-center   \n",
       "60880     ice-contracts-for-florence-correctional-center   \n",
       "64558  ice-contractsmous-re-san-diego-correctional-fa...   \n",
       "73009  ice-contractsmous-re-north-georgia-detention-c...   \n",
       "73243      ice-contractsmous-re-stewart-detention-center   \n",
       "53431  ice-contractsmous-re-torrance-county-detention...   \n",
       "41847     ice-contractsmous-re-houston-processing-center   \n",
       "41078  ice-contractsmous-re-t-don-hutto-residential-c...   \n",
       "51563                            operation-safeguard-ice   \n",
       "57458   ice-contractsmous-re-adelanto-detention-facility   \n",
       "59122   ice-contractsmous-re-adelanto-detention-facility   \n",
       "57456  ice-contractsmous-re-denver-contract-detention...   \n",
       "60095   ice-contractsmous-re-broward-transitional-center   \n",
       "49955  ice-contractsmous-re-central-texas-detention-f...   \n",
       "60873  ice-contractsmous-re-joe-corley-detention-faci...   \n",
       "38781  usmsice-agreementsmous-re-joe-corley-detention...   \n",
       "60879    ice-contractsmous-re-lasalle-detention-facility   \n",
       "57441    ice-contractsmous-re-northwest-detention-center   \n",
       "38772  ice-contractsmous-re-val-verde-correctional-fa...   \n",
       "38536  ice-national-lpr-solicitation-retraction-and-r...   \n",
       "41255            artesia-ice-detention-center-complaints   \n",
       "51238   first-record-mentioning-the-silk-road-task-force   \n",
       "39931  secure-communities-statsreportspolicies-2008-p...   \n",
       "48426      ice-contractsmous-re-laredo-processing-center   \n",
       "70848  ice-contractsmous-re-south-texas-family-reside...   \n",
       "77135  adelanto-ca-contractsmous-w-ice-re-adelanto-de...   \n",
       "56674            operation-safeguard-contract-and-moumoa   \n",
       "53397  sarah-saldana-schedule-december-23-2014-july-2015   \n",
       "76666  solitary-confinement-policiesprocedures-and-re...   \n",
       "76867  priority-enforcement-program-local-law-enforce...   \n",
       "54578      secure-communities-program-reviewsevaluations   \n",
       "52473            ice-contract-form-completion-procedures   \n",
       "70836    ice-contractsmousigsas-re-eloy-detention-center   \n",
       "73541  ice-contractsmousigsas-re-west-tennessee-deten...   \n",
       "81072  ice-contractsmousigsas-re-laredo-processing-ce...   \n",
       "75118                               fearlibrarianfreedom   \n",
       "66911                  ice-harris-corporation-contract-1   \n",
       "71356                  ice-harris-corporation-contract-3   \n",
       "77133                  ice-harris-corporation-contract-4   \n",
       "71358                  ice-harris-corporation-contract-5   \n",
       "73244                  ice-harris-corporation-contract-6   \n",
       "75696                  ice-harris-corporation-contract-1   \n",
       "76019                  ice-harris-corporation-contract-8   \n",
       "77134                  ice-harris-corporation-contract-9   \n",
       "76018                 ice-harris-corporation-contract-10   \n",
       "83900                 ice-harris-corporation-contract-13   \n",
       "76017                 ice-harris-corporation-contract-14   \n",
       "75501           ice-digital-receiver-technology-contract   \n",
       "\n",
       "                         title description  \n",
       "id                                          \n",
       "74559   2015-ICFO-04708_141795              \n",
       "41462    2015-ICFO-00843_61647              \n",
       "36305    2015-ICFO-00925_39854              \n",
       "41463    2015-ICFO-02197_61654              \n",
       "73557   2015-ICFO-00813_140344              \n",
       "42232    2015-ICFO-01103_63226              \n",
       "28098    2014-ICFO-02026_11003              \n",
       "39321    2014-ICFO-04417_53437              \n",
       "37455    2014-ICFO-05862_45563              \n",
       "31355    2015-ICFO-06250_21317              \n",
       "41059    2015-ICFO-14496_59419              \n",
       "60880   2015-ICFO-14486_125847              \n",
       "64558   2015-ICFO-14456_132116              \n",
       "73009   2015-ICFO-13508_139232              \n",
       "73243   2015-ICFO-95472_139648              \n",
       "53431   2015-ICFO-16763_101419              \n",
       "41847    2015-ICFO-16784_62158              \n",
       "41078    2015-ICFO-16796_59544              \n",
       "51563    2015-ICFO-66842_94404              \n",
       "57458   2015-ICFO-69562_122383              \n",
       "59122   2015-ICFO-69562_123610              \n",
       "57456   2015-ICFO-68826_122378              \n",
       "60095   2015-ICFO-69576_124580              \n",
       "49955    2015-ICFO-68919_88958              \n",
       "60873   2015-ICFO-68925_125799              \n",
       "38781    2015-ICFO-68931_52258              \n",
       "60879   2015-ICFO-69588_125805              \n",
       "57441   2015-ICFO-69594_122331              \n",
       "38772    2015-ICFO-69596_52207              \n",
       "38536    2015-ICFO-71418_51262              \n",
       "41255    2015-ICFO-76918_60307              \n",
       "51238    2015-ICFO-88321_93769              \n",
       "39931    2015-ICFO-76060_55392              \n",
       "48426    2015-ICFO-84734_82433              \n",
       "70848   2015-ICFO-84728_135787              \n",
       "77135   2015-ICFO-85376_146936              \n",
       "56674   2015-ICFO-88977_119260              \n",
       "53397   2015-ICFO-89036_100525              \n",
       "76666   2015-ICFO-90730_145864              \n",
       "76867   2015-ICFO-91159_146575              \n",
       "54578   2015-ICFO-91160_107736              \n",
       "52473    2015-ICFO-91350_96889              \n",
       "70836   2015-ICFO-94825_135767              \n",
       "73541   2015-ICFO-95159_140269              \n",
       "81072   2015-ICFO-95353_147206              \n",
       "75118  2015-ICFO-100157_142441              \n",
       "66911   2016-ICFO-11541_134060              \n",
       "71356   2016-ICFO-11550_136699              \n",
       "77133   2016-ICFO-11570_146937              \n",
       "71358   2016-ICFO-11575_136703              \n",
       "73244   2016-ICFO-11579_139651              \n",
       "75696   2016-ICFO-15523_143566              \n",
       "76019   2016-ICFO-15528_144667              \n",
       "77134   2016-ICFO-15531_146939              \n",
       "76018   2016-ICFO-15534_144665              \n",
       "83900   2016-ICFO-15543_151713              \n",
       "76017   2016-ICFO-15547_144663              \n",
       "75501   2016-ICFO-15513_143128              "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "files[\n",
    "    (files[\"file_ext\"] == \".zip\") &\n",
    "    (files[\"agency_name\"] == \"Immigration and Customs Enforcement\")\n",
    "][[ \"request_slug\", \"title\", \"description\"]].fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://d3gn0r3afghep.cloudfront.net/foia_files/2016/02/22/2016-ICFO-15513_143128.zip\n"
     ]
    }
   ],
   "source": [
    "print(files.loc[75501][\"ffile\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Random Sound!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "97"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "(files[\"file_ext\"] == \".wav\").sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{'access': 'public',\n",
      " 'agency_name': 'St. Louis County Police Department',\n",
      " 'communication_id': 109317,\n",
      " 'date': '2014-11-20T00:00:00',\n",
      " 'description': nan,\n",
      " 'doc_id': nan,\n",
      " 'ffile': 'https://d3gn0r3afghep.cloudfront.net/foia_files/Recorded_on_11-Aug-2014_at_14.25.36_118EQA106266900_censored_1_1.wav',\n",
      " 'file_ext': '.wav',\n",
      " 'is_likely_letter': False,\n",
      " 'jurisdiction_name': 'St. Louis County, MO',\n",
      " 'pages': 0,\n",
      " 'request_id': 13292,\n",
      " 'request_slug': 'communications-with-faa-dhs-and-dla',\n",
      " 'source': 'St. Louis County Police Department',\n",
      " 'title': 'Recorded on 11-Aug-2014 at 14.25.36 (118EQ(A106266900) '\n",
      "          'censored.wav'}\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "\n",
       "                <audio controls=\"controls\" >\n",
       "                    <source src=\"https://d3gn0r3afghep.cloudfront.net/foia_files/Recorded_on_11-Aug-2014_at_14.25.36_118EQA106266900_censored_1_1.wav\" type=\"audio/x-wav\" />\n",
       "                    Your browser does not support the audio element.\n",
       "                </audio>\n",
       "              "
      ],
      "text/plain": [
       "<IPython.lib.display.Audio object>"
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sample_audio = files[\n",
    "    (files[\"file_ext\"] == \".wav\")\n",
    "].sample(1).iloc[0]\n",
    "pprint(dict(sample_audio))\n",
    "display.Audio(url=sample_audio[\"ffile\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "---\n",
    "\n",
    "---\n",
    "\n",
    "---"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.4.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}