kaggle2_injest.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Injesting Health Nutrition and Population Statistics Data into BigQuery**\n",
    "This procedure will load the health nutrition and population statistcs data in the single .csv file into BigQuery. The file is presently uploaded into GCP but needs to be split into tables through the following process. The data was obtained from Kaggle (https://www.kaggle.com/theworldbank/health-nutrition-and-population-statistics). a JSON file was created to define the schema and an additional empty column was required for it to properly load; this can be address as we edit the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "#name the dataset\n",
    "dataset_id = \"kaggle2_staging\""
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Dataset 'electric-spark-266716:kaggle2_staging' successfully created.\n"
     ]
    }
   ],
   "source": [
    "#create the actual dataset using bq cli\n",
    "!bq --location=US mk --dataset {dataset_id}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Waiting on bqjob_r92479c4c8b8ae5f_00000171956711c7_1 ... (5s) Current status: DONE   \n"
     ]
    }
   ],
   "source": [
    "#load the health nutrition data and create the table\n",
    "!bq --location=US load --skip_leading_rows=1\\\n",
    "--source_format=CSV {dataset_id}.Health_Nutrition_Population_Statistics\\\n",
    "\"gs://global_surface_temperatures/health_nutrition_population_dataset/HealthNutrition.csv\" \\\n",
    "schema.json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### **Exploratory Queries**"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The query below simply takes the first 12 records in the table."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>countryName</th>\n",
       "      <th>countryCode</th>\n",
       "      <th>metric</th>\n",
       "      <th>metricCode</th>\n",
       "      <th>yr_1960</th>\n",
       "      <th>yr_1961</th>\n",
       "      <th>yr_1962</th>\n",
       "      <th>yr_1963</th>\n",
       "      <th>yr_1964</th>\n",
       "      <th>yr_1965</th>\n",
       "      <th>...</th>\n",
       "      <th>yr_2007</th>\n",
       "      <th>yr_2008</th>\n",
       "      <th>yr_2009</th>\n",
       "      <th>yr_2010</th>\n",
       "      <th>yr_2011</th>\n",
       "      <th>yr_2012</th>\n",
       "      <th>yr_2013</th>\n",
       "      <th>yr_2014</th>\n",
       "      <th>yr_2015</th>\n",
       "      <th>empty</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Adolescent fertility rate (births per 1,000 wo...</td>\n",
       "      <td>SP.ADO.TFRT</td>\n",
       "      <td>1.335550e+02</td>\n",
       "      <td>1.341591e+02</td>\n",
       "      <td>1.348579e+02</td>\n",
       "      <td>1.345046e+02</td>\n",
       "      <td>1.341052e+02</td>\n",
       "      <td>1.335696e+02</td>\n",
       "      <td>...</td>\n",
       "      <td>4.999985e+01</td>\n",
       "      <td>4.988705e+01</td>\n",
       "      <td>4.978121e+01</td>\n",
       "      <td>4.967297e+01</td>\n",
       "      <td>4.953605e+01</td>\n",
       "      <td>4.938374e+01</td>\n",
       "      <td>4.879656e+01</td>\n",
       "      <td>4.819642e+01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Age dependency ratio (% of working-age populat...</td>\n",
       "      <td>SP.POP.DPND</td>\n",
       "      <td>8.779923e+01</td>\n",
       "      <td>8.922171e+01</td>\n",
       "      <td>9.050240e+01</td>\n",
       "      <td>9.160301e+01</td>\n",
       "      <td>9.241492e+01</td>\n",
       "      <td>9.286816e+01</td>\n",
       "      <td>...</td>\n",
       "      <td>6.463007e+01</td>\n",
       "      <td>6.360263e+01</td>\n",
       "      <td>6.273802e+01</td>\n",
       "      <td>6.205350e+01</td>\n",
       "      <td>6.179494e+01</td>\n",
       "      <td>6.168190e+01</td>\n",
       "      <td>6.167869e+01</td>\n",
       "      <td>6.171979e+01</td>\n",
       "      <td>6.175419e+01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Age dependency ratio, old</td>\n",
       "      <td>SP.POP.DPND.OL</td>\n",
       "      <td>6.635328e+00</td>\n",
       "      <td>6.746978e+00</td>\n",
       "      <td>6.840980e+00</td>\n",
       "      <td>6.915871e+00</td>\n",
       "      <td>6.968155e+00</td>\n",
       "      <td>6.996499e+00</td>\n",
       "      <td>...</td>\n",
       "      <td>6.765206e+00</td>\n",
       "      <td>6.712015e+00</td>\n",
       "      <td>6.672599e+00</td>\n",
       "      <td>6.653749e+00</td>\n",
       "      <td>6.678820e+00</td>\n",
       "      <td>6.715494e+00</td>\n",
       "      <td>6.767541e+00</td>\n",
       "      <td>6.840458e+00</td>\n",
       "      <td>6.937488e+00</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Age dependency ratio, young</td>\n",
       "      <td>SP.POP.DPND.YG</td>\n",
       "      <td>8.102425e+01</td>\n",
       "      <td>8.231614e+01</td>\n",
       "      <td>8.345981e+01</td>\n",
       "      <td>8.441397e+01</td>\n",
       "      <td>8.509666e+01</td>\n",
       "      <td>8.547072e+01</td>\n",
       "      <td>...</td>\n",
       "      <td>5.616857e+01</td>\n",
       "      <td>5.512340e+01</td>\n",
       "      <td>5.425597e+01</td>\n",
       "      <td>5.357864e+01</td>\n",
       "      <td>5.331823e+01</td>\n",
       "      <td>5.320907e+01</td>\n",
       "      <td>5.320536e+01</td>\n",
       "      <td>5.322831e+01</td>\n",
       "      <td>5.321811e+01</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Birth rate, crude (per 1,000 people)</td>\n",
       "      <td>SP.DYN.CBRT.IN</td>\n",
       "      <td>4.769972e+01</td>\n",
       "      <td>4.745709e+01</td>\n",
       "      <td>4.719957e+01</td>\n",
       "      <td>4.691921e+01</td>\n",
       "      <td>4.661179e+01</td>\n",
       "      <td>4.627075e+01</td>\n",
       "      <td>...</td>\n",
       "      <td>2.728815e+01</td>\n",
       "      <td>2.732055e+01</td>\n",
       "      <td>2.737012e+01</td>\n",
       "      <td>2.742164e+01</td>\n",
       "      <td>2.739020e+01</td>\n",
       "      <td>2.728440e+01</td>\n",
       "      <td>2.706754e+01</td>\n",
       "      <td>2.674617e+01</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Death rate, crude (per 1,000 people)</td>\n",
       "      <td>SP.DYN.CDRT.IN</td>\n",
       "      <td>1.975249e+01</td>\n",
       "      <td>1.928680e+01</td>\n",
       "      <td>1.882386e+01</td>\n",
       "      <td>1.835815e+01</td>\n",
       "      <td>1.788855e+01</td>\n",
       "      <td>1.741839e+01</td>\n",
       "      <td>...</td>\n",
       "      <td>6.061947e+00</td>\n",
       "      <td>6.014266e+00</td>\n",
       "      <td>5.959082e+00</td>\n",
       "      <td>5.911760e+00</td>\n",
       "      <td>5.872837e+00</td>\n",
       "      <td>5.833642e+00</td>\n",
       "      <td>5.790663e+00</td>\n",
       "      <td>5.744911e+00</td>\n",
       "      <td>NaN</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 00-04</td>\n",
       "      <td>SP.POP.0004.FE</td>\n",
       "      <td>8.042316e+06</td>\n",
       "      <td>8.268326e+06</td>\n",
       "      <td>8.506450e+06</td>\n",
       "      <td>8.758767e+06</td>\n",
       "      <td>9.016638e+06</td>\n",
       "      <td>9.281102e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.979545e+07</td>\n",
       "      <td>2.023598e+07</td>\n",
       "      <td>2.071189e+07</td>\n",
       "      <td>2.122380e+07</td>\n",
       "      <td>2.184746e+07</td>\n",
       "      <td>2.246794e+07</td>\n",
       "      <td>2.306040e+07</td>\n",
       "      <td>2.357029e+07</td>\n",
       "      <td>2.396381e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 05-09</td>\n",
       "      <td>SP.POP.0509.FE</td>\n",
       "      <td>6.413146e+06</td>\n",
       "      <td>6.681949e+06</td>\n",
       "      <td>6.923963e+06</td>\n",
       "      <td>7.129112e+06</td>\n",
       "      <td>7.306209e+06</td>\n",
       "      <td>7.471743e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.810795e+07</td>\n",
       "      <td>1.831139e+07</td>\n",
       "      <td>1.854932e+07</td>\n",
       "      <td>1.880947e+07</td>\n",
       "      <td>1.916016e+07</td>\n",
       "      <td>1.955924e+07</td>\n",
       "      <td>2.000788e+07</td>\n",
       "      <td>2.050108e+07</td>\n",
       "      <td>2.102129e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 10-14</td>\n",
       "      <td>SP.POP.1014.FE</td>\n",
       "      <td>5.076764e+06</td>\n",
       "      <td>5.283657e+06</td>\n",
       "      <td>5.519511e+06</td>\n",
       "      <td>5.783558e+06</td>\n",
       "      <td>6.058561e+06</td>\n",
       "      <td>6.317083e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.753865e+07</td>\n",
       "      <td>1.758338e+07</td>\n",
       "      <td>1.763500e+07</td>\n",
       "      <td>1.769913e+07</td>\n",
       "      <td>1.780028e+07</td>\n",
       "      <td>1.793730e+07</td>\n",
       "      <td>1.811818e+07</td>\n",
       "      <td>1.835851e+07</td>\n",
       "      <td>1.867327e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 15-19</td>\n",
       "      <td>SP.POP.1519.FE</td>\n",
       "      <td>4.104905e+06</td>\n",
       "      <td>4.219178e+06</td>\n",
       "      <td>4.356993e+06</td>\n",
       "      <td>4.517097e+06</td>\n",
       "      <td>4.700129e+06</td>\n",
       "      <td>4.909004e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.714961e+07</td>\n",
       "      <td>1.726051e+07</td>\n",
       "      <td>1.732804e+07</td>\n",
       "      <td>1.736186e+07</td>\n",
       "      <td>1.734755e+07</td>\n",
       "      <td>1.732713e+07</td>\n",
       "      <td>1.731978e+07</td>\n",
       "      <td>1.734831e+07</td>\n",
       "      <td>1.743144e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 20-24</td>\n",
       "      <td>SP.POP.2024.FE</td>\n",
       "      <td>3.686505e+06</td>\n",
       "      <td>3.726187e+06</td>\n",
       "      <td>3.764941e+06</td>\n",
       "      <td>3.810229e+06</td>\n",
       "      <td>3.874405e+06</td>\n",
       "      <td>3.965057e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.595838e+07</td>\n",
       "      <td>1.632082e+07</td>\n",
       "      <td>1.662522e+07</td>\n",
       "      <td>1.685436e+07</td>\n",
       "      <td>1.696156e+07</td>\n",
       "      <td>1.700177e+07</td>\n",
       "      <td>1.699730e+07</td>\n",
       "      <td>1.697793e+07</td>\n",
       "      <td>1.696610e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Arab World</td>\n",
       "      <td>ARB</td>\n",
       "      <td>Female population 25-29</td>\n",
       "      <td>SP.POP.2529.FE</td>\n",
       "      <td>3.376121e+06</td>\n",
       "      <td>3.420946e+06</td>\n",
       "      <td>3.464269e+06</td>\n",
       "      <td>3.506844e+06</td>\n",
       "      <td>3.549548e+06</td>\n",
       "      <td>3.592515e+06</td>\n",
       "      <td>...</td>\n",
       "      <td>1.400234e+07</td>\n",
       "      <td>1.446202e+07</td>\n",
       "      <td>1.490453e+07</td>\n",
       "      <td>1.531813e+07</td>\n",
       "      <td>1.564454e+07</td>\n",
       "      <td>1.592533e+07</td>\n",
       "      <td>1.615821e+07</td>\n",
       "      <td>1.634134e+07</td>\n",
       "      <td>1.647542e+07</td>\n",
       "      <td>None</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>12 rows × 61 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "   countryName countryCode                                             metric  \\\n",
       "0   Arab World         ARB  Adolescent fertility rate (births per 1,000 wo...   \n",
       "1   Arab World         ARB  Age dependency ratio (% of working-age populat...   \n",
       "2   Arab World         ARB                          Age dependency ratio, old   \n",
       "3   Arab World         ARB                        Age dependency ratio, young   \n",
       "4   Arab World         ARB               Birth rate, crude (per 1,000 people)   \n",
       "5   Arab World         ARB               Death rate, crude (per 1,000 people)   \n",
       "6   Arab World         ARB                            Female population 00-04   \n",
       "7   Arab World         ARB                            Female population 05-09   \n",
       "8   Arab World         ARB                            Female population 10-14   \n",
       "9   Arab World         ARB                            Female population 15-19   \n",
       "10  Arab World         ARB                            Female population 20-24   \n",
       "11  Arab World         ARB                            Female population 25-29   \n",
       "\n",
       "        metricCode       yr_1960       yr_1961       yr_1962       yr_1963  \\\n",
       "0      SP.ADO.TFRT  1.335550e+02  1.341591e+02  1.348579e+02  1.345046e+02   \n",
       "1      SP.POP.DPND  8.779923e+01  8.922171e+01  9.050240e+01  9.160301e+01   \n",
       "2   SP.POP.DPND.OL  6.635328e+00  6.746978e+00  6.840980e+00  6.915871e+00   \n",
       "3   SP.POP.DPND.YG  8.102425e+01  8.231614e+01  8.345981e+01  8.441397e+01   \n",
       "4   SP.DYN.CBRT.IN  4.769972e+01  4.745709e+01  4.719957e+01  4.691921e+01   \n",
       "5   SP.DYN.CDRT.IN  1.975249e+01  1.928680e+01  1.882386e+01  1.835815e+01   \n",
       "6   SP.POP.0004.FE  8.042316e+06  8.268326e+06  8.506450e+06  8.758767e+06   \n",
       "7   SP.POP.0509.FE  6.413146e+06  6.681949e+06  6.923963e+06  7.129112e+06   \n",
       "8   SP.POP.1014.FE  5.076764e+06  5.283657e+06  5.519511e+06  5.783558e+06   \n",
       "9   SP.POP.1519.FE  4.104905e+06  4.219178e+06  4.356993e+06  4.517097e+06   \n",
       "10  SP.POP.2024.FE  3.686505e+06  3.726187e+06  3.764941e+06  3.810229e+06   \n",
       "11  SP.POP.2529.FE  3.376121e+06  3.420946e+06  3.464269e+06  3.506844e+06   \n",
       "\n",
       "         yr_1964       yr_1965  ...       yr_2007       yr_2008       yr_2009  \\\n",
       "0   1.341052e+02  1.335696e+02  ...  4.999985e+01  4.988705e+01  4.978121e+01   \n",
       "1   9.241492e+01  9.286816e+01  ...  6.463007e+01  6.360263e+01  6.273802e+01   \n",
       "2   6.968155e+00  6.996499e+00  ...  6.765206e+00  6.712015e+00  6.672599e+00   \n",
       "3   8.509666e+01  8.547072e+01  ...  5.616857e+01  5.512340e+01  5.425597e+01   \n",
       "4   4.661179e+01  4.627075e+01  ...  2.728815e+01  2.732055e+01  2.737012e+01   \n",
       "5   1.788855e+01  1.741839e+01  ...  6.061947e+00  6.014266e+00  5.959082e+00   \n",
       "6   9.016638e+06  9.281102e+06  ...  1.979545e+07  2.023598e+07  2.071189e+07   \n",
       "7   7.306209e+06  7.471743e+06  ...  1.810795e+07  1.831139e+07  1.854932e+07   \n",
       "8   6.058561e+06  6.317083e+06  ...  1.753865e+07  1.758338e+07  1.763500e+07   \n",
       "9   4.700129e+06  4.909004e+06  ...  1.714961e+07  1.726051e+07  1.732804e+07   \n",
       "10  3.874405e+06  3.965057e+06  ...  1.595838e+07  1.632082e+07  1.662522e+07   \n",
       "11  3.549548e+06  3.592515e+06  ...  1.400234e+07  1.446202e+07  1.490453e+07   \n",
       "\n",
       "         yr_2010       yr_2011       yr_2012       yr_2013       yr_2014  \\\n",
       "0   4.967297e+01  4.953605e+01  4.938374e+01  4.879656e+01  4.819642e+01   \n",
       "1   6.205350e+01  6.179494e+01  6.168190e+01  6.167869e+01  6.171979e+01   \n",
       "2   6.653749e+00  6.678820e+00  6.715494e+00  6.767541e+00  6.840458e+00   \n",
       "3   5.357864e+01  5.331823e+01  5.320907e+01  5.320536e+01  5.322831e+01   \n",
       "4   2.742164e+01  2.739020e+01  2.728440e+01  2.706754e+01  2.674617e+01   \n",
       "5   5.911760e+00  5.872837e+00  5.833642e+00  5.790663e+00  5.744911e+00   \n",
       "6   2.122380e+07  2.184746e+07  2.246794e+07  2.306040e+07  2.357029e+07   \n",
       "7   1.880947e+07  1.916016e+07  1.955924e+07  2.000788e+07  2.050108e+07   \n",
       "8   1.769913e+07  1.780028e+07  1.793730e+07  1.811818e+07  1.835851e+07   \n",
       "9   1.736186e+07  1.734755e+07  1.732713e+07  1.731978e+07  1.734831e+07   \n",
       "10  1.685436e+07  1.696156e+07  1.700177e+07  1.699730e+07  1.697793e+07   \n",
       "11  1.531813e+07  1.564454e+07  1.592533e+07  1.615821e+07  1.634134e+07   \n",
       "\n",
       "         yr_2015  empty  \n",
       "0            NaN   None  \n",
       "1   6.175419e+01   None  \n",
       "2   6.937488e+00   None  \n",
       "3   5.321811e+01   None  \n",
       "4            NaN   None  \n",
       "5            NaN   None  \n",
       "6   2.396381e+07   None  \n",
       "7   2.102129e+07   None  \n",
       "8   1.867327e+07   None  \n",
       "9   1.743144e+07   None  \n",
       "10  1.696610e+07   None  \n",
       "11  1.647542e+07   None  \n",
       "\n",
       "[12 rows x 61 columns]"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT *\n",
    "FROM kaggle2_staging.Health_Nutrition_Population_Statistics\n",
    "LIMIT 12"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "#### The following query orders the countries by their fertility rate total in the year 1972 in descending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 26,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>countryName</th>\n",
       "      <th>metric</th>\n",
       "      <th>yr_1972</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Rwanda</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>8.278</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Libya</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>8.135</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Kenya</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>8.012</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Cote d'Ivoire</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>7.937</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Yemen, Rep.</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>7.894</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>253</th>\n",
       "      <td>Palau</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>254</th>\n",
       "      <td>San Marino</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>255</th>\n",
       "      <td>Sint Maarten (Dutch part)</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>256</th>\n",
       "      <td>Turks and Caicos Islands</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>257</th>\n",
       "      <td>Tuvalu</td>\n",
       "      <td>Fertility rate, total (births per woman)</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>258 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "                   countryName                                    metric  \\\n",
       "0                       Rwanda  Fertility rate, total (births per woman)   \n",
       "1                        Libya  Fertility rate, total (births per woman)   \n",
       "2                        Kenya  Fertility rate, total (births per woman)   \n",
       "3                Cote d'Ivoire  Fertility rate, total (births per woman)   \n",
       "4                  Yemen, Rep.  Fertility rate, total (births per woman)   \n",
       "..                         ...                                       ...   \n",
       "253                      Palau  Fertility rate, total (births per woman)   \n",
       "254                 San Marino  Fertility rate, total (births per woman)   \n",
       "255  Sint Maarten (Dutch part)  Fertility rate, total (births per woman)   \n",
       "256   Turks and Caicos Islands  Fertility rate, total (births per woman)   \n",
       "257                     Tuvalu  Fertility rate, total (births per woman)   \n",
       "\n",
       "     yr_1972  \n",
       "0      8.278  \n",
       "1      8.135  \n",
       "2      8.012  \n",
       "3      7.937  \n",
       "4      7.894  \n",
       "..       ...  \n",
       "253      NaN  \n",
       "254      NaN  \n",
       "255      NaN  \n",
       "256      NaN  \n",
       "257      NaN  \n",
       "\n",
       "[258 rows x 3 columns]"
      ]
     },
     "execution_count": 26,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "%%bigquery\n",
    "SELECT countryName, metric, yr_1972\n",
    "FROM kaggle2_staging.Health_Nutrition_Population_Statistics\n",
    "WHERE metricCode = \"SP.DYN.TFRT.IN\"\n",
    "ORDER BY yr_1972 DESC\n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.5.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}