4_vadkarok_hr_parser.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 83,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T04:54:01.973517Z",
     "start_time": "2019-04-18T04:53:52.656512Z"
    }
   },
   "outputs": [],
   "source": [
    "import urllib.request as req\n",
    "#!pip install fastkml\n",
    "from fastkml import kml\n",
    "import pandas as pd\n",
    "import re"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 84,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: fastkml in c:\\programdata\\anaconda3\\lib\\site-packages (0.11)\n",
      "Requirement already satisfied: python-dateutil in c:\\programdata\\anaconda3\\lib\\site-packages (from fastkml) (2.8.1)\n",
      "Requirement already satisfied: pygeoif in c:\\programdata\\anaconda3\\lib\\site-packages (from fastkml) (0.7)\n",
      "Requirement already satisfied: six>=1.5 in c:\\programdata\\anaconda3\\lib\\site-packages (from python-dateutil->fastkml) (1.13.0)\n"
     ]
    }
   ],
   "source": [
    "!pip install fastkml"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 85,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:08.960146Z",
     "start_time": "2019-04-18T05:35:08.955131Z"
    }
   },
   "outputs": [],
   "source": [
    "# using the map from https://hargitamegye.ro/vadkarok/interaktiv-terkep.html\n",
    "# downloading in KML format\n",
    "url = 'http://www.google.com/maps/d/kml?forcekml=1&mid=1sIXAUcYDAkCBUzw_6DZelr1rt48'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 86,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:10.107128Z",
     "start_time": "2019-04-18T05:35:09.126127Z"
    }
   },
   "outputs": [],
   "source": [
    "fname, _ = req.urlretrieve(url)\n",
    "k = kml.KML()\n",
    "\n",
    "with open(fname, 'rt', encoding='utf-8') as f:\n",
    "    k.from_string(f.read().encode('utf-8'))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 87,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:10.194128Z",
     "start_time": "2019-04-18T05:35:10.111128Z"
    }
   },
   "outputs": [],
   "source": [
    "columns_raw = ['geo_loc', 'latitude', 'longitude', 'content', 'image']\n",
    "df = pd.DataFrame(columns=columns_raw)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 88,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:10.218129Z",
     "start_time": "2019-04-18T05:35:10.199130Z"
    }
   },
   "outputs": [],
   "source": [
    "folders = next(k.features()).features()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 89,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.236139Z",
     "start_time": "2019-04-18T05:35:10.222130Z"
    }
   },
   "outputs": [],
   "source": [
    "# parse KML, insert data into dataframe\n",
    "for f in folders:\n",
    "    for pm in f.features():\n",
    "        lon, lat, _ =  pm.geometry.coords[0]\n",
    "        media_ln = ''\n",
    "        if pm.extended_data is not None:\n",
    "            media_ln = pm.extended_data.elements[0].value\n",
    "        df = df.append({\n",
    "                    'geo_loc': pm.name, \n",
    "                    'latitude': lat, \n",
    "                    'longitude': lon, \n",
    "                    'content': pm.description, \n",
    "                    'image': media_ln}, \n",
    "                ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 90,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.250130Z",
     "start_time": "2019-04-18T05:35:12.243139Z"
    }
   },
   "outputs": [],
   "source": [
    "def remove_links(desc):\n",
    "    '''\n",
    "        Input: description extracted from KML files\n",
    "        Output: description without the links of attached images\n",
    "    '''\n",
    "    desc = desc.lstrip().rstrip()\n",
    "    desc = re.sub(r'<img[^>]*/>', '', desc)\n",
    "    desc = re.sub(r'<br>', '', desc)\n",
    "    return desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 91,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.278134Z",
     "start_time": "2019-04-18T05:35:12.254133Z"
    }
   },
   "outputs": [],
   "source": [
    "df['content'] = df['content'].apply(remove_links)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 92,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.344131Z",
     "start_time": "2019-04-18T05:35:12.287127Z"
    }
   },
   "outputs": [],
   "source": [
    "dp = re.compile('([0-9]{4})[. ]+(január|február|március|április|május|június|július|' + \n",
    "                'augusztus|szeptember|október|november|december|[0-9]{1,2})[. ]+([0-9]{1,2})')\n",
    "\n",
    "def standardize_date(date_groups):\n",
    "    '''\n",
    "        Input: result of regexp match with 3 groups, corresponding to (year, month, day)\n",
    "        Output: (year, month, day) tuple, where month is converted to numerical format\n",
    "    '''\n",
    "    months_hu = [u'január', u'február', u'március', \n",
    "                 u'április', u'május', u'június', \n",
    "                 u'július', u'augusztus', u'szeptember', \n",
    "                 u'október', u'november', u'december']\n",
    "    \n",
    "    y, m, d = date_groups\n",
    "    \n",
    "    try: \n",
    "        m = str(months_hu.index(m) + 1)\n",
    "    except:\n",
    "        return y, m, d\n",
    "    \n",
    "    return y, m, d\n",
    "\n",
    "\n",
    "def get_date(desc):\n",
    "    '''\n",
    "        Input: description extracted from KML\n",
    "        Output: Finds dates in the description, returns it in pandas datetime format\n",
    "    '''\n",
    "    res = dp.search(desc)\n",
    "    if res is not None:\n",
    "        date = standardize_date(res.groups())\n",
    "    else:\n",
    "        date = ''\n",
    "    \n",
    "    return pd.to_datetime('-'.join(date))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 93,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.481134Z",
     "start_time": "2019-04-18T05:35:12.351132Z"
    }
   },
   "outputs": [],
   "source": [
    "df['date'] = df['content'].apply(get_date)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 94,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.505133Z",
     "start_time": "2019-04-18T05:35:12.492131Z"
    }
   },
   "outputs": [],
   "source": [
    "lnp = re.compile('https?://[^ ,]*')\n",
    "\n",
    "def get_link(desc):\n",
    "    '''\n",
    "        Input: description extracted from KML\n",
    "        Output: finds links to external news sites\n",
    "    '''\n",
    "    res = lnp.search(desc)\n",
    "    if res is not None:\n",
    "        ln = desc[res.start():res.end()]\n",
    "    else: \n",
    "        ln = 'https://hargitamegye.ro/vadkarok/interaktiv-terkep.html'\n",
    "        \n",
    "    return ln"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 95,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.535141Z",
     "start_time": "2019-04-18T05:35:12.513130Z"
    }
   },
   "outputs": [],
   "source": [
    "df['link'] = df['content'].apply(get_link)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 96,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.560132Z",
     "start_time": "2019-04-18T05:35:12.542135Z"
    }
   },
   "outputs": [],
   "source": [
    "def clean_content(desc):\n",
    "    '''\n",
    "        Input: description extracted from KML\n",
    "        Output: removes links from description\n",
    "    '''\n",
    "    if 'http' in desc:\n",
    "        return desc[:desc.find('http')]\n",
    "    else:\n",
    "        return desc"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.601135Z",
     "start_time": "2019-04-18T05:35:12.565129Z"
    }
   },
   "outputs": [],
   "source": [
    "df['content'] = df['content'].apply(clean_content)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 98,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.685138Z",
     "start_time": "2019-04-18T05:35:12.605131Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "224\n"
     ]
    },
    {
     "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>geo_loc</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>content</th>\n",
       "      <th>image</th>\n",
       "      <th>date</th>\n",
       "      <th>link</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Siménfalva</td>\n",
       "      <td>46.337221</td>\n",
       "      <td>25.107726</td>\n",
       "      <td>Kórházba került 2019. január 6-án, vasárnap eg...</td>\n",
       "      <td></td>\n",
       "      <td>2019-01-06</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Kőrispatak</td>\n",
       "      <td>46.417871</td>\n",
       "      <td>24.968825</td>\n",
       "      <td>2019. március 22-én 11 óra körül életveszélyes...</td>\n",
       "      <td></td>\n",
       "      <td>2019-03-22</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Székelyszenterzsébet</td>\n",
       "      <td>46.290531</td>\n",
       "      <td>24.935896</td>\n",
       "      <td>2018. február 6-án egy székelyszenterzsébeti f...</td>\n",
       "      <td></td>\n",
       "      <td>2018-02-06</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Homoródremete</td>\n",
       "      <td>46.292650</td>\n",
       "      <td>25.402600</td>\n",
       "      <td>A Homoródszentmárton községhez tartozó Homoród...</td>\n",
       "      <td>https://doc-0k-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Szent Anna-tó</td>\n",
       "      <td>46.126892</td>\n",
       "      <td>25.890094</td>\n",
       "      <td>Újabb medvetámadás áldozata került kórházba Ha...</td>\n",
       "      <td>https://doc-0g-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>46.146075</td>\n",
       "      <td>25.856959</td>\n",
       "      <td>Újból emberre támadt a medve Hargita megyében,...</td>\n",
       "      <td>https://doc-10-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>46.147443</td>\n",
       "      <td>25.860907</td>\n",
       "      <td>Újabb medvetámadás történt július 24-én éjjel ...</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>46.143518</td>\n",
       "      <td>25.861250</td>\n",
       "      <td>2018. augusztus 5-én kora reggel a 28 éves tus...</td>\n",
       "      <td>https://doc-0k-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>2018-08-05</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>46.143697</td>\n",
       "      <td>25.852667</td>\n",
       "      <td>2018 augusztus 5-én kora reggel a 28 éves tusn...</td>\n",
       "      <td>https://doc-0o-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>2018-08-05</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Tarcsafalva</td>\n",
       "      <td>46.375859</td>\n",
       "      <td>25.130940</td>\n",
       "      <td>2018. augusztus 21-én emberre támadt a medve K...</td>\n",
       "      <td></td>\n",
       "      <td>2018-08-21</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Karcfalva</td>\n",
       "      <td>46.530569</td>\n",
       "      <td>25.761535</td>\n",
       "      <td>– Nyolc medve volt a kukoricásba! - meséli Pre...</td>\n",
       "      <td>https://doc-00-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Csíkszentkirály</td>\n",
       "      <td>46.312080</td>\n",
       "      <td>25.821061</td>\n",
       "      <td>Szeptember 21-én újabb medvetámadás történt Cs...</td>\n",
       "      <td>https://doc-0s-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Kissolymos</td>\n",
       "      <td>46.338384</td>\n",
       "      <td>24.960462</td>\n",
       "      <td>Medve harapott meg egy pásztort a Hargita megy...</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Csíkszereda</td>\n",
       "      <td>46.371689</td>\n",
       "      <td>25.794850</td>\n",
       "      <td>Két sérülthöz riasztották 2017. április 30-án,...</td>\n",
       "      <td></td>\n",
       "      <td>2017-04-30</td>\n",
       "      <td>https://szekelyhon.ro/aktualis/medve-tamadt-ke...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>Csíkdánfalva</td>\n",
       "      <td>46.515809</td>\n",
       "      <td>25.753395</td>\n",
       "      <td>Combján harapdált össze egy pásztort 2017. jún...</td>\n",
       "      <td>https://doc-04-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>2017-06-24</td>\n",
       "      <td>https://szekelyhon.ro/aktualis/nappal-tamadt-e...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 geo_loc   latitude  longitude  \\\n",
       "0             Siménfalva  46.337221  25.107726   \n",
       "1             Kőrispatak  46.417871  24.968825   \n",
       "2   Székelyszenterzsébet  46.290531  24.935896   \n",
       "3          Homoródremete  46.292650  25.402600   \n",
       "4          Szent Anna-tó  46.126892  25.890094   \n",
       "5            Tusnádfürdő  46.146075  25.856959   \n",
       "6            Tusnádfürdő  46.147443  25.860907   \n",
       "7            Tusnádfürdő  46.143518  25.861250   \n",
       "8            Tusnádfürdő  46.143697  25.852667   \n",
       "9            Tarcsafalva  46.375859  25.130940   \n",
       "10             Karcfalva  46.530569  25.761535   \n",
       "11       Csíkszentkirály  46.312080  25.821061   \n",
       "12            Kissolymos  46.338384  24.960462   \n",
       "13           Csíkszereda  46.371689  25.794850   \n",
       "14          Csíkdánfalva  46.515809  25.753395   \n",
       "\n",
       "                                              content  \\\n",
       "0   Kórházba került 2019. január 6-án, vasárnap eg...   \n",
       "1   2019. március 22-én 11 óra körül életveszélyes...   \n",
       "2   2018. február 6-án egy székelyszenterzsébeti f...   \n",
       "3   A Homoródszentmárton községhez tartozó Homoród...   \n",
       "4   Újabb medvetámadás áldozata került kórházba Ha...   \n",
       "5   Újból emberre támadt a medve Hargita megyében,...   \n",
       "6   Újabb medvetámadás történt július 24-én éjjel ...   \n",
       "7   2018. augusztus 5-én kora reggel a 28 éves tus...   \n",
       "8   2018 augusztus 5-én kora reggel a 28 éves tusn...   \n",
       "9   2018. augusztus 21-én emberre támadt a medve K...   \n",
       "10  – Nyolc medve volt a kukoricásba! - meséli Pre...   \n",
       "11  Szeptember 21-én újabb medvetámadás történt Cs...   \n",
       "12  Medve harapott meg egy pásztort a Hargita megy...   \n",
       "13  Két sérülthöz riasztották 2017. április 30-án,...   \n",
       "14  Combján harapdált össze egy pásztort 2017. jún...   \n",
       "\n",
       "                                                image       date  \\\n",
       "0                                                     2019-01-06   \n",
       "1                                                     2019-03-22   \n",
       "2                                                     2018-02-06   \n",
       "3   https://doc-0k-6g-mymaps.googleusercontent.com...        NaT   \n",
       "4   https://doc-0g-6g-mymaps.googleusercontent.com...        NaT   \n",
       "5   https://doc-10-6g-mymaps.googleusercontent.com...        NaT   \n",
       "6                                                            NaT   \n",
       "7   https://doc-0k-6g-mymaps.googleusercontent.com... 2018-08-05   \n",
       "8   https://doc-0o-6g-mymaps.googleusercontent.com... 2018-08-05   \n",
       "9                                                     2018-08-21   \n",
       "10  https://doc-00-6g-mymaps.googleusercontent.com...        NaT   \n",
       "11  https://doc-0s-6g-mymaps.googleusercontent.com...        NaT   \n",
       "12                                                           NaT   \n",
       "13                                                    2017-04-30   \n",
       "14  https://doc-04-6g-mymaps.googleusercontent.com... 2017-06-24   \n",
       "\n",
       "                                                 link  \n",
       "0   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "1   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "2   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "3   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "4   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "5   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "6   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "7   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "8   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "9   https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "10  https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "11  https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "12  https://hargitamegye.ro/vadkarok/interaktiv-te...  \n",
       "13  https://szekelyhon.ro/aktualis/medve-tamadt-ke...  \n",
       "14  https://szekelyhon.ro/aktualis/nappal-tamadt-e...  "
      ]
     },
     "execution_count": 98,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(len(df))\n",
    "\n",
    "df.head(15)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 99,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.704135Z",
     "start_time": "2019-04-18T05:35:12.691126Z"
    }
   },
   "outputs": [],
   "source": [
    "df['checked']=''\n",
    "df['exact_loc']=0\n",
    "df['source']='Vadkárok HR'\n",
    "df['title']=df['geo_loc']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 100,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:12.731132Z",
     "start_time": "2019-04-18T05:35:12.710143Z"
    }
   },
   "outputs": [],
   "source": [
    "from utils import text_processor"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 101,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:13.310129Z",
     "start_time": "2019-04-18T05:35:12.736130Z"
    }
   },
   "outputs": [],
   "source": [
    "duplicate=[]\n",
    "relevant=[]\n",
    "severity=[]\n",
    "deaths=[]\n",
    "for i in df.index:\n",
    "    l=df.loc[i]['link']\n",
    "    d=0\n",
    "    if l:\n",
    "        if 'szekelyhon' in l: d=1\n",
    "    duplicate.append(d)\n",
    "    c=df.loc[i]['content']\n",
    "    t=df.loc[i]['title']\n",
    "    r,s,d=text_processor(t,c)\n",
    "    relevant.append(1) #override r\n",
    "    severity.append(s)\n",
    "    deaths.append(d)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:13.335144Z",
     "start_time": "2019-04-18T05:35:13.318129Z"
    }
   },
   "outputs": [],
   "source": [
    "df['duplicate']=duplicate\n",
    "df['relevant']=relevant\n",
    "df['severity']=severity\n",
    "df['deaths']=deaths"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 103,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:14.130132Z",
     "start_time": "2019-04-18T05:35:14.111131Z"
    }
   },
   "outputs": [],
   "source": [
    "df=df[['checked',\n",
    "    'date',\n",
    "    'link',\n",
    "    'image',\n",
    "    'source',\n",
    "    'title',\n",
    "    'content',\n",
    "    'relevant',\n",
    "    'severity',\n",
    "    'deaths',\n",
    "    'duplicate',\n",
    "    'geo_loc',\n",
    "    'exact_loc',\n",
    "    'latitude',\n",
    "    'longitude']].set_index('checked')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 104,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2019-04-18T05:35:15.312135Z",
     "start_time": "2019-04-18T05:35:14.914135Z"
    }
   },
   "outputs": [],
   "source": [
    "df.to_excel('data/vadkarok_hr.xlsx')\n",
    "df.to_excel('data/vadkarok_hr_curated.xlsx')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill in missing date from image EXIF data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 106,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Collecting exifread\n",
      "  Downloading https://files.pythonhosted.org/packages/7b/cb/92b644626830115910cf2b36d3dfa600adbec86dff3207a7de3bfd6c6a60/ExifRead-2.1.2.tar.gz\n",
      "Building wheels for collected packages: exifread\n",
      "  Building wheel for exifread (setup.py): started\n",
      "  Building wheel for exifread (setup.py): finished with status 'done'\n",
      "  Created wheel for exifread: filename=ExifRead-2.1.2-cp37-none-any.whl size=34204 sha256=6f751370630938344e61d69dea28a19cd91767bc84db752e7f4233a18b1690fb\n",
      "  Stored in directory: C:\\Users\\csala\\AppData\\Local\\pip\\Cache\\wheels\\a2\\82\\de\\fd5f70739a3c8d7475cc21f4e186150abbc5d77180af7d94a2\n",
      "Successfully built exifread\n",
      "Installing collected packages: exifread\n",
      "Successfully installed exifread-2.1.2\n"
     ]
    }
   ],
   "source": [
    "!pip install exifread"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "metadata": {},
   "outputs": [],
   "source": [
    "no_date=df[df['date'].astype(str)=='NaT']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "metadata": {},
   "outputs": [],
   "source": [
    "no_date_yes_image=no_date[no_date['image'].astype(str)!='']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "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>date</th>\n",
       "      <th>link</th>\n",
       "      <th>image</th>\n",
       "      <th>source</th>\n",
       "      <th>title</th>\n",
       "      <th>content</th>\n",
       "      <th>relevant</th>\n",
       "      <th>severity</th>\n",
       "      <th>deaths</th>\n",
       "      <th>duplicate</th>\n",
       "      <th>geo_loc</th>\n",
       "      <th>exact_loc</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>checked</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "      <td>https://doc-0k-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Homoródremete</td>\n",
       "      <td>A Homoródszentmárton községhez tartozó Homoród...</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Homoródremete</td>\n",
       "      <td>0</td>\n",
       "      <td>46.292650</td>\n",
       "      <td>25.402600</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "      <td>https://doc-0g-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Szent Anna-tó</td>\n",
       "      <td>Újabb medvetámadás áldozata került kórházba Ha...</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Szent Anna-tó</td>\n",
       "      <td>0</td>\n",
       "      <td>46.126892</td>\n",
       "      <td>25.890094</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "      <td>https://doc-10-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>Újból emberre támadt a medve Hargita megyében,...</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Tusnádfürdő</td>\n",
       "      <td>0</td>\n",
       "      <td>46.146075</td>\n",
       "      <td>25.856959</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "      <td>https://doc-00-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Karcfalva</td>\n",
       "      <td>– Nyolc medve volt a kukoricásba! - meséli Pre...</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Karcfalva</td>\n",
       "      <td>0</td>\n",
       "      <td>46.530569</td>\n",
       "      <td>25.761535</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://hargitamegye.ro/vadkarok/interaktiv-te...</td>\n",
       "      <td>https://doc-0s-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Csíkszentkirály</td>\n",
       "      <td>Szeptember 21-én újabb medvetámadás történt Cs...</td>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Csíkszentkirály</td>\n",
       "      <td>0</td>\n",
       "      <td>46.312080</td>\n",
       "      <td>25.821061</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th></th>\n",
       "      <td>NaT</td>\n",
       "      <td>https://www.facebook.com/borbolycsaba/videos/v...</td>\n",
       "      <td>https://doc-10-6g-mymaps.googleusercontent.com...</td>\n",
       "      <td>Vadkárok HR</td>\n",
       "      <td>Csíkmindszent</td>\n",
       "      <td>Augusztus 26-án gombászás közben támadt a 38 é...</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>Csíkmindszent</td>\n",
       "      <td>0</td>\n",
       "      <td>46.328057</td>\n",
       "      <td>25.887421</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "        date                                               link  \\\n",
       "checked                                                           \n",
       "         NaT  https://hargitamegye.ro/vadkarok/interaktiv-te...   \n",
       "         NaT  https://hargitamegye.ro/vadkarok/interaktiv-te...   \n",
       "         NaT  https://hargitamegye.ro/vadkarok/interaktiv-te...   \n",
       "         NaT  https://hargitamegye.ro/vadkarok/interaktiv-te...   \n",
       "         NaT  https://hargitamegye.ro/vadkarok/interaktiv-te...   \n",
       "         NaT  https://www.facebook.com/borbolycsaba/videos/v...   \n",
       "\n",
       "                                                     image       source  \\\n",
       "checked                                                                   \n",
       "         https://doc-0k-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "         https://doc-0g-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "         https://doc-10-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "         https://doc-00-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "         https://doc-0s-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "         https://doc-10-6g-mymaps.googleusercontent.com...  Vadkárok HR   \n",
       "\n",
       "                   title                                            content  \\\n",
       "checked                                                                       \n",
       "           Homoródremete  A Homoródszentmárton községhez tartozó Homoród...   \n",
       "           Szent Anna-tó  Újabb medvetámadás áldozata került kórházba Ha...   \n",
       "             Tusnádfürdő  Újból emberre támadt a medve Hargita megyében,...   \n",
       "               Karcfalva  – Nyolc medve volt a kukoricásba! - meséli Pre...   \n",
       "         Csíkszentkirály  Szeptember 21-én újabb medvetámadás történt Cs...   \n",
       "           Csíkmindszent  Augusztus 26-án gombászás közben támadt a 38 é...   \n",
       "\n",
       "         relevant  severity  deaths  duplicate          geo_loc  exact_loc  \\\n",
       "checked                                                                      \n",
       "                1         3       0          0    Homoródremete          0   \n",
       "                1         4       0          0    Szent Anna-tó          0   \n",
       "                1         3       0          0      Tusnádfürdő          0   \n",
       "                1         3       0          0        Karcfalva          0   \n",
       "                1         4       0          0  Csíkszentkirály          0   \n",
       "                1         3       0          0    Csíkmindszent          0   \n",
       "\n",
       "          latitude  longitude  \n",
       "checked                        \n",
       "         46.292650  25.402600  \n",
       "         46.126892  25.890094  \n",
       "         46.146075  25.856959  \n",
       "         46.530569  25.761535  \n",
       "         46.312080  25.821061  \n",
       "         46.328057  25.887421  "
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "no_date_yes_image"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 135,
   "metadata": {},
   "outputs": [],
   "source": [
    "import exifread\n",
    "import urllib.request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "https://doc-0k-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/j4pcli5pfmmbes7q30tllaba3o/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALobI_ipqW5ICbMvGNPKbt3arQBei5-scjRLfl-blvv71F7hO4ennVw-6-206Gk7q1cILxsQno7vHU9NcxiAeNTA6rQT7GlT56CMeEn4BhkBoSpBUkiztnF1qxAabdgzPVO1pvxHRjj5H0cIUibIYNxzd3E48eZ2WI5Gflbib3JHZJzMTw7qT2dH8t-QKGFMoq7EdPnw1uvA_Fr3a2glYJFoeDPiMK2_7QPJRVGwg3C6aF2wiQcXRjtiYfJlWJzmbcw8cUbUoyi0hNWIjPyUVPYNjppGtw?fife\n",
      "No EXIF\n",
      "https://doc-0g-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/mte4p4bnuof32j3hm3v4na9kec/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALrYq8wIwpYzie0YHfjTBAo3sZCFSf2zsyFkZ2uGMD3WsZiG4IzgO1X1gSnGlit7EJVcQ0elBxxsMmsw8oKzhhDbR2it9bfDhGMteq82J6F1C7AU5O6N_XInLyagczjh64WPqXvruiIeDdN7QhKKjH7w8k7nwJAgfsEgYE6LW_BEohmfI6E7Fa4rMWPKpYU6pDPUWLX8dD1QNWukxsEVBenpGehoZCx10MxagPI3SnvPvmmDiB7qCZWZOMe0yyfGjqx-gN777gk4-zbGbJH1yI4XVWlbrA?fife\n",
      "No EXIF\n",
      "https://doc-10-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/gek80ca1cm1acm6720i0pdd62g/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALoA9Nn3ZB4NxOnU1KZRII21fW34-36rkz5N5ZGE-86XYVwIQ73yz0eaWr--qikcktkeBI-F51G5gE9UiehPVUItDn_TWZ9FyDYLhW67kpYvswbDl_xVgCL4g7y-01vb2kgyI-kQtpHWdjtrcF7owjl_DXgx2rEZg4F_5bozhpyRLzP0mcBSAR4pO9uLC-gztXHJvJC-Dvehc_9WMQaKq4lKisdMpxHoG0M6hQUbIdIWwHAZ6b8xrY9dCMoaKdU19NfqlVs51Pn-75EDOee9Wl2MVvW99Q?fife\n",
      "No EXIF\n",
      "https://doc-00-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/gj34nqtlk97bsdbiq1d0h46i88/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALqH6MuVQi3e69mAasPC1i5UcqF4qbTmhnSO2vqb3JUpq9hvkx-TTJGMENhrjUXhmvwjXEoyy3y9YED-jagN6-ttAM3IB9QkZLbp74SfSyi57tbxyC_S7J3ac-FxPzVbxRJvqNuSnurQQt2jbNuL1hkeYSNJeiVvCsWn92Q60VEvushsc1573158xbhi3vyemJVTeMfzoKtcQqTmPoqNx9llYQ7T4tykkmJ0O66iKorLAgU8LvbanrezEBxy-s8-iPftlr87dQCQpaWOMj_fCo5tmEX7LA?fife\n",
      "No EXIF\n",
      "https://doc-0s-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/1ijrusdr1dk01fr0504b0q8v40/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALoOozmeoZLILpxPdeHrmS3exUO9l1bXxTz8cz9qj86PgPcUq92mUA-Vey4F3UZajLz_UzkpuLfOeDigsshQuhRT5lc3i1HAtkLGZo4GP0lOXPvSPlt7Ue6R9hopF2TUqAJJYB92X0FC_tnt8Qh4D1ocKoh01-SikpVv1sYQsi4zYihGdyqnoNqGs2rOrw6mayrYjLa8933LNTBQ0o4G73VKzOp5LZrDmeGQn6ZqlBcuSFbhEnnNtjVzczCTV9_z5cCvXRfGqWs38EqE51CjauGlAATtwQ?fife\n",
      "No EXIF\n",
      "https://doc-10-6g-mymaps.googleusercontent.com/untrusted/hostedimage/f6u64nodcabo26320jjaagdfpc/ld2e349ubao0lq2tjdtn3j5p14/1580233500000/8vpJ8s-AWmMZHl1okKvYY29jERWnD2LC/*/2AF2TALqqtblsJpdU5q6eU9wFUfgL9WpYkYvE3DU_IxUC3kA8K9sDBOo27rTEkW3LWkJX4xsCqfbQt3vth4qbAIM2BpuqbCWosejUEatZlnfBaGpJGcs8rAvBAo_vSiJtCFWyN1eCV_iDZL-U_4vKRbVok-F4Q7gG58sOSCbYPJR6Dsu56hkvuqq8FOqlpBRqwpZ-nhvBsh7HuzytJsYRhJxKgGxJi2WXaakyjbYlGjJRwRZ256wVG3B97hrJAR9hDBjjfPiHf0s-iQ9w9nk1L_fQpiLTUqVAHQ?fife\n",
      "No EXIF\n"
     ]
    }
   ],
   "source": [
    "for im_link in no_date_yes_image['image'].values:\n",
    "    im_link=im_link.split(' ')[0] #if multiple images, take only first\n",
    "    print(im_link)\n",
    "    try:\n",
    "        urllib.request.urlretrieve(im_link, \"temp.png\")\n",
    "        with open('temp.png', 'rb') as fh:\n",
    "            tags = exifread.process_file(fh, stop_tag=\"EXIF DateTimeOriginal\")\n",
    "            if tags:\n",
    "                dateTaken = tags[\"EXIF DateTimeOriginal\"]\n",
    "                print(dateTaken)\n",
    "            else:\n",
    "                print('No EXIF')\n",
    "    except:\n",
    "        print('No readable image')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Fill in missing dates - if you can, manually"
   ]
  }
 ],
 "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.8.8"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}