sen_covid_R1_ChangeByLA.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hDXxYmiQhT3U"
      },
      "source": [
        "# Analysing the impact of COVID on special educational needs - notebook 1: change by local authority\n",
        "\n",
        "Go to [National statistics on special educational needs in England](https://explore-education-statistics.service.gov.uk/find-statistics/special-educational-needs-in-england) and click 'Download all data', then unzip, it's called sen_ncyear_.csv - it has a column for each year group (Q is nc_year_1) and a column (M) for 'primary_need' - so you can filter that down to 'Speech, Language and Communications needs'\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EG0nPZ1TpJJ5"
      },
      "source": [
        "## Install the packages we need"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "E5cR6y1bV8ca"
      },
      "outputs": [],
      "source": [
        "#load rmagic to be able to run R\n",
        "#from https://towardsdatascience.com/how-to-use-r-in-google-colab-b6e02d736497\n",
        "%load_ext rpy2.ipython"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "fJECEDHjke92",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "2ae86f9a-af39-4734-adc5-e386cfd06cc1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://cran.rstudio.com/src/contrib/tidyverse_1.3.2.tar.gz'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Content type 'application/x-gzip'\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  length 702514 bytes (686 KB)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 686 KB\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: The downloaded source packages are in\n",
            "\t‘/tmp/Rtmpc5269Q/downloaded_packages’\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://cran.rstudio.com/src/contrib/downloader_0.4.tar.gz'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Content type 'application/x-gzip'\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  length 6808 bytes\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 6808 bytes\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: The downloaded source packages are in\n",
            "\t‘/tmp/Rtmpc5269Q/downloaded_packages’\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: also installing the dependency ‘snakecase’\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://cran.rstudio.com/src/contrib/snakecase_0.11.0.tar.gz'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Content type 'application/x-gzip'\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  length 459702 bytes (448 KB)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 448 KB\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://cran.rstudio.com/src/contrib/janitor_2.1.0.tar.gz'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Content type 'application/x-gzip'\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  length 215365 bytes (210 KB)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 210 KB\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: The downloaded source packages are in\n",
            "\t‘/tmp/Rtmpc5269Q/downloaded_packages’\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "Attaching package: ‘janitor’\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: The following objects are masked from ‘package:stats’:\n",
            "\n",
            "    chisq.test, fisher.test\n",
            "\n",
            "\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#install the tidyverse package\n",
        "install.packages('tidyverse')\n",
        "library('tidyverse')\n",
        "#install the downloader package: https://cran.r-project.org/web/packages/downloader/index.html\n",
        "install.packages(\"downloader\")\n",
        "library(downloader)\n",
        "#this helps us calculate grand totals later\n",
        "install.packages('janitor')\n",
        "library(janitor)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iIJSwwX7_nfu",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "d31c74c7-a15a-41e4-9e93-4d87394f05e7"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Installing package into ‘/usr/local/lib/R/site-library’\n",
            "(as ‘lib’ is unspecified)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://cran.rstudio.com/src/contrib/stringi_1.7.8.tar.gz'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Content type 'application/x-gzip'\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  length 8032287 bytes (7.7 MB)\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: =\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 7.7 MB\n",
            "\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: The downloaded source packages are in\n",
            "\t‘/tmp/Rtmpc5269Q/downloaded_packages’\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "install.packages('stringi')\n",
        "library(stringi)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "N4nnn_qtpMs_"
      },
      "source": [
        "## Import the zip file, extract the data\n",
        "\n",
        "The data is published in a zip file that can be accessed from the 'Download all data' button at https://explore-education-statistics.service.gov.uk/find-statistics/special-educational-needs-in-england/2020-21#dataDownloads-1\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "dAuVBSTNi7TK"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#store the URL for the data zip file which is found by right-clicking on \n",
        "#'Download all data' at https://explore-education-statistics.service.gov.uk/find-statistics/special-educational-needs-in-england/2020-21#dataDownloads-1\n",
        "zipurl <- \"https://content.explore-education-statistics.service.gov.uk/api/releases/0a424edd-2bc7-45aa-a45e-5ae6dc4f23ab/files\"\n",
        "# This is the 21/22 data\n",
        "zipurl <- \"https://content.explore-education-statistics.service.gov.uk/api/releases/daf8d64d-ce21-4f21-c28c-08da3ee963bf/files\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IE-Uby99jXzU",
        "outputId": "b4d4465f-2978-4a61-99a7-c3cca511bb5a"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stderr",
          "text": [
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: trying URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/daf8d64d-ce21-4f21-c28c-08da3ee963bf/files'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: downloaded 51.6 MB\n",
            "\n",
            "\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#download the zip file from the url\n",
        "downloader::download(zipurl, dest=\"datasets.zip\", mode=\"wb\") \n",
        "#unzip it \n",
        "unzip (\"datasets.zip\", exdir = \"./\")"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PyNB5gxJh42t",
        "outputId": "547ca267-c8dd-4c49-e498-81db73c1d319"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "Rows: 218715 Columns: 48\n",
            "── Column specification ────────────────────────────────────────────────────────\n",
            "Delimiter: \",\"\n",
            "chr (11): time_identifier, geographic_level, country_code, country_name, reg...\n",
            "dbl (37): time_period, old_la_code, number_of_pupils, nc_early_years, nc_rec...\n",
            "\n",
            "ℹ Use `spec()` to retrieve the full column specification for this data.\n",
            "ℹ Specify the column types or set `show_col_types = FALSE` to quiet this message.\n"
          ]
        }
      ],
      "source": [
        "\n",
        "%%R \n",
        "#import the data extracted from the zip\n",
        "data21 <- \"data/sen_ncyear.csv\"\n",
        "#this is the name in 2022\n",
        "data22 <- \"data/sen_ncyear_.csv\"\n",
        "data <- readr::read_csv(data22)\n",
        "\n"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "AM-bN4mCqYEd"
      },
      "source": [
        "## `filter()` the data to what we need\n",
        "\n",
        "This is a very large dataframe, and we don't need all of it. Firstly, we're specifically interested in the figures on speech and language."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "PeqR8-5lqdQQ",
        "outputId": "e8844a55-e4c3-4b33-a5ac-8d3a5437a943"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 16,735 × 48\n",
            "   time_period time_identifier geographic_level country_code country_name\n",
            "         <dbl> <chr>           <chr>            <chr>        <chr>       \n",
            " 1      202122 Academic year   National         E92000001    England     \n",
            " 2      202122 Academic year   National         E92000001    England     \n",
            " 3      202122 Academic year   National         E92000001    England     \n",
            " 4      202122 Academic year   National         E92000001    England     \n",
            " 5      202122 Academic year   National         E92000001    England     \n",
            " 6      202122 Academic year   National         E92000001    England     \n",
            " 7      202122 Academic year   National         E92000001    England     \n",
            " 8      202122 Academic year   National         E92000001    England     \n",
            " 9      202122 Academic year   National         E92000001    England     \n",
            "10      202122 Academic year   National         E92000001    England     \n",
            "# … with 16,725 more rows, and 43 more variables: region_name <chr>,\n",
            "#   region_code <chr>, old_la_code <dbl>, la_name <chr>, new_la_code <chr>,\n",
            "#   phase_type_grouping <chr>, pupil_sen_status <chr>, primary_need <chr>,\n",
            "#   number_of_pupils <dbl>, nc_early_years <dbl>, nc_reception <dbl>,\n",
            "#   nc_year_1 <dbl>, nc_year_2 <dbl>, nc_year_3 <dbl>, nc_year_4 <dbl>,\n",
            "#   nc_year_5 <dbl>, nc_year_6 <dbl>, nc_year_7 <dbl>, nc_year_8 <dbl>,\n",
            "#   nc_year_9 <dbl>, nc_year_10 <dbl>, nc_year_11 <dbl>, nc_year_12 <dbl>, …\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf <- filter(data, primary_need == \"Speech, Language and Communications needs\")\n",
        "print(slcdf)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "yZzzaIMkCATZ"
      },
      "source": [
        "### Filter: primary schools only\n",
        "\n",
        "We also only want to look at primary schools."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4Gpx5yqWtx34",
        "outputId": "bf9820ef-641e-488d-b24a-fc257e71663f"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 3,374 × 48\n",
            "   time_period time_identifier geographic_level country_code country_name\n",
            "         <dbl> <chr>           <chr>            <chr>        <chr>       \n",
            " 1      202122 Academic year   National         E92000001    England     \n",
            " 2      202122 Academic year   National         E92000001    England     \n",
            " 3      202122 Academic year   National         E92000001    England     \n",
            " 4      202122 Academic year   Regional         E92000001    England     \n",
            " 5      202122 Academic year   Regional         E92000001    England     \n",
            " 6      202122 Academic year   Regional         E92000001    England     \n",
            " 7      202122 Academic year   Local authority  E92000001    England     \n",
            " 8      202122 Academic year   Local authority  E92000001    England     \n",
            " 9      202122 Academic year   Local authority  E92000001    England     \n",
            "10      202122 Academic year   Local authority  E92000001    England     \n",
            "# … with 3,364 more rows, and 43 more variables: region_name <chr>,\n",
            "#   region_code <chr>, old_la_code <dbl>, la_name <chr>, new_la_code <chr>,\n",
            "#   phase_type_grouping <chr>, pupil_sen_status <chr>, primary_need <chr>,\n",
            "#   number_of_pupils <dbl>, nc_early_years <dbl>, nc_reception <dbl>,\n",
            "#   nc_year_1 <dbl>, nc_year_2 <dbl>, nc_year_3 <dbl>, nc_year_4 <dbl>,\n",
            "#   nc_year_5 <dbl>, nc_year_6 <dbl>, nc_year_7 <dbl>, nc_year_8 <dbl>,\n",
            "#   nc_year_9 <dbl>, nc_year_10 <dbl>, nc_year_11 <dbl>, nc_year_12 <dbl>, …\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf <- filter(slcdf, phase_type_grouping == \"State-funded primary\")\n",
        "print(slcdf)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "UIxJ-Md6CYdP"
      },
      "source": [
        "### Filter: totals only\n",
        "\n",
        "We also don't need the sub-categories - just the total of those needing support."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "2bL33oJyCC8P",
        "outputId": "ab2ee96c-c596-4465-bd50-dfeec1b976ad"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "pupil_sen_status\n",
            "     SEN Support Statement or EHC            Total \n",
            "            1125             1124             1125 \n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#show the unique values and their frequency\n",
        "table(slcdf['pupil_sen_status'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "t9mhVUkcBPkI",
        "outputId": "451ccfe7-9156-414f-fb29-a6f95d1bcb1d"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 1,125 × 48\n",
            "   time_period time_identifier geographic_level country_code country_name\n",
            "         <dbl> <chr>           <chr>            <chr>        <chr>       \n",
            " 1      202122 Academic year   National         E92000001    England     \n",
            " 2      202122 Academic year   Regional         E92000001    England     \n",
            " 3      202122 Academic year   Local authority  E92000001    England     \n",
            " 4      202122 Academic year   Local authority  E92000001    England     \n",
            " 5      202122 Academic year   Local authority  E92000001    England     \n",
            " 6      202122 Academic year   Local authority  E92000001    England     \n",
            " 7      202122 Academic year   Local authority  E92000001    England     \n",
            " 8      202122 Academic year   Local authority  E92000001    England     \n",
            " 9      202122 Academic year   Local authority  E92000001    England     \n",
            "10      202122 Academic year   Local authority  E92000001    England     \n",
            "# … with 1,115 more rows, and 43 more variables: region_name <chr>,\n",
            "#   region_code <chr>, old_la_code <dbl>, la_name <chr>, new_la_code <chr>,\n",
            "#   phase_type_grouping <chr>, pupil_sen_status <chr>, primary_need <chr>,\n",
            "#   number_of_pupils <dbl>, nc_early_years <dbl>, nc_reception <dbl>,\n",
            "#   nc_year_1 <dbl>, nc_year_2 <dbl>, nc_year_3 <dbl>, nc_year_4 <dbl>,\n",
            "#   nc_year_5 <dbl>, nc_year_6 <dbl>, nc_year_7 <dbl>, nc_year_8 <dbl>,\n",
            "#   nc_year_9 <dbl>, nc_year_10 <dbl>, nc_year_11 <dbl>, nc_year_12 <dbl>, …\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf_filter3 <- filter(slcdf, pupil_sen_status == \"Total\")\n",
        "print(slcdf_filter3)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "EInN3zgUD83F"
      },
      "source": [
        "### Filter: local authorities only\n",
        "\n",
        "We also only want to look at data for local authorities. "
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "sJT2Jpd8QS26",
        "outputId": "a78a3bb3-bead-49ce-9d7f-a089f5ab3441"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "geographic_level\n",
            "Local authority        National        Regional \n",
            "           1055               7              63 \n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#show the unique values and their frequency\n",
        "table(slcdf_filter3['geographic_level'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "IIr7y0jYD0KK",
        "outputId": "eb49027c-517b-434f-f5d0-df292ac3f3e4"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 1,055 × 48\n",
            "   time_period time_identifier geographic_level country_code country_name\n",
            "         <dbl> <chr>           <chr>            <chr>        <chr>       \n",
            " 1      202122 Academic year   Local authority  E92000001    England     \n",
            " 2      202122 Academic year   Local authority  E92000001    England     \n",
            " 3      202122 Academic year   Local authority  E92000001    England     \n",
            " 4      202122 Academic year   Local authority  E92000001    England     \n",
            " 5      202122 Academic year   Local authority  E92000001    England     \n",
            " 6      202122 Academic year   Local authority  E92000001    England     \n",
            " 7      202122 Academic year   Local authority  E92000001    England     \n",
            " 8      202122 Academic year   Local authority  E92000001    England     \n",
            " 9      202122 Academic year   Local authority  E92000001    England     \n",
            "10      202122 Academic year   Local authority  E92000001    England     \n",
            "# … with 1,045 more rows, and 43 more variables: region_name <chr>,\n",
            "#   region_code <chr>, old_la_code <dbl>, la_name <chr>, new_la_code <chr>,\n",
            "#   phase_type_grouping <chr>, pupil_sen_status <chr>, primary_need <chr>,\n",
            "#   number_of_pupils <dbl>, nc_early_years <dbl>, nc_reception <dbl>,\n",
            "#   nc_year_1 <dbl>, nc_year_2 <dbl>, nc_year_3 <dbl>, nc_year_4 <dbl>,\n",
            "#   nc_year_5 <dbl>, nc_year_6 <dbl>, nc_year_7 <dbl>, nc_year_8 <dbl>,\n",
            "#   nc_year_9 <dbl>, nc_year_10 <dbl>, nc_year_11 <dbl>, nc_year_12 <dbl>, …\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf_filter4 <- filter(slcdf_filter3, geographic_level == \"Local authority\")\n",
        "print(slcdf_filter4)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "ZZuAqbXcb1aD"
      },
      "outputs": [],
      "source": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "pmuJbXfkBkv5"
      },
      "source": [
        "## Remove columns\n",
        "\n",
        "We also have a number of columns we don't need."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "yDcXFtZTBpQi",
        "outputId": "a8b026d2-8265-46c1-8732-7f9e2c32bf76"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " [1] \"time_period\"             \"time_identifier\"        \n",
            " [3] \"geographic_level\"        \"country_code\"           \n",
            " [5] \"country_name\"            \"region_name\"            \n",
            " [7] \"region_code\"             \"old_la_code\"            \n",
            " [9] \"la_name\"                 \"new_la_code\"            \n",
            "[11] \"phase_type_grouping\"     \"pupil_sen_status\"       \n",
            "[13] \"primary_need\"            \"number_of_pupils\"       \n",
            "[15] \"nc_early_years\"          \"nc_reception\"           \n",
            "[17] \"nc_year_1\"               \"nc_year_2\"              \n",
            "[19] \"nc_year_3\"               \"nc_year_4\"              \n",
            "[21] \"nc_year_5\"               \"nc_year_6\"              \n",
            "[23] \"nc_year_7\"               \"nc_year_8\"              \n",
            "[25] \"nc_year_9\"               \"nc_year_10\"             \n",
            "[27] \"nc_year_11\"              \"nc_year_12\"             \n",
            "[29] \"nc_year_13\"              \"nc_year_14\"             \n",
            "[31] \"nc_not_followed\"         \"nc_early_years_percent\" \n",
            "[33] \"nc_reception_percent\"    \"nc_year_1_percent\"      \n",
            "[35] \"nc_year_2_percent\"       \"nc_year_3_percent\"      \n",
            "[37] \"nc_year_4_percent\"       \"nc_year_5_percent\"      \n",
            "[39] \"nc_year_6_percent\"       \"nc_year_7_percent\"      \n",
            "[41] \"nc_year_8_percent\"       \"nc_year_9_percent\"      \n",
            "[43] \"nc_year_10_percent\"      \"nc_year_11_percent\"     \n",
            "[45] \"nc_year_12_percent\"      \"nc_year_13_percent\"     \n",
            "[47] \"nc_year_14_percent\"      \"nc_not_followed_percent\"\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#show the columns\n",
        "colnames(slcdf_filter4)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "10FXzg1TDjih"
      },
      "source": [
        "Some columns only have one value"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "oTejBfZODbZD",
        "outputId": "9b8f41c7-1813-4171-dd8f-e86ab1010136"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "time_identifier\n",
            "Academic year \n",
            "         1055 \n",
            "geographic_level\n",
            "Local authority \n",
            "           1055 \n",
            "country_name\n",
            "England \n",
            "   1055 \n",
            "country_code\n",
            "E92000001 \n",
            "     1055 \n",
            "phase_type_grouping\n",
            "State-funded primary \n",
            "                1055 \n",
            "pupil_sen_status\n",
            "Total \n",
            " 1055 \n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#count the unique values in the time_identifier column\n",
        "print(table(slcdf_filter4['time_identifier']))\n",
        "#repeat for the geographic level column\n",
        "print(table(slcdf_filter4['geographic_level']))\n",
        "#repeat for country_name\n",
        "print(table(slcdf_filter4['country_name']))\n",
        "#repeat for country_code\n",
        "print(table(slcdf_filter4['country_code']))\n",
        "#repeat for phase_type_grouping\n",
        "print(table(slcdf_filter4['phase_type_grouping']))\n",
        "#repeat for pupil_sen_status\n",
        "print(table(slcdf_filter4['pupil_sen_status']))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1x3CQn-gFjNb"
      },
      "source": [
        "We remove that by using `select()` and a minus before the column we want to exclude."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "-Phbicv4DUTW",
        "outputId": "c1d6a11f-9956-4fcd-b5a8-36bc7ac5c812"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 1,055 × 42\n",
            "   time_period region_name region_code old_la_code la_name           new_la_code\n",
            "         <dbl> <chr>       <chr>             <dbl> <chr>             <chr>      \n",
            " 1      202122 North East  E12000001           805 Hartlepool        E06000001  \n",
            " 2      202122 North East  E12000001           806 Middlesbrough     E06000002  \n",
            " 3      202122 North East  E12000001           807 Redcar and Cleve… E06000003  \n",
            " 4      202122 North East  E12000001           808 Stockton-on-Tees  E06000004  \n",
            " 5      202122 North East  E12000001           841 Darlington        E06000005  \n",
            " 6      202122 North East  E12000001           840 County Durham     E06000047  \n",
            " 7      202122 North East  E12000001           929 Northumberland    E06000057  \n",
            " 8      202122 North East  E12000001           391 Newcastle upon T… E08000021  \n",
            " 9      202122 North East  E12000001           392 North Tyneside    E08000022  \n",
            "10      202122 North East  E12000001           393 South Tyneside    E08000023  \n",
            "# … with 1,045 more rows, and 36 more variables: primary_need <chr>,\n",
            "#   number_of_pupils <dbl>, nc_early_years <dbl>, nc_reception <dbl>,\n",
            "#   nc_year_1 <dbl>, nc_year_2 <dbl>, nc_year_3 <dbl>, nc_year_4 <dbl>,\n",
            "#   nc_year_5 <dbl>, nc_year_6 <dbl>, nc_year_7 <dbl>, nc_year_8 <dbl>,\n",
            "#   nc_year_9 <dbl>, nc_year_10 <dbl>, nc_year_11 <dbl>, nc_year_12 <dbl>,\n",
            "#   nc_year_13 <dbl>, nc_year_14 <dbl>, nc_not_followed <dbl>,\n",
            "#   nc_early_years_percent <dbl>, nc_reception_percent <dbl>, …\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#remove the specified columns\n",
        "slcdf_col_filter1 <- select(slcdf_filter4, \n",
        "                            -c(time_identifier, \n",
        "                               geographic_level,\n",
        "                               country_name,\n",
        "                               country_code,\n",
        "                               phase_type_grouping,\n",
        "                               pupil_sen_status))\n",
        "slcdf_col_filter1"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "lWOctXXTESnf"
      },
      "source": [
        "And we don't need years 7 onwards because that's after primary school."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "H_fCNTrPEVo0",
        "outputId": "59edb85c-75ca-4a07-e772-f5cadbf10ee1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"nc_year_7\"       \"nc_year_8\"       \"nc_year_9\"       \"nc_year_10\"     \n",
            "[5] \"nc_year_11\"      \"nc_year_12\"      \"nc_year_13\"      \"nc_year_14\"     \n",
            "[9] \"nc_not_followed\"\n",
            "[1] \"nc_year_7_percent\"       \"nc_year_8_percent\"      \n",
            "[3] \"nc_year_9_percent\"       \"nc_year_10_percent\"     \n",
            "[5] \"nc_year_11_percent\"      \"nc_year_12_percent\"     \n",
            "[7] \"nc_year_13_percent\"      \"nc_year_14_percent\"     \n",
            "[9] \"nc_not_followed_percent\"\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#show the columns we want to exclude\n",
        "print(colnames(slcdf_col_filter1)[17:25])\n",
        "print(colnames(slcdf_col_filter1)[34:42])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "bE4spjVRFvKb",
        "outputId": "ec2be870-af49-4c3d-f04f-7dbfeef7149e"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            " [1] \"time_period\"            \"region_name\"            \"region_code\"           \n",
            " [4] \"old_la_code\"            \"la_name\"                \"new_la_code\"           \n",
            " [7] \"primary_need\"           \"number_of_pupils\"       \"nc_early_years\"        \n",
            "[10] \"nc_reception\"           \"nc_year_1\"              \"nc_year_2\"             \n",
            "[13] \"nc_year_3\"              \"nc_year_4\"              \"nc_year_5\"             \n",
            "[16] \"nc_year_6\"              \"nc_early_years_percent\" \"nc_reception_percent\"  \n",
            "[19] \"nc_year_1_percent\"      \"nc_year_2_percent\"      \"nc_year_3_percent\"     \n",
            "[22] \"nc_year_4_percent\"      \"nc_year_5_percent\"      \"nc_year_6_percent\"     \n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#remove the specified column\n",
        "slcdf_col_filter2 <- select(slcdf_col_filter1, \n",
        "                            -colnames(slcdf_col_filter1)[17:25]) %>% \n",
        "  select(-colnames(slcdf_col_filter1)[34:42])\n",
        "#show the column names left\n",
        "colnames(slcdf_col_filter2)"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "30tmcSH1V2oW"
      },
      "source": [
        "## Pivot by LA and year using `group_by()` and `summarize()`\n",
        "\n",
        "There are a number of ways to create pivot table-like summaries in R, including [the `pivottabler` package](https://cran.r-project.org/web/packages/pivottabler/vignettes/v00-vignettes.html) and `dplyr`. We'll be using the latter approach, [documented here](https://rstudio-conf-2020.github.io/r-for-excel/pivot-tables.html)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ywVhaf_QWB2r",
        "outputId": "8df6e57d-e004-417b-d350-9af51999b2a1"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 154 × 2\n",
            "   la_name                      count_of_la\n",
            "   <chr>                              <int>\n",
            " 1 Barking and Dagenham                   7\n",
            " 2 Barnet                                 7\n",
            " 3 Barnsley                               7\n",
            " 4 Bath and North East Somerset           7\n",
            " 5 Bedford                                7\n",
            " 6 Bexley                                 7\n",
            " 7 Birmingham                             7\n",
            " 8 Blackburn with Darwen                  7\n",
            " 9 Blackpool                              7\n",
            "10 Bolton                                 7\n",
            "# … with 144 more rows\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#the group_by line specifies the rows of the pivot table\n",
        "slcdf_col_filter2 %>%\n",
        "  group_by(la_name) %>% \n",
        "  summarize(count_of_la = n()) # this specifies the name and calculation to create the 'values' part"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Adding rows\n",
        "\n",
        "This tells us that there are 7 values for each LA. Those would be the figures for each of the 7 academic years covered by the data. We need to add those as rows so that they are separated out.\n",
        "\n",
        "We can begin to do this by adding the `time_period` column as a second argument in `group_by()`"
      ],
      "metadata": {
        "id": "DU9hRPNZM11U"
      }
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "KdaJk5NacjHp",
        "outputId": "29e571b9-a3aa-49e8-fca8-7b3788249048"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "`summarise()` has grouped output by 'la_name'. You can override using the\n",
            "`.groups` argument.\n",
            "# A tibble: 1,055 × 3\n",
            "# Groups:   la_name [154]\n",
            "   la_name              time_period count_of_la\n",
            "   <chr>                      <dbl>       <int>\n",
            " 1 Barking and Dagenham      201516           1\n",
            " 2 Barking and Dagenham      201617           1\n",
            " 3 Barking and Dagenham      201718           1\n",
            " 4 Barking and Dagenham      201819           1\n",
            " 5 Barking and Dagenham      201920           1\n",
            " 6 Barking and Dagenham      202021           1\n",
            " 7 Barking and Dagenham      202122           1\n",
            " 8 Barnet                    201516           1\n",
            " 9 Barnet                    201617           1\n",
            "10 Barnet                    201718           1\n",
            "# … with 1,045 more rows\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#the group_by line specifies the rows of the pivot table\n",
        "slcdf_col_filter2 %>%\n",
        "  group_by(la_name, time_period) %>% \n",
        "  summarize(count_of_la = n()) # this specifies the name and calculation to create the 'values' part"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "Co0NdPBtofTQ"
      },
      "source": [
        "### Sum, don't count\n",
        "\n",
        "Now, this just adds another column rather than a column headed with each year, but we can reshape this table to fix that. First, let's change from counting to summing."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "1Ra26NqJoVRa",
        "outputId": "4f2b6a10-4bbc-4b1d-8537-5ea3b7d90dc3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "`summarise()` has grouped output by 'la_name'. You can override using the\n",
            "`.groups` argument.\n",
            "# A tibble: 1,055 × 3\n",
            "# Groups:   la_name [154]\n",
            "   la_name              time_period sum_of_yr1\n",
            "   <chr>                      <dbl>      <dbl>\n",
            " 1 Barking and Dagenham      201516        241\n",
            " 2 Barking and Dagenham      201617        299\n",
            " 3 Barking and Dagenham      201718        285\n",
            " 4 Barking and Dagenham      201819        287\n",
            " 5 Barking and Dagenham      201920        282\n",
            " 6 Barking and Dagenham      202021        316\n",
            " 7 Barking and Dagenham      202122        319\n",
            " 8 Barnet                    201516        254\n",
            " 9 Barnet                    201617        239\n",
            "10 Barnet                    201718        241\n",
            "# … with 1,045 more rows\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#the group_by line specifies the rows of the pivot table\n",
        "slcdf_col_filter2 %>%\n",
        "  group_by(la_name, time_period) %>% \n",
        "  summarize(sum_of_yr1 = sum(nc_year_1)) # now we 'sum' a specific column, \n",
        "  #and the resulting column is called 'sum_of_yr1'"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bwdZKlzYpOQZ"
      },
      "source": [
        "## Reshape long to wide using `spread()`\n",
        "\n",
        "We now reshape to make that second column of years into the column headings, so there's only one row per LA, and we can more easily calculate year on year change.\n",
        "\n",
        "Note that R doesn't like number-only column names, so it puts each one inside the code accent: `"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "wPGGQ8mqoytr",
        "outputId": "27a7ef6b-d7a6-4dfc-82ed-9ed7aba5abba"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "`summarise()` has grouped output by 'la_name'. You can override using the\n",
            "`.groups` argument.\n",
            "# A tibble: 154 × 8\n",
            "# Groups:   la_name [154]\n",
            "   la_name        `201516` `201617` `201718` `201819` `201920` `202021` `202122`\n",
            "   <chr>             <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>\n",
            " 1 Barking and D…      241      299      285      287      282      316      319\n",
            " 2 Barnet              254      239      241      250      234      209      244\n",
            " 3 Barnsley            114      147      141      125      129      107      143\n",
            " 4 Bath and Nort…       82      101       95      118       96      115      157\n",
            " 5 Bedford              93       84      115      110      107      130      112\n",
            " 6 Bexley              209      215      213      247      245      200      219\n",
            " 7 Birmingham          765      836      868      900      899      936     1114\n",
            " 8 Blackburn wit…      181      200      199      216      224      226      281\n",
            " 9 Blackpool           146      139      170      180      203      178      182\n",
            "10 Bolton              142      178      218      214      260      260      290\n",
            "# … with 144 more rows\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#save the previous results\n",
        "pivot_by_la_yr_yr1 <- slcdf_col_filter2 %>%\n",
        "  group_by(la_name, time_period) %>% \n",
        "  summarize(sum_of_yr1 = sum(nc_year_1)) # now we 'sum' a specific column, \n",
        "  #and the resulting column is called 'sum_of_yr1'\n",
        "#specify we want to convert the time_period column values to column names \n",
        "#and insert the values from the sum_of_yr1 column underneath\n",
        "yr1_wide <- pivot_by_la_yr_yr1 %>% spread(time_period, sum_of_yr1)\n",
        "#show the results\n",
        "yr1_wide"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Add a grand total\n",
        "\n",
        "We will want a national change figure, too. So let's add that row."
      ],
      "metadata": {
        "id": "BuTGIDjdYJ5j"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#check how many rows so we don't overwrite any\n",
        "nrow(yr1_wide)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "WBpZRHH6ZEqT",
        "outputId": "96c5883b-9679-4f03-a801-3f978978e1b4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] 154\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#create empty vector to contain totals, \n",
        "#we start with a number which will be replaced with the text that will go in the first column\n",
        "#but for now we need a number so the others aren't stored as strings\n",
        "totalsvec <- (0)\n",
        "#loop through the other column names\n",
        "for (i in colnames(yr1_wide)[seq(2,8)]){\n",
        "    #print the column name we're working with\n",
        "    print(i)\n",
        "    #add all the numbers in that column\n",
        "    coltotal <- sum(yr1_wide[i][!is.na(yr1_wide[i])])\n",
        "    print(coltotal)\n",
        "    #add it to the vector\n",
        "    totalsvec <- c(totalsvec,coltotal)\n",
        "}\n",
        "print(totalsvec)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "0MrSzBQWYOWR",
        "outputId": "7e139295-14bc-48b8-98a4-8154c547ef20"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"201516\"\n",
            "[1] 33464\n",
            "[1] \"201617\"\n",
            "[1] 35061\n",
            "[1] \"201718\"\n",
            "[1] 35949\n",
            "[1] \"201819\"\n",
            "[1] 37178\n",
            "[1] \"201920\"\n",
            "[1] 37632\n",
            "[1] \"202021\"\n",
            "[1] 38560\n",
            "[1] \"202122\"\n",
            "[1] 42341\n",
            "[1]     0 33464 35061 35949 37178 37632 38560 42341\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#transpose the vectore and convert to a dataframe\n",
        "newdf <- as.data.frame(t(totalsvec))\n",
        "print(newdf)\n",
        "#assign the column names to that dataframe\n",
        "colnames(newdf) <- colnames(yr1_wide)\n",
        "print(newdf)\n",
        "#change the first cell to 'total'\n",
        "newdf[1,1] <- \"Total\"\n",
        "print(newdf)"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "RH4WSnd7cV2j",
        "outputId": "f370aefe-c29a-4525-a612-782f276f142d"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "  V1    V2    V3    V4    V5    V6    V7    V8\n",
            "1  0 33464 35061 35949 37178 37632 38560 42341\n",
            "  la_name 201516 201617 201718 201819 201920 202021 202122\n",
            "1       0  33464  35061  35949  37178  37632  38560  42341\n",
            "  la_name 201516 201617 201718 201819 201920 202021 202122\n",
            "1   Total  33464  35061  35949  37178  37632  38560  42341\n"
          ]
        }
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#bind the two dataframes together\n",
        "yr1_wtotal <- rbind(yr1_wide,newdf)\n",
        "#and check the last few rows\n",
        "print(yr1_wtotal[seq(152,155),] )\n",
        "#overwrite the original dataframe\n",
        "yr1_wide <- yr1_wtotal"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "4RHOArDUbuEl",
        "outputId": "98b0f268-68e0-4c50-f6a7-9fd8dbb10f5e"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 4 × 8\n",
            "# Groups:   la_name [4]\n",
            "  la_name        `201516` `201617` `201718` `201819` `201920` `202021` `202122`\n",
            "  <chr>             <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>\n",
            "1 Wolverhampton       102      121      133      142      151      188      246\n",
            "2 Worcestershire      473      514      563      615      622      671      724\n",
            "3 York                 65       61       90       70       62       90       74\n",
            "4 Total             33464    35061    35949    37178    37632    38560    42341\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_fn425nlsWxA"
      },
      "source": [
        "## Calculate year on year changes\n",
        "\n",
        "Now we can add new columns with the year on year changes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "NieHEaQ5st3n",
        "outputId": "dc59106e-b535-47d8-b09a-ce50b89e6ed3"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] \"la_name\" \"201516\"  \"201617\"  \"201718\"  \"201819\"  \"201920\"  \"202021\" \n",
            "[8] \"202122\" \n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#remind ourselves of the column names\n",
        "colnames(yr1_wide)"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#subtract 2021 from 2122 to get the change - and store in a new column\n",
        "yr1_wide['YOY21to22'] <- yr1_wide['202122'] - yr1_wide['202021']\n",
        "#show the results to check they tally\n",
        "yr1_wide[, c(1,7,8,9)]"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "ddmP1K7bQn1K",
        "outputId": "6b0b6008-a60c-4c9f-b0ce-a76a6e20bf5b"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 155 × 4\n",
            "# Groups:   la_name [155]\n",
            "   la_name                      `202021` `202122` YOY21to22\n",
            "   <chr>                           <dbl>    <dbl>     <dbl>\n",
            " 1 Barking and Dagenham              316      319         3\n",
            " 2 Barnet                            209      244        35\n",
            " 3 Barnsley                          107      143        36\n",
            " 4 Bath and North East Somerset      115      157        42\n",
            " 5 Bedford                           130      112       -18\n",
            " 6 Bexley                            200      219        19\n",
            " 7 Birmingham                        936     1114       178\n",
            " 8 Blackburn with Darwen             226      281        55\n",
            " 9 Blackpool                         178      182         4\n",
            "10 Bolton                            260      290        30\n",
            "# … with 145 more rows\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "J0Lp0P_8uvcU"
      },
      "source": [
        "### Calculate them as percentages\n",
        "\n",
        "Now add another column with those values as percentage changes."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "CgfKVlzG_Z8W",
        "outputId": "3f511fd7-bc6b-4c26-a369-061528cc20c4"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 155 × 5\n",
            "# Groups:   la_name [155]\n",
            "   la_name                      `202021` `202122` YOY21to22 YOYperc21to22\n",
            "   <chr>                           <dbl>    <dbl>     <dbl>         <dbl>\n",
            " 1 Barking and Dagenham              316      319         3       0.00949\n",
            " 2 Barnet                            209      244        35       0.167  \n",
            " 3 Barnsley                          107      143        36       0.336  \n",
            " 4 Bath and North East Somerset      115      157        42       0.365  \n",
            " 5 Bedford                           130      112       -18      -0.138  \n",
            " 6 Bexley                            200      219        19       0.095  \n",
            " 7 Birmingham                        936     1114       178       0.190  \n",
            " 8 Blackburn with Darwen             226      281        55       0.243  \n",
            " 9 Blackpool                         178      182         4       0.0225 \n",
            "10 Bolton                            260      290        30       0.115  \n",
            "# … with 145 more rows\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#divide the change by the older 2021 figure to get the % change - and store in a new column\n",
        "yr1_wide['YOYperc21to22'] <- yr1_wide['YOY21to22']/yr1_wide['202021']\n",
        "#show the results to check they tally - the first figure should be around 1% or 0.01 when rounded\n",
        "yr1_wide[, c(1,7,8,9,10)]"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "SGO6frC6ZCvR"
      },
      "source": [
        "## Repeat for other years\n",
        "\n",
        "Can we codify this process and loop to create the other columns?"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "I5Q2Fetc12XS",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "ed7f8e7f-2901-4639-ad29-bf494d297694"
      },
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "[1] 3\n",
            "[1] \"201617\"\n",
            "[1] \"201516\"\n",
            "[1] 4\n",
            "[1] \"201718\"\n",
            "[1] \"201617\"\n",
            "[1] 5\n",
            "[1] \"201819\"\n",
            "[1] \"201718\"\n",
            "[1] 6\n",
            "[1] \"201920\"\n",
            "[1] \"201819\"\n",
            "[1] 7\n",
            "[1] \"202021\"\n",
            "[1] \"201920\"\n"
          ]
        }
      ],
      "source": [
        "%%R\n",
        "#loop through the column names from 3rd to 7th position\n",
        "for (i in seq(3,7)){\n",
        "    print(i)\n",
        "    thisyr <- colnames(yr1_wide)[i]\n",
        "    previousyr <- colnames(yr1_wide)[i-1]\n",
        "    print(thisyr)\n",
        "    print(previousyr)\n",
        "    #subtract the previous year from the current one to get the change - and store \n",
        "    YOYchange <- yr1_wide[thisyr] - yr1_wide[previousyr]\n",
        "    YOYpercChange <- YOYchange/yr1_wide[previousyr]\n",
        "    #extract the two digits for the later year\n",
        "    toyr <- substr(thisyr,5,6)\n",
        "    fromyr <- substr(previousyr,5,6)\n",
        "    #create column names from these\n",
        "    colname <- paste0(\"YOY\",fromyr,\"to\",toyr)\n",
        "    perccolname <- paste0(\"YOYperc\",fromyr,\"to\",toyr)\n",
        "    #create a new column with that name and the values calculated\n",
        "    yr1_wide[colname] <- YOYchange\n",
        "    yr1_wide[perccolname] <- YOYpercChange\n",
        "}"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "yr1_wide"
      ],
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "id": "h66OTntbV-6c",
        "outputId": "bfc91eed-8ae6-4a56-cbbc-fc3c6fc18cf4"
      },
      "execution_count": null,
      "outputs": [
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "# A tibble: 155 × 20\n",
            "# Groups:   la_name [155]\n",
            "   la_name        `201516` `201617` `201718` `201819` `201920` `202021` `202122`\n",
            "   <chr>             <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>    <dbl>\n",
            " 1 Barking and D…      241      299      285      287      282      316      319\n",
            " 2 Barnet              254      239      241      250      234      209      244\n",
            " 3 Barnsley            114      147      141      125      129      107      143\n",
            " 4 Bath and Nort…       82      101       95      118       96      115      157\n",
            " 5 Bedford              93       84      115      110      107      130      112\n",
            " 6 Bexley              209      215      213      247      245      200      219\n",
            " 7 Birmingham          765      836      868      900      899      936     1114\n",
            " 8 Blackburn wit…      181      200      199      216      224      226      281\n",
            " 9 Blackpool           146      139      170      180      203      178      182\n",
            "10 Bolton              142      178      218      214      260      260      290\n",
            "# … with 145 more rows, and 12 more variables: YOY21to22 <dbl>,\n",
            "#   YOYperc21to22 <dbl>, YOY16to17 <dbl>, YOYperc16to17 <dbl>, YOY17to18 <dbl>,\n",
            "#   YOYperc17to18 <dbl>, YOY18to19 <dbl>, YOYperc18to19 <dbl>, YOY19to20 <dbl>,\n",
            "#   YOYperc19to20 <dbl>, YOY20to21 <dbl>, YOYperc20to21 <dbl>\n"
          ]
        }
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "gjqCATHqgU4G"
      },
      "source": [
        "## Export results\n",
        "\n",
        "Let's export as a CSV."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "6E7eDPPYZtAn"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#export as a CSV\n",
        "write.csv(yr1_wide, \"yr1_analysis.csv\")"
      ]
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}