01_InputGenWorkPlay.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import geopandas as gpd\n",
    "\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>Age1</th>\n",
       "      <th>Age2</th>\n",
       "      <th>Age3</th>\n",
       "      <th>Age4</th>\n",
       "      <th>Age5</th>\n",
       "      <th>Age6</th>\n",
       "      <th>Age7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>95AA01S2</td>\n",
       "      <td>207</td>\n",
       "      <td>42</td>\n",
       "      <td>105</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>95AA01S3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>95AA02W1</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>95AA04W1</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       From        To  Age1  Age2  Age3  Age4  Age5  Age6  Age7\n",
       "0  95AA01S1  95AA01S1    15     1     6     7     1     0     0\n",
       "1  95AA01S1  95AA01S2   207    42   105    60     0     0     0\n",
       "2  95AA01S1  95AA01S3     3     0     2     1     0     0     0\n",
       "3  95AA01S1  95AA02W1     1     0     0     1     0     0     0\n",
       "4  95AA01S1  95AA04W1     5     1     2     2     0     0     0"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#Read in data - Unsure on age categories - so have just named them Age1 through Age7\n",
    "df=pd.read_csv(\"MSOAflows.csv\", header=None)\n",
    "df=df.rename(columns={0:\"From\", 1:\"To\", 2:\"Age1\", 3:\"Age2\",\n",
    "                  4:\"Age3\", 5:\"Age4\", 6:\"Age5\", 7:\"Age6\", 8:\"Age7\"})\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Shapefile merging, read from internet sources. Areas not included in flow data\n",
    "\n",
    "Outputs geom - full geometry shapefile for total UK countries. Length > dataset - unsure how you want shapefiles coded that aren't in the flow dataset - so have left them enumerated at the bottom, but they just don't have a mapping in the flows data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "EWMSOA=gpd.read_file('https://opendata.arcgis.com/datasets/02aa733fc3414b0ea4179899e499918d_0.zip?outSR=%7B%22latestWkid%22%3A27700%2C%22wkid%22%3A27700%7D')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "SIZ=gpd.read_file('http://sedsh127.sedsh.gov.uk/Atom_data/ScotGov/ZippedShapefiles/SG_IntermediateZoneBdry_2011.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "NISOA=gpd.read_file('https://www.nisra.gov.uk/sites/nisra.gov.uk/files/publications/SOA2011_Esri_Shapefile_0.zip')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "EWMSOA=EWMSOA.to_crs(\"EPSG:4326\")\n",
    "NISOA=NISOA.to_crs(\"EPSG:4326\")\n",
    "SIZ=SIZ.to_crs(\"EPSG:4326\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "EWMSOA.rename(columns={\"msoa11cd\":\"cd\"}, inplace=True)\n",
    "NISOA.rename(columns={\"SOA_CODE\":\"cd\"}, inplace=True)\n",
    "SIZ.rename(columns={\"InterZone\":\"cd\"}, inplace=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(9370, 2)"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geom=gpd.GeoDataFrame(EWMSOA[{\"cd\",\"geometry\"}])\n",
    "geom=geom.append(NISOA[{\"cd\",\"geometry\"}], ignore_index=True)\n",
    "geom=geom.append(SIZ[{\"cd\",\"geometry\"}], ignore_index=True)\n",
    "geom.shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Create Master lookup for True IDs (TID) including empty-cells and non UK destinations"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookup=pd.DataFrame({\"CD\":list(df.To.unique())})\n",
    "lookup=lookup.merge(geom, left_on=\"CD\", right_on=\"cd\", how=\"outer\")\n",
    "lookup.cd.fillna(lookup.CD, inplace=True)\n",
    "lookup['TID']=np.arange(10149)+1"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "lookupdict=dict(zip(lookup.cd, lookup.TID))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.From=df.From.map(lookupdict)\n",
    "df.To=df.To.map(lookupdict)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Output flows by age with correct IDs\n",
    "\n",
    "df.to_csv('ageflows.csv')"
   ]
  },
  {
   "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>From</th>\n",
       "      <th>To</th>\n",
       "      <th>Age1</th>\n",
       "      <th>Age2</th>\n",
       "      <th>Age3</th>\n",
       "      <th>Age4</th>\n",
       "      <th>Age5</th>\n",
       "      <th>Age6</th>\n",
       "      <th>Age7</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>15</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>7</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>207</td>\n",
       "      <td>42</td>\n",
       "      <td>105</td>\n",
       "      <td>60</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   From  To  Age1  Age2  Age3  Age4  Age5  Age6  Age7\n",
       "0     1   1    15     1     6     7     1     0     0\n",
       "1     1   2   207    42   105    60     0     0     0\n",
       "2     1   3     3     0     2     1     0     0     0\n",
       "3     1   4     1     0     0     1     0     0     0\n",
       "4     1   5     5     1     2     2     0     0     0"
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Output shapefiles with Correct IDs\n",
    "\n",
    "geom['ID']=geom.cd.map(lookupdict)\n",
    "geom=geom.sort_values(by=['ID'])\n",
    "geom.to_file(\"fullgeom.shp\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "## Generating Work and Play Files"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "work=pd.DataFrame(df[{\"From\", \"Age1\", \"Age2\", \"Age3\", \"Age4\", \n",
    "                      \"Age5\", \"Age6\", \"Age7\"}].groupby('From').sum())\n",
    "work['sum']=work.loc[:,work.columns != \"From\"].sum(axis=1)\n",
    "work.reset_index(inplace=True)\n",
    "work=work[['From', 'sum']]\n",
    "work.to_csv('worksize.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Generating Play file - take MSOA, IZ, SOA population estimates from gov sources and compile into master list.\n",
    " Merge with work file and subtract work population flow from the total population to get estimate of \"play\". \n",
    " \n",
    " Note 571 areas have \"from\" flows recorded which are higher than the estimated population - meaning negative estimates\n",
    " for play population."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Taking Scottish Data and converting to Two Column DataFrame with Code (cd) and Total Population (tot)\n",
    "scotpop=pd.read_csv(\"https://www.opendata.nhs.scot/dataset/7f010430-6ce1-4813-b25c-f7f335bdc4dc/resource/93df4c88-f74b-4630-abd8-459a19b12f47/download/iz2011-pop-est_02042020.csv\"\n",
    "                   , skiprows=3, header=None)\n",
    "scotpop1=scotpop[scotpop[0]==2018]\n",
    "scotpop1=scotpop1[{1,3,4}]\n",
    "scotpop1=scotpop1.iloc[2:,]\n",
    "scotpop1=scotpop1.groupby(1).sum()\n",
    "scotpop1=scotpop1.reset_index()\n",
    "scotpop1=scotpop1.rename(columns={1:\"cd\", 4:\"tot\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Taking Northern Irish Data and converting to Two Column DataFrame with Code (cd) and Total Population (tot)\n",
    "nipop=pd.read_csv(\"https://www.opendatani.gov.uk/dataset/2135b9bf-eb38-4c81-b614-7b07e8fdfc82/resource/fc3f91dd-a0c7-488a-9996-bd057a7cfffe/download/super-output-areas-soas-by-gender-and-broad-age-bands-mid-2001-to-mid-2018.csv\")\n",
    "nipop1=nipop[nipop['Age_Group']==\"All ages\"]\n",
    "nipop1=nipop1[nipop1['Mid_Year_Ending']==2018]\n",
    "nipop1=nipop1[nipop1['Gender']==\"All persons\"]\n",
    "nipop1=nipop1[{\"Geo_Code\", \"Population_Estimate\"}]\n",
    "nipop1=nipop1.rename(columns={\"Geo_Code\":\"cd\", \"Population_Estimate\":\"tot\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {},
   "outputs": [],
   "source": [
    "## File from https://www.ons.gov.uk/file?uri=%2fpeoplepopulationandcommunity%2fpopulationandmigration%2fpopulationestimates%2fdatasets%2fmiddlesuperoutputareamidyearpopulationestimatesnationalstatistics%2fmid2018sape21dt14a/sape21dt15mid2018msoaquinaryestimatesunformatted.zip\n",
    "#Taking England and Wales Data and converting to Two Column DataFrame with Code (cd) and Total Population (tot)\n",
    "# Not read directly from link as indexing subsheets in zipped xlsx format is lengthy. Can be updated\n",
    "ewpop=pd.read_excel(\"SAPE21DT15-mid-2018-msoa-quinary-estimates-unformatted.xlsx\", sheet_name=\"Mid-2018 Persons\", skiprows=4)\n",
    "ewpop=ewpop[{\"Area Codes\", \"All Ages\"}]\n",
    "ewpop=ewpop.rename(columns={\"Area Codes\":\"cd\", \"All Ages\":\"tot\"})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>tot</th>\n",
       "      <th>cd</th>\n",
       "      <th>ID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>8065</th>\n",
       "      <td>382</td>\n",
       "      <td>95YY15S1</td>\n",
       "      <td>673</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7679</th>\n",
       "      <td>881</td>\n",
       "      <td>95MM14S1</td>\n",
       "      <td>1889</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8780</th>\n",
       "      <td>885</td>\n",
       "      <td>S02001925</td>\n",
       "      <td>9560</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7201</th>\n",
       "      <td>1026</td>\n",
       "      <td>95AA01S1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7795</th>\n",
       "      <td>1038</td>\n",
       "      <td>95QQ03W1</td>\n",
       "      <td>1744</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4330</th>\n",
       "      <td>19374</td>\n",
       "      <td>E02000891</td>\n",
       "      <td>331</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>243</th>\n",
       "      <td>19855</td>\n",
       "      <td>E02001731</td>\n",
       "      <td>929</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>242</th>\n",
       "      <td>19868</td>\n",
       "      <td>E02001730</td>\n",
       "      <td>1758</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5103</th>\n",
       "      <td>21716</td>\n",
       "      <td>E02003475</td>\n",
       "      <td>2759</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4245</th>\n",
       "      <td>24969</td>\n",
       "      <td>E02000726</td>\n",
       "      <td>1494</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>9370 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        tot         cd    ID\n",
       "8065    382   95YY15S1   673\n",
       "7679    881   95MM14S1  1889\n",
       "8780    885  S02001925  9560\n",
       "7201   1026   95AA01S1     1\n",
       "7795   1038   95QQ03W1  1744\n",
       "...     ...        ...   ...\n",
       "4330  19374  E02000891   331\n",
       "243   19855  E02001731   929\n",
       "242   19868  E02001730  1758\n",
       "5103  21716  E02003475  2759\n",
       "4245  24969  E02000726  1494\n",
       "\n",
       "[9370 rows x 3 columns]"
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Generate master population estimate DataFrame (cd, tot)\n",
    "totpop=ewpop.append(nipop1, ignore_index=True).append(scotpop1, ignore_index=True)\n",
    "totpop['ID']=totpop.cd.map(lookupdict)\n",
    "totpop.sort_values(by=['tot'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### Finally generate playsize.csv by merging total population file with work file and generating \"play\" column"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "#Merge totpop with work file and use lookupdict to convert cd into master list of areas.  \n",
    "#newpop=totpop.merge(lookup[{\"CD\", \"TID\"}], left_on=\"cd\", right_on=\"CD\", how=\"left\")\n",
    "newpop=totpop.merge(work, left_on=\"ID\", right_on=\"From\", how=\"left\")\n",
    "newpop['sum'].replace(np.nan, 0, inplace=True)\n",
    "newpop['play']=newpop['tot'] - newpop['sum']\n",
    "playpop=newpop[['ID', 'play']]\n",
    "playpop=playpop.sort_values(by=['ID'])\n",
    "playpop.to_csv('playsize.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(571, 2)"
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "playpop[playpop['play']<=0].shape"
   ]
  }
 ],
 "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": 4
}