scripts/mturk_streetview.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Analyzing MTurk Coding\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Read in the files from geo sampling that are fed to Mturk\n",
    "jakarta = pd.read_csv(\"../data/google_street_view_metadata/jakarta.csv\")\n",
    "lagos   = pd.read_csv(\"../data/google_street_view_metadata/lagos.csv\")\n",
    "bangkok = pd.read_csv(\"../data/google_street_view_metadata/bangkok.csv\")\n",
    "wayne   = pd.read_csv(\"../data/google_street_view_metadata/wayne2.csv\")\n",
    "\n",
    "# Read in the Mturk data\n",
    "jakarta_mturk = pd.read_csv(\"../data/mturk/jakarta2_mturk_14_06_18.csv\")  \n",
    "lagos_mturk   = pd.read_csv(\"../data/mturk/lagos_mturk_2018_06_08.csv\")\n",
    "bangkok_mturk = pd.read_csv(\"../data/mturk/bangkok_mturk_2018_06_02.csv\")\n",
    "wayne_mturk   = pd.read_csv(\"../data/mturk/wayne_mturk_2018_06_11_18.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>id</th>\n",
       "      <th>segment_id</th>\n",
       "      <th>osm_id</th>\n",
       "      <th>osm_name</th>\n",
       "      <th>osm_type</th>\n",
       "      <th>start_lat</th>\n",
       "      <th>start_long</th>\n",
       "      <th>end_lat</th>\n",
       "      <th>end_long</th>\n",
       "      <th>gsv_metadata</th>\n",
       "      <th>copyright</th>\n",
       "      <th>date</th>\n",
       "      <th>location</th>\n",
       "      <th>pano_id</th>\n",
       "      <th>status</th>\n",
       "      <th>url_img0</th>\n",
       "      <th>url_img90</th>\n",
       "      <th>url_img180</th>\n",
       "      <th>url_img270</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>4705034</td>\n",
       "      <td>NaN</td>\n",
       "      <td>primary</td>\n",
       "      <td>-6.180847</td>\n",
       "      <td>106.822621</td>\n",
       "      <td>-6.180454</td>\n",
       "      <td>106.822612</td>\n",
       "      <td>{u'date': u'2017-01', u'status': u'OK', u'loca...</td>\n",
       "      <td>© 2017 Google</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>{u'lat': -6.1807921, u'lng': 106.8226687}</td>\n",
       "      <td>ZsuaCXoZMzBUiimX6wPgOg</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0000.jpg</td>\n",
       "      <td>jakarta/img90_0000.jpg</td>\n",
       "      <td>jakarta/img180_0000.jpg</td>\n",
       "      <td>jakarta/img270_0000.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>4705043</td>\n",
       "      <td>Jalan Gunung Sahari III</td>\n",
       "      <td>tertiary</td>\n",
       "      <td>-6.167753</td>\n",
       "      <td>106.842434</td>\n",
       "      <td>-6.167984</td>\n",
       "      <td>106.839682</td>\n",
       "      <td>{u'date': u'2017-01', u'status': u'OK', u'loca...</td>\n",
       "      <td>© 2017 Google</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>{u'lat': -6.16773708251, u'lng': 106.842439482}</td>\n",
       "      <td>IZDY6K-68eEZ_2Gn0UX7GQ</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0001.jpg</td>\n",
       "      <td>jakarta/img90_0001.jpg</td>\n",
       "      <td>jakarta/img180_0001.jpg</td>\n",
       "      <td>jakarta/img270_0001.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2</td>\n",
       "      <td>9</td>\n",
       "      <td>4705053</td>\n",
       "      <td>Jalan Gunung Sahari Raya</td>\n",
       "      <td>primary</td>\n",
       "      <td>-6.156200</td>\n",
       "      <td>106.836681</td>\n",
       "      <td>-6.160577</td>\n",
       "      <td>106.837808</td>\n",
       "      <td>{u'date': u'2017-01', u'status': u'OK', u'loca...</td>\n",
       "      <td>© 2017 Google</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>{u'lat': -6.15615671549, u'lng': 106.836705101}</td>\n",
       "      <td>efaqkGpxjxArkRbc2_8Ezw</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0002.jpg</td>\n",
       "      <td>jakarta/img90_0002.jpg</td>\n",
       "      <td>jakarta/img180_0002.jpg</td>\n",
       "      <td>jakarta/img270_0002.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>11</td>\n",
       "      <td>4705053</td>\n",
       "      <td>Jalan Gunung Sahari Raya</td>\n",
       "      <td>primary</td>\n",
       "      <td>-6.164970</td>\n",
       "      <td>106.838870</td>\n",
       "      <td>-6.169344</td>\n",
       "      <td>106.840010</td>\n",
       "      <td>{u'date': u'2017-01', u'status': u'OK', u'loca...</td>\n",
       "      <td>© 2017 Google</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>{u'lat': -6.1650009, u'lng': 106.8389159}</td>\n",
       "      <td>TOfUJy6Rmv1imEwasCct6w</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0003.jpg</td>\n",
       "      <td>jakarta/img90_0003.jpg</td>\n",
       "      <td>jakarta/img180_0003.jpg</td>\n",
       "      <td>jakarta/img270_0003.jpg</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>17</td>\n",
       "      <td>10522098</td>\n",
       "      <td>Jalan Lenteng Agung Raya</td>\n",
       "      <td>trunk</td>\n",
       "      <td>-6.312436</td>\n",
       "      <td>106.838374</td>\n",
       "      <td>-6.316894</td>\n",
       "      <td>106.837624</td>\n",
       "      <td>{u'date': u'2017-01', u'status': u'OK', u'loca...</td>\n",
       "      <td>© 2017 Google</td>\n",
       "      <td>2017-01</td>\n",
       "      <td>{u'lat': -6.31243889595, u'lng': 106.838416343}</td>\n",
       "      <td>mKRSPlWntlYFbMlq0rXvPA</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0004.jpg</td>\n",
       "      <td>jakarta/img90_0004.jpg</td>\n",
       "      <td>jakarta/img180_0004.jpg</td>\n",
       "      <td>jakarta/img270_0004.jpg</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   id  segment_id    osm_id                  osm_name  osm_type  start_lat  \\\n",
       "0   0           0   4705034                       NaN   primary  -6.180847   \n",
       "1   1           2   4705043   Jalan Gunung Sahari III  tertiary  -6.167753   \n",
       "2   2           9   4705053  Jalan Gunung Sahari Raya   primary  -6.156200   \n",
       "3   3          11   4705053  Jalan Gunung Sahari Raya   primary  -6.164970   \n",
       "4   4          17  10522098  Jalan Lenteng Agung Raya     trunk  -6.312436   \n",
       "\n",
       "   start_long   end_lat    end_long  \\\n",
       "0  106.822621 -6.180454  106.822612   \n",
       "1  106.842434 -6.167984  106.839682   \n",
       "2  106.836681 -6.160577  106.837808   \n",
       "3  106.838870 -6.169344  106.840010   \n",
       "4  106.838374 -6.316894  106.837624   \n",
       "\n",
       "                                        gsv_metadata      copyright     date  \\\n",
       "0  {u'date': u'2017-01', u'status': u'OK', u'loca...  © 2017 Google  2017-01   \n",
       "1  {u'date': u'2017-01', u'status': u'OK', u'loca...  © 2017 Google  2017-01   \n",
       "2  {u'date': u'2017-01', u'status': u'OK', u'loca...  © 2017 Google  2017-01   \n",
       "3  {u'date': u'2017-01', u'status': u'OK', u'loca...  © 2017 Google  2017-01   \n",
       "4  {u'date': u'2017-01', u'status': u'OK', u'loca...  © 2017 Google  2017-01   \n",
       "\n",
       "                                          location                 pano_id  \\\n",
       "0        {u'lat': -6.1807921, u'lng': 106.8226687}  ZsuaCXoZMzBUiimX6wPgOg   \n",
       "1  {u'lat': -6.16773708251, u'lng': 106.842439482}  IZDY6K-68eEZ_2Gn0UX7GQ   \n",
       "2  {u'lat': -6.15615671549, u'lng': 106.836705101}  efaqkGpxjxArkRbc2_8Ezw   \n",
       "3        {u'lat': -6.1650009, u'lng': 106.8389159}  TOfUJy6Rmv1imEwasCct6w   \n",
       "4  {u'lat': -6.31243889595, u'lng': 106.838416343}  mKRSPlWntlYFbMlq0rXvPA   \n",
       "\n",
       "  status               url_img0               url_img90  \\\n",
       "0     OK  jakarta/img0_0000.jpg  jakarta/img90_0000.jpg   \n",
       "1     OK  jakarta/img0_0001.jpg  jakarta/img90_0001.jpg   \n",
       "2     OK  jakarta/img0_0002.jpg  jakarta/img90_0002.jpg   \n",
       "3     OK  jakarta/img0_0003.jpg  jakarta/img90_0003.jpg   \n",
       "4     OK  jakarta/img0_0004.jpg  jakarta/img90_0004.jpg   \n",
       "\n",
       "                url_img180               url_img270  \n",
       "0  jakarta/img180_0000.jpg  jakarta/img270_0000.jpg  \n",
       "1  jakarta/img180_0001.jpg  jakarta/img270_0001.jpg  \n",
       "2  jakarta/img180_0002.jpg  jakarta/img270_0002.jpg  \n",
       "3  jakarta/img180_0003.jpg  jakarta/img270_0003.jpg  \n",
       "4  jakarta/img180_0004.jpg  jakarta/img270_0004.jpg  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jakarta.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>HITId</th>\n",
       "      <th>HITTypeId</th>\n",
       "      <th>Title</th>\n",
       "      <th>Description</th>\n",
       "      <th>Keywords</th>\n",
       "      <th>Reward</th>\n",
       "      <th>CreationTime</th>\n",
       "      <th>MaxAssignments</th>\n",
       "      <th>RequesterAnnotation</th>\n",
       "      <th>AssignmentDurationInSeconds</th>\n",
       "      <th>...</th>\n",
       "      <th>Input.status</th>\n",
       "      <th>Input.url_img0</th>\n",
       "      <th>Input.url_img90</th>\n",
       "      <th>Input.url_img180</th>\n",
       "      <th>Input.url_img270</th>\n",
       "      <th>Answer.Do you see any of the following?</th>\n",
       "      <th>Answer.Does the road have any...?</th>\n",
       "      <th>Answer.If there are any road markings are they clear?</th>\n",
       "      <th>Approve</th>\n",
       "      <th>Reject</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3ZFRE2BDQ9J4YG5PF15PQ4Q06UWZXZ</td>\n",
       "      <td>35EE6WR8LG7XDWM9J5XGORMSQW571C</td>\n",
       "      <td>3 questions on condition of the road in the image</td>\n",
       "      <td>3 simple yes/no and checkbox questions per image</td>\n",
       "      <td>image, tag, picture, tagging, photo, multiple ...</td>\n",
       "      <td>$0.05</td>\n",
       "      <td>Wed Jun 13 22:27:53 PDT 2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BatchId:3268555;OriginalHitTemplateId:862366346;</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0000.jpg</td>\n",
       "      <td>jakarta/img90_0000.jpg</td>\n",
       "      <td>jakarta/img180_0000.jpg</td>\n",
       "      <td>jakarta/img270_0000.jpg</td>\n",
       "      <td>Litter|A paved sidewalk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3WJGKMRWVIEDSHPYWNK5LZMNHUYCDB</td>\n",
       "      <td>35EE6WR8LG7XDWM9J5XGORMSQW571C</td>\n",
       "      <td>3 questions on condition of the road in the image</td>\n",
       "      <td>3 simple yes/no and checkbox questions per image</td>\n",
       "      <td>image, tag, picture, tagging, photo, multiple ...</td>\n",
       "      <td>$0.05</td>\n",
       "      <td>Wed Jun 13 22:27:38 PDT 2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BatchId:3268555;OriginalHitTemplateId:862366346;</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0606.jpg</td>\n",
       "      <td>jakarta/img90_0606.jpg</td>\n",
       "      <td>jakarta/img180_0606.jpg</td>\n",
       "      <td>jakarta/img270_0606.jpg</td>\n",
       "      <td>A paved sidewalk</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3FW4EL5A3LTDRPZG25BID3MX8CX22K</td>\n",
       "      <td>35EE6WR8LG7XDWM9J5XGORMSQW571C</td>\n",
       "      <td>3 questions on condition of the road in the image</td>\n",
       "      <td>3 simple yes/no and checkbox questions per image</td>\n",
       "      <td>image, tag, picture, tagging, photo, multiple ...</td>\n",
       "      <td>$0.05</td>\n",
       "      <td>Wed Jun 13 22:27:52 PDT 2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BatchId:3268555;OriginalHitTemplateId:862366346;</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0201.jpg</td>\n",
       "      <td>jakarta/img90_0201.jpg</td>\n",
       "      <td>jakarta/img180_0201.jpg</td>\n",
       "      <td>jakarta/img270_0201.jpg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3ODOP6T3ASPNU3E36JU66IJVSQT24G</td>\n",
       "      <td>35EE6WR8LG7XDWM9J5XGORMSQW571C</td>\n",
       "      <td>3 questions on condition of the road in the image</td>\n",
       "      <td>3 simple yes/no and checkbox questions per image</td>\n",
       "      <td>image, tag, picture, tagging, photo, multiple ...</td>\n",
       "      <td>$0.05</td>\n",
       "      <td>Wed Jun 13 22:27:53 PDT 2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BatchId:3268555;OriginalHitTemplateId:862366346;</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0001.jpg</td>\n",
       "      <td>jakarta/img90_0001.jpg</td>\n",
       "      <td>jakarta/img180_0001.jpg</td>\n",
       "      <td>jakarta/img270_0001.jpg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>3V7ICJJAZALHIGEZPCNH7BMZMW6B4R</td>\n",
       "      <td>35EE6WR8LG7XDWM9J5XGORMSQW571C</td>\n",
       "      <td>3 questions on condition of the road in the image</td>\n",
       "      <td>3 simple yes/no and checkbox questions per image</td>\n",
       "      <td>image, tag, picture, tagging, photo, multiple ...</td>\n",
       "      <td>$0.05</td>\n",
       "      <td>Wed Jun 13 22:28:09 PDT 2018</td>\n",
       "      <td>1</td>\n",
       "      <td>BatchId:3268555;OriginalHitTemplateId:862366346;</td>\n",
       "      <td>600</td>\n",
       "      <td>...</td>\n",
       "      <td>OK</td>\n",
       "      <td>jakarta/img0_0813.jpg</td>\n",
       "      <td>jakarta/img90_0813.jpg</td>\n",
       "      <td>jakarta/img180_0813.jpg</td>\n",
       "      <td>jakarta/img270_0813.jpg</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Cracks|Potholes</td>\n",
       "      <td>No</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>5 rows × 51 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                            HITId                       HITTypeId  \\\n",
       "0  3ZFRE2BDQ9J4YG5PF15PQ4Q06UWZXZ  35EE6WR8LG7XDWM9J5XGORMSQW571C   \n",
       "1  3WJGKMRWVIEDSHPYWNK5LZMNHUYCDB  35EE6WR8LG7XDWM9J5XGORMSQW571C   \n",
       "2  3FW4EL5A3LTDRPZG25BID3MX8CX22K  35EE6WR8LG7XDWM9J5XGORMSQW571C   \n",
       "3  3ODOP6T3ASPNU3E36JU66IJVSQT24G  35EE6WR8LG7XDWM9J5XGORMSQW571C   \n",
       "4  3V7ICJJAZALHIGEZPCNH7BMZMW6B4R  35EE6WR8LG7XDWM9J5XGORMSQW571C   \n",
       "\n",
       "                                               Title  \\\n",
       "0  3 questions on condition of the road in the image   \n",
       "1  3 questions on condition of the road in the image   \n",
       "2  3 questions on condition of the road in the image   \n",
       "3  3 questions on condition of the road in the image   \n",
       "4  3 questions on condition of the road in the image   \n",
       "\n",
       "                                        Description  \\\n",
       "0  3 simple yes/no and checkbox questions per image   \n",
       "1  3 simple yes/no and checkbox questions per image   \n",
       "2  3 simple yes/no and checkbox questions per image   \n",
       "3  3 simple yes/no and checkbox questions per image   \n",
       "4  3 simple yes/no and checkbox questions per image   \n",
       "\n",
       "                                            Keywords  Reward  \\\n",
       "0  image, tag, picture, tagging, photo, multiple ...  $0.05    \n",
       "1  image, tag, picture, tagging, photo, multiple ...  $0.05    \n",
       "2  image, tag, picture, tagging, photo, multiple ...  $0.05    \n",
       "3  image, tag, picture, tagging, photo, multiple ...  $0.05    \n",
       "4  image, tag, picture, tagging, photo, multiple ...  $0.05    \n",
       "\n",
       "                   CreationTime  MaxAssignments  \\\n",
       "0  Wed Jun 13 22:27:53 PDT 2018               1   \n",
       "1  Wed Jun 13 22:27:38 PDT 2018               1   \n",
       "2  Wed Jun 13 22:27:52 PDT 2018               1   \n",
       "3  Wed Jun 13 22:27:53 PDT 2018               1   \n",
       "4  Wed Jun 13 22:28:09 PDT 2018               1   \n",
       "\n",
       "                                RequesterAnnotation  \\\n",
       "0  BatchId:3268555;OriginalHitTemplateId:862366346;   \n",
       "1  BatchId:3268555;OriginalHitTemplateId:862366346;   \n",
       "2  BatchId:3268555;OriginalHitTemplateId:862366346;   \n",
       "3  BatchId:3268555;OriginalHitTemplateId:862366346;   \n",
       "4  BatchId:3268555;OriginalHitTemplateId:862366346;   \n",
       "\n",
       "   AssignmentDurationInSeconds  ...    Input.status         Input.url_img0  \\\n",
       "0                          600  ...              OK  jakarta/img0_0000.jpg   \n",
       "1                          600  ...              OK  jakarta/img0_0606.jpg   \n",
       "2                          600  ...              OK  jakarta/img0_0201.jpg   \n",
       "3                          600  ...              OK  jakarta/img0_0001.jpg   \n",
       "4                          600  ...              OK  jakarta/img0_0813.jpg   \n",
       "\n",
       "          Input.url_img90         Input.url_img180         Input.url_img270  \\\n",
       "0  jakarta/img90_0000.jpg  jakarta/img180_0000.jpg  jakarta/img270_0000.jpg   \n",
       "1  jakarta/img90_0606.jpg  jakarta/img180_0606.jpg  jakarta/img270_0606.jpg   \n",
       "2  jakarta/img90_0201.jpg  jakarta/img180_0201.jpg  jakarta/img270_0201.jpg   \n",
       "3  jakarta/img90_0001.jpg  jakarta/img180_0001.jpg  jakarta/img270_0001.jpg   \n",
       "4  jakarta/img90_0813.jpg  jakarta/img180_0813.jpg  jakarta/img270_0813.jpg   \n",
       "\n",
       "  Answer.Do you see any of the following? Answer.Does the road have any...?  \\\n",
       "0                 Litter|A paved sidewalk                               NaN   \n",
       "1                        A paved sidewalk                               NaN   \n",
       "2                                     NaN                               NaN   \n",
       "3                                     NaN                               NaN   \n",
       "4                                     NaN                   Cracks|Potholes   \n",
       "\n",
       "  Answer.If there are any road markings are they clear? Approve Reject  \n",
       "0                                                 No        NaN    NaN  \n",
       "1                                                Yes        NaN    NaN  \n",
       "2                                                 No        NaN    NaN  \n",
       "3                                                 No        NaN    NaN  \n",
       "4                                                 No        NaN    NaN  \n",
       "\n",
       "[5 rows x 51 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "jakarta_mturk.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Number of rows in the original Jakarta Mturk data: 978\n",
      "After removing rejected HITs: 978\n",
      "Number of rows in the original Lagos Mturk data: 872\n",
      "After removing rejected HITs: 872\n",
      "Number of rows in the original Bangkok Mturk data: 999\n",
      "After removing rejected HITs: 999\n",
      "Number of rows in the original Wayne Mturk data: 4828\n",
      "After removing rejected HITs: 4828\n"
     ]
    }
   ],
   "source": [
    "# Remove HITs that were rejected from the MTurk files\n",
    "print(\"Number of rows in the original Jakarta Mturk data: \" + str(jakarta_mturk.shape[0]))\n",
    "jakarta_mturk = jakarta_mturk.loc[jakarta_mturk['AssignmentStatus'] != \"Rejected\"]\n",
    "print(\"After removing rejected HITs: \" + str(jakarta_mturk.shape[0]))\n",
    "\n",
    "print(\"Number of rows in the original Lagos Mturk data: \" + str(lagos_mturk.shape[0]))\n",
    "lagos_mturk = lagos_mturk.loc[lagos_mturk['AssignmentStatus'] != \"Rejected\"]\n",
    "print(\"After removing rejected HITs: \" + str(lagos_mturk.shape[0]))\n",
    "\n",
    "print(\"Number of rows in the original Bangkok Mturk data: \" + str(bangkok_mturk.shape[0]))\n",
    "bangkok_mturk = bangkok_mturk.loc[bangkok_mturk['AssignmentStatus'] != \"Rejected\"]\n",
    "print(\"After removing rejected HITs: \" + str(bangkok_mturk.shape[0]))\n",
    "\n",
    "print(\"Number of rows in the original Wayne Mturk data: \" + str(wayne_mturk.shape[0]))\n",
    "wayne_mturk = wayne_mturk.loc[wayne_mturk['AssignmentStatus'] != \"Rejected\"]\n",
    "print(\"After removing rejected HITs: \" + str(wayne_mturk.shape[0]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Left join original data with Mturk\n",
    "jakarta_all = jakarta.merge(jakarta_mturk, how = 'left', left_on = 'url_img0', right_on = 'Input.url_img0')\n",
    "lagos_all   = lagos.merge(lagos_mturk, how = 'left', left_on = 'url_img0', right_on = 'Input.url_img0')\n",
    "bangkok_all = bangkok.merge(bangkok_mturk, how = 'left', left_on = 'url_img0', right_on = 'Input.url_img0')\n",
    "wayne_all   = wayne.merge(wayne_mturk, how = 'left', left_on = 'url_img0', right_on = 'Input.url_img0')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "978\n",
      "872\n",
      "999\n",
      "4828\n"
     ]
    }
   ],
   "source": [
    "# Number of rows in the final data (confirming)\n",
    "print(jakarta_all.shape[0])\n",
    "print(lagos_all.shape[0])\n",
    "print(bangkok_all.shape[0])\n",
    "print(wayne_all.shape[0])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Let's subset the data to relevant cols\n",
    "small_subset = ('osm_type',\n",
    "                'date',\n",
    "                'Answer.Do you see any of the following?',\n",
    "                'Answer.Does the road have any...?',\n",
    "                'Answer.If there are any road markings are they clear?')\n",
    "jakarta_small = jakarta_all.loc[:, small_subset]\n",
    "lagos_small   = lagos_all.loc[:, small_subset]\n",
    "bangkok_small = bangkok_all.loc[:, small_subset]\n",
    "wayne_small   = wayne_all.loc[:, small_subset]\n",
    "\n",
    "# Add a column enumerating city\n",
    "jakarta_small['city'] = 'jakarta'\n",
    "lagos_small['city']   = 'lagos'\n",
    "bangkok_small['city'] = 'bangkok'\n",
    "wayne_small['city']   = 'wayne'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Recoding for convenience and clarity\n",
    "\n",
    "# Let's tally proportion of roads with potholes and cracks\n",
    "jakarta_small['cracks'] = jakarta_small['Answer.Does the road have any...?'].str.contains('Cracks', na = False)\n",
    "lagos_small['cracks']   = lagos_small['Answer.Does the road have any...?'].str.contains('Cracks', na = False)\n",
    "bangkok_small['cracks'] = bangkok_small['Answer.Does the road have any...?'].str.contains('Cracks', na = False)\n",
    "wayne_small['cracks']   = wayne_small['Answer.Does the road have any...?'].str.contains('Cracks', na = False)\n",
    "\n",
    "jakarta_small['potholes'] = jakarta_small['Answer.Does the road have any...?'].str.contains('Potholes', na = False)\n",
    "lagos_small['potholes']   = lagos_small['Answer.Does the road have any...?'].str.contains('Potholes', na = False)\n",
    "bangkok_small['potholes'] = bangkok_small['Answer.Does the road have any...?'].str.contains('Potholes', na = False)\n",
    "wayne_small['potholes']   = wayne_small['Answer.Does the road have any...?'].str.contains('Potholes', na = False)\n",
    "\n",
    "# Road Markings\n",
    "jakarta_small['clear_road_markings'] = jakarta_small['Answer.If there are any road markings are they clear?'] == 'Yes'\n",
    "lagos_small['clear_road_markings']   = lagos_small['Answer.If there are any road markings are they clear?'] == 'Yes'\n",
    "bangkok_small['clear_road_markings'] = bangkok_small['Answer.If there are any road markings are they clear?'] == 'Yes'\n",
    "wayne_small['clear_road_markings']   = wayne_small['Answer.If there are any road markings are they clear?'] == 'Yes'\n",
    "\n",
    "jakarta_small['roads_w_markings']    = jakarta_small['Answer.If there are any road markings are they clear?'].str.contains('Yes|No', na = False)\n",
    "lagos_small['roads_w_markings']      = lagos_small['Answer.If there are any road markings are they clear?'].str.contains('Yes|No', na = False)\n",
    "bangkok_small['roads_w_markings']    = bangkok_small['Answer.If there are any road markings are they clear?'].str.contains('Yes|No', na = False)\n",
    "wayne_small['roads_w_markings']      = wayne_small['Answer.If there are any road markings are they clear?'].str.contains('Yes|No', na = False)\n",
    "\n",
    "# Questions about litter on the street and whether the sidewalk was paved was not asked for Jakarta\n",
    "jakarta_small['litter'] = jakarta_small['Answer.Do you see any of the following?'].str.contains('Litter', na = False)\n",
    "lagos_small['litter']   = lagos_small['Answer.Do you see any of the following?'].str.contains('Litter', na = False)\n",
    "bangkok_small['litter'] = bangkok_small['Answer.Do you see any of the following?'].str.contains('Litter', na = False)\n",
    "wayne_small['litter']   = wayne_small['Answer.Do you see any of the following?'].str.contains('Litter', na = False)\n",
    "\n",
    "jakarta_small['paved_sidewalk'] = jakarta_small['Answer.Do you see any of the following?'].str.contains('sidewalk', na = False)\n",
    "lagos_small['paved_sidewalk']   = lagos_small['Answer.Do you see any of the following?'].str.contains('sidewalk', na = False)\n",
    "bangkok_small['paved_sidewalk'] = bangkok_small['Answer.Do you see any of the following?'].str.contains('sidewalk', na = False)\n",
    "wayne_small['paved_sidewalk']   = wayne_small['Answer.Do you see any of the following?'].str.contains('sidewalk', na = False)\n",
    "\n",
    "# Let's subset on relevant cols.\n",
    "fin_cols = ('city', 'osm_type', 'date', 'potholes', 'cracks', 'clear_road_markings', 'roads_w_markings', 'litter', 'paved_sidewalk')\n",
    "jakarta_final = jakarta_small.loc[:, fin_cols]\n",
    "lagos_final   = lagos_small.loc[:, fin_cols]\n",
    "bangkok_final = bangkok_small.loc[:, fin_cols]\n",
    "wayne_final   = wayne_small.loc[:, fin_cols]\n",
    "\n",
    "# Concatenate\n",
    "all_cities = pd.concat([jakarta_final, lagos_final, bangkok_final, wayne_final])\n",
    "# Coerce data types\n",
    "all_cities[['litter','paved_sidewalk']] = all_cities[['litter','paved_sidewalk']].apply(pd.to_numeric)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>min</th>\n",
       "      <th>max</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bangkok</th>\n",
       "      <td>1970-08-01</td>\n",
       "      <td>2016-12-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jakarta</th>\n",
       "      <td>2013-02-01</td>\n",
       "      <td>2017-07-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lagos</th>\n",
       "      <td>2016-10-01</td>\n",
       "      <td>2017-04-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wayne</th>\n",
       "      <td>2007-07-01</td>\n",
       "      <td>2017-06-01</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "               min        max\n",
       "city                         \n",
       "bangkok 1970-08-01 2016-12-01\n",
       "jakarta 2013-02-01 2017-07-01\n",
       "lagos   2016-10-01 2017-04-01\n",
       "wayne   2007-07-01 2017-06-01"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "from datetime import datetime\n",
    "all_cities['date'] = pd.to_datetime(all_cities['date'])\n",
    "all_cities.groupby(['city'])['date'].agg(['min', 'max'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "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>potholes</th>\n",
       "      <th>cracks</th>\n",
       "      <th>clear_road_markings</th>\n",
       "      <th>roads_w_markings</th>\n",
       "      <th>litter</th>\n",
       "      <th>paved_sidewalk</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>bangkok</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.51</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>jakarta</th>\n",
       "      <td>0.23</td>\n",
       "      <td>0.44</td>\n",
       "      <td>0.34</td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.21</td>\n",
       "      <td>0.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>lagos</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>wayne</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.60</td>\n",
       "      <td>0.90</td>\n",
       "      <td>0.09</td>\n",
       "      <td>0.67</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         potholes  cracks  clear_road_markings  roads_w_markings  litter  \\\n",
       "city                                                                       \n",
       "bangkok      0.06    0.24                 0.81              0.98    0.06   \n",
       "jakarta      0.23    0.44                 0.34              0.97    0.21   \n",
       "lagos        0.06    0.20                 0.23              0.95    0.15   \n",
       "wayne        0.07    0.62                 0.60              0.90    0.09   \n",
       "\n",
       "         paved_sidewalk  \n",
       "city                     \n",
       "bangkok            0.51  \n",
       "jakarta            0.49  \n",
       "lagos              0.30  \n",
       "wayne              0.67  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Let's do some group by means\n",
    "all_cities.groupby(['city']).mean().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "city\n",
       "bangkok    0.83\n",
       "jakarta    0.35\n",
       "lagos      0.25\n",
       "wayne      0.66\n",
       "dtype: float64"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Proportion of road segments with markings that are clear\n",
    "(all_cities.groupby(['city'])['clear_road_markings'].sum()/all_cities.groupby(['city'])['roads_w_markings'].sum()).round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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></th>\n",
       "      <th>potholes</th>\n",
       "      <th>cracks</th>\n",
       "      <th>clear_road_markings</th>\n",
       "      <th>roads_w_markings</th>\n",
       "      <th>litter</th>\n",
       "      <th>paved_sidewalk</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>city</th>\n",
       "      <th>osm_type</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">bangkok</th>\n",
       "      <th>primary</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.83</td>\n",
       "      <td>0.99</td>\n",
       "      <td>0.04</td>\n",
       "      <td>0.50</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>secondary</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.24</td>\n",
       "      <td>0.81</td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.57</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tertiary</th>\n",
       "      <td>0.05</td>\n",
       "      <td>0.27</td>\n",
       "      <td>0.76</td>\n",
       "      <td>0.96</td>\n",
       "      <td>0.10</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trunk</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.06</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.30</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">jakarta</th>\n",
       "      <th>primary</th>\n",
       "      <td>0.17</td>\n",
       "      <td>0.41</td>\n",
       "      <td>0.48</td>\n",
       "      <td>0.98</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.55</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>secondary</th>\n",
       "      <td>0.25</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.36</td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.22</td>\n",
       "      <td>0.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tertiary</th>\n",
       "      <td>0.26</td>\n",
       "      <td>0.47</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.25</td>\n",
       "      <td>0.46</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trunk</th>\n",
       "      <td>0.18</td>\n",
       "      <td>0.38</td>\n",
       "      <td>0.45</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">lagos</th>\n",
       "      <th>primary</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.18</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>secondary</th>\n",
       "      <td>0.06</td>\n",
       "      <td>0.17</td>\n",
       "      <td>0.28</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.35</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tertiary</th>\n",
       "      <td>0.10</td>\n",
       "      <td>0.20</td>\n",
       "      <td>0.13</td>\n",
       "      <td>0.95</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trunk</th>\n",
       "      <td>0.03</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.30</td>\n",
       "      <td>0.92</td>\n",
       "      <td>0.16</td>\n",
       "      <td>0.36</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th rowspan=\"4\" valign=\"top\">wayne</th>\n",
       "      <th>primary</th>\n",
       "      <td>0.05</td>\n",
       "      <td>0.58</td>\n",
       "      <td>0.69</td>\n",
       "      <td>0.92</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>secondary</th>\n",
       "      <td>0.07</td>\n",
       "      <td>0.62</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.89</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.69</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>tertiary</th>\n",
       "      <td>0.08</td>\n",
       "      <td>0.65</td>\n",
       "      <td>0.57</td>\n",
       "      <td>0.90</td>\n",
       "      <td>0.12</td>\n",
       "      <td>0.65</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>trunk</th>\n",
       "      <td>0.03</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.66</td>\n",
       "      <td>0.97</td>\n",
       "      <td>0.06</td>\n",
       "      <td>0.40</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   potholes  cracks  clear_road_markings  roads_w_markings  \\\n",
       "city    osm_type                                                             \n",
       "bangkok primary        0.07    0.25                 0.83              0.99   \n",
       "        secondary      0.07    0.24                 0.81              0.97   \n",
       "        tertiary       0.05    0.27                 0.76              0.96   \n",
       "        trunk          0.06    0.06                 1.00              1.00   \n",
       "jakarta primary        0.17    0.41                 0.48              0.98   \n",
       "        secondary      0.25    0.45                 0.36              0.97   \n",
       "        tertiary       0.26    0.47                 0.23              0.97   \n",
       "        trunk          0.18    0.38                 0.45              0.95   \n",
       "lagos   primary        0.06    0.28                 0.17              0.95   \n",
       "        secondary      0.06    0.17                 0.28              0.95   \n",
       "        tertiary       0.10    0.20                 0.13              0.95   \n",
       "        trunk          0.03    0.16                 0.30              0.92   \n",
       "wayne   primary        0.05    0.58                 0.69              0.92   \n",
       "        secondary      0.07    0.62                 0.57              0.89   \n",
       "        tertiary       0.08    0.65                 0.57              0.90   \n",
       "        trunk          0.03    0.66                 0.66              0.97   \n",
       "\n",
       "                   litter  paved_sidewalk  \n",
       "city    osm_type                           \n",
       "bangkok primary      0.04            0.50  \n",
       "        secondary    0.06            0.57  \n",
       "        tertiary     0.10            0.52  \n",
       "        trunk        0.00            0.30  \n",
       "jakarta primary      0.15            0.55  \n",
       "        secondary    0.22            0.52  \n",
       "        tertiary     0.25            0.46  \n",
       "        trunk        0.13            0.47  \n",
       "lagos   primary      0.15            0.18  \n",
       "        secondary    0.15            0.35  \n",
       "        tertiary     0.15            0.28  \n",
       "        trunk        0.16            0.36  \n",
       "wayne   primary      0.08            0.69  \n",
       "        secondary    0.08            0.69  \n",
       "        tertiary     0.12            0.65  \n",
       "        trunk        0.06            0.40  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "all_cities.groupby(['city', 'osm_type']).mean().round(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Potholes:\n",
      "\n",
      "The coefficient for osm_type_primary is -0.03\n",
      "The coefficient for osm_type_secondary is -0.01\n",
      "The coefficient for osm_type_trunk is -0.05\n",
      "The coefficient for city_bangkok is -0.0\n",
      "The coefficient for city_jakarta is 0.16\n",
      "The coefficient for city_lagos is -0.0\n",
      "\n",
      "Cracks:\n",
      "\n",
      "The coefficient for osm_type_primary is -0.05\n",
      "The coefficient for osm_type_secondary is -0.03\n",
      "The coefficient for osm_type_trunk is -0.09\n",
      "The coefficient for city_bangkok is -0.37\n",
      "The coefficient for city_jakarta is -0.17\n",
      "The coefficient for city_lagos is -0.41\n"
     ]
    }
   ],
   "source": [
    "from sklearn.linear_model import LinearRegression\n",
    "\n",
    "X_train = all_cities.loc[:, ('osm_type', 'city')]\n",
    "y_potholes = all_cities[['potholes']]\n",
    "y_cracks = all_cities[['cracks']]\n",
    "\n",
    "X_train_fin = pd.get_dummies(X_train)\n",
    "X_train_finn = X_train_fin.loc[:, ('osm_type_primary', 'osm_type_secondary', 'osm_type_trunk', 'city_bangkok', 'city_jakarta', 'city_lagos')]\n",
    "\n",
    "print(\"Potholes:\\n\")\n",
    "lm = LinearRegression()\n",
    "lm.fit(X_train_finn, y_potholes)\n",
    "\n",
    "for idx, col_name in enumerate(X_train_finn.columns):\n",
    "    print(\"The coefficient for {} is {}\".format(col_name, lm.coef_[0][idx].round(2)))\n",
    "\n",
    "print(\"\\nCracks:\\n\")\n",
    "lm = LinearRegression()\n",
    "lm.fit(X_train_finn, y_cracks)\n",
    "\n",
    "for idx, col_name in enumerate(X_train_finn.columns):\n",
    "    print(\"The coefficient for {} is {}\".format(col_name, lm.coef_[0][idx].round(2)))"
   ]
  },
  {
   "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.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}