work/notebook/0_Take_a_look_dataset.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Data Engineering Capstone Project\n",
    "# Datasets Raw Exploration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "+ sudo apt-get update -y\n",
      "Get:1 http://archive.ubuntu.com/ubuntu bionic InRelease [242 kB]\n",
      "Get:2 http://archive.ubuntu.com/ubuntu bionic-updates InRelease [88.7 kB]      \n",
      "Get:4 http://archive.ubuntu.com/ubuntu bionic-backports InRelease [74.6 kB]\n",
      "Get:5 http://security.ubuntu.com/ubuntu bionic-security InRelease [88.7 kB]    \n",
      "Get:6 http://archive.ubuntu.com/ubuntu bionic/main amd64 Packages [1,344 kB]   \n",
      "Get:3 http://repos.mesosphere.com/ubuntu xenial InRelease [5,275 B]            \n",
      "Get:7 http://archive.ubuntu.com/ubuntu bionic/multiverse amd64 Packages [186 kB]\n",
      "Get:8 http://archive.ubuntu.com/ubuntu bionic/universe amd64 Packages [11.3 MB]\n",
      "Get:9 http://archive.ubuntu.com/ubuntu bionic/restricted amd64 Packages [13.5 kB]\n",
      "Get:10 http://archive.ubuntu.com/ubuntu bionic-updates/restricted amd64 Packages [73.6 kB]\n",
      "Get:11 http://archive.ubuntu.com/ubuntu bionic-updates/universe amd64 Packages [1,385 kB]\n",
      "Get:12 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 Packages [1,226 kB]\n",
      "Get:13 http://archive.ubuntu.com/ubuntu bionic-updates/multiverse amd64 Packages [20.1 kB]\n",
      "Get:14 http://archive.ubuntu.com/ubuntu bionic-backports/main amd64 Packages [8,286 B]\n",
      "Get:15 http://archive.ubuntu.com/ubuntu bionic-backports/universe amd64 Packages [8,158 B]\n",
      "Get:16 http://repos.mesosphere.com/ubuntu xenial/main amd64 Packages [20.6 kB] \n",
      "Get:17 http://security.ubuntu.com/ubuntu bionic-security/universe amd64 Packages [852 kB]\n",
      "Get:18 http://security.ubuntu.com/ubuntu bionic-security/restricted amd64 Packages [59.3 kB]\n",
      "Get:19 http://security.ubuntu.com/ubuntu bionic-security/multiverse amd64 Packages [8,815 B]\n",
      "Get:20 http://security.ubuntu.com/ubuntu bionic-security/main amd64 Packages [930 kB]\n",
      "Fetched 18.0 MB in 3s (5,707 kB/s)                        \n",
      "Reading package lists... Done\n",
      "+ sudo apt-get upgrade -y\n",
      "Reading package lists... Done\n",
      "Building dependency tree       \n",
      "Reading state information... Done\n",
      "Calculating upgrade... Done\n",
      "The following packages have been kept back:\n",
      "  mesos\n",
      "The following packages will be upgraded:\n",
      "  apt libapt-pkg5.0 libldap-2.4-2 libldap-common libpulse0 libsystemd0\n",
      "  libudev1 linux-libc-dev tzdata\n",
      "9 upgraded, 0 newly installed, 0 to remove and 1 not upgraded.\n",
      "Need to get 3,894 kB of archives.\n",
      "After this operation, 10.2 kB of additional disk space will be used.\n",
      "Get:1 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libsystemd0 amd64 237-3ubuntu10.40 [207 kB]\n",
      "Get:2 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libudev1 amd64 237-3ubuntu10.40 [56.7 kB]\n",
      "Get:3 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libapt-pkg5.0 amd64 1.6.12ubuntu0.1 [807 kB]\n",
      "Get:4 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 apt amd64 1.6.12ubuntu0.1 [1,201 kB]\n",
      "Get:5 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 tzdata all 2020a-0ubuntu0.18.04 [190 kB]\n",
      "Get:6 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libldap-common all 2.4.45+dfsg-1ubuntu1.5 [16.9 kB]\n",
      "Get:7 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libldap-2.4-2 amd64 2.4.45+dfsg-1ubuntu1.5 [155 kB]\n",
      "Get:8 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 libpulse0 amd64 1:11.1-1ubuntu7.7 [267 kB]\n",
      "Get:9 http://archive.ubuntu.com/ubuntu bionic-updates/main amd64 linux-libc-dev amd64 4.15.0-101.102 [994 kB]\n",
      "Fetched 3,894 kB in 0s (15.9 MB/s)      \n",
      "debconf: delaying package configuration, since apt-utils is not installed\n",
      "(Reading database ... 121612 files and directories currently installed.)\n",
      "Preparing to unpack .../libsystemd0_237-3ubuntu10.40_amd64.deb ...\n",
      "Unpacking libsystemd0:amd64 (237-3ubuntu10.40) over (237-3ubuntu10.39) ...\n",
      "Setting up libsystemd0:amd64 (237-3ubuntu10.40) ...\n",
      "(Reading database ... 121612 files and directories currently installed.)\n",
      "Preparing to unpack .../libudev1_237-3ubuntu10.40_amd64.deb ...\n",
      "Unpacking libudev1:amd64 (237-3ubuntu10.40) over (237-3ubuntu10.39) ...\n",
      "Setting up libudev1:amd64 (237-3ubuntu10.40) ...\n",
      "(Reading database ... 121612 files and directories currently installed.)\n",
      "Preparing to unpack .../libapt-pkg5.0_1.6.12ubuntu0.1_amd64.deb ...\n",
      "Unpacking libapt-pkg5.0:amd64 (1.6.12ubuntu0.1) over (1.6.12) ...\n",
      "Setting up libapt-pkg5.0:amd64 (1.6.12ubuntu0.1) ...\n",
      "(Reading database ... 121612 files and directories currently installed.)\n",
      "Preparing to unpack .../apt_1.6.12ubuntu0.1_amd64.deb ...\n",
      "Unpacking apt (1.6.12ubuntu0.1) over (1.6.12) ...\n",
      "Setting up apt (1.6.12ubuntu0.1) ...\n",
      "(Reading database ... 121612 files and directories currently installed.)\n",
      "Preparing to unpack .../tzdata_2020a-0ubuntu0.18.04_all.deb ...\n",
      "Unpacking tzdata (2020a-0ubuntu0.18.04) over (2019c-0ubuntu0.18.04) ...\n",
      "Preparing to unpack .../libldap-common_2.4.45+dfsg-1ubuntu1.5_all.deb ...\n",
      "Unpacking libldap-common (2.4.45+dfsg-1ubuntu1.5) over (2.4.45+dfsg-1ubuntu1.4) ...\n",
      "Preparing to unpack .../libldap-2.4-2_2.4.45+dfsg-1ubuntu1.5_amd64.deb ...\n",
      "Unpacking libldap-2.4-2:amd64 (2.4.45+dfsg-1ubuntu1.5) over (2.4.45+dfsg-1ubuntu1.4) ...\n",
      "Preparing to unpack .../libpulse0_1%3a11.1-1ubuntu7.7_amd64.deb ...\n",
      "Unpacking libpulse0:amd64 (1:11.1-1ubuntu7.7) over (1:11.1-1ubuntu7.5) ...\n",
      "Preparing to unpack .../linux-libc-dev_4.15.0-101.102_amd64.deb ...\n",
      "Unpacking linux-libc-dev:amd64 (4.15.0-101.102) over (4.15.0-99.100) ...\n",
      "Setting up libldap-common (2.4.45+dfsg-1ubuntu1.5) ...\n",
      "Setting up tzdata (2020a-0ubuntu0.18.04) ...\n",
      "debconf: unable to initialize frontend: Dialog\n",
      "debconf: (No usable dialog-like program is installed, so the dialog based frontend cannot be used. at /usr/share/perl5/Debconf/FrontEnd/Dialog.pm line 76.)\n",
      "debconf: falling back to frontend: Readline\n",
      "\n",
      "Current default time zone: 'Etc/UTC'\n",
      "Local time is now:      Sun May 24 08:20:20 UTC 2020.\n",
      "Universal Time is now:  Sun May 24 08:20:20 UTC 2020.\n",
      "Run 'dpkg-reconfigure tzdata' if you wish to change it.\n",
      "\n",
      "Setting up linux-libc-dev:amd64 (4.15.0-101.102) ...\n",
      "Setting up libpulse0:amd64 (1:11.1-1ubuntu7.7) ...\n",
      "Setting up libldap-2.4-2:amd64 (2.4.45+dfsg-1ubuntu1.5) ...\n",
      "Processing triggers for libc-bin (2.27-3ubuntu1) ...\n",
      "+ sudo apt-get install htop\n",
      "Reading package lists... Done\n",
      "Building dependency tree       \n",
      "Reading state information... Done\n",
      "Suggested packages:\n",
      "  lsof strace\n",
      "The following NEW packages will be installed:\n",
      "  htop\n",
      "0 upgraded, 1 newly installed, 0 to remove and 1 not upgraded.\n",
      "Need to get 80.0 kB of archives.\n",
      "After this operation, 221 kB of additional disk space will be used.\n",
      "Get:1 http://archive.ubuntu.com/ubuntu bionic/main amd64 htop amd64 2.1.0-3 [80.0 kB]\n",
      "Fetched 80.0 kB in 0s (1,337 kB/s)\n",
      "debconf: delaying package configuration, since apt-utils is not installed\n",
      "Selecting previously unselected package htop.\n",
      "(Reading database ... 121615 files and directories currently installed.)\n",
      "Preparing to unpack .../htop_2.1.0-3_amd64.deb ...\n",
      "Unpacking htop (2.1.0-3) ...\n",
      "Setting up htop (2.1.0-3) ...\n",
      "Processing triggers for mime-support (3.60ubuntu1) ...\n",
      "+ python3 -m pip install --user --upgrade pip\n",
      "Requirement already up-to-date: pip in /home/anthelix/.local/lib/python3.7/site-packages (20.1.1)\n",
      "+ python3 -m pip install -r ../requirements.txt --upgrade\n",
      "Collecting psycopg2-binary\n",
      "  Using cached psycopg2_binary-2.8.5-cp37-cp37m-manylinux1_x86_64.whl (2.9 MB)\n",
      "Processing /home/anthelix/.cache/pip/wheels/5e/9a/e0/2ce591d3bc02114f16ae1cdd7a4e34ff4d3b99eca54eeee53a/bokeh-2.0.2-py3-none-any.whl\n",
      "Collecting plotly\n",
      "  Using cached plotly-4.7.1-py2.py3-none-any.whl (11.5 MB)\n",
      "Collecting chart_studio\n",
      "  Using cached chart_studio-1.1.0-py3-none-any.whl (64 kB)\n",
      "Requirement already up-to-date: numpy in /opt/conda/lib/python3.7/site-packages (from -r ../requirements.txt (line 5)) (1.18.4)\n",
      "Requirement already up-to-date: scipy in /opt/conda/lib/python3.7/site-packages (from -r ../requirements.txt (line 6)) (1.4.1)\n",
      "Collecting boto3\n",
      "  Using cached boto3-1.13.16-py2.py3-none-any.whl (128 kB)\n",
      "Collecting python-dotenv\n",
      "  Using cached python_dotenv-0.13.0-py2.py3-none-any.whl (17 kB)\n",
      "Requirement already satisfied, skipping upgrade: python-dateutil>=2.1 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (2.8.1)\n",
      "Requirement already satisfied, skipping upgrade: pillow>=4.0 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (7.1.2)\n",
      "Requirement already satisfied, skipping upgrade: typing-extensions>=3.7.4 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (3.7.4.2)\n",
      "Requirement already satisfied, skipping upgrade: tornado>=5 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (6.0.4)\n",
      "Requirement already satisfied, skipping upgrade: packaging>=16.8 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (20.1)\n"
     ]
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Requirement already satisfied, skipping upgrade: Jinja2>=2.7 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (2.11.2)\n",
      "Requirement already satisfied, skipping upgrade: PyYAML>=3.10 in /opt/conda/lib/python3.7/site-packages (from bokeh->-r ../requirements.txt (line 2)) (5.3.1)\n",
      "Requirement already satisfied, skipping upgrade: six in /opt/conda/lib/python3.7/site-packages (from plotly->-r ../requirements.txt (line 3)) (1.14.0)\n",
      "Processing /home/anthelix/.cache/pip/wheels/f9/8d/8d/f6af3f7f9eea3553bc2fe6d53e4b287dad18b06a861ac56ddf/retrying-1.3.3-py3-none-any.whl\n",
      "Requirement already satisfied, skipping upgrade: requests in /opt/conda/lib/python3.7/site-packages (from chart_studio->-r ../requirements.txt (line 4)) (2.23.0)\n",
      "Collecting s3transfer<0.4.0,>=0.3.0\n",
      "  Using cached s3transfer-0.3.3-py2.py3-none-any.whl (69 kB)\n",
      "Collecting botocore<1.17.0,>=1.16.16\n",
      "  Using cached botocore-1.16.16-py2.py3-none-any.whl (6.2 MB)\n",
      "Collecting jmespath<1.0.0,>=0.7.1\n",
      "  Using cached jmespath-0.10.0-py2.py3-none-any.whl (24 kB)\n",
      "Requirement already satisfied, skipping upgrade: pyparsing>=2.0.2 in /opt/conda/lib/python3.7/site-packages (from packaging>=16.8->bokeh->-r ../requirements.txt (line 2)) (2.4.7)\n",
      "Requirement already satisfied, skipping upgrade: MarkupSafe>=0.23 in /opt/conda/lib/python3.7/site-packages (from Jinja2>=2.7->bokeh->-r ../requirements.txt (line 2)) (1.1.1)\n",
      "Requirement already satisfied, skipping upgrade: certifi>=2017.4.17 in /opt/conda/lib/python3.7/site-packages (from requests->chart_studio->-r ../requirements.txt (line 4)) (2020.4.5.1)\n",
      "Requirement already satisfied, skipping upgrade: idna<3,>=2.5 in /opt/conda/lib/python3.7/site-packages (from requests->chart_studio->-r ../requirements.txt (line 4)) (2.9)\n",
      "Requirement already satisfied, skipping upgrade: urllib3!=1.25.0,!=1.25.1,<1.26,>=1.21.1 in /opt/conda/lib/python3.7/site-packages (from requests->chart_studio->-r ../requirements.txt (line 4)) (1.25.9)\n",
      "Requirement already satisfied, skipping upgrade: chardet<4,>=3.0.2 in /opt/conda/lib/python3.7/site-packages (from requests->chart_studio->-r ../requirements.txt (line 4)) (3.0.4)\n",
      "Collecting docutils<0.16,>=0.10\n",
      "  Using cached docutils-0.15.2-py3-none-any.whl (547 kB)\n",
      "Installing collected packages: psycopg2-binary, bokeh, retrying, plotly, chart-studio, docutils, jmespath, botocore, s3transfer, boto3, python-dotenv\n",
      "  Attempting uninstall: bokeh\n",
      "    Found existing installation: bokeh 2.0.1\n",
      "    Uninstalling bokeh-2.0.1:\n",
      "      Successfully uninstalled bokeh-2.0.1\n",
      "Successfully installed bokeh-2.0.2 boto3-1.13.16 botocore-1.16.16 chart-studio-1.1.0 docutils-0.15.2 jmespath-0.10.0 plotly-4.7.1 psycopg2-binary-2.8.5 python-dotenv-0.13.0 retrying-1.3.3 s3transfer-0.3.3\n",
      "+ POSTGRES_JAR=postgresql-42.2.10.jar\n",
      "+ [ -f postgresql-42.2.10.jar ]\n",
      "+ echo postgresql-42.2.10.jar exist\n",
      "postgresql-42.2.10.jar exist\n",
      "+ sudo cp log4j.properties /usr/local/spark/conf/log4j.properties\n",
      "cp: cannot stat 'log4j.properties': No such file or directory\n"
     ]
    }
   ],
   "source": [
    "!sh ../bootstrap_jupyter.sh"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "import os\n",
    "import sys\n",
    "import datetime\n",
    "import numpy as np\n",
    "import pandas as pd\n",
    "path = '../../data/'\n",
    "pd.set_option('display.max_columns', None)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "list_filename= ['I94_SAS_Labels_Descriptions.SAS', 'immigration_data_sample.csv', \n",
    "                \"GlobalLandTemperaturesByCity.csv\", 'wikipedia-iso-country-codes.csv',\n",
    "                \"airports-extended.csv\",\"airport-codes_csv.csv\", 'us-cities-demographics.csv',\n",
    "               'WDIData.csv', \"education-statistics/EdStatsData.csv\"]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def sum1forline(list_filename):\n",
    "    my_sum = 0\n",
    "    path = '../../data/'\n",
    "    for filename in list_filename:\n",
    "        with open(path+filename) as f:\n",
    "            a = sum(1 for line in f)\n",
    "            print(\"{} rows in {} .\".format(a, filename))\n",
    "            my_sum+=a\n",
    "            \n",
    "    return(\"Total of the files : {:,}\".format(my_sum))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "1100 rows in I94_SAS_Labels_Descriptions.SAS .\n",
      "1001 rows in immigration_data_sample.csv .\n",
      "8599213 rows in GlobalLandTemperaturesByCity.csv .\n",
      "247 rows in wikipedia-iso-country-codes.csv .\n",
      "10668 rows in airports-extended.csv .\n",
      "55076 rows in airport-codes_csv.csv .\n",
      "2892 rows in us-cities-demographics.csv .\n",
      "422137 rows in WDIData.csv .\n",
      "886931 rows in education-statistics/EdStatsData.csv .\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "'Total of the files : 9,979,265'"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "sum1forline(list_filename)"
   ]
  },
  {
   "cell_type": "raw",
   "metadata": {},
   "source": []
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## I94 Immigration Data\n",
    "\n",
    "* immigration_data_sample.csv\n",
    "* There are 1000 rows and 29 columns in *immigration_data_sample.csv*.\n",
    "* df_immigration"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 1000 rows and 29 columns in  *immigration_data_sample.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>2938436</td>\n",
       "      <td>5960799.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>245.0</td>\n",
       "      <td>SAI</td>\n",
       "      <td>20545.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>20550.0</td>\n",
       "      <td>30.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160615</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>P</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1986.0</td>\n",
       "      <td>04132016</td>\n",
       "      <td>M</td>\n",
       "      <td>3882.0</td>\n",
       "      <td>MU</td>\n",
       "      <td>4.416258e+10</td>\n",
       "      <td>00763</td>\n",
       "      <td>CP</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>35</th>\n",
       "      <td>3020206</td>\n",
       "      <td>6055103.0</td>\n",
       "      <td>2016.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>252.0</td>\n",
       "      <td>209.0</td>\n",
       "      <td>AGA</td>\n",
       "      <td>20572.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>GU</td>\n",
       "      <td>20577.0</td>\n",
       "      <td>27.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>20160801</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>A</td>\n",
       "      <td>D</td>\n",
       "      <td>NaN</td>\n",
       "      <td>M</td>\n",
       "      <td>1989.0</td>\n",
       "      <td>06112016</td>\n",
       "      <td>M</td>\n",
       "      <td>3943.0</td>\n",
       "      <td>TW</td>\n",
       "      <td>5.109649e+10</td>\n",
       "      <td>00311</td>\n",
       "      <td>GMT</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    Unnamed: 0      cicid   i94yr  i94mon  i94cit  i94res i94port  arrdate  \\\n",
       "15     2938436  5960799.0  2016.0     4.0   245.0   245.0     SAI  20545.0   \n",
       "35     3020206  6055103.0  2016.0     4.0   252.0   209.0     AGA  20572.0   \n",
       "\n",
       "    i94mode i94addr  depdate  i94bir  i94visa  count  dtadfile visapost occup  \\\n",
       "15      1.0     NaN  20550.0    30.0      2.0    1.0  20160615      NaN   NaN   \n",
       "35      1.0      GU  20577.0    27.0      2.0    1.0  20160801      NaN   NaN   \n",
       "\n",
       "   entdepa entdepd  entdepu matflag  biryear   dtaddto gender  insnum airline  \\\n",
       "15       P       D      NaN       M   1986.0  04132016      M  3882.0      MU   \n",
       "35       A       D      NaN       M   1989.0  06112016      M  3943.0      TW   \n",
       "\n",
       "          admnum  fltno visatype  \n",
       "15  4.416258e+10  00763       CP  \n",
       "35  5.109649e+10  00311      GMT  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Unnamed: 0</th>\n",
       "      <th>cicid</th>\n",
       "      <th>i94yr</th>\n",
       "      <th>i94mon</th>\n",
       "      <th>i94cit</th>\n",
       "      <th>i94res</th>\n",
       "      <th>i94port</th>\n",
       "      <th>arrdate</th>\n",
       "      <th>i94mode</th>\n",
       "      <th>i94addr</th>\n",
       "      <th>depdate</th>\n",
       "      <th>i94bir</th>\n",
       "      <th>i94visa</th>\n",
       "      <th>count</th>\n",
       "      <th>dtadfile</th>\n",
       "      <th>visapost</th>\n",
       "      <th>occup</th>\n",
       "      <th>entdepa</th>\n",
       "      <th>entdepd</th>\n",
       "      <th>entdepu</th>\n",
       "      <th>matflag</th>\n",
       "      <th>biryear</th>\n",
       "      <th>dtaddto</th>\n",
       "      <th>gender</th>\n",
       "      <th>insnum</th>\n",
       "      <th>airline</th>\n",
       "      <th>admnum</th>\n",
       "      <th>fltno</th>\n",
       "      <th>visatype</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>59</td>\n",
       "      <td>49</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>618</td>\n",
       "      <td>996</td>\n",
       "      <td>0</td>\n",
       "      <td>46</td>\n",
       "      <td>1000</td>\n",
       "      <td>46</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>141</td>\n",
       "      <td>965</td>\n",
       "      <td>33</td>\n",
       "      <td>0</td>\n",
       "      <td>8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>5.9</td>\n",
       "      <td>4.9</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>61.8</td>\n",
       "      <td>99.6</td>\n",
       "      <td>0</td>\n",
       "      <td>4.6</td>\n",
       "      <td>100</td>\n",
       "      <td>4.6</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>14.1</td>\n",
       "      <td>96.5</td>\n",
       "      <td>3.3</td>\n",
       "      <td>0</td>\n",
       "      <td>0.8</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Unnamed: 0    cicid    i94yr   i94mon   i94cit   i94res  \\\n",
       "column type           int64  float64  float64  float64  float64  float64   \n",
       "null values (nb)          0        0        0        0        0        0   \n",
       "null values (%)           0        0        0        0        0        0   \n",
       "\n",
       "                 i94port  arrdate  i94mode i94addr  depdate   i94bir  i94visa  \\\n",
       "column type       object  float64  float64  object  float64  float64  float64   \n",
       "null values (nb)       0        0        0      59       49        0        0   \n",
       "null values (%)        0        0        0     5.9      4.9        0        0   \n",
       "\n",
       "                    count dtadfile visapost   occup entdepa entdepd  entdepu  \\\n",
       "column type       float64    int64   object  object  object  object  float64   \n",
       "null values (nb)        0        0      618     996       0      46     1000   \n",
       "null values (%)         0        0     61.8    99.6       0     4.6      100   \n",
       "\n",
       "                 matflag  biryear dtaddto  gender   insnum airline   admnum  \\\n",
       "column type       object  float64  object  object  float64  object  float64   \n",
       "null values (nb)      46        0       0     141      965      33        0   \n",
       "null values (%)      4.6        0       0    14.1     96.5     3.3        0   \n",
       "\n",
       "                   fltno visatype  \n",
       "column type       object   object  \n",
       "null values (nb)       8        0  \n",
       "null values (%)      0.8        0  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    }
   ],
   "source": [
    "file = \"immigration_data_sample.csv\"\n",
    "df = pd.read_csv(path+file, sep=\",\")\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "display(df.dropna(subset=[\"insnum\"]).head(2))\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "display(tab_info)\n",
    "#change subset\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Global Land Temperature Data\n",
    "* GlobalLandTemperaturesByCity.csv\n",
    "* There are 8599212 rows and 7 columns in  *GlobalLandTemperaturesByCity.csv*. \n",
    "* df_temperature"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 8599212 rows and 7 columns in  *GlobalLandTemperaturesByCity.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1743-11-01</td>\n",
       "      <td>6.068</td>\n",
       "      <td>1.737</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1743-12-01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Århus</td>\n",
       "      <td>Denmark</td>\n",
       "      <td>57.05N</td>\n",
       "      <td>10.33E</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           dt  AverageTemperature  AverageTemperatureUncertainty   City  \\\n",
       "0  1743-11-01               6.068                          1.737  Århus   \n",
       "1  1743-12-01                 NaN                            NaN  Århus   \n",
       "\n",
       "   Country Latitude Longitude  \n",
       "0  Denmark   57.05N    10.33E  \n",
       "1  Denmark   57.05N    10.33E  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>dt</th>\n",
       "      <th>AverageTemperature</th>\n",
       "      <th>AverageTemperatureUncertainty</th>\n",
       "      <th>City</th>\n",
       "      <th>Country</th>\n",
       "      <th>Latitude</th>\n",
       "      <th>Longitude</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>364130</td>\n",
       "      <td>364130</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>4.23446</td>\n",
       "      <td>4.23446</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                      dt AverageTemperature AverageTemperatureUncertainty  \\\n",
       "column type       object            float64                       float64   \n",
       "null values (nb)       0             364130                        364130   \n",
       "null values (%)        0            4.23446                       4.23446   \n",
       "\n",
       "                    City Country Latitude Longitude  \n",
       "column type       object  object   object    object  \n",
       "null values (nb)       0       0        0         0  \n",
       "null values (%)        0       0        0         0  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"GlobalLandTemperaturesByCity.csv\"\n",
    "df = pd.read_csv(path+file, sep=\",\", nrows=9000000)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "display(df.head(2))\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset\n",
    "#df.dropna(subset=[\"insnum\"]).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Airports Code Data\n",
    "* airport-codes_csv.csv\n",
    "* There are 55075 rows and 12 columns in  *airport-codes_csv.csv*.\n",
    "* df_airport_code"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 55075 rows and 12 columns in  *airport-codes_csv.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>ident</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>elevation_ft</th>\n",
       "      <th>continent</th>\n",
       "      <th>iso_country</th>\n",
       "      <th>iso_region</th>\n",
       "      <th>municipality</th>\n",
       "      <th>gps_code</th>\n",
       "      <th>iata_code</th>\n",
       "      <th>local_code</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>223</th>\n",
       "      <td>03N</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Utirik Airport</td>\n",
       "      <td>4.0</td>\n",
       "      <td>OC</td>\n",
       "      <td>MH</td>\n",
       "      <td>MH-UTI</td>\n",
       "      <td>Utirik Island</td>\n",
       "      <td>K03N</td>\n",
       "      <td>UTK</td>\n",
       "      <td>03N</td>\n",
       "      <td>169.852005, 11.222</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>440</th>\n",
       "      <td>07FA</td>\n",
       "      <td>small_airport</td>\n",
       "      <td>Ocean Reef Club Airport</td>\n",
       "      <td>8.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>US</td>\n",
       "      <td>US-FL</td>\n",
       "      <td>Key Largo</td>\n",
       "      <td>07FA</td>\n",
       "      <td>OCA</td>\n",
       "      <td>07FA</td>\n",
       "      <td>-80.274803161621, 25.325399398804</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    ident           type                     name  elevation_ft continent  \\\n",
       "223   03N  small_airport           Utirik Airport           4.0        OC   \n",
       "440  07FA  small_airport  Ocean Reef Club Airport           8.0       NaN   \n",
       "\n",
       "    iso_country iso_region   municipality gps_code iata_code local_code  \\\n",
       "223          MH     MH-UTI  Utirik Island     K03N       UTK        03N   \n",
       "440          US      US-FL      Key Largo     07FA       OCA       07FA   \n",
       "\n",
       "                           coordinates  \n",
       "223                 169.852005, 11.222  \n",
       "440  -80.274803161621, 25.325399398804  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>ident</th>\n",
       "      <th>type</th>\n",
       "      <th>name</th>\n",
       "      <th>elevation_ft</th>\n",
       "      <th>continent</th>\n",
       "      <th>iso_country</th>\n",
       "      <th>iso_region</th>\n",
       "      <th>municipality</th>\n",
       "      <th>gps_code</th>\n",
       "      <th>iata_code</th>\n",
       "      <th>local_code</th>\n",
       "      <th>coordinates</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>7006</td>\n",
       "      <td>27719</td>\n",
       "      <td>247</td>\n",
       "      <td>0</td>\n",
       "      <td>5676</td>\n",
       "      <td>14045</td>\n",
       "      <td>45886</td>\n",
       "      <td>26389</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>12.7208</td>\n",
       "      <td>50.3296</td>\n",
       "      <td>0.448479</td>\n",
       "      <td>0</td>\n",
       "      <td>10.3059</td>\n",
       "      <td>25.5016</td>\n",
       "      <td>83.3155</td>\n",
       "      <td>47.9147</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                   ident    type    name elevation_ft continent iso_country  \\\n",
       "column type       object  object  object      float64    object      object   \n",
       "null values (nb)       0       0       0         7006     27719         247   \n",
       "null values (%)        0       0       0      12.7208   50.3296    0.448479   \n",
       "\n",
       "                 iso_region municipality gps_code iata_code local_code  \\\n",
       "column type          object       object   object    object     object   \n",
       "null values (nb)          0         5676    14045     45886      26389   \n",
       "null values (%)           0      10.3059  25.5016   83.3155    47.9147   \n",
       "\n",
       "                 coordinates  \n",
       "column type           object  \n",
       "null values (nb)           0  \n",
       "null values (%)            0  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"airport-codes_csv.csv\"\n",
    "df = pd.read_csv(path+file, sep=\",\", nrows=9000000)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "#display(df.head(2))\n",
    "display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subse"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Global Airports Data\n",
    "* airports-extended.csv\n",
    "* There are 10668 rows and 13 columns in  *airports-extended.csv*. \n",
    "* df_global_airports"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 10668 rows and 13 columns in  *airports-extended.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>iata</th>\n",
       "      <th>icao</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>altitude</th>\n",
       "      <th>timezone</th>\n",
       "      <th>dst</th>\n",
       "      <th>tz_timezone</th>\n",
       "      <th>type</th>\n",
       "      <th>data_source</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Goroka Airport</td>\n",
       "      <td>Goroka</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>GKA</td>\n",
       "      <td>AYGA</td>\n",
       "      <td>-6.08169</td>\n",
       "      <td>145.391998</td>\n",
       "      <td>5282</td>\n",
       "      <td>10</td>\n",
       "      <td>U</td>\n",
       "      <td>Pacific/Port_Moresby</td>\n",
       "      <td>airport</td>\n",
       "      <td>OurAirports</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Madang Airport</td>\n",
       "      <td>Madang</td>\n",
       "      <td>Papua New Guinea</td>\n",
       "      <td>MAG</td>\n",
       "      <td>AYMD</td>\n",
       "      <td>-5.20708</td>\n",
       "      <td>145.789001</td>\n",
       "      <td>20</td>\n",
       "      <td>10</td>\n",
       "      <td>U</td>\n",
       "      <td>Pacific/Port_Moresby</td>\n",
       "      <td>airport</td>\n",
       "      <td>OurAirports</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "              name    city           country iata  icao  latitude   longitude  \\\n",
       "id                                                                              \n",
       "1   Goroka Airport  Goroka  Papua New Guinea  GKA  AYGA  -6.08169  145.391998   \n",
       "2   Madang Airport  Madang  Papua New Guinea  MAG  AYMD  -5.20708  145.789001   \n",
       "\n",
       "    altitude timezone dst           tz_timezone     type  data_source  \n",
       "id                                                                     \n",
       "1       5282       10   U  Pacific/Port_Moresby  airport  OurAirports  \n",
       "2         20       10   U  Pacific/Port_Moresby  airport  OurAirports  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>name</th>\n",
       "      <th>city</th>\n",
       "      <th>country</th>\n",
       "      <th>iata</th>\n",
       "      <th>icao</th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "      <th>altitude</th>\n",
       "      <th>timezone</th>\n",
       "      <th>dst</th>\n",
       "      <th>tz_timezone</th>\n",
       "      <th>type</th>\n",
       "      <th>data_source</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>47</td>\n",
       "      <td>0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0.44057</td>\n",
       "      <td>0</td>\n",
       "      <td>0.0187477</td>\n",
       "      <td>0.00937383</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    name     city country       iata        icao latitude  \\\n",
       "column type       object   object  object     object      object  float64   \n",
       "null values (nb)       0       47       0          2           1        0   \n",
       "null values (%)        0  0.44057       0  0.0187477  0.00937383        0   \n",
       "\n",
       "                 longitude altitude timezone     dst tz_timezone    type  \\\n",
       "column type        float64    int64   object  object      object  object   \n",
       "null values (nb)         0        0        0       0           0       0   \n",
       "null values (%)          0        0        0       0           0       0   \n",
       "\n",
       "                 data_source  \n",
       "column type           object  \n",
       "null values (nb)           0  \n",
       "null values (%)            0  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"airports-extended.csv\"\n",
    "df = pd.read_csv(path+file, \n",
    "                 header = None, \n",
    "                 index_col=0,\n",
    "                 names=['id', 'name', 'city', 'country', 'iata', 'icao', 'latitude', 'longitude', 'altitude', \n",
    "                            'timezone', 'dst', 'tz_timezone', 'type', 'data_source']).rename(columns=lambda col: col)\n",
    "\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset\n",
    "#df.dropna(subset=[\"insnum\"]).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Iso Country Data\n",
    "* wikipedia-iso-country-codes.csv\n",
    "* There are 246 rows and 4 columns in  *wikipedia-iso-country-codes.csv*.\n",
    "* df_iso_country"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 246 rows and 5 columns in  *wikipedia-iso-country-codes.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>English short name lower case</th>\n",
       "      <th>Alpha-2 code</th>\n",
       "      <th>Alpha-3 code</th>\n",
       "      <th>Numeric code</th>\n",
       "      <th>ISO 3166-2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Zimbabwe</td>\n",
       "      <td>ZW</td>\n",
       "      <td>ZWE</td>\n",
       "      <td>716</td>\n",
       "      <td>ISO 3166-2:ZW</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Zambia</td>\n",
       "      <td>ZM</td>\n",
       "      <td>ZMB</td>\n",
       "      <td>894</td>\n",
       "      <td>ISO 3166-2:ZM</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  English short name lower case Alpha-2 code Alpha-3 code  Numeric code  \\\n",
       "0                      Zimbabwe           ZW          ZWE           716   \n",
       "1                        Zambia           ZM          ZMB           894   \n",
       "\n",
       "      ISO 3166-2  \n",
       "0  ISO 3166-2:ZW  \n",
       "1  ISO 3166-2:ZM  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>English short name lower case</th>\n",
       "      <th>Alpha-2 code</th>\n",
       "      <th>Alpha-3 code</th>\n",
       "      <th>Numeric code</th>\n",
       "      <th>ISO 3166-2</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>1</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0.406504</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 English short name lower case Alpha-2 code Alpha-3 code  \\\n",
       "column type                             object       object       object   \n",
       "null values (nb)                             0            1            0   \n",
       "null values (%)                              0     0.406504            0   \n",
       "\n",
       "                 Numeric code ISO 3166-2  \n",
       "column type             int64     object  \n",
       "null values (nb)            0          0  \n",
       "null values (%)             0          0  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"wikipedia-iso-country-codes.csv\"\n",
    "df = pd.read_csv(path+file, sep=\",\", nrows=9000000)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset\n",
    "#df.dropna(subset=[\"insnum\"]).head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## US cities Demographics\n",
    "* us-cities-demographics.csv\n",
    "* There are 2891 rows and 12 columns in  *us-cities-demographics.csv*. \n",
    "* df_demograph"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 2891 rows and 12 columns in  *us-cities-demographics.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Median Age</th>\n",
       "      <th>Male Population</th>\n",
       "      <th>Female Population</th>\n",
       "      <th>Total Population</th>\n",
       "      <th>Number of Veterans</th>\n",
       "      <th>Foreign-born</th>\n",
       "      <th>Average Household Size</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Race</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Silver Spring</td>\n",
       "      <td>Maryland</td>\n",
       "      <td>33.8</td>\n",
       "      <td>40601.0</td>\n",
       "      <td>41862.0</td>\n",
       "      <td>82463</td>\n",
       "      <td>1562.0</td>\n",
       "      <td>30908.0</td>\n",
       "      <td>2.60</td>\n",
       "      <td>MD</td>\n",
       "      <td>Hispanic or Latino</td>\n",
       "      <td>25924</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Quincy</td>\n",
       "      <td>Massachusetts</td>\n",
       "      <td>41.0</td>\n",
       "      <td>44129.0</td>\n",
       "      <td>49500.0</td>\n",
       "      <td>93629</td>\n",
       "      <td>4147.0</td>\n",
       "      <td>32935.0</td>\n",
       "      <td>2.39</td>\n",
       "      <td>MA</td>\n",
       "      <td>White</td>\n",
       "      <td>58723</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            City          State  Median Age  Male Population  \\\n",
       "0  Silver Spring       Maryland        33.8          40601.0   \n",
       "1         Quincy  Massachusetts        41.0          44129.0   \n",
       "\n",
       "   Female Population  Total Population  Number of Veterans  Foreign-born  \\\n",
       "0            41862.0             82463              1562.0       30908.0   \n",
       "1            49500.0             93629              4147.0       32935.0   \n",
       "\n",
       "   Average Household Size State Code                Race  Count  \n",
       "0                    2.60         MD  Hispanic or Latino  25924  \n",
       "1                    2.39         MA               White  58723  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>City</th>\n",
       "      <th>State</th>\n",
       "      <th>Median Age</th>\n",
       "      <th>Male Population</th>\n",
       "      <th>Female Population</th>\n",
       "      <th>Total Population</th>\n",
       "      <th>Number of Veterans</th>\n",
       "      <th>Foreign-born</th>\n",
       "      <th>Average Household Size</th>\n",
       "      <th>State Code</th>\n",
       "      <th>Race</th>\n",
       "      <th>Count</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>int64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>int64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>0</td>\n",
       "      <td>13</td>\n",
       "      <td>13</td>\n",
       "      <td>16</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0.10377</td>\n",
       "      <td>0.10377</td>\n",
       "      <td>0</td>\n",
       "      <td>0.449671</td>\n",
       "      <td>0.449671</td>\n",
       "      <td>0.553442</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                    City   State Median Age Male Population Female Population  \\\n",
       "column type       object  object    float64         float64           float64   \n",
       "null values (nb)       0       0          0               3                 3   \n",
       "null values (%)        0       0          0         0.10377           0.10377   \n",
       "\n",
       "                 Total Population Number of Veterans Foreign-born  \\\n",
       "column type                 int64            float64      float64   \n",
       "null values (nb)                0                 13           13   \n",
       "null values (%)                 0           0.449671     0.449671   \n",
       "\n",
       "                 Average Household Size State Code    Race  Count  \n",
       "column type                     float64     object  object  int64  \n",
       "null values (nb)                     16          0       0      0  \n",
       "null values (%)                0.553442          0       0      0  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"us-cities-demographics.csv\"\n",
    "df = pd.read_csv(path+file, sep=\";\")\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## World Development Indicators Data\n",
    "* WDIData.csv\n",
    "* There are 422136 rows and 64 columns in  *WDIData.csv*.\n",
    "* df_indicator_dev"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 422136 rows and 64 columns in  *WDIData.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>1960</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>1966</th>\n",
       "      <th>1967</th>\n",
       "      <th>1968</th>\n",
       "      <th>1969</th>\n",
       "      <th>1970</th>\n",
       "      <th>1971</th>\n",
       "      <th>1972</th>\n",
       "      <th>1973</th>\n",
       "      <th>1974</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>1981</th>\n",
       "      <th>1982</th>\n",
       "      <th>1983</th>\n",
       "      <th>1984</th>\n",
       "      <th>1985</th>\n",
       "      <th>1986</th>\n",
       "      <th>1987</th>\n",
       "      <th>1988</th>\n",
       "      <th>1989</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>Unnamed: 63</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>2005 PPP conversion factor, GDP (LCU per inter...</td>\n",
       "      <td>PA.NUS.PPP.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\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>1</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>2005 PPP conversion factor, private consumptio...</td>\n",
       "      <td>PA.NUS.PRVT.PP.05</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country Name Country Code  \\\n",
       "0   Arab World          ARB   \n",
       "1   Arab World          ARB   \n",
       "\n",
       "                                      Indicator Name     Indicator Code  1960  \\\n",
       "0  2005 PPP conversion factor, GDP (LCU per inter...      PA.NUS.PPP.05   NaN   \n",
       "1  2005 PPP conversion factor, private consumptio...  PA.NUS.PRVT.PP.05   NaN   \n",
       "\n",
       "   1961  1962  1963  1964  1965  1966  1967  1968  1969  1970  1971  1972  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1973  1974  1975  1976  1977  1978  1979  1980  1981  1982  1983  1984  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1985  1986  1987  1988  1989  1990  1991  1992  1993  1994  1995  1996  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  2007  2008  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   2009  2010  2011  2012  2013  2014  2015  2016  2017  2018  Unnamed: 63  \n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN          NaN  \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN          NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>1960</th>\n",
       "      <th>1961</th>\n",
       "      <th>1962</th>\n",
       "      <th>1963</th>\n",
       "      <th>1964</th>\n",
       "      <th>1965</th>\n",
       "      <th>1966</th>\n",
       "      <th>1967</th>\n",
       "      <th>1968</th>\n",
       "      <th>1969</th>\n",
       "      <th>1970</th>\n",
       "      <th>1971</th>\n",
       "      <th>1972</th>\n",
       "      <th>1973</th>\n",
       "      <th>1974</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>1981</th>\n",
       "      <th>1982</th>\n",
       "      <th>1983</th>\n",
       "      <th>1984</th>\n",
       "      <th>1985</th>\n",
       "      <th>1986</th>\n",
       "      <th>1987</th>\n",
       "      <th>1988</th>\n",
       "      <th>1989</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2018</th>\n",
       "      <th>Unnamed: 63</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>383874</td>\n",
       "      <td>380159</td>\n",
       "      <td>377980</td>\n",
       "      <td>378100</td>\n",
       "      <td>377572</td>\n",
       "      <td>374886</td>\n",
       "      <td>375289</td>\n",
       "      <td>373557</td>\n",
       "      <td>373978</td>\n",
       "      <td>372175</td>\n",
       "      <td>329188</td>\n",
       "      <td>322941</td>\n",
       "      <td>320189</td>\n",
       "      <td>320579</td>\n",
       "      <td>319205</td>\n",
       "      <td>315106</td>\n",
       "      <td>313014</td>\n",
       "      <td>309504</td>\n",
       "      <td>309893</td>\n",
       "      <td>309060</td>\n",
       "      <td>304245</td>\n",
       "      <td>302355</td>\n",
       "      <td>301070</td>\n",
       "      <td>301293</td>\n",
       "      <td>300466</td>\n",
       "      <td>299300</td>\n",
       "      <td>298440</td>\n",
       "      <td>297044</td>\n",
       "      <td>297320</td>\n",
       "      <td>295547</td>\n",
       "      <td>271268</td>\n",
       "      <td>266167</td>\n",
       "      <td>262167</td>\n",
       "      <td>260677</td>\n",
       "      <td>258472</td>\n",
       "      <td>250812</td>\n",
       "      <td>250584</td>\n",
       "      <td>249441</td>\n",
       "      <td>247381</td>\n",
       "      <td>242129</td>\n",
       "      <td>217939</td>\n",
       "      <td>227649</td>\n",
       "      <td>222698</td>\n",
       "      <td>221900</td>\n",
       "      <td>216441</td>\n",
       "      <td>202656</td>\n",
       "      <td>204650</td>\n",
       "      <td>200703</td>\n",
       "      <td>201672</td>\n",
       "      <td>200654</td>\n",
       "      <td>185174</td>\n",
       "      <td>194482</td>\n",
       "      <td>192860</td>\n",
       "      <td>197314</td>\n",
       "      <td>192098</td>\n",
       "      <td>198058</td>\n",
       "      <td>206444</td>\n",
       "      <td>232401</td>\n",
       "      <td>338818</td>\n",
       "      <td>422136</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>90.9361</td>\n",
       "      <td>90.056</td>\n",
       "      <td>89.5399</td>\n",
       "      <td>89.5683</td>\n",
       "      <td>89.4432</td>\n",
       "      <td>88.8069</td>\n",
       "      <td>88.9024</td>\n",
       "      <td>88.4921</td>\n",
       "      <td>88.5918</td>\n",
       "      <td>88.1647</td>\n",
       "      <td>77.9815</td>\n",
       "      <td>76.5016</td>\n",
       "      <td>75.8497</td>\n",
       "      <td>75.9421</td>\n",
       "      <td>75.6166</td>\n",
       "      <td>74.6456</td>\n",
       "      <td>74.15</td>\n",
       "      <td>73.3186</td>\n",
       "      <td>73.4107</td>\n",
       "      <td>73.2134</td>\n",
       "      <td>72.0727</td>\n",
       "      <td>71.625</td>\n",
       "      <td>71.3206</td>\n",
       "      <td>71.3734</td>\n",
       "      <td>71.1775</td>\n",
       "      <td>70.9013</td>\n",
       "      <td>70.6976</td>\n",
       "      <td>70.3669</td>\n",
       "      <td>70.4323</td>\n",
       "      <td>70.0123</td>\n",
       "      <td>64.2608</td>\n",
       "      <td>63.0524</td>\n",
       "      <td>62.1049</td>\n",
       "      <td>61.7519</td>\n",
       "      <td>61.2296</td>\n",
       "      <td>59.415</td>\n",
       "      <td>59.361</td>\n",
       "      <td>59.0902</td>\n",
       "      <td>58.6022</td>\n",
       "      <td>57.3581</td>\n",
       "      <td>51.6277</td>\n",
       "      <td>53.9279</td>\n",
       "      <td>52.755</td>\n",
       "      <td>52.566</td>\n",
       "      <td>51.2728</td>\n",
       "      <td>48.0073</td>\n",
       "      <td>48.4796</td>\n",
       "      <td>47.5446</td>\n",
       "      <td>47.7742</td>\n",
       "      <td>47.533</td>\n",
       "      <td>43.866</td>\n",
       "      <td>46.0709</td>\n",
       "      <td>45.6867</td>\n",
       "      <td>46.7418</td>\n",
       "      <td>45.5062</td>\n",
       "      <td>46.9181</td>\n",
       "      <td>48.9046</td>\n",
       "      <td>55.0536</td>\n",
       "      <td>80.2628</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Country Name Country Code Indicator Name Indicator Code  \\\n",
       "column type            object       object         object         object   \n",
       "null values (nb)            0            0              0              0   \n",
       "null values (%)             0            0              0              0   \n",
       "\n",
       "                     1960     1961     1962     1963     1964     1965  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   383874   380159   377980   378100   377572   374886   \n",
       "null values (%)   90.9361   90.056  89.5399  89.5683  89.4432  88.8069   \n",
       "\n",
       "                     1966     1967     1968     1969     1970     1971  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   375289   373557   373978   372175   329188   322941   \n",
       "null values (%)   88.9024  88.4921  88.5918  88.1647  77.9815  76.5016   \n",
       "\n",
       "                     1972     1973     1974     1975     1976     1977  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   320189   320579   319205   315106   313014   309504   \n",
       "null values (%)   75.8497  75.9421  75.6166  74.6456    74.15  73.3186   \n",
       "\n",
       "                     1978     1979     1980     1981     1982     1983  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   309893   309060   304245   302355   301070   301293   \n",
       "null values (%)   73.4107  73.2134  72.0727   71.625  71.3206  71.3734   \n",
       "\n",
       "                     1984     1985     1986     1987     1988     1989  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   300466   299300   298440   297044   297320   295547   \n",
       "null values (%)   71.1775  70.9013  70.6976  70.3669  70.4323  70.0123   \n",
       "\n",
       "                     1990     1991     1992     1993     1994     1995  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   271268   266167   262167   260677   258472   250812   \n",
       "null values (%)   64.2608  63.0524  62.1049  61.7519  61.2296   59.415   \n",
       "\n",
       "                     1996     1997     1998     1999     2000     2001  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   250584   249441   247381   242129   217939   227649   \n",
       "null values (%)    59.361  59.0902  58.6022  57.3581  51.6277  53.9279   \n",
       "\n",
       "                     2002     2003     2004     2005     2006     2007  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   222698   221900   216441   202656   204650   200703   \n",
       "null values (%)    52.755   52.566  51.2728  48.0073  48.4796  47.5446   \n",
       "\n",
       "                     2008     2009     2010     2011     2012     2013  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   201672   200654   185174   194482   192860   197314   \n",
       "null values (%)   47.7742   47.533   43.866  46.0709  45.6867  46.7418   \n",
       "\n",
       "                     2014     2015     2016     2017     2018 Unnamed: 63  \n",
       "column type       float64  float64  float64  float64  float64     float64  \n",
       "null values (nb)   192098   198058   206444   232401   338818      422136  \n",
       "null values (%)   45.5062  46.9181  48.9046  55.0536  80.2628         100  "
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"WDIData.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Education statistics Data\n",
    "* education-statistics/EdStatsData.csv\n",
    "* There are 886930 rows and 70 columns in  *education-statistics/EdStatsData.csv*. \n",
    "* df_Educ_data\n",
    "* Edit: i find some data in 'World Development Indicators Data' so I didn't use this dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 886930 rows and 70 columns in  *education-statistics/EdStatsData.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>1970</th>\n",
       "      <th>1971</th>\n",
       "      <th>1972</th>\n",
       "      <th>1973</th>\n",
       "      <th>1974</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>1981</th>\n",
       "      <th>1982</th>\n",
       "      <th>1983</th>\n",
       "      <th>1984</th>\n",
       "      <th>1985</th>\n",
       "      <th>1986</th>\n",
       "      <th>1987</th>\n",
       "      <th>1988</th>\n",
       "      <th>1989</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "      <th>2045</th>\n",
       "      <th>2050</th>\n",
       "      <th>2055</th>\n",
       "      <th>2060</th>\n",
       "      <th>2065</th>\n",
       "      <th>2070</th>\n",
       "      <th>2075</th>\n",
       "      <th>2080</th>\n",
       "      <th>2085</th>\n",
       "      <th>2090</th>\n",
       "      <th>2095</th>\n",
       "      <th>2100</th>\n",
       "      <th>Unnamed: 69</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Adjusted net enrolment rate, lower secondary, ...</td>\n",
       "      <td>UIS.NERA.2</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\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>1</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Adjusted net enrolment rate, lower secondary, ...</td>\n",
       "      <td>UIS.NERA.2.F</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Country Name Country Code  \\\n",
       "0   Arab World          ARB   \n",
       "1   Arab World          ARB   \n",
       "\n",
       "                                      Indicator Name Indicator Code  1970  \\\n",
       "0  Adjusted net enrolment rate, lower secondary, ...     UIS.NERA.2   NaN   \n",
       "1  Adjusted net enrolment rate, lower secondary, ...   UIS.NERA.2.F   NaN   \n",
       "\n",
       "   1971  1972  1973  1974  1975  1976  1977  1978  1979  1980  1981  1982  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1983  1984  1985  1986  1987  1988  1989  1990  1991  1992  1993  1994  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   1995  1996  1997  1998  1999  2000  2001  2002  2003  2004  2005  2006  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   2007  2008  2009  2010  2011  2012  2013  2014  2015  2016  2017  2020  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   2025  2030  2035  2040  2045  2050  2055  2060  2065  2070  2075  2080  \\\n",
       "0   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "1   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   NaN   \n",
       "\n",
       "   2085  2090  2095  2100  Unnamed: 69  \n",
       "0   NaN   NaN   NaN   NaN          NaN  \n",
       "1   NaN   NaN   NaN   NaN          NaN  "
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country Name</th>\n",
       "      <th>Country Code</th>\n",
       "      <th>Indicator Name</th>\n",
       "      <th>Indicator Code</th>\n",
       "      <th>1970</th>\n",
       "      <th>1971</th>\n",
       "      <th>1972</th>\n",
       "      <th>1973</th>\n",
       "      <th>1974</th>\n",
       "      <th>1975</th>\n",
       "      <th>1976</th>\n",
       "      <th>1977</th>\n",
       "      <th>1978</th>\n",
       "      <th>1979</th>\n",
       "      <th>1980</th>\n",
       "      <th>1981</th>\n",
       "      <th>1982</th>\n",
       "      <th>1983</th>\n",
       "      <th>1984</th>\n",
       "      <th>1985</th>\n",
       "      <th>1986</th>\n",
       "      <th>1987</th>\n",
       "      <th>1988</th>\n",
       "      <th>1989</th>\n",
       "      <th>1990</th>\n",
       "      <th>1991</th>\n",
       "      <th>1992</th>\n",
       "      <th>1993</th>\n",
       "      <th>1994</th>\n",
       "      <th>1995</th>\n",
       "      <th>1996</th>\n",
       "      <th>1997</th>\n",
       "      <th>1998</th>\n",
       "      <th>1999</th>\n",
       "      <th>2000</th>\n",
       "      <th>2001</th>\n",
       "      <th>2002</th>\n",
       "      <th>2003</th>\n",
       "      <th>2004</th>\n",
       "      <th>2005</th>\n",
       "      <th>2006</th>\n",
       "      <th>2007</th>\n",
       "      <th>2008</th>\n",
       "      <th>2009</th>\n",
       "      <th>2010</th>\n",
       "      <th>2011</th>\n",
       "      <th>2012</th>\n",
       "      <th>2013</th>\n",
       "      <th>2014</th>\n",
       "      <th>2015</th>\n",
       "      <th>2016</th>\n",
       "      <th>2017</th>\n",
       "      <th>2020</th>\n",
       "      <th>2025</th>\n",
       "      <th>2030</th>\n",
       "      <th>2035</th>\n",
       "      <th>2040</th>\n",
       "      <th>2045</th>\n",
       "      <th>2050</th>\n",
       "      <th>2055</th>\n",
       "      <th>2060</th>\n",
       "      <th>2065</th>\n",
       "      <th>2070</th>\n",
       "      <th>2075</th>\n",
       "      <th>2080</th>\n",
       "      <th>2085</th>\n",
       "      <th>2090</th>\n",
       "      <th>2095</th>\n",
       "      <th>2100</th>\n",
       "      <th>Unnamed: 69</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "      <td>float64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>814642</td>\n",
       "      <td>851393</td>\n",
       "      <td>851311</td>\n",
       "      <td>851385</td>\n",
       "      <td>851200</td>\n",
       "      <td>799624</td>\n",
       "      <td>849447</td>\n",
       "      <td>849356</td>\n",
       "      <td>849354</td>\n",
       "      <td>850121</td>\n",
       "      <td>797808</td>\n",
       "      <td>848153</td>\n",
       "      <td>849419</td>\n",
       "      <td>848470</td>\n",
       "      <td>848324</td>\n",
       "      <td>796634</td>\n",
       "      <td>847558</td>\n",
       "      <td>848289</td>\n",
       "      <td>848378</td>\n",
       "      <td>849390</td>\n",
       "      <td>762525</td>\n",
       "      <td>812493</td>\n",
       "      <td>811387</td>\n",
       "      <td>811137</td>\n",
       "      <td>809468</td>\n",
       "      <td>755569</td>\n",
       "      <td>810123</td>\n",
       "      <td>813477</td>\n",
       "      <td>802016</td>\n",
       "      <td>768091</td>\n",
       "      <td>710254</td>\n",
       "      <td>763421</td>\n",
       "      <td>762725</td>\n",
       "      <td>756567</td>\n",
       "      <td>758116</td>\n",
       "      <td>702822</td>\n",
       "      <td>746618</td>\n",
       "      <td>749658</td>\n",
       "      <td>752543</td>\n",
       "      <td>744822</td>\n",
       "      <td>644488</td>\n",
       "      <td>740918</td>\n",
       "      <td>739666</td>\n",
       "      <td>749421</td>\n",
       "      <td>773141</td>\n",
       "      <td>755872</td>\n",
       "      <td>870470</td>\n",
       "      <td>886787</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>835494</td>\n",
       "      <td>886930</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>91.8496</td>\n",
       "      <td>95.9933</td>\n",
       "      <td>95.984</td>\n",
       "      <td>95.9924</td>\n",
       "      <td>95.9715</td>\n",
       "      <td>90.1564</td>\n",
       "      <td>95.7738</td>\n",
       "      <td>95.7636</td>\n",
       "      <td>95.7634</td>\n",
       "      <td>95.8498</td>\n",
       "      <td>89.9516</td>\n",
       "      <td>95.628</td>\n",
       "      <td>95.7707</td>\n",
       "      <td>95.6637</td>\n",
       "      <td>95.6472</td>\n",
       "      <td>89.8193</td>\n",
       "      <td>95.5609</td>\n",
       "      <td>95.6433</td>\n",
       "      <td>95.6533</td>\n",
       "      <td>95.7674</td>\n",
       "      <td>85.9735</td>\n",
       "      <td>91.6073</td>\n",
       "      <td>91.4826</td>\n",
       "      <td>91.4545</td>\n",
       "      <td>91.2663</td>\n",
       "      <td>85.1892</td>\n",
       "      <td>91.3401</td>\n",
       "      <td>91.7183</td>\n",
       "      <td>90.4261</td>\n",
       "      <td>86.6011</td>\n",
       "      <td>80.0801</td>\n",
       "      <td>86.0745</td>\n",
       "      <td>85.9961</td>\n",
       "      <td>85.3018</td>\n",
       "      <td>85.4764</td>\n",
       "      <td>79.2421</td>\n",
       "      <td>84.18</td>\n",
       "      <td>84.5228</td>\n",
       "      <td>84.8481</td>\n",
       "      <td>83.9775</td>\n",
       "      <td>72.665</td>\n",
       "      <td>83.5374</td>\n",
       "      <td>83.3962</td>\n",
       "      <td>84.4961</td>\n",
       "      <td>87.1705</td>\n",
       "      <td>85.2234</td>\n",
       "      <td>98.1442</td>\n",
       "      <td>99.9839</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>94.2007</td>\n",
       "      <td>100</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Country Name Country Code Indicator Name Indicator Code  \\\n",
       "column type            object       object         object         object   \n",
       "null values (nb)            0            0              0              0   \n",
       "null values (%)             0            0              0              0   \n",
       "\n",
       "                     1970     1971     1972     1973     1974     1975  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   814642   851393   851311   851385   851200   799624   \n",
       "null values (%)   91.8496  95.9933   95.984  95.9924  95.9715  90.1564   \n",
       "\n",
       "                     1976     1977     1978     1979     1980     1981  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   849447   849356   849354   850121   797808   848153   \n",
       "null values (%)   95.7738  95.7636  95.7634  95.8498  89.9516   95.628   \n",
       "\n",
       "                     1982     1983     1984     1985     1986     1987  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   849419   848470   848324   796634   847558   848289   \n",
       "null values (%)   95.7707  95.6637  95.6472  89.8193  95.5609  95.6433   \n",
       "\n",
       "                     1988     1989     1990     1991     1992     1993  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   848378   849390   762525   812493   811387   811137   \n",
       "null values (%)   95.6533  95.7674  85.9735  91.6073  91.4826  91.4545   \n",
       "\n",
       "                     1994     1995     1996     1997     1998     1999  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   809468   755569   810123   813477   802016   768091   \n",
       "null values (%)   91.2663  85.1892  91.3401  91.7183  90.4261  86.6011   \n",
       "\n",
       "                     2000     2001     2002     2003     2004     2005  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   710254   763421   762725   756567   758116   702822   \n",
       "null values (%)   80.0801  86.0745  85.9961  85.3018  85.4764  79.2421   \n",
       "\n",
       "                     2006     2007     2008     2009     2010     2011  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   746618   749658   752543   744822   644488   740918   \n",
       "null values (%)     84.18  84.5228  84.8481  83.9775   72.665  83.5374   \n",
       "\n",
       "                     2012     2013     2014     2015     2016     2017  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   739666   749421   773141   755872   870470   886787   \n",
       "null values (%)   83.3962  84.4961  87.1705  85.2234  98.1442  99.9839   \n",
       "\n",
       "                     2020     2025     2030     2035     2040     2045  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   835494   835494   835494   835494   835494   835494   \n",
       "null values (%)   94.2007  94.2007  94.2007  94.2007  94.2007  94.2007   \n",
       "\n",
       "                     2050     2055     2060     2065     2070     2075  \\\n",
       "column type       float64  float64  float64  float64  float64  float64   \n",
       "null values (nb)   835494   835494   835494   835494   835494   835494   \n",
       "null values (%)   94.2007  94.2007  94.2007  94.2007  94.2007  94.2007   \n",
       "\n",
       "                     2080     2085     2090     2095     2100 Unnamed: 69  \n",
       "column type       float64  float64  float64  float64  float64     float64  \n",
       "null values (nb)   835494   835494   835494   835494   835494      886930  \n",
       "null values (%)   94.2007  94.2007  94.2007  94.2007  94.2007         100  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"education-statistics/EdStatsData.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 55 rows and 2 columns in  *i94addr.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>State_id</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Al</td>\n",
       "      <td>Alabama</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Ak</td>\n",
       "      <td>Alaska</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  State_id    State\n",
       "0       Al  Alabama\n",
       "1       Ak   Alaska"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>State_id</th>\n",
       "      <th>State</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 State_id   State\n",
       "column type        object  object\n",
       "null values (nb)        0       0\n",
       "null values (%)         0       0"
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"i94addr.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 4 rows and 2 columns in  *i94mode.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Mode_id</th>\n",
       "      <th>Mode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Air</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Sea</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Mode_id Mode\n",
       "0        1  Air\n",
       "1        2  Sea"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Mode_id</th>\n",
       "      <th>Mode</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Mode_id    Mode\n",
       "column type        int64  object\n",
       "null values (nb)       0       0\n",
       "null values (%)        0       0"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"i94mode.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 583 rows and 3 columns in  *i94port.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Port_id</th>\n",
       "      <th>Port_city</th>\n",
       "      <th>State_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Alc</td>\n",
       "      <td>Alcan</td>\n",
       "      <td>Ak</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Anc</td>\n",
       "      <td>Anchorage</td>\n",
       "      <td>Ak</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  Port_id  Port_city State_id\n",
       "0     Alc      Alcan       Ak\n",
       "1     Anc  Anchorage       Ak"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Port_id</th>\n",
       "      <th>Port_city</th>\n",
       "      <th>State_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Port_id Port_city State_id\n",
       "column type       object    object   object\n",
       "null values (nb)       0         0        0\n",
       "null values (%)        0         0        0"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"i94port.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 17,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "There are 3 rows and 2 columns in  *i94visa.csv*. \n"
     ]
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Code_visa</th>\n",
       "      <th>Visa</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Business</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Pleasure</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Code_visa      Visa\n",
       "0          1  Business\n",
       "1          2  Pleasure"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "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>Code_visa</th>\n",
       "      <th>Visa</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>column type</th>\n",
       "      <td>int64</td>\n",
       "      <td>object</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (nb)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>null values (%)</th>\n",
       "      <td>0</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                 Code_visa    Visa\n",
       "column type          int64  object\n",
       "null values (nb)         0       0\n",
       "null values (%)          0       0"
      ]
     },
     "execution_count": 17,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "file = \"i94visa.csv\"\n",
    "df = pd.read_csv(path+file)\n",
    "nRow, nCol = df.shape\n",
    "print(\"There are {} rows and {} columns in  *{}*. \".format(nRow, nCol, file))\n",
    "\n",
    "#display(df[pd.notnull(df).any(axis=1)].head(2))\n",
    "display(df.head(2))\n",
    "#display(df.dropna(subset=[\"iata_code\"]).head(2))\n",
    "\n",
    "tab_info=pd.DataFrame(df.dtypes).T.rename(index={0:'column type'})\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()).T.rename(index={0:'null values (nb)'}))\n",
    "tab_info=tab_info.append(pd.DataFrame(df.isnull().sum()/df.shape[0]*100)\n",
    "                         .T.rename(index={0:'null values (%)'}))\n",
    "tab_info\n",
    "#change subset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.6"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}