Digital_Apprentiship_Program .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 5,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Setup complete.\n"
     ]
    }
   ],
   "source": [
    "# Downloading the dataset \n",
    "\n",
    "import requests\n",
    "\n",
    "download_url ='https://raw.githubusercontent.com/onaio/ona-tech/master/data/water_points.json'\n",
    "target_json_path = 'water_points.json'\n",
    "\n",
    "response = requests.get(download_url)\n",
    "response.raise_for_status() #check if request was successful\n",
    "with open(target_json_path, 'wb') as f:\n",
    "    f.write(response.content)\n",
    "\n",
    "print('Setup complete.')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "pandas.core.frame.DataFrame"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import pandas as pd\n",
    "resources = pd.read_json('water_points.json')\n",
    "type(resources)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>water_pay</th>\n",
       "      <th>respondent</th>\n",
       "      <th>research_asst_name</th>\n",
       "      <th>water_used_season</th>\n",
       "      <th>_bamboo_dataset_id</th>\n",
       "      <th>_deleted_at</th>\n",
       "      <th>water_point_condition</th>\n",
       "      <th>_xform_id_string</th>\n",
       "      <th>other_point_1km</th>\n",
       "      <th>_attachments</th>\n",
       "      <th>communities_villages</th>\n",
       "      <th>end</th>\n",
       "      <th>animal_number</th>\n",
       "      <th>water_point_id</th>\n",
       "      <th>start</th>\n",
       "      <th>water_connected</th>\n",
       "      <th>water_manager_name</th>\n",
       "      <th>_status</th>\n",
       "      <th>enum_id_1</th>\n",
       "      <th>water_lift_mechanism</th>\n",
       "      <th>districts_divisions</th>\n",
       "      <th>_uuid</th>\n",
       "      <th>grid</th>\n",
       "      <th>date</th>\n",
       "      <th>formhub/uuid</th>\n",
       "      <th>road_available</th>\n",
       "      <th>water_functioning</th>\n",
       "      <th>_submission_time</th>\n",
       "      <th>signal</th>\n",
       "      <th>water_source_type</th>\n",
       "      <th>_geolocation</th>\n",
       "      <th>water_point_image</th>\n",
       "      <th>water_point_geocode</th>\n",
       "      <th>deviceid</th>\n",
       "      <th>locations_wards</th>\n",
       "      <th>water_manager</th>\n",
       "      <th>water_developer</th>\n",
       "      <th>_id</th>\n",
       "      <th>animal_point</th>\n",
       "      <th>water_mechanism_plate</th>\n",
       "      <th>water_lift_mechanism_type</th>\n",
       "      <th>road_type</th>\n",
       "      <th>water_mechanism_plate_units</th>\n",
       "      <th>water_mechanism_plate_no</th>\n",
       "      <th>water_not_functioning</th>\n",
       "      <th>water_source_type_other</th>\n",
       "      <th>simserial</th>\n",
       "      <th>subscriberid</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>0</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>Haruna Mohammed</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>no</td>\n",
       "      <td>[north_ghana/attachments/1351696546452.jpg]</td>\n",
       "      <td>Gumaryili</td>\n",
       "      <td>2012-11-12T11:46:32.454Z</td>\n",
       "      <td>more_500</td>\n",
       "      <td>xxx</td>\n",
       "      <td>2012-10-31T15:11:04.618Z</td>\n",
       "      <td>no</td>\n",
       "      <td>community members</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>5</td>\n",
       "      <td>no</td>\n",
       "      <td>northern</td>\n",
       "      <td>f8bcee72d7a0400fb99ae11bbf804010</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>2012-11-13 07:13:57</td>\n",
       "      <td>low</td>\n",
       "      <td>dam_dugout</td>\n",
       "      <td>[10.1892764, -0.66410362]</td>\n",
       "      <td>1351696546452.jpg</td>\n",
       "      <td>10.1892764 -0.66410362 155.10000610351563 5.0</td>\n",
       "      <td>355047040123780</td>\n",
       "      <td>west_mamprusi</td>\n",
       "      <td>community</td>\n",
       "      <td>community</td>\n",
       "      <td>381705</td>\n",
       "      <td>yes</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>1</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>Haruna Mohmmed</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>yes</td>\n",
       "      <td>[north_ghana/attachments/1351701849971.jpg]</td>\n",
       "      <td>Selinvoya</td>\n",
       "      <td>2012-11-12T11:49:36.619Z</td>\n",
       "      <td>50_to_500</td>\n",
       "      <td>xxx</td>\n",
       "      <td>2012-10-31T16:41:49.738Z</td>\n",
       "      <td>no</td>\n",
       "      <td>Amadu Salifu</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>5</td>\n",
       "      <td>yes</td>\n",
       "      <td>northern</td>\n",
       "      <td>c2f6b298955f47ab9f177bee1214141d</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>2012-11-13 07:14:04</td>\n",
       "      <td>high</td>\n",
       "      <td>unprotected_well</td>\n",
       "      <td>[10.28173052, -0.56901122]</td>\n",
       "      <td>1351701849971.jpg</td>\n",
       "      <td>10.28173052 -0.56901122 201.89999389648438 5.0</td>\n",
       "      <td>355047040123780</td>\n",
       "      <td>west_mamprusi</td>\n",
       "      <td>individual</td>\n",
       "      <td>community</td>\n",
       "      <td>381706</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>manual_power</td>\n",
       "      <td>gravel</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>2</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>Haruna Mohammed</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>yes</td>\n",
       "      <td>[north_ghana/attachments/1351702462336.jpg]</td>\n",
       "      <td>Selinvoya</td>\n",
       "      <td>2012-10-31T16:57:37.864Z</td>\n",
       "      <td>50_to_500</td>\n",
       "      <td>xxx</td>\n",
       "      <td>2012-10-31T16:52:02.601Z</td>\n",
       "      <td>no</td>\n",
       "      <td>Sulemana Abdulai</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>5</td>\n",
       "      <td>yes</td>\n",
       "      <td>northern</td>\n",
       "      <td>6bc6d188611d47f6a666cfd1eaa33998</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>2012-11-13 07:14:07</td>\n",
       "      <td>high</td>\n",
       "      <td>borehole</td>\n",
       "      <td>[10.28169238, -0.56962993]</td>\n",
       "      <td>1351702462336.jpg</td>\n",
       "      <td>10.28169238 -0.56962993 202.60000610351563 5.0</td>\n",
       "      <td>355047040123780</td>\n",
       "      <td>west_mamprusi</td>\n",
       "      <td>community</td>\n",
       "      <td>individual</td>\n",
       "      <td>381707</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>manual_power</td>\n",
       "      <td>paved</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>3</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>Haruna Mohammed</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>yes</td>\n",
       "      <td>[north_ghana/attachments/1351702971561.jpg]</td>\n",
       "      <td>Selinvoya</td>\n",
       "      <td>2012-10-31T17:06:55.047Z</td>\n",
       "      <td>50_to_500</td>\n",
       "      <td>xxx</td>\n",
       "      <td>2012-10-31T16:58:46.672Z</td>\n",
       "      <td>no</td>\n",
       "      <td>Haruna Mohammed</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>5</td>\n",
       "      <td>yes</td>\n",
       "      <td>northern</td>\n",
       "      <td>4b28ac4cbba744d79ba4257f772f94d6</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>2012-11-13 07:14:14</td>\n",
       "      <td>high</td>\n",
       "      <td>borehole</td>\n",
       "      <td>[10.28115661, -0.56918339]</td>\n",
       "      <td>1351702971561.jpg</td>\n",
       "      <td>10.28115661 -0.56918339 199.6999969482422 5.0</td>\n",
       "      <td>355047040123780</td>\n",
       "      <td>west_mamprusi</td>\n",
       "      <td>individual</td>\n",
       "      <td>community</td>\n",
       "      <td>381708</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>manual_power</td>\n",
       "      <td>paved</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>4</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>Haruna Mohammed</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>NaT</td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>yes</td>\n",
       "      <td>[north_ghana/attachments/1351703622326.jpg]</td>\n",
       "      <td>Selinvoya</td>\n",
       "      <td>2012-10-31T17:15:57.847Z</td>\n",
       "      <td>50_to_500</td>\n",
       "      <td>xxx</td>\n",
       "      <td>2012-10-31T17:08:27.160Z</td>\n",
       "      <td>no</td>\n",
       "      <td>Sulemana</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>5</td>\n",
       "      <td>yes</td>\n",
       "      <td>northern</td>\n",
       "      <td>7893ce5321804f229e533f36e90c9c6f</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>2012-10-31</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>yes</td>\n",
       "      <td>yes</td>\n",
       "      <td>2012-11-13 07:14:22</td>\n",
       "      <td>high</td>\n",
       "      <td>borehole</td>\n",
       "      <td>[10.28044635, -0.56723556]</td>\n",
       "      <td>1351703622326.jpg</td>\n",
       "      <td>10.28044635 -0.56723556 208.6999969482422 5.0</td>\n",
       "      <td>355047040123780</td>\n",
       "      <td>west_mamprusi</td>\n",
       "      <td>community</td>\n",
       "      <td>community</td>\n",
       "      <td>381709</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>manual_power</td>\n",
       "      <td>paved</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "  water_pay respondent research_asst_name water_used_season  \\\n",
       "0        no  community    Haruna Mohammed        year_round   \n",
       "1        no  community     Haruna Mohmmed        year_round   \n",
       "2        no  community    Haruna Mohammed        year_round   \n",
       "3        no  community    Haruna Mohammed        year_round   \n",
       "4        no  community    Haruna Mohammed        year_round   \n",
       "\n",
       "  _bamboo_dataset_id _deleted_at water_point_condition     _xform_id_string  \\\n",
       "0                            NaT           functioning  _08_Water_points_CV   \n",
       "1                            NaT           functioning  _08_Water_points_CV   \n",
       "2                            NaT           functioning  _08_Water_points_CV   \n",
       "3                            NaT           functioning  _08_Water_points_CV   \n",
       "4                            NaT           functioning  _08_Water_points_CV   \n",
       "\n",
       "  other_point_1km                                 _attachments  \\\n",
       "0              no  [north_ghana/attachments/1351696546452.jpg]   \n",
       "1             yes  [north_ghana/attachments/1351701849971.jpg]   \n",
       "2             yes  [north_ghana/attachments/1351702462336.jpg]   \n",
       "3             yes  [north_ghana/attachments/1351702971561.jpg]   \n",
       "4             yes  [north_ghana/attachments/1351703622326.jpg]   \n",
       "\n",
       "  communities_villages                       end animal_number water_point_id  \\\n",
       "0            Gumaryili  2012-11-12T11:46:32.454Z      more_500            xxx   \n",
       "1            Selinvoya  2012-11-12T11:49:36.619Z     50_to_500            xxx   \n",
       "2            Selinvoya  2012-10-31T16:57:37.864Z     50_to_500            xxx   \n",
       "3            Selinvoya  2012-10-31T17:06:55.047Z     50_to_500            xxx   \n",
       "4            Selinvoya  2012-10-31T17:15:57.847Z     50_to_500            xxx   \n",
       "\n",
       "                      start water_connected water_manager_name  \\\n",
       "0  2012-10-31T15:11:04.618Z              no  community members   \n",
       "1  2012-10-31T16:41:49.738Z              no       Amadu Salifu   \n",
       "2  2012-10-31T16:52:02.601Z              no   Sulemana Abdulai   \n",
       "3  2012-10-31T16:58:46.672Z              no    Haruna Mohammed   \n",
       "4  2012-10-31T17:08:27.160Z              no          Sulemana    \n",
       "\n",
       "             _status  enum_id_1 water_lift_mechanism districts_divisions  \\\n",
       "0  submitted_via_web          5                   no            northern   \n",
       "1  submitted_via_web          5                  yes            northern   \n",
       "2  submitted_via_web          5                  yes            northern   \n",
       "3  submitted_via_web          5                  yes            northern   \n",
       "4  submitted_via_web          5                  yes            northern   \n",
       "\n",
       "                              _uuid                grid       date  \\\n",
       "0  f8bcee72d7a0400fb99ae11bbf804010  grid_further_500_m 2012-10-31   \n",
       "1  c2f6b298955f47ab9f177bee1214141d  grid_further_500_m 2012-10-31   \n",
       "2  6bc6d188611d47f6a666cfd1eaa33998  grid_further_500_m 2012-10-31   \n",
       "3  4b28ac4cbba744d79ba4257f772f94d6  grid_further_500_m 2012-10-31   \n",
       "4  7893ce5321804f229e533f36e90c9c6f  grid_further_500_m 2012-10-31   \n",
       "\n",
       "                       formhub/uuid road_available water_functioning  \\\n",
       "0  4d41d54d134c4bfa9078571addd819b9             no               yes   \n",
       "1  4d41d54d134c4bfa9078571addd819b9            yes               yes   \n",
       "2  4d41d54d134c4bfa9078571addd819b9            yes               yes   \n",
       "3  4d41d54d134c4bfa9078571addd819b9            yes               yes   \n",
       "4  4d41d54d134c4bfa9078571addd819b9            yes               yes   \n",
       "\n",
       "     _submission_time signal water_source_type                _geolocation  \\\n",
       "0 2012-11-13 07:13:57    low        dam_dugout   [10.1892764, -0.66410362]   \n",
       "1 2012-11-13 07:14:04   high  unprotected_well  [10.28173052, -0.56901122]   \n",
       "2 2012-11-13 07:14:07   high          borehole  [10.28169238, -0.56962993]   \n",
       "3 2012-11-13 07:14:14   high          borehole  [10.28115661, -0.56918339]   \n",
       "4 2012-11-13 07:14:22   high          borehole  [10.28044635, -0.56723556]   \n",
       "\n",
       "   water_point_image                             water_point_geocode  \\\n",
       "0  1351696546452.jpg   10.1892764 -0.66410362 155.10000610351563 5.0   \n",
       "1  1351701849971.jpg  10.28173052 -0.56901122 201.89999389648438 5.0   \n",
       "2  1351702462336.jpg  10.28169238 -0.56962993 202.60000610351563 5.0   \n",
       "3  1351702971561.jpg   10.28115661 -0.56918339 199.6999969482422 5.0   \n",
       "4  1351703622326.jpg   10.28044635 -0.56723556 208.6999969482422 5.0   \n",
       "\n",
       "          deviceid locations_wards water_manager water_developer     _id  \\\n",
       "0  355047040123780   west_mamprusi     community       community  381705   \n",
       "1  355047040123780   west_mamprusi    individual       community  381706   \n",
       "2  355047040123780   west_mamprusi     community      individual  381707   \n",
       "3  355047040123780   west_mamprusi    individual       community  381708   \n",
       "4  355047040123780   west_mamprusi     community       community  381709   \n",
       "\n",
       "  animal_point water_mechanism_plate water_lift_mechanism_type road_type  \\\n",
       "0          yes                   NaN                       NaN       NaN   \n",
       "1          yes                    no              manual_power    gravel   \n",
       "2          yes                    no              manual_power     paved   \n",
       "3          yes                    no              manual_power     paved   \n",
       "4          yes                    no              manual_power     paved   \n",
       "\n",
       "  water_mechanism_plate_units  water_mechanism_plate_no water_not_functioning  \\\n",
       "0                         NaN                       NaN                   NaN   \n",
       "1                         NaN                       NaN                   NaN   \n",
       "2                         NaN                       NaN                   NaN   \n",
       "3                         NaN                       NaN                   NaN   \n",
       "4                         NaN                       NaN                   NaN   \n",
       "\n",
       "  water_source_type_other  simserial  subscriberid  \n",
       "0                     NaN        NaN           NaN  \n",
       "1                     NaN        NaN           NaN  \n",
       "2                     NaN        NaN           NaN  \n",
       "3                     NaN        NaN           NaN  \n",
       "4                     NaN        NaN           NaN  "
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.set_option('display.max.columns', None)\n",
    "resources.head()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "<class 'pandas.core.frame.DataFrame'>\n",
      "RangeIndex: 712 entries, 0 to 711\n",
      "Data columns (total 48 columns):\n",
      "water_pay                      712 non-null object\n",
      "respondent                     712 non-null object\n",
      "research_asst_name             712 non-null object\n",
      "water_used_season              712 non-null object\n",
      "_bamboo_dataset_id             712 non-null object\n",
      "_deleted_at                    0 non-null datetime64[ns]\n",
      "water_point_condition          712 non-null object\n",
      "_xform_id_string               712 non-null object\n",
      "other_point_1km                712 non-null object\n",
      "_attachments                   712 non-null object\n",
      "communities_villages           712 non-null object\n",
      "end                            712 non-null object\n",
      "animal_number                  528 non-null object\n",
      "water_point_id                 712 non-null object\n",
      "start                          712 non-null object\n",
      "water_connected                712 non-null object\n",
      "water_manager_name             708 non-null object\n",
      "_status                        712 non-null object\n",
      "enum_id_1                      712 non-null int64\n",
      "water_lift_mechanism           712 non-null object\n",
      "districts_divisions            712 non-null object\n",
      "_uuid                          712 non-null object\n",
      "grid                           712 non-null object\n",
      "date                           712 non-null datetime64[ns]\n",
      "formhub/uuid                   712 non-null object\n",
      "road_available                 712 non-null object\n",
      "water_functioning              712 non-null object\n",
      "_submission_time               712 non-null datetime64[ns]\n",
      "signal                         712 non-null object\n",
      "water_source_type              712 non-null object\n",
      "_geolocation                   712 non-null object\n",
      "water_point_image              712 non-null object\n",
      "water_point_geocode            709 non-null object\n",
      "deviceid                       712 non-null int64\n",
      "locations_wards                712 non-null object\n",
      "water_manager                  712 non-null object\n",
      "water_developer                712 non-null object\n",
      "_id                            712 non-null int64\n",
      "animal_point                   712 non-null object\n",
      "water_mechanism_plate          649 non-null object\n",
      "water_lift_mechanism_type      649 non-null object\n",
      "road_type                      334 non-null object\n",
      "water_mechanism_plate_units    43 non-null object\n",
      "water_mechanism_plate_no       43 non-null float64\n",
      "water_not_functioning          87 non-null object\n",
      "water_source_type_other        3 non-null object\n",
      "simserial                      5 non-null float64\n",
      "subscriberid                   5 non-null float64\n",
      "dtypes: datetime64[ns](3), float64(3), int64(3), object(39)\n",
      "memory usage: 267.1+ KB\n"
     ]
    }
   ],
   "source": [
    "resources.info()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "(712, 48)"
      ]
     },
     "execution_count": 12,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "resources.shape"
   ]
  },
  {
   "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>water_pay</th>\n",
       "      <th>respondent</th>\n",
       "      <th>research_asst_name</th>\n",
       "      <th>water_used_season</th>\n",
       "      <th>_bamboo_dataset_id</th>\n",
       "      <th>water_point_condition</th>\n",
       "      <th>_xform_id_string</th>\n",
       "      <th>other_point_1km</th>\n",
       "      <th>_attachments</th>\n",
       "      <th>communities_villages</th>\n",
       "      <th>end</th>\n",
       "      <th>animal_number</th>\n",
       "      <th>water_point_id</th>\n",
       "      <th>start</th>\n",
       "      <th>water_connected</th>\n",
       "      <th>water_manager_name</th>\n",
       "      <th>_status</th>\n",
       "      <th>water_lift_mechanism</th>\n",
       "      <th>districts_divisions</th>\n",
       "      <th>_uuid</th>\n",
       "      <th>grid</th>\n",
       "      <th>formhub/uuid</th>\n",
       "      <th>road_available</th>\n",
       "      <th>water_functioning</th>\n",
       "      <th>signal</th>\n",
       "      <th>water_source_type</th>\n",
       "      <th>_geolocation</th>\n",
       "      <th>water_point_image</th>\n",
       "      <th>water_point_geocode</th>\n",
       "      <th>locations_wards</th>\n",
       "      <th>water_manager</th>\n",
       "      <th>water_developer</th>\n",
       "      <th>animal_point</th>\n",
       "      <th>water_mechanism_plate</th>\n",
       "      <th>water_lift_mechanism_type</th>\n",
       "      <th>road_type</th>\n",
       "      <th>water_mechanism_plate_units</th>\n",
       "      <th>water_not_functioning</th>\n",
       "      <th>water_source_type_other</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <td>count</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>528</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>708</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>709</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>712</td>\n",
       "      <td>649</td>\n",
       "      <td>649</td>\n",
       "      <td>334</td>\n",
       "      <td>43</td>\n",
       "      <td>87</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>unique</td>\n",
       "      <td>3</td>\n",
       "      <td>4</td>\n",
       "      <td>30</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>6</td>\n",
       "      <td>1</td>\n",
       "      <td>2</td>\n",
       "      <td>711</td>\n",
       "      <td>65</td>\n",
       "      <td>711</td>\n",
       "      <td>4</td>\n",
       "      <td>81</td>\n",
       "      <td>711</td>\n",
       "      <td>3</td>\n",
       "      <td>498</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>712</td>\n",
       "      <td>4</td>\n",
       "      <td>1</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>9</td>\n",
       "      <td>709</td>\n",
       "      <td>711</td>\n",
       "      <td>708</td>\n",
       "      <td>2</td>\n",
       "      <td>8</td>\n",
       "      <td>9</td>\n",
       "      <td>3</td>\n",
       "      <td>3</td>\n",
       "      <td>5</td>\n",
       "      <td>4</td>\n",
       "      <td>2</td>\n",
       "      <td>11</td>\n",
       "      <td>3</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>top</td>\n",
       "      <td>no</td>\n",
       "      <td>community</td>\n",
       "      <td>HM</td>\n",
       "      <td>year_round</td>\n",
       "      <td></td>\n",
       "      <td>functioning</td>\n",
       "      <td>_08_Water_points_CV</td>\n",
       "      <td>yes</td>\n",
       "      <td>[north_ghana/attachments/1358081512606.jpg]</td>\n",
       "      <td>Kpatarigu</td>\n",
       "      <td>2013-01-13T12:56:37.235Z</td>\n",
       "      <td>50_to_500</td>\n",
       "      <td>XXX</td>\n",
       "      <td>2013-01-13T12:49:32.776Z</td>\n",
       "      <td>no</td>\n",
       "      <td>Xxx</td>\n",
       "      <td>submitted_via_web</td>\n",
       "      <td>yes</td>\n",
       "      <td>upper_east</td>\n",
       "      <td>e6e297f3f909478fa2bf56af69a682ae</td>\n",
       "      <td>grid_further_500_m</td>\n",
       "      <td>4d41d54d134c4bfa9078571addd819b9</td>\n",
       "      <td>no</td>\n",
       "      <td>yes</td>\n",
       "      <td>high</td>\n",
       "      <td>unprotected_well</td>\n",
       "      <td>[None, None]</td>\n",
       "      <td>1358081512606.jpg</td>\n",
       "      <td>10.5379067 -1.38202276 229.6999969482422 5.0</td>\n",
       "      <td>builsa</td>\n",
       "      <td>community</td>\n",
       "      <td>individual</td>\n",
       "      <td>yes</td>\n",
       "      <td>no</td>\n",
       "      <td>manual_power</td>\n",
       "      <td>gravel dirt</td>\n",
       "      <td>horsepoower</td>\n",
       "      <td>kiosk_broken</td>\n",
       "      <td>Pipe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <td>freq</td>\n",
       "      <td>693</td>\n",
       "      <td>640</td>\n",
       "      <td>214</td>\n",
       "      <td>503</td>\n",
       "      <td>712</td>\n",
       "      <td>590</td>\n",
       "      <td>712</td>\n",
       "      <td>679</td>\n",
       "      <td>2</td>\n",
       "      <td>51</td>\n",
       "      <td>2</td>\n",
       "      <td>258</td>\n",
       "      <td>325</td>\n",
       "      <td>2</td>\n",
       "      <td>682</td>\n",
       "      <td>31</td>\n",
       "      <td>712</td>\n",
       "      <td>649</td>\n",
       "      <td>405</td>\n",
       "      <td>1</td>\n",
       "      <td>317</td>\n",
       "      <td>712</td>\n",
       "      <td>377</td>\n",
       "      <td>623</td>\n",
       "      <td>380</td>\n",
       "      <td>228</td>\n",
       "      <td>3</td>\n",
       "      <td>2</td>\n",
       "      <td>2</td>\n",
       "      <td>406</td>\n",
       "      <td>401</td>\n",
       "      <td>244</td>\n",
       "      <td>528</td>\n",
       "      <td>588</td>\n",
       "      <td>426</td>\n",
       "      <td>138</td>\n",
       "      <td>42</td>\n",
       "      <td>39</td>\n",
       "      <td>1</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       water_pay respondent research_asst_name water_used_season  \\\n",
       "count        712        712                712               712   \n",
       "unique         3          4                 30                 6   \n",
       "top           no  community                 HM        year_round   \n",
       "freq         693        640                214               503   \n",
       "\n",
       "       _bamboo_dataset_id water_point_condition     _xform_id_string  \\\n",
       "count                 712                   712                  712   \n",
       "unique                  1                     6                    1   \n",
       "top                                 functioning  _08_Water_points_CV   \n",
       "freq                  712                   590                  712   \n",
       "\n",
       "       other_point_1km                                 _attachments  \\\n",
       "count              712                                          712   \n",
       "unique               2                                          711   \n",
       "top                yes  [north_ghana/attachments/1358081512606.jpg]   \n",
       "freq               679                                            2   \n",
       "\n",
       "       communities_villages                       end animal_number  \\\n",
       "count                   712                       712           528   \n",
       "unique                   65                       711             4   \n",
       "top               Kpatarigu  2013-01-13T12:56:37.235Z     50_to_500   \n",
       "freq                     51                         2           258   \n",
       "\n",
       "       water_point_id                     start water_connected  \\\n",
       "count             712                       712             712   \n",
       "unique             81                       711               3   \n",
       "top               XXX  2013-01-13T12:49:32.776Z              no   \n",
       "freq              325                         2             682   \n",
       "\n",
       "       water_manager_name            _status water_lift_mechanism  \\\n",
       "count                 708                712                  712   \n",
       "unique                498                  1                    3   \n",
       "top                   Xxx  submitted_via_web                  yes   \n",
       "freq                   31                712                  649   \n",
       "\n",
       "       districts_divisions                             _uuid  \\\n",
       "count                  712                               712   \n",
       "unique                   2                               712   \n",
       "top             upper_east  e6e297f3f909478fa2bf56af69a682ae   \n",
       "freq                   405                                 1   \n",
       "\n",
       "                      grid                      formhub/uuid road_available  \\\n",
       "count                  712                               712            712   \n",
       "unique                   4                                 1              3   \n",
       "top     grid_further_500_m  4d41d54d134c4bfa9078571addd819b9             no   \n",
       "freq                   317                               712            377   \n",
       "\n",
       "       water_functioning signal water_source_type  _geolocation  \\\n",
       "count                712    712               712           712   \n",
       "unique                 3      3                 9           709   \n",
       "top                  yes   high  unprotected_well  [None, None]   \n",
       "freq                 623    380               228             3   \n",
       "\n",
       "        water_point_image                           water_point_geocode  \\\n",
       "count                 712                                           709   \n",
       "unique                711                                           708   \n",
       "top     1358081512606.jpg  10.5379067 -1.38202276 229.6999969482422 5.0   \n",
       "freq                    2                                             2   \n",
       "\n",
       "       locations_wards water_manager water_developer animal_point  \\\n",
       "count              712           712             712          712   \n",
       "unique               2             8               9            3   \n",
       "top             builsa     community      individual          yes   \n",
       "freq               406           401             244          528   \n",
       "\n",
       "       water_mechanism_plate water_lift_mechanism_type    road_type  \\\n",
       "count                    649                       649          334   \n",
       "unique                     3                         5            4   \n",
       "top                       no              manual_power  gravel dirt   \n",
       "freq                     588                       426          138   \n",
       "\n",
       "       water_mechanism_plate_units water_not_functioning  \\\n",
       "count                           43                    87   \n",
       "unique                           2                    11   \n",
       "top                    horsepoower          kiosk_broken   \n",
       "freq                            42                    39   \n",
       "\n",
       "       water_source_type_other  \n",
       "count                        3  \n",
       "unique                       3  \n",
       "top                       Pipe  \n",
       "freq                         1  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "import numpy as np\n",
    "resources.describe(include=np.object)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "functioning           590\n",
       "broken                 40\n",
       "abandoned              36\n",
       "newly_constructed      28\n",
       "under_construction     16\n",
       "na_dn                   2\n",
       "Name: water_point_condition, dtype: int64"
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Water point states\n",
    "resources['water_point_condition'].value_counts()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 102,
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "True     590\n",
       "False    122\n",
       "Name: water_point_condition, dtype: int64"
      ]
     },
     "execution_count": 102,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# NUMBER OF WATER POINTS THAT ARE FUNCTUIONAL\n",
    "\n",
    "is_functioning = resources['water_point_condition']=='functioning'\n",
    "is_functioning.value_counts()\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 97,
   "metadata": {
    "scrolled": false
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "communities_villages  water_point_condition\n",
       "Abanyeri              functioning               3\n",
       "                      newly_constructed         1\n",
       "Akpari-yeri           functioning               3\n",
       "Alavanyo              functioning               2\n",
       "                      abandoned                 1\n",
       "                                               ..\n",
       "Zukpeni               abandoned                 3\n",
       "                      functioning               2\n",
       "                      broken                    1\n",
       "Zundem                functioning              29\n",
       "                      under_construction        1\n",
       "Name: water_point_condition, Length: 134, dtype: int64"
      ]
     },
     "execution_count": 97,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# NUMBER OF WATER POINTS PER COMMUNITY\n",
    "\n",
    "water_points = resources.groupby('communities_villages').water_point_condition.value_counts()\n",
    "water_points"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 16,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "Nayoku         0.150\n",
       "Kubore         0.100\n",
       "Loagri_1_      0.100\n",
       "Vundema        0.050\n",
       "Bandem         0.050\n",
       "Gbaarigu       0.050\n",
       "Zanwara        0.050\n",
       "Namgurima      0.050\n",
       "Arigu          0.050\n",
       "Soo            0.050\n",
       "Banyangsa      0.025\n",
       "Nyankpiensa    0.025\n",
       "Kaasa          0.025\n",
       "Luisa          0.025\n",
       "Zogsa          0.025\n",
       "Kanwaasa       0.025\n",
       "Guuta          0.025\n",
       "Jagsa          0.025\n",
       "Longsa         0.025\n",
       "Kpatarigu      0.025\n",
       "Tantala        0.025\n",
       "Zukpeni        0.025\n",
       "Name: communities_villages, dtype: float64"
      ]
     },
     "execution_count": 16,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# COMMUNITY RANK BY BROKEN WATER POINTS in PERCENTAGE\n",
    "broken_water_points = resources.loc[resources['water_point_condition']=='broken','communities_villages'].value_counts('broken','communities_villages')\n",
    "broken_water_points\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.7.4"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}