Challenges vs solution.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Challenges 1>* solutions\n",
    "\n",
    "From the hackathon website https://wirvsvirushackathon.devpost.com/"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 333,
   "metadata": {},
   "outputs": [],
   "source": [
    "import requests\n",
    "import json\n",
    "import pandas as pd\n",
    "import altair as alt\n",
    "from tqdm import tqdm\n",
    "from bs4 import BeautifulSoup"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 334,
   "metadata": {},
   "outputs": [],
   "source": [
    "BASEURL = 'https://wirvsvirushackathon.devpost.com/'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get the project gallery"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 335,
   "metadata": {},
   "outputs": [],
   "source": [
    "gallery = requests.get(f\"{BASEURL}project-gallery\")\n",
    "gallery_soup = BeautifulSoup(gallery.text, 'html.parser')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Get the category pages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '1 - lebensmittel-matching'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '2- tauschplattform'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '3- arbeiterinnenverteilung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '4 - verteilung von helferinnen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '5 - hilfsmittelverteilung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '6 - medizingeräteherstellung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '7 - e-kinderbetreuung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '8 - corona tracking'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '9 - e-anträge'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '10 - analoge unterstützung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '11 - infektionsfall-Übermittlung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '12 - social distancing'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '13 - tafel-reorganisation'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '14 - vulnerable gruppen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '15 - e-sport'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '16 - lokale unternehmen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '17 - supermarkt status'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '18 - mental health'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '19 - e-learning'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '20 - corona-testprozesse'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '21 - krisenkommunikation'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '22 -kreative berufe'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '23 - generelle kommunikation'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '24 - krankenhäuser'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '25 - e-kulturangebote'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '26 - home office'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '27 - patientenaustausch'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '28 - kreativer gesundheitsschutz'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '29 - landwirtschaft'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '30 - gefährdete gruppen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '31 - digitale krankheits-anamnese'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '32 - krisenbewältigung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '33 - lebensnotwendige dienstleistungen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '34 - nachbarschaftshilfe'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '35 - gesellschaftliche fragen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '36 - grenzkontrollen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '37 - Öffentliche verwaltung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '38 - daten'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '39 - staatliche kommunikation'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '40 - medizinisches personal'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '41 - forschung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '42 - rechtliche fragen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '43 - medizinische versorgung'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '44 - flatten the curve'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '45 - haustiere'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]', '46 - gamification'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '47 - wirtschaftliche auswirkungen'),\n",
       " ('filter[bitte wählt eure herausforderung aus:][]',\n",
       "  '48 - finanzelle unterstützung'),\n",
       " ('filter[in welche kategorie ordnet ihr euer projekt ein?][]',\n",
       "  'neuentwicklung'),\n",
       " ('filter[in welche kategorie ordnet ihr euer projekt ein?][]',\n",
       "  'weiterentwicklung bestehender lösung'),\n",
       " ('filter[in welche kategorie ordnet ihr euer projekt ein?][]',\n",
       "  'kommerzielle lösung')]"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[(herausforderung['name'], herausforderung['value']) for herausforderung\n",
    " in gallery_soup.select('.filter-submissions > .panel > ul > li > label > input')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "metadata": {},
   "outputs": [],
   "source": [
    "herausforderung = [requests.get(f\"{BASEURL}submissions/search\", {k: v}) for k, v\n",
    "                   in [(herausforderung['name'], herausforderung['value']) for herausforderung\n",
    "                       in gallery_soup.select('.filter-submissions > .panel:nth-of-type(1) > ul > li > label > input')]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "A bit of testing for to see we got what we expected"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 336,
   "metadata": {},
   "outputs": [],
   "source": [
    "assert len(herausforderung) == 48\n",
    "assert all([h.ok for h in herausforderung])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 337,
   "metadata": {},
   "outputs": [],
   "source": [
    "herausforderung_soups = [BeautifulSoup(h.text, 'html.parser') for h in herausforderung]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# How many entries per herausforderung?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 143,
   "metadata": {
    "collapsed": true,
    "jupyter": {
     "outputs_hidden": true
    }
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[62,\n",
       " 8,\n",
       " 28,\n",
       " 14,\n",
       " 23,\n",
       " 25,\n",
       " 24,\n",
       " 96,\n",
       " 19,\n",
       " 33,\n",
       " 23,\n",
       " 98,\n",
       " 6,\n",
       " 13,\n",
       " 13,\n",
       " 103,\n",
       " 40,\n",
       " 75,\n",
       " 76,\n",
       " 35,\n",
       " 27,\n",
       " 9,\n",
       " 51,\n",
       " 53,\n",
       " 37,\n",
       " 27,\n",
       " 5,\n",
       " 24,\n",
       " 12,\n",
       " 15,\n",
       " 37,\n",
       " 14,\n",
       " 18,\n",
       " 45,\n",
       " 25,\n",
       " 7,\n",
       " 10,\n",
       " 71,\n",
       " 23,\n",
       " 24,\n",
       " 9,\n",
       " 4,\n",
       " 23,\n",
       " 19,\n",
       " 6,\n",
       " 56,\n",
       " 24,\n",
       " 9,\n",
       " 1039,\n",
       " 196,\n",
       " 49]"
      ]
     },
     "execution_count": 143,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "[int(h.select('.items_info > p > b:nth-of-type(2)')[0].text) for h in herausforderung_soups]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Align the titles with counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 338,
   "metadata": {},
   "outputs": [],
   "source": [
    "solution_counts = pd.DataFrame(\n",
    "    zip([h['value'] for h in gallery_soup.select('.filter-submissions > .panel > ul > li > label > input')],\n",
    "        [int(h.select('.items_info > p > b:nth-of-type(2)')[0].text) for h in herausforderung_soups]),\n",
    "    columns=['name', 'count'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 339,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1 - lebensmittel-matching</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2- tauschplattform</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3- arbeiterinnenverteilung</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4 - verteilung von helferinnen</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5 - hilfsmittelverteilung</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                             name  count\n",
       "0       1 - lebensmittel-matching     62\n",
       "1              2- tauschplattform      8\n",
       "2      3- arbeiterinnenverteilung     28\n",
       "3  4 - verteilung von helferinnen     14\n",
       "4       5 - hilfsmittelverteilung     23"
      ]
     },
     "execution_count": 339,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "solution_counts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 340,
   "metadata": {},
   "outputs": [],
   "source": [
    "solution_counts.index = [s[0].strip() for s in solution_counts.name.str.split('-')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 341,
   "metadata": {},
   "outputs": [],
   "source": [
    "solution_counts.name = [s[1].strip() for s in solution_counts.name.str.split('-', 1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 342,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>lebensmittel-matching</td>\n",
       "      <td>62</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>tauschplattform</td>\n",
       "      <td>8</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>arbeiterinnenverteilung</td>\n",
       "      <td>28</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>verteilung von helferinnen</td>\n",
       "      <td>14</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>hilfsmittelverteilung</td>\n",
       "      <td>23</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         name  count\n",
       "1       lebensmittel-matching     62\n",
       "2             tauschplattform      8\n",
       "3     arbeiterinnenverteilung     28\n",
       "4  verteilung von helferinnen     14\n",
       "5       hilfsmittelverteilung     23"
      ]
     },
     "execution_count": 342,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "solution_counts.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 343,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-014937d655d443e69b1ecad76e55a0e1\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-014937d655d443e69b1ecad76e55a0e1\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-014937d655d443e69b1ecad76e55a0e1\");\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}}, \"data\": {\"name\": \"data-c45bf8f157cf870d78ed7c04e51c0fa8\"}, \"mark\": \"bar\", \"encoding\": {\"tooltip\": [{\"type\": \"nominal\", \"field\": \"name\"}, {\"type\": \"quantitative\", \"field\": \"count\"}], \"x\": {\"type\": \"ordinal\", \"field\": \"name\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"count\"}}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-c45bf8f157cf870d78ed7c04e51c0fa8\": [{\"index\": \"1\", \"name\": \"lebensmittel-matching\", \"count\": 62}, {\"index\": \"2\", \"name\": \"tauschplattform\", \"count\": 8}, {\"index\": \"3\", \"name\": \"arbeiterinnenverteilung\", \"count\": 28}, {\"index\": \"4\", \"name\": \"verteilung von helferinnen\", \"count\": 14}, {\"index\": \"5\", \"name\": \"hilfsmittelverteilung\", \"count\": 23}, {\"index\": \"6\", \"name\": \"medizinger\\u00e4teherstellung\", \"count\": 25}, {\"index\": \"7\", \"name\": \"e-kinderbetreuung\", \"count\": 24}, {\"index\": \"8\", \"name\": \"corona tracking\", \"count\": 96}, {\"index\": \"9\", \"name\": \"e-antr\\u00e4ge\", \"count\": 19}, {\"index\": \"10\", \"name\": \"analoge unterst\\u00fctzung\", \"count\": 33}, {\"index\": \"11\", \"name\": \"infektionsfall-\\u00dcbermittlung\", \"count\": 23}, {\"index\": \"12\", \"name\": \"social distancing\", \"count\": 98}, {\"index\": \"13\", \"name\": \"tafel-reorganisation\", \"count\": 6}, {\"index\": \"14\", \"name\": \"vulnerable gruppen\", \"count\": 13}, {\"index\": \"15\", \"name\": \"e-sport\", \"count\": 13}, {\"index\": \"16\", \"name\": \"lokale unternehmen\", \"count\": 103}, {\"index\": \"17\", \"name\": \"supermarkt status\", \"count\": 40}, {\"index\": \"18\", \"name\": \"mental health\", \"count\": 75}, {\"index\": \"19\", \"name\": \"e-learning\", \"count\": 76}, {\"index\": \"20\", \"name\": \"corona-testprozesse\", \"count\": 35}, {\"index\": \"21\", \"name\": \"krisenkommunikation\", \"count\": 27}, {\"index\": \"22\", \"name\": \"kreative berufe\", \"count\": 9}, {\"index\": \"23\", \"name\": \"generelle kommunikation\", \"count\": 51}, {\"index\": \"24\", \"name\": \"krankenh\\u00e4user\", \"count\": 53}, {\"index\": \"25\", \"name\": \"e-kulturangebote\", \"count\": 37}, {\"index\": \"26\", \"name\": \"home office\", \"count\": 27}, {\"index\": \"27\", \"name\": \"patientenaustausch\", \"count\": 5}, {\"index\": \"28\", \"name\": \"kreativer gesundheitsschutz\", \"count\": 24}, {\"index\": \"29\", \"name\": \"landwirtschaft\", \"count\": 12}, {\"index\": \"30\", \"name\": \"gef\\u00e4hrdete gruppen\", \"count\": 15}, {\"index\": \"31\", \"name\": \"digitale krankheits-anamnese\", \"count\": 37}, {\"index\": \"32\", \"name\": \"krisenbew\\u00e4ltigung\", \"count\": 14}, {\"index\": \"33\", \"name\": \"lebensnotwendige dienstleistungen\", \"count\": 18}, {\"index\": \"34\", \"name\": \"nachbarschaftshilfe\", \"count\": 45}, {\"index\": \"35\", \"name\": \"gesellschaftliche fragen\", \"count\": 25}, {\"index\": \"36\", \"name\": \"grenzkontrollen\", \"count\": 7}, {\"index\": \"37\", \"name\": \"\\u00d6ffentliche verwaltung\", \"count\": 10}, {\"index\": \"38\", \"name\": \"daten\", \"count\": 71}, {\"index\": \"39\", \"name\": \"staatliche kommunikation\", \"count\": 23}, {\"index\": \"40\", \"name\": \"medizinisches personal\", \"count\": 24}, {\"index\": \"41\", \"name\": \"forschung\", \"count\": 9}, {\"index\": \"42\", \"name\": \"rechtliche fragen\", \"count\": 4}, {\"index\": \"43\", \"name\": \"medizinische versorgung\", \"count\": 23}, {\"index\": \"44\", \"name\": \"flatten the curve\", \"count\": 19}, {\"index\": \"45\", \"name\": \"haustiere\", \"count\": 6}, {\"index\": \"46\", \"name\": \"gamification\", \"count\": 56}, {\"index\": \"47\", \"name\": \"wirtschaftliche auswirkungen\", \"count\": 24}, {\"index\": \"48\", \"name\": \"finanzelle unterst\\u00fctzung\", \"count\": 9}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.Chart(...)"
      ]
     },
     "execution_count": 343,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "alt.Chart(solution_counts.reset_index()).mark_bar().encode(\n",
    "    x='name:O',\n",
    "    y='count',\n",
    "    tooltip=['name', 'count']\n",
    ")"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Solution counts"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 344,
   "metadata": {},
   "outputs": [],
   "source": [
    "challenge_counts = pd.read_csv('categories_count_with_ids.csv', sep=';', index_col='id')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 345,
   "metadata": {},
   "outputs": [],
   "source": [
    "challenge_counts.index = solution_counts.index.astype(str, copy = False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 346,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>count</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Analoge Unterstützung</td>\n",
       "      <td>23</td>\n",
       "      <td>analog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>e-Learning</td>\n",
       "      <td>42</td>\n",
       "      <td>education</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>e-Sport</td>\n",
       "      <td>8</td>\n",
       "      <td>fun</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Finanzelle Unterstützung</td>\n",
       "      <td>1</td>\n",
       "      <td>economy</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Flatten the Curve</td>\n",
       "      <td>1</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        name  count   category\n",
       "1      Analoge Unterstützung     23     analog\n",
       "10                e-Learning     42  education\n",
       "11                   e-Sport      8        fun\n",
       "12  Finanzelle Unterstützung      1    economy\n",
       "13         Flatten the Curve      1        NaN"
      ]
     },
     "execution_count": 346,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "challenge_counts.sort_index().head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 347,
   "metadata": {},
   "outputs": [],
   "source": [
    "df = pd.concat([\n",
    "        solution_counts.drop(['name'], axis=1).rename({'count': 's_count'}, axis=1),\n",
    "        challenge_counts.rename({'count': 'c_count'}, axis=1)],\n",
    "    axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 348,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>s_count</th>\n",
       "      <th>name</th>\n",
       "      <th>c_count</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>62</td>\n",
       "      <td>Analoge Unterstützung</td>\n",
       "      <td>23</td>\n",
       "      <td>analog</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>ArbeiterInnenverteilung</td>\n",
       "      <td>46</td>\n",
       "      <td>matching and distribution</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>28</td>\n",
       "      <td>Corona Tracking</td>\n",
       "      <td>58</td>\n",
       "      <td>tracking</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>14</td>\n",
       "      <td>Corona-Testprozesse</td>\n",
       "      <td>22</td>\n",
       "      <td>information exchange</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>23</td>\n",
       "      <td>Daten</td>\n",
       "      <td>17</td>\n",
       "      <td>data</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   s_count                     name  c_count                   category\n",
       "1       62    Analoge Unterstützung       23                     analog\n",
       "2        8  ArbeiterInnenverteilung       46  matching and distribution\n",
       "3       28          Corona Tracking       58                   tracking\n",
       "4       14      Corona-Testprozesse       22       information exchange\n",
       "5       23                    Daten       17                       data"
      ]
     },
     "execution_count": 348,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 349,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.to_csv('challenges_and_solution_counts.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 351,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "\n",
       "<div id=\"altair-viz-dab8c40606704ebb8289279ea74ca190\"></div>\n",
       "<script type=\"text/javascript\">\n",
       "  (function(spec, embedOpt){\n",
       "    let outputDiv = document.currentScript.previousElementSibling;\n",
       "    if (outputDiv.id !== \"altair-viz-dab8c40606704ebb8289279ea74ca190\") {\n",
       "      outputDiv = document.getElementById(\"altair-viz-dab8c40606704ebb8289279ea74ca190\");\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}}, \"layer\": [{\"mark\": \"line\", \"encoding\": {\"x\": {\"type\": \"nominal\", \"field\": \"name\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"c_count\"}}}, {\"mark\": {\"type\": \"line\", \"color\": \"hotpink\"}, \"encoding\": {\"x\": {\"type\": \"nominal\", \"field\": \"name\"}, \"y\": {\"type\": \"quantitative\", \"field\": \"s_count\"}}}], \"data\": {\"name\": \"data-4906f82b79b89aa9cb6ef6c4e15656f6\"}, \"$schema\": \"https://vega.github.io/schema/vega-lite/v4.8.1.json\", \"datasets\": {\"data-4906f82b79b89aa9cb6ef6c4e15656f6\": [{\"index\": \"1\", \"s_count\": 62, \"name\": \"Analoge Unterst\\u00fctzung\", \"c_count\": 23, \"category\": \"analog\"}, {\"index\": \"2\", \"s_count\": 8, \"name\": \"ArbeiterInnenverteilung\", \"c_count\": 46, \"category\": \"matching and distribution\"}, {\"index\": \"3\", \"s_count\": 28, \"name\": \"Corona Tracking\", \"c_count\": 58, \"category\": \"tracking\"}, {\"index\": \"4\", \"s_count\": 14, \"name\": \"Corona-Testprozesse\", \"c_count\": 22, \"category\": \"information exchange\"}, {\"index\": \"5\", \"s_count\": 23, \"name\": \"Daten\", \"c_count\": 17, \"category\": \"data\"}, {\"index\": \"6\", \"s_count\": 25, \"name\": \"Digitale Krankheits-Anamnese\", \"c_count\": 14, \"category\": \"information exchange\"}, {\"index\": \"7\", \"s_count\": 24, \"name\": \"e-Antr\\u00e4ge\", \"c_count\": 20, \"category\": \"buerocracy\"}, {\"index\": \"8\", \"s_count\": 96, \"name\": \"e-Kinderbetreuung\", \"c_count\": 11, \"category\": \"care\"}, {\"index\": \"9\", \"s_count\": 19, \"name\": \"e-Kulturangebote\", \"c_count\": 19, \"category\": \"culture\"}, {\"index\": \"10\", \"s_count\": 33, \"name\": \"e-Learning\", \"c_count\": 42, \"category\": \"education\"}, {\"index\": \"11\", \"s_count\": 23, \"name\": \"e-Sport\", \"c_count\": 8, \"category\": \"fun\"}, {\"index\": \"12\", \"s_count\": 98, \"name\": \"Finanzelle Unterst\\u00fctzung\", \"c_count\": 1, \"category\": \"economy\"}, {\"index\": \"13\", \"s_count\": 6, \"name\": \"Flatten the Curve\", \"c_count\": 1, \"category\": null}, {\"index\": \"14\", \"s_count\": 13, \"name\": \"Forschung\", \"c_count\": 2, \"category\": \"research\"}, {\"index\": \"15\", \"s_count\": 13, \"name\": \"Gamification\", \"c_count\": 9, \"category\": \"fun\"}, {\"index\": \"16\", \"s_count\": 103, \"name\": \"Gef\\u00e4hrdete Gruppen\", \"c_count\": 14, \"category\": \"vulnerable groups\"}, {\"index\": \"17\", \"s_count\": 40, \"name\": \"Generelle Kommunikation\", \"c_count\": 29, \"category\": \"communication\"}, {\"index\": \"18\", \"s_count\": 75, \"name\": \"Gesellschaftliche Fragen\", \"c_count\": 5, \"category\": \"social\"}, {\"index\": \"19\", \"s_count\": 76, \"name\": \"Grenzkontrollen\", \"c_count\": 2, \"category\": \"borders\"}, {\"index\": \"20\", \"s_count\": 35, \"name\": \"Haustiere\", \"c_count\": 2, \"category\": \"animals\"}, {\"index\": \"21\", \"s_count\": 27, \"name\": \"Hilfsmittelverteilung\", \"c_count\": 24, \"category\": \"matching and distribution\"}, {\"index\": \"22\", \"s_count\": 9, \"name\": \"Home Office\", \"c_count\": 13, \"category\": null}, {\"index\": \"23\", \"s_count\": 51, \"name\": \"Infektionsfall-\\u00dcbermittlung\", \"c_count\": 8, \"category\": \"information exchange\"}, {\"index\": \"24\", \"s_count\": 53, \"name\": \"Krankenh\\u00e4user\", \"c_count\": 12, \"category\": \"hospitals\"}, {\"index\": \"25\", \"s_count\": 37, \"name\": \"Kreative Berufe\", \"c_count\": 4, \"category\": \"creativity\"}, {\"index\": \"26\", \"s_count\": 27, \"name\": \"Kreativer Gesundheitsschutz\", \"c_count\": 18, \"category\": \"creativity\"}, {\"index\": \"27\", \"s_count\": 5, \"name\": \"Krisenbew\\u00e4ltigung\", \"c_count\": 3, \"category\": null}, {\"index\": \"28\", \"s_count\": 24, \"name\": \"Krisenkommunikation\", \"c_count\": 16, \"category\": \"communication\"}, {\"index\": \"29\", \"s_count\": 12, \"name\": \"Landwirtschaft\", \"c_count\": 10, \"category\": \"economy\"}, {\"index\": \"30\", \"s_count\": 15, \"name\": \"Lebensmittel-Matching\", \"c_count\": 50, \"category\": \"matching and distribution\"}, {\"index\": \"31\", \"s_count\": 37, \"name\": \"Lebensnotwendige Dienstleistungen\", \"c_count\": 6, \"category\": \"services\"}, {\"index\": \"32\", \"s_count\": 14, \"name\": \"Lokale Unternehmen\", \"c_count\": 45, \"category\": \"economy\"}, {\"index\": \"33\", \"s_count\": 18, \"name\": \"Medizinger\\u00e4teherstellung\", \"c_count\": 18, \"category\": \"production\"}, {\"index\": \"34\", \"s_count\": 45, \"name\": \"Medizinische Versorgung\", \"c_count\": 7, \"category\": null}, {\"index\": \"35\", \"s_count\": 25, \"name\": \"Medizinisches Personal\", \"c_count\": 4, \"category\": \"matching and distribution\"}, {\"index\": \"36\", \"s_count\": 7, \"name\": \"Mental Health\", \"c_count\": 34, \"category\": \"care\"}, {\"index\": \"37\", \"s_count\": 10, \"name\": \"Nachbarschaftshilfe\", \"c_count\": 58, \"category\": \"matching and distribution\"}, {\"index\": \"38\", \"s_count\": 71, \"name\": \"\\u00d6ffentliche Verwaltung\", \"c_count\": 12, \"category\": \"buerocracy\"}, {\"index\": \"39\", \"s_count\": 23, \"name\": \"Patientenaustausch\", \"c_count\": 8, \"category\": \"matching and distribution\"}, {\"index\": \"40\", \"s_count\": 24, \"name\": \"Rechtliche Fragen\", \"c_count\": 2, \"category\": \"legal\"}, {\"index\": \"41\", \"s_count\": 9, \"name\": \"Social Distancing\", \"c_count\": 42, \"category\": \"analog\"}, {\"index\": \"42\", \"s_count\": 4, \"name\": \"Staatliche Kommunikation\", \"c_count\": 14, \"category\": \"communication\"}, {\"index\": \"43\", \"s_count\": 23, \"name\": \"Supermarkt Status\", \"c_count\": 19, \"category\": null}, {\"index\": \"44\", \"s_count\": 19, \"name\": \"Tafel-Reorganisation\", \"c_count\": 5, \"category\": \"matching and distribution\"}, {\"index\": \"45\", \"s_count\": 6, \"name\": \"Tauschplattform\", \"c_count\": 16, \"category\": \"matching and distribution\"}, {\"index\": \"46\", \"s_count\": 56, \"name\": \"Verteilung von HelferInnen\", \"c_count\": 6, \"category\": \"matching and distribution\"}, {\"index\": \"47\", \"s_count\": 24, \"name\": \"Vulnerable Gruppen\", \"c_count\": 5, \"category\": \"vulnerable groups\"}, {\"index\": \"48\", \"s_count\": 9, \"name\": \"Wirtschaftliche Auswirkungen\", \"c_count\": 4, \"category\": \"economy\"}]}}, {\"mode\": \"vega-lite\"});\n",
       "</script>"
      ],
      "text/plain": [
       "alt.LayerChart(...)"
      ]
     },
     "execution_count": 351,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "chart = alt.Chart(df.reset_index())\n",
    "\n",
    "chart.mark_line().encode(x='name', y='c_count') + chart.mark_line(color='hotpink').encode(x='name', y='s_count')"
   ]
  }
 ],
 "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.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}