8.Project-DataExploration.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Data Exploration With Pandas\n",
    "\n",
    "In this notebook we will analyse hypothetical migration data."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "%matplotlib inline\n",
    "sns.set()\n",
    "import json"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 1.  Loading and getting familiar with data"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "metadata": {},
   "outputs": [],
   "source": [
    "with open('data/sample.json','r')as f:\n",
    "    mig =  json.load(f)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{'ID': '82Q6H2OCLB',\n",
       " 'age': 42,\n",
       " 'gender': 'male',\n",
       " 'profession': {'major': 'Arts, Design, Entertainment, Sports, and Media Occupations',\n",
       "  'branch': 'Interior Designers'},\n",
       " 'interest': 'stories',\n",
       " 'home_address': {'province': 'PN1',\n",
       "  'district': 'Panchthar',\n",
       "  'MCP': 'Miklajung RM',\n",
       "  'ward': 'Ward 6'},\n",
       " 'foreign_address': {'country': 'Aruba',\n",
       "  'ID': 'AW',\n",
       "  'lat': '12.52111',\n",
       "  'lon': '-69.968338',\n",
       "  'city': 'ORANJESTAD'},\n",
       " 'date_out': '2015.2.12',\n",
       " 'date_return': '2019.3.9',\n",
       " 'fund2invest': {'invetsment sector': '8%',\n",
       "  'tourism and hospitality': '2%',\n",
       "  'animal husbandary': '6%',\n",
       "  'infrastructure': '8%',\n",
       "  'cottage industries': '6%',\n",
       "  'personal service business': '10%',\n",
       "  'retail business': '4%',\n",
       "  'consultancy service': '2%',\n",
       "  'alternative energy': '6%',\n",
       "  'agriculture': '8%',\n",
       "  'total_fund': 7109},\n",
       " 'remittance': [{'month': 6, 'amount': 459},\n",
       "  {'month': 3, 'amount': 466},\n",
       "  {'month': 3, 'amount': 794},\n",
       "  {'month': 8, 'amount': 400}],\n",
       " 'travell': [{'date': '2017.11.7',\n",
       "   'place': {'country': 'Lithuania', 'city': 'Panevezhis'}},\n",
       "  {'date': '2015.1.26', 'place': {'country': 'Papua-New-Guinea', 'city': 'N'}},\n",
       "  {'date': '2019.7.5',\n",
       "   'place': {'country': 'Macedonia-[FYROM]', 'city': 'A'}}],\n",
       " 'social_work': [{'date': '2002.7.6', 'work': 'pollution control'},\n",
       "  {'date': '2012.1.15', 'work': 'workshop for young'}],\n",
       " 'personal_view': ['While all parties involved in the construction process have contributed to the current fiasco, one lesson is especially poignant as stakeholders take the next necessary steps to identify a new contractor: more thoughtful deliberation must factor into procurement decisions. A number of factors—including corruption, mismanagement, deviation from contract promises, sluggish cash flow, and a circular bout of finger pointing—has played into the current Melamchi saga. But tussles between contractors and government officials, often manifesting in contract terminations, have featured heavily in these delays.']}"
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "mig[0]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "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>age</th>\n",
       "      <th>date_out</th>\n",
       "      <th>date_return</th>\n",
       "      <th>foreign_address</th>\n",
       "      <th>fund2invest</th>\n",
       "      <th>gender</th>\n",
       "      <th>home_address</th>\n",
       "      <th>interest</th>\n",
       "      <th>personal_view</th>\n",
       "      <th>profession</th>\n",
       "      <th>remittance</th>\n",
       "      <th>social_work</th>\n",
       "      <th>travell</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82Q6H2OCLB</th>\n",
       "      <td>42</td>\n",
       "      <td>2015.2.12</td>\n",
       "      <td>2019.3.9</td>\n",
       "      <td>{'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...</td>\n",
       "      <td>{'invetsment sector': '8%', 'tourism and hospi...</td>\n",
       "      <td>male</td>\n",
       "      <td>{'province': 'PN1', 'district': 'Panchthar', '...</td>\n",
       "      <td>stories</td>\n",
       "      <td>[While all parties involved in the constructio...</td>\n",
       "      <td>{'major': 'Arts, Design, Entertainment, Sports...</td>\n",
       "      <td>[{'month': 6, 'amount': 459}, {'month': 3, 'am...</td>\n",
       "      <td>[{'date': '2002.7.6', 'work': 'pollution contr...</td>\n",
       "      <td>[{'date': '2017.11.7', 'place': {'country': 'L...</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age   date_out date_return  \\\n",
       "ID                                       \n",
       "82Q6H2OCLB   42  2015.2.12    2019.3.9   \n",
       "\n",
       "                                              foreign_address  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...   \n",
       "\n",
       "                                                  fund2invest gender  \\\n",
       "ID                                                                     \n",
       "82Q6H2OCLB  {'invetsment sector': '8%', 'tourism and hospi...   male   \n",
       "\n",
       "                                                 home_address interest  \\\n",
       "ID                                                                       \n",
       "82Q6H2OCLB  {'province': 'PN1', 'district': 'Panchthar', '...  stories   \n",
       "\n",
       "                                                personal_view  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [While all parties involved in the constructio...   \n",
       "\n",
       "                                                   profession  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'major': 'Arts, Design, Entertainment, Sports...   \n",
       "\n",
       "                                                   remittance  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'month': 6, 'amount': 459}, {'month': 3, 'am...   \n",
       "\n",
       "                                                  social_work  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'date': '2002.7.6', 'work': 'pollution contr...   \n",
       "\n",
       "                                                      travell  \n",
       "ID                                                             \n",
       "82Q6H2OCLB  [{'date': '2017.11.7', 'place': {'country': 'L...  "
      ]
     },
     "execution_count": 29,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df = pd.DataFrame(mig)\n",
    "df = df.set_index('ID')\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 30,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(1000, 13)"
      ]
     },
     "execution_count": 30,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.shape"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 31,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['age', 'date_out', 'date_return', 'foreign_address', 'fund2invest',\n",
       "       'gender', 'home_address', 'interest', 'personal_view', 'profession',\n",
       "       'remittance', 'social_work', 'travell'],\n",
       "      dtype='object')"
      ]
     },
     "execution_count": 31,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 32,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Index(['82Q6H2OCLB', '53OHV7F4E3', '66C9X10E0M', '7A6VKHNE9Q', 'K8P0CX2V99',\n",
       "       '9ZMLWRZO17', 'JQPP89162S', 'QP373XQ6S3', 'V4TI4Q471M', 'IEWHWW4MV6',\n",
       "       ...\n",
       "       'K7A2AOWW2F', '49N6Y1010C', '1NT8P67V5I', 'EX6TWC9U1T', '7Y3CZ49704',\n",
       "       'JA7FB4PRVZ', 'REOU6TJU0B', '59G172SSYN', '8595VF97O2', 'M3L69T086M'],\n",
       "      dtype='object', name='ID', length=1000)"
      ]
     },
     "execution_count": 32,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.index"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### 2. Simple Queries"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Create a new column for ```province``` and ```district```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 36,
   "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>age</th>\n",
       "      <th>date_out</th>\n",
       "      <th>date_return</th>\n",
       "      <th>foreign_address</th>\n",
       "      <th>fund2invest</th>\n",
       "      <th>gender</th>\n",
       "      <th>home_address</th>\n",
       "      <th>interest</th>\n",
       "      <th>personal_view</th>\n",
       "      <th>profession</th>\n",
       "      <th>remittance</th>\n",
       "      <th>social_work</th>\n",
       "      <th>travell</th>\n",
       "      <th>province</th>\n",
       "      <th>district</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82Q6H2OCLB</th>\n",
       "      <td>42</td>\n",
       "      <td>2015.2.12</td>\n",
       "      <td>2019.3.9</td>\n",
       "      <td>{'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...</td>\n",
       "      <td>{'invetsment sector': '8%', 'tourism and hospi...</td>\n",
       "      <td>male</td>\n",
       "      <td>{'province': 'PN1', 'district': 'Panchthar', '...</td>\n",
       "      <td>stories</td>\n",
       "      <td>[While all parties involved in the constructio...</td>\n",
       "      <td>{'major': 'Arts, Design, Entertainment, Sports...</td>\n",
       "      <td>[{'month': 6, 'amount': 459}, {'month': 3, 'am...</td>\n",
       "      <td>[{'date': '2002.7.6', 'work': 'pollution contr...</td>\n",
       "      <td>[{'date': '2017.11.7', 'place': {'country': 'L...</td>\n",
       "      <td>PN1</td>\n",
       "      <td>Panchthar</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age   date_out date_return  \\\n",
       "ID                                       \n",
       "82Q6H2OCLB   42  2015.2.12    2019.3.9   \n",
       "\n",
       "                                              foreign_address  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...   \n",
       "\n",
       "                                                  fund2invest gender  \\\n",
       "ID                                                                     \n",
       "82Q6H2OCLB  {'invetsment sector': '8%', 'tourism and hospi...   male   \n",
       "\n",
       "                                                 home_address interest  \\\n",
       "ID                                                                       \n",
       "82Q6H2OCLB  {'province': 'PN1', 'district': 'Panchthar', '...  stories   \n",
       "\n",
       "                                                personal_view  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [While all parties involved in the constructio...   \n",
       "\n",
       "                                                   profession  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'major': 'Arts, Design, Entertainment, Sports...   \n",
       "\n",
       "                                                   remittance  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'month': 6, 'amount': 459}, {'month': 3, 'am...   \n",
       "\n",
       "                                                  social_work  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'date': '2002.7.6', 'work': 'pollution contr...   \n",
       "\n",
       "                                                      travell province  \\\n",
       "ID                                                                       \n",
       "82Q6H2OCLB  [{'date': '2017.11.7', 'place': {'country': 'L...      PN1   \n",
       "\n",
       "             district  \n",
       "ID                     \n",
       "82Q6H2OCLB  Panchthar  "
      ]
     },
     "execution_count": 36,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['province'] = df['home_address'].apply(lambda x: x['province'])\n",
    "df['district'] = df['home_address'].apply(lambda x: x['district'])\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Create a new column for ```foreign-country```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 37,
   "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>age</th>\n",
       "      <th>date_out</th>\n",
       "      <th>date_return</th>\n",
       "      <th>foreign_address</th>\n",
       "      <th>fund2invest</th>\n",
       "      <th>gender</th>\n",
       "      <th>home_address</th>\n",
       "      <th>interest</th>\n",
       "      <th>personal_view</th>\n",
       "      <th>profession</th>\n",
       "      <th>remittance</th>\n",
       "      <th>social_work</th>\n",
       "      <th>travell</th>\n",
       "      <th>province</th>\n",
       "      <th>district</th>\n",
       "      <th>foreign-country</th>\n",
       "      <th>foreign-city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>82Q6H2OCLB</th>\n",
       "      <td>42</td>\n",
       "      <td>2015.2.12</td>\n",
       "      <td>2019.3.9</td>\n",
       "      <td>{'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...</td>\n",
       "      <td>{'invetsment sector': '8%', 'tourism and hospi...</td>\n",
       "      <td>male</td>\n",
       "      <td>{'province': 'PN1', 'district': 'Panchthar', '...</td>\n",
       "      <td>stories</td>\n",
       "      <td>[While all parties involved in the constructio...</td>\n",
       "      <td>{'major': 'Arts, Design, Entertainment, Sports...</td>\n",
       "      <td>[{'month': 6, 'amount': 459}, {'month': 3, 'am...</td>\n",
       "      <td>[{'date': '2002.7.6', 'work': 'pollution contr...</td>\n",
       "      <td>[{'date': '2017.11.7', 'place': {'country': 'L...</td>\n",
       "      <td>PN1</td>\n",
       "      <td>Panchthar</td>\n",
       "      <td>Aruba</td>\n",
       "      <td>ORANJESTAD</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age   date_out date_return  \\\n",
       "ID                                       \n",
       "82Q6H2OCLB   42  2015.2.12    2019.3.9   \n",
       "\n",
       "                                              foreign_address  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'country': 'Aruba', 'ID': 'AW', 'lat': '12.52...   \n",
       "\n",
       "                                                  fund2invest gender  \\\n",
       "ID                                                                     \n",
       "82Q6H2OCLB  {'invetsment sector': '8%', 'tourism and hospi...   male   \n",
       "\n",
       "                                                 home_address interest  \\\n",
       "ID                                                                       \n",
       "82Q6H2OCLB  {'province': 'PN1', 'district': 'Panchthar', '...  stories   \n",
       "\n",
       "                                                personal_view  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [While all parties involved in the constructio...   \n",
       "\n",
       "                                                   profession  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  {'major': 'Arts, Design, Entertainment, Sports...   \n",
       "\n",
       "                                                   remittance  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'month': 6, 'amount': 459}, {'month': 3, 'am...   \n",
       "\n",
       "                                                  social_work  \\\n",
       "ID                                                              \n",
       "82Q6H2OCLB  [{'date': '2002.7.6', 'work': 'pollution contr...   \n",
       "\n",
       "                                                      travell province  \\\n",
       "ID                                                                       \n",
       "82Q6H2OCLB  [{'date': '2017.11.7', 'place': {'country': 'L...      PN1   \n",
       "\n",
       "             district foreign-country foreign-city  \n",
       "ID                                                  \n",
       "82Q6H2OCLB  Panchthar           Aruba   ORANJESTAD  "
      ]
     },
     "execution_count": 37,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df['foreign-country'] = df['foreign_address'].apply(lambda x: x['country'])\n",
    "df['foreign-city'] = df['foreign_address'].apply(lambda x: x['city'])\n",
    "df.head(1)"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How many are in ```foreign-country``` Japan?\n",
    "\n",
    "Hint: Use ```Filter```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 38,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(6, 17)"
      ]
     },
     "execution_count": 38,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['foreign-country']=='Japan'].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How many are in ```district``` Jhapa?\n",
    "\n",
    "Hint: Use ```Filter```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 39,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(12, 17)"
      ]
     },
     "execution_count": 39,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['district']=='Jhapa'].shape"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Collect the statistics based on country\n",
    "\n",
    "Hint: Use ```groupby``` followed by ```size```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 40,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "foreign-country\n",
       "Afghanistan       3\n",
       "Albania           5\n",
       "Algeria           5\n",
       "American-Samoa    3\n",
       "Andorra           7\n",
       "dtype: int64"
      ]
     },
     "execution_count": 40,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('foreign-country').size().head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- What is the mean age of people from ```Jumla```\n",
    "\n",
    "Hint: Use ```Filter``` for district ```Jumla``` and select column ```age``` then apply ```mean()```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 69,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "41.0"
      ]
     },
     "execution_count": 69,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[df['district']=='Jumla']['age'].mean()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Create a table for mean ```age``` of people from each ```district```\n",
    "\n",
    "Hint: Use ```groupby``` district and selest ```age``` column then implement ```aggregate``` with ```mean``` key word"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "district\n",
       "Achham          38.923077\n",
       "Arghakhanchi    37.090909\n",
       "Baglung         34.692308\n",
       "Baitadi         39.500000\n",
       "Bajhang         35.588235\n",
       "Name: age, dtype: float64"
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df.groupby('district')['age'].aggregate('mean').head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- Count the number of people in each of the ```foreign-country``` who has age below <30.\n",
    "\n",
    "Hint: create ```user-defined-function``` for counting age below 30 and supply the function inside ```aggregate``` functionality after ```groupby``` operation over ```foreign-country```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "foreign-country\n",
       "Afghanistan       0\n",
       "Albania           1\n",
       "Algeria           0\n",
       "American-Samoa    1\n",
       "Andorra           2\n",
       "Name: age, dtype: int64"
      ]
     },
     "execution_count": 59,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def age_count(col):\n",
    "    count = 0\n",
    "    for item in col:\n",
    "        if item<30:\n",
    "            count= count+1\n",
    "    return count\n",
    "            \n",
    "    \n",
    "df.groupby('foreign-country')['age'].aggregate(age_count).head()"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "- How many people from ```Jhapa``` are in ```Japan```?\n",
    "\n",
    "Hint: Use double ```Filter``` for ```district``` and ```foreing-country```"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 68,
   "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>age</th>\n",
       "      <th>date_out</th>\n",
       "      <th>date_return</th>\n",
       "      <th>foreign_address</th>\n",
       "      <th>fund2invest</th>\n",
       "      <th>gender</th>\n",
       "      <th>home_address</th>\n",
       "      <th>interest</th>\n",
       "      <th>personal_view</th>\n",
       "      <th>profession</th>\n",
       "      <th>remittance</th>\n",
       "      <th>social_work</th>\n",
       "      <th>travell</th>\n",
       "      <th>province</th>\n",
       "      <th>district</th>\n",
       "      <th>foreign-country</th>\n",
       "      <th>foreign-city</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>ID</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>KG908X485D</th>\n",
       "      <td>40</td>\n",
       "      <td>2013.8.19</td>\n",
       "      <td>2020.10.26</td>\n",
       "      <td>{'country': 'Japan', 'ID': 'JP', 'lat': '36.20...</td>\n",
       "      <td>{'invetsment sector': '1%', 'tourism and hospi...</td>\n",
       "      <td>male</td>\n",
       "      <td>{'province': 'PN1', 'district': 'Jhapa', 'MCP'...</td>\n",
       "      <td>singing</td>\n",
       "      <td>[Granted, these initiatives are nothing new—th...</td>\n",
       "      <td>{'major': 'Transportation and Material Moving ...</td>\n",
       "      <td>[{'month': 1, 'amount': 213}, {'month': 1, 'am...</td>\n",
       "      <td>[{'date': '2010.11.20', 'work': 'donation for ...</td>\n",
       "      <td>[{'date': '2015.9.1', 'place': {'country': 'Gu...</td>\n",
       "      <td>PN1</td>\n",
       "      <td>Jhapa</td>\n",
       "      <td>Japan</td>\n",
       "      <td>Yonago</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "            age   date_out date_return  \\\n",
       "ID                                       \n",
       "KG908X485D   40  2013.8.19  2020.10.26   \n",
       "\n",
       "                                              foreign_address  \\\n",
       "ID                                                              \n",
       "KG908X485D  {'country': 'Japan', 'ID': 'JP', 'lat': '36.20...   \n",
       "\n",
       "                                                  fund2invest gender  \\\n",
       "ID                                                                     \n",
       "KG908X485D  {'invetsment sector': '1%', 'tourism and hospi...   male   \n",
       "\n",
       "                                                 home_address interest  \\\n",
       "ID                                                                       \n",
       "KG908X485D  {'province': 'PN1', 'district': 'Jhapa', 'MCP'...  singing   \n",
       "\n",
       "                                                personal_view  \\\n",
       "ID                                                              \n",
       "KG908X485D  [Granted, these initiatives are nothing new—th...   \n",
       "\n",
       "                                                   profession  \\\n",
       "ID                                                              \n",
       "KG908X485D  {'major': 'Transportation and Material Moving ...   \n",
       "\n",
       "                                                   remittance  \\\n",
       "ID                                                              \n",
       "KG908X485D  [{'month': 1, 'amount': 213}, {'month': 1, 'am...   \n",
       "\n",
       "                                                  social_work  \\\n",
       "ID                                                              \n",
       "KG908X485D  [{'date': '2010.11.20', 'work': 'donation for ...   \n",
       "\n",
       "                                                      travell province  \\\n",
       "ID                                                                       \n",
       "KG908X485D  [{'date': '2015.9.1', 'place': {'country': 'Gu...      PN1   \n",
       "\n",
       "           district foreign-country foreign-city  \n",
       "ID                                                \n",
       "KG908X485D    Jhapa           Japan       Yonago  "
      ]
     },
     "execution_count": 68,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "ddf = df[df['district']=='Jhapa']\n",
    "ddf[ddf['foreign-country']=='Japan']"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Assignment\n",
    "1. Create a dataframe for male and female separately. What is the shape of the data?\n",
    "2. Create a dataframe for male and female separately and find out (a) how many female are in abroad from each of the district (b) how many male are in the each of the foreign country?\n",
    "3. What is the difference in mean value of age of male and female from district Jumla?\n",
    "4. How many females are there in Japan from Jhapa?"
   ]
  }
 ],
 "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.3"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}