code/london-hhold-income-2001-2012.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "177de76c-caab-4d97-9520-06f9f33bde11",
   "metadata": {},
   "source": [
    "# Manipulate London Household Income\n",
    "\n",
    "Part of [london-data](https://github.com/jamesdamillington/london-data), by [jamesdamillington](https://github.com/jamesdamillington)\n",
    "\n",
    "## Aim\n",
    "Tidy [Household Income Estimates for Small Areas data](https://data.london.gov.uk/dataset/household-income-estimates-small-areas) from `.xlsx` to simple `.csv` that can be readily joined to [ONS Geographies data](https://github.com/jamesdamillington/london-data/tree/main/data/geographies/census). "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "27816853-2f20-488f-a1f7-7568ca8380da",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Last tested: 2022-08-25\n"
     ]
    }
   ],
   "source": [
    "from datetime import date\n",
    "print(f'Last tested: {date.today()}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "2fcf34ed-4076-41bf-9fa4-551f25e14523",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from functools import reduce\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3f539ee3-72ea-4e1c-848b-2e05db3e5d32",
   "metadata": {},
   "source": [
    "Read [Household Income Estimates for Small Areas data](https://data.london.gov.uk/dataset/household-income-estimates-small-areas) (downloaded 2022-09-15) \n",
    "> Source: Greater London Authority licensed under the Open Government Licence v.2.0  "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "850515cc-ca35-4d90-a4d3-fd5a2d05f04b",
   "metadata": {},
   "outputs": [],
   "source": [
    "econ_path = Path(\"../data/inputs/economic/\")\n",
    "econ_filepath = econ_path/\"gla-household-income-estimates.xlsx\""
   ]
  },
  {
   "cell_type": "markdown",
   "id": "d3256e55-462b-44d6-9b61-1ef8142a314d",
   "metadata": {},
   "source": [
    "Function to read a sheet (_Persons_, _Male_, _Female_) from the excel workbook and clean. This works for 2019 and 2020 but not 2018 and earlier!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "602ed139-9ac6-469b-984b-6f1764d28c6f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4835 entries, 0 to 4834\n",
      "Data columns (total 16 columns):\n",
      " #   Column         Non-Null Count  Dtype \n",
      "---  ------         --------------  ----- \n",
      " 0   LSOA11CD       4835 non-null   object\n",
      " 1   LSOA11NM       4835 non-null   object\n",
      " 2   LAD11CD        4835 non-null   object\n",
      " 3   LAD11NM        4835 non-null   object\n",
      " 4   HHI_mean_2001  4835 non-null   int64 \n",
      " 5   HHI_mean_2002  4835 non-null   int64 \n",
      " 6   HHI_mean_2003  4835 non-null   int64 \n",
      " 7   HHI_mean_2004  4835 non-null   int64 \n",
      " 8   HHI_mean_2005  4835 non-null   int64 \n",
      " 9   HHI_mean_2006  4835 non-null   int64 \n",
      " 10  HHI_mean_2007  4835 non-null   int64 \n",
      " 11  HHI_mean_2008  4835 non-null   int64 \n",
      " 12  HHI_mean_2009  4835 non-null   int64 \n",
      " 13  HHI_mean_2010  4835 non-null   int64 \n",
      " 14  HHI_mean_2011  4835 non-null   int64 \n",
      " 15  HHI_mean_2012  4835 non-null   int64 \n",
      "dtypes: int64(12), object(4)\n",
      "memory usage: 604.5+ KB\n"
     ]
    }
   ],
   "source": [
    "mean_pd = pd.read_excel(econ_filepath, sheet_name = \"LSOA11\", usecols=\"A:P\", header=None, skiprows=3)\n",
    "mean_pd.columns = [\"LSOA11CD\", \"LSOA11NM\", \"LAD11CD\", \"LAD11NM\",\n",
    "                   \"HHI_mean_2001\",\"HHI_mean_2002\",\"HHI_mean_2003\",\"HHI_mean_2004\",\n",
    "                   \"HHI_mean_2005\",\"HHI_mean_2006\",\"HHI_mean_2007\",\"HHI_mean_2008\",\n",
    "                   \"HHI_mean_2009\",\"HHI_mean_2010\",\"HHI_mean_2011\",\"HHI_mean_2012\"]  #so 2001/02 becomes 2001\n",
    "mean_pd.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "d6f3e3fc-e0e5-4aa1-925f-04a1350cec8d",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4835 entries, 0 to 4834\n",
      "Data columns (total 16 columns):\n",
      " #   Column           Non-Null Count  Dtype \n",
      "---  ------           --------------  ----- \n",
      " 0   LSOA11CD         4835 non-null   object\n",
      " 1   LSOA11NM         4835 non-null   object\n",
      " 2   LAD11CD          4835 non-null   object\n",
      " 3   LAD11NM          4835 non-null   object\n",
      " 4   HHI_median_2001  4835 non-null   int64 \n",
      " 5   HHI_median_2002  4835 non-null   int64 \n",
      " 6   HHI_median_2003  4835 non-null   int64 \n",
      " 7   HHI_median_2004  4835 non-null   int64 \n",
      " 8   HHI_median_2005  4835 non-null   int64 \n",
      " 9   HHI_median_2006  4835 non-null   int64 \n",
      " 10  HHI_median_2007  4835 non-null   int64 \n",
      " 11  HHI_median_2008  4835 non-null   int64 \n",
      " 12  HHI_median_2009  4835 non-null   int64 \n",
      " 13  HHI_median_2010  4835 non-null   int64 \n",
      " 14  HHI_median_2011  4835 non-null   int64 \n",
      " 15  HHI_median_2012  4835 non-null   int64 \n",
      "dtypes: int64(12), object(4)\n",
      "memory usage: 604.5+ KB\n"
     ]
    }
   ],
   "source": [
    "median_pd = pd.read_excel(econ_filepath, sheet_name = \"LSOA11\", usecols=\"A:D,R:AC\", header=None, skiprows=3)\n",
    "median_pd.columns = [\"LSOA11CD\", \"LSOA11NM\", \"LAD11CD\", \"LAD11NM\",\n",
    "                   \"HHI_median_2001\",\"HHI_median_2002\",\"HHI_median_2003\",\"HHI_median_2004\",\n",
    "                   \"HHI_median_2005\",\"HHI_median_2006\",\"HHI_median_2007\",\"HHI_median_2008\",\n",
    "                   \"HHI_median_2009\",\"HHI_median_2010\",\"HHI_median_2011\",\"HHI_median_2012\"]  #so 2001/02 becomes 2001\n",
    "median_pd.info()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dabc836b-4dcb-4bcf-bad7-93986445c04a",
   "metadata": {},
   "source": [
    "Write to file"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "06ff2824-00b7-4fc9-a042-2364d2a0da11",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_path = Path(\"../data/economic/\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "8061a38f-d069-43bf-affc-a06562db33d8",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_mean = out_path/\"london-2001-2012-HHI-mean.csv\"\n",
    "mean_pd.to_csv(out_mean,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "f54f4d3e-90b4-4fdc-9642-7cc1c4147c1c",
   "metadata": {},
   "outputs": [],
   "source": [
    "out_median = out_path/\"london-2001-2012-HHI-median.csv\"\n",
    "median_pd.to_csv(out_median,index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "d6ffc681-9f32-48d0-9083-00c40bfe09a5",
   "metadata": {},
   "outputs": [],
   "source": [
    "with open(out_path/\"metadata_HHI.txt\", 'w') as file:\n",
    "    pd.read_excel(econ_filepath, sheet_name = \"Metadata\").to_string(file, index=False)"
   ]
  }
 ],
 "metadata": {
  "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.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}