pandas-real-data.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "0",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Data Exploration with Python and Jupyter\n",
    "\n",
    "Basic usage of the Pandas library to download a dataset,\n",
    "explore its contents, clean up missing or invalid data,\n",
    "filter the data according to different criteria,\n",
    "and plot visualizations of the data.\n",
    "\n",
    "- [Part 1: Python and Jupyter](https://ssciwr.github.io/jupyter-data-exploration)\n",
    "- [Part 2: Pandas with toy data](https://ssciwr.github.io/jupyter-data-exploration/pandas-toy-data.slides.html)\n",
    "- **Part 3: Pandas with real data**\n",
    "\n",
    "*Press `Spacebar` to go to the next slide (or `?` to see all navigation shortcuts)*"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Let's download some real data\n",
    "\n",
    "For some reason, the London Fire Brigade provides a public spreadsheet of all animal rescue incidents since 2009:\n",
    "\n",
    "https://data.london.gov.uk/dataset/animal-rescue-incidents-attended-by-lfb\n",
    "\n",
    "They provide a link to the dataset in excel format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "2",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# import the Pandas library & matplotlib for plotting\n",
    "\n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "3",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# download an excel spreadsheet with some data and convert it to a DataFrame\n",
    "url = \"https://data.london.gov.uk/download/animal-rescue-incidents-attended-by-lfb/01007433-55c2-4b8a-b799-626d9e3bc284/Animal%20Rescue%20incidents%20attended%20by%20LFB%20from%20Jan%202009.csv.xlsx\"\n",
    "df = pd.read_excel(url)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "4",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "## Suggested workflow / philosophy\n",
    "\n",
    "- you want to do something\n",
    "  - if you know / have a guess which function to use, look at its docstring: `?function_name`\n",
    "  - if you don't have any idea what to try, google `how do I ... in pandas`\n",
    "  - if in doubt, just try something!\n",
    "- if you get an error, copy & paste the last bit into google (along with `funtion_name` and/or `pandas`)\n",
    "  - don't be intimidated by the long and apparently nonsensical error messages\n",
    "  - almost certainly someone else has had this exact problem\n",
    "  - almost certainly the solution is waiting for you\n",
    "- look for a stackoverflow answer with many up-votes\n",
    "  - ignore the green tick, this just means the person asking the question liked the answer\n",
    "  - typically an answer with many up-votes is a better option\n",
    "  - more recent answers can also be better: sometimes a library has changed since an older answer was written\n",
    "\n",
    "(For anyone who wasn't already doing this, that may be the most useful thing in this course)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Display the DataFrame"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "6",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "source": [
    "# Column data types"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df.dtypes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "9",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Convert DateTimeOfCall to a date-time"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "10",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df[\"DateTimeOfCall\"].head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "11",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# this looks like what we want..\n",
    "pd.to_datetime(df[\"DateTimeOfCall\"]).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "12",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# ..but which number is the month and which is the day?\n",
    "# how can we check if what we just did was correct?\n",
    "pd.to_datetime(df[\"DateTimeOfCall\"]).plot()\n",
    "# should be a single monotonically increasing line: looks good!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "13",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# replace DateTimeOfCall column in dataframe with this one\n",
    "df[\"DateTimeOfCall\"] = pd.to_datetime(df[\"DateTimeOfCall\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "14",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Use the datetime as the index"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "15",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df.set_index(\"DateTimeOfCall\", inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "16",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "17",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# can now use datetime to select rows: here is jan 2021\n",
    "df.loc[\"2021-01-01\":\"2021-01-31\", \"FinalDescription\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "18",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# resample the timeseries by month and count incidents\n",
    "df.resample(\"ME\")[\"IncidentNumber\"].count().plot(title=\"Monthly Calls\")\n",
    "# see https://pandas.pydata.org/docs/user_guide/timeseries.html#timeseries-offset-aliases\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "19",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# resample by year, sum total costs, average hourly costs\n",
    "fig, axs = plt.subplots(figsize=(16, 4), ncols=2)\n",
    "df.resample(\"YE\")[\"IncidentNotionalCost(£)\"].sum().plot(\n",
    "    title=\"Year total cost\", ax=axs[0]\n",
    ")\n",
    "df.resample(\"YE\")[\"HourlyNotionalCost(£)\"].mean().plot(\n",
    "    title=\"Average hourly cost\", ax=axs[1]\n",
    ")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "20",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Missing data\n",
    "\n",
    "Different strategies for dealing with missing data:\n",
    "\n",
    "- Ignore the issue\n",
    "  - some things may break / not work as expected\n",
    "- Remove rows/columns with missing data\n",
    "  - remove all rows with missing data: `df.dropna(axis=0)`\n",
    "  - remove all columns with missing data: `df.dropna(axis=1)`\n",
    "- Guess (impute) missing data\n",
    "  - replace all missing entries with a value: `df.fillna(1)`\n",
    "  - replace missing entries with mean for that column `df.fillna(df.mean())`\n",
    "  - replace each missing entry with previous valid entry: `df.fillna(method=\"pad\")`\n",
    "  - replace missing by interpolating between valid entries: `df.interpolate()`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "21",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# count missing entries for each column\n",
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "22",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# If PumpCount is missing, typically so is PumpHoursTotal\n",
    "# 66 rows are missing at least one of these\n",
    "pump_missing = df[\"PumpCount\"].isna() | df[\"PumpHoursTotal\"].isna()\n",
    "print(pump_missing.sum())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "23",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# so we could choose to drop these rows\n",
    "df1 = df.drop(df.loc[pump_missing == True].index)\n",
    "# here we made a new dataset df1 with these rows dropped\n",
    "# to drop the rows from the original dataset df, could do:\n",
    "#\n",
    "# df = df.drop(df.loc[pump_missing == True].index)\n",
    "#\n",
    "# or:\n",
    "#\n",
    "# df.drop(df.loc[pump_missing == True].index, inplace=True)\n",
    "#\n",
    "print(len(df1))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "24",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# another equivalent way to do this\n",
    "df2 = df.dropna(subset=[\"PumpCount\", \"PumpHoursTotal\"])\n",
    "print(len(df2))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "25",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# but if we drop them, we lose valid data from other columns\n",
    "# let's look at the distribution of values:\n",
    "fig, axs = plt.subplots(1, 2, figsize=(14, 6))\n",
    "df.plot.hist(y=\"PumpCount\", ax=axs[0])\n",
    "df.plot.hist(y=\"PumpHoursTotal\", ax=axs[1])\n",
    "plt.plot()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "26",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# looks like it would be better to replace missing PumpCount and PumpHoursTotal fields with 1\n",
    "?df.fillna\n",
    "df.fillna({\"PumpCount\": 1, \"PumpHoursTotal\": 1}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "27",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.isna().sum()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "28",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Count the unique entries in each column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "29",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "df.nunique().sort_values()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "30",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# \"cat\" and \"Cat\" are treated as different animals here:\n",
    "df[\"AnimalGroupParent\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "31",
   "metadata": {
    "slideshow": {
     "slide_type": "fragment"
    }
   },
   "outputs": [],
   "source": [
    "# select rows where AnimalGroupParent is \"cat\", replace with \"Cat\"\n",
    "df.loc[df[\"AnimalGroupParent\"] == \"cat\", \"AnimalGroupParent\"] = \"Cat\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "32",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df[\"AnimalGroupParent\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "33",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "df.groupby(\"AnimalGroupParent\")[\"IncidentNumber\"].count().sort_values().plot.barh(\n",
    "    logx=True\n",
    ")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "34",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# apparently different hourly costs\n",
    "# does it depend on the type of event? or does it just increase over time?\n",
    "df[\"HourlyNotionalCost(£)\"].unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "35",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# just goes up over time\n",
    "df[\"HourlyNotionalCost(£)\"].plot.line()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "36",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# Group incidents by fire station & count them\n",
    "df.groupby(\"StnGroundName\")[\"IncidentNumber\"].count()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "37",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "## Plot location of calls on a map\n",
    "\n",
    "- note: this section uses some more libraries, to install them:\n",
    "- `pip install geopandas contextily`"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "38",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "# drop missing longitude/latitude\n",
    "df2 = df.dropna(subset=[\"Longitude\", \"Latitude\"])\n",
    "# also drop zero values\n",
    "df2 = df2[df2[\"Latitude\"] != 0]\n",
    "# convert to geodataframe using geopandas\n",
    "import geopandas\n",
    "\n",
    "# set crs to EPSG:4326 to specify WGS84 Latitude/Longitude\n",
    "gdf = geopandas.GeoDataFrame(\n",
    "    df2,\n",
    "    geometry=geopandas.points_from_xy(df2[\"Longitude\"], df2[\"Latitude\"]),\n",
    "    crs=\"EPSG:4326\",\n",
    ")\n",
    "gdf.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "39",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(figsize=(16, 16))\n",
    "# plot location of calls involving animals\n",
    "gdf.plot(ax=ax, color=\"black\", alpha=0.3)\n",
    "plt.title(\"Call locations\")\n",
    "# plt.axis(\"off\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "40",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "import contextily as cx\n",
    "\n",
    "f, ax = plt.subplots(figsize=(16, 16))\n",
    "# plot location of calls involving animals\n",
    "gdf.plot(ax=ax, color=\"black\", alpha=0.3)\n",
    "# add a basemap of the region using contextily\n",
    "cx.add_basemap(ax, crs=gdf.crs)\n",
    "plt.title(\"Call locations\")\n",
    "plt.axis(\"off\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "41",
   "metadata": {
    "slideshow": {
     "slide_type": "subslide"
    }
   },
   "outputs": [],
   "source": [
    "f, ax = plt.subplots(figsize=(16, 16))\n",
    "# plot location of calls involving animals\n",
    "for animal, colour in [\n",
    "    (\"Cow\", \"black\"),\n",
    "    (\"Deer\", \"red\"),\n",
    "    (\"Fox\", \"blue\"),\n",
    "    (\"Snake\", \"yellow\"),\n",
    "]:\n",
    "    gdf[gdf[\"AnimalGroupParent\"] == animal].plot(\n",
    "        ax=ax, color=colour, alpha=0.5, label=animal\n",
    "    )\n",
    "# add a basemap of the region using contextily\n",
    "cx.add_basemap(ax, crs=gdf.crs)\n",
    "plt.title(\"Call locations by animal\")\n",
    "plt.legend()\n",
    "plt.axis(\"off\")\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "42",
   "metadata": {
    "slideshow": {
     "slide_type": "slide"
    }
   },
   "source": [
    "# Next steps\n",
    "\n",
    "- experiment with your own datasets\n",
    "- read some pandas documentation\n",
    "  - [user guide](https://pandas.pydata.org/docs/user_guide/index.html)\n",
    "- follow a tutorial\n",
    "  - [getting started tutorials](https://pandas.pydata.org/docs/getting_started/intro_tutorials/index.html)\n",
    "- free interactive kaggle courses\n",
    "  - [pandas](https://www.kaggle.com/learn/pandas)\n",
    "  - [data cleaning](https://www.kaggle.com/learn/data-cleaning)"
   ]
  }
 ],
 "metadata": {
  "celltoolbar": "Slideshow",
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.12.0"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}