pandas.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Problem Statement"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>Indicator</th>\n",
       "      <th>PUBLISH STATES</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                          Indicator PUBLISH STATES  Year WHO region  \\\n",
       "0  Life expectancy at birth (years)      Published  1990     Europe   \n",
       "1  Life expectancy at birth (years)      Published  2000     Europe   \n",
       "\n",
       "  World Bank income group  Country         Sex  Display Value  Numeric  Low  \\\n",
       "0             High-income  Andorra  Both sexes             77     77.0  NaN   \n",
       "1             High-income  Andorra  Both sexes             80     80.0  NaN   \n",
       "\n",
       "   High  Comments  \n",
       "0   NaN       NaN  \n",
       "1   NaN       NaN  "
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.read_csv('https://raw.githubusercontent.com/jackiekazil/data-wrangling/master/data/chp3/data-text.csv')\n",
    "df.head(2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>STATION</th>\n",
       "      <th>STATION_NAME</th>\n",
       "      <th>DATE</th>\n",
       "      <th>PRCP</th>\n",
       "      <th>SNWD</th>\n",
       "      <th>SNOW</th>\n",
       "      <th>TMAX</th>\n",
       "      <th>TMIN</th>\n",
       "      <th>WDFG</th>\n",
       "      <th>PGTM</th>\n",
       "      <th>...</th>\n",
       "      <th>WT09</th>\n",
       "      <th>WT07</th>\n",
       "      <th>WT01</th>\n",
       "      <th>WT06</th>\n",
       "      <th>WT05</th>\n",
       "      <th>WT04</th>\n",
       "      <th>WT16</th>\n",
       "      <th>WT08</th>\n",
       "      <th>WT18</th>\n",
       "      <th>WT03</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>GHCND:GME00111445</td>\n",
       "      <td>BERLIN TEMPELHOF GM</td>\n",
       "      <td>19310101</td>\n",
       "      <td>46</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-11</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>GHCND:GME00111445</td>\n",
       "      <td>BERLIN TEMPELHOF GM</td>\n",
       "      <td>19310102</td>\n",
       "      <td>107</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>50</td>\n",
       "      <td>11</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>...</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "      <td>-9999</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2 rows × 21 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "             STATION         STATION_NAME      DATE  PRCP  SNWD  SNOW  TMAX  \\\n",
       "0  GHCND:GME00111445  BERLIN TEMPELHOF GM  19310101    46 -9999 -9999 -9999   \n",
       "1  GHCND:GME00111445  BERLIN TEMPELHOF GM  19310102   107 -9999 -9999    50   \n",
       "\n",
       "   TMIN  WDFG  PGTM  ...  WT09  WT07  WT01  WT06  WT05  WT04  WT16  WT08  \\\n",
       "0   -11 -9999 -9999  ... -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999   \n",
       "1    11 -9999 -9999  ... -9999 -9999 -9999 -9999 -9999 -9999 -9999 -9999   \n",
       "\n",
       "   WT18  WT03  \n",
       "0 -9999 -9999  \n",
       "1 -9999 -9999  \n",
       "\n",
       "[2 rows x 21 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df1 = pd.read_csv('https://raw.githubusercontent.com/kjam/data-wrangling-pycon/master/data/berlin_weather_oldest.csv')\n",
    "df1.head(2)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "1. Get the Metadata from the above files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 4656 entries, 0 to 4655\n",
      "Data columns (total 12 columns):\n",
      "Indicator                  4656 non-null object\n",
      "PUBLISH STATES             4656 non-null object\n",
      "Year                       4656 non-null int64\n",
      "WHO region                 4656 non-null object\n",
      "World Bank income group    4656 non-null object\n",
      "Country                    4656 non-null object\n",
      "Sex                        4656 non-null object\n",
      "Display Value              4656 non-null int64\n",
      "Numeric                    4656 non-null float64\n",
      "Low                        0 non-null float64\n",
      "High                       0 non-null float64\n",
      "Comments                   0 non-null float64\n",
      "dtypes: float64(4), int64(2), object(6)\n",
      "memory usage: 436.6+ KB\n",
      "None\n",
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 117208 entries, 0 to 117207\n",
      "Data columns (total 21 columns):\n",
      "STATION         117208 non-null object\n",
      "STATION_NAME    117208 non-null object\n",
      "DATE            117208 non-null int64\n",
      "PRCP            117208 non-null int64\n",
      "SNWD            117208 non-null int64\n",
      "SNOW            117208 non-null int64\n",
      "TMAX            117208 non-null int64\n",
      "TMIN            117208 non-null int64\n",
      "WDFG            117208 non-null int64\n",
      "PGTM            117208 non-null int64\n",
      "WSFG            117208 non-null int64\n",
      "WT09            117208 non-null int64\n",
      "WT07            117208 non-null int64\n",
      "WT01            117208 non-null int64\n",
      "WT06            117208 non-null int64\n",
      "WT05            117208 non-null int64\n",
      "WT04            117208 non-null int64\n",
      "WT16            117208 non-null int64\n",
      "WT08            117208 non-null int64\n",
      "WT18            117208 non-null int64\n",
      "WT03            117208 non-null int64\n",
      "dtypes: int64(19), object(2)\n",
      "memory usage: 18.8+ MB\n",
      "None\n"
     ]
    }
   ],
   "source": [
    "print(df.info())\n",
    "print(df1.info())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "2. Get the row names from the above files."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "[   0    1    2 ... 4653 4654 4655]\n",
      "[     0      1      2 ... 117205 117206 117207]\n"
     ]
    }
   ],
   "source": [
    "print(np.array((df.index)))\n",
    "print(np.array(df1.index))"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "3. Change the column name from any of the above file."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "                        indicator_id PUBLISH STATES  Year  \\\n",
      "0   Life expectancy at birth (years)      Published  1990   \n",
      "1   Life expectancy at birth (years)      Published  2000   \n",
      "2  Life expectancy at age 60 (years)      Published  2012   \n",
      "3  Life expectancy at age 60 (years)      Published  2000   \n",
      "4   Life expectancy at birth (years)      Published  2012   \n",
      "\n",
      "              WHO region World Bank income group               Country  \\\n",
      "0                 Europe             High-income               Andorra   \n",
      "1                 Europe             High-income               Andorra   \n",
      "2                 Europe             High-income               Andorra   \n",
      "3                 Europe             High-income               Andorra   \n",
      "4  Eastern Mediterranean             High-income  United Arab Emirates   \n",
      "\n",
      "          Sex  Display Value  Numeric  Low  High  Comments  \n",
      "0  Both sexes             77     77.0  NaN   NaN       NaN  \n",
      "1  Both sexes             80     80.0  NaN   NaN       NaN  \n",
      "2      Female             28     28.0  NaN   NaN       NaN  \n",
      "3  Both sexes             23     23.0  NaN   NaN       NaN  \n",
      "4      Female             78     78.0  NaN   NaN       NaN  \n"
     ]
    }
   ],
   "source": [
    "print(df.rename({'Indicator':'indicator_id'}, axis=1).head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "4. Change the column name from any of the above file and store the changes made\n",
    "permanently."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>indicator_id</th>\n",
       "      <th>PUBLISH STATES</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Female</td>\n",
       "      <td>28</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>23</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>High-income</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Female</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        indicator_id PUBLISH STATES  Year  \\\n",
       "0   Life expectancy at birth (years)      Published  1990   \n",
       "1   Life expectancy at birth (years)      Published  2000   \n",
       "2  Life expectancy at age 60 (years)      Published  2012   \n",
       "3  Life expectancy at age 60 (years)      Published  2000   \n",
       "4   Life expectancy at birth (years)      Published  2012   \n",
       "\n",
       "              WHO region World Bank income group               Country  \\\n",
       "0                 Europe             High-income               Andorra   \n",
       "1                 Europe             High-income               Andorra   \n",
       "2                 Europe             High-income               Andorra   \n",
       "3                 Europe             High-income               Andorra   \n",
       "4  Eastern Mediterranean             High-income  United Arab Emirates   \n",
       "\n",
       "          Sex  Display Value  Numeric  Low  High  Comments  \n",
       "0  Both sexes             77     77.0  NaN   NaN       NaN  \n",
       "1  Both sexes             80     80.0  NaN   NaN       NaN  \n",
       "2      Female             28     28.0  NaN   NaN       NaN  \n",
       "3  Both sexes             23     23.0  NaN   NaN       NaN  \n",
       "4      Female             78     78.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename({'Indicator':'indicator_id'}, inplace=True, axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "5. Change the names of multiple columns."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Female</td>\n",
       "      <td>28</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>23</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>High-income</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Female</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                        indicator_id Publication Status  Year  \\\n",
       "0   Life expectancy at birth (years)          Published  1990   \n",
       "1   Life expectancy at birth (years)          Published  2000   \n",
       "2  Life expectancy at age 60 (years)          Published  2012   \n",
       "3  Life expectancy at age 60 (years)          Published  2000   \n",
       "4   Life expectancy at birth (years)          Published  2012   \n",
       "\n",
       "              WHO Region World Bank income group               Country  \\\n",
       "0                 Europe             High-income               Andorra   \n",
       "1                 Europe             High-income               Andorra   \n",
       "2                 Europe             High-income               Andorra   \n",
       "3                 Europe             High-income               Andorra   \n",
       "4  Eastern Mediterranean             High-income  United Arab Emirates   \n",
       "\n",
       "          Sex  Display Value  Numeric  Low  High  Comments  \n",
       "0  Both sexes             77     77.0  NaN   NaN       NaN  \n",
       "1  Both sexes             80     80.0  NaN   NaN       NaN  \n",
       "2      Female             28     28.0  NaN   NaN       NaN  \n",
       "3  Both sexes             23     23.0  NaN   NaN       NaN  \n",
       "4      Female             78     78.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.rename({'PUBLISH STATES':'Publication Status', 'WHO region': 'WHO Region'}, inplace=True, axis=1)\n",
    "df.head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "6. Arrange values of a particular column in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "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>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1270</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Germany</td>\n",
       "      <td>Male</td>\n",
       "      <td>72</td>\n",
       "      <td>72.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3193</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Lower-middle-income</td>\n",
       "      <td>Republic of Moldova</td>\n",
       "      <td>Male</td>\n",
       "      <td>65</td>\n",
       "      <td>65.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3194</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Lower-middle-income</td>\n",
       "      <td>Republic of Moldova</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>68</td>\n",
       "      <td>68.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3197</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>Lower-middle-income</td>\n",
       "      <td>Republic of Moldova</td>\n",
       "      <td>Male</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           indicator_id Publication Status  Year WHO Region  \\\n",
       "0      Life expectancy at birth (years)          Published  1990     Europe   \n",
       "1270   Life expectancy at birth (years)          Published  1990     Europe   \n",
       "3193   Life expectancy at birth (years)          Published  1990     Europe   \n",
       "3194   Life expectancy at birth (years)          Published  1990     Europe   \n",
       "3197  Life expectancy at age 60 (years)          Published  1990     Europe   \n",
       "\n",
       "     World Bank income group              Country         Sex  Display Value  \\\n",
       "0                High-income              Andorra  Both sexes             77   \n",
       "1270             High-income              Germany        Male             72   \n",
       "3193     Lower-middle-income  Republic of Moldova        Male             65   \n",
       "3194     Lower-middle-income  Republic of Moldova  Both sexes             68   \n",
       "3197     Lower-middle-income  Republic of Moldova        Male             15   \n",
       "\n",
       "      Numeric  Low  High  Comments  \n",
       "0        77.0  NaN   NaN       NaN  \n",
       "1270     72.0  NaN   NaN       NaN  \n",
       "3193     65.0  NaN   NaN       NaN  \n",
       "3194     68.0  NaN   NaN       NaN  \n",
       "3197     15.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 9,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=['Year'], ascending=True).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "7. Arrange multiple column values in ascending order."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "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>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>554</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>Low-income</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>49</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>555</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>Low-income</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Female</td>\n",
       "      <td>15</td>\n",
       "      <td>15.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>965</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>Low-income</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Male</td>\n",
       "      <td>49</td>\n",
       "      <td>49.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1395</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>Low-income</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>14</td>\n",
       "      <td>14.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1792</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>Low-income</td>\n",
       "      <td>Afghanistan</td>\n",
       "      <td>Female</td>\n",
       "      <td>50</td>\n",
       "      <td>50.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                           indicator_id Publication Status  Year  \\\n",
       "554    Life expectancy at birth (years)          Published  1990   \n",
       "555   Life expectancy at age 60 (years)          Published  1990   \n",
       "965    Life expectancy at birth (years)          Published  1990   \n",
       "1395  Life expectancy at age 60 (years)          Published  1990   \n",
       "1792   Life expectancy at birth (years)          Published  1990   \n",
       "\n",
       "                 WHO Region World Bank income group      Country         Sex  \\\n",
       "554   Eastern Mediterranean              Low-income  Afghanistan  Both sexes   \n",
       "555   Eastern Mediterranean              Low-income  Afghanistan      Female   \n",
       "965   Eastern Mediterranean              Low-income  Afghanistan        Male   \n",
       "1395  Eastern Mediterranean              Low-income  Afghanistan  Both sexes   \n",
       "1792  Eastern Mediterranean              Low-income  Afghanistan      Female   \n",
       "\n",
       "      Display Value  Numeric  Low  High  Comments  \n",
       "554              49     49.0  NaN   NaN       NaN  \n",
       "555              15     15.0  NaN   NaN       NaN  \n",
       "965              49     49.0  NaN   NaN       NaN  \n",
       "1395             14     14.0  NaN   NaN       NaN  \n",
       "1792             50     50.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.sort_values(by=['Country','Year', 'WHO Region'], ascending=True).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>Country</th>\n",
       "      <th>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Female</td>\n",
       "      <td>28</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Andorra</td>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>23</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Female</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                Country                       indicator_id Publication Status  \\\n",
       "0               Andorra   Life expectancy at birth (years)          Published   \n",
       "1               Andorra   Life expectancy at birth (years)          Published   \n",
       "2               Andorra  Life expectancy at age 60 (years)          Published   \n",
       "3               Andorra  Life expectancy at age 60 (years)          Published   \n",
       "4  United Arab Emirates   Life expectancy at birth (years)          Published   \n",
       "\n",
       "   Year             WHO Region World Bank income group         Sex  \\\n",
       "0  1990                 Europe             High-income  Both sexes   \n",
       "1  2000                 Europe             High-income  Both sexes   \n",
       "2  2012                 Europe             High-income      Female   \n",
       "3  2000                 Europe             High-income  Both sexes   \n",
       "4  2012  Eastern Mediterranean             High-income      Female   \n",
       "\n",
       "   Display Value  Numeric  Low  High  Comments  \n",
       "0             77     77.0  NaN   NaN       NaN  \n",
       "1             80     80.0  NaN   NaN       NaN  \n",
       "2             28     28.0  NaN   NaN       NaN  \n",
       "3             23     23.0  NaN   NaN       NaN  \n",
       "4             78     78.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "column_titles = ['Country','indicator_id','Publication Status','Year', 'WHO Region', 'World Bank income group', 'Sex', 'Display Value', 'Numeric', 'Low', 'High', 'Comments']\n",
    "df.reindex(columns=column_titles).head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['indicator_id', 'Publication Status', 'Year', 'WHO Region',\n",
      "       'World Bank income group', 'Country', 'Sex', 'Display Value', 'Numeric',\n",
      "       'Low', 'High', 'Comments'],\n",
      "      dtype='object')\n"
     ]
    }
   ],
   "source": [
    "print(df.columns)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "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>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Austria</td>\n",
       "      <td>Female</td>\n",
       "      <td>83</td>\n",
       "      <td>83.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Western Pacific</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Brunei Darussalam</td>\n",
       "      <td>Female</td>\n",
       "      <td>21</td>\n",
       "      <td>21.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>37</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Cyprus</td>\n",
       "      <td>Female</td>\n",
       "      <td>26</td>\n",
       "      <td>26.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         indicator_id Publication Status  Year  \\\n",
       "11   Life expectancy at birth (years)          Published  2012   \n",
       "24  Life expectancy at age 60 (years)          Published  2012   \n",
       "37  Life expectancy at age 60 (years)          Published  2012   \n",
       "\n",
       "         WHO Region World Bank income group            Country     Sex  \\\n",
       "11           Europe             High-income            Austria  Female   \n",
       "24  Western Pacific             High-income  Brunei Darussalam  Female   \n",
       "37           Europe             High-income             Cyprus  Female   \n",
       "\n",
       "    Display Value  Numeric  Low  High  Comments  \n",
       "11             83     83.0  NaN   NaN       NaN  \n",
       "24             21     21.0  NaN   NaN       NaN  \n",
       "37             26     26.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.loc[[11, 24, 37]]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "11. Get the subset rows excluding 5, 12, 23, and 56"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "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>indicator_id</th>\n",
       "      <th>Publication Status</th>\n",
       "      <th>Year</th>\n",
       "      <th>WHO Region</th>\n",
       "      <th>World Bank income group</th>\n",
       "      <th>Country</th>\n",
       "      <th>Sex</th>\n",
       "      <th>Display Value</th>\n",
       "      <th>Numeric</th>\n",
       "      <th>Low</th>\n",
       "      <th>High</th>\n",
       "      <th>Comments</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>77</td>\n",
       "      <td>77.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Female</td>\n",
       "      <td>28</td>\n",
       "      <td>28.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Europe</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Andorra</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>23</td>\n",
       "      <td>23.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Eastern Mediterranean</td>\n",
       "      <td>High-income</td>\n",
       "      <td>United Arab Emirates</td>\n",
       "      <td>Female</td>\n",
       "      <td>78</td>\n",
       "      <td>78.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>1990</td>\n",
       "      <td>Americas</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Male</td>\n",
       "      <td>17</td>\n",
       "      <td>17.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>Life expectancy at age 60 (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Americas</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Antigua and Barbuda</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>22</td>\n",
       "      <td>22.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Western Pacific</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Male</td>\n",
       "      <td>81</td>\n",
       "      <td>81.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2000</td>\n",
       "      <td>Western Pacific</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>80</td>\n",
       "      <td>80.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>Life expectancy at birth (years)</td>\n",
       "      <td>Published</td>\n",
       "      <td>2012</td>\n",
       "      <td>Western Pacific</td>\n",
       "      <td>High-income</td>\n",
       "      <td>Australia</td>\n",
       "      <td>Both sexes</td>\n",
       "      <td>83</td>\n",
       "      <td>83.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "                         indicator_id Publication Status  Year  \\\n",
       "0    Life expectancy at birth (years)          Published  1990   \n",
       "1    Life expectancy at birth (years)          Published  2000   \n",
       "2   Life expectancy at age 60 (years)          Published  2012   \n",
       "3   Life expectancy at age 60 (years)          Published  2000   \n",
       "4    Life expectancy at birth (years)          Published  2012   \n",
       "6   Life expectancy at age 60 (years)          Published  1990   \n",
       "7   Life expectancy at age 60 (years)          Published  2012   \n",
       "8    Life expectancy at birth (years)          Published  2012   \n",
       "9    Life expectancy at birth (years)          Published  2000   \n",
       "10   Life expectancy at birth (years)          Published  2012   \n",
       "\n",
       "               WHO Region World Bank income group               Country  \\\n",
       "0                  Europe             High-income               Andorra   \n",
       "1                  Europe             High-income               Andorra   \n",
       "2                  Europe             High-income               Andorra   \n",
       "3                  Europe             High-income               Andorra   \n",
       "4   Eastern Mediterranean             High-income  United Arab Emirates   \n",
       "6                Americas             High-income   Antigua and Barbuda   \n",
       "7                Americas             High-income   Antigua and Barbuda   \n",
       "8         Western Pacific             High-income             Australia   \n",
       "9         Western Pacific             High-income             Australia   \n",
       "10        Western Pacific             High-income             Australia   \n",
       "\n",
       "           Sex  Display Value  Numeric  Low  High  Comments  \n",
       "0   Both sexes             77     77.0  NaN   NaN       NaN  \n",
       "1   Both sexes             80     80.0  NaN   NaN       NaN  \n",
       "2       Female             28     28.0  NaN   NaN       NaN  \n",
       "3   Both sexes             23     23.0  NaN   NaN       NaN  \n",
       "4       Female             78     78.0  NaN   NaN       NaN  \n",
       "6         Male             17     17.0  NaN   NaN       NaN  \n",
       "7   Both sexes             22     22.0  NaN   NaN       NaN  \n",
       "8         Male             81     81.0  NaN   NaN       NaN  \n",
       "9   Both sexes             80     80.0  NaN   NaN       NaN  \n",
       "10  Both sexes             83     83.0  NaN   NaN       NaN  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "index = df.index.isin([5, 12, 23, 56])\n",
    "df[~index].head(10)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Load datasets from CSV"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 18,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "   UserID      User  Gender  Registered   Cancelled\n",
      "0       1   Charles    male  2012-12-21         NaN\n",
      "1       2     Pedro    male  2010-08-01  2010-08-08\n",
      "2       3  Caroline  female  2012-10-23  2016-06-07\n",
      "3       4   Brielle  female  2013-07-17         NaN\n",
      "4       5  Benjamin    male  2010-11-25         NaN\n",
      "   SessionID SessionDate  UserID\n",
      "0          1  2010-01-05       2\n",
      "1          2  2010-08-01       2\n",
      "2          3  2010-11-25       2\n",
      "3          4  2011-09-21       5\n",
      "4          5  2011-10-19       4\n",
      "   TransactionID TransactionDate  UserID  ProductID  Quantity\n",
      "0              1      2010-08-21     7.0          2         1\n",
      "1              2      2011-05-26     3.0          4         1\n",
      "2              3      2011-06-16     3.0          3         1\n",
      "3              4      2012-08-26     1.0          2         3\n",
      "4              5      2013-06-06     2.0          4         1\n"
     ]
    }
   ],
   "source": [
    "users = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/users.csv')\n",
    "sessions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/sessions.csv')\n",
    "products = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/products.csv')\n",
    "transactions = pd.read_csv('https://raw.githubusercontent.com/ben519/DataWrangling/master/Data/transactions.csv')\n",
    "print(users.head())\n",
    "print(sessions.head())\n",
    "print(transactions.head())"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "12. Join users to transactions, keeping all rows from transactions and only matching rows from\n",
    "users (left join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "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>TransactionID</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>User</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Registered</th>\n",
       "      <th>Cancelled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</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>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Charles</td>\n",
       "      <td>male</td>\n",
       "      <td>2012-12-21</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionID TransactionDate  UserID  ProductID  Quantity      User  \\\n",
       "0              1      2010-08-21     7.0          2         1       NaN   \n",
       "1              2      2011-05-26     3.0          4         1  Caroline   \n",
       "2              3      2011-06-16     3.0          3         1  Caroline   \n",
       "3              4      2012-08-26     1.0          2         3   Charles   \n",
       "4              5      2013-06-06     2.0          4         1     Pedro   \n",
       "5              6      2013-12-23     2.0          5         6     Pedro   \n",
       "6              7      2013-12-30     3.0          4         1  Caroline   \n",
       "7              8      2014-04-24     NaN          2         3       NaN   \n",
       "8              9      2015-04-24     7.0          4         3       NaN   \n",
       "9             10      2016-05-08     3.0          4         4  Caroline   \n",
       "\n",
       "   Gender  Registered   Cancelled  \n",
       "0     NaN         NaN         NaN  \n",
       "1  female  2012-10-23  2016-06-07  \n",
       "2  female  2012-10-23  2016-06-07  \n",
       "3    male  2012-12-21         NaN  \n",
       "4    male  2010-08-01  2010-08-08  \n",
       "5    male  2010-08-01  2010-08-08  \n",
       "6  female  2012-10-23  2016-06-07  \n",
       "7     NaN         NaN         NaN  \n",
       "8     NaN         NaN         NaN  \n",
       "9  female  2012-10-23  2016-06-07  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(transactions, users, on='UserID', how='left')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "13. Which transactions have a UserID not in users?"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 21,
   "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>TransactionID</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>8</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionID TransactionDate  UserID  ProductID  Quantity\n",
       "0              1      2010-08-21     7.0          2         1\n",
       "7              8      2014-04-24     NaN          2         3\n",
       "8              9      2015-04-24     7.0          4         3"
      ]
     },
     "execution_count": 21,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "transactions[~transactions['UserID'].isin(users['UserID'])]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "14. Join users to transactions, keeping only rows from transactions and users that match via\n",
    "UserID (inner join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 22,
   "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>TransactionID</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>User</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Registered</th>\n",
       "      <th>Cancelled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>Charles</td>\n",
       "      <td>male</td>\n",
       "      <td>2012-12-21</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   TransactionID TransactionDate  UserID  ProductID  Quantity      User  \\\n",
       "0              2      2011-05-26     3.0          4         1  Caroline   \n",
       "1              3      2011-06-16     3.0          3         1  Caroline   \n",
       "2              7      2013-12-30     3.0          4         1  Caroline   \n",
       "3             10      2016-05-08     3.0          4         4  Caroline   \n",
       "4              4      2012-08-26     1.0          2         3   Charles   \n",
       "5              5      2013-06-06     2.0          4         1     Pedro   \n",
       "6              6      2013-12-23     2.0          5         6     Pedro   \n",
       "\n",
       "   Gender  Registered   Cancelled  \n",
       "0  female  2012-10-23  2016-06-07  \n",
       "1  female  2012-10-23  2016-06-07  \n",
       "2  female  2012-10-23  2016-06-07  \n",
       "3  female  2012-10-23  2016-06-07  \n",
       "4    male  2012-12-21         NaN  \n",
       "5    male  2010-08-01  2010-08-08  \n",
       "6    male  2010-08-01  2010-08-08  "
      ]
     },
     "execution_count": 22,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(transactions, users, on='UserID', how='inner')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "15. Join users to transactions, displaying all matching rows AND all non-matching rows (full\n",
    "outer join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 23,
   "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>TransactionID</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "      <th>User</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Registered</th>\n",
       "      <th>Cancelled</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1.0</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>1.0</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>9.0</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2.0</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3.0</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>7.0</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>10.0</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>4.0</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>Charles</td>\n",
       "      <td>male</td>\n",
       "      <td>2012-12-21</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>5.0</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>6.0</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5.0</td>\n",
       "      <td>6.0</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>8.0</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Brielle</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-07-17</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Benjamin</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-11-25</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID TransactionDate  UserID  ProductID  Quantity      User  \\\n",
       "0             1.0      2010-08-21     7.0        2.0       1.0       NaN   \n",
       "1             9.0      2015-04-24     7.0        4.0       3.0       NaN   \n",
       "2             2.0      2011-05-26     3.0        4.0       1.0  Caroline   \n",
       "3             3.0      2011-06-16     3.0        3.0       1.0  Caroline   \n",
       "4             7.0      2013-12-30     3.0        4.0       1.0  Caroline   \n",
       "5            10.0      2016-05-08     3.0        4.0       4.0  Caroline   \n",
       "6             4.0      2012-08-26     1.0        2.0       3.0   Charles   \n",
       "7             5.0      2013-06-06     2.0        4.0       1.0     Pedro   \n",
       "8             6.0      2013-12-23     2.0        5.0       6.0     Pedro   \n",
       "9             8.0      2014-04-24     NaN        2.0       3.0       NaN   \n",
       "10            NaN             NaN     4.0        NaN       NaN   Brielle   \n",
       "11            NaN             NaN     5.0        NaN       NaN  Benjamin   \n",
       "\n",
       "    Gender  Registered   Cancelled  \n",
       "0      NaN         NaN         NaN  \n",
       "1      NaN         NaN         NaN  \n",
       "2   female  2012-10-23  2016-06-07  \n",
       "3   female  2012-10-23  2016-06-07  \n",
       "4   female  2012-10-23  2016-06-07  \n",
       "5   female  2012-10-23  2016-06-07  \n",
       "6     male  2012-12-21         NaN  \n",
       "7     male  2010-08-01  2010-08-08  \n",
       "8     male  2010-08-01  2010-08-08  \n",
       "9      NaN         NaN         NaN  \n",
       "10  female  2013-07-17         NaN  \n",
       "11    male  2010-11-25         NaN  "
      ]
     },
     "execution_count": 23,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(transactions, users, on='UserID', how='outer')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "16. Determine which sessions occurred on the same day each user registered"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 24,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>User</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Registered</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>SessionID</th>\n",
       "      <th>SessionDate</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "      <td>2</td>\n",
       "      <td>2010-08-01</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>Brielle</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-07-17</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>2013-07-17</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   UserID     User  Gender  Registered   Cancelled  SessionID SessionDate\n",
       "0       2    Pedro    male  2010-08-01  2010-08-08          2  2010-08-01\n",
       "1       4  Brielle  female  2013-07-17         NaN          9  2013-07-17"
      ]
     },
     "execution_count": 24,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(left=users,right=sessions,how=\"inner\",left_on=['UserID','Registered'],right_on=['UserID','SessionDate'])"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "17. Build a dataset with every possible (UserID, ProductID) pair (cross join)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    UserID  ProductID\n",
       "0        1          1\n",
       "1        1          2\n",
       "2        1          3\n",
       "3        1          4\n",
       "4        1          5\n",
       "5        2          1\n",
       "6        2          2\n",
       "7        2          3\n",
       "8        2          4\n",
       "9        2          5\n",
       "10       3          1\n",
       "11       3          2\n",
       "12       3          3\n",
       "13       3          4\n",
       "14       3          5\n",
       "15       4          1\n",
       "16       4          2\n",
       "17       4          3\n",
       "18       4          4\n",
       "19       4          5\n",
       "20       5          1\n",
       "21       5          2\n",
       "22       5          3\n",
       "23       5          4\n",
       "24       5          5"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_userid = pd.DataFrame({\"UserID\":users[\"UserID\"]}) \n",
    "df_Tran = pd.DataFrame({\"ProductID\":products[\"ProductID\"]})  \n",
    "df_userid['Key'] = 1 \n",
    "df_Tran['Key'] = 1 \n",
    "dataset=pd.merge(df_userid,df_Tran,how='outer',on=\"Key\")[['UserID','ProductID']] \n",
    "dataset"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "18. Determine how much quantity of each product was purchased by each user"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 34,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>2</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>2</td>\n",
       "      <td>5</td>\n",
       "      <td>6.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>4.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>4</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>1</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>5</td>\n",
       "      <td>2</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>5</td>\n",
       "      <td>3</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>26</th>\n",
       "      <td>5</td>\n",
       "      <td>5</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    UserID  ProductID  Quantity\n",
       "0        1          1       0.0\n",
       "1        1          2       3.0\n",
       "2        1          3       0.0\n",
       "3        1          4       0.0\n",
       "4        1          5       0.0\n",
       "5        2          1       0.0\n",
       "6        2          2       0.0\n",
       "7        2          3       0.0\n",
       "8        2          4       1.0\n",
       "9        2          5       6.0\n",
       "10       3          1       0.0\n",
       "11       3          2       0.0\n",
       "12       3          3       1.0\n",
       "13       3          4       1.0\n",
       "14       3          4       1.0\n",
       "15       3          4       4.0\n",
       "16       3          5       0.0\n",
       "17       4          1       0.0\n",
       "18       4          2       0.0\n",
       "19       4          3       0.0\n",
       "20       4          4       0.0\n",
       "21       4          5       0.0\n",
       "22       5          1       0.0\n",
       "23       5          2       0.0\n",
       "24       5          3       0.0\n",
       "25       5          4       0.0\n",
       "26       5          5       0.0"
      ]
     },
     "execution_count": 34,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_user_prod_quant = pd.merge(dataset,transactions,how='left',on=['UserID','ProductID'])\n",
    "df_user_prod_quant.groupby(['UserID', 'ProductID'])['Quantity'].sum()\n",
    "df_user_prod_quant.fillna(0)[['UserID', 'ProductID', 'Quantity']]"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "19. For each user, get each possible pair of pair transactions (TransactionID1, TransacationID2)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 35,
   "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>TransactionID_x</th>\n",
       "      <th>TransactionDate_x</th>\n",
       "      <th>UserID</th>\n",
       "      <th>ProductID_x</th>\n",
       "      <th>Quantity_x</th>\n",
       "      <th>TransactionID_y</th>\n",
       "      <th>TransactionDate_y</th>\n",
       "      <th>ProductID_y</th>\n",
       "      <th>Quantity_y</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>7.0</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2010-08-21</td>\n",
       "      <td>2</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>7.0</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>2015-04-24</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>5</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>6</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>8</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>9</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>10</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>11</th>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3.0</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>12</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>14</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>15</th>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>16</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>17</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>3</td>\n",
       "      <td>2011-06-16</td>\n",
       "      <td>3</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>18</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>7</td>\n",
       "      <td>2013-12-30</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>19</th>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>3.0</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "      <td>10</td>\n",
       "      <td>2016-05-08</td>\n",
       "      <td>4</td>\n",
       "      <td>4</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>20</th>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>1.0</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>21</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>22</th>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>2.0</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>23</th>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>5</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>24</th>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>2.0</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "      <td>6</td>\n",
       "      <td>2013-12-23</td>\n",
       "      <td>5</td>\n",
       "      <td>6</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>25</th>\n",
       "      <td>8</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "      <td>8</td>\n",
       "      <td>2014-04-24</td>\n",
       "      <td>2</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "    TransactionID_x TransactionDate_x  UserID  ProductID_x  Quantity_x  \\\n",
       "0                 1        2010-08-21     7.0            2           1   \n",
       "1                 1        2010-08-21     7.0            2           1   \n",
       "2                 9        2015-04-24     7.0            4           3   \n",
       "3                 9        2015-04-24     7.0            4           3   \n",
       "4                 2        2011-05-26     3.0            4           1   \n",
       "5                 2        2011-05-26     3.0            4           1   \n",
       "6                 2        2011-05-26     3.0            4           1   \n",
       "7                 2        2011-05-26     3.0            4           1   \n",
       "8                 3        2011-06-16     3.0            3           1   \n",
       "9                 3        2011-06-16     3.0            3           1   \n",
       "10                3        2011-06-16     3.0            3           1   \n",
       "11                3        2011-06-16     3.0            3           1   \n",
       "12                7        2013-12-30     3.0            4           1   \n",
       "13                7        2013-12-30     3.0            4           1   \n",
       "14                7        2013-12-30     3.0            4           1   \n",
       "15                7        2013-12-30     3.0            4           1   \n",
       "16               10        2016-05-08     3.0            4           4   \n",
       "17               10        2016-05-08     3.0            4           4   \n",
       "18               10        2016-05-08     3.0            4           4   \n",
       "19               10        2016-05-08     3.0            4           4   \n",
       "20                4        2012-08-26     1.0            2           3   \n",
       "21                5        2013-06-06     2.0            4           1   \n",
       "22                5        2013-06-06     2.0            4           1   \n",
       "23                6        2013-12-23     2.0            5           6   \n",
       "24                6        2013-12-23     2.0            5           6   \n",
       "25                8        2014-04-24     NaN            2           3   \n",
       "\n",
       "    TransactionID_y TransactionDate_y  ProductID_y  Quantity_y  \n",
       "0                 1        2010-08-21            2           1  \n",
       "1                 9        2015-04-24            4           3  \n",
       "2                 1        2010-08-21            2           1  \n",
       "3                 9        2015-04-24            4           3  \n",
       "4                 2        2011-05-26            4           1  \n",
       "5                 3        2011-06-16            3           1  \n",
       "6                 7        2013-12-30            4           1  \n",
       "7                10        2016-05-08            4           4  \n",
       "8                 2        2011-05-26            4           1  \n",
       "9                 3        2011-06-16            3           1  \n",
       "10                7        2013-12-30            4           1  \n",
       "11               10        2016-05-08            4           4  \n",
       "12                2        2011-05-26            4           1  \n",
       "13                3        2011-06-16            3           1  \n",
       "14                7        2013-12-30            4           1  \n",
       "15               10        2016-05-08            4           4  \n",
       "16                2        2011-05-26            4           1  \n",
       "17                3        2011-06-16            3           1  \n",
       "18                7        2013-12-30            4           1  \n",
       "19               10        2016-05-08            4           4  \n",
       "20                4        2012-08-26            2           3  \n",
       "21                5        2013-06-06            4           1  \n",
       "22                6        2013-12-23            5           6  \n",
       "23                5        2013-06-06            4           1  \n",
       "24                6        2013-12-23            5           6  \n",
       "25                8        2014-04-24            2           3  "
      ]
     },
     "execution_count": 35,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.merge(transactions, transactions, how='outer', on='UserID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "20. Join each user to his/her first occuring transaction in the transactions table"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>UserID</th>\n",
       "      <th>User</th>\n",
       "      <th>Gender</th>\n",
       "      <th>Registered</th>\n",
       "      <th>Cancelled</th>\n",
       "      <th>TransactionID</th>\n",
       "      <th>TransactionDate</th>\n",
       "      <th>ProductID</th>\n",
       "      <th>Quantity</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>Charles</td>\n",
       "      <td>male</td>\n",
       "      <td>2012-12-21</td>\n",
       "      <td>NaN</td>\n",
       "      <td>4.0</td>\n",
       "      <td>2012-08-26</td>\n",
       "      <td>2.0</td>\n",
       "      <td>3.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2</td>\n",
       "      <td>Pedro</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-08-01</td>\n",
       "      <td>2010-08-08</td>\n",
       "      <td>5.0</td>\n",
       "      <td>2013-06-06</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>3</td>\n",
       "      <td>Caroline</td>\n",
       "      <td>female</td>\n",
       "      <td>2012-10-23</td>\n",
       "      <td>2016-06-07</td>\n",
       "      <td>2.0</td>\n",
       "      <td>2011-05-26</td>\n",
       "      <td>4.0</td>\n",
       "      <td>1.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>7</th>\n",
       "      <td>4</td>\n",
       "      <td>Brielle</td>\n",
       "      <td>female</td>\n",
       "      <td>2013-07-17</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>8</th>\n",
       "      <td>5</td>\n",
       "      <td>Benjamin</td>\n",
       "      <td>male</td>\n",
       "      <td>2010-11-25</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": [
       "   UserID      User  Gender  Registered   Cancelled  TransactionID  \\\n",
       "0       1   Charles    male  2012-12-21         NaN            4.0   \n",
       "1       2     Pedro    male  2010-08-01  2010-08-08            5.0   \n",
       "3       3  Caroline  female  2012-10-23  2016-06-07            2.0   \n",
       "7       4   Brielle  female  2013-07-17         NaN            NaN   \n",
       "8       5  Benjamin    male  2010-11-25         NaN            NaN   \n",
       "\n",
       "  TransactionDate  ProductID  Quantity  \n",
       "0      2012-08-26        2.0       3.0  \n",
       "1      2013-06-06        4.0       1.0  \n",
       "3      2011-05-26        4.0       1.0  \n",
       "7             NaN        NaN       NaN  \n",
       "8             NaN        NaN       NaN  "
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "d = pd.merge(users,transactions,how='left',on='UserID')\n",
    "d.drop_duplicates(subset='UserID')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "21. Test to see if we can drop columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 42,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['UserID', 'User', 'Gender', 'Registered', 'Cancelled', 'TransactionID', 'TransactionDate', 'ProductID', 'Quantity']\n",
      "['UserID', 'User', 'Gender', 'Registered']\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "['Cancelled', 'TransactionID', 'TransactionDate', 'ProductID', 'Quantity']"
      ]
     },
     "execution_count": 42,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "my_columns = list(d.columns) \n",
    "print(my_columns)\n",
    "\n",
    "print(list(d.dropna(thresh=int(d.shape[0] * .9), axis=1).columns))\n",
    "\n",
    "missing_info = list(d.columns[d.isnull().any()])\n",
    "missing_info"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 44,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "number missing for column Cancelled: 3\n",
      "number missing for column TransactionID: 2\n",
      "number missing for column TransactionDate: 2\n",
      "number missing for column ProductID: 2\n",
      "number missing for column Quantity: 2\n"
     ]
    }
   ],
   "source": [
    "for col in missing_info: \n",
    "    num_missing = d[d[col].isnull() == True].shape[0]\n",
    "    print('number missing for column {}: {}'.format(col, num_missing))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 48,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "percent missing for column Cancelled: 0.33\n",
      "percent missing for column TransactionID: 0.22\n",
      "percent missing for column TransactionDate: 0.22\n",
      "percent missing for column ProductID: 0.22\n",
      "percent missing for column Quantity: 0.22\n"
     ]
    }
   ],
   "source": [
    "for col in missing_info: \n",
    "    percent_missing = d[d[col].isnull() == True].shape[0] / d.shape[0] \n",
    "    print('percent missing for column {}: {}'.format(col, round(percent_missing, 2)))"
   ]
  }
 ],
 "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.7.7"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}