week-5.17.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "c515dd9e",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied: datapackage in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (1.15.2)\n",
      "Requirement already satisfied: jsonschema>=2.5 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (4.4.0)\n",
      "Requirement already satisfied: chardet>=3.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (4.0.0)\n",
      "Requirement already satisfied: six>=1.10 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (1.16.0)\n",
      "Requirement already satisfied: tabulator>=1.29 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (1.53.5)\n",
      "Requirement already satisfied: tableschema>=1.12.1 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (1.20.2)\n",
      "Requirement already satisfied: unicodecsv>=0.14 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (0.14.1)\n",
      "Requirement already satisfied: click>=6.7 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (8.0.4)\n",
      "Requirement already satisfied: requests>=2.8 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (2.27.1)\n",
      "Requirement already satisfied: jsonpointer>=1.10 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from datapackage) (2.3)\n",
      "Requirement already satisfied: attrs>=17.4.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from jsonschema>=2.5->datapackage) (21.4.0)\n",
      "Requirement already satisfied: pyrsistent!=0.17.0,!=0.17.1,!=0.17.2,>=0.14.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from jsonschema>=2.5->datapackage) (0.18.0)\n",
      "Requirement already satisfied: charset-normalizer~=2.0.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from requests>=2.8->datapackage) (2.0.4)\n",
      "Requirement already satisfied: idna<4,>=2.5 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from requests>=2.8->datapackage) (3.3)\n",
      "Requirement already satisfied: certifi>=2017.4.17 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from requests>=2.8->datapackage) (2022.12.7)\n",
      "Requirement already satisfied: urllib3<1.27,>=1.21.1 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from requests>=2.8->datapackage) (1.26.9)\n",
      "Requirement already satisfied: isodate>=0.5.4 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tableschema>=1.12.1->datapackage) (0.6.1)\n",
      "Requirement already satisfied: cached-property>=1.5 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tableschema>=1.12.1->datapackage) (1.5.2)\n",
      "Requirement already satisfied: rfc3986>=1.1.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tableschema>=1.12.1->datapackage) (2.0.0)\n",
      "Requirement already satisfied: python-dateutil>=2.4 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tableschema>=1.12.1->datapackage) (2.8.2)\n",
      "Requirement already satisfied: openpyxl>=2.6 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (3.0.9)\n",
      "Requirement already satisfied: linear-tsv>=1.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (1.1.0)\n",
      "Requirement already satisfied: xlrd>=1.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (2.0.1)\n",
      "Requirement already satisfied: jsonlines>=1.1 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (3.1.0)\n",
      "Requirement already satisfied: boto3>=1.9 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (1.21.32)\n",
      "Requirement already satisfied: ijson>=3.0.3 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (3.2.0.post0)\n",
      "Requirement already satisfied: sqlalchemy>=0.9.6 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from tabulator>=1.29->datapackage) (1.4.32)\n",
      "Requirement already satisfied: jmespath<2.0.0,>=0.7.1 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from boto3>=1.9->tabulator>=1.29->datapackage) (0.10.0)\n",
      "Requirement already satisfied: s3transfer<0.6.0,>=0.5.0 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from boto3>=1.9->tabulator>=1.29->datapackage) (0.5.0)\n",
      "Requirement already satisfied: botocore<1.25.0,>=1.24.32 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from boto3>=1.9->tabulator>=1.29->datapackage) (1.24.32)\n",
      "Requirement already satisfied: et-xmlfile in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from openpyxl>=2.6->tabulator>=1.29->datapackage) (1.1.0)\n",
      "Requirement already satisfied: greenlet!=0.4.17 in /Users/mac/opt/anaconda3/lib/python3.9/site-packages (from sqlalchemy>=0.9.6->tabulator>=1.29->datapackage) (2.0.2)\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import csv\n",
    "from datetime import datetime\n",
    "#!pip install datapackage\n",
    "import datapackage"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1ccad680",
   "metadata": {},
   "outputs": [],
   "source": [
    "data_url = 'https://datahub.io/core/country-codes/datapackage.json'\n",
    "\n",
    "# to load Data Package into storage\n",
    "package = datapackage.Package(data_url)\n",
    "\n",
    "# to load only tabular data\n",
    "resources = package.resources\n",
    "for resource in resources:\n",
    "    if resource.tabular:\n",
    "        geodata = pd.read_csv(resource.descriptor['path'])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "34d68153",
   "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>0</th>\n",
       "      <th>1</th>\n",
       "      <th>2</th>\n",
       "      <th>3</th>\n",
       "      <th>4</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>FIFA</th>\n",
       "      <td>TPE</td>\n",
       "      <td>AFG</td>\n",
       "      <td>ALB</td>\n",
       "      <td>ALG</td>\n",
       "      <td>ASA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Dial</th>\n",
       "      <td>886</td>\n",
       "      <td>93</td>\n",
       "      <td>355</td>\n",
       "      <td>213</td>\n",
       "      <td>1-684</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO3166-1-Alpha-3</th>\n",
       "      <td>TWN</td>\n",
       "      <td>AFG</td>\n",
       "      <td>ALB</td>\n",
       "      <td>DZA</td>\n",
       "      <td>ASM</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>MARC</th>\n",
       "      <td>ch</td>\n",
       "      <td>af</td>\n",
       "      <td>aa</td>\n",
       "      <td>ae</td>\n",
       "      <td>as</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>is_independent</th>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Yes</td>\n",
       "      <td>Territory of US</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO3166-1-numeric</th>\n",
       "      <td>158.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>GAUL</th>\n",
       "      <td>925</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>FIPS</th>\n",
       "      <td>TW</td>\n",
       "      <td>AF</td>\n",
       "      <td>AL</td>\n",
       "      <td>AG</td>\n",
       "      <td>AQ</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>WMO</th>\n",
       "      <td></td>\n",
       "      <td>AF</td>\n",
       "      <td>AB</td>\n",
       "      <td>AL</td>\n",
       "      <td></td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO3166-1-Alpha-2</th>\n",
       "      <td>TW</td>\n",
       "      <td>AF</td>\n",
       "      <td>AL</td>\n",
       "      <td>DZ</td>\n",
       "      <td>AS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ITU</th>\n",
       "      <td></td>\n",
       "      <td>AFG</td>\n",
       "      <td>ALB</td>\n",
       "      <td>ALG</td>\n",
       "      <td>SMA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>IOC</th>\n",
       "      <td>TPE</td>\n",
       "      <td>AFG</td>\n",
       "      <td>ALB</td>\n",
       "      <td>ALG</td>\n",
       "      <td>ASA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>DS</th>\n",
       "      <td>RC</td>\n",
       "      <td>AFG</td>\n",
       "      <td>AL</td>\n",
       "      <td>DZ</td>\n",
       "      <td>USA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Spanish Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>República Islámica del Afganistán (la)</td>\n",
       "      <td>la República de Albania</td>\n",
       "      <td>la República Argelina Democrática y Popular</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Global Code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "      <td>True</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Intermediate Region Code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_fr</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Albanie</td>\n",
       "      <td>Algérie</td>\n",
       "      <td>Samoa américaines</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM French Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan (l') [masc.]</td>\n",
       "      <td>Albanie (l') [fém.]</td>\n",
       "      <td>Algérie (l') [fém.]</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO4217-currency_name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghani</td>\n",
       "      <td>Lek</td>\n",
       "      <td>Algerian Dinar</td>\n",
       "      <td>US Dollar</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Developed / Developing Countries</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Developing</td>\n",
       "      <td>Developed</td>\n",
       "      <td>Developing</td>\n",
       "      <td>Developing</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Russian Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Исламская Республика Афганистан</td>\n",
       "      <td>Республика Албания</td>\n",
       "      <td>Алжирская Народная Демократическая Республика</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM English Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO4217-currency_alphabetic_code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>AFN</td>\n",
       "      <td>ALL</td>\n",
       "      <td>DZD</td>\n",
       "      <td>USD</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Small Island Developing States (SIDS)</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>x</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Spanish Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afganistán (el)</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Argelia</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO4217-currency_numeric_code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>971</td>\n",
       "      <td>008</td>\n",
       "      <td>012</td>\n",
       "      <td>840</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Chinese Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>阿富汗伊斯兰共和国</td>\n",
       "      <td>阿尔巴尼亚共和国</td>\n",
       "      <td>阿尔及利亚民主人民共和国</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM French Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>République islamique d'Afghanistan</td>\n",
       "      <td>la République d'Albanie</td>\n",
       "      <td>la République algérienne démocratique et popul...</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Russian Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Афганистан</td>\n",
       "      <td>Албания</td>\n",
       "      <td>Алжир</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>M49</th>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>8.0</td>\n",
       "      <td>12.0</td>\n",
       "      <td>16.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sub-region Code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>34.0</td>\n",
       "      <td>39.0</td>\n",
       "      <td>15.0</td>\n",
       "      <td>61.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region Code</th>\n",
       "      <td>NaN</td>\n",
       "      <td>142.0</td>\n",
       "      <td>150.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>9.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_ar</th>\n",
       "      <td>NaN</td>\n",
       "      <td>أفغانستان</td>\n",
       "      <td>ألبانيا</td>\n",
       "      <td>الجزائر</td>\n",
       "      <td>ساموا الأمريكية</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO4217-currency_minor_unit</th>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Arabic Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>جمهورية أفغانستان الإسلامية</td>\n",
       "      <td>جمهورية ألبانيا</td>\n",
       "      <td>الجمهورية الجزائرية الديمقراطية الشعبية</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Chinese Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>阿富汗</td>\n",
       "      <td>阿尔巴尼亚</td>\n",
       "      <td>阿尔及利亚</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Land Locked Developing Countries (LLDC)</th>\n",
       "      <td>NaN</td>\n",
       "      <td>x</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Intermediate Region Name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_es</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afganistán</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Argelia</td>\n",
       "      <td>Samoa Americana</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM English Formal</th>\n",
       "      <td>NaN</td>\n",
       "      <td>the Islamic Republic of Afghanistan</td>\n",
       "      <td>the Republic of Albania</td>\n",
       "      <td>the People's Democratic Republic of Algeria</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_cn</th>\n",
       "      <td>NaN</td>\n",
       "      <td>阿富汗</td>\n",
       "      <td>阿尔巴尼亚</td>\n",
       "      <td>阿尔及利亚</td>\n",
       "      <td>美属萨摩亚</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_en</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>American Samoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ISO4217-currency_country_name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>AFGHANISTAN</td>\n",
       "      <td>ALBANIA</td>\n",
       "      <td>ALGERIA</td>\n",
       "      <td>AMERICAN SAMOA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Least Developed Countries (LDC)</th>\n",
       "      <td>NaN</td>\n",
       "      <td>x</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Region Name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Asia</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Africa</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>UNTERM Arabic Short</th>\n",
       "      <td>NaN</td>\n",
       "      <td>أفغانستان</td>\n",
       "      <td>ألبانيا</td>\n",
       "      <td>الجزائر</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Sub-region Name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Southern Europe</td>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>Polynesia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>official_name_ru</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Афганистан</td>\n",
       "      <td>Албания</td>\n",
       "      <td>Алжир</td>\n",
       "      <td>Американское Самоа</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Global Name</th>\n",
       "      <td>NaN</td>\n",
       "      <td>World</td>\n",
       "      <td>World</td>\n",
       "      <td>World</td>\n",
       "      <td>World</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Capital</th>\n",
       "      <td>Taipei</td>\n",
       "      <td>Kabul</td>\n",
       "      <td>Tirana</td>\n",
       "      <td>Algiers</td>\n",
       "      <td>Pago Pago</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Continent</th>\n",
       "      <td>AS</td>\n",
       "      <td>AS</td>\n",
       "      <td>EU</td>\n",
       "      <td>AF</td>\n",
       "      <td>OC</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>TLD</th>\n",
       "      <td>.tw</td>\n",
       "      <td>.af</td>\n",
       "      <td>.al</td>\n",
       "      <td>.dz</td>\n",
       "      <td>.as</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Languages</th>\n",
       "      <td>zh-TW,zh,nan,hak</td>\n",
       "      <td>fa-AF,ps,uz-AF,tk</td>\n",
       "      <td>sq,el</td>\n",
       "      <td>ar-DZ</td>\n",
       "      <td>en-AS,sm,to</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Geoname ID</th>\n",
       "      <td>1668284.0</td>\n",
       "      <td>1149361.0</td>\n",
       "      <td>783754.0</td>\n",
       "      <td>2589581.0</td>\n",
       "      <td>5880801.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>CLDR display name</th>\n",
       "      <td>Taiwan</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Albania</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>American Samoa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EDGAR</th>\n",
       "      <td>NaN</td>\n",
       "      <td>B2</td>\n",
       "      <td>B3</td>\n",
       "      <td>B4</td>\n",
       "      <td>B5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                                                        0   \n",
       "FIFA                                                  TPE  \\\n",
       "Dial                                                  886   \n",
       "ISO3166-1-Alpha-3                                     TWN   \n",
       "MARC                                                   ch   \n",
       "is_independent                                        Yes   \n",
       "ISO3166-1-numeric                                   158.0   \n",
       "GAUL                                                  925   \n",
       "FIPS                                                   TW   \n",
       "WMO                                                         \n",
       "ISO3166-1-Alpha-2                                      TW   \n",
       "ITU                                                         \n",
       "IOC                                                   TPE   \n",
       "DS                                                     RC   \n",
       "UNTERM Spanish Formal                                 NaN   \n",
       "Global Code                                           NaN   \n",
       "Intermediate Region Code                              NaN   \n",
       "official_name_fr                                      NaN   \n",
       "UNTERM French Short                                   NaN   \n",
       "ISO4217-currency_name                                 NaN   \n",
       "Developed / Developing Countries                      NaN   \n",
       "UNTERM Russian Formal                                 NaN   \n",
       "UNTERM English Short                                  NaN   \n",
       "ISO4217-currency_alphabetic_code                      NaN   \n",
       "Small Island Developing States (SIDS)                 NaN   \n",
       "UNTERM Spanish Short                                  NaN   \n",
       "ISO4217-currency_numeric_code                         NaN   \n",
       "UNTERM Chinese Formal                                 NaN   \n",
       "UNTERM French Formal                                  NaN   \n",
       "UNTERM Russian Short                                  NaN   \n",
       "M49                                                   NaN   \n",
       "Sub-region Code                                       NaN   \n",
       "Region Code                                           NaN   \n",
       "official_name_ar                                      NaN   \n",
       "ISO4217-currency_minor_unit                           NaN   \n",
       "UNTERM Arabic Formal                                  NaN   \n",
       "UNTERM Chinese Short                                  NaN   \n",
       "Land Locked Developing Countries (LLDC)               NaN   \n",
       "Intermediate Region Name                              NaN   \n",
       "official_name_es                                      NaN   \n",
       "UNTERM English Formal                                 NaN   \n",
       "official_name_cn                                      NaN   \n",
       "official_name_en                                      NaN   \n",
       "ISO4217-currency_country_name                         NaN   \n",
       "Least Developed Countries (LDC)                       NaN   \n",
       "Region Name                                           NaN   \n",
       "UNTERM Arabic Short                                   NaN   \n",
       "Sub-region Name                                       NaN   \n",
       "official_name_ru                                      NaN   \n",
       "Global Name                                           NaN   \n",
       "Capital                                            Taipei   \n",
       "Continent                                              AS   \n",
       "TLD                                                   .tw   \n",
       "Languages                                zh-TW,zh,nan,hak   \n",
       "Geoname ID                                      1668284.0   \n",
       "CLDR display name                                  Taiwan   \n",
       "EDGAR                                                 NaN   \n",
       "\n",
       "                                                                              1   \n",
       "FIFA                                                                        AFG  \\\n",
       "Dial                                                                         93   \n",
       "ISO3166-1-Alpha-3                                                           AFG   \n",
       "MARC                                                                         af   \n",
       "is_independent                                                              Yes   \n",
       "ISO3166-1-numeric                                                           4.0   \n",
       "GAUL                                                                          1   \n",
       "FIPS                                                                         AF   \n",
       "WMO                                                                          AF   \n",
       "ISO3166-1-Alpha-2                                                            AF   \n",
       "ITU                                                                         AFG   \n",
       "IOC                                                                         AFG   \n",
       "DS                                                                          AFG   \n",
       "UNTERM Spanish Formal                    República Islámica del Afganistán (la)   \n",
       "Global Code                                                                True   \n",
       "Intermediate Region Code                                                    NaN   \n",
       "official_name_fr                                                    Afghanistan   \n",
       "UNTERM French Short                                    Afghanistan (l') [masc.]   \n",
       "ISO4217-currency_name                                                   Afghani   \n",
       "Developed / Developing Countries                                     Developing   \n",
       "UNTERM Russian Formal                           Исламская Республика Афганистан   \n",
       "UNTERM English Short                                                Afghanistan   \n",
       "ISO4217-currency_alphabetic_code                                            AFN   \n",
       "Small Island Developing States (SIDS)                                       NaN   \n",
       "UNTERM Spanish Short                                            Afganistán (el)   \n",
       "ISO4217-currency_numeric_code                                               971   \n",
       "UNTERM Chinese Formal                                                 阿富汗伊斯兰共和国   \n",
       "UNTERM French Formal                         République islamique d'Afghanistan   \n",
       "UNTERM Russian Short                                                 Афганистан   \n",
       "M49                                                                         4.0   \n",
       "Sub-region Code                                                            34.0   \n",
       "Region Code                                                               142.0   \n",
       "official_name_ar                                                      أفغانستان   \n",
       "ISO4217-currency_minor_unit                                                   2   \n",
       "UNTERM Arabic Formal                                جمهورية أفغانستان الإسلامية   \n",
       "UNTERM Chinese Short                                                        阿富汗   \n",
       "Land Locked Developing Countries (LLDC)                                       x   \n",
       "Intermediate Region Name                                                    NaN   \n",
       "official_name_es                                                     Afganistán   \n",
       "UNTERM English Formal                       the Islamic Republic of Afghanistan   \n",
       "official_name_cn                                                            阿富汗   \n",
       "official_name_en                                                    Afghanistan   \n",
       "ISO4217-currency_country_name                                       AFGHANISTAN   \n",
       "Least Developed Countries (LDC)                                               x   \n",
       "Region Name                                                                Asia   \n",
       "UNTERM Arabic Short                                                   أفغانستان   \n",
       "Sub-region Name                                                   Southern Asia   \n",
       "official_name_ru                                                     Афганистан   \n",
       "Global Name                                                               World   \n",
       "Capital                                                                   Kabul   \n",
       "Continent                                                                    AS   \n",
       "TLD                                                                         .af   \n",
       "Languages                                                     fa-AF,ps,uz-AF,tk   \n",
       "Geoname ID                                                            1149361.0   \n",
       "CLDR display name                                                   Afghanistan   \n",
       "EDGAR                                                                        B2   \n",
       "\n",
       "                                                               2   \n",
       "FIFA                                                         ALB  \\\n",
       "Dial                                                         355   \n",
       "ISO3166-1-Alpha-3                                            ALB   \n",
       "MARC                                                          aa   \n",
       "is_independent                                               Yes   \n",
       "ISO3166-1-numeric                                            8.0   \n",
       "GAUL                                                           3   \n",
       "FIPS                                                          AL   \n",
       "WMO                                                           AB   \n",
       "ISO3166-1-Alpha-2                                             AL   \n",
       "ITU                                                          ALB   \n",
       "IOC                                                          ALB   \n",
       "DS                                                            AL   \n",
       "UNTERM Spanish Formal                    la República de Albania   \n",
       "Global Code                                                 True   \n",
       "Intermediate Region Code                                     NaN   \n",
       "official_name_fr                                         Albanie   \n",
       "UNTERM French Short                          Albanie (l') [fém.]   \n",
       "ISO4217-currency_name                                        Lek   \n",
       "Developed / Developing Countries                       Developed   \n",
       "UNTERM Russian Formal                         Республика Албания   \n",
       "UNTERM English Short                                     Albania   \n",
       "ISO4217-currency_alphabetic_code                             ALL   \n",
       "Small Island Developing States (SIDS)                        NaN   \n",
       "UNTERM Spanish Short                                     Albania   \n",
       "ISO4217-currency_numeric_code                                008   \n",
       "UNTERM Chinese Formal                                   阿尔巴尼亚共和国   \n",
       "UNTERM French Formal                     la République d'Albanie   \n",
       "UNTERM Russian Short                                     Албания   \n",
       "M49                                                          8.0   \n",
       "Sub-region Code                                             39.0   \n",
       "Region Code                                                150.0   \n",
       "official_name_ar                                         ألبانيا   \n",
       "ISO4217-currency_minor_unit                                    2   \n",
       "UNTERM Arabic Formal                             جمهورية ألبانيا   \n",
       "UNTERM Chinese Short                                       阿尔巴尼亚   \n",
       "Land Locked Developing Countries (LLDC)                      NaN   \n",
       "Intermediate Region Name                                     NaN   \n",
       "official_name_es                                         Albania   \n",
       "UNTERM English Formal                    the Republic of Albania   \n",
       "official_name_cn                                           阿尔巴尼亚   \n",
       "official_name_en                                         Albania   \n",
       "ISO4217-currency_country_name                            ALBANIA   \n",
       "Least Developed Countries (LDC)                              NaN   \n",
       "Region Name                                               Europe   \n",
       "UNTERM Arabic Short                                      ألبانيا   \n",
       "Sub-region Name                                  Southern Europe   \n",
       "official_name_ru                                         Албания   \n",
       "Global Name                                                World   \n",
       "Capital                                                   Tirana   \n",
       "Continent                                                     EU   \n",
       "TLD                                                          .al   \n",
       "Languages                                                  sq,el   \n",
       "Geoname ID                                              783754.0   \n",
       "CLDR display name                                        Albania   \n",
       "EDGAR                                                         B3   \n",
       "\n",
       "                                                                                         3   \n",
       "FIFA                                                                                   ALG  \\\n",
       "Dial                                                                                   213   \n",
       "ISO3166-1-Alpha-3                                                                      DZA   \n",
       "MARC                                                                                    ae   \n",
       "is_independent                                                                         Yes   \n",
       "ISO3166-1-numeric                                                                     12.0   \n",
       "GAUL                                                                                     4   \n",
       "FIPS                                                                                    AG   \n",
       "WMO                                                                                     AL   \n",
       "ISO3166-1-Alpha-2                                                                       DZ   \n",
       "ITU                                                                                    ALG   \n",
       "IOC                                                                                    ALG   \n",
       "DS                                                                                      DZ   \n",
       "UNTERM Spanish Formal                          la República Argelina Democrática y Popular   \n",
       "Global Code                                                                           True   \n",
       "Intermediate Region Code                                                               NaN   \n",
       "official_name_fr                                                                   Algérie   \n",
       "UNTERM French Short                                                    Algérie (l') [fém.]   \n",
       "ISO4217-currency_name                                                       Algerian Dinar   \n",
       "Developed / Developing Countries                                                Developing   \n",
       "UNTERM Russian Formal                        Алжирская Народная Демократическая Республика   \n",
       "UNTERM English Short                                                               Algeria   \n",
       "ISO4217-currency_alphabetic_code                                                       DZD   \n",
       "Small Island Developing States (SIDS)                                                  NaN   \n",
       "UNTERM Spanish Short                                                               Argelia   \n",
       "ISO4217-currency_numeric_code                                                          012   \n",
       "UNTERM Chinese Formal                                                         阿尔及利亚民主人民共和国   \n",
       "UNTERM French Formal                     la République algérienne démocratique et popul...   \n",
       "UNTERM Russian Short                                                                 Алжир   \n",
       "M49                                                                                   12.0   \n",
       "Sub-region Code                                                                       15.0   \n",
       "Region Code                                                                            2.0   \n",
       "official_name_ar                                                                   الجزائر   \n",
       "ISO4217-currency_minor_unit                                                              2   \n",
       "UNTERM Arabic Formal                               الجمهورية الجزائرية الديمقراطية الشعبية   \n",
       "UNTERM Chinese Short                                                                 阿尔及利亚   \n",
       "Land Locked Developing Countries (LLDC)                                                NaN   \n",
       "Intermediate Region Name                                                               NaN   \n",
       "official_name_es                                                                   Argelia   \n",
       "UNTERM English Formal                          the People's Democratic Republic of Algeria   \n",
       "official_name_cn                                                                     阿尔及利亚   \n",
       "official_name_en                                                                   Algeria   \n",
       "ISO4217-currency_country_name                                                      ALGERIA   \n",
       "Least Developed Countries (LDC)                                                        NaN   \n",
       "Region Name                                                                         Africa   \n",
       "UNTERM Arabic Short                                                                الجزائر   \n",
       "Sub-region Name                                                            Northern Africa   \n",
       "official_name_ru                                                                     Алжир   \n",
       "Global Name                                                                          World   \n",
       "Capital                                                                            Algiers   \n",
       "Continent                                                                               AF   \n",
       "TLD                                                                                    .dz   \n",
       "Languages                                                                            ar-DZ   \n",
       "Geoname ID                                                                       2589581.0   \n",
       "CLDR display name                                                                  Algeria   \n",
       "EDGAR                                                                                   B4   \n",
       "\n",
       "                                                          4  \n",
       "FIFA                                                    ASA  \n",
       "Dial                                                  1-684  \n",
       "ISO3166-1-Alpha-3                                       ASM  \n",
       "MARC                                                     as  \n",
       "is_independent                              Territory of US  \n",
       "ISO3166-1-numeric                                      16.0  \n",
       "GAUL                                                      5  \n",
       "FIPS                                                     AQ  \n",
       "WMO                                                          \n",
       "ISO3166-1-Alpha-2                                        AS  \n",
       "ITU                                                     SMA  \n",
       "IOC                                                     ASA  \n",
       "DS                                                      USA  \n",
       "UNTERM Spanish Formal                                   NaN  \n",
       "Global Code                                            True  \n",
       "Intermediate Region Code                                NaN  \n",
       "official_name_fr                          Samoa américaines  \n",
       "UNTERM French Short                                     NaN  \n",
       "ISO4217-currency_name                             US Dollar  \n",
       "Developed / Developing Countries                 Developing  \n",
       "UNTERM Russian Formal                                   NaN  \n",
       "UNTERM English Short                                    NaN  \n",
       "ISO4217-currency_alphabetic_code                        USD  \n",
       "Small Island Developing States (SIDS)                     x  \n",
       "UNTERM Spanish Short                                    NaN  \n",
       "ISO4217-currency_numeric_code                           840  \n",
       "UNTERM Chinese Formal                                   NaN  \n",
       "UNTERM French Formal                                    NaN  \n",
       "UNTERM Russian Short                                    NaN  \n",
       "M49                                                    16.0  \n",
       "Sub-region Code                                        61.0  \n",
       "Region Code                                             9.0  \n",
       "official_name_ar                            ساموا الأمريكية  \n",
       "ISO4217-currency_minor_unit                               2  \n",
       "UNTERM Arabic Formal                                    NaN  \n",
       "UNTERM Chinese Short                                    NaN  \n",
       "Land Locked Developing Countries (LLDC)                 NaN  \n",
       "Intermediate Region Name                                NaN  \n",
       "official_name_es                            Samoa Americana  \n",
       "UNTERM English Formal                                   NaN  \n",
       "official_name_cn                                      美属萨摩亚  \n",
       "official_name_en                             American Samoa  \n",
       "ISO4217-currency_country_name                AMERICAN SAMOA  \n",
       "Least Developed Countries (LDC)                         NaN  \n",
       "Region Name                                         Oceania  \n",
       "UNTERM Arabic Short                                     NaN  \n",
       "Sub-region Name                                   Polynesia  \n",
       "official_name_ru                         Американское Самоа  \n",
       "Global Name                                           World  \n",
       "Capital                                           Pago Pago  \n",
       "Continent                                                OC  \n",
       "TLD                                                     .as  \n",
       "Languages                                       en-AS,sm,to  \n",
       "Geoname ID                                        5880801.0  \n",
       "CLDR display name                            American Samoa  \n",
       "EDGAR                                                    B5  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "head=geodata.head()\n",
    "head.transpose()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "a859407f",
   "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>Sub-region Name</th>\n",
       "      <th>CLDR display name</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Region Name</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Taiwan</td>\n",
       "      <td>AS</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Southern Asia</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Southern Europe</td>\n",
       "      <td>Albania</td>\n",
       "      <td>EU</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Northern Africa</td>\n",
       "      <td>Algeria</td>\n",
       "      <td>AF</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Polynesia</td>\n",
       "      <td>American Samoa</td>\n",
       "      <td>OC</td>\n",
       "      <td>Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Southern Europe</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>EU</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Sub-Saharan Africa</td>\n",
       "      <td>Angola</td>\n",
       "      <td>AF</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Latin America and the Caribbean</td>\n",
       "      <td>Anguilla</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Antarctica</td>\n",
       "      <td>AN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Latin America and the Caribbean</td>\n",
       "      <td>Antigua &amp; Barbuda</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Americas</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   Sub-region Name  CLDR display name Continent Region Name\n",
       "0                              NaN             Taiwan        AS         NaN\n",
       "1                    Southern Asia        Afghanistan        AS        Asia\n",
       "2                  Southern Europe            Albania        EU      Europe\n",
       "3                  Northern Africa            Algeria        AF      Africa\n",
       "4                        Polynesia     American Samoa        OC     Oceania\n",
       "5                  Southern Europe            Andorra        EU      Europe\n",
       "6               Sub-Saharan Africa             Angola        AF      Africa\n",
       "7  Latin America and the Caribbean           Anguilla       NaN    Americas\n",
       "8                              NaN         Antarctica        AN         NaN\n",
       "9  Latin America and the Caribbean  Antigua & Barbuda       NaN    Americas"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodf=geodata[['Sub-region Name','CLDR display name','Continent','Region Name']]\n",
    "geodf.head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "3949a869",
   "metadata": {},
   "source": [
    "Africa: AF    \n",
    "Antarctica: AN     \n",
    "Asia: AS     \n",
    "Europe: EU     \n",
    "North America: NA    \n",
    "Australia/Oceania: OC     \n",
    "South America: SA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "48d9bd13",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Continent\n",
       "AF    58\n",
       "EU    52\n",
       "AS    51\n",
       "OC    28\n",
       "SA    14\n",
       "AN     5\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodf.Continent.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "7ce44eca",
   "metadata": {},
   "source": [
    "-There is not Continent named for North America, I check there is some value in Sub-region Name, So I want to fill this value by NA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "007b206e",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "/var/folders/bw/mpr2ftmn17q2d9fz0_5r1qrh0000gn/T/ipykernel_3872/1071593433.py:1: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  geodf.loc[(geodf['Continent'].isna()) & (geodf['Sub-region Name'] == 'Northern America'), 'Continent'] = 'NA'\n"
     ]
    }
   ],
   "source": [
    "geodf.loc[(geodf['Continent'].isna()) & (geodf['Sub-region Name'] == 'Northern America'), 'Continent'] = 'NA'"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "6bdf26a6",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Continent\n",
       "AF    58\n",
       "EU    52\n",
       "AS    51\n",
       "OC    28\n",
       "SA    14\n",
       "AN     5\n",
       "NA     5\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "geodf.Continent.value_counts()"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "83e51a6c",
   "metadata": {},
   "source": [
    "Now we have value in NA, but we still need to relate our data so still exist the potential problem."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "id": "208be799",
   "metadata": {},
   "outputs": [],
   "source": [
    "#geodf.to_csv('geodf.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 114,
   "id": "527ba6ee",
   "metadata": {},
   "outputs": [],
   "source": [
    "df_sample = pd.read_csv('/Users/mac/Desktop/MCGILL/RS/COVID19_twitter_full_dataset.csv',nrows=2000000)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 115,
   "id": "533f45c3",
   "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>tweet_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>tweet_timestamp</th>\n",
       "      <th>keyword</th>\n",
       "      <th>country/region</th>\n",
       "      <th>valence_intensity</th>\n",
       "      <th>fear_intensity</th>\n",
       "      <th>anger_intensity</th>\n",
       "      <th>happiness_intensity</th>\n",
       "      <th>sadness_intensity</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>emotion</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1221957211913457664</td>\n",
       "      <td>1319491585</td>\n",
       "      <td>2020-01-27 16-44-36</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>0.336</td>\n",
       "      <td>0.575</td>\n",
       "      <td>0.505</td>\n",
       "      <td>0.184</td>\n",
       "      <td>0.507</td>\n",
       "      <td>negative</td>\n",
       "      <td>fear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1221957309829545985</td>\n",
       "      <td>1629375746</td>\n",
       "      <td>2020-01-27 16-45-00</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>Malaysia</td>\n",
       "      <td>0.445</td>\n",
       "      <td>0.470</td>\n",
       "      <td>0.403</td>\n",
       "      <td>0.263</td>\n",
       "      <td>0.395</td>\n",
       "      <td>negative</td>\n",
       "      <td>fear</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1221957488158789632</td>\n",
       "      <td>894465432</td>\n",
       "      <td>2020-01-27 16-45-42</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>-</td>\n",
       "      <td>0.265</td>\n",
       "      <td>0.718</td>\n",
       "      <td>0.572</td>\n",
       "      <td>0.218</td>\n",
       "      <td>0.694</td>\n",
       "      <td>very negative</td>\n",
       "      <td>fear</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id     user_id      tweet_timestamp keyword   \n",
       "0  1221957211913457664  1319491585  2020-01-27 16-44-36   wuhan  \\\n",
       "1  1221957309829545985  1629375746  2020-01-27 16-45-00   wuhan   \n",
       "2  1221957488158789632   894465432  2020-01-27 16-45-42   wuhan   \n",
       "\n",
       "  country/region  valence_intensity  fear_intensity  anger_intensity   \n",
       "0       Malaysia              0.336           0.575            0.505  \\\n",
       "1       Malaysia              0.445           0.470            0.403   \n",
       "2              -              0.265           0.718            0.572   \n",
       "\n",
       "   happiness_intensity  sadness_intensity      sentiment emotion  \n",
       "0                0.184              0.507       negative    fear  \n",
       "1                0.263              0.395       negative    fear  \n",
       "2                0.218              0.694  very negative    fear  "
      ]
     },
     "execution_count": 115,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sample.head(3)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 116,
   "id": "bfcbbf4c",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df = pd.read_csv('/Users/mac/Desktop/MCGILL/RS/COVID19_twitter_full_dataset.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 117,
   "id": "487c42fc",
   "metadata": {},
   "outputs": [],
   "source": [
    "#df_country = pd.read_csv('/Users/mac/Desktop/MCGILL/RS/COVID19_twitter_full_dataset.csv',usecols= [\"tweet_id\",\"country/region\",\"tweet_timestamp\"])"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "07484e54",
   "metadata": {},
   "source": [
    " # 1.\n",
    "For the period ranging from January 28th, 2020 to September 1st, 2021 display the average number of tweets made per continents (i.e. North America, South America, Africa, Europe, Middle-East and Asia+Australia). Explain your strategy;"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 118,
   "id": "d9a38791",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "country/region\n",
       "United States        418577\n",
       "-                    351321\n",
       "United Kingdom        94362\n",
       "India                 62654\n",
       "Canada                56510\n",
       "                      ...  \n",
       "Micronesia                1\n",
       "Equatorial Guinea         1\n",
       "Tokelau                   1\n",
       "St. Kitts & Nevis         1\n",
       "Holy See                  1\n",
       "Name: count, Length: 248, dtype: int64"
      ]
     },
     "execution_count": 118,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sample['country/region'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 119,
   "id": "24de5db7",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(2000000, 13)"
      ]
     },
     "execution_count": 119,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_sample['tweet_timestamp'] = pd.to_datetime(df_sample['tweet_timestamp'],format='%Y-%m-%d %H-%M-%S')\n",
    "df_sample['tweet_date'] = df_sample['tweet_timestamp'].dt.date #We just need date\n",
    "df_sample.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 120,
   "id": "ac3e2b9d",
   "metadata": {},
   "outputs": [],
   "source": [
    "start_date = pd.to_datetime('2020-01-28')\n",
    "end_date = pd.to_datetime('2021-09-01')\n",
    "\n",
    "# Select rows within the specified time range\n",
    "time_range_df = df_sample[(df_sample['tweet_timestamp'] >= start_date) & (df_sample['tweet_timestamp'] <= end_date)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 121,
   "id": "b7bef479",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(1996975, 13)\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "Timestamp('2020-03-01 11:13:40')"
      ]
     },
     "execution_count": 121,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "print(time_range_df.shape)\n",
    "time_range_df['tweet_timestamp'].max()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 122,
   "id": "670effb3",
   "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>tweet_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>tweet_timestamp</th>\n",
       "      <th>keyword</th>\n",
       "      <th>country/region</th>\n",
       "      <th>valence_intensity</th>\n",
       "      <th>fear_intensity</th>\n",
       "      <th>anger_intensity</th>\n",
       "      <th>happiness_intensity</th>\n",
       "      <th>sadness_intensity</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>emotion</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>Sub-region Name</th>\n",
       "      <th>CLDR display name</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1222066794015285251</td>\n",
       "      <td>714684004495204352</td>\n",
       "      <td>2020-01-28 00:00:03</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.516</td>\n",
       "      <td>0.441</td>\n",
       "      <td>0.341</td>\n",
       "      <td>0.337</td>\n",
       "      <td>0.371</td>\n",
       "      <td>neutral or mixed</td>\n",
       "      <td>no specific emotion</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1222067442031030272</td>\n",
       "      <td>625911078</td>\n",
       "      <td>2020-01-28 00:02:37</td>\n",
       "      <td>corona</td>\n",
       "      <td>China</td>\n",
       "      <td>0.538</td>\n",
       "      <td>0.380</td>\n",
       "      <td>0.364</td>\n",
       "      <td>0.439</td>\n",
       "      <td>0.416</td>\n",
       "      <td>positive</td>\n",
       "      <td>happiness</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1222067569873448960</td>\n",
       "      <td>4300743322</td>\n",
       "      <td>2020-01-28 00:03:08</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.446</td>\n",
       "      <td>0.440</td>\n",
       "      <td>0.399</td>\n",
       "      <td>0.302</td>\n",
       "      <td>0.473</td>\n",
       "      <td>negative</td>\n",
       "      <td>sadness</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1222068165867294720</td>\n",
       "      <td>1330770042</td>\n",
       "      <td>2020-01-28 00:05:30</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.427</td>\n",
       "      <td>0.637</td>\n",
       "      <td>0.516</td>\n",
       "      <td>0.266</td>\n",
       "      <td>0.498</td>\n",
       "      <td>negative</td>\n",
       "      <td>fear</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1222068817431425025</td>\n",
       "      <td>1032188509</td>\n",
       "      <td>2020-01-28 00:08:05</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.506</td>\n",
       "      <td>0.431</td>\n",
       "      <td>0.357</td>\n",
       "      <td>0.331</td>\n",
       "      <td>0.354</td>\n",
       "      <td>neutral or mixed</td>\n",
       "      <td>no specific emotion</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id             user_id     tweet_timestamp keyword   \n",
       "0  1222066794015285251  714684004495204352 2020-01-28 00:00:03   wuhan  \\\n",
       "1  1222067442031030272           625911078 2020-01-28 00:02:37  corona   \n",
       "2  1222067569873448960          4300743322 2020-01-28 00:03:08   wuhan   \n",
       "3  1222068165867294720          1330770042 2020-01-28 00:05:30   wuhan   \n",
       "4  1222068817431425025          1032188509 2020-01-28 00:08:05   wuhan   \n",
       "\n",
       "  country/region  valence_intensity  fear_intensity  anger_intensity   \n",
       "0          China              0.516           0.441            0.341  \\\n",
       "1          China              0.538           0.380            0.364   \n",
       "2          China              0.446           0.440            0.399   \n",
       "3          China              0.427           0.637            0.516   \n",
       "4          China              0.506           0.431            0.357   \n",
       "\n",
       "   happiness_intensity  sadness_intensity         sentiment   \n",
       "0                0.337              0.371  neutral or mixed  \\\n",
       "1                0.439              0.416          positive   \n",
       "2                0.302              0.473          negative   \n",
       "3                0.266              0.498          negative   \n",
       "4                0.331              0.354  neutral or mixed   \n",
       "\n",
       "               emotion  tweet_date Sub-region Name CLDR display name   \n",
       "0  no specific emotion  2020-01-28    Eastern Asia             China  \\\n",
       "1            happiness  2020-01-28    Eastern Asia             China   \n",
       "2              sadness  2020-01-28    Eastern Asia             China   \n",
       "3                 fear  2020-01-28    Eastern Asia             China   \n",
       "4  no specific emotion  2020-01-28    Eastern Asia             China   \n",
       "\n",
       "  Continent Region Name _merge  \n",
       "0        AS        Asia   both  \n",
       "1        AS        Asia   both  \n",
       "2        AS        Asia   both  \n",
       "3        AS        Asia   both  \n",
       "4        AS        Asia   both  "
      ]
     },
     "execution_count": 122,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df = pd.merge(time_range_df, geodf,left_on='country/region', right_on='CLDR display name',indicator=True)\n",
    "merged_df.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 123,
   "id": "6bb0082f",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Continent\n",
       "AS    252439\n",
       "AF     83500\n",
       "EU     76997\n",
       "NA     56604\n",
       "OC     39098\n",
       "SA      9195\n",
       "AN       392\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 123,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "merged_df.Continent.value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 124,
   "id": "37667ebe",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Average number of tweets per continent: 74032.14285714286\n"
     ]
    }
   ],
   "source": [
    "# Group the DataFrame by 'continent' and calculate the average number of tweets\n",
    "average_tweets_per_continent = merged_df.groupby('Continent').size().mean()\n",
    "# Print the average number of tweets per continent\n",
    "print(\"Average number of tweets per continent:\", average_tweets_per_continent)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 125,
   "id": "cb20289d",
   "metadata": {},
   "outputs": [],
   "source": [
    "average_tweets_per_continent = merged_df.groupby('Continent').size().reset_index(name='tweet_count')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 126,
   "id": "e168a8e9",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "  Continent  tweet_count\n",
      "0        AF        83500\n",
      "1        AN          392\n",
      "2        AS       252439\n",
      "3        EU        76997\n",
      "4        NA        56604\n",
      "5        OC        39098\n",
      "6        SA         9195\n"
     ]
    }
   ],
   "source": [
    "print(average_tweets_per_continent)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "bafb8401",
   "metadata": {},
   "source": [
    "To conclud this question, I download the mapping dataset from datapackage ('https://datahub.io/core/country-codes/datapackage.json') and I extracted the informationI want to use, like the country name, Continent, Region incase I need ( which actually I used to add the information about North America."
   ]
  },
  {
   "cell_type": "markdown",
   "id": "1d8f78b3",
   "metadata": {},
   "source": [
    "# 2 \n",
    "Aware of the fact that there are so many idle users, we want to work with users appearing atleast once per month over the period ranging from February 2020 to August 2021."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 127,
   "id": "35076423",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Filter DataFrame for users appearing at least once per month from February 2020 to August 2021\n",
    "start_date = pd.to_datetime('2020-02-01')\n",
    "#end_date = pd.to_datetime('2021-08-31')\n",
    "end_date = time_range_df['tweet_timestamp'].max()\n",
    "# for now I just could run this sample data so based on the size I just use the end date as the last data being selected.\n",
    "\n",
    "active_users = merged_df.groupby('user_id').filter(\n",
    "    lambda x: x['tweet_timestamp'].dt.to_period('M').nunique() >= ((end_date.year - start_date.year) * 12) + (end_date.month - start_date.month) + 1\n",
    ")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 129,
   "id": "f731c825",
   "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>tweet_id</th>\n",
       "      <th>user_id</th>\n",
       "      <th>tweet_timestamp</th>\n",
       "      <th>keyword</th>\n",
       "      <th>country/region</th>\n",
       "      <th>valence_intensity</th>\n",
       "      <th>fear_intensity</th>\n",
       "      <th>anger_intensity</th>\n",
       "      <th>happiness_intensity</th>\n",
       "      <th>sadness_intensity</th>\n",
       "      <th>sentiment</th>\n",
       "      <th>emotion</th>\n",
       "      <th>tweet_date</th>\n",
       "      <th>Sub-region Name</th>\n",
       "      <th>CLDR display name</th>\n",
       "      <th>Continent</th>\n",
       "      <th>Region Name</th>\n",
       "      <th>_merge</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1222066794015285251</td>\n",
       "      <td>714684004495204352</td>\n",
       "      <td>2020-01-28 00:00:03</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.516</td>\n",
       "      <td>0.441</td>\n",
       "      <td>0.341</td>\n",
       "      <td>0.337</td>\n",
       "      <td>0.371</td>\n",
       "      <td>neutral or mixed</td>\n",
       "      <td>no specific emotion</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1222067569873448960</td>\n",
       "      <td>4300743322</td>\n",
       "      <td>2020-01-28 00:03:08</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.446</td>\n",
       "      <td>0.440</td>\n",
       "      <td>0.399</td>\n",
       "      <td>0.302</td>\n",
       "      <td>0.473</td>\n",
       "      <td>negative</td>\n",
       "      <td>sadness</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1222068817431425025</td>\n",
       "      <td>1032188509</td>\n",
       "      <td>2020-01-28 00:08:05</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.506</td>\n",
       "      <td>0.431</td>\n",
       "      <td>0.357</td>\n",
       "      <td>0.331</td>\n",
       "      <td>0.354</td>\n",
       "      <td>neutral or mixed</td>\n",
       "      <td>no specific emotion</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>1222069141755981824</td>\n",
       "      <td>382788364</td>\n",
       "      <td>2020-01-28 00:09:22</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.530</td>\n",
       "      <td>0.385</td>\n",
       "      <td>0.431</td>\n",
       "      <td>0.408</td>\n",
       "      <td>0.376</td>\n",
       "      <td>positive</td>\n",
       "      <td>happiness</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>1222069662374137857</td>\n",
       "      <td>189366570</td>\n",
       "      <td>2020-01-28 00:11:27</td>\n",
       "      <td>wuhan</td>\n",
       "      <td>China</td>\n",
       "      <td>0.488</td>\n",
       "      <td>0.510</td>\n",
       "      <td>0.378</td>\n",
       "      <td>0.318</td>\n",
       "      <td>0.352</td>\n",
       "      <td>neutral or mixed</td>\n",
       "      <td>no specific emotion</td>\n",
       "      <td>2020-01-28</td>\n",
       "      <td>Eastern Asia</td>\n",
       "      <td>China</td>\n",
       "      <td>AS</td>\n",
       "      <td>Asia</td>\n",
       "      <td>both</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              tweet_id             user_id     tweet_timestamp keyword   \n",
       "0  1222066794015285251  714684004495204352 2020-01-28 00:00:03   wuhan  \\\n",
       "2  1222067569873448960          4300743322 2020-01-28 00:03:08   wuhan   \n",
       "4  1222068817431425025          1032188509 2020-01-28 00:08:05   wuhan   \n",
       "6  1222069141755981824           382788364 2020-01-28 00:09:22   wuhan   \n",
       "7  1222069662374137857           189366570 2020-01-28 00:11:27   wuhan   \n",
       "\n",
       "  country/region  valence_intensity  fear_intensity  anger_intensity   \n",
       "0          China              0.516           0.441            0.341  \\\n",
       "2          China              0.446           0.440            0.399   \n",
       "4          China              0.506           0.431            0.357   \n",
       "6          China              0.530           0.385            0.431   \n",
       "7          China              0.488           0.510            0.378   \n",
       "\n",
       "   happiness_intensity  sadness_intensity         sentiment   \n",
       "0                0.337              0.371  neutral or mixed  \\\n",
       "2                0.302              0.473          negative   \n",
       "4                0.331              0.354  neutral or mixed   \n",
       "6                0.408              0.376          positive   \n",
       "7                0.318              0.352  neutral or mixed   \n",
       "\n",
       "               emotion  tweet_date Sub-region Name CLDR display name   \n",
       "0  no specific emotion  2020-01-28    Eastern Asia             China  \\\n",
       "2              sadness  2020-01-28    Eastern Asia             China   \n",
       "4  no specific emotion  2020-01-28    Eastern Asia             China   \n",
       "6            happiness  2020-01-28    Eastern Asia             China   \n",
       "7  no specific emotion  2020-01-28    Eastern Asia             China   \n",
       "\n",
       "  Continent Region Name _merge  \n",
       "0        AS        Asia   both  \n",
       "2        AS        Asia   both  \n",
       "4        AS        Asia   both  \n",
       "6        AS        Asia   both  \n",
       "7        AS        Asia   both  "
      ]
     },
     "execution_count": 129,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "active_users.head()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "995f11b4",
   "metadata": {},
   "source": [
    "## 2.1\n",
    "For each continent select the distinct corresponding users, the average valence, fear, anger, happiness and sadness.\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 130,
   "id": "320bbd7b",
   "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>UserID</th>\n",
       "      <th>valence</th>\n",
       "      <th>fear</th>\n",
       "      <th>anger</th>\n",
       "      <th>happiness</th>\n",
       "      <th>sadness</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>Continent</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>AF</th>\n",
       "      <td>3399</td>\n",
       "      <td>0.440857</td>\n",
       "      <td>0.500298</td>\n",
       "      <td>0.441962</td>\n",
       "      <td>0.280175</td>\n",
       "      <td>0.430544</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AN</th>\n",
       "      <td>28</td>\n",
       "      <td>0.430231</td>\n",
       "      <td>0.479500</td>\n",
       "      <td>0.462538</td>\n",
       "      <td>0.281160</td>\n",
       "      <td>0.425692</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>AS</th>\n",
       "      <td>10202</td>\n",
       "      <td>0.455832</td>\n",
       "      <td>0.495992</td>\n",
       "      <td>0.428414</td>\n",
       "      <td>0.292956</td>\n",
       "      <td>0.425300</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>EU</th>\n",
       "      <td>3260</td>\n",
       "      <td>0.449115</td>\n",
       "      <td>0.496413</td>\n",
       "      <td>0.435863</td>\n",
       "      <td>0.289481</td>\n",
       "      <td>0.422906</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>NA</th>\n",
       "      <td>2575</td>\n",
       "      <td>0.451525</td>\n",
       "      <td>0.493099</td>\n",
       "      <td>0.431245</td>\n",
       "      <td>0.292129</td>\n",
       "      <td>0.422324</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>OC</th>\n",
       "      <td>2145</td>\n",
       "      <td>0.447907</td>\n",
       "      <td>0.503032</td>\n",
       "      <td>0.441189</td>\n",
       "      <td>0.288999</td>\n",
       "      <td>0.428873</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>SA</th>\n",
       "      <td>276</td>\n",
       "      <td>0.456381</td>\n",
       "      <td>0.496484</td>\n",
       "      <td>0.420333</td>\n",
       "      <td>0.294779</td>\n",
       "      <td>0.418702</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           UserID   valence      fear     anger  happiness   sadness\n",
       "Continent                                                           \n",
       "AF           3399  0.440857  0.500298  0.441962   0.280175  0.430544\n",
       "AN             28  0.430231  0.479500  0.462538   0.281160  0.425692\n",
       "AS          10202  0.455832  0.495992  0.428414   0.292956  0.425300\n",
       "EU           3260  0.449115  0.496413  0.435863   0.289481  0.422906\n",
       "NA           2575  0.451525  0.493099  0.431245   0.292129  0.422324\n",
       "OC           2145  0.447907  0.503032  0.441189   0.288999  0.428873\n",
       "SA            276  0.456381  0.496484  0.420333   0.294779  0.418702"
      ]
     },
     "execution_count": 130,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Group the DataFrame by 'continent' and calculate the distinct users and average values for each emotion\n",
    "continent_stats = active_users.groupby('Continent').agg(\n",
    "    UserID=('user_id','nunique'),\n",
    "    valence=('valence_intensity','mean'),\n",
    "    fear=('fear_intensity','mean'),\n",
    "    anger=('anger_intensity','mean'),\n",
    "    happiness=('happiness_intensity','mean'),\n",
    "    sadness=('sadness_intensity','mean')\n",
    ")\n",
    "# Print the statistics for each continent\n",
    "continent_stats\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 131,
   "id": "c1a336a4",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Try to have the dedicated format\n",
    "continent_stats.reset_index(inplace=True)\n",
    "# Create a new column 'continent_simple' based on the values of the 'continent' column\n",
    "continent_stats['continent'] = continent_stats['Continent'].map({\n",
    "    'AF': 'Africa',\n",
    "    'AS': 'Asia',\n",
    "    'AN': 'Antarctica',\n",
    "    'EU': 'Europe',\n",
    "    'NA': 'North America',\n",
    "    'OC': 'Australia/Oceania',\n",
    "    'SA': 'South America'\n",
    "})"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 132,
   "id": "8921dc5b",
   "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>UserID</th>\n",
       "      <th>valence</th>\n",
       "      <th>fear</th>\n",
       "      <th>anger</th>\n",
       "      <th>happiness</th>\n",
       "      <th>sadness</th>\n",
       "      <th>continent</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>3399</td>\n",
       "      <td>0.440857</td>\n",
       "      <td>0.500298</td>\n",
       "      <td>0.441962</td>\n",
       "      <td>0.280175</td>\n",
       "      <td>0.430544</td>\n",
       "      <td>Africa</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>28</td>\n",
       "      <td>0.430231</td>\n",
       "      <td>0.479500</td>\n",
       "      <td>0.462538</td>\n",
       "      <td>0.281160</td>\n",
       "      <td>0.425692</td>\n",
       "      <td>Antarctica</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>10202</td>\n",
       "      <td>0.455832</td>\n",
       "      <td>0.495992</td>\n",
       "      <td>0.428414</td>\n",
       "      <td>0.292956</td>\n",
       "      <td>0.425300</td>\n",
       "      <td>Asia</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3260</td>\n",
       "      <td>0.449115</td>\n",
       "      <td>0.496413</td>\n",
       "      <td>0.435863</td>\n",
       "      <td>0.289481</td>\n",
       "      <td>0.422906</td>\n",
       "      <td>Europe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2575</td>\n",
       "      <td>0.451525</td>\n",
       "      <td>0.493099</td>\n",
       "      <td>0.431245</td>\n",
       "      <td>0.292129</td>\n",
       "      <td>0.422324</td>\n",
       "      <td>North America</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2145</td>\n",
       "      <td>0.447907</td>\n",
       "      <td>0.503032</td>\n",
       "      <td>0.441189</td>\n",
       "      <td>0.288999</td>\n",
       "      <td>0.428873</td>\n",
       "      <td>Australia/Oceania</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>276</td>\n",
       "      <td>0.456381</td>\n",
       "      <td>0.496484</td>\n",
       "      <td>0.420333</td>\n",
       "      <td>0.294779</td>\n",
       "      <td>0.418702</td>\n",
       "      <td>South America</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID   valence      fear     anger  happiness   sadness   \n",
       "0    3399  0.440857  0.500298  0.441962   0.280175  0.430544  \\\n",
       "1      28  0.430231  0.479500  0.462538   0.281160  0.425692   \n",
       "2   10202  0.455832  0.495992  0.428414   0.292956  0.425300   \n",
       "3    3260  0.449115  0.496413  0.435863   0.289481  0.422906   \n",
       "4    2575  0.451525  0.493099  0.431245   0.292129  0.422324   \n",
       "5    2145  0.447907  0.503032  0.441189   0.288999  0.428873   \n",
       "6     276  0.456381  0.496484  0.420333   0.294779  0.418702   \n",
       "\n",
       "           continent  \n",
       "0             Africa  \n",
       "1         Antarctica  \n",
       "2               Asia  \n",
       "3             Europe  \n",
       "4      North America  \n",
       "5  Australia/Oceania  \n",
       "6      South America  "
      ]
     },
     "execution_count": 132,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "continent_stats=continent_stats.drop('Continent',axis=1)\n",
    "continent_stats"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "5b5f0474",
   "metadata": {},
   "source": [
    "Over your created data, we want to perform a PCA. But due to the size of the data, you are asked to first do a stratified sampling by selecting 40% of the dataset."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "9a146ca8",
   "metadata": {},
   "outputs": [],
   "source": [
    "from sklearn.model_selection import train_test_split\n",
    "# Perform stratified sampling to select 40% of the data\n",
    "#sampled_data, _ = train_test_split(active_users, test_size=0.4, stratify=df['user_id'])\n",
    "# Print the sampled data\n",
    "#print(sampled_data)\n",
    "#For now the data size wouldn't be the problem so I used all of them\n"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "f087113b",
   "metadata": {},
   "source": [
    "## 2.3\n",
    "Over the 40% you are asked to do a PCA and display following the two first components the scatter plot coloured with respect to the continents. Make an interpretation of your results."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "45fc5a41",
   "metadata": {},
   "outputs": [],
   "source": [
    "import matplotlib.pyplot as plt\n",
    "from sklearn.decomposition import PCA"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "d9baa1fb",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'active_users' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [3]\u001b[0m, in \u001b[0;36m<cell line: 2>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m#extract the numerical columns from the DataFrame of active users into a separate variable\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m numerical_columns \u001b[38;5;241m=\u001b[39m \u001b[43mactive_users\u001b[49m\u001b[38;5;241m.\u001b[39mselect_dtypes(include\u001b[38;5;241m=\u001b[39mnp\u001b[38;5;241m.\u001b[39mnumber)\u001b[38;5;241m.\u001b[39mdrop(\u001b[38;5;124m'\u001b[39m\u001b[38;5;124muser_id\u001b[39m\u001b[38;5;124m'\u001b[39m, axis\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m1\u001b[39m)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'active_users' is not defined"
     ]
    }
   ],
   "source": [
    "#extract the numerical columns from the DataFrame of active users into a separate variable\n",
    "numerical_columns = active_users.select_dtypes(include=np.number).drop('user_id', axis=1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "86d0a89d",
   "metadata": {},
   "outputs": [
    {
     "ename": "NameError",
     "evalue": "name 'PCA' is not defined",
     "output_type": "error",
     "traceback": [
      "\u001b[0;31m---------------------------------------------------------------------------\u001b[0m",
      "\u001b[0;31mNameError\u001b[0m                                 Traceback (most recent call last)",
      "Input \u001b[0;32mIn [1]\u001b[0m, in \u001b[0;36m<cell line: 2>\u001b[0;34m()\u001b[0m\n\u001b[1;32m      1\u001b[0m \u001b[38;5;66;03m#Perform PCA on the features:\u001b[39;00m\n\u001b[0;32m----> 2\u001b[0m pca \u001b[38;5;241m=\u001b[39m \u001b[43mPCA\u001b[49m(n_components\u001b[38;5;241m=\u001b[39m\u001b[38;5;241m2\u001b[39m)\n\u001b[1;32m      3\u001b[0m principal_components \u001b[38;5;241m=\u001b[39m pca\u001b[38;5;241m.\u001b[39mfit_transform(numerical_columns)\n",
      "\u001b[0;31mNameError\u001b[0m: name 'PCA' is not defined"
     ]
    }
   ],
   "source": [
    "#Perform PCA on the features:\n",
    "pca = PCA(n_components=2)\n",
    "principal_components = pca.fit_transform(numerical_columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "83a72b96",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a new DataFrame combining the principal components and continent labels:\n",
    "pca_df = pd.DataFrame(data=principal_components, columns=['PC1', 'PC2'])\n",
    "pca_df['Continent'] = continents\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "94827bb7",
   "metadata": {},
   "outputs": [],
   "source": [
    "#Create a scatter plot:\n",
    "plt.figure(figsize=(10, 8))\n",
    "colors = ['blue', 'green', 'red', 'purple', 'orange', 'yellow']  # Assign colors to continents\n",
    "\n",
    "for continent, color in zip(pca_df['Continent'].unique(), colors):\n",
    "    subset = pca_df[pca_df['Continent'] == continent]\n",
    "    plt.scatter(subset['PC1'], subset['PC2'], c=color, label=continent, alpha=0.5)\n",
    "\n",
    "plt.xlabel('Principal Component 1')\n",
    "plt.ylabel('Principal Component 2')\n",
    "plt.title('PCA Scatter Plot')\n",
    "plt.legend()\n",
    "plt.show()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "d8a331c6",
   "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}