2_data_enricher.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 52,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Se agrega el GDP al dataset de provincias"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 53,
   "metadata": {},
   "outputs": [],
   "source": [
    "provinces_gdp = pd.read_csv(\"context_datasets/provinces_gdp.csv\", sep = \";\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 54,
   "metadata": {},
   "outputs": [],
   "source": [
    "# se renombra la columna de la provincia para que coincida con la columna de la tabla a hacer el merge\n",
    "provinces_gdp.rename(columns = {\"name\": \"cus_province\"}, inplace = True)\n",
    "# para evitar problemas, se eliminan los espacios en blanco de la columna de la provincia\n",
    "provinces_gdp[\"cus_province\"] = provinces_gdp[\"cus_province\"].str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 55,
   "metadata": {},
   "outputs": [],
   "source": [
    "provinces_gdp[\"cus_province\"] = provinces_gdp[\"cus_province\"].str.replace(\" \", \"_\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 56,
   "metadata": {},
   "outputs": [],
   "source": [
    "# se lee la base a hacer el merge\n",
    "customers = pd.read_csv(\"clean_datasets/customers.csv\", sep = \";\")\n",
    "# para evitar problemas, se eliminan los espacios en blanco de la columna de la provincia\n",
    "customers[\"cus_province\"] = customers[\"cus_province\"].str.replace(\" \", \"_\").str.strip()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 57,
   "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>cust_id</th>\n",
       "      <th>cripto_acc_status</th>\n",
       "      <th>cripto_acc_created_at</th>\n",
       "      <th>cust_sex</th>\n",
       "      <th>cus_province</th>\n",
       "      <th>cust_member_since</th>\n",
       "      <th>cus_country</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>192047</td>\n",
       "      <td>disabled</td>\n",
       "      <td>2021-12-16 11:31:37</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2020-02-20 11:43:17</td>\n",
       "      <td>UNDEFINED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>236745</td>\n",
       "      <td>disabled</td>\n",
       "      <td>2021-12-21 14:11:58</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>santa_catarina</td>\n",
       "      <td>2021-03-10 20:17:28</td>\n",
       "      <td>BRA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>146948</td>\n",
       "      <td>disabled</td>\n",
       "      <td>2021-12-22 15:56:23</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-09-19 13:00:32</td>\n",
       "      <td>UNDEFINED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>139853</td>\n",
       "      <td>disabled</td>\n",
       "      <td>2021-12-12 17:42:13</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2018-06-21 23:49:51</td>\n",
       "      <td>UNDEFINED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>230446</td>\n",
       "      <td>disabled</td>\n",
       "      <td>2021-12-14 12:18:26</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2021-01-04 13:18:16</td>\n",
       "      <td>UNDEFINED</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",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61385</th>\n",
       "      <td>173129</td>\n",
       "      <td>investing</td>\n",
       "      <td>2021-12-27 19:12:54</td>\n",
       "      <td>male</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2019-08-04 23:55:38</td>\n",
       "      <td>UNDEFINED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61386</th>\n",
       "      <td>77677</td>\n",
       "      <td>investing</td>\n",
       "      <td>2021-12-27 12:32:52</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>NaN</td>\n",
       "      <td>2016-01-22 16:27:08</td>\n",
       "      <td>UNDEFINED</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61387</th>\n",
       "      <td>196424</td>\n",
       "      <td>investing</td>\n",
       "      <td>2021-12-27 07:57:10</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>minas_gerais</td>\n",
       "      <td>2020-04-12 15:36:16</td>\n",
       "      <td>BRA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61388</th>\n",
       "      <td>157363</td>\n",
       "      <td>investing</td>\n",
       "      <td>2021-12-26 21:29:35</td>\n",
       "      <td>male</td>\n",
       "      <td>são_paulo</td>\n",
       "      <td>2019-01-28 20:34:53</td>\n",
       "      <td>BRA</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>61389</th>\n",
       "      <td>59535</td>\n",
       "      <td>investing</td>\n",
       "      <td>2021-12-27 18:58:40</td>\n",
       "      <td>unspecified</td>\n",
       "      <td>são_paulo</td>\n",
       "      <td>2015-01-05 13:37:57</td>\n",
       "      <td>BRA</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>61390 rows × 7 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "       cust_id cripto_acc_status cripto_acc_created_at     cust_sex  \\\n",
       "0       192047          disabled   2021-12-16 11:31:37         male   \n",
       "1       236745          disabled   2021-12-21 14:11:58  unspecified   \n",
       "2       146948          disabled   2021-12-22 15:56:23  unspecified   \n",
       "3       139853          disabled   2021-12-12 17:42:13  unspecified   \n",
       "4       230446          disabled   2021-12-14 12:18:26  unspecified   \n",
       "...        ...               ...                   ...          ...   \n",
       "61385   173129         investing   2021-12-27 19:12:54         male   \n",
       "61386    77677         investing   2021-12-27 12:32:52  unspecified   \n",
       "61387   196424         investing   2021-12-27 07:57:10  unspecified   \n",
       "61388   157363         investing   2021-12-26 21:29:35         male   \n",
       "61389    59535         investing   2021-12-27 18:58:40  unspecified   \n",
       "\n",
       "         cus_province    cust_member_since cus_country  \n",
       "0                 NaN  2020-02-20 11:43:17   UNDEFINED  \n",
       "1      santa_catarina  2021-03-10 20:17:28         BRA  \n",
       "2                 NaN  2018-09-19 13:00:32   UNDEFINED  \n",
       "3                 NaN  2018-06-21 23:49:51   UNDEFINED  \n",
       "4                 NaN  2021-01-04 13:18:16   UNDEFINED  \n",
       "...               ...                  ...         ...  \n",
       "61385             NaN  2019-08-04 23:55:38   UNDEFINED  \n",
       "61386             NaN  2016-01-22 16:27:08   UNDEFINED  \n",
       "61387    minas_gerais  2020-04-12 15:36:16         BRA  \n",
       "61388       são_paulo  2019-01-28 20:34:53         BRA  \n",
       "61389       são_paulo  2015-01-05 13:37:57         BRA  \n",
       "\n",
       "[61390 rows x 7 columns]"
      ]
     },
     "execution_count": 57,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "customers"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "### Se hace un join para agregar la columna de GDP al dataset de clientes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "metadata": {},
   "outputs": [],
   "source": [
    "## join customers and provinces_gdp on cus_province so that customers can be enriched with gdp data\n",
    "customers = pd.merge(customers, provinces_gdp, on = \"cus_province\", how = \"left\")"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 59,
   "metadata": {},
   "outputs": [],
   "source": [
    "customers.to_csv(\"clean_datasets\\\\customers.csv\", sep = \";\", index = False)"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3.9.7 64-bit",
   "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.9.7"
  },
  "orig_nbformat": 4,
  "vscode": {
   "interpreter": {
    "hash": "db0e05d799ad150ff6f95b13eef15f09568f87966873a3b36fbb1aeeee34a4e4"
   }
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}