data_cleaning/clean_num_officers_by_agency.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean and reformat the raw data describing all Texas law enforcement agencies the number of officers in each.\n",
    "\n",
    "\n",
    "* Input: `tji/raw-and-processing/TCOLE - Num Officers By Agency.xlsx`\n",
    "* Output: `tji/auxiliary-datasets/`\n",
    "  * `num_officers_by_agency.csv`\n",
    "  * `agencies_and_counties.csv`\n",
    "  \n",
    "##### Author: Everett Wetchler (everett.wetchler@gmail.com)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 1. Setup and read data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "DW_PROJECT_CDR = 'tji/deaths-in-custody'\n",
    "DW_PROJECT_OIS = 'tji/officer-involved-shootings'\n",
    "DW_PROJECT_AUXILIARY_DATASETS = 'tji/auxiliary-datasets'\n",
    "DW_PROJECT_RAW_AND_PROCESSING = 'tji/raw-and-processing'\n",
    "\n",
    "OUTPUT_DW_PROJECT = DW_PROJECT_AUXILIARY_DATASETS"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Everett Wetchler 2018-12-17 14:05:27 PST\n",
      "\n",
      "datadotworld 1.6.0\n",
      "numpy 1.14.5\n",
      "pandas 0.23.3\n",
      "Git hash: 66a2c5da8c3ddd0ff19e6f7ac947b952d14323e9\n",
      "Git repo: git@github.com:texas-justice-initiative/data-processing.git\n",
      "watermark 1.6.1\n"
     ]
    }
   ],
   "source": [
    "import datadotworld as dw\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "\n",
    "%load_ext watermark\n",
    "%watermark -a \"Everett Wetchler\" -d -t -z -r -g -w -p datadotworld,numpy,pandas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "from lib.cleaning_tools import read_dtw_excel\n",
    "from lib.standardize_police_agency_names import standardize_agency_name"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing excel file to temp file: /var/folders/dc/8cbxbsh515s908xl0zyprszm0000gn/T/tmpha0ik43l\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Department Name</th>\n",
       "      <th>County Name</th>\n",
       "      <th>July 1st 2016</th>\n",
       "      <th>Dec 31st 2016</th>\n",
       "      <th>July 1st 2017</th>\n",
       "      <th>Dec 31st 2017</th>\n",
       "      <th>State?</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PALESTINE CITY MARSHAL'S OFFICE</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDERSON CO. CONST. PCT. 3</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANDERSON CO. CONST. PCT. 4</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANDERSON CO. CONST. PCT. 2</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PALESTINE FIRE DEPT.</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Department Name County Name  July 1st 2016  Dec 31st 2016  \\\n",
       "0  PALESTINE CITY MARSHAL'S OFFICE   ANDERSON               0              0   \n",
       "1       ANDERSON CO. CONST. PCT. 3   ANDERSON               1              1   \n",
       "2       ANDERSON CO. CONST. PCT. 4   ANDERSON               2              2   \n",
       "3       ANDERSON CO. CONST. PCT. 2   ANDERSON               2              2   \n",
       "4             PALESTINE FIRE DEPT.   ANDERSON               2              2   \n",
       "\n",
       "   July 1st 2017  Dec 31st 2017 State?  \n",
       "0              0              1    NaN  \n",
       "1              1              1    NaN  \n",
       "2              1              1    NaN  \n",
       "3              2              2    NaN  \n",
       "4              1              2    NaN  "
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = read_dtw_excel(DW_PROJECT_RAW_AND_PROCESSING, 'original/TCOLE - Num Officers By Agency.xlsx', select_sheet='Sheet1')\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 2. Begin cleaning"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Rename columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.columns = ['agency', 'county',\n",
    "              'num_officers_2016_07_01', 'num_officers_2016_12_31',\n",
    "              'num_officers_2017_07_01', 'num_officers_2017_12_31',\n",
    "              'is_state_agency']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Standardize agency and county names"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>agency</th>\n",
       "      <th>county</th>\n",
       "      <th>num_officers_2016_07_01</th>\n",
       "      <th>num_officers_2016_12_31</th>\n",
       "      <th>num_officers_2017_07_01</th>\n",
       "      <th>num_officers_2017_12_31</th>\n",
       "      <th>is_state_agency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>PALESTINE CITY MARSHALS OFFICE</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>ANDERSON CO CONST PCT 3</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>ANDERSON CO CONST PCT 4</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>ANDERSON CO CONST PCT 2</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>PALESTINE FIRE DEPT</td>\n",
       "      <td>ANDERSON</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>False</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           agency    county  num_officers_2016_07_01  \\\n",
       "0  PALESTINE CITY MARSHALS OFFICE  ANDERSON                        0   \n",
       "1         ANDERSON CO CONST PCT 3  ANDERSON                        1   \n",
       "2         ANDERSON CO CONST PCT 4  ANDERSON                        2   \n",
       "3         ANDERSON CO CONST PCT 2  ANDERSON                        2   \n",
       "4             PALESTINE FIRE DEPT  ANDERSON                        2   \n",
       "\n",
       "   num_officers_2016_12_31  num_officers_2017_07_01  num_officers_2017_12_31  \\\n",
       "0                        0                        0                        1   \n",
       "1                        1                        1                        1   \n",
       "2                        2                        1                        1   \n",
       "3                        2                        2                        2   \n",
       "4                        2                        1                        2   \n",
       "\n",
       "   is_state_agency  \n",
       "0            False  \n",
       "1            False  \n",
       "2            False  \n",
       "3            False  \n",
       "4            False  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['agency'] = df['agency'].apply(standardize_agency_name)\n",
    "df['county'] = df['county'].apply(lambda d: d.strip().upper())\n",
    "df['is_state_agency'] = df['is_state_agency'] == 'y'\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create special county (\"TEXAS\") for state agencies\n",
    "\n",
    "#### Instead of leaving them in Travis county, where they default to"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Identified 15 state agencies -- changed their county to STATE\n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>agency</th>\n",
       "      <th>county</th>\n",
       "      <th>num_officers_2016_07_01</th>\n",
       "      <th>num_officers_2016_12_31</th>\n",
       "      <th>num_officers_2017_07_01</th>\n",
       "      <th>num_officers_2017_12_31</th>\n",
       "      <th>is_state_agency</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2388</th>\n",
       "      <td>TEXAS RACING COMMISSION</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2389</th>\n",
       "      <td>TEXAS JUVENILE JUSTICE DEPT</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2391</th>\n",
       "      <td>TEXAS LOTTERY COMMISSION</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2392</th>\n",
       "      <td>TEXAS ST BOARD OF PHARMACY</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>8</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>6</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2395</th>\n",
       "      <td>TEXAS ST BOARD OF DENTAL EXAMINERS</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>10</td>\n",
       "      <td>12</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2407</th>\n",
       "      <td>TEXAS COMMISSION ON LAW ENFORCEMENT</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>19</td>\n",
       "      <td>20</td>\n",
       "      <td>21</td>\n",
       "      <td>19</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2408</th>\n",
       "      <td>TEXAS COMPTROLLER OF PUBLIC ACCOUNTS</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>19</td>\n",
       "      <td>22</td>\n",
       "      <td>21</td>\n",
       "      <td>24</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2410</th>\n",
       "      <td>TEXAS JUVENILE JUSTICE DEPT OIG</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>22</td>\n",
       "      <td>20</td>\n",
       "      <td>24</td>\n",
       "      <td>23</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2413</th>\n",
       "      <td>TEXAS DEPT OF INSURANCE FRAUD UNIT</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>29</td>\n",
       "      <td>30</td>\n",
       "      <td>26</td>\n",
       "      <td>25</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2418</th>\n",
       "      <td>TEXAS HEALTH AND HUMAN SERVICES COMM- OFFICE O...</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>34</td>\n",
       "      <td>37</td>\n",
       "      <td>36</td>\n",
       "      <td>33</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2423</th>\n",
       "      <td>TEXAS RAILROAD ASSOCIATION</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>99</td>\n",
       "      <td>97</td>\n",
       "      <td>99</td>\n",
       "      <td>92</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2424</th>\n",
       "      <td>TEXAS ATTY GENERALS OFFICE</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>164</td>\n",
       "      <td>171</td>\n",
       "      <td>173</td>\n",
       "      <td>175</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2425</th>\n",
       "      <td>TEXAS ALCOHOLIC BEVERAGE COMM</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>301</td>\n",
       "      <td>291</td>\n",
       "      <td>287</td>\n",
       "      <td>292</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2428</th>\n",
       "      <td>TEXAS PARKS WILDLIFE DEPT</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>745</td>\n",
       "      <td>770</td>\n",
       "      <td>766</td>\n",
       "      <td>785</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2430</th>\n",
       "      <td>TEXAS DEPT OF PUBLIC SAFETY</td>\n",
       "      <td>TEXAS</td>\n",
       "      <td>4874</td>\n",
       "      <td>5023</td>\n",
       "      <td>5256</td>\n",
       "      <td>5363</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                 agency county  \\\n",
       "2388                            TEXAS RACING COMMISSION  TEXAS   \n",
       "2389                        TEXAS JUVENILE JUSTICE DEPT  TEXAS   \n",
       "2391                           TEXAS LOTTERY COMMISSION  TEXAS   \n",
       "2392                         TEXAS ST BOARD OF PHARMACY  TEXAS   \n",
       "2395                 TEXAS ST BOARD OF DENTAL EXAMINERS  TEXAS   \n",
       "2407                TEXAS COMMISSION ON LAW ENFORCEMENT  TEXAS   \n",
       "2408               TEXAS COMPTROLLER OF PUBLIC ACCOUNTS  TEXAS   \n",
       "2410                    TEXAS JUVENILE JUSTICE DEPT OIG  TEXAS   \n",
       "2413                 TEXAS DEPT OF INSURANCE FRAUD UNIT  TEXAS   \n",
       "2418  TEXAS HEALTH AND HUMAN SERVICES COMM- OFFICE O...  TEXAS   \n",
       "2423                         TEXAS RAILROAD ASSOCIATION  TEXAS   \n",
       "2424                         TEXAS ATTY GENERALS OFFICE  TEXAS   \n",
       "2425                      TEXAS ALCOHOLIC BEVERAGE COMM  TEXAS   \n",
       "2428                          TEXAS PARKS WILDLIFE DEPT  TEXAS   \n",
       "2430                        TEXAS DEPT OF PUBLIC SAFETY  TEXAS   \n",
       "\n",
       "      num_officers_2016_07_01  num_officers_2016_12_31  \\\n",
       "2388                        4                        5   \n",
       "2389                        4                        4   \n",
       "2391                        7                        7   \n",
       "2392                        8                        7   \n",
       "2395                       10                       12   \n",
       "2407                       19                       20   \n",
       "2408                       19                       22   \n",
       "2410                       22                       20   \n",
       "2413                       29                       30   \n",
       "2418                       34                       37   \n",
       "2423                       99                       97   \n",
       "2424                      164                      171   \n",
       "2425                      301                      291   \n",
       "2428                      745                      770   \n",
       "2430                     4874                     5023   \n",
       "\n",
       "      num_officers_2017_07_01  num_officers_2017_12_31  is_state_agency  \n",
       "2388                        5                        5             True  \n",
       "2389                        4                        4             True  \n",
       "2391                        7                        7             True  \n",
       "2392                        7                        6             True  \n",
       "2395                        4                        2             True  \n",
       "2407                       21                       19             True  \n",
       "2408                       21                       24             True  \n",
       "2410                       24                       23             True  \n",
       "2413                       26                       25             True  \n",
       "2418                       36                       33             True  \n",
       "2423                       99                       92             True  \n",
       "2424                      173                      175             True  \n",
       "2425                      287                      292             True  \n",
       "2428                      766                      785             True  \n",
       "2430                     5256                     5363             True  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[df.is_state_agency, 'county'] = 'TEXAS'\n",
    "print(\"Identified %d state agencies -- changed their county to STATE\" % df.is_state_agency.sum())\n",
    "df[df.is_state_agency]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Note the last line - Texas DPS - which includes Texas Highway Patrol. It's by far the bulk of the state department force."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Texas has 2703 police agencies\n",
      " - 123 of them had zero officers at the end of 2017\n",
      " - 2580 had 1+ officers at the end of 2017\n"
     ]
    }
   ],
   "source": [
    "print(\"Texas has %d police agencies\\n - %d of them had zero officers at the end of 2017\\n - %d had 1+ officers at the end of 2017\" % (\n",
    "    len(df), (df['num_officers_2017_12_31'] == 0).sum(), (df['num_officers_2017_12_31'] > 0).sum()))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## 3. Write"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.sort_values(['county', 'agency'], inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing to data.world: tji/auxiliary-datasets/num_officers_by_agency.csv\n"
     ]
    }
   ],
   "source": [
    "with dw.open_remote_file(OUTPUT_DW_PROJECT, 'num_officers_by_agency.csv') as w:\n",
    "    print(\"Writing to data.world: %s/%s\" % (OUTPUT_DW_PROJECT, 'num_officers_by_agency.csv'))\n",
    "    df.to_csv(w, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Writing to data.world: tji/auxiliary-datasets/agencies_and_counties.csv\n"
     ]
    }
   ],
   "source": [
    "with dw.open_remote_file(OUTPUT_DW_PROJECT, 'agencies_and_counties.csv') as w:\n",
    "    print(\"Writing to data.world: %s/%s\" % (OUTPUT_DW_PROJECT, 'agencies_and_counties.csv'))\n",
    "    df[['agency', 'county']].to_csv(w, index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.6.5"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}