data_clean.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6a347ab9",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "37d3771f",
   "metadata": {},
   "outputs": [],
   "source": [
    "remapping = {'Bristol': 'Bristol, City of',\n",
    "             'Cambridgeshire': 'Cambridge',\n",
    "             'Comhairle nan Eilean Siar': 'Na h-Eileanan Siar',\n",
    "             'Edinburgh': 'City of Edinburgh',\n",
    "             'Glasgow': 'Glasgow City',\n",
    "             'Gloucestershire': 'Gloucester',\n",
    "             'Lincolnshire': 'Lincoln',\n",
    "             'Leicestershire': 'Leicester',\n",
    "             'Nottinghamshire': 'Nottingham',\n",
    "             'Derbyshire': 'Derby',\n",
    "             'Hertfordshire': 'Herefordshire, County of',\n",
    "             'Lancashire': 'Lancaster',\n",
    "             'Oadby & Wigston': 'Oadby and Wigston',\n",
    "             'Oxfordshire': 'Oxford',\n",
    "             'Staffordshire': 'Stafford',\n",
    "             'Warwickshire': 'Warwick',\n",
    "             'Worcestershire': 'Worcester'\n",
    "            }"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "99b378b4",
   "metadata": {},
   "source": [
    "## 2007"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "84637a5c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not yet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "a0b46033",
   "metadata": {},
   "source": [
    "## 2008"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "482d6895",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not yet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "dac32a16",
   "metadata": {},
   "source": [
    "## 2009"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "2fba44cd",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not yet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "55ac360f",
   "metadata": {},
   "source": [
    "## 2010"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "d3c5b578",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed '-' from data_df, in doing so, removed 375 rows.\n",
      "Removed 'No staff above...' from data_df, in doing so, removed 61 rows.\n",
      "Removed 'Did not respond' from data_df, in doing so, removed 27 rows.\n",
      "Removed 'Refused to answer' from data_df, in doing so, removed 11 rows.\n",
      "Removed 'Unusable information returned' from data_df, in doing so, removed 7 rows.\n",
      "Removed Total '0' from data_df, in doing so, removed 4 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2010\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, skiprows=[0,1,3,4]).fillna(0)\n",
    "data_df = data_df.rename(columns={\"Total Remuneration\": \"Total\"})\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "for i in range(1, len(data_df)):\n",
    "    if data_df.loc[i, 'Council'] == 0:\n",
    "        data_df.loc[i, 'Council'] = data_df.loc[i-1, 'Council']\n",
    "        \n",
    "# Filter out \"-\" in Total\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != \"-\"]\n",
    "print(\"Removed '-' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# Filter out \"No staff above...\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Name'] != \"No staff above £100,000\"]\n",
    "print(\"Removed 'No staff above...' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# Filter out \"Did not respond\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Name'] != \"Did not respond\"]\n",
    "print(\"Removed 'Did not respond' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# Filter out \"Refused to answer\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Name'] != \"Refused to answer\"]\n",
    "print(\"Removed 'Refused to answer' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "\n",
    "# Filter out \"Unusable information returned\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Name'] != \"Unusable information returned\"]\n",
    "print(\"Removed 'Unusable information returned' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2007_08.pkl\")\n",
    "#data_df[data_df.duplicated(subset=['Council'],keep=False)]"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "27a477d6",
   "metadata": {},
   "source": [
    "## 2011"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "796e00fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not yet\n",
    "# This has: 2008-09, 2009-10"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "fe952aec",
   "metadata": {},
   "source": [
    "## 2012"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "fc490985",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Data isn't useful, a repeat\n",
    "# This has: 2011-12"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ce516f27",
   "metadata": {},
   "source": [
    "## 2013"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "d3c0ce63",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 1278 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2013\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, 'Full', skiprows=[0]).fillna(0)\n",
    "data_df = data_df.rename(columns={\"2011-12\": \"Total\"})\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "    \n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2011_12.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "22307a26",
   "metadata": {},
   "source": [
    "## 2014"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "c1c390b0",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 793 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2014\n",
    "extension=\"xls\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, skiprows=[1]).fillna(0)\n",
    "\n",
    "data_df = data_df.rename(columns={\"Unnamed: 12\": \"Total\"})\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "    \n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2012_13.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "709562c4",
   "metadata": {},
   "source": [
    "## 2015"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "f3f965e9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed count_people '0' from data_df, in doing so, removed 33 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2015\n",
    "extension=\"xls\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"Full data >£100,000\",).fillna(0)\n",
    "\n",
    "data_df = data_df.rename(columns={\"Unnamed: 0\": \"Council\", \"≥£100,000\": \"count_people\"})\n",
    "data_df = data_df[[\"Council\", \"count_people\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['count_people'] != 0]\n",
    "print(\"Removed count_people '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2013_14.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "8ff3fb1b",
   "metadata": {},
   "source": [
    "## 2016"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "89679dd8",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Not yet"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b215a5f",
   "metadata": {},
   "source": [
    "## 2017"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "3e2142ca",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed 2014_15 '0' from data_df, in doing so, removed 19 rows.\n",
      "Removed 2015_16 '0' from data_df, in doing so, removed 4 rows.\n",
      "Removed 2014_15 'N/a' from data_df, in doing so, removed 1 rows.\n",
      "Removed 2015_16 'N/a' from data_df, in doing so, removed 0 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2017\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"Employees >£100,000 by council\").fillna(0)\n",
    "\n",
    "data_df = data_df.rename(columns={\"Local Authority\": \"Council\", \"Number over £100,000 in 2014-15\": \"2014_15\", \"Number over £100,000 in 2015-16\": \"2015_16\"})\n",
    "data_df = data_df[[\"Council\", \"2014_15\", \"2015_16\"]]\n",
    "data_df.drop(data_df.tail(1).index,inplace=True)\n",
    "data_df.drop(data_df.tail(1).index,inplace=True)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['2014_15'] != 0]\n",
    "print(\"Removed 2014_15 '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['2015_16'] != 0]\n",
    "print(\"Removed 2015_16 '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# Filter out Total \"N\\a\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['2014_15'] != \"N/a\"]\n",
    "print(\"Removed 2014_15 'N/a' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['2015_16'] != \"N/a\"]\n",
    "print(\"Removed 2015_16 'N/a' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "# 2014_15\n",
    "data_df_2014 = data_df.rename(columns={\"2014_15\": \"count_people\"})\n",
    "data_df_2014.to_pickle(\"pickles/2014_15.pkl\")\n",
    "\n",
    "# 2015_16\n",
    "data_df_2015 = data_df.rename(columns={\"2015_16\": \"count_people\"})\n",
    "data_df_2015.to_pickle(\"pickles/2015_16.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "26e4ba06",
   "metadata": {},
   "source": [
    "## 2018"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "fad12633",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 39 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2018\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"Full data\", skiprows=[0]).fillna(0)\n",
    "\n",
    "data_df = data_df.rename(columns={\"2016-17\": \"Total\"})\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2016_17.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b76528b4",
   "metadata": {},
   "source": [
    "## 2019"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "4bb5d865",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 31 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2019\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"2017-18 data\").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "data_df.drop(data_df.tail(18).index,inplace=True)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2017_18.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "6b19c682",
   "metadata": {},
   "source": [
    "## 2020"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "id": "61f8dfec",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 24 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2020\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"2018-19 data\").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "data_df.drop(data_df.tail(20).index,inplace=True)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2018_19.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3dd8af3f",
   "metadata": {},
   "source": [
    "## 2021"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "id": "dfa0fc0e",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 25 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2021\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"2019-20 totals\").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2019_20.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "050ab79a",
   "metadata": {},
   "source": [
    "## 2022"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "id": "5708579b",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 36 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2022\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"2020-21 totals\").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2020_21.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "2b2b5790",
   "metadata": {},
   "source": [
    "## 2023"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "e311ff74",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 0 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2023\n",
    "extension=\"xlsx\"\n",
    "file = \"data/Town_Hall_Rich_List_\" + str(year) + \".\" + str(extension)\n",
    "data_df = pd.read_excel(file, \"21-22 totals\").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Council\", \"Name\", \"Total\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Council'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Council\", \"count\"),\n",
    "        sum_tc=(\"Total\", \"sum\"),\n",
    "        mean_tc=(\"Total\", \"mean\"),\n",
    "        sd_tc=(\"Total\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Council'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df.to_pickle(\"pickles/2021_22.pkl\")"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "ddc700ec",
   "metadata": {},
   "source": [
    "## 2024"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "f37014db",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Removed Total '0' from data_df, in doing so, removed 59 rows.\n"
     ]
    }
   ],
   "source": [
    "year = 2024\n",
    "extension=\"xlsx\"\n",
    "file = f\"data/Town_Hall_Rich_List_{year}.{extension}\"\n",
    "data_df = pd.read_excel(file, \"Town Hall Rich List 2024 \").fillna(0)\n",
    "\n",
    "data_df = data_df[[\"Local authority\", \"Name\", \"Total (£)\"]]\n",
    "\n",
    "# Filter out Total \"0\"\n",
    "before_len = len(data_df)\n",
    "data_df = data_df[data_df['Total (£)'] != 0]\n",
    "print(\"Removed Total '0' from data_df, in doing so, removed \" + str(before_len - len(data_df)) + \" rows.\")\n",
    "\n",
    "data_df = data_df \\\n",
    "    .groupby(['Local authority'], sort=False, as_index=False) \\\n",
    "    .agg(\n",
    "        count_people=(\"Local authority\", \"count\"),\n",
    "        sum_tc=(\"Total (£)\", \"sum\"),\n",
    "        mean_tc=(\"Total (£)\", \"mean\"),\n",
    "        sd_tc=(\"Total (£)\", np.std)\n",
    ").fillna(0)\n",
    "\n",
    "data_df['Council'] = data_df['Local authority'].str.strip()\n",
    "data_df['Council'] = data_df['Council'].replace({k: v for k, v in remapping.items() if not data_df['Council'].str.contains(v).any()})\n",
    "\n",
    "data_df = data_df[['Council', 'count_people', 'sum_tc', 'mean_tc', 'sd_tc']]\n",
    "\n",
    "data_df.to_pickle(\"pickles/2022_23.pkl\")"
   ]
  }
 ],
 "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.10.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}