Notebooks/data_production_notebook.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Data Production\n",
    "\n",
    "After having performed prior Exploratory Data Analysis, this notebook seeks to clean up some of the data in a way that allows for streamlined usage in the models which will be created later."
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 215,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:42.141353Z",
     "start_time": "2020-07-29T16:20:42.132358Z"
    }
   },
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import seaborn as sns\n",
    "import matplotlib.pyplot as plt\n",
    "import json\n",
    "import urllib.request\n",
    "from sklearn.impute import KNNImputer, SimpleImputer\n",
    "from sklearn.preprocessing import OneHotEncoder, StandardScaler, MinMaxScaler\n",
    "from sklearn.preprocessing import FunctionTransformer\n",
    "from sklearn.model_selection import train_test_split\n",
    "\n",
    "from sklearn.ensemble import RandomForestClassifier\n",
    "\n",
    "from sklearn.compose import ColumnTransformer\n",
    "from sklearn.compose import make_column_selector\n",
    "## ANCHORUP\n",
    "log_transformer=FunctionTransformer(func=np.log,inverse_func=np.exp,validate=False,check_inverse=False)\n",
    "#validate has to be false for the nans to work, as does check_inverse\n",
    "#Currently not being used\n",
    "\n",
    "from sklearn.pipeline import Pipeline\n",
    "from sklearn.model_selection import cross_val_score\n",
    "\n",
    "from IPython.display import Audio\n",
    "def announce_when_done(target_file='https://www.myinstants.com/media/sounds/meme-de-creditos-finales.mp3'):\n",
    "    '''\n",
    "    place at the end of a notebook, this will play a file when done.  I have no idea how it adds to \n",
    "    notebook memory overhead.  Hopefully not much.\n",
    "    '''\n",
    "    return Audio(url=target_file,autoplay=True)\n",
    "import missingno as msno"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 216,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:42.291203Z",
     "start_time": "2020-07-29T16:20:42.282691Z"
    }
   },
   "outputs": [],
   "source": [
    "def find_overlaps(parent, child,stop_early=False,dataframe=wwdf):\n",
    "    '''\n",
    "    Reports if there is an element in \"child\" that is present in more than one \"parent\"\n",
    "    \n",
    "    \"stop_early\" returns at the first instance of an overlap, otherwise all overlapped-values are returned.\n",
    "    '''\n",
    "    check_list=dataframe.groupby(parent)[child].unique().apply(set).values.tolist()\n",
    "    overlap_list=[]\n",
    "    while len(check_list)>0:\n",
    "        examine=check_list.pop()\n",
    "        for s in examine:\n",
    "            for g in check_list:\n",
    "                if s in g:\n",
    "                    if stop_early:\n",
    "                        return f'First duplication found for {s}'\n",
    "                    overlap_list.append(s)\n",
    "    return set(overlap_list)\n",
    "                \n",
    "#way slower than it needs to be likely.\n",
    "#useful for eda"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 217,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.383025Z",
     "start_time": "2020-07-29T16:20:42.456268Z"
    }
   },
   "outputs": [],
   "source": [
    "ww=pd.read_csv(r'~\\fis\\tanzania_water\\water_well.csv')\n",
    "wt=pd.read_csv(r'~\\fis\\tanzania_water\\water_well_target.csv')\n",
    "wwdf=ww.merge(wt,on='id',validate='one_to_one') #checks to make sure we're good \n",
    "\n",
    "try:\n",
    "    wwdf.set_index('id',inplace=True)\n",
    "except:\n",
    "    print('done')\n",
    "    \n",
    "#import both data sets, check that they work, set the indices to the id column\n",
    "\n",
    "wwdf_raw=wwdf.copy()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 218,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.483563Z",
     "start_time": "2020-07-29T16:20:43.384137Z"
    }
   },
   "outputs": [],
   "source": [
    "#filling in elevation found via google maps api\n",
    "try:\n",
    "    wwdf['elevation']=wwdf['gps_height']\n",
    "except:\n",
    "    print(f'elevation setting went wrong, gps_height in columns is {\"gps_height\" in wwdf.columns}')\n",
    "\n",
    "replacement_dfs=pd.read_csv('grabbed_alts_with_ids.csv',index_col=0)\n",
    "\n",
    "wwdf.loc[replacement_dfs.index,'elevation']=replacement_dfs['elev']\n",
    "\n",
    "#dropping the gps_height column\n",
    "try:\n",
    "    wwdf.drop(columns=['gps_height'],inplace=True)\n",
    "except:\n",
    "    print(f'something went wrong, gps_height in columns is {\"gps_height\" in wwdf.columns}')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 219,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.525859Z",
     "start_time": "2020-07-29T16:20:43.488832Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    wwdf.drop(columns='scheme_name')\n",
    "except:\n",
    "    print(f'elevation setting went wrong, gps_height in columns is {\"scheme_name\" in wwdf.columns}')\n",
    "    \n",
    "#Scheme name is hole-ridden and is at least partially captured by other columns, so is dropped"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 220,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.634512Z",
     "start_time": "2020-07-29T16:20:43.529353Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf.loc[wwdf.construction_year==0,'construction_year']=np.nan\n",
    "#0s are NaNs\n",
    "wwdf['date_recorded']=wwdf['date_recorded'].apply(lambda d: int(d[-4:]) if type(d)==str else d)\n",
    "#just extracting the year for date_recorded, conditional is present to guard against problems that emerge when re-running"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 221,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.676185Z",
     "start_time": "2020-07-29T16:20:43.636027Z"
    }
   },
   "outputs": [],
   "source": [
    "try:\n",
    "    wwdf['time_interval']=wwdf['date_recorded']-wwdf['construction_year']\n",
    "    wwdf.drop(columns=['date_recorded','construction_year'],inplace=True)\n",
    "except: \n",
    "    print(f'date recorded still in: {\"date_recorded\" in wwdf.columns}, construction year still in:  {\"construction_year\" in wwdf.columns}')\n",
    "    \n",
    "#Create time interval column -- later work may improve this"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 222,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.847000Z",
     "start_time": "2020-07-29T16:20:43.679170Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf.public_meeting=wwdf.public_meeting.apply(lambda s: s if pd.isna(s) else (1 if s==True else 0 if s==False else s))\n",
    "wwdf.permit=wwdf.permit.apply(lambda s: s if pd.isna(s) else (1 if s==True else 0 if s==False else s))\n",
    "#Uglier sytnax to guard against re-running\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 223,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:43.936370Z",
     "start_time": "2020-07-29T16:20:43.848266Z"
    }
   },
   "outputs": [],
   "source": [
    "for c in ['num_private','recorded_by','wpt_name']:\n",
    "    try:     \n",
    "        wwdf.drop(columns=[c],inplace=True)\n",
    "    except:\n",
    "        print(f'recorded_by still in: {\"recorded_by\" in wwdf.columns}, wpt_name still in:  {\"wpt_name\" in wwdf.columns}, num_private still in: {\"num_private\" in wwdf.columns}')\n",
    "\n",
    "      #not useful columns"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 224,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:45.437332Z",
     "start_time": "2020-07-29T16:20:44.561534Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf=wwdf.applymap(lambda s: s.upper() if type(s)==str else s)\n",
    "#Crude protection by way of "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 229,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:56.845303Z",
     "start_time": "2020-07-29T16:20:56.840787Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "did not run, protected\n"
     ]
    }
   ],
   "source": [
    "try:\n",
    "    amount_tsh_safeguard+=1\n",
    "    print('did not run, protected')\n",
    "except:\n",
    "    amount_tsh_safeguard=0\n",
    "if amount_tsh_safeguard==0:\n",
    "    wwdf['amount_tsh']=wwdf['amount_tsh'].apply(lambda n: np.log(n+1))\n",
    "\n",
    "#mapping amount tsh to log(n+1) (+1 to guard against the 0s), which is inside a conditional meant to protect it from being re-ran mindlessly"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 230,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:59.201088Z",
     "start_time": "2020-07-29T16:20:59.170049Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf['population']=wwdf['population'].apply(lambda x: np.nan if (x==0 or x==1) else x)\n",
    "#Mapping nan values for population"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 231,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:59.681326Z",
     "start_time": "2020-07-29T16:20:59.622455Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf['water_quality_character']=wwdf.water_quality.apply(lambda s: s.replace(' ABANDONED',''))\n",
    "wwdf['water_quality_abandoned']=wwdf.water_quality.apply(lambda s: 1 if 'ABANDONED' in s else 0)\n",
    "\n",
    "#Another way to look at water_quality"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 232,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:20:59.951941Z",
     "start_time": "2020-07-29T16:20:59.946463Z"
    }
   },
   "outputs": [],
   "source": [
    "# Mostly ordered in terms of apparent quality, except for location\n",
    "# columns that seem to be about roughly the same thing\n",
    "# treating long&lat&elevation separate from other location stuff for syntactical reasons\n",
    "# similarly, water_quality_character&water_quality_abandoned are not in here as they should be used together\n",
    "exclusives=[['basin','subvillage','region','region_code','district_code','lga','ward'],\n",
    " ['scheme_management','scheme_name'],\n",
    " ['extraction_type_group','extraction_type_class','extraction_type'],\n",
    " ['management','management_group'],\n",
    " ['payment_type','payment'],\n",
    " ['water_quality','quality_group'],\n",
    " ['quantity','quantity_group'],\n",
    " ['source_class','source','source_type'],\n",
    " ['waterpoint_type','waterpoint_type_group']\n",
    "]\n",
    "\n",
    "be_wary=['population','installer','subvillage','lga']\n",
    "\n",
    "notes='subvillage is large, lga is large'"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:02:23.629756Z",
     "start_time": "2020-07-29T16:02:23.626349Z"
    }
   },
   "source": [
    "### This is-- in principle-- a leaky method that's being used right now.  Should go back and try to make a non-leaky filling with KNN for lat&long,\n",
    "### notice this will require going back and changing how elevation is handled\n",
    "### It shouldn't *really* be leaky though, since we're not assuming some distribution *really*, each \n",
    "### location does have a specific area and that's what we're mapping from.  We could be **more** accurate with more data, but I don't think overfitting should really be possible like this. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 233,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:21:02.684354Z",
     "start_time": "2020-07-29T16:21:02.211216Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "(-2.962380523139534, 34.179475708779066)\n"
     ]
    }
   ],
   "source": [
    "\n",
    "subvillage_dict=wwdf.loc[(wwdf.latitude!=-2e-08) & (wwdf.longitude!=0)].groupby('subvillage')[['longitude','latitude']].mean().to_dict(orient='index')\n",
    "ward_dict=wwdf.loc[(wwdf.latitude!=-2e-08) & (wwdf.longitude!=0)].groupby('ward')[['longitude','latitude']].mean().to_dict(orient='index')\n",
    "lga_dict=wwdf.loc[(wwdf.latitude!=-2e-08) & (wwdf.longitude!=0)].groupby('lga')[['longitude','latitude']].mean().to_dict(orient='index')\n",
    "region_code_dict=wwdf.loc[(wwdf.latitude!=-2e-08) & (wwdf.longitude!=0)].groupby('region_code')[['longitude','latitude']].mean().to_dict(orient='index')\n",
    "def guess_latlong(row):\n",
    "    try:\n",
    "        lat=subvillage_dict[row['subvillage']]['latitude']\n",
    "        long=subvillage_dict[row['subvillage']]['longitude']\n",
    "        return(lat,long)\n",
    "    except:\n",
    "        \n",
    "#         print('subvillage failed')\n",
    "        pass\n",
    "    try:\n",
    "        lat=ward_dict[row['subvillage']]['latitude']\n",
    "        long=ward_dict[row['ward']]['longitude']\n",
    "        return(lat,long)\n",
    "    except:\n",
    "#         print('ward failed')\n",
    "        pass\n",
    "    try:\n",
    "        lat=lga_dict[row['lga']]['latitude']\n",
    "        long=lga_dict[row['lga']]['longitude']\n",
    "        return(lat,long)\n",
    "    except:\n",
    "#         print('lgafailed')\n",
    "        pass\n",
    "    try:\n",
    "        lat=region_code_dict[row['region_code']]['latitude']\n",
    "        long=region_code_dict[row['region_code']]['longitude']\n",
    "        return(lat,long)\n",
    "\n",
    "    except:\n",
    "        print(f'error for {row.name}')\n",
    "        return(row.latitude,row.longitude)\n",
    "print(guess_latlong(wwdf.loc[6091]))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 234,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:21:04.079991Z",
     "start_time": "2020-07-29T16:21:03.846569Z"
    }
   },
   "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>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6091</th>\n",
       "      <td>-2.000000e-08</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32376</th>\n",
       "      <td>-2.000000e-08</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72678</th>\n",
       "      <td>-2.000000e-08</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>56725</th>\n",
       "      <td>-2.000000e-08</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>13042</th>\n",
       "      <td>-2.000000e-08</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           latitude  longitude\n",
       "id                            \n",
       "6091  -2.000000e-08        0.0\n",
       "32376 -2.000000e-08        0.0\n",
       "72678 -2.000000e-08        0.0\n",
       "56725 -2.000000e-08        0.0\n",
       "13042 -2.000000e-08        0.0"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "data": {
      "text/html": [
       "<div>\n",
       "<style scoped>\n",
       "    .dataframe tbody tr th:only-of-type {\n",
       "        vertical-align: middle;\n",
       "    }\n",
       "\n",
       "    .dataframe tbody tr th {\n",
       "        vertical-align: top;\n",
       "    }\n",
       "\n",
       "    .dataframe thead th {\n",
       "        text-align: right;\n",
       "    }\n",
       "</style>\n",
       "<table border=\"1\" class=\"dataframe\">\n",
       "  <thead>\n",
       "    <tr style=\"text-align: right;\">\n",
       "      <th></th>\n",
       "      <th>latitude</th>\n",
       "      <th>longitude</th>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>id</th>\n",
       "      <th></th>\n",
       "      <th></th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>6091</th>\n",
       "      <td>-2.962381</td>\n",
       "      <td>34.179476</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>32376</th>\n",
       "      <td>-2.359152</td>\n",
       "      <td>34.535098</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>72678</th>\n",
       "      <td>-2.962381</td>\n",
       "      <td>34.179476</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "       latitude  longitude\n",
       "id                        \n",
       "6091  -2.962381  34.179476\n",
       "32376 -2.359152  34.535098\n",
       "72678 -2.962381  34.179476"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "checking latitude value counts now:\n",
      "-2.962381    696\n",
      "-2.620502    278\n",
      "-2.568506    164\n",
      "Name: latitude, dtype: int64\n"
     ]
    }
   ],
   "source": [
    "bad_vals=wwdf.loc[(wwdf['longitude']==0) | ((wwdf['latitude']==-2e-08)),['latitude','longitude']]\n",
    "display(bad_vals.head())\n",
    "new_val_series=wwdf.loc[(wwdf['longitude']==0) | ((wwdf['latitude']==-2e-08))].apply(lambda r: guess_latlong(r),axis=1)\n",
    "try:\n",
    "    new_vals=pd.DataFrame([[LAT,LONG] for LAT,LONG in new_val_series],columns=['latitude','longitude'],index=bad_vals.index)\n",
    "    wwdf.loc[(wwdf['longitude']==0) | ((wwdf['latitude']==-2e-08)),['latitude','longitude']]=new_vals\n",
    "except: \n",
    "    print('might be done already')\n",
    "display(new_vals.head(3))\n",
    "\n",
    "print('checking latitude value counts now:')\n",
    "print((wwdf.latitude.value_counts()).head(3))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 235,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:21:04.887304Z",
     "start_time": "2020-07-29T16:21:04.849821Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf['extraction_type']=wwdf['extraction_type'].apply(lambda s: s.replace('OTHER - ',''))\n",
    "#cleaning up the formatting"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Some time-intervals  are wrong, and are probably??? a result of an incorrect date_recorded, likely 2004 should be 2014?  Waiting for feedback from Alex though, for now just dropping them:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 243,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:24:45.147055Z",
     "start_time": "2020-07-29T16:24:45.138058Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf.loc[wwdf.time_interval<0,'time_interval']=np.nan"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:25:06.738337Z",
     "start_time": "2020-07-29T16:25:06.735105Z"
    }
   },
   "source": [
    "# One final look-over before exporting the data:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 246,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:25:08.132746Z",
     "start_time": "2020-07-29T16:25:07.155863Z"
    }
   },
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "amount_tsh: missing values: 0\n",
      "unique values:98\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0.0         0.700993\n",
       "500.0       0.052222\n",
       "50.0        0.041616\n",
       "1000.0      0.025051\n",
       "20.0        0.024630\n",
       "              ...   \n",
       "8500.0      0.000017\n",
       "6300.0      0.000017\n",
       "220.0       0.000017\n",
       "138000.0    0.000017\n",
       "12.0        0.000017\n",
       "Name: amount_tsh, Length: 98, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "funder: missing values: 3635\n",
      "unique values:1897\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GOVERNMENT OF TANZANIA    0.152929\n",
       "NaN                       0.061195\n",
       "DANIDA                    0.052424\n",
       "HESAWA                    0.037071\n",
       "RWSSP                     0.023131\n",
       "                            ...   \n",
       "KAHEMA                    0.000017\n",
       "MISRI GOVERNMENT          0.000017\n",
       "MSIKITINI                 0.000017\n",
       "MAKAPUCHINI               0.000017\n",
       "MATATA SELEMANI           0.000017\n",
       "Name: funder, Length: 1898, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "installer: missing values: 3655\n",
      "unique values:1935\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "DWE                         0.293013\n",
       "NaN                         0.061532\n",
       "GOVERNMENT                  0.031835\n",
       "HESAWA                      0.023485\n",
       "RWE                         0.020303\n",
       "                              ...   \n",
       "DAR ES SALAAM TECHNICIAN    0.000017\n",
       "UPENDO GROUP                0.000017\n",
       "AGRICAN                     0.000017\n",
       "CHACHA ISSAME               0.000017\n",
       "FAUSTINE                    0.000017\n",
       "Name: installer, Length: 1936, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "longitude: missing values: 0\n",
      "unique values:57706\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "34.179476    0.011717\n",
       "33.091564    0.004680\n",
       "33.282024    0.002761\n",
       "35.800014    0.001229\n",
       "34.163082    0.000337\n",
       "               ...   \n",
       "31.155207    0.000017\n",
       "38.905254    0.000017\n",
       "39.125162    0.000017\n",
       "36.694695    0.000017\n",
       "35.005922    0.000017\n",
       "Name: longitude, Length: 57706, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "latitude: missing values: 0\n",
      "unique values:57707\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "-2.962381    0.011717\n",
       "-2.620502    0.004680\n",
       "-2.568506    0.002761\n",
       "-4.653235    0.001229\n",
       "-7.117099    0.000337\n",
       "               ...   \n",
       "-3.487229    0.000017\n",
       "-4.759079    0.000017\n",
       "-7.832852    0.000017\n",
       "-3.238303    0.000017\n",
       "-2.598965    0.000017\n",
       "Name: latitude, Length: 57707, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "basin: missing values: 0\n",
      "unique values:9\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "LAKE VICTORIA              0.172525\n",
       "PANGANI                    0.150505\n",
       "RUFIJI                     0.134276\n",
       "INTERNAL                   0.131061\n",
       "LAKE TANGANYIKA            0.108283\n",
       "WAMI / RUVU                0.100791\n",
       "LAKE NYASA                 0.085606\n",
       "RUVUMA / SOUTHERN COAST    0.075640\n",
       "LAKE RUKWA                 0.041313\n",
       "Name: basin, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "subvillage: missing values: 371\n",
      "unique values:19287\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "MADUKANI      0.008552\n",
       "SHULENI       0.008519\n",
       "MAJENGO       0.008451\n",
       "KATI          0.006279\n",
       "NaN           0.006246\n",
       "                ...   \n",
       "KIGURUKA      0.000017\n",
       "MWAMBOKU      0.000017\n",
       "KINYIWANDA    0.000017\n",
       "NSING'OLE     0.000017\n",
       "MAKIWA        0.000017\n",
       "Name: subvillage, Length: 19288, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "region: missing values: 0\n",
      "unique values:21\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "IRINGA           0.089125\n",
       "SHINYANGA        0.083872\n",
       "MBEYA            0.078098\n",
       "KILIMANJARO      0.073721\n",
       "MOROGORO         0.067441\n",
       "ARUSHA           0.056397\n",
       "KAGERA           0.055825\n",
       "MWANZA           0.052222\n",
       "KIGOMA           0.047407\n",
       "RUVUMA           0.044444\n",
       "PWANI            0.044360\n",
       "TANGA            0.042879\n",
       "DODOMA           0.037054\n",
       "SINGIDA          0.035236\n",
       "MARA             0.033148\n",
       "TABORA           0.032980\n",
       "RUKWA            0.030438\n",
       "MTWARA           0.029125\n",
       "MANYARA          0.026650\n",
       "LINDI            0.026027\n",
       "DAR ES SALAAM    0.013552\n",
       "Name: region, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "region_code: missing values: 0\n",
      "unique values:27\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "11    0.089226\n",
       "17    0.084360\n",
       "12    0.078098\n",
       "3     0.073721\n",
       "5     0.068013\n",
       "18    0.055960\n",
       "19    0.051296\n",
       "2     0.050909\n",
       "16    0.047407\n",
       "10    0.044444\n",
       "4     0.042306\n",
       "1     0.037054\n",
       "13    0.035236\n",
       "14    0.033316\n",
       "20    0.033148\n",
       "15    0.030438\n",
       "6     0.027088\n",
       "21    0.026650\n",
       "80    0.020842\n",
       "60    0.017256\n",
       "90    0.015438\n",
       "7     0.013552\n",
       "99    0.007121\n",
       "9     0.006566\n",
       "24    0.005488\n",
       "8     0.005051\n",
       "40    0.000017\n",
       "Name: region_code, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "district_code: missing values: 0\n",
      "unique values:20\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1     0.205438\n",
       "2     0.188098\n",
       "3     0.168316\n",
       "4     0.151498\n",
       "5     0.073333\n",
       "6     0.068586\n",
       "7     0.056279\n",
       "8     0.017559\n",
       "30    0.016751\n",
       "33    0.014714\n",
       "53    0.012542\n",
       "43    0.008502\n",
       "13    0.006582\n",
       "23    0.004933\n",
       "63    0.003283\n",
       "62    0.001835\n",
       "60    0.001061\n",
       "0     0.000387\n",
       "80    0.000202\n",
       "67    0.000101\n",
       "Name: district_code, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "lga: missing values: 0\n",
      "unique values:125\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NJOMBE          0.042138\n",
       "ARUSHA RURAL    0.021077\n",
       "MOSHI RURAL     0.021061\n",
       "BARIADI         0.019815\n",
       "RUNGWE          0.018620\n",
       "                  ...   \n",
       "MOSHI URBAN     0.001330\n",
       "KIGOMA URBAN    0.001195\n",
       "ARUSHA URBAN    0.001061\n",
       "LINDI URBAN     0.000354\n",
       "NYAMAGANA       0.000017\n",
       "Name: lga, Length: 125, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "ward: missing values: 0\n",
      "unique values:2092\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "IGOSI               0.005168\n",
       "IMALINYI            0.004242\n",
       "SIHA KATI           0.003906\n",
       "MDANDU              0.003889\n",
       "NDURUMA             0.003653\n",
       "                      ...   \n",
       "MWANGA KASKAZINI    0.000017\n",
       "KIRONGO             0.000017\n",
       "KORONGONI           0.000017\n",
       "IZIA                0.000017\n",
       "MITOLE              0.000017\n",
       "Name: ward, Length: 2092, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "population: missing values: 28406\n",
      "unique values:1047\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NaN       0.478215\n",
       "200.0     0.032660\n",
       "150.0     0.031852\n",
       "250.0     0.028300\n",
       "300.0     0.024848\n",
       "            ...   \n",
       "491.0     0.000017\n",
       "807.0     0.000017\n",
       "948.0     0.000017\n",
       "1410.0    0.000017\n",
       "419.0     0.000017\n",
       "Name: population, Length: 1048, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "public_meeting: missing values: 3334\n",
      "unique values:2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1.0    0.858771\n",
       "0.0    0.085101\n",
       "NaN    0.056128\n",
       "Name: public_meeting, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "scheme_management: missing values: 3877\n",
      "unique values:12\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "VWC                 0.619411\n",
       "WUG                 0.087643\n",
       "NaN                 0.065269\n",
       "WATER AUTHORITY     0.053081\n",
       "WUA                 0.048535\n",
       "WATER BOARD         0.046263\n",
       "PARASTATAL          0.028283\n",
       "PRIVATE OPERATOR    0.017896\n",
       "COMPANY             0.017862\n",
       "OTHER               0.012896\n",
       "SWC                 0.001633\n",
       "TRUST               0.001212\n",
       "NONE                0.000017\n",
       "Name: scheme_management, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "scheme_name: missing values: 28166\n",
      "unique values:2576\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NaN                       0.474175\n",
       "K                         0.011532\n",
       "NONE                      0.011263\n",
       "BOREHOLE                  0.009192\n",
       "CHALINZE WATE             0.006835\n",
       "                            ...   \n",
       "LWAMGASA                  0.000017\n",
       "MAHUNGURU WATER SUPPLY    0.000017\n",
       "ARASHI WATER SCHEME       0.000017\n",
       "QUICK WINDS               0.000017\n",
       "SHIRIMATUNDA              0.000017\n",
       "Name: scheme_name, Length: 2577, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "permit: missing values: 3056\n",
      "unique values:2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "1.0    0.654074\n",
       "0.0    0.294478\n",
       "NaN    0.051448\n",
       "Name: permit, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "extraction_type: missing values: 0\n",
      "unique values:18\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GRAVITY              0.450842\n",
       "NIRA/TANIRA          0.137273\n",
       "OTHER                0.108249\n",
       "SUBMERSIBLE          0.080202\n",
       "SWN 80               0.061785\n",
       "MONO                 0.048232\n",
       "INDIA MARK II        0.040404\n",
       "AFRIDEV              0.029798\n",
       "KSB                  0.023822\n",
       "ROPE PUMP            0.007593\n",
       "SWN 81               0.003855\n",
       "WINDMILL             0.001970\n",
       "INDIA MARK III       0.001650\n",
       "CEMO                 0.001515\n",
       "PLAY PUMP            0.001431\n",
       "WALIMI               0.000808\n",
       "CLIMAX               0.000539\n",
       "MKULIMA/SHINYANGA    0.000034\n",
       "Name: extraction_type, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "extraction_type_group: missing values: 0\n",
      "unique values:13\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GRAVITY            0.450842\n",
       "NIRA/TANIRA        0.137273\n",
       "OTHER              0.108249\n",
       "SUBMERSIBLE        0.104024\n",
       "SWN 80             0.061785\n",
       "MONO               0.048232\n",
       "INDIA MARK II      0.040404\n",
       "AFRIDEV            0.029798\n",
       "ROPE PUMP          0.007593\n",
       "OTHER HANDPUMP     0.006128\n",
       "OTHER MOTORPUMP    0.002054\n",
       "WIND-POWERED       0.001970\n",
       "INDIA MARK III     0.001650\n",
       "Name: extraction_type_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "extraction_type_class: missing values: 0\n",
      "unique values:7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GRAVITY         0.450842\n",
       "HANDPUMP        0.277037\n",
       "OTHER           0.108249\n",
       "SUBMERSIBLE     0.104024\n",
       "MOTORPUMP       0.050286\n",
       "ROPE PUMP       0.007593\n",
       "WIND-POWERED    0.001970\n",
       "Name: extraction_type_class, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "management: missing values: 0\n",
      "unique values:12\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "VWC                 0.681936\n",
       "WUG                 0.109680\n",
       "WATER BOARD         0.049377\n",
       "WUA                 0.042677\n",
       "PRIVATE OPERATOR    0.033182\n",
       "PARASTATAL          0.029764\n",
       "WATER AUTHORITY     0.015219\n",
       "OTHER               0.014209\n",
       "COMPANY             0.011532\n",
       "UNKNOWN             0.009444\n",
       "OTHER - SCHOOL      0.001667\n",
       "TRUST               0.001313\n",
       "Name: management, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "management_group: missing values: 0\n",
      "unique values:5\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "USER-GROUP    0.883670\n",
       "COMMERCIAL    0.061246\n",
       "PARASTATAL    0.029764\n",
       "OTHER         0.015875\n",
       "UNKNOWN       0.009444\n",
       "Name: management_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "payment: missing values: 0\n",
      "unique values:7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NEVER PAY                0.426734\n",
       "PAY PER BUCKET           0.151263\n",
       "PAY MONTHLY              0.139731\n",
       "UNKNOWN                  0.137323\n",
       "PAY WHEN SCHEME FAILS    0.065892\n",
       "PAY ANNUALLY             0.061313\n",
       "OTHER                    0.017744\n",
       "Name: payment, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "payment_type: missing values: 0\n",
      "unique values:7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NEVER PAY     0.426734\n",
       "PER BUCKET    0.151263\n",
       "MONTHLY       0.139731\n",
       "UNKNOWN       0.137323\n",
       "ON FAILURE    0.065892\n",
       "ANNUALLY      0.061313\n",
       "OTHER         0.017744\n",
       "Name: payment_type, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "water_quality: missing values: 0\n",
      "unique values:8\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "SOFT                  0.855522\n",
       "SALTY                 0.081751\n",
       "UNKNOWN               0.031582\n",
       "MILKY                 0.013535\n",
       "COLOURED              0.008249\n",
       "SALTY ABANDONED       0.005707\n",
       "FLUORIDE              0.003367\n",
       "FLUORIDE ABANDONED    0.000286\n",
       "Name: water_quality, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "quality_group: missing values: 0\n",
      "unique values:6\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GOOD        0.855522\n",
       "SALTY       0.087458\n",
       "UNKNOWN     0.031582\n",
       "MILKY       0.013535\n",
       "COLORED     0.008249\n",
       "FLUORIDE    0.003653\n",
       "Name: quality_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "quantity: missing values: 0\n",
      "unique values:5\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ENOUGH          0.558687\n",
       "INSUFFICIENT    0.254697\n",
       "DRY             0.105152\n",
       "SEASONAL        0.068182\n",
       "UNKNOWN         0.013283\n",
       "Name: quantity, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "quantity_group: missing values: 0\n",
      "unique values:5\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "ENOUGH          0.558687\n",
       "INSUFFICIENT    0.254697\n",
       "DRY             0.105152\n",
       "SEASONAL        0.068182\n",
       "UNKNOWN         0.013283\n",
       "Name: quantity_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "source: missing values: 0\n",
      "unique values:10\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "SPRING                  0.286549\n",
       "SHALLOW WELL            0.283232\n",
       "MACHINE DBH             0.186448\n",
       "RIVER                   0.161818\n",
       "RAINWATER HARVESTING    0.038636\n",
       "HAND DTW                0.014714\n",
       "LAKE                    0.012879\n",
       "DAM                     0.011044\n",
       "OTHER                   0.003569\n",
       "UNKNOWN                 0.001111\n",
       "Name: source, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "source_type: missing values: 0\n",
      "unique values:7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "SPRING                  0.286549\n",
       "SHALLOW WELL            0.283232\n",
       "BOREHOLE                0.201162\n",
       "RIVER/LAKE              0.174697\n",
       "RAINWATER HARVESTING    0.038636\n",
       "DAM                     0.011044\n",
       "OTHER                   0.004680\n",
       "Name: source_type, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "source_class: missing values: 0\n",
      "unique values:3\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "GROUNDWATER    0.770943\n",
       "SURFACE        0.224377\n",
       "UNKNOWN        0.004680\n",
       "Name: source_class, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "waterpoint_type: missing values: 0\n",
      "unique values:7\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "COMMUNAL STANDPIPE             0.480168\n",
       "HAND PUMP                      0.294411\n",
       "OTHER                          0.107407\n",
       "COMMUNAL STANDPIPE MULTIPLE    0.102744\n",
       "IMPROVED SPRING                0.013199\n",
       "CATTLE TROUGH                  0.001953\n",
       "DAM                            0.000118\n",
       "Name: waterpoint_type, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "waterpoint_type_group: missing values: 0\n",
      "unique values:6\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "COMMUNAL STANDPIPE    0.582912\n",
       "HAND PUMP             0.294411\n",
       "OTHER                 0.107407\n",
       "IMPROVED SPRING       0.013199\n",
       "CATTLE TROUGH         0.001953\n",
       "DAM                   0.000118\n",
       "Name: waterpoint_type_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "status_group: missing values: 0\n",
      "unique values:3\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "FUNCTIONAL                 0.543081\n",
       "NON FUNCTIONAL             0.384242\n",
       "FUNCTIONAL NEEDS REPAIR    0.072677\n",
       "Name: status_group, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "elevation: missing values: 0\n",
      "unique values:20691\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       " 1347.148193    0.011751\n",
       " 1194.566162    0.004680\n",
       " 1140.341553    0.002761\n",
       " 1667.332275    0.001229\n",
       "-15.000000      0.001010\n",
       "                  ...   \n",
       " 1225.338013    0.000017\n",
       " 1230.672363    0.000017\n",
       " 995.706848     0.000017\n",
       " 1230.680664    0.000017\n",
       " 1213.228027    0.000017\n",
       "Name: elevation, Length: 20691, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "time_interval: missing values: 20718\n",
      "unique values:54\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "NaN     0.348788\n",
       "3.0     0.046128\n",
       "1.0     0.038771\n",
       "2.0     0.035842\n",
       "5.0     0.033333\n",
       "4.0     0.031818\n",
       "13.0    0.031465\n",
       "7.0     0.023636\n",
       "6.0     0.023249\n",
       "11.0    0.022761\n",
       "8.0     0.019529\n",
       "14.0    0.019529\n",
       "33.0    0.018855\n",
       "15.0    0.016717\n",
       "23.0    0.015236\n",
       "10.0    0.014613\n",
       "16.0    0.013906\n",
       "9.0     0.013704\n",
       "19.0    0.012896\n",
       "27.0    0.012828\n",
       "18.0    0.012694\n",
       "31.0    0.011936\n",
       "28.0    0.011936\n",
       "35.0    0.010892\n",
       "17.0    0.010707\n",
       "39.0    0.010118\n",
       "37.0    0.010101\n",
       "26.0    0.009916\n",
       "0.0     0.009899\n",
       "25.0    0.009815\n",
       "21.0    0.009731\n",
       "29.0    0.009495\n",
       "12.0    0.009411\n",
       "20.0    0.009108\n",
       "41.0    0.008215\n",
       "30.0    0.006044\n",
       "43.0    0.005993\n",
       "38.0    0.005909\n",
       "36.0    0.005471\n",
       "22.0    0.005438\n",
       "24.0    0.004394\n",
       "40.0    0.003923\n",
       "34.0    0.003098\n",
       "32.0    0.002929\n",
       "53.0    0.001532\n",
       "42.0    0.001515\n",
       "50.0    0.001414\n",
       "45.0    0.001111\n",
       "46.0    0.000808\n",
       "44.0    0.000791\n",
       "48.0    0.000690\n",
       "51.0    0.000522\n",
       "49.0    0.000421\n",
       "47.0    0.000236\n",
       "52.0    0.000185\n",
       "Name: time_interval, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "water_quality_character: missing values: 0\n",
      "unique values:6\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "SOFT        0.855522\n",
       "SALTY       0.087458\n",
       "UNKNOWN     0.031582\n",
       "MILKY       0.013535\n",
       "COLOURED    0.008249\n",
       "FLUORIDE    0.003653\n",
       "Name: water_quality_character, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n",
      "water_quality_abandoned: missing values: 0\n",
      "unique values:2\n"
     ]
    },
    {
     "data": {
      "text/plain": [
       "0    0.994007\n",
       "1    0.005993\n",
       "Name: water_quality_abandoned, dtype: float64"
      ]
     },
     "metadata": {},
     "output_type": "display_data"
    },
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "------------------------------\n"
     ]
    }
   ],
   "source": [
    "for c in wwdf:\n",
    "    print(f'{c}: missing values: {wwdf[c].isna().sum()}\\nunique values:{wwdf[c].nunique()}')\n",
    "    display(wwdf[c].value_counts(normalize=True,dropna=False))\n",
    "    print('---'*10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 249,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:27:04.151118Z",
     "start_time": "2020-07-29T16:27:02.866216Z"
    }
   },
   "outputs": [],
   "source": [
    "wwdf.to_csv('cleaned_data_072920_shaw.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 250,
   "metadata": {
    "ExecuteTime": {
     "end_time": "2020-07-29T16:27:18.308793Z",
     "start_time": "2020-07-29T16:27:17.760593Z"
    }
   },
   "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>id</th>\n",
       "      <th>amount_tsh</th>\n",
       "      <th>funder</th>\n",
       "      <th>installer</th>\n",
       "      <th>longitude</th>\n",
       "      <th>latitude</th>\n",
       "      <th>basin</th>\n",
       "      <th>subvillage</th>\n",
       "      <th>region</th>\n",
       "      <th>region_code</th>\n",
       "      <th>...</th>\n",
       "      <th>source</th>\n",
       "      <th>source_type</th>\n",
       "      <th>source_class</th>\n",
       "      <th>waterpoint_type</th>\n",
       "      <th>waterpoint_type_group</th>\n",
       "      <th>status_group</th>\n",
       "      <th>elevation</th>\n",
       "      <th>time_interval</th>\n",
       "      <th>water_quality_character</th>\n",
       "      <th>water_quality_abandoned</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>69572</td>\n",
       "      <td>6000.0</td>\n",
       "      <td>ROMAN</td>\n",
       "      <td>ROMAN</td>\n",
       "      <td>34.938093</td>\n",
       "      <td>-9.856322</td>\n",
       "      <td>LAKE NYASA</td>\n",
       "      <td>MNYUSI B</td>\n",
       "      <td>IRINGA</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>SPRING</td>\n",
       "      <td>SPRING</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1390.000000</td>\n",
       "      <td>12.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>8776</td>\n",
       "      <td>0.0</td>\n",
       "      <td>GRUMETI</td>\n",
       "      <td>GRUMETI</td>\n",
       "      <td>34.698766</td>\n",
       "      <td>-2.147466</td>\n",
       "      <td>LAKE VICTORIA</td>\n",
       "      <td>NYAMARA</td>\n",
       "      <td>MARA</td>\n",
       "      <td>20</td>\n",
       "      <td>...</td>\n",
       "      <td>RAINWATER HARVESTING</td>\n",
       "      <td>RAINWATER HARVESTING</td>\n",
       "      <td>SURFACE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1399.000000</td>\n",
       "      <td>3.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>34310</td>\n",
       "      <td>25.0</td>\n",
       "      <td>LOTTERY CLUB</td>\n",
       "      <td>WORLD VISION</td>\n",
       "      <td>37.460664</td>\n",
       "      <td>-3.821329</td>\n",
       "      <td>PANGANI</td>\n",
       "      <td>MAJENGO</td>\n",
       "      <td>MANYARA</td>\n",
       "      <td>21</td>\n",
       "      <td>...</td>\n",
       "      <td>DAM</td>\n",
       "      <td>DAM</td>\n",
       "      <td>SURFACE</td>\n",
       "      <td>COMMUNAL STANDPIPE MULTIPLE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>686.000000</td>\n",
       "      <td>4.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>67743</td>\n",
       "      <td>0.0</td>\n",
       "      <td>UNICEF</td>\n",
       "      <td>UNICEF</td>\n",
       "      <td>38.486161</td>\n",
       "      <td>-11.155298</td>\n",
       "      <td>RUVUMA / SOUTHERN COAST</td>\n",
       "      <td>MAHAKAMANI</td>\n",
       "      <td>MTWARA</td>\n",
       "      <td>90</td>\n",
       "      <td>...</td>\n",
       "      <td>MACHINE DBH</td>\n",
       "      <td>BOREHOLE</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>COMMUNAL STANDPIPE MULTIPLE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>NON FUNCTIONAL</td>\n",
       "      <td>263.000000</td>\n",
       "      <td>27.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>19728</td>\n",
       "      <td>0.0</td>\n",
       "      <td>ACTION IN A</td>\n",
       "      <td>ARTISAN</td>\n",
       "      <td>31.130847</td>\n",
       "      <td>-1.825359</td>\n",
       "      <td>LAKE VICTORIA</td>\n",
       "      <td>KYANYAMISA</td>\n",
       "      <td>KAGERA</td>\n",
       "      <td>18</td>\n",
       "      <td>...</td>\n",
       "      <td>RAINWATER HARVESTING</td>\n",
       "      <td>RAINWATER HARVESTING</td>\n",
       "      <td>SURFACE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1671.699341</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59395</th>\n",
       "      <td>60739</td>\n",
       "      <td>10.0</td>\n",
       "      <td>GERMANY REPUBLI</td>\n",
       "      <td>CES</td>\n",
       "      <td>37.169807</td>\n",
       "      <td>-3.253847</td>\n",
       "      <td>PANGANI</td>\n",
       "      <td>KIDURUNI</td>\n",
       "      <td>KILIMANJARO</td>\n",
       "      <td>3</td>\n",
       "      <td>...</td>\n",
       "      <td>SPRING</td>\n",
       "      <td>SPRING</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1210.000000</td>\n",
       "      <td>14.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59396</th>\n",
       "      <td>27263</td>\n",
       "      <td>4700.0</td>\n",
       "      <td>CEFA-NJOMBE</td>\n",
       "      <td>CEFA</td>\n",
       "      <td>35.249991</td>\n",
       "      <td>-9.070629</td>\n",
       "      <td>RUFIJI</td>\n",
       "      <td>IGUMBILO</td>\n",
       "      <td>IRINGA</td>\n",
       "      <td>11</td>\n",
       "      <td>...</td>\n",
       "      <td>RIVER</td>\n",
       "      <td>RIVER/LAKE</td>\n",
       "      <td>SURFACE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>COMMUNAL STANDPIPE</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1212.000000</td>\n",
       "      <td>15.0</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59397</th>\n",
       "      <td>37057</td>\n",
       "      <td>0.0</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.017087</td>\n",
       "      <td>-8.750434</td>\n",
       "      <td>RUFIJI</td>\n",
       "      <td>MADUNGULU</td>\n",
       "      <td>MBEYA</td>\n",
       "      <td>12</td>\n",
       "      <td>...</td>\n",
       "      <td>MACHINE DBH</td>\n",
       "      <td>BOREHOLE</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1069.052612</td>\n",
       "      <td>NaN</td>\n",
       "      <td>FLUORIDE</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59398</th>\n",
       "      <td>31282</td>\n",
       "      <td>0.0</td>\n",
       "      <td>MALEC</td>\n",
       "      <td>MUSA</td>\n",
       "      <td>35.861315</td>\n",
       "      <td>-6.378573</td>\n",
       "      <td>RUFIJI</td>\n",
       "      <td>MWINYI</td>\n",
       "      <td>DODOMA</td>\n",
       "      <td>1</td>\n",
       "      <td>...</td>\n",
       "      <td>SHALLOW WELL</td>\n",
       "      <td>SHALLOW WELL</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>1053.703613</td>\n",
       "      <td>NaN</td>\n",
       "      <td>SOFT</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>59399</th>\n",
       "      <td>26348</td>\n",
       "      <td>0.0</td>\n",
       "      <td>WORLD BANK</td>\n",
       "      <td>WORLD</td>\n",
       "      <td>38.104048</td>\n",
       "      <td>-6.747464</td>\n",
       "      <td>WAMI / RUVU</td>\n",
       "      <td>KIKATANYEMBA</td>\n",
       "      <td>MOROGORO</td>\n",
       "      <td>5</td>\n",
       "      <td>...</td>\n",
       "      <td>SHALLOW WELL</td>\n",
       "      <td>SHALLOW WELL</td>\n",
       "      <td>GROUNDWATER</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>HAND PUMP</td>\n",
       "      <td>FUNCTIONAL</td>\n",
       "      <td>191.000000</td>\n",
       "      <td>9.0</td>\n",
       "      <td>SALTY</td>\n",
       "      <td>0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>59400 rows × 39 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          id  amount_tsh           funder     installer  longitude   latitude  \\\n",
       "0      69572      6000.0            ROMAN         ROMAN  34.938093  -9.856322   \n",
       "1       8776         0.0          GRUMETI       GRUMETI  34.698766  -2.147466   \n",
       "2      34310        25.0     LOTTERY CLUB  WORLD VISION  37.460664  -3.821329   \n",
       "3      67743         0.0           UNICEF        UNICEF  38.486161 -11.155298   \n",
       "4      19728         0.0      ACTION IN A       ARTISAN  31.130847  -1.825359   \n",
       "...      ...         ...              ...           ...        ...        ...   \n",
       "59395  60739        10.0  GERMANY REPUBLI           CES  37.169807  -3.253847   \n",
       "59396  27263      4700.0      CEFA-NJOMBE          CEFA  35.249991  -9.070629   \n",
       "59397  37057         0.0              NaN           NaN  34.017087  -8.750434   \n",
       "59398  31282         0.0            MALEC          MUSA  35.861315  -6.378573   \n",
       "59399  26348         0.0       WORLD BANK         WORLD  38.104048  -6.747464   \n",
       "\n",
       "                         basin    subvillage       region  region_code  ...  \\\n",
       "0                   LAKE NYASA      MNYUSI B       IRINGA           11  ...   \n",
       "1                LAKE VICTORIA       NYAMARA         MARA           20  ...   \n",
       "2                      PANGANI       MAJENGO      MANYARA           21  ...   \n",
       "3      RUVUMA / SOUTHERN COAST    MAHAKAMANI       MTWARA           90  ...   \n",
       "4                LAKE VICTORIA    KYANYAMISA       KAGERA           18  ...   \n",
       "...                        ...           ...          ...          ...  ...   \n",
       "59395                  PANGANI      KIDURUNI  KILIMANJARO            3  ...   \n",
       "59396                   RUFIJI      IGUMBILO       IRINGA           11  ...   \n",
       "59397                   RUFIJI     MADUNGULU        MBEYA           12  ...   \n",
       "59398                   RUFIJI        MWINYI       DODOMA            1  ...   \n",
       "59399              WAMI / RUVU  KIKATANYEMBA     MOROGORO            5  ...   \n",
       "\n",
       "                     source           source_type source_class  \\\n",
       "0                    SPRING                SPRING  GROUNDWATER   \n",
       "1      RAINWATER HARVESTING  RAINWATER HARVESTING      SURFACE   \n",
       "2                       DAM                   DAM      SURFACE   \n",
       "3               MACHINE DBH              BOREHOLE  GROUNDWATER   \n",
       "4      RAINWATER HARVESTING  RAINWATER HARVESTING      SURFACE   \n",
       "...                     ...                   ...          ...   \n",
       "59395                SPRING                SPRING  GROUNDWATER   \n",
       "59396                 RIVER            RIVER/LAKE      SURFACE   \n",
       "59397           MACHINE DBH              BOREHOLE  GROUNDWATER   \n",
       "59398          SHALLOW WELL          SHALLOW WELL  GROUNDWATER   \n",
       "59399          SHALLOW WELL          SHALLOW WELL  GROUNDWATER   \n",
       "\n",
       "                   waterpoint_type  waterpoint_type_group    status_group  \\\n",
       "0               COMMUNAL STANDPIPE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "1               COMMUNAL STANDPIPE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "2      COMMUNAL STANDPIPE MULTIPLE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "3      COMMUNAL STANDPIPE MULTIPLE     COMMUNAL STANDPIPE  NON FUNCTIONAL   \n",
       "4               COMMUNAL STANDPIPE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "...                            ...                    ...             ...   \n",
       "59395           COMMUNAL STANDPIPE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "59396           COMMUNAL STANDPIPE     COMMUNAL STANDPIPE      FUNCTIONAL   \n",
       "59397                    HAND PUMP              HAND PUMP      FUNCTIONAL   \n",
       "59398                    HAND PUMP              HAND PUMP      FUNCTIONAL   \n",
       "59399                    HAND PUMP              HAND PUMP      FUNCTIONAL   \n",
       "\n",
       "         elevation  time_interval water_quality_character  \\\n",
       "0      1390.000000           12.0                    SOFT   \n",
       "1      1399.000000            3.0                    SOFT   \n",
       "2       686.000000            4.0                    SOFT   \n",
       "3       263.000000           27.0                    SOFT   \n",
       "4      1671.699341            NaN                    SOFT   \n",
       "...            ...            ...                     ...   \n",
       "59395  1210.000000           14.0                    SOFT   \n",
       "59396  1212.000000           15.0                    SOFT   \n",
       "59397  1069.052612            NaN                FLUORIDE   \n",
       "59398  1053.703613            NaN                    SOFT   \n",
       "59399   191.000000            9.0                   SALTY   \n",
       "\n",
       "      water_quality_abandoned  \n",
       "0                           0  \n",
       "1                           0  \n",
       "2                           0  \n",
       "3                           0  \n",
       "4                           0  \n",
       "...                       ...  \n",
       "59395                       0  \n",
       "59396                       0  \n",
       "59397                       0  \n",
       "59398                       0  \n",
       "59399                       0  \n",
       "\n",
       "[59400 rows x 39 columns]"
      ]
     },
     "execution_count": 250,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.read_csv('cleaned_data_072920_shaw.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.8.3"
  },
  "toc": {
   "base_numbering": 1,
   "nav_menu": {},
   "number_sections": true,
   "sideBar": true,
   "skip_h1_title": false,
   "title_cell": "Table of Contents",
   "title_sidebar": "Contents",
   "toc_cell": false,
   "toc_position": {},
   "toc_section_display": true,
   "toc_window_display": false
  },
  "varInspector": {
   "cols": {
    "lenName": 16,
    "lenType": 16,
    "lenVar": 40
   },
   "kernels_config": {
    "python": {
     "delete_cmd_postfix": "",
     "delete_cmd_prefix": "del ",
     "library": "var_list.py",
     "varRefreshCmd": "print(var_dic_list())"
    },
    "r": {
     "delete_cmd_postfix": ") ",
     "delete_cmd_prefix": "rm(",
     "library": "var_list.r",
     "varRefreshCmd": "cat(var_dic_list()) "
    }
   },
   "types_to_exclude": [
    "module",
    "function",
    "builtin_function_or_method",
    "instance",
    "_Feature"
   ],
   "window_display": false
  }
 },
 "nbformat": 4,
 "nbformat_minor": 4
}