clean-data-extract-coordinates.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Clean & Extract Photo Coordinates of data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from soc_module import *\n",
    "import re\n",
    "import datetime\n",
    "from datetime import date, datetime"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load in alameda geojson file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "alameda = geojson.load(open(\"data/alameda-2010.geojson\"))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Write function to filter any old index cols in data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def filter_old_index(name):\n",
    "    if re.match(r\"Unnamed: 0.*\", name):\n",
    "        return False\n",
    "    return True"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Load in survey data"
   ]
  },
  {
   "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>Timestamp</th>\n",
       "      <th>Census Tract</th>\n",
       "      <th>On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many empty beer or liquor bottles are visible in streets, yards, or alleys?</th>\n",
       "      <th>On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many cigarette or cigar butts or discarded cigarette packages  are on the sidewalk or in the gutters?</th>\n",
       "      <th>On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many condoms are present on the sidewalk, in the gutters, or street of block face?</th>\n",
       "      <th>On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how much garbage, litter, or broken glass in the street or on the sidewalks?</th>\n",
       "      <th>Are there abandoned cars in the neighborhood? How many do you see?</th>\n",
       "      <th>On a scale of 1-5 where 1 is \"Friendly Responses / Greetings / Helpful\" and 5 is \"Treated with Suspicion\", How were you regarded by the people in the block face?</th>\n",
       "      <th>On a scale of 1 - 4, where 1 is \"Very well kept / good condition\" and 4 is \"Poor / badly deteriorated condition\", in general, how would you rate the condition of buildings on the block face? (includes residential buildings, recreational facilities, manufacturing plants, business / industrial headquarters, etc)</th>\n",
       "      <th>Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23)</th>\n",
       "      <th>...</th>\n",
       "      <th>Other thoughts or comments for Image #2</th>\n",
       "      <th>Image #3</th>\n",
       "      <th>Full Address of Block Face in Image #3 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.</th>\n",
       "      <th>Other thoughts or comments for Image #3</th>\n",
       "      <th>Image #4</th>\n",
       "      <th>Other thoughts or comments for Image #4</th>\n",
       "      <th>Full Address of Block Face in Image #4 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.</th>\n",
       "      <th>Image #5</th>\n",
       "      <th>Full Address of Block Face in Image #5 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.</th>\n",
       "      <th>Other thoughts or comments for Image #5</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>10/25/2019 12:52:40</td>\n",
       "      <td>4030.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>The Salvation Army was closed on this particul...</td>\n",
       "      <td>https://drive.google.com/open?id=1KY1Mvgc9-eLq...</td>\n",
       "      <td>601 Webster Street, Oakland, CA, 94609</td>\n",
       "      <td>I should have gone in here to try the food, i...</td>\n",
       "      <td>https://drive.google.com/open?id=1keCgzxOTujwn...</td>\n",
       "      <td>UGS crew --- possibly a gang or street dance t...</td>\n",
       "      <td>601 Webster St, Oakland, CA, 94609</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>10/25/2019 12:54:25</td>\n",
       "      <td>4205.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1-eg5yVHgMiRt...</td>\n",
       "      <td>1391 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1mouPFYSJ16OW...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1391 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>https://drive.google.com/open?id=1ymFvN6smtsGK...</td>\n",
       "      <td>708 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10/25/2019 14:32:16</td>\n",
       "      <td>4214.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>Another example of a well-maintained house on ...</td>\n",
       "      <td>https://drive.google.com/open?id=12Fqi0YrnKfV9...</td>\n",
       "      <td>1136 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>One of many houses that’s currently being reno...</td>\n",
       "      <td>https://drive.google.com/open?id=189j-3sZAefhs...</td>\n",
       "      <td>PG&amp;E is currently working on Spruce Street, wi...</td>\n",
       "      <td>1128 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>https://drive.google.com/open?id=1Hn70fF7xfZh1...</td>\n",
       "      <td>1133 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>This house is located at the east side of Spru...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10/25/2019 15:27:57</td>\n",
       "      <td>4030.0</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>Yes</td>\n",
       "      <td>...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1ykCymHyvIELa...</td>\n",
       "      <td>376 8th Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1rfVrj-7QZkbf...</td>\n",
       "      <td>NaN</td>\n",
       "      <td>800 Franklin Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>10/25/2019 21:24:53</td>\n",
       "      <td>4213.0</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>No</td>\n",
       "      <td>...</td>\n",
       "      <td>For the stretch from the intersection of Carlo...</td>\n",
       "      <td>https://drive.google.com/open?id=1S_BJ03A64lP9...</td>\n",
       "      <td>1647 Hopkins Street, Berkeley, CA, 94707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1a4rgJSw2qIMk...</td>\n",
       "      <td>I felt that this residence was fairly represen...</td>\n",
       "      <td>1288 Carlotta Avenue, Berkeley, CA, 94707</td>\n",
       "      <td>https://drive.google.com/open?id=1clCPXj-QBzMp...</td>\n",
       "      <td>1611 Hopkins Street, Berkeley, CA, 94707</td>\n",
       "      <td>This building had a mattress leaning on one of...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 32 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             Timestamp  Census Tract  \\\n",
       "0  10/25/2019 12:52:40        4030.0   \n",
       "1  10/25/2019 12:54:25        4205.0   \n",
       "2  10/25/2019 14:32:16        4214.0   \n",
       "3  10/25/2019 15:27:57        4030.0   \n",
       "4  10/25/2019 21:24:53        4213.0   \n",
       "\n",
       "   On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many empty beer or liquor bottles are visible in streets, yards, or alleys?   \\\n",
       "0                                                  1                                                                                           \n",
       "1                                                  1                                                                                           \n",
       "2                                                  1                                                                                           \n",
       "3                                                  1                                                                                           \n",
       "4                                                  1                                                                                           \n",
       "\n",
       "   On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many cigarette or cigar butts or discarded cigarette packages  are on the sidewalk or in the gutters?   \\\n",
       "0                                                  2                                                                                                                      \n",
       "1                                                  1                                                                                                                      \n",
       "2                                                  1                                                                                                                      \n",
       "3                                                  2                                                                                                                      \n",
       "4                                                  1                                                                                                                      \n",
       "\n",
       "   On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many condoms are present on the sidewalk, in the gutters, or street of block face?   \\\n",
       "0                                                  1                                                                                                   \n",
       "1                                                  1                                                                                                   \n",
       "2                                                  1                                                                                                   \n",
       "3                                                  1                                                                                                   \n",
       "4                                                  1                                                                                                   \n",
       "\n",
       "   On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how much garbage, litter, or broken glass in the street or on the sidewalks?   \\\n",
       "0                                                  2                                                                                        \n",
       "1                                                  1                                                                                        \n",
       "2                                                  1                                                                                        \n",
       "3                                                  3                                                                                        \n",
       "4                                                  1                                                                                        \n",
       "\n",
       "    Are there abandoned cars in the neighborhood? How many do you see?   \\\n",
       "0                                                  0                      \n",
       "1                                                  0                      \n",
       "2                                                  0                      \n",
       "3                                                  0                      \n",
       "4                                                  0                      \n",
       "\n",
       "   On a scale of 1-5 where 1 is \"Friendly Responses / Greetings / Helpful\" and 5 is \"Treated with Suspicion\", How were you regarded by the people in the block face?  \\\n",
       "0                                                  4                                                                                                                   \n",
       "1                                                  1                                                                                                                   \n",
       "2                                                  2                                                                                                                   \n",
       "3                                                  3                                                                                                                   \n",
       "4                                                  2                                                                                                                   \n",
       "\n",
       "   On a scale of 1 - 4, where 1 is \"Very well kept / good condition\" and 4 is \"Poor / badly deteriorated condition\", in general, how would you rate the condition of buildings on the block face? (includes residential buildings, recreational facilities, manufacturing plants, business / industrial headquarters, etc)  \\\n",
       "0                                                  4                                                                                                                                                                                                                                                                         \n",
       "1                                                  1                                                                                                                                                                                                                                                                         \n",
       "2                                                  2                                                                                                                                                                                                                                                                         \n",
       "3                                                  2                                                                                                                                                                                                                                                                         \n",
       "4                                                  4                                                                                                                                                                                                                                                                         \n",
       "\n",
       "  Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23)  \\\n",
       "0                                                Yes                                                                      \n",
       "1                                                 No                                                                      \n",
       "2                                                 No                                                                      \n",
       "3                                                Yes                                                                      \n",
       "4                                                 No                                                                      \n",
       "\n",
       "   ...            Other thoughts or comments for Image #2  \\\n",
       "0  ...  The Salvation Army was closed on this particul...   \n",
       "1  ...                                                NaN   \n",
       "2  ...  Another example of a well-maintained house on ...   \n",
       "3  ...                                                NaN   \n",
       "4  ...  For the stretch from the intersection of Carlo...   \n",
       "\n",
       "                                            Image #3  \\\n",
       "0  https://drive.google.com/open?id=1KY1Mvgc9-eLq...   \n",
       "1  https://drive.google.com/open?id=1-eg5yVHgMiRt...   \n",
       "2  https://drive.google.com/open?id=12Fqi0YrnKfV9...   \n",
       "3  https://drive.google.com/open?id=1ykCymHyvIELa...   \n",
       "4  https://drive.google.com/open?id=1S_BJ03A64lP9...   \n",
       "\n",
       "  Full Address of Block Face in Image #3 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.  \\\n",
       "0            601 Webster Street, Oakland, CA, 94609                                                                                            \n",
       "1                 1391 Solano Ave, Albany, CA, 94706                                                                                           \n",
       "2            1136 Spruce Street, Berkeley, CA, 94704                                                                                           \n",
       "3                 376 8th Street, Oakland, CA, 94607                                                                                           \n",
       "4           1647 Hopkins Street, Berkeley, CA, 94707                                                                                           \n",
       "\n",
       "             Other thoughts or comments for Image #3  \\\n",
       "0   I should have gone in here to try the food, i...   \n",
       "1                                                NaN   \n",
       "2  One of many houses that’s currently being reno...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                                            Image #4  \\\n",
       "0  https://drive.google.com/open?id=1keCgzxOTujwn...   \n",
       "1  https://drive.google.com/open?id=1mouPFYSJ16OW...   \n",
       "2  https://drive.google.com/open?id=189j-3sZAefhs...   \n",
       "3  https://drive.google.com/open?id=1rfVrj-7QZkbf...   \n",
       "4  https://drive.google.com/open?id=1a4rgJSw2qIMk...   \n",
       "\n",
       "             Other thoughts or comments for Image #4  \\\n",
       "0  UGS crew --- possibly a gang or street dance t...   \n",
       "1                                                NaN   \n",
       "2  PG&E is currently working on Spruce Street, wi...   \n",
       "3                                                NaN   \n",
       "4  I felt that this residence was fairly represen...   \n",
       "\n",
       "  Full Address of Block Face in Image #4 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.  \\\n",
       "0                 601 Webster St, Oakland, CA, 94609                                                                                           \n",
       "1                 1391 Solano Ave, Albany, CA, 94706                                                                                           \n",
       "2            1128 Spruce Street, Berkeley, CA, 94704                                                                                           \n",
       "3            800 Franklin Street, Oakland, CA, 94607                                                                                           \n",
       "4          1288 Carlotta Avenue, Berkeley, CA, 94707                                                                                           \n",
       "\n",
       "                                            Image #5  \\\n",
       "0                                                NaN   \n",
       "1  https://drive.google.com/open?id=1ymFvN6smtsGK...   \n",
       "2  https://drive.google.com/open?id=1Hn70fF7xfZh1...   \n",
       "3                                                NaN   \n",
       "4  https://drive.google.com/open?id=1clCPXj-QBzMp...   \n",
       "\n",
       "  Full Address of Block Face in Image #5 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.  \\\n",
       "0                                                NaN                                                                                           \n",
       "1                  708 Solano Ave, Albany, CA, 94706                                                                                           \n",
       "2            1133 Spruce Street, Berkeley, CA, 94704                                                                                           \n",
       "3                                                NaN                                                                                           \n",
       "4           1611 Hopkins Street, Berkeley, CA, 94707                                                                                           \n",
       "\n",
       "             Other thoughts or comments for Image #5  \n",
       "0                                                NaN  \n",
       "1                                                NaN  \n",
       "2  This house is located at the east side of Spru...  \n",
       "3                                                NaN  \n",
       "4  This building had a mattress leaning on one of...  \n",
       "\n",
       "[5 rows x 32 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "data = pd.read_csv('data/ORIGINAL_RESPONSES.csv', usecols=filter_old_index)\n",
    "data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Separate new responses from old ones (bc get_coords takes a long time)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "old = pd.read_csv(\"data/all-responses.csv\", usecols=filter_old_index)\n",
    "new = data[~(data[\"Timestamp\"].isin(old[\"Timestamp\"]))]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Get image coordinates - this takes a while, 38 mins when doing it on all ~150 rows"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/Users/xt/Documents/Academia/Fall 2019/dsmodule/SOC-130AC/soc_module.py:71: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  data['Census Tract'] = data['Census Tract'].apply(fix_tract)\n",
      "/Users/xt/Documents/Academia/Fall 2019/dsmodule/SOC-130AC/soc_module.py:98: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame.\n",
      "Try using .loc[row_indexer,col_indexer] = value instead\n",
      "\n",
      "See the caveats in the documentation: http://pandas.pydata.org/pandas-docs/stable/indexing.html#indexing-view-versus-copy\n",
      "  data['Image #' + str(j)+ ' coordinates'] = image_coords\n"
     ]
    }
   ],
   "source": [
    "%%time\n",
    "new_coords = get_coords(new, alameda, \"sociology-130ac\")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "bring new coords back into old data, write to csv"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = pd.concat([old, new_coords])#.iloc[1:,]\n",
    "data.to_csv(\"data/all-responses.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Define mapping to rename cols"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "new_col_names = {\n",
    "    'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how many empty beer or liquor bottles are visible in streets, yards, or alleys? ': \"Amount of Beer/Liquor Bottles (1 to 5 scale)\",\n",
    "    'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many cigarette or cigar butts or discarded cigarette packages  are on the sidewalk or in the gutters? ': \"Amount of Cigarette/Cigar Butts (1 to 5 scale)\",\n",
    "    'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\",  how many condoms are present on the sidewalk, in the gutters, or street of block face? ': \"Amount of Condoms (1 to 5 scale)\",\n",
    "    'On a scale of 1 - 5, where 1 is \"None\" and 5 is \"A Lot\", how much garbage, litter, or broken glass in the street or on the sidewalks? ': \"Amount of Garbage (1 to 5 scale)\",\n",
    "    ' Are there abandoned cars in the neighborhood? How many do you see? ': \"Abandoned Cars (Y?N)\",\n",
    "    'On a scale of 1-5 where 1 is \"Friendly Responses / Greetings / Helpful\" and 5 is \"Treated with Suspicion\", How were you regarded by the people in the block face?': \"Treatment (1 (Friendly) to 5 (Suspicion) scale)\",\n",
    "    'On a scale of 1 - 4, where 1 is \"Very well kept / good condition\" and 4 is \"Poor / badly deteriorated condition\", in general, how would you rate the condition of buildings on the block face? (includes residential buildings, recreational facilities, manufacturing plants, business / industrial headquarters, etc)': \"Condition of Buildings (1 (Good) to 4 (Poor) scale)\",\n",
    "    'Is there graffiti or evidence of graffiti that has been painted over on buildings, signs, or walls? (Questions 22-23)': \"Graffiti (Y/N)\",\n",
    "    'On a scale of 1 - 4, where 1 is \"No fencing\" and 4 is \"High mesh fencing with barbed wire or spiked tops\", is there fencing and what kind? (includes all property)': \"Fenching (1 (None) to 4 (High mesh) scale)\",\n",
    "    'Are any commercial/residential buildings being renovated?': \"Renovations (Y/N)\",\n",
    "    'What kinds of establishments are there on the block face? Select all that apply.': \"Types of Establishments\",\n",
    "    'On a scale of 1-3, where 1 is \"Few or none\" and 3 is a \"Most/all of it\", how many trees are linking the street of the block face? ': \"Amount of Trees Linked the Block Fence (1 (Few) to 3 (Most) scale)\",\n",
    "    'Is there public transportation available in the block face? ': \"Public Transporation Available (Y/N)\",\n",
    "    'Are private security guards visible?': \"Private Security Visible (Y/N)\",\n",
    "    'Is there a police officer visible? ': \"Police Visible (Y/N)\",\n",
    "    'Full Address of Block Face in Image #1 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #1 Address\",\n",
    "    'Full Address of Block Face in Image #2 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #2 Address\",\n",
    "    'Full Address of Block Face in Image #3 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #3 Address\",\n",
    "    'Full Address of Block Face in Image #4 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #4 Address\",\n",
    "    'Full Address of Block Face in Image #5 (Street Number, Street Name, City, State, Zip Code). E.g.: 2128 Oxford Street, Berkeley, CA, 94704.': \"Image #5 Address\",\n",
    "}"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Extract class_data and image_data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 51,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "skipped\n",
      "skipped\n",
      "skipped\n",
      "skipped\n",
      "skipped\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>Image #1</th>\n",
       "      <th>Image #1 Address</th>\n",
       "      <th>Other thoughts or comments for Image #1</th>\n",
       "      <th>Image #2</th>\n",
       "      <th>Image #2 Address</th>\n",
       "      <th>Other thoughts or comments for Image #2</th>\n",
       "      <th>Image #3</th>\n",
       "      <th>Image #3 Address</th>\n",
       "      <th>Other thoughts or comments for Image #3</th>\n",
       "      <th>Image #4</th>\n",
       "      <th>...</th>\n",
       "      <th>Image #4 Address</th>\n",
       "      <th>Image #5</th>\n",
       "      <th>Image #5 Address</th>\n",
       "      <th>Other thoughts or comments for Image #5</th>\n",
       "      <th>Image #1 coordinates</th>\n",
       "      <th>Image #2 coordinates</th>\n",
       "      <th>Image #3 coordinates</th>\n",
       "      <th>Image #4 coordinates</th>\n",
       "      <th>Image #5 coordinates</th>\n",
       "      <th>Census Tract</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>https://drive.google.com/open?id=1RmWTmeeYjbIg...</td>\n",
       "      <td>601 Webster St, Oakland, CA, 94609</td>\n",
       "      <td>This was one of a few stores that were gated u...</td>\n",
       "      <td>https://drive.google.com/open?id=1PXD3bu_7j2vV...</td>\n",
       "      <td>601 Webster Street, Oakland, CA, 94609</td>\n",
       "      <td>The Salvation Army was closed on this particul...</td>\n",
       "      <td>https://drive.google.com/open?id=1KY1Mvgc9-eLq...</td>\n",
       "      <td>601 Webster Street, Oakland, CA, 94609</td>\n",
       "      <td>I should have gone in here to try the food, i...</td>\n",
       "      <td>https://drive.google.com/open?id=1keCgzxOTujwn...</td>\n",
       "      <td>...</td>\n",
       "      <td>601 Webster St, Oakland, CA, 94609</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[37.798025, -122.272283]</td>\n",
       "      <td>[37.798025, -122.272283]</td>\n",
       "      <td>[37.798025, -122.272283]</td>\n",
       "      <td>[37.798025, -122.272283]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>https://drive.google.com/open?id=1stJ2WAm5XvqD...</td>\n",
       "      <td>931 Carmel Ave, Albany, CA, 94706</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1UlT_fZkGjDLH...</td>\n",
       "      <td>1362 Marin Ave, Albany, CA , 94706</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1-eg5yVHgMiRt...</td>\n",
       "      <td>1391 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1mouPFYSJ16OW...</td>\n",
       "      <td>...</td>\n",
       "      <td>1391 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>https://drive.google.com/open?id=1ymFvN6smtsGK...</td>\n",
       "      <td>708 Solano Ave, Albany, CA, 94706</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[37.8899946030529, -122.289521691888]</td>\n",
       "      <td>[37.8883455055134, -122.288877697661]</td>\n",
       "      <td>[37.8908002222222, -122.290461888889]</td>\n",
       "      <td>[37.8908002222222, -122.290461888889]</td>\n",
       "      <td>[37.888633027305, -122.307014522052]</td>\n",
       "      <td>4205</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>https://drive.google.com/open?id=1kpIEXVR335w3...</td>\n",
       "      <td>2200 Los Angeles St, Berkeley, CA, 94704</td>\n",
       "      <td>The only house on Los Angeles Street with a si...</td>\n",
       "      <td>https://drive.google.com/open?id=1zRsqDdsZBeYD...</td>\n",
       "      <td>2210 Los Angeles Street, Berkeley, CA, 94704</td>\n",
       "      <td>Another example of a well-maintained house on ...</td>\n",
       "      <td>https://drive.google.com/open?id=12Fqi0YrnKfV9...</td>\n",
       "      <td>1136 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>One of many houses that’s currently being reno...</td>\n",
       "      <td>https://drive.google.com/open?id=189j-3sZAefhs...</td>\n",
       "      <td>...</td>\n",
       "      <td>1128 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>https://drive.google.com/open?id=1Hn70fF7xfZh1...</td>\n",
       "      <td>1133 Spruce Street, Berkeley, CA, 94704</td>\n",
       "      <td>This house is located at the east side of Spru...</td>\n",
       "      <td>(37.8883409, -122.270922)</td>\n",
       "      <td>(37.8883409, -122.270922)</td>\n",
       "      <td>[37.8876024042097, -122.266919689902]</td>\n",
       "      <td>[37.8877886656757, -122.266948315416]</td>\n",
       "      <td>[37.8879082848895, -122.26673971951]</td>\n",
       "      <td>4214</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>https://drive.google.com/open?id=15rdCtKIgX-Fb...</td>\n",
       "      <td>378 8th Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1hMNbBE8pc9Q8...</td>\n",
       "      <td>360 8th Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1ykCymHyvIELa...</td>\n",
       "      <td>376 8th Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1rfVrj-7QZkbf...</td>\n",
       "      <td>...</td>\n",
       "      <td>800 Franklin Street, Oakland, CA, 94607</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>[37.7995886695836, -122.272012934734]</td>\n",
       "      <td>[37.7994604111548, -122.27167993785]</td>\n",
       "      <td>[37.8020326, -122.2784424]</td>\n",
       "      <td>[37.799789, -122.272409]</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4030</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>https://drive.google.com/open?id=1F1B-1oHlh9y6...</td>\n",
       "      <td>1645 Hopkins Street, Berkeley, CA, 94707</td>\n",
       "      <td>Unfortunately, this building was at the end of...</td>\n",
       "      <td>https://drive.google.com/open?id=1ycZ91fitTHwh...</td>\n",
       "      <td>1226 Carlotta Avenue, Berkeley, CA, 94707</td>\n",
       "      <td>For the stretch from the intersection of Carlo...</td>\n",
       "      <td>https://drive.google.com/open?id=1S_BJ03A64lP9...</td>\n",
       "      <td>1647 Hopkins Street, Berkeley, CA, 94707</td>\n",
       "      <td>NaN</td>\n",
       "      <td>https://drive.google.com/open?id=1a4rgJSw2qIMk...</td>\n",
       "      <td>...</td>\n",
       "      <td>1288 Carlotta Avenue, Berkeley, CA, 94707</td>\n",
       "      <td>https://drive.google.com/open?id=1clCPXj-QBzMp...</td>\n",
       "      <td>1611 Hopkins Street, Berkeley, CA, 94707</td>\n",
       "      <td>This building had a mattress leaning on one of...</td>\n",
       "      <td>[37.88256465, -122.28065260328]</td>\n",
       "      <td>[37.8839914809146, -122.28048481675]</td>\n",
       "      <td>[37.8824167547207, -122.280525537024]</td>\n",
       "      <td>[37.8826557841063, -122.280673827285]</td>\n",
       "      <td>[37.8822196, -122.281236836644]</td>\n",
       "      <td>4213</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                                            Image #1  \\\n",
       "0  https://drive.google.com/open?id=1RmWTmeeYjbIg...   \n",
       "1  https://drive.google.com/open?id=1stJ2WAm5XvqD...   \n",
       "2  https://drive.google.com/open?id=1kpIEXVR335w3...   \n",
       "3  https://drive.google.com/open?id=15rdCtKIgX-Fb...   \n",
       "4  https://drive.google.com/open?id=1F1B-1oHlh9y6...   \n",
       "\n",
       "                           Image #1 Address  \\\n",
       "0       601 Webster St, Oakland, CA, 94609    \n",
       "1         931 Carmel Ave, Albany, CA, 94706   \n",
       "2  2200 Los Angeles St, Berkeley, CA, 94704   \n",
       "3        378 8th Street, Oakland, CA, 94607   \n",
       "4  1645 Hopkins Street, Berkeley, CA, 94707   \n",
       "\n",
       "             Other thoughts or comments for Image #1  \\\n",
       "0  This was one of a few stores that were gated u...   \n",
       "1                                                NaN   \n",
       "2  The only house on Los Angeles Street with a si...   \n",
       "3                                                NaN   \n",
       "4  Unfortunately, this building was at the end of...   \n",
       "\n",
       "                                            Image #2  \\\n",
       "0  https://drive.google.com/open?id=1PXD3bu_7j2vV...   \n",
       "1  https://drive.google.com/open?id=1UlT_fZkGjDLH...   \n",
       "2  https://drive.google.com/open?id=1zRsqDdsZBeYD...   \n",
       "3  https://drive.google.com/open?id=1hMNbBE8pc9Q8...   \n",
       "4  https://drive.google.com/open?id=1ycZ91fitTHwh...   \n",
       "\n",
       "                               Image #2 Address  \\\n",
       "0       601 Webster Street, Oakland, CA, 94609    \n",
       "1            1362 Marin Ave, Albany, CA , 94706   \n",
       "2  2210 Los Angeles Street, Berkeley, CA, 94704   \n",
       "3            360 8th Street, Oakland, CA, 94607   \n",
       "4     1226 Carlotta Avenue, Berkeley, CA, 94707   \n",
       "\n",
       "             Other thoughts or comments for Image #2  \\\n",
       "0  The Salvation Army was closed on this particul...   \n",
       "1                                                NaN   \n",
       "2  Another example of a well-maintained house on ...   \n",
       "3                                                NaN   \n",
       "4  For the stretch from the intersection of Carlo...   \n",
       "\n",
       "                                            Image #3  \\\n",
       "0  https://drive.google.com/open?id=1KY1Mvgc9-eLq...   \n",
       "1  https://drive.google.com/open?id=1-eg5yVHgMiRt...   \n",
       "2  https://drive.google.com/open?id=12Fqi0YrnKfV9...   \n",
       "3  https://drive.google.com/open?id=1ykCymHyvIELa...   \n",
       "4  https://drive.google.com/open?id=1S_BJ03A64lP9...   \n",
       "\n",
       "                           Image #3 Address  \\\n",
       "0   601 Webster Street, Oakland, CA, 94609    \n",
       "1        1391 Solano Ave, Albany, CA, 94706   \n",
       "2   1136 Spruce Street, Berkeley, CA, 94704   \n",
       "3        376 8th Street, Oakland, CA, 94607   \n",
       "4  1647 Hopkins Street, Berkeley, CA, 94707   \n",
       "\n",
       "             Other thoughts or comments for Image #3  \\\n",
       "0   I should have gone in here to try the food, i...   \n",
       "1                                                NaN   \n",
       "2  One of many houses that’s currently being reno...   \n",
       "3                                                NaN   \n",
       "4                                                NaN   \n",
       "\n",
       "                                            Image #4  ...  \\\n",
       "0  https://drive.google.com/open?id=1keCgzxOTujwn...  ...   \n",
       "1  https://drive.google.com/open?id=1mouPFYSJ16OW...  ...   \n",
       "2  https://drive.google.com/open?id=189j-3sZAefhs...  ...   \n",
       "3  https://drive.google.com/open?id=1rfVrj-7QZkbf...  ...   \n",
       "4  https://drive.google.com/open?id=1a4rgJSw2qIMk...  ...   \n",
       "\n",
       "                            Image #4 Address  \\\n",
       "0         601 Webster St, Oakland, CA, 94609   \n",
       "1         1391 Solano Ave, Albany, CA, 94706   \n",
       "2    1128 Spruce Street, Berkeley, CA, 94704   \n",
       "3    800 Franklin Street, Oakland, CA, 94607   \n",
       "4  1288 Carlotta Avenue, Berkeley, CA, 94707   \n",
       "\n",
       "                                            Image #5  \\\n",
       "0                                                NaN   \n",
       "1  https://drive.google.com/open?id=1ymFvN6smtsGK...   \n",
       "2  https://drive.google.com/open?id=1Hn70fF7xfZh1...   \n",
       "3                                                NaN   \n",
       "4  https://drive.google.com/open?id=1clCPXj-QBzMp...   \n",
       "\n",
       "                           Image #5 Address  \\\n",
       "0                                       NaN   \n",
       "1         708 Solano Ave, Albany, CA, 94706   \n",
       "2   1133 Spruce Street, Berkeley, CA, 94704   \n",
       "3                                       NaN   \n",
       "4  1611 Hopkins Street, Berkeley, CA, 94707   \n",
       "\n",
       "             Other thoughts or comments for Image #5  \\\n",
       "0                                                NaN   \n",
       "1                                                NaN   \n",
       "2  This house is located at the east side of Spru...   \n",
       "3                                                NaN   \n",
       "4  This building had a mattress leaning on one of...   \n",
       "\n",
       "                    Image #1 coordinates  \\\n",
       "0               [37.798025, -122.272283]   \n",
       "1  [37.8899946030529, -122.289521691888]   \n",
       "2              (37.8883409, -122.270922)   \n",
       "3  [37.7995886695836, -122.272012934734]   \n",
       "4        [37.88256465, -122.28065260328]   \n",
       "\n",
       "                    Image #2 coordinates  \\\n",
       "0               [37.798025, -122.272283]   \n",
       "1  [37.8883455055134, -122.288877697661]   \n",
       "2              (37.8883409, -122.270922)   \n",
       "3   [37.7994604111548, -122.27167993785]   \n",
       "4   [37.8839914809146, -122.28048481675]   \n",
       "\n",
       "                    Image #3 coordinates  \\\n",
       "0               [37.798025, -122.272283]   \n",
       "1  [37.8908002222222, -122.290461888889]   \n",
       "2  [37.8876024042097, -122.266919689902]   \n",
       "3             [37.8020326, -122.2784424]   \n",
       "4  [37.8824167547207, -122.280525537024]   \n",
       "\n",
       "                    Image #4 coordinates  \\\n",
       "0               [37.798025, -122.272283]   \n",
       "1  [37.8908002222222, -122.290461888889]   \n",
       "2  [37.8877886656757, -122.266948315416]   \n",
       "3               [37.799789, -122.272409]   \n",
       "4  [37.8826557841063, -122.280673827285]   \n",
       "\n",
       "                   Image #5 coordinates Census Tract  \n",
       "0                                   NaN         4030  \n",
       "1  [37.888633027305, -122.307014522052]         4205  \n",
       "2  [37.8879082848895, -122.26673971951]         4214  \n",
       "3                                   NaN         4030  \n",
       "4       [37.8822196, -122.281236836644]         4213  \n",
       "\n",
       "[5 rows x 21 columns]"
      ]
     },
     "execution_count": 51,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "class_data = data.copy()\n",
    "\n",
    "class_data = class_data.rename(new_col_names, axis=1)\n",
    "\n",
    "class_data['Timestamp'] = class_data['Timestamp'].astype('str')\n",
    "class_data = class_data.iloc[1:,:] # why??\n",
    "\n",
    "# rekey yes and no responses\n",
    "for c in class_data.columns:\n",
    "    try:\n",
    "        if \"Yes\" in set(class_data[c]):\n",
    "            class_data[c] = class_data[c].map({\"Yes\": 1, \"No\": 0})\n",
    "    except:\n",
    "        print(\"skipped\")\n",
    "\n",
    "# limit to submissions from Fall 2019\n",
    "class_data['Timestamp'] = [datetime.strptime(time, '%m/%d/%Y %H:%M:%S') for time in class_data['Timestamp']]\n",
    "class_data = class_data[[time.year == 2019 for time in class_data['Timestamp']]]\n",
    "class_data = class_data.reset_index(drop=True)\n",
    "\n",
    "image_data = class_data.iloc[:,17:]\n",
    "\n",
    "image_data['Census Tract'] = class_data['Census Tract']\n",
    "image_data.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Select columns for class_data and change some entries of establishments col"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "class_data = class_data.iloc[:,:17]\n",
    "\n",
    "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Bodega, deli, corner-store, convenience store', 'Bodega deli corner-store convenience store')\n",
    "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Payday lenders, check cashers, or pawn shops', 'Payday lenders check cashers or pawn shops')\n",
    "class_data[\"Types of Establishments\"] = class_data[\"Types of Establishments\"].str.replace('Professional offices \\(doctor, dentist, lawyer, accountant, real estate\\)', 'Professional offices (doctor dentist lawyer accountant real estate)')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "image_data.to_csv(\"data/image_data.csv\", index=False)\n",
    "class_data.to_csv(\"data/class_data.csv\", index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "@webio": {
   "lastCommId": null,
   "lastKernelId": null
  },
  "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.7.3"
  },
  "notify_time": "5",
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}