generate_wlm_mapping_tables_from_monuments_all.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "import pandas as pd # Beautiful tool for data wrangling! e.g. '!pip install pandas' from a Notebook\n",
    "# See https://mariadb.com/blog/how-connect-python-programs-mariadb e.g. '!pip install mysql' from Notebook\n",
    "import MySQLdb \n",
    "import re\n",
    "from collections import Counter\n",
    "import os\n",
    "from collections import OrderedDict\n",
    "import pickle\n",
    "\n",
    "pd.set_option(\"display.max_rows\",35) # Useful when having large Pandas DataFrames like we do here"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "conn = MySQLdb.connect(user='mos', passwd='', db='monuments_db', charset='utf8', use_unicode=True)\n",
    "cursor = conn.cursor()\n",
    "cursor.execute(\"SET NAMES utf8\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "# Load full table into memory\n",
    "sql = \"SELECT * FROM monuments_all\"\n",
    "df = pd.io.sql.read_sql(conn.escape_string(sql), conn)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Test one country output"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "['dz',\n",
       " 'ar',\n",
       " 'wikipedia',\n",
       " '01-002',\n",
       " 'dz',\n",
       " 'DZ-01',\n",
       " '',\n",
       " None,\n",
       " None,\n",
       " 'ساحة الحرية تميمون',\n",
       " '',\n",
       " 'DZ-01',\n",
       " 29.256001000000001,\n",
       " 0.236378,\n",
       " 585.0,\n",
       " 5.0,\n",
       " '',\n",
       " '',\n",
       " '//ar.wikipedia.org/w/index.php?title=%D9%82%D8%A7%D8%A6%D9%85%D8%A9_%D8%A7%D9%84%D9%85%D9%88%D8%A7%D9%82%D8%B9_%D9%88%D8%A7%D9%84%D9%85%D8%B9%D8%A7%D9%84%D9%85_%D8%A7%D9%84%D9%85%D8%B5%D9%86%D9%81%D8%A9_%D9%81%D9%8A_%D9%88%D9%84%D8%A7%D9%8A%D8%A9_%D8%A3%D8%AF%D8%B1%D8%A7%D8%B1_%28%D8%A7%D9%84%D8%AC%D8%B2%D8%A7%D8%A6%D8%B1%29&oldid=18593163',\n",
       " Timestamp('2016-06-08 10:25:15'),\n",
       " '',\n",
       " '']"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "dz = df[df[\"country\"] == \"dz\"]\n",
    "list(dz.iloc[1])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "metadata": {
    "collapsed": false
   },
   "outputs": [],
   "source": [
    "def create_mapping_tables_from_monuments_all(df, code):\n",
    "    \"\"\"Takes a Pandas DataFrame object together with a country code and writes wikitables\n",
    "    to files named after the table they are produced from.\n",
    "    \"\"\"\n",
    "    country = df[df[\"country\"] == code]\n",
    "    country_fields = list(country.keys())\n",
    "    country_values = list(country.iloc[0]) # get first row in table as example data from \n",
    "    \n",
    "    h1 = \"= Non-standardized fields from country \" + code + \" in monuments_all =\\n\"\n",
    "    table_header = '{| class=\"wikitable\" style=\"width: 675px;\\n'\n",
    "    table_name = '|+ '+code + \"\\n\"\n",
    "    # create table columns\n",
    "    table_columns = \"! heritage field\\n! example value\\n! Wikidata property\\n! Conversion\\n! Comment\\n|-\\n\"\n",
    "    table_rows = []\n",
    "    for (field, value) in zip(country_fields, country_values):\n",
    "        row=\"| \"+ str(field) + \"\\n| \" + str(value) + \"\\n|\\n| \\n| \\n|-\\n\"\n",
    "        table_rows.append(row)\n",
    "    \n",
    "    table_rows_str = \"\".join(table_rows)\n",
    "    # Fill in examples values from the first record in the table\n",
    "    table_footer = \"\\n|}\"\n",
    "    wikitable = table_header + table_name + table_columns + table_rows_str[:-1] + table_footer\n",
    "    \n",
    "    if os.path.isdir(\"./mappingtables\"):\n",
    "        out = open(\"./mappingtables/\" + code + \".mappingtable\",\"w\")\n",
    "        out.write(wikitable)\n",
    "        out.flush()\n",
    "        \n",
    "        print(\"Directory ./mappingtables exists. Wrote file {}\".format(out.name))\n",
    "        out.close()\n",
    "    else:\n",
    "        os.mkdir(\"./mappingtables\")\n",
    "        with open(\"./mappingtables/\" + code + \".mappingtable\",\"w\") as out:\n",
    "            out.write(wikitable)\n",
    "            print(\"./mappingtables doesn't exist. Created it and wrote file {}\".format(out.name))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {
    "collapsed": false
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Directory ./mappingtables exists. Wrote file ./mappingtables/fr-object.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/gb-nir.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/de-he.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ua.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/cm.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ro.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/gh.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/np.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/de-nrw-bm.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/uy.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ad.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/cl.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/gb-eng.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/de-by.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/se-bbr.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/pk.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/by.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ke.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/it-88.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/nl-prov.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/us.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/jo.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/mt.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/us-ca.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/rs.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/it-bz.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/il.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/pa.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/dk-fortids.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ve.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ie.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ee.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/se-ship.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/dk-bygning.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/za.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/mx.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/am.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/nl-aw.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/at.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ru.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/se-arbetsl.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/fr.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/gb-sct.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/bo.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/be-bru.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/nl-gem.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/es.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/de-nrw-k.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/tn.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/gb-wls.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/cn.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/no.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/hu.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/be-vlg.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/az.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/pt.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/jp-nhs.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/aq.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/nl.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/cz.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ph.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ch.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/se-fornmin.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ch-old.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/sv.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/co.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ca.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/ar.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/hk.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/be-wal.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/dz.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/sk.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/th.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/pl.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/lu.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/in.mappingtable\n",
      "Directory ./mappingtables exists. Wrote file ./mappingtables/it.mappingtable\n"
     ]
    }
   ],
   "source": [
    "country_codes = pickle.load(open(\"./country_codes.pickle\", \"rb\"))\n",
    "\n",
    "for code in country_codes:\n",
    "    create_mapping_tables_from_monuments_all(df, code)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": [
    "languages = ['az', 'cs', 'da', 'ru', 'nl', 'it', 'fr', 'ca', 'hu', 'sv', 'lb', 'den', 'no',\\\n",
    "                 'es', 'sr', 'uk', 'de', 'ar', 'be-tarask', 'he', 'en', 'hy', 'gl', 'pl', 'et', 'th', 'ro', 'sk', 'pt']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {
    "collapsed": true
   },
   "outputs": [],
   "source": []
  }
 ],
 "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.5.1"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 0
}