7. Visualizing Growth and Building Edits Over Time.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-09-13T22:50:10.706105Z",
     "start_time": "2019-09-13T22:50:09.326852Z"
    }
   },
   "outputs": [
    {
     "data": {
      "application/vnd.jupyter.widget-view+json": {
       "model_id": "62539d9ebbc14a1dadb98693eb59c95d",
       "version_major": 2,
       "version_minor": 0
      },
      "text/plain": [
       "HBox(children=(IntProgress(value=1, bar_style='info', max=1), HTML(value='')))"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n"
     ]
    }
   ],
   "source": [
    "from workshop_utils import * \n",
    "import pandas as pd\n",
    "import matplotlib.pyplot as plt\n",
    "import matplotlib.dates as mdates\n",
    "import seaborn as sns\n",
    "import datetime\n",
    "import geopandas as gpd\n",
    "import shapely\n",
    "%matplotlib inline\n",
    "%config InlineBackend.figure_format = 'retina'\n",
    "from tqdm import tqdm_notebook as tqdm\n",
    "tqdm().pandas();"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# First, get extract all of the building edits for an area associated with a previous HOT task.\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Getting the buildings from task https://tasks.hotosm.org/project/4690 (2018 Ebola)\n",
    "\n",
    "```sql\n",
    "SELECT\n",
    "  subsaharan_africa.id, \n",
    "  geom,\n",
    "  subsaharan_africa.tags, changeset, updated, valid_until, version, minor_version,\n",
    "  changesets.id AS c_id, \n",
    "  changesets.tags as c_tags,\n",
    "  changesets.uid,\n",
    "  changesets.user\n",
    "  \n",
    "FROM subsaharan_africa\n",
    "\n",
    "JOIN changesets on subsaharan_africa.changeset = changesets.id\n",
    "\n",
    "WHERE   \n",
    "  element_at(subsaharan_africa.tags, 'building') IS NOT NULL AND ST_WITHIN(\n",
    "  geom , \n",
    "  ST_POLYGON('POLYGON((17.8183 0.0461, 18.9637 0.0461, 18.9637 -0.9686, 17.8183 -0.9686, 17.8183 0.0461))'))\n",
    "\n",
    "ORDER BY updated\n",
    "```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:39:18.161330Z",
     "start_time": "2019-08-28T04:39:12.634791Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "building_edits = load_dataframe_from_s3('https://us-east-2.console.aws.amazon.com/athena/query/results/ba10a1bb-3347-4c40-a1ae-2f8ff63f344a/csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "place = \"Mbandaka - Bikoro\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:39:19.178890Z",
     "start_time": "2019-08-28T04:39:19.165205Z"
    },
    "scrolled": true
   },
   "outputs": [],
   "source": [
    "building_edits.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:40:51.676386Z",
     "start_time": "2019-08-28T04:40:49.501301Z"
    }
   },
   "outputs": [],
   "source": [
    "#Cast the date as date\n",
    "building_edits['updated'] = building_edits.updated.progress_apply(pd.Timestamp)\n",
    "building_edits['date'] = building_edits.updated.progress_apply(pd.Timestamp.date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:41:56.125260Z",
     "start_time": "2019-08-28T04:41:55.682108Z"
    }
   },
   "outputs": [],
   "source": [
    "gb_date = building_edits.groupby('date').aggregate({\n",
    "    'c_id':'count',\n",
    "    'id':'nunique',\n",
    "    'uid':'nunique'\n",
    "});\n",
    "gb_date.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:41:57.021874Z",
     "start_time": "2019-08-28T04:41:56.837793Z"
    },
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "sns.set_style('whitegrid')\n",
    "ax = gb_date['c_id'].plot(figsize=(14,4))\n",
    "ax.set_xlabel(\"Date\"); ax.set_ylabel(\"\");\n",
    "ax.set_title(\"Edits in {}\".format(place),fontsize=16);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-08-28T04:42:14.471947Z",
     "start_time": "2019-08-28T04:42:14.289916Z"
    }
   },
   "outputs": [],
   "source": [
    "sns.set_style('whitegrid')\n",
    "ax = gb_date['uid'].plot(style='.', figsize=(14,4))\n",
    "ax.set_xlabel(\"Date\"); ax.set_ylabel(\"\");\n",
    "ax.set_title(\"Unique editors active per day in {}\".format(place),fontsize=16);"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "from shapely import wkt"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "building_edits['geom'] = building_edits.geom.progress_apply(wkt.loads) #Makes the geometry an actual object..."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "objects = gpd.GeoDataFrame(building_edits, geometry='geom')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "objects['date'] = objects.updated.apply(lambda x: x.isoformat())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "scrolled": false
   },
   "outputs": [],
   "source": [
    "from keplergl import KeplerGl \n",
    "map_1 = KeplerGl(height=600)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "map_1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "map_1.add_data(data=objects[objects.updated > datetime.datetime(2018,1,1)][\n",
    "    ['geom','changeset','date','user','version','minor_version']], name='objects')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Visualization\n",
    "\n",
    "- Set layer blending to additive.\n",
    "- fill color based on minor_version\n",
    "\n",
    "Once you do this, buildings that glow have been edited the most.  If you zoom in on them, you can see their shapes change through each version!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "hide_input": false,
  "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.4"
  },
  "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
}