data_preprocessing .ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 151,
   "id": "8ccad49a",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 152,
   "id": "1b590970",
   "metadata": {},
   "outputs": [],
   "source": [
    "def preprocess_common(data,test,n):\n",
    "    data = data.drop(columns = ['date_recorded','gps_height','wpt_name','num_private','subvillage'\n",
    "                                ,'lga','ward','recorded_by','extraction_type','management', 'management_group', 'payment' , 'quality_group', 'quantity' , 'source_type',  'waterpoint_type_group'])\n",
    "    test = test.drop(columns = ['date_recorded','gps_height','wpt_name','num_private','subvillage'\n",
    "                                ,'lga','ward','recorded_by','extraction_type','management', 'management_group', 'payment' , 'quality_group', 'quantity' , 'source_type',  'waterpoint_type_group'])\n",
    "\n",
    "    data.permit = data.permit.astype(bool).astype(int)\n",
    "    data.public_meeting = data.public_meeting.astype(bool).astype(int)\n",
    "    test.permit = test.permit.astype(bool).astype(int)\n",
    "    test.public_meeting = test.public_meeting.astype(bool).astype(int)\n",
    "    \n",
    "    def get_n(x,x_test,n):\n",
    "    \n",
    "        list_a = set(x.value_counts().index[:n].values) #Gets the top 10 values from the series\n",
    "        x[~x.isin(list_a)] = 'Other' #vectorisation - boolean mask; tilda - inverting the boolean mask\n",
    "        x_test[~x_test.isin(list_a)] = 'Other'\n",
    "        return x,x_test\n",
    " \n",
    "    data.funder,test.funder = get_n(data.funder,test.funder,n)\n",
    "    data.installer,test.installer = get_n(data.installer,test.installer,n) \n",
    "    data.scheme_management,test.scheme_management = get_n(data.scheme_management,test.scheme_management,n) \n",
    "    \n",
    "    def replace_with_median(data,test):\n",
    "        region_median = data.groupby(['region_code'])[['latitude', 'longitude']].median()\n",
    "        def geo_update(row, df_geo):\n",
    "            row['longitude'] = df_geo.loc[row['region_code']]['longitude']\n",
    "            row['latitude'] = df_geo.loc[row['region_code']]['latitude']\n",
    "            return row\n",
    "\n",
    "        data.loc[data['longitude']==0, ['longitude', 'latitude']] = \\\n",
    "            data[data['longitude']==0].apply(\n",
    "                geo_update, df_geo=region_median, axis=1)[['longitude', 'latitude']]\n",
    "        \n",
    "        test.loc[test['longitude']==0, ['longitude', 'latitude']] = \\\n",
    "            test[test['longitude']==0].apply(\n",
    "                geo_update, df_geo=region_median, axis=1)[['longitude', 'latitude']]\n",
    "        return data,test\n",
    "    \n",
    "    data,test = replace_with_median(data,test)\n",
    "    \n",
    "    def replace_with_mode(data,test):\n",
    "        df_scheme = data.groupby(['region'])['scheme_name'].agg(pd.Series.mode)\n",
    "\n",
    "        def scheme_update(row, df_scheme):\n",
    "            row['scheme_name'] = df_scheme[row['region']]\n",
    "            return row\n",
    "\n",
    "        data.loc[data['scheme_name'].isnull(), ['scheme_name']] = \\\n",
    "            data[data['scheme_name'].isnull()].apply(\n",
    "                scheme_update, df_scheme=df_scheme, axis=1)[['scheme_name']]\n",
    "        \n",
    "        test.loc[test['scheme_name'].isnull(), ['scheme_name']] = \\\n",
    "            test[test['scheme_name'].isnull()].apply(\n",
    "                scheme_update, df_scheme=df_scheme, axis=1)[['scheme_name']]\n",
    "        return data,test\n",
    "    \n",
    "    data,test = replace_with_mode(data,test)\n",
    "    data.scheme_name,test.scheme_name = get_n(data.scheme_name,test.scheme_name,n)\n",
    "   \n",
    "    data = data.drop(columns = ['region'])\n",
    "    test = test.drop(columns = ['region'])\n",
    "    return data,test\n",
    "\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 153,
   "id": "7c7ef722",
   "metadata": {},
   "outputs": [],
   "source": [
    "train_X = pd.read_csv(\n",
    "    \"data/train_feature.csv\",\n",
    "    index_col=\"id\"\n",
    ")\n",
    "\n",
    "train_Y = pd.read_csv(\n",
    "    \"data/train_labels.csv\",\n",
    "    index_col=\"id\"\n",
    ")\n",
    "\n",
    "test = pd.read_csv(\n",
    "    \"data/test.csv\",\n",
    "    index_col=\"id\"\n",
    ")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 154,
   "id": "fd9f5371",
   "metadata": {},
   "outputs": [],
   "source": [
    "def oneHotEncode(data,test,cat_columns):\n",
    "    data = pd.get_dummies(data,columns = cat_columns)\n",
    "    test = pd.get_dummies(test,columns = cat_columns)\n",
    "    return data, test"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 155,
   "id": "9b7f9e4f",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 155,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(set(train_X.columns) - set(test.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 156,
   "id": "965c5ebb",
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-152-a22458b1298d>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  x[~x.isin(list_a)] = 'Other' #vectorisation - boolean mask; tilda - inverting the boolean mask\n",
      "<ipython-input-152-a22458b1298d>:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  x_test[~x_test.isin(list_a)] = 'Other'\n",
      "<ipython-input-152-a22458b1298d>:15: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  x[~x.isin(list_a)] = 'Other' #vectorisation - boolean mask; tilda - inverting the boolean mask\n",
      "<ipython-input-152-a22458b1298d>:16: SettingWithCopyWarning: \n",
      "A value is trying to be set on a copy of a slice from a DataFrame\n",
      "\n",
      "See the caveats in the documentation: https://pandas.pydata.org/pandas-docs/stable/user_guide/indexing.html#returning-a-view-versus-a-copy\n",
      "  x_test[~x_test.isin(list_a)] = 'Other'\n"
     ]
    }
   ],
   "source": [
    "P_train_X, P_test = preprocess_common(train_X,test,10)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 157,
   "id": "ceb599ab",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 157,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(set(train_X.columns) - set(test.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 158,
   "id": "7d76c565",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['amount_tsh', 'funder', 'installer', 'longitude', 'latitude', 'basin',\n",
      "       'region_code', 'district_code', 'population', 'public_meeting',\n",
      "       'scheme_management', 'scheme_name', 'permit', 'construction_year',\n",
      "       'extraction_type_group', 'extraction_type_class', 'payment_type',\n",
      "       'water_quality', 'quantity_group', 'source', 'source_class',\n",
      "       'waterpoint_type'],\n",
      "      dtype='object')\n",
      "22\n"
     ]
    }
   ],
   "source": [
    "print(P_train_X.columns)\n",
    "print(len(P_train_X.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 159,
   "id": "80ff9c8f",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['amount_tsh', 'funder', 'installer', 'longitude', 'latitude', 'basin',\n",
      "       'region_code', 'district_code', 'population', 'public_meeting',\n",
      "       'scheme_management', 'scheme_name', 'permit', 'construction_year',\n",
      "       'extraction_type_group', 'extraction_type_class', 'payment_type',\n",
      "       'water_quality', 'quantity_group', 'source', 'source_class',\n",
      "       'waterpoint_type'],\n",
      "      dtype='object')\n",
      "22\n"
     ]
    }
   ],
   "source": [
    "print(P_test.columns)\n",
    "print(len(P_test.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 160,
   "id": "d00118a3",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['funder', 'installer', 'basin', 'scheme_management', 'scheme_name', 'extraction_type_group', 'extraction_type_class', 'payment_type', 'water_quality', 'quantity_group', 'source', 'source_class', 'waterpoint_type']\n",
      "13\n"
     ]
    }
   ],
   "source": [
    "cat_features=[i for i in P_train_X.columns if P_train_X.dtypes[i]=='object']\n",
    "print(cat_features)\n",
    "print(len(cat_features))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 161,
   "id": "69fd0442",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "['funder', 'installer', 'basin', 'scheme_management', 'scheme_name', 'extraction_type_group', 'extraction_type_class', 'payment_type', 'water_quality', 'quantity_group', 'source', 'source_class', 'waterpoint_type']\n",
      "13\n"
     ]
    }
   ],
   "source": [
    "cat_features=[i for i in P_test.columns if P_test.dtypes[i]=='object']\n",
    "print(cat_features)\n",
    "print(len(cat_features))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 162,
   "id": "0703c38c",
   "metadata": {},
   "outputs": [],
   "source": [
    "# P_train_X, P_test = oneHotEncode(P_train_X, P_test,cat_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 163,
   "id": "bcd3ea55",
   "metadata": {},
   "outputs": [],
   "source": [
    "# P_train_X.to_csv(\"cleaned_1_train.csv\",index=False)\n",
    "# P_test.to_csv(\"cleaned_1_test.csv\",index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 164,
   "id": "8165ad99",
   "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>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>region_code</th>\n",
       "      <th>district_code</th>\n",
       "      <th>population</th>\n",
       "      <th>public_meeting</th>\n",
       "      <th>scheme_management</th>\n",
       "      <th>scheme_name</th>\n",
       "      <th>permit</th>\n",
       "      <th>construction_year</th>\n",
       "      <th>extraction_type_group</th>\n",
       "      <th>extraction_type_class</th>\n",
       "      <th>payment_type</th>\n",
       "      <th>water_quality</th>\n",
       "      <th>quantity_group</th>\n",
       "      <th>source</th>\n",
       "      <th>source_class</th>\n",
       "      <th>waterpoint_type</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>count</th>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400.000000</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "      <td>59400</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>unique</th>\n",
       "      <td>NaN</td>\n",
       "      <td>11</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>9</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10</td>\n",
       "      <td>11</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>13</td>\n",
       "      <td>7</td>\n",
       "      <td>7</td>\n",
       "      <td>8</td>\n",
       "      <td>5</td>\n",
       "      <td>10</td>\n",
       "      <td>3</td>\n",
       "      <td>7</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>top</th>\n",
       "      <td>NaN</td>\n",
       "      <td>Other</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>Lake Victoria</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>VWC</td>\n",
       "      <td>Other</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>gravity</td>\n",
       "      <td>gravity</td>\n",
       "      <td>never pay</td>\n",
       "      <td>soft</td>\n",
       "      <td>enough</td>\n",
       "      <td>spring</td>\n",
       "      <td>groundwater</td>\n",
       "      <td>communal standpipe</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>freq</th>\n",
       "      <td>NaN</td>\n",
       "      <td>36967</td>\n",
       "      <td>33013</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>10248</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>36793</td>\n",
       "      <td>33831</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>26780</td>\n",
       "      <td>26780</td>\n",
       "      <td>25348</td>\n",
       "      <td>50818</td>\n",
       "      <td>33186</td>\n",
       "      <td>17021</td>\n",
       "      <td>45794</td>\n",
       "      <td>28522</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>mean</th>\n",
       "      <td>317.650385</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>35.084525</td>\n",
       "      <td>-5.797934</td>\n",
       "      <td>NaN</td>\n",
       "      <td>15.297003</td>\n",
       "      <td>5.629747</td>\n",
       "      <td>179.909983</td>\n",
       "      <td>0.914899</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.705522</td>\n",
       "      <td>1300.652475</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",
       "      <th>std</th>\n",
       "      <td>2997.574558</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2.593514</td>\n",
       "      <td>2.811667</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.587406</td>\n",
       "      <td>9.633649</td>\n",
       "      <td>471.482176</td>\n",
       "      <td>0.279034</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.455812</td>\n",
       "      <td>951.620547</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",
       "      <th>min</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>29.607122</td>\n",
       "      <td>-11.649440</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</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",
       "      <th>25%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>33.091138</td>\n",
       "      <td>-8.541226</td>\n",
       "      <td>NaN</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>2.000000</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>0.000000</td>\n",
       "      <td>0.000000</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",
       "      <th>50%</th>\n",
       "      <td>0.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>34.908743</td>\n",
       "      <td>-5.022018</td>\n",
       "      <td>NaN</td>\n",
       "      <td>12.000000</td>\n",
       "      <td>3.000000</td>\n",
       "      <td>25.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>1986.000000</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",
       "      <th>75%</th>\n",
       "      <td>20.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>37.178387</td>\n",
       "      <td>-3.342792</td>\n",
       "      <td>NaN</td>\n",
       "      <td>17.000000</td>\n",
       "      <td>5.000000</td>\n",
       "      <td>215.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2004.000000</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",
       "      <th>max</th>\n",
       "      <td>350000.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>40.345193</td>\n",
       "      <td>-0.998464</td>\n",
       "      <td>NaN</td>\n",
       "      <td>99.000000</td>\n",
       "      <td>80.000000</td>\n",
       "      <td>30500.000000</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>NaN</td>\n",
       "      <td>NaN</td>\n",
       "      <td>1.000000</td>\n",
       "      <td>2013.000000</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",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "           amount_tsh funder installer     longitude      latitude  \\\n",
       "count    59400.000000  59400     59400  59400.000000  59400.000000   \n",
       "unique            NaN     11        11           NaN           NaN   \n",
       "top               NaN  Other     Other           NaN           NaN   \n",
       "freq              NaN  36967     33013           NaN           NaN   \n",
       "mean       317.650385    NaN       NaN     35.084525     -5.797934   \n",
       "std       2997.574558    NaN       NaN      2.593514      2.811667   \n",
       "min          0.000000    NaN       NaN     29.607122    -11.649440   \n",
       "25%          0.000000    NaN       NaN     33.091138     -8.541226   \n",
       "50%          0.000000    NaN       NaN     34.908743     -5.022018   \n",
       "75%         20.000000    NaN       NaN     37.178387     -3.342792   \n",
       "max     350000.000000    NaN       NaN     40.345193     -0.998464   \n",
       "\n",
       "                basin   region_code  district_code    population  \\\n",
       "count           59400  59400.000000   59400.000000  59400.000000   \n",
       "unique              9           NaN            NaN           NaN   \n",
       "top     Lake Victoria           NaN            NaN           NaN   \n",
       "freq            10248           NaN            NaN           NaN   \n",
       "mean              NaN     15.297003       5.629747    179.909983   \n",
       "std               NaN     17.587406       9.633649    471.482176   \n",
       "min               NaN      1.000000       0.000000      0.000000   \n",
       "25%               NaN      5.000000       2.000000      0.000000   \n",
       "50%               NaN     12.000000       3.000000     25.000000   \n",
       "75%               NaN     17.000000       5.000000    215.000000   \n",
       "max               NaN     99.000000      80.000000  30500.000000   \n",
       "\n",
       "        public_meeting scheme_management scheme_name        permit  \\\n",
       "count     59400.000000             59400       59400  59400.000000   \n",
       "unique             NaN                10          11           NaN   \n",
       "top                NaN               VWC       Other           NaN   \n",
       "freq               NaN             36793       33831           NaN   \n",
       "mean          0.914899               NaN         NaN      0.705522   \n",
       "std           0.279034               NaN         NaN      0.455812   \n",
       "min           0.000000               NaN         NaN      0.000000   \n",
       "25%           1.000000               NaN         NaN      0.000000   \n",
       "50%           1.000000               NaN         NaN      1.000000   \n",
       "75%           1.000000               NaN         NaN      1.000000   \n",
       "max           1.000000               NaN         NaN      1.000000   \n",
       "\n",
       "        construction_year extraction_type_group extraction_type_class  \\\n",
       "count        59400.000000                 59400                 59400   \n",
       "unique                NaN                    13                     7   \n",
       "top                   NaN               gravity               gravity   \n",
       "freq                  NaN                 26780                 26780   \n",
       "mean          1300.652475                   NaN                   NaN   \n",
       "std            951.620547                   NaN                   NaN   \n",
       "min              0.000000                   NaN                   NaN   \n",
       "25%              0.000000                   NaN                   NaN   \n",
       "50%           1986.000000                   NaN                   NaN   \n",
       "75%           2004.000000                   NaN                   NaN   \n",
       "max           2013.000000                   NaN                   NaN   \n",
       "\n",
       "       payment_type water_quality quantity_group  source source_class  \\\n",
       "count         59400         59400          59400   59400        59400   \n",
       "unique            7             8              5      10            3   \n",
       "top       never pay          soft         enough  spring  groundwater   \n",
       "freq          25348         50818          33186   17021        45794   \n",
       "mean            NaN           NaN            NaN     NaN          NaN   \n",
       "std             NaN           NaN            NaN     NaN          NaN   \n",
       "min             NaN           NaN            NaN     NaN          NaN   \n",
       "25%             NaN           NaN            NaN     NaN          NaN   \n",
       "50%             NaN           NaN            NaN     NaN          NaN   \n",
       "75%             NaN           NaN            NaN     NaN          NaN   \n",
       "max             NaN           NaN            NaN     NaN          NaN   \n",
       "\n",
       "           waterpoint_type  \n",
       "count                59400  \n",
       "unique                   7  \n",
       "top     communal standpipe  \n",
       "freq                 28522  \n",
       "mean                   NaN  \n",
       "std                    NaN  \n",
       "min                    NaN  \n",
       "25%                    NaN  \n",
       "50%                    NaN  \n",
       "75%                    NaN  \n",
       "max                    NaN  "
      ]
     },
     "execution_count": 164,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "pd.options.display.max_columns = 40\n",
    "P_train_X.describe(include='all')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 165,
   "id": "20aab8b5",
   "metadata": {},
   "outputs": [],
   "source": [
    "P_train_X, P_test = oneHotEncode(P_train_X, P_test,cat_features)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 166,
   "id": "28102363",
   "metadata": {},
   "outputs": [],
   "source": [
    "# P_train_X.to_csv(\"data/cleaned_1_train.csv\")\n",
    "# P_test.to_csv(\"data/cleaned_1_test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 167,
   "id": "578d56f8",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['amount_tsh', 'longitude', 'latitude', 'region_code', 'district_code',\n",
      "       'population', 'public_meeting', 'permit', 'construction_year',\n",
      "       'funder_Danida',\n",
      "       ...\n",
      "       'source_class_groundwater', 'source_class_surface',\n",
      "       'source_class_unknown', 'waterpoint_type_cattle trough',\n",
      "       'waterpoint_type_communal standpipe',\n",
      "       'waterpoint_type_communal standpipe multiple', 'waterpoint_type_dam',\n",
      "       'waterpoint_type_hand pump', 'waterpoint_type_improved spring',\n",
      "       'waterpoint_type_other'],\n",
      "      dtype='object', length=121)\n",
      "121\n"
     ]
    }
   ],
   "source": [
    "print(P_train_X.columns)\n",
    "print(len(P_train_X.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 168,
   "id": "4d639617",
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Index(['amount_tsh', 'longitude', 'latitude', 'region_code', 'district_code',\n",
      "       'population', 'public_meeting', 'permit', 'construction_year',\n",
      "       'funder_Danida',\n",
      "       ...\n",
      "       'source_class_groundwater', 'source_class_surface',\n",
      "       'source_class_unknown', 'waterpoint_type_cattle trough',\n",
      "       'waterpoint_type_communal standpipe',\n",
      "       'waterpoint_type_communal standpipe multiple', 'waterpoint_type_dam',\n",
      "       'waterpoint_type_hand pump', 'waterpoint_type_improved spring',\n",
      "       'waterpoint_type_other'],\n",
      "      dtype='object', length=121)\n",
      "121\n"
     ]
    }
   ],
   "source": [
    "print(P_test.columns)\n",
    "print(len(P_test.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 169,
   "id": "01f5288d",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[]"
      ]
     },
     "execution_count": 169,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "list(set(P_train_X.columns) - set(P_test.columns))"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 171,
   "id": "01214fe5",
   "metadata": {},
   "outputs": [],
   "source": [
    "P_train_X.to_csv(\"data/cleaned_1_train.csv\")\n",
    "P_test.to_csv(\"data/cleaned_1_test.csv\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "8a1a5cc9",
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}