data-modeling.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "8541a641-222e-431a-8ebc-7dbb84919495",
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import os\n",
    "\n",
    "from datetime import date\n",
    "from pathlib import Path"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "6e41ad52-e4a6-484e-a35d-ef61770c9dd9",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "[PosixPath('data/20240107_products.csv'),\n",
       " PosixPath('data/20240128_products.csv'),\n",
       " PosixPath('data/20240626_products.csv'),\n",
       " PosixPath('data/20240225_products.csv'),\n",
       " PosixPath('data/20240407_products.csv'),\n",
       " PosixPath('data/20240508_products.csv'),\n",
       " PosixPath('data/20240403_products.csv'),\n",
       " PosixPath('data/20240221_products.csv'),\n",
       " PosixPath('data/20240324_products.csv'),\n",
       " PosixPath('data/20240306_products.csv'),\n",
       " PosixPath('data/20240124_products.csv'),\n",
       " PosixPath('data/20231231_products.csv'),\n",
       " PosixPath('data/20240121_products.csv'),\n",
       " PosixPath('data/20240512_products.csv'),\n",
       " PosixPath('data/20240417_products.csv'),\n",
       " PosixPath('data/20240707_products.csv'),\n",
       " PosixPath('data/20240519_products.csv'),\n",
       " PosixPath('data/20240117_products.csv'),\n",
       " PosixPath('data/20240623_products.csv'),\n",
       " PosixPath('data/20240214_products.csv'),\n",
       " PosixPath('data/20240605_products.csv'),\n",
       " PosixPath('data/20240630_products.csv'),\n",
       " PosixPath('data/20240616_products.csv'),\n",
       " PosixPath('data/20240714_products.csv'),\n",
       " PosixPath('data/20240529_products.csv'),\n",
       " PosixPath('data/20240515_products.csv'),\n",
       " PosixPath('data/20240104_products.csv'),\n",
       " PosixPath('data/20240505_products.csv'),\n",
       " PosixPath('data/20240410_products.csv'),\n",
       " PosixPath('data/20240421_products.csv'),\n",
       " PosixPath('data/20240612_products.csv'),\n",
       " PosixPath('data/20240313_products.csv'),\n",
       " PosixPath('data/20240131_products.csv'),\n",
       " PosixPath('data/20240114_products.csv'),\n",
       " PosixPath('data/20240228_products.csv'),\n",
       " PosixPath('data/20240207_products.csv'),\n",
       " PosixPath('data/20240721_products.csv'),\n",
       " PosixPath('data/20240710_products.csv'),\n",
       " PosixPath('data/20240522_products.csv'),\n",
       " PosixPath('data/20240602_products.csv'),\n",
       " PosixPath('data/20240211_products.csv'),\n",
       " PosixPath('data/20240609_products.csv'),\n",
       " PosixPath('data/20240414_products.csv'),\n",
       " PosixPath('data/20240619_products.csv'),\n",
       " PosixPath('data/20240331_products.csv'),\n",
       " PosixPath('data/20240320_products.csv'),\n",
       " PosixPath('data/20240703_products.csv'),\n",
       " PosixPath('data/20240111_products.csv'),\n",
       " PosixPath('data/20240327_products.csv'),\n",
       " PosixPath('data/20240303_products.csv'),\n",
       " PosixPath('data/20240218_products.csv'),\n",
       " PosixPath('data/20240310_products.csv'),\n",
       " PosixPath('data/20240526_products.csv'),\n",
       " PosixPath('data/20240317_products.csv'),\n",
       " PosixPath('data/20240424_products.csv'),\n",
       " PosixPath('data/20240501_products.csv'),\n",
       " PosixPath('data/20240717_products.csv'),\n",
       " PosixPath('data/20240204_products.csv'),\n",
       " PosixPath('data/20240428_products.csv')]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "path = Path(\"data\")\n",
    "entries = [path / entry for entry in os.listdir(path) if entry.endswith(\".csv\")]\n",
    "list(entries)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "38bc0df4-b3ed-4b5b-b8bf-9cebdf699a80",
   "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>id</th>\n",
       "      <th>code</th>\n",
       "      <th>brand</th>\n",
       "      <th>name</th>\n",
       "      <th>2024-04-28</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>2715</th>\n",
       "      <td>17948</td>\n",
       "      <td>104829</td>\n",
       "      <td>ENERGIZER ARGENTINA</td>\n",
       "      <td>ENERGIZER PILA AAA MAX X4U</td>\n",
       "      <td>3399.0</td>\n",
       "      <td>PILAS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2716</th>\n",
       "      <td>141</td>\n",
       "      <td>1429</td>\n",
       "      <td>A BALANZA</td>\n",
       "      <td>MILANESA DE CERDO XKG</td>\n",
       "      <td>6699.0</td>\n",
       "      <td>PREPARADOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2717</th>\n",
       "      <td>69</td>\n",
       "      <td>127</td>\n",
       "      <td>A BALANZA</td>\n",
       "      <td>MILANESAS DE CARNE XKG</td>\n",
       "      <td>7499.0</td>\n",
       "      <td>PREPARADOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2718</th>\n",
       "      <td>70</td>\n",
       "      <td>128</td>\n",
       "      <td>A BALANZA</td>\n",
       "      <td>MUSLO DE POLLO REBOZADO XKG</td>\n",
       "      <td>4999.0</td>\n",
       "      <td>PREPARADOS</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2719</th>\n",
       "      <td>61</td>\n",
       "      <td>106</td>\n",
       "      <td>A BALANZA</td>\n",
       "      <td>SUPREMA DE POLLO REBOZADA XKG</td>\n",
       "      <td>5499.0</td>\n",
       "      <td>PREPARADOS</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id    code                brand                           name  \\\n",
       "2715  17948  104829  ENERGIZER ARGENTINA     ENERGIZER PILA AAA MAX X4U   \n",
       "2716    141    1429            A BALANZA          MILANESA DE CERDO XKG   \n",
       "2717     69     127            A BALANZA         MILANESAS DE CARNE XKG   \n",
       "2718     70     128            A BALANZA    MUSLO DE POLLO REBOZADO XKG   \n",
       "2719     61     106            A BALANZA  SUPREMA DE POLLO REBOZADA XKG   \n",
       "\n",
       "      2024-04-28    category  \n",
       "2715      3399.0       PILAS  \n",
       "2716      6699.0  PREPARADOS  \n",
       "2717      7499.0  PREPARADOS  \n",
       "2718      4999.0  PREPARADOS  \n",
       "2719      5499.0  PREPARADOS  "
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "def load_dataset(csv_path):\n",
    "    df = pd.read_csv(csv_path)\n",
    "    filename = csv_path.name\n",
    "    dataset_date = date(int(filename[0:4]), int(filename[4:6]), int(filename[6:8]))\n",
    "    column_name_mapping = dict(\n",
    "        codigo=\"code\",\n",
    "        marca=\"brand\",\n",
    "        nombre=\"name\",\n",
    "        precio=dataset_date,\n",
    "        categoria=\"category\",\n",
    "        )\n",
    "    return df.rename(columns=column_name_mapping)\n",
    "\n",
    "df = load_dataset(entries[-1])\n",
    "\n",
    "df.tail()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "d5ea162b-d930-4922-9567-097fc52696ac",
   "metadata": {
    "scrolled": true
   },
   "outputs": [
    {
     "data": {
      "text/plain": [
       "code\n",
       "117140    2\n",
       "110560    2\n",
       "Name: count, dtype: int64"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "duplicated_codes = df.code.value_counts()[lambda count: count > 1]\n",
    "duplicated_codes"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "10fd4a7f-3e75-4c81-85ba-edef0d59c19e",
   "metadata": {
    "scrolled": true
   },
   "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>code</th>\n",
       "      <th>brand</th>\n",
       "      <th>name</th>\n",
       "      <th>2024-04-28</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>334</th>\n",
       "      <td>4902</td>\n",
       "      <td>110560</td>\n",
       "      <td>EL ABASTECEDOR</td>\n",
       "      <td>CEREALITAS TOSTADAS ARROZ X160</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>GALLETAS / TOSTADAS / GRISINES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>335</th>\n",
       "      <td>1475</td>\n",
       "      <td>110560</td>\n",
       "      <td>EL ABASTECEDOR</td>\n",
       "      <td>CEREALITAS TOSTADAS ARROZ X160</td>\n",
       "      <td>1725.0</td>\n",
       "      <td>GALLETAS / TOSTADAS / GRISINES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2470</th>\n",
       "      <td>10413</td>\n",
       "      <td>117140</td>\n",
       "      <td>PRECIOS JUSTOS</td>\n",
       "      <td>GLADE AROMATIZANTE L/V RPTO 175G</td>\n",
       "      <td>6105.0</td>\n",
       "      <td>DESODORANTES DE AMBIENTES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2471</th>\n",
       "      <td>2946</td>\n",
       "      <td>117140</td>\n",
       "      <td>PRECIOS JUSTOS</td>\n",
       "      <td>GLADE AROMATIZANTE L/V RPTO 175G</td>\n",
       "      <td>6105.0</td>\n",
       "      <td>DESODORANTES DE AMBIENTES</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "         id    code           brand                              name  \\\n",
       "334    4902  110560  EL ABASTECEDOR    CEREALITAS TOSTADAS ARROZ X160   \n",
       "335    1475  110560  EL ABASTECEDOR    CEREALITAS TOSTADAS ARROZ X160   \n",
       "2470  10413  117140  PRECIOS JUSTOS  GLADE AROMATIZANTE L/V RPTO 175G   \n",
       "2471   2946  117140  PRECIOS JUSTOS  GLADE AROMATIZANTE L/V RPTO 175G   \n",
       "\n",
       "      2024-04-28                        category  \n",
       "334       1725.0  GALLETAS / TOSTADAS / GRISINES  \n",
       "335       1725.0  GALLETAS / TOSTADAS / GRISINES  \n",
       "2470      6105.0       DESODORANTES DE AMBIENTES  \n",
       "2471      6105.0       DESODORANTES DE AMBIENTES  "
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df[lambda item: item.code.isin(duplicated_codes.index)]"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "id": "b852eeed-8486-4269-b171-ba10fd6f03dc",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                          ACEITES\n",
       "1                         ADEREZOS\n",
       "2              APTOS PARA CELIACOS\n",
       "3                          ARROCES\n",
       "4                           AZUCAR\n",
       "                  ...             \n",
       "131    LIMPIEZA DE PISOS Y MUEBLES\n",
       "132         LIMPIEZA DEL AUTOMOTOR\n",
       "133                        PAPELES\n",
       "134                          PILAS\n",
       "135                     PREPARADOS\n",
       "Name: category, Length: 136, dtype: object"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "categories = df.category.drop_duplicates().reset_index(drop=True)\n",
    "categories"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "f76584fb-ee44-4616-9f4b-742962e9cc09",
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "0                MOLINO CAÃUELAS S.A.\n",
       "1                       PRECIOS JUSTOS\n",
       "2                 MOLINOS RIO DE LA PL\n",
       "3                       EL ABASTECEDOR\n",
       "4                 ACEITERA GENERAL DEH\n",
       "                    ...               \n",
       "193                           LIMPIEZA\n",
       "194                          ROLL PACK\n",
       "195               TEXTIL CATAMARCA S.A\n",
       "196                           DROF SRL\n",
       "197    PAPELERA SAN ANDRES DE GILES SA\n",
       "Name: brand, Length: 198, dtype: object"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "brands = df.brand.drop_duplicates().reset_index(drop=True)\n",
    "brands"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 10,
   "id": "051b17ae-881b-4bff-a48e-97b65015ea61",
   "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>code</th>\n",
       "      <th>name</th>\n",
       "      <th>brand</th>\n",
       "      <th>category</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>114746</td>\n",
       "      <td>CAÃUELAS AC.OLI.INTEN.LATA.X500C</td>\n",
       "      <td>MOLINO CAÃUELAS S.A.</td>\n",
       "      <td>ACEITES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>112222</td>\n",
       "      <td>CAÃUELAS AC.OLIVA E/VIR.PETX500</td>\n",
       "      <td>MOLINO CAÃUELAS S.A.</td>\n",
       "      <td>ACEITES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>112537</td>\n",
       "      <td>CAÃUELAS ACEITE OLIVA E/VIR LATAX500</td>\n",
       "      <td>MOLINO CAÃUELAS S.A.</td>\n",
       "      <td>ACEITES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>103100</td>\n",
       "      <td>CAÃUELAS ACEITE GIRASOL PET X1.5L</td>\n",
       "      <td>PRECIOS JUSTOS</td>\n",
       "      <td>ACEITES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>103684</td>\n",
       "      <td>CAÃUELAS ACEITE GIRASOL PET X900CC</td>\n",
       "      <td>PRECIOS JUSTOS</td>\n",
       "      <td>ACEITES</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2715</th>\n",
       "      <td>102136</td>\n",
       "      <td>WASSINGTON POM.LIQ.NEGRA X60G</td>\n",
       "      <td>WASSINGTON S.A.C.I.F</td>\n",
       "      <td>CALZADO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2716</th>\n",
       "      <td>120600</td>\n",
       "      <td>WASSINGTON POM.MARR X65G</td>\n",
       "      <td>WASSINGTON S.A.C.I.F</td>\n",
       "      <td>CALZADO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2717</th>\n",
       "      <td>102139</td>\n",
       "      <td>WASSINGTON POM.MARRON X30GR</td>\n",
       "      <td>WASSINGTON S.A.C.I.F</td>\n",
       "      <td>CALZADO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2718</th>\n",
       "      <td>120599</td>\n",
       "      <td>WASSINGTON POM.NEGRA CALZ.X60G</td>\n",
       "      <td>WASSINGTON S.A.C.I.F</td>\n",
       "      <td>CALZADO</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2719</th>\n",
       "      <td>102140</td>\n",
       "      <td>WASSINGTON POM.NEGRA X30GR</td>\n",
       "      <td>WASSINGTON S.A.C.I.F</td>\n",
       "      <td>CALZADO</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>2720 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "        code                                   name                  brand  \\\n",
       "0     114746      CAÃUELAS AC.OLI.INTEN.LATA.X500C  MOLINO CAÃUELAS S.A.   \n",
       "1     112222       CAÃUELAS AC.OLIVA E/VIR.PETX500  MOLINO CAÃUELAS S.A.   \n",
       "2     112537  CAÃUELAS ACEITE OLIVA E/VIR LATAX500  MOLINO CAÃUELAS S.A.   \n",
       "3     103100     CAÃUELAS ACEITE GIRASOL PET X1.5L         PRECIOS JUSTOS   \n",
       "4     103684    CAÃUELAS ACEITE GIRASOL PET X900CC         PRECIOS JUSTOS   \n",
       "...      ...                                    ...                    ...   \n",
       "2715  102136          WASSINGTON POM.LIQ.NEGRA X60G   WASSINGTON S.A.C.I.F   \n",
       "2716  120600               WASSINGTON POM.MARR X65G   WASSINGTON S.A.C.I.F   \n",
       "2717  102139            WASSINGTON POM.MARRON X30GR   WASSINGTON S.A.C.I.F   \n",
       "2718  120599         WASSINGTON POM.NEGRA CALZ.X60G   WASSINGTON S.A.C.I.F   \n",
       "2719  102140             WASSINGTON POM.NEGRA X30GR   WASSINGTON S.A.C.I.F   \n",
       "\n",
       "     category  \n",
       "0     ACEITES  \n",
       "1     ACEITES  \n",
       "2     ACEITES  \n",
       "3     ACEITES  \n",
       "4     ACEITES  \n",
       "...       ...  \n",
       "2715  CALZADO  \n",
       "2716  CALZADO  \n",
       "2717  CALZADO  \n",
       "2718  CALZADO  \n",
       "2719  CALZADO  \n",
       "\n",
       "[2720 rows x 4 columns]"
      ]
     },
     "execution_count": 10,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "products = df[[\"code\", \"name\", \"brand\", \"category\"]]\n",
    "products"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3 (ipykernel)",
   "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}