DataProcessingPython.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Preprocess restriction data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Get updated dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "restrictions_df = pd.read_csv('data/Kaggle_CountryInfo/restrictions.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Make some initial transformations towards a columnwise format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Update type format\n",
    "restrictions_df.loc[:,'type'] = 'Date.' + restrictions_df.loc[:,'type'].astype(str)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename last column\n",
    "restrictions_df.columns = ['country_region', 'date', 'type', 'limit', 'mandatory', 'notes']"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get set of columns and rows for the dates\n",
    "countries = restrictions_df.country_region.unique()\n",
    "dates = restrictions_df.type.unique()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "#append extra info for the rows\n",
    "cols = np.append(dates, [\"limit\", \"mandatory\", \"notes\"])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Create new dataframe\n",
    "df = pd.DataFrame(index=countries, columns=cols)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [],
   "source": [
    "def agg_notes_without_nas(notes):\n",
    "    result = []\n",
    "    for note in notes:\n",
    "        if note is not np.nan:\n",
    "            result.append(note)\n",
    "    if len(result) > 0:\n",
    "        return \",\".join(result)\n",
    "    else: return np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Get Dates data\n",
    "tmp_dates = restrictions_df.pivot_table(index=\"country_region\", columns=\"type\", values=\"date\", aggfunc=lambda x: x)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, tmp_dates.columns] = tmp_dates.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [],
   "source": [
    "limits = restrictions_df.groupby(\"country_region\").limit.sum()\n",
    "mandatory = restrictions_df.groupby(\"country_region\").mandatory.agg(agg_notes_without_nas)\n",
    "notes = restrictions_df.groupby(\"country_region\").notes.agg(agg_notes_without_nas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [],
   "source": [
    "df.loc[:, \"limit\"] = limits.values\n",
    "df.loc[:, \"mandatory\"] = mandatory.values\n",
    "df.loc[:, \"notes\"] = notes.values"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [],
   "source": [
    "# eliminate NaNs\n",
    "# df = df.fillna(\"\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [],
   "source": [
    "# make country a column again\n",
    "df = df.reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [],
   "source": [
    "# rename columns\n",
    "df.columns = ['Country.Region', 'Date.Schools', 'Date.Public Places', 'Date.Gatherings',\n",
    "       'Date.Stay at Home', 'Date.Lockdown', 'Date.Non-essential',\n",
    "       'Gatherings.limit', 'mandatory', 'notes']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Add extra columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [],
   "source": [
    "df['Country.Continent'] = np.nan\n",
    "df['Province.State'] = np.nan"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Rearange columns\n",
    "df = df.loc[:, ['Country.Continent', 'Country.Region', 'Province.State', 'Date.Schools',\n",
    "       'Date.Public Places', 'Date.Gatherings', 'Date.Stay at Home',\n",
    "       'Date.Lockdown', 'Date.Non-essential', 'Gatherings.limit', 'mandatory',\n",
    "       'notes']]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Copy the Country.Region column to Province.State\n",
    "df.loc[:, 'Province.State'] = df.loc[:, 'Country.Region']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Update Restrictions dataset with new data"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Load restriction dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "restrictions_colwise_df = pd.read_csv('data/Kaggle_CountryInfo/restrictions_columnwise_updated.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": [
    "restrictions_colwise_df[restrictions_colwise_df.eq('[]').any(1)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "ename": "ValueError",
     "evalue": "('Unknown string format:', '[]')",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mTypeError\u001b[0m                                 Traceback (most recent call last)",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/arrays/datetimes.py\u001b[0m in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)\u001b[0m\n\u001b[1;32m   1860\u001b[0m         \u001b[0;32mtry\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1861\u001b[0;31m             \u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtz_parsed\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconversion\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mdatetime_to_datetime64\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mdata\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1862\u001b[0m             \u001b[0;31m# If tzaware, these values represent unix timestamps, so we\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslibs/conversion.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslibs.conversion.datetime_to_datetime64\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;31mTypeError\u001b[0m: Unrecognized value type: <class 'str'>",
      "\nDuring handling of the above exception, another exception occurred:\n",
      "\u001b[0;31mValueError\u001b[0m                                Traceback (most recent call last)",
      "\u001b[0;32m<ipython-input-6-970a5238d7db>\u001b[0m in \u001b[0;36m<module>\u001b[0;34m\u001b[0m\n\u001b[1;32m      2\u001b[0m \u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Schools'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Schools'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      3\u001b[0m \u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Public Places'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Public Places'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m----> 4\u001b[0;31m \u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Date.Gatherings'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Gatherings'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m      5\u001b[0m \u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Stay at Home'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Stay at Home'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m      6\u001b[0m \u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Lockdown'\u001b[0m\u001b[0;34m]\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mpd\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mto_datetime\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mrestrictions_colwise_df\u001b[0m\u001b[0;34m[\u001b[0m\u001b[0;34m'Date.Lockdown'\u001b[0m\u001b[0;34m]\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py\u001b[0m in \u001b[0;36mto_datetime\u001b[0;34m(arg, errors, dayfirst, yearfirst, utc, box, format, exact, unit, infer_datetime_format, origin, cache)\u001b[0m\n\u001b[1;32m    590\u001b[0m         \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    591\u001b[0m             \u001b[0;32mfrom\u001b[0m \u001b[0mpandas\u001b[0m \u001b[0;32mimport\u001b[0m \u001b[0mSeries\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 592\u001b[0;31m             \u001b[0mvalues\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mconvert_listlike\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0m_values\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;32mTrue\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mformat\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    593\u001b[0m             \u001b[0mresult\u001b[0m \u001b[0;34m=\u001b[0m \u001b[0mSeries\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mvalues\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mindex\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mindex\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mname\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mname\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    594\u001b[0m     \u001b[0;32melif\u001b[0m \u001b[0misinstance\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mABCDataFrame\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mcompat\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mMutableMapping\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/tools/datetimes.py\u001b[0m in \u001b[0;36m_convert_listlike_datetimes\u001b[0;34m(arg, box, format, name, tz, unit, errors, infer_datetime_format, dayfirst, yearfirst, exact)\u001b[0m\n\u001b[1;32m    300\u001b[0m             \u001b[0marg\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mdayfirst\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdayfirst\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0myearfirst\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0myearfirst\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    301\u001b[0m             \u001b[0mutc\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mutc\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0merrors\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0merrors\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mrequire_iso8601\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequire_iso8601\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 302\u001b[0;31m             allow_object=True)\n\u001b[0m\u001b[1;32m    303\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    304\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mtz_parsed\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/arrays/datetimes.py\u001b[0m in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)\u001b[0m\n\u001b[1;32m   1864\u001b[0m             \u001b[0;32mreturn\u001b[0m \u001b[0mvalues\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mview\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m'i8'\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtz_parsed\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1865\u001b[0m         \u001b[0;32mexcept\u001b[0m \u001b[0;34m(\u001b[0m\u001b[0mValueError\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mTypeError\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1866\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1867\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1868\u001b[0m     \u001b[0;32mif\u001b[0m \u001b[0mtz_parsed\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mnot\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/pandas/core/arrays/datetimes.py\u001b[0m in \u001b[0;36mobjects_to_datetime64ns\u001b[0;34m(data, dayfirst, yearfirst, utc, errors, require_iso8601, allow_object)\u001b[0m\n\u001b[1;32m   1855\u001b[0m             \u001b[0mdayfirst\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mdayfirst\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1856\u001b[0m             \u001b[0myearfirst\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0myearfirst\u001b[0m\u001b[0;34m,\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1857\u001b[0;31m             \u001b[0mrequire_iso8601\u001b[0m\u001b[0;34m=\u001b[0m\u001b[0mrequire_iso8601\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1858\u001b[0m         )\n\u001b[1;32m   1859\u001b[0m     \u001b[0;32mexcept\u001b[0m \u001b[0mValueError\u001b[0m \u001b[0;32mas\u001b[0m \u001b[0me\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime_object\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslib.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslib.array_to_datetime_object\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32mpandas/_libs/tslibs/parsing.pyx\u001b[0m in \u001b[0;36mpandas._libs.tslibs.parsing.parse_datetime_string\u001b[0;34m()\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/dateutil/parser/_parser.py\u001b[0m in \u001b[0;36mparse\u001b[0;34m(timestr, parserinfo, **kwargs)\u001b[0m\n\u001b[1;32m   1356\u001b[0m         \u001b[0;32mreturn\u001b[0m \u001b[0mparser\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mparserinfo\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtimestr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1357\u001b[0m     \u001b[0;32melse\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m-> 1358\u001b[0;31m         \u001b[0;32mreturn\u001b[0m \u001b[0mDEFAULTPARSER\u001b[0m\u001b[0;34m.\u001b[0m\u001b[0mparse\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mtimestr\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0;34m**\u001b[0m\u001b[0mkwargs\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m   1359\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m   1360\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;32m~/anaconda3/lib/python3.7/site-packages/dateutil/parser/_parser.py\u001b[0m in \u001b[0;36mparse\u001b[0;34m(self, timestr, default, ignoretz, tzinfos, **kwargs)\u001b[0m\n\u001b[1;32m    647\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    648\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mres\u001b[0m \u001b[0;32mis\u001b[0m \u001b[0;32mNone\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0;32m--> 649\u001b[0;31m             \u001b[0;32mraise\u001b[0m \u001b[0mValueError\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0;34m\"Unknown string format:\"\u001b[0m\u001b[0;34m,\u001b[0m \u001b[0mtimestr\u001b[0m\u001b[0;34m)\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n\u001b[0m\u001b[1;32m    650\u001b[0m \u001b[0;34m\u001b[0m\u001b[0m\n\u001b[1;32m    651\u001b[0m         \u001b[0;32mif\u001b[0m \u001b[0mlen\u001b[0m\u001b[0;34m(\u001b[0m\u001b[0mres\u001b[0m\u001b[0;34m)\u001b[0m \u001b[0;34m==\u001b[0m \u001b[0;36m0\u001b[0m\u001b[0;34m:\u001b[0m\u001b[0;34m\u001b[0m\u001b[0;34m\u001b[0m\u001b[0m\n",
      "\u001b[0;31mValueError\u001b[0m: ('Unknown string format:', '[]')"
     ]
    }
   ],
   "source": [
    "# Correct format\n",
    "restrictions_colwise_df['Date.Schools'] = pd.to_datetime(restrictions_colwise_df['Date.Schools'])\n",
    "restrictions_colwise_df['Date.Public Places'] = pd.to_datetime(restrictions_colwise_df['Date.Public Places'])\n",
    "restrictions_colwise_df['Date.Date.Gatherings'] = pd.to_datetime(restrictions_colwise_df['Date.Gatherings'])\n",
    "restrictions_colwise_df['Date.Stay at Home'] = pd.to_datetime(restrictions_colwise_df['Date.Stay at Home'])\n",
    "restrictions_colwise_df['Date.Lockdown'] = pd.to_datetime(restrictions_colwise_df['Date.Lockdown'])\n",
    "restrictions_colwise_df['Date.Non-essential'] = pd.to_datetime(restrictions_colwise_df['Date.Non-essential'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Merge with new data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "metadata": {},
   "outputs": [],
   "source": [
    "def setdiff_sorted(array1,array2,assume_unique=False):\n",
    "    \"\"\"\n",
    "    Compares 2 arrays and returns the difference\n",
    "    \"\"\"\n",
    "    ans = np.setdiff1d(array1,array2,assume_unique).tolist()\n",
    "    if assume_unique:\n",
    "        return sorted(ans)\n",
    "    return ans"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "metadata": {},
   "outputs": [],
   "source": [
    "# First we update the previous data\n",
    "restrictions_colwise_df.update(df, overwrite=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "metadata": {},
   "outputs": [],
   "source": [
    "# then we check if there are new rows and update them\n",
    "new_rows = setdiff_sorted(df['Province.State'].unique(), restrictions_colwise_df['Province.State'].unique())"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Add new rowsupdate\n",
    "for row in new_rows:\n",
    "    restrictions_colwise_df = restrictions_colwise_df.append(df[df['Province.State'] == row], ignore_index=True, verify_integrity=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Finally we sort by Province.State \n",
    "restrictions_colwise_df.sort_values(by='Country.Region', ascending=False, inplace=True)\n",
    "restrictions_colwise_df = restrictions_colwise_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 71,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Save new updated version\n",
    "restrictions_colwise_df.to_csv(\"data/Kaggle_CountryInfo/restrictions_columnwise_updated.csv\", index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Auxiliary methods"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [],
   "source": [
    "import urllib.request"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [],
   "source": [
    "url = 'https://raw.githubusercontent.com/grammakov/USA-cities-and-states/master/us_cities_states_counties.csv'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "('us_cities_states_counties.csv', <http.client.HTTPMessage at 0x1168a34e0>)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "urllib.request.urlretrieve(url, 'us_cities_states_counties.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "metadata": {},
   "outputs": [],
   "source": [
    "us_states = pd.read_csv('us_cities_states_counties.csv', sep='|')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 45,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['City', 'State short', 'State full', 'County', 'City alias'], dtype='object')"
      ]
     },
     "execution_count": 45,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "us_states.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "restrictions_colwise_df.loc[restrictions_colwise_df['Province.State'].isin(us_states['State full'].unique()), 'Country.Continent'] = 'North America'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 62,
   "metadata": {},
   "outputs": [],
   "source": [
    "restrictions_colwise_df = restrictions_colwise_df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Country.Continent</th>\n",
       "      <th>Country.Region</th>\n",
       "      <th>Province.State</th>\n",
       "      <th>Date.Schools</th>\n",
       "      <th>Date.Public Places</th>\n",
       "      <th>Date.Gatherings</th>\n",
       "      <th>Date.Stay at Home</th>\n",
       "      <th>Date.Lockdown</th>\n",
       "      <th>Date.Non-essential</th>\n",
       "      <th>Gatherings.limit</th>\n",
       "      <th>mandatory</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>China (Wuhan)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-01-23T00:00:00.000000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>all public transport, including buses, railway...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>China (Hubei)</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-01-24T00:00:00.000000000</td>\n",
       "      <td>2020-02-13</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes</td>\n",
       "      <td>12 cities in Hubei. Shut down all non-essentia...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>China</td>\n",
       "      <td>2020-01-26T00:00:00.000000000</td>\n",
       "      <td>2020-01-26T00:00:00.000000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes,Yes</td>\n",
       "      <td>,Covered most of the country (not 100%)</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country.Continent Country.Region Province.State  \\\n",
       "13              Asia          China  China (Wuhan)   \n",
       "12              Asia          China  China (Hubei)   \n",
       "11              Asia          China          China   \n",
       "\n",
       "                     Date.Schools             Date.Public Places  \\\n",
       "13                            NaN                            NaN   \n",
       "12                            NaN                            NaN   \n",
       "11  2020-01-26T00:00:00.000000000  2020-01-26T00:00:00.000000000   \n",
       "\n",
       "   Date.Gatherings Date.Stay at Home                  Date.Lockdown  \\\n",
       "13             NaN               NaN  2020-01-23T00:00:00.000000000   \n",
       "12             NaN               NaN  2020-01-24T00:00:00.000000000   \n",
       "11             NaN               NaN                            NaN   \n",
       "\n",
       "   Date.Non-essential  Gatherings.limit mandatory  \\\n",
       "13                NaN                 0       Yes   \n",
       "12         2020-02-13                 0       Yes   \n",
       "11                NaN                 0   Yes,Yes   \n",
       "\n",
       "                                                notes  \n",
       "13  all public transport, including buses, railway...  \n",
       "12  12 cities in Hubei. Shut down all non-essentia...  \n",
       "11            ,Covered most of the country (not 100%)  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "restrictions_colwise_df[restrictions_colwise_df['Country.Region'].str.contains('Ch')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 72,
   "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>Country.Continent</th>\n",
       "      <th>Country.Region</th>\n",
       "      <th>Province.State</th>\n",
       "      <th>Date.Schools</th>\n",
       "      <th>Date.Public Places</th>\n",
       "      <th>Date.Gatherings</th>\n",
       "      <th>Date.Stay at Home</th>\n",
       "      <th>Date.Lockdown</th>\n",
       "      <th>Date.Non-essential</th>\n",
       "      <th>Gatherings.limit</th>\n",
       "      <th>mandatory</th>\n",
       "      <th>notes</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td></td>\n",
       "      <td>Czech</td>\n",
       "      <td>Czech</td>\n",
       "      <td>3/13/2020</td>\n",
       "      <td>3/13/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>3/17/2020</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0</td>\n",
       "      <td>Yes,Yes,Yes</td>\n",
       "      <td>3/18: everyone is required to cover their nose...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Country.Continent Country.Region Province.State Date.Schools  \\\n",
       "17                            Czech          Czech    3/13/2020   \n",
       "\n",
       "   Date.Public Places Date.Gatherings Date.Stay at Home Date.Lockdown  \\\n",
       "17          3/13/2020             NaN         3/17/2020           NaN   \n",
       "\n",
       "   Date.Non-essential  Gatherings.limit    mandatory  \\\n",
       "17                NaN                 0  Yes,Yes,Yes   \n",
       "\n",
       "                                                notes  \n",
       "17  3/18: everyone is required to cover their nose...  "
      ]
     },
     "execution_count": 72,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['Country.Region'].str.contains('Cz')]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 63,
   "metadata": {},
   "outputs": [],
   "source": [
    "# index = 3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 64,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Country.Continent                               NaN\n",
       "Country.Region                        Washington DC\n",
       "Province.State                        Washington DC\n",
       "Date.Schools          2020-03-16T00:00:00.000000000\n",
       "Date.Public Places    2020-03-15T00:00:00.000000000\n",
       "Date.Gatherings                                 NaN\n",
       "Date.Stay at Home                               NaN\n",
       "Date.Lockdown                                   NaN\n",
       "Date.Non-essential                              NaN\n",
       "Gatherings.limit                                  0\n",
       "mandatory                                   Yes,Yes\n",
       "notes                                             ,\n",
       "Name: 3, dtype: object"
      ]
     },
     "execution_count": 64,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# restrictions_colwise_df.loc[index, :]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 65,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Notes, will need to rename country_region to Province.State!"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 67,
   "metadata": {},
   "outputs": [],
   "source": [
    "# restrictions_colwise_df.loc[index, 'Country.Region'] = 'US'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df = df.append({'Country.Continent':'Middle East', \n",
    "#            'Country.Region':'Completely Made Up', \n",
    "#            'Province.State':'', \n",
    "#            'Date.Schools':'', \n",
    "#            'Date.Public Places':'', \n",
    "#            'Date.Gatherings':'', \n",
    "#            'Date.Stay at Home':'', \n",
    "#            'Date.Lockdown':'2020-3-15', \n",
    "#            'Date.Non-essential':'', \n",
    "#            'Gatherings.limit':0, \n",
    "#            'mandatory':'Yes', \n",
    "#            'notes':''}, ignore_index=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [],
   "source": [
    "# df.loc[df[\"Province.State\"] == '', 'Province.State'] = df.loc[df[\"Province.State\"] == '', 'Country.Region']"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python [conda env:djangoenv]",
   "language": "python",
   "name": "conda-env-djangoenv-py"
  },
  "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}