2 Exploratory Data Analysis.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Exploratory Data Analysis\n",
    "\n",
    "This notebook introduces you to exploratory data analysis using the [pandas](https://pandas.pydata.org) library. Pandas is not specific to geospatial data, so what we do here can be applied to pretty much any dataset you encounter.\n",
    "\n",
    "![pandas logo](https://pandas.pydata.org/pandas-docs/stable/_static/pandas.svg)\n",
    "\n",
    "As a quick intro, we reommend [10 minutes to pandas](https://pandas.pydata.org/docs/getting_started/10min.html#min). This notebook will point you to the relevant sections of the [pandas user guide](https://pandas.pydata.org/docs/user_guide/index.html), which is generally a great resource whenever you need documentation about a pandas feature.\n",
    "\n",
    "Let's get started by loading pandas:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Loading data\n",
    "\n",
    "Load some traffic accident data directly from the web ([NYC Crash Mapper](http://crashmapper.org/#/?cfat=true&cinj=true&endDate=2019-03&geo=citywide&identifier=&lngLats=%255B%255D&mfat=true&minj=true&noInjFat=false&pfat=true&pinj=true&startDate=2018-03)):"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "url=\"https://chekpeds.carto.com/api/v2/sql?q=SELECT%20c.cartodb_id%2C%20c.socrata_id%2C%20c.the_geom%2C%20c.on_street_name%2C%20c.cross_street_name%2C%20c.date_val%20AS%20date_time%2C%20c.latitude%2C%20c.longitude%2C%20c.borough%2C%20c.zip_code%2C%20c.crash_count%2C%20c.number_of_cyclist_injured%2C%20c.number_of_cyclist_killed%2C%20c.number_of_motorist_injured%2C%20c.number_of_motorist_killed%2C%20c.number_of_pedestrian_injured%2C%20c.number_of_pedestrian_killed%2C%20c.number_of_persons_injured%2C%20c.number_of_persons_killed%2C%20array_to_string(c.contributing_factor%2C%20%27%2C%27)%20as%20contributing_factors%2C%20array_to_string(c.vehicle_type%2C%20%27%2C%27)%20as%20vehicle_types%20FROM%20crashes_all_prod%20c%20%20WHERE%20(%20year%3A%3Atext%20%7C%7C%20LPAD(month%3A%3Atext%2C%202%2C%20%270%27)%20%3C%3D%20%272019%27%20%7C%7C%20LPAD(3%3A%3Atext%2C%202%2C%20%270%27)%20)%20AND%20(%20year%3A%3Atext%20%7C%7C%20LPAD(month%3A%3Atext%2C%202%2C%20%270%27)%20%3E%3D%20%272018%27%20%7C%7C%20LPAD(3%3A%3Atext%2C%202%2C%20%270%27)%20)%20%20AND%20(%20number_of_cyclist_injured%20%3E%200%20OR%20number_of_motorist_injured%20%3E%200%20OR%20number_of_pedestrian_injured%20%3E%200%20%20OR%20%20number_of_cyclist_killed%20%3E%200%20OR%20number_of_motorist_killed%20%3E%200%20OR%20number_of_pedestrian_killed%20%3E%200%20)&format=CSV\"\n",
    "accidents = pd.read_csv(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "This creates a [dataframe](https://pandas.pydata.org/pandas-docs/stable/getting_started/dsintro.html#dataframe), let's take a glimpse:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Loading a local file is just as easy:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents = pd.read_csv(\"accidents.csv\") # <- this is actually an older version of the same dataset we loaded from the web before\n",
    "accidents"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's check out the [data types](https://pandas.pydata.org/pandas-docs/stable/getting_started/basics.html#basics-dtypes) on our accidents data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Strings and the timestamp are stored as [objects](https://pandas.pydata.org/pandas-docs/stable/user_guide/categorical.html?highlight=object) – we'll fix that in a bit.\n",
    "\n",
    "Let's try to access parts of the accidents DataFrame. First, a single column. This will return a pandas [Series](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.Series.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents['vehicle_types']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Slicing rows – returns a new DataFrame:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[2:10] # rows 2 to 9 (!)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[2:]   # all rows from row 2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[:10]  # all rows up to row 9"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using [.loc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.loc.html)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.loc[3]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Using .loc to pick columns:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.loc[27:29, ['number_of_cyclist_injured', 'number_of_cyclist_killed']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Using [.iloc](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.iloc.html)\n",
    "\n",
    "... to get the same result. Notice how .loc and .iloc use different indexing – .loc is *inclusive* the last mentioned index (29 here), .iloc is *exlusive* (30 in this example). Here's a [good article](https://www.shanelynn.ie/select-pandas-dataframe-rows-and-columns-using-iloc-loc-and-ix/) comparing the different access methods."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.iloc[27:30, 11:13]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### DataFrame indexes\n",
    "\n",
    "Each dataframe has an [index](https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#indexing). Initially, that's just a number for each row:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get an overview of the columns in the dataset (again)..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.columns"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "... and index the dataset by timestamp of the accident instead:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.set_index(pd.to_datetime(accidents['date_time']), inplace=True)\n",
    "accidents.set_index(accidents.index.tz_localize(None), inplace=True)\n",
    "accidents.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now we can easily query the accidents by date range:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.loc['2019-01-01':'2019-02-01']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many accidents do we have per month of the year?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.resample('M').sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "..and per hour?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.resample('H').sum()  "
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How many accidents are there on average per hour of the day?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "hourly_accidents = accidents.resample('H').sum()\n",
    "hourly_accidents['hour'] = hourly_accidents.index.hour\n",
    "hourly_accidents.groupby(['hour']).mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's plot this:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# you only need this one once – all plots from here on will be embedded in the notebook\n",
    "%matplotlib inline \n",
    "\n",
    "mean_hourly_accidents = hourly_accidents.groupby(['hour']).mean()\n",
    "mean_hourly_accidents.plot(y='crash_count')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 🏋 Exercise\n",
    "\n",
    "**Which month of the year has the [highest number](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.max.html) of accidents?**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "# your code here..."
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Boolean indexing\n",
    "\n",
    "With [boolean indexing](https://www.geeksforgeeks.org/boolean-indexing-in-pandas/), we can select rows where certain criterea (or combinations thereof) are true. As an example, let's select all accidents where cyclists have been injured:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents['number_of_cyclist_injured'] > 0"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Filter the dataframe using this boolean series:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[accidents['number_of_cyclist_injured'] > 0]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Combine multiple citeria:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[(accidents['number_of_cyclist_injured'] > 0) | (accidents['number_of_cyclist_killed'] > 0)]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Query using string matching:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[accidents['vehicle_types'].str.contains('Taxi')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Ooops... Do we have null values in our ```vehicle_types``` column?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[accidents['vehicle_types'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's replace the NaN values with an empty string:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.vehicle_types.fillna('', inplace=True)\n",
    "accidents[accidents['vehicle_types'].isnull()]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Now this should work:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[accidents['vehicle_types'].str.contains('Taxi')]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Writing the filtered dataset to a CSV file:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "taxi_accidents = accidents[accidents['vehicle_types'].str.contains('Taxi')]\n",
    "taxi_accidents.to_csv('taxi_accidents.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Plotting and (simple) mapping"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.plot(x='longitude', \n",
    "               y='latitude', \n",
    "               kind='scatter')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "These accidents are all in NYC, so the dots at the top left and bottom right are clearly wrong coordinates. Let's remove them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents = accidents[ (accidents['latitude'] > 30) & (accidents['longitude'] > -100)  ]\n",
    "accidents.plot.scatter(x='longitude', \n",
    "               y='latitude')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Not very pretty, but it's a start. \n",
    "\n",
    "Let's make a fancy map now. See the docs for explanation of the [variables](https://pandas.pydata.org/pandas-docs/stable/reference/api/pandas.DataFrame.plot.html#pandas.DataFrame.plot) and [color scales](https://matplotlib.org/users/colormaps.html)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents[accidents['number_of_persons_injured'] > 0].plot.scatter(x='longitude', \n",
    "               y='latitude', \n",
    "               c='number_of_persons_injured',\n",
    "               s=0.7,\n",
    "               title='NYC accidents with injuries',                                                   \n",
    "               colormap='Wistia',\n",
    "               figsize=[14,14])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's take a look at hexbins to identify potential hotspots:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.plot.hexbin(x='longitude', \n",
    "               y='latitude',\n",
    "               figsize=[14,14])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's make a KDE map using [Seaborn](https://seaborn.pydata.org). If you don't know what KDE is, here is an excellent interactive tutortial explaning the idea of [Kernel Density Estimation](https://mathisonian.github.io/kde/)."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "import seaborn as sns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "plt.figure(figsize=(14, 14))\n",
    "sns.kdeplot(accidents['longitude'], \n",
    "            accidents['latitude'],\n",
    "            cmap='Reds',\n",
    "            shade=True)\n",
    "plt.savefig('kde.pdf') # saves the plot to a PDF file in the same folder as this notebook \n",
    "                       # – this command has to go in the same cell that produces the plot!"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Let's get an overview of the accidents dataset:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "accidents.describe()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "How does the distribution of monthly fatalities look like?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "monthly_accidents = accidents.resample('M').sum()\n",
    "\n",
    "plt.figure(figsize=(14, 5))\n",
    "sns.boxplot(monthly_accidents['number_of_persons_killed'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "🤔 How do you [read a box plot again](https://en.wikipedia.org/wiki/Box_plot#Visualization)? \n",
    "\n",
    "Let's make a distribution plot:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "plt.figure(figsize=(14, 8))\n",
    "sns.distplot(monthly_accidents['number_of_persons_killed'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Scatter plot with fitted linear regression line:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "sns.lmplot(x='number_of_persons_injured',y='number_of_persons_killed',data=monthly_accidents)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# 🏋🏼‍♀️ Exercise\n",
    "\n",
    "Use the things we have discussed plus the corresponding documentation to accomplish the following tasks:\n",
    "\n",
    "1. Read this file from the [Uppsala Conflict Data Program](https://ucdp.uu.se): http://ucdp.uu.se/downloads/ged/ged181-csv.zip\n",
    "2. Create some maps of the conflicts to identify clusters.\n",
    "3. Aggregate the data in different ways, e.g. by type of violence or region, and create summary statistics.\n",
    "\n",
    "🤓 Have fun!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  },
  {
   "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.7.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}