Clean_Most_Danish_Municipalities.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "# import shapefile as shp\n",
    "# import matplotlib.pyplot as plt\n",
    "# import seaborn as sns\n",
    "import geopandas as gpd\n",
    "from shapely.geometry import MultiPolygon,Polygon, LineString, Point\n",
    "from collections import defaultdict"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "data = gpd.read_file('kommuner.geojson')\n",
    "# https://github.com/Neogeografen/dagi/tree/master/geojson"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "results = pd.read_csv('../../export_okt1_22/WCA_export_Results.tsv',sep='\\t')\n",
    "comps = pd.read_csv('../../export_okt1_22/WCA_export_Competitions.tsv',sep='\\t')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "comps = comps[comps['countryId']=='Denmark']\n",
    "comps.rename(columns={'id':'competitionId'},inplace=True)\n",
    "dk_res = comps.merge(results,left_on='competitionId',right_on='competitionId')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getCompAndCords():\n",
    "    personToComps = defaultdict(set)\n",
    "    compCords = {}\n",
    "    for val in dk_res.values:\n",
    "        personToComps[val[27]].add(val[0])\n",
    "        compCords[val[0]] = (val[19]/10e5,val[20]/10e5)\n",
    "    return compCords, personToComps"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getKommunerByPerson(personToComps,compCords):\n",
    "    komcount = defaultdict(set) # Person -> set\n",
    "    for per in personToComps:\n",
    "        for comp in personToComps[per]:\n",
    "            x,y = compCords[comp]\n",
    "            for idx,val in enumerate(data.geometry):\n",
    "                if Polygon(val).contains(Point(y,x)):\n",
    "                    komcount[per].add(data.KOMNAVN[idx])\n",
    "                    break\n",
    "    return komcount"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "compCords, personToComps = getCompAndCords()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "komcount = getKommunerByPerson(personToComps,compCords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "overview = [(per,len(komcount[per])) for per in komcount]\n",
    "overview.sort(key=lambda x:x[1],reverse=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "('2011TRON01', 29)\n",
      "('2006BUUS01', 28)\n",
      "('2008ANDE02', 26)\n",
      "('2013EGDA02', 24)\n",
      "('2013EGDA01', 24)\n",
      "('2015KANS01', 23)\n",
      "('2010BECK01', 22)\n",
      "('2015JORG01', 22)\n",
      "('2012GOOD02', 21)\n",
      "('2008NIEL01', 21)\n",
      "('2006BUUS02', 20)\n",
      "('2014DETL01', 20)\n",
      "('2017JARN01', 19)\n",
      "('2010MOFF02', 19)\n",
      "('2018KJOL01', 17)\n",
      "('2010NIEL01', 16)\n",
      "('2017BYRN02', 16)\n",
      "('2017BYRN01', 16)\n",
      "('2017EXNE01', 16)\n",
      "('2013NIEL02', 16)\n"
     ]
    }
   ],
   "source": [
    "for val in overview[:20]:\n",
    "    print(val) # Print top 20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "def getCompsPerKommune(compCords):\n",
    "    compsPerKommune = defaultdict(int) # name -> count\n",
    "    for comp in compCords:\n",
    "        x,y = compCords[comp]\n",
    "        for idx,val in enumerate(data.geometry):\n",
    "            if Polygon(val).contains(Point(y,x)):\n",
    "                compsPerKommune[data.KOMNAVN[idx]] += 1\n",
    "                break\n",
    "    return compsPerKommune"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "compsPerKommune = getCompsPerKommune(compCords)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "overviewK = [(per,compsPerKommune[per]) for per in compsPerKommune]\n",
    "overviewK.sort(key=lambda x:x[1],reverse=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[('Roskilde', 12),\n",
       " ('Tårnby', 9),\n",
       " ('Ballerup', 8),\n",
       " ('Odense', 8),\n",
       " ('Hvidovre', 7),\n",
       " ('Aarhus', 5),\n",
       " ('Lejre', 5),\n",
       " ('Svendborg', 5),\n",
       " ('København', 4),\n",
       " ('Faaborg-Midtfyn', 4),\n",
       " ('Lyngby-Taarbæk', 3),\n",
       " ('Slagelse', 3),\n",
       " ('Frederikshavn', 3),\n",
       " ('Furesø', 2),\n",
       " ('Aalborg', 1),\n",
       " ('Assens', 1),\n",
       " ('Nyborg', 1),\n",
       " ('Vejle', 1),\n",
       " ('Kolding', 1),\n",
       " ('Hillerød', 1),\n",
       " ('Allerød', 1),\n",
       " ('Brøndby', 1),\n",
       " ('Fredensborg', 1),\n",
       " ('Guldborgsund', 1),\n",
       " ('Fredericia', 1),\n",
       " ('Greve', 1),\n",
       " ('Frederiksberg', 1),\n",
       " ('Odsherred', 1),\n",
       " ('Rebild', 1),\n",
       " ('Ringsted', 1),\n",
       " ('Skanderborg', 1),\n",
       " ('Haderslev', 1),\n",
       " ('Varde', 1),\n",
       " ('Holstebro', 1)]"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "overviewK"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.10"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}