sen_covid_R3_eslNumbers.ipynb

{
  "cells": [
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "hDXxYmiQhT3U"
      },
      "source": [
        "# Analysing the impact of COVID on special educational needs - notebook 3: change by ESL numbers\n",
        "\n",
        "Data on pupil numbers is at https://explore-education-statistics.service.gov.uk/find-statistics/school-pupils-and-their-characteristics. The raw data can be downloaded, queries can be made, or particular queries re-loaded.  \n",
        "\n",
        "A zip file can be accessed from the 'Download all data' button at https://explore-education-statistics.service.gov.uk/find-statistics/school-pupils-and-their-characteristics#explore-data-and-files\n",
        "\n",
        "This would allow us not only to put SEN figures into the context of pupil numbers (alternative hypothesis: any rise in SEN numbers is simply due to a rise in pupil numbers for whom English is a second language)\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": "0a765d43-0b51-4204-8076-f74b8dc875c6"
      },
      "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/RtmpJYgCGV/downloaded_packages’\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "── Attaching packages ─────────────────────────────────────── tidyverse 1.3.2 ──\n",
            "✔ ggplot2 3.3.6     ✔ purrr   0.3.4\n",
            "✔ tibble  3.1.7     ✔ dplyr   1.0.9\n",
            "✔ tidyr   1.2.0     ✔ stringr 1.4.0\n",
            "✔ readr   2.1.2     ✔ forcats 0.5.1\n",
            "── Conflicts ────────────────────────────────────────── tidyverse_conflicts() ──\n",
            "✖ dplyr::filter() masks stats::filter()\n",
            "✖ dplyr::lag()    masks stats::lag()\n"
          ]
        },
        {
          "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/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/RtmpJYgCGV/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 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)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "iIJSwwX7_nfu",
        "colab": {
          "base_uri": "https://localhost:8080/"
        },
        "outputId": "8665ec89-6c3d-474d-9339-f8b0a2b96aab"
      },
      "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/RtmpJYgCGV/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/school-pupils-and-their-characteristics#explore-data-and-files\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/school-pupils-and-their-characteristics#explore-data-and-files\n",
        "zipurl <- \"https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files\"\n"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 949
        },
        "id": "IE-Uby99jXzU",
        "outputId": "7255acce-6c26-4612-f74a-deca0fd76aec"
      },
      "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/cf516998-1dc1-411d-8225-13f6320547fb/files'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Error in download.file(url, method = method, ...) : \n",
            "  cannot open URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files'\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In addition: \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: Warning message:\n",
            "\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: In download.file(url, method = method, ...) :\n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]: \n",
            " \n",
            "WARNING:rpy2.rinterface_lib.callbacks:R[write to console]:  cannot open URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files': HTTP status was '500 Internal Server Error'\n",
            "\n"
          ]
        },
        {
          "output_type": "stream",
          "name": "stdout",
          "text": [
            "\n",
            "Error in download.file(url, method = method, ...) : \n",
            "  cannot open URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files'\n"
          ]
        },
        {
          "output_type": "error",
          "ename": "RInterpreterError",
          "evalue": "ignored",
          "traceback": [
            "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
            "\u001b[0;31mRRuntimeError\u001b[0m                             Traceback (most recent call last)",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/ipython/rmagic.py\u001b[0m in \u001b[0;36meval\u001b[0;34m(self, code)\u001b[0m\n\u001b[1;32m    267\u001b[0m                 \u001b[0;31m# Need the newline in case the last line in code is a comment.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 268\u001b[0;31m                 \u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvisible\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mro\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mr\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"withVisible({%s\\n})\"\u001b[0m \u001b[0;34m%\u001b[0m \u001b[0mcode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    269\u001b[0m             \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mri\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0membedded\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mRRuntimeError\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0mexception\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/robjects/__init__.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, string)\u001b[0m\n\u001b[1;32m    437\u001b[0m         \u001b[0mp\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mrinterface\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mstring\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 438\u001b[0;31m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0meval\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mp\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    439\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mconversion\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrpy2py\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mres\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/robjects/functions.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    198\u001b[0m         return (super(SignatureTranslatedFunction, self)\n\u001b[0;32m--> 199\u001b[0;31m                 .__call__(*args, **kwargs))\n\u001b[0m\u001b[1;32m    200\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/robjects/functions.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    124\u001b[0m                 \u001b[0mnew_kwargs\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconversion\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mpy2rpy\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mv\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 125\u001b[0;31m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0msuper\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mFunction\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m__call__\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0mnew_args\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mnew_kwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    126\u001b[0m         \u001b[0mres\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconversion\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrpy2py\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mres\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/rinterface_lib/conversion.py\u001b[0m in \u001b[0;36m_\u001b[0;34m(*args, **kwargs)\u001b[0m\n\u001b[1;32m     44\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0m_\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m---> 45\u001b[0;31m         \u001b[0mcdata\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfunction\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m     46\u001b[0m         \u001b[0;31m# TODO: test cdata is of the expected CType\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/rinterface.py\u001b[0m in \u001b[0;36m__call__\u001b[0;34m(self, *args, **kwargs)\u001b[0m\n\u001b[1;32m    679\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0merror_occured\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 680\u001b[0;31m                 \u001b[0;32mraise\u001b[0m \u001b[0membedded\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mRRuntimeError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0m_rinterface\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_geterrmessage\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    681\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mres\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mRRuntimeError\u001b[0m: Error in download.file(url, method = method, ...) : \n  cannot open URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files'\n",
            "\nDuring handling of the above exception, another exception occurred:\n",
            "\u001b[0;31mRInterpreterError\u001b[0m                         Traceback (most recent call last)",
            "\u001b[0;32m<ipython-input-6-eee3afb07331>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[0;32m----> 1\u001b[0;31m \u001b[0mget_ipython\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mrun_cell_magic\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'R'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m''\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'#download the zip file from the url\\ndownloader::download(zipurl, dest=\"datasets.zip\", mode=\"wb\") \\n#unzip it \\nunzip (\"datasets.zip\", exdir = \"./\")\\n'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/IPython/core/interactiveshell.py\u001b[0m in \u001b[0;36mrun_cell_magic\u001b[0;34m(self, magic_name, line, cell)\u001b[0m\n\u001b[1;32m   2357\u001b[0m             \u001b[0;32mwith\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mbuiltin_trap\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2358\u001b[0m                 \u001b[0margs\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mmagic_arg_s\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcell\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 2359\u001b[0;31m                 \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mfn\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0margs\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   2360\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   2361\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m<decorator-gen-122>\u001b[0m in \u001b[0;36mR\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/IPython/core/magic.py\u001b[0m in \u001b[0;36m<lambda>\u001b[0;34m(f, *a, **k)\u001b[0m\n\u001b[1;32m    185\u001b[0m     \u001b[0;31m# but it's overkill for just that one bit of state.\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    186\u001b[0m     \u001b[0;32mdef\u001b[0m \u001b[0mmagic_deco\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 187\u001b[0;31m         \u001b[0mcall\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mlambda\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m:\u001b[0m \u001b[0mf\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m*\u001b[0m\u001b[0ma\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mk\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    188\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    189\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mcallable\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/ipython/rmagic.py\u001b[0m in \u001b[0;36mR\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m    781\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mstdout\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mendswith\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    782\u001b[0m                 \u001b[0mprint\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0me\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0merr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 783\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    784\u001b[0m         \u001b[0;32mfinally\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    785\u001b[0m             \u001b[0;32mif\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdevice\u001b[0m \u001b[0;32min\u001b[0m \u001b[0;34m[\u001b[0m\u001b[0;34m'png'\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m'svg'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/ipython/rmagic.py\u001b[0m in \u001b[0;36mR\u001b[0;34m(self, line, cell, local_ns)\u001b[0m\n\u001b[1;32m    761\u001b[0m                     \u001b[0mreturn_output\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0;32mFalse\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    762\u001b[0m             \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 763\u001b[0;31m                 \u001b[0mtext_result\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mresult\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvisible\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0meval\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mcode\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    764\u001b[0m                 \u001b[0mtext_output\u001b[0m \u001b[0;34m+=\u001b[0m \u001b[0mtext_result\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    765\u001b[0m                 \u001b[0;32mif\u001b[0m \u001b[0mvisible\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;32m/usr/local/lib/python3.7/dist-packages/rpy2/ipython/rmagic.py\u001b[0m in \u001b[0;36meval\u001b[0;34m(self, code)\u001b[0m\n\u001b[1;32m    271\u001b[0m                 \u001b[0mwarning_or_other_msg\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    272\u001b[0m                 raise RInterpreterError(code, str(exception),\n\u001b[0;32m--> 273\u001b[0;31m                                         warning_or_other_msg)\n\u001b[0m\u001b[1;32m    274\u001b[0m             \u001b[0mtext_output\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mself\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mflush\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    275\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mtext_output\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvalue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mvisible\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;36m0\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
            "\u001b[0;31mRInterpreterError\u001b[0m: Failed to parse and evaluate line '#download the zip file from the url\\ndownloader::download(zipurl, dest=\"datasets.zip\", mode=\"wb\") \\n#unzip it \\nunzip (\"datasets.zip\", exdir = \"./\")\\n'.\nR error message: \"Error in download.file(url, method = method, ...) : \\n  cannot open URL 'https://content.explore-education-statistics.service.gov.uk/api/releases/cf516998-1dc1-411d-8225-13f6320547fb/files'\""
          ]
        }
      ],
      "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": {
        "id": "PyNB5gxJh42t"
      },
      "outputs": [],
      "source": [
        "\n",
        "%%R \n",
        "#import the data extracted from the zip\n",
        "#this is the name in 2022\n",
        "data22 <- \"data/spc_pupils_ethnicity_and_language_.csv\"\n",
        "data <- readr::read_csv(data22)\n",
        "\n",
        "data"
      ]
    },
    {
      "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 only interested in primary schools."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "PeqR8-5lqdQQ"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf <- filter(data, phase_type_grouping == \"State-funded primary\")\n",
        "print(slcdf)"
      ]
    },
    {
      "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": {
        "id": "sJT2Jpd8QS26"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#show the unique values and their frequency\n",
        "table(slcdf['geographic_level'])"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "IIr7y0jYD0KK"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf_filter4 <- filter(slcdf, geographic_level == \"Local authority\")\n",
        "print(slcdf_filter4)"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Filter: All ethnicities only\n",
        "\n",
        "There are separate counts for each ethnicity, as well as a total. We only need the totals."
      ],
      "metadata": {
        "id": "xd72QQEwaEwV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#count the unique values in the time_identifier column\n",
        "print(table(slcdf_filter4['ethnicity']))"
      ],
      "metadata": {
        "id": "d8PiogoFaKhA"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf_filter4 <- filter(slcdf_filter4, ethnicity == \"Total\")\n",
        "print(slcdf_filter4)"
      ],
      "metadata": {
        "id": "9iAj4oZFaRvb"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "### Filter: Language\n",
        "\n",
        "There are four categories for language. We only need the row specifying how many are other than English."
      ],
      "metadata": {
        "id": "FboFz5NhZqdx"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#count the unique values in the time_identifier column\n",
        "print(table(slcdf_filter4['language']))"
      ],
      "metadata": {
        "id": "8XWYagFaZnxI"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#filter to where column values match specified string\n",
        "slcdf_filter4 <- filter(slcdf_filter4, language == \"Known or believed to be other than English\")\n",
        "print(slcdf_filter4)"
      ],
      "metadata": {
        "id": "oQDlvyoybKQf"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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": {
        "id": "yDcXFtZTBpQi"
      },
      "outputs": [],
      "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": {
        "id": "oTejBfZODbZD"
      },
      "outputs": [],
      "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 phase_type_grouping\n",
        "print(table(slcdf_filter4['ethnicity']))"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "1x3CQn-gFjNb"
      },
      "source": [
        "We remove that by using `select()` and a minus before the column we want to exclude.\n",
        "\n",
        "Note: we remove `headcount` because we are only interested in the percentage of pupils, and otherwise the `spread()` function below does not work."
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "-Phbicv4DUTW"
      },
      "outputs": [],
      "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",
        "                               ethnicity,\n",
        "                               headcount))\n",
        "slcdf_col_filter1"
      ]
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "bwdZKlzYpOQZ"
      },
      "source": [
        "## Reshape long to wide using `spread()`\n",
        "\n",
        "We now reshape to make that 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": {
        "id": "wPGGQ8mqoytr"
      },
      "outputs": [],
      "source": [
        "%%R\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",
        "esl_wide <- slcdf_col_filter1 %>% spread(time_period, percent_of_pupils)\n",
        "#show the results\n",
        "esl_wide[c(4,7,8,9,10,11,12,13)]"
      ]
    },
    {
      "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": {
        "id": "NieHEaQ5st3n"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#remind ourselves of the column names\n",
        "colnames(esl_wide)"
      ]
    },
    {
      "cell_type": "code",
      "execution_count": null,
      "metadata": {
        "id": "I5Q2Fetc12XS"
      },
      "outputs": [],
      "source": [
        "%%R\n",
        "#loop through the column names from 8th to 13th position\n",
        "for (i in seq(8,13)){\n",
        "    print(i)\n",
        "    thisyr <- colnames(esl_wide)[i]\n",
        "    previousyr <- colnames(esl_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 <- esl_wide[thisyr] - esl_wide[previousyr]\n",
        "    YOYpercChange <- YOYchange/esl_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",
        "    esl_wide[colname] <- YOYchange\n",
        "    esl_wide[perccolname] <- YOYpercChange\n",
        "}"
      ]
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "esl_wide"
      ],
      "metadata": {
        "id": "h66OTntbV-6c"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "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(esl_wide, \"esl_totals.csv\")"
      ]
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Merge with the SEN analysis\n",
        "\n",
        "A glance already tells us that there's been no pupil numbers increase to account for the increase in demand for SEN. \n",
        "\n",
        "But we still need to merge it to put those increases into context.\n",
        "\n",
        "We've uploaded the results of the analysis from the first notebook to Google Drive and published as a CSV, which is imported below. The data can also be generated by the other notebook and uploaded to the Files area on the left if needed."
      ],
      "metadata": {
        "id": "a8suEUlXErUP"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#store the URL for the CSV\n",
        "analysiscsvurl = \"https://docs.google.com/spreadsheets/d/e/2PACX-1vTRm8tw8O_QO_o4bk4Y_3sUlX0N5ukNrCeHj08RyShS3cAEmKTZdvB8g48zDHbl8l_dmDtjOUUrB12L/pub?gid=814230511&single=true&output=csv\"\n",
        "#import the CSV into a dataframe\n",
        "analysisdata <- readr::read_csv(analysiscsvurl)\n",
        "analysisdata"
      ],
      "metadata": {
        "id": "pqp4OoEOFNy7"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "Let's hope the LA names are consistent... if they are we should get a dataframe with 155 rows (the amount in the analysis CSV - there are 156 in the pupil numbers dataframe)."
      ],
      "metadata": {
        "id": "Y3X2OYWlGavV"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#merge the two dataframes on the la_name colum - all = F makes it an inner join\n",
        "merged_data <- merge(analysisdata, esl_wide, by = \"la_name\", all = F, suffixes = c(\"_language\",\"_esl\"))\n",
        "#check results\n",
        "print(nrow(merged_data))\n",
        "print(colnames(merged_data))"
      ],
      "metadata": {
        "id": "1Rq4di97Gc36"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Export merged results\n",
        "\n",
        "Let's export again."
      ],
      "metadata": {
        "id": "UBZnqOs4HTpA"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#export as a CSV\n",
        "write.csv(merged_data, \"merged_sen_esl_data.csv\")"
      ],
      "metadata": {
        "id": "L3HvjPpsIfws"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "## Compare SEN percentages with ESL percentages\n",
        "\n",
        "If SEN changes are 'caused' by a rise in pupils with English as a second language then there should be some sort of relationship. "
      ],
      "metadata": {
        "id": "Tj7zOeX6ImhL"
      }
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "print(colnames(merged_data))"
      ],
      "metadata": {
        "id": "hRqKnkZpJAxd"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#create a simple data frame with the 2021 figures\n",
        "percs2021 <- merged_data[,c(21,43)]"
      ],
      "metadata": {
        "id": "YRTiBQxkKO43"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#calculate a correlation coefficient, removing pairs where one number is missing\n",
        "#https://www.statmethods.net/stats/correlations.html\n",
        "cor(percs2021, use=\"pairwise.complete.obs\", method=\"pearson\")"
      ],
      "metadata": {
        "id": "wtCBVJUegFDz"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#create a simple data frame with the 2021 figures and the 2020 ESL change\n",
        "percs20to21 <- merged_data[,c(21,42)]\n",
        "#calculate a correlation coefficient, removing pairs where one number is missing\n",
        "#https://www.statmethods.net/stats/correlations.html\n",
        "cor(percs20to21, use=\"pairwise.complete.obs\", method=\"pearson\")"
      ],
      "metadata": {
        "id": "C2hP8IBrjAy3"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "code",
      "source": [
        "%%R\n",
        "#create a simple data frame with the 2021 figures and the 2020 ESL change\n",
        "percs19to21 <- merged_data[,c(21,41)]\n",
        "#calculate a correlation coefficient, removing pairs where one number is missing\n",
        "#https://www.statmethods.net/stats/correlations.html\n",
        "cor(percs19to21, use=\"pairwise.complete.obs\", method=\"pearson\")"
      ],
      "metadata": {
        "id": "KP7tW3brjMAg"
      },
      "execution_count": null,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "source": [
        "None of these correlations are even approaching significant."
      ],
      "metadata": {
        "id": "_auoC9OyjRS6"
      }
    }
  ],
  "metadata": {
    "colab": {
      "collapsed_sections": [],
      "provenance": [],
      "toc_visible": true
    },
    "kernelspec": {
      "display_name": "Python 3",
      "name": "python3"
    },
    "language_info": {
      "name": "python"
    }
  },
  "nbformat": 4,
  "nbformat_minor": 0
}