2 - Data cleaning - Limpieza de datos.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd\n",
    "# Importo OS solo para ver la lista de archivos de data\n",
    "import os"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [],
   "source": [
    "def eliminar_filas_invalidas(df):\n",
    "\n",
    "    filas=[]\n",
    "\n",
    "    for i in df.index:\n",
    "\n",
    "        try:\n",
    "            float(df.iloc[i][\"Cierre\"])\n",
    "        \n",
    "        except:\n",
    "            print(i)\n",
    "            filas.append(i)\n",
    "\n",
    "    df.drop(filas, axis=0,inplace=True)\n",
    "    df.reset_index(drop=True)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "def formatear_fecha(fecha):\n",
    "\n",
    "    dict_meses = {\n",
    "        \"ene\":\"01\",\n",
    "        \"feb\":\"02\",\n",
    "        \"mar\":\"03\",\n",
    "        \"abr\":\"04\",\n",
    "        \"may\":\"05\",\n",
    "        \"jun\":\"06\",\n",
    "        \"jul\":\"07\",\n",
    "        \"ago\":\"08\",\n",
    "        \"sept\":\"09\",\n",
    "        \"oct\":\"10\",\n",
    "        \"nov\":\"11\",\n",
    "        \"dic\":\"12\"\n",
    "    }\n",
    "\n",
    "    return fecha.replace(fecha.split(\" \")[1], dict_meses[fecha.split(\" \")[1]])"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "def main(archivo, indice):\n",
    "    df = pd.read_csv(f\"./data/{indice}/{archivo}\")\n",
    "\n",
    "    #Renombro  las columnas para que sean más accesibles\n",
    "    df.rename(columns = {\"Abrir\": \"Apertura\", \"Máx.\": \"Max\",\"Mín.\":\"Min\",\"Cierre*\":\"Cierre\"}, inplace=True)\n",
    "\n",
    "    #Elimino la columna Cierre ajus.** ya que está duplicada\n",
    "    df.drop(columns=[\"Cierre ajus.**\"], inplace=True)\n",
    "\n",
    "    #Elimino la última fila que no trae información\n",
    "    df.drop(df.index[-1], axis=0,inplace=True)\n",
    "\n",
    "    df[\"Indice\"] = np.full(df.shape[0], fill_value=indice)\n",
    "\n",
    "    df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\",\"Volumen\"]] = (df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\",\"Volumen\"]].applymap(lambda x : x.replace(\".\",\"\").replace(\",\",\"\")))\n",
    "\n",
    "    eliminar_filas_invalidas(df)\n",
    "\n",
    "    df[\"Volumen\"] = df[\"Volumen\"].replace(\"-\",\"0\")\n",
    "\n",
    "    df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\",\"Volumen\"]] = df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\",\"Volumen\"]].astype(float)\n",
    "    df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\"]] = df[[\"Apertura\", \"Max\", \"Min\", \"Cierre\"]]/100\n",
    "\n",
    "    df[\"Cambio\"] = df[\"Cierre\"] - df[\"Apertura\"]\n",
    "    df[\"Cambio%\"] = round(df[\"Cambio\"]*100 / df[\"Apertura\"],3)\n",
    "\n",
    "    df[\"Fecha\"] = df[\"Fecha\"].map(lambda x : formatear_fecha(x))\n",
    "    df[\"Fecha\"] = df[\"Fecha\"].map(lambda x : pd.to_datetime(x, format='%d %m %Y')).astype(\"datetime64[ns]\")\n",
    "\n",
    "    df[\"weekday\"] = df[\"Fecha\"].map(lambda x : x.strftime('%A'))\n",
    "    df[\"month\"] = df[\"Fecha\"].map(lambda x : x.strftime('%B'))\n",
    "    df[\"year\"] = df[\"Fecha\"].map(lambda x : x.year )\n",
    "\n",
    "    return df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "./data\n",
      "Airtable\n",
      "ATX\n",
      "BFX\n",
      "BVSP\n",
      "CASE30\n",
      "DJI\n",
      "FCHI\n",
      "GDAXI\n",
      "GSPC\n",
      "HSI\n",
      "IBEX\n",
      "INDC.MC\n",
      "INDD.MC\n",
      "INDS.MC\n",
      "IPSA\n",
      "IXIC\n",
      "JKSE\n",
      "JN0U.JO\n",
      "KS11\n",
      "MXX\n",
      "N100\n",
      "N225\n",
      "NZ50\n",
      "OMXC20\n",
      "OMXSPI\n",
      "OSEAX\n",
      "SSMI\n",
      "STOXX50E\n",
      "TA125.TA\n",
      "TWII\n"
     ]
    }
   ],
   "source": [
    "dict_ficheros = {carpeta.split(\"\\\\\")[-1]:file for carpeta, subcarpeta, file in os.walk(\"./data\") if file!=[]}\n",
    "\n",
    "for carpeta in list(dict_ficheros.keys()):\n",
    "\n",
    "    if carpeta ==\"./data\" or carpeta == \"Airtable\": continue\n",
    "        \n",
    "    for archivo in dict_ficheros[carpeta]:\n",
    "        print(archivo)    \n",
    "        \n",
    "        if archivo.find(\"componentes\") != -1:\n",
    "            continue\n",
    "\n",
    "        print(carpeta)   \n",
    "        print(archivo)    \n",
    "        df = main(archivo,carpeta)\n",
    "        df.to_csv(f'./data/{carpeta}/{archivo}', index=False) \n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "{}\n"
     ]
    }
   ],
   "source": [
    "#Elimino las carpetas vacias\n",
    "carpetas_vacias  = {carpeta.split(\"\\\\\")[-1]:file for carpeta, subcarpeta, file in os.walk(\"./data\") if file==[] }\n",
    "print(carpetas_vacias)\n",
    "\n",
    "for carpeta in carpetas_vacias:\n",
    "\n",
    "    if carpeta ==\"./data\": continue\n",
    "    \n",
    "    os.removedirs(f\"./data/{carpeta}\")\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.9.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}