Calcular Costos de Tamaño LAP.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# **Calcular Costos de Tamaño**"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import matplotlib.pyplot as plt\n",
    "import math\n",
    "\n",
    "pd.options.display.float_format = '{:.2f}'.format"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [],
   "source": [
    "# Ventas categorizadas y asignación resultante\n",
    "bdd_ventas = pd.read_excel(\"BDD_Bodegas_Categorizada_proy.xlsx\")\n",
    "bdd_asignaciones = pd.read_excel(\"resultados/Pre-Informe/datos_aloc_p_3_manhattan_proy.xlsx\")\n",
    "bdd_ventas['Fecha'] = pd.to_datetime(bdd_ventas['Fecha'])\n",
    "bdd_ventas['Semana'] = bdd_ventas['Fecha'].dt.strftime('%U')\n",
    "bdd_ventas['Año'] = bdd_ventas['Fecha'].dt.year\n",
    "bdd_ventas['Año-Sem'] = bdd_ventas['Año'].astype(str) + \"-\" + bdd_ventas['Semana'].astype(str)\n",
    "\n",
    "# Comunas\n",
    "bdd_comunas = pd.read_excel(\"BDD_Bodegas.xlsx\", sheet_name=3)\n",
    "\n",
    "bdd_asignaciones_intercambiadas = bdd_asignaciones[['Unnamed: 0', 'Bodega Asignada', 'Tiempo']]\n",
    "bdd_asignaciones = bdd_asignaciones[['Unnamed: 0', 'Bodega Asignada', 'Tiempo']]\n",
    "\n",
    "# Agrupación de ventas por cliente \n",
    "bdd_ventas_agrupadas = bdd_ventas.groupby(['ID Cliente', 'Año-Sem']).agg({\"Cantidad\": \"sum\", \"Comuna Despacho\": \"first\", 'ID Bodega Despacho': 'first', 'Categoria': 'first'}).reset_index()\n",
    "bdd_ventas_agrupadas = bdd_ventas_agrupadas.merge(bdd_comunas, left_on='Comuna Despacho', right_on='Comuna')\n",
    "bdd_ventas_agrupadas['Cantidad'] = bdd_ventas_agrupadas['Cantidad'].apply(lambda x: 0.01 if x == 0 else x)\n",
    "\n",
    "# Juntamos asginaciones con demandas tanto de la bdd historica como de la proyeccion\n",
    "bdd_juntadas = bdd_ventas_agrupadas.merge(bdd_asignaciones, left_on = \"ID Cliente\", right_on = \"Unnamed: 0\")\n",
    "\n",
    "# Resultados\n",
    "df_bodegas = bdd_juntadas.groupby(['Bodega Asignada', 'Año-Sem'])['Cantidad'].sum().reset_index()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "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>Bodega Asignada</th>\n",
       "      <th>Año-Sem</th>\n",
       "      <th>Cantidad</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>2031-38</td>\n",
       "      <td>9778.59</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>1</td>\n",
       "      <td>2031-39</td>\n",
       "      <td>10290.64</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>1</td>\n",
       "      <td>2031-40</td>\n",
       "      <td>6276.52</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>1</td>\n",
       "      <td>2031-41</td>\n",
       "      <td>9165.75</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>1</td>\n",
       "      <td>2031-42</td>\n",
       "      <td>3948.49</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>157</th>\n",
       "      <td>8</td>\n",
       "      <td>2032-34</td>\n",
       "      <td>23458.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>158</th>\n",
       "      <td>8</td>\n",
       "      <td>2032-35</td>\n",
       "      <td>21361.43</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>159</th>\n",
       "      <td>8</td>\n",
       "      <td>2032-36</td>\n",
       "      <td>31051.81</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>160</th>\n",
       "      <td>8</td>\n",
       "      <td>2032-37</td>\n",
       "      <td>16959.47</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>161</th>\n",
       "      <td>8</td>\n",
       "      <td>2032-38</td>\n",
       "      <td>7798.15</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>162 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "     Bodega Asignada  Año-Sem  Cantidad\n",
       "0                  1  2031-38   9778.59\n",
       "1                  1  2031-39  10290.64\n",
       "2                  1  2031-40   6276.52\n",
       "3                  1  2031-41   9165.75\n",
       "4                  1  2031-42   3948.49\n",
       "..               ...      ...       ...\n",
       "157                8  2032-34  23458.39\n",
       "158                8  2032-35  21361.43\n",
       "159                8  2032-36  31051.81\n",
       "160                8  2032-37  16959.47\n",
       "161                8  2032-38   7798.15\n",
       "\n",
       "[162 rows x 3 columns]"
      ]
     },
     "execution_count": 4,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "df_bodegas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "promedio_por_bodega = df_bodegas.groupby('Bodega Asignada')['Cantidad'].mean()\n",
    "promedio_bodegas = dict()\n",
    "for i in range(len(promedio_por_bodega)):\n",
    "    promedio_bodegas[promedio_por_bodega.index[i]] = promedio_por_bodega.iloc[i]\n",
    "\n",
    "costo_tpte = 1200 # Costo por kilómetro recorrido por camión\n",
    "costo_posicion = 36700 # Costo fijo de posición por producto en una bodega\n",
    "distancias_Stgo_a = {1: 371.39, 4: 179.66, 8: 534.03}\n",
    "distancias_Stgo_f = {1: 372.52, 4: 189.66, 8: 600.62}"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calcular_promedios(df_bodegas):\n",
    "    promedio_por_bodega = df_bodegas.groupby('Bodega Asignada')['Cantidad'].mean()\n",
    "    promedio_bodegas = dict()\n",
    "    for i in range(len(promedio_por_bodega)):\n",
    "        promedio_bodegas[promedio_por_bodega.index[i]] = promedio_por_bodega.iloc[i]\n",
    "    return promedio_bodegas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "162"
      ]
     },
     "execution_count": 15,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "len(df_bodegas)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "metadata": {},
   "outputs": [
    {
     "data": {
      "text/plain": [
       "{1: 6161.216381254617, 4: 58258.24291778562, 8: 19123.62011423694}"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "promedio_bodegas = calcular_promedios(df_bodegas)\n",
    "promedio_bodegas"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 29,
   "metadata": {},
   "outputs": [],
   "source": [
    "def calcular_promedios(df_bodegas):\n",
    "    promedio_por_bodega = df_bodegas.groupby('Bodega Asignada')['Cantidad'].mean()\n",
    "    promedio_bodegas = dict()\n",
    "    for i in range(len(promedio_por_bodega)):\n",
    "        promedio_bodegas[promedio_por_bodega.index[i]] = promedio_por_bodega.iloc[i]\n",
    "    return promedio_bodegas\n",
    "\n",
    "def tasa_ocupacion (demanda, promedio_bodegas, numero_bodega, delta):\n",
    "    tamaño = promedio_bodegas[numero_bodega]*delta\n",
    "    bdd = demanda[demanda[\"Bodega Asignada\"] == numero_bodega]\n",
    "    suma = 0\n",
    "    for i in range(len(bdd)):\n",
    "        if bdd.iloc[i, 2] >= tamaño:\n",
    "            suma += 1\n",
    "        else:\n",
    "            suma += bdd.iloc[i, 2] / tamaño\n",
    "    promedio = suma / len(bdd)\n",
    "    return promedio\n",
    "\n",
    "def semanas_sobreocupacion(demanda, promedio_bodegas, numero_bodega, delta):\n",
    "    tamaño = promedio_bodegas[numero_bodega]*delta\n",
    "    bdd = demanda[demanda[\"Bodega Asignada\"] == numero_bodega]\n",
    "    suma = 0\n",
    "    camiones_a_pedir = 0\n",
    "    for i in range(len(bdd)):\n",
    "        if bdd.iloc[i, 2] > tamaño:\n",
    "            suma += 1\n",
    "        camiones_a_pedir += math.ceil(bdd.iloc[i,2]/tamaño)\n",
    "    porcentaje = round(suma/len(bdd) * 100, 1)\n",
    "    return suma, f\"{porcentaje}%\", camiones_a_pedir\n",
    "\n",
    "def semanas_sobreocupacion_sim(demanda, promedio_bodegas, numero_bodega, delta):\n",
    "    tamaño = promedio_bodegas[numero_bodega]*delta\n",
    "    bdd = demanda[demanda[\"Bodega Asignada\"] == numero_bodega]\n",
    "    suma = 0\n",
    "    camiones_a_pedir = 0\n",
    "    for i in range(len(bdd)):\n",
    "        if bdd.iloc[i, 2] > tamaño:\n",
    "            suma += 1\n",
    "        camiones_a_pedir += math.ceil(bdd.iloc[i,2]/tamaño)\n",
    "    porcentaje = suma/len(bdd)\n",
    "    return suma, porcentaje, camiones_a_pedir\n",
    "\n",
    "def analisis_tamaño(demanda, promedio_bodegas, numero_bodega, delta, caso, N):\n",
    "    resultados = []\n",
    "    distancias_Stgo_a = {1: 371.39, 4: 179.66, 8: 534.03}\n",
    "    distancias_Stgo_f = {1: 372.52, 4: 189.66, 8: 600.62}\n",
    "    if caso == 'Actual':\n",
    "        distancias = distancias_Stgo_a\n",
    "    else:\n",
    "        distancias = distancias_Stgo_f\n",
    "    tamaño = promedio_bodegas[numero_bodega]*delta\n",
    "    tasa = tasa_ocupacion(demanda, promedio_bodegas, numero_bodega, delta)\n",
    "    semanas, porcentaje, camiones = semanas_sobreocupacion(demanda, promedio_bodegas, numero_bodega, delta)\n",
    "    costo_fijo = 36700*tamaño/1000000\n",
    "    costo_tpte = 1200*distancias[numero_bodega]*camiones/1000000\n",
    "    costo_tpte = costo_tpte/N\n",
    "    costo_total = costo_fijo + costo_tpte\n",
    "    resultados.append([delta, f\"{round(tasa * 100, 2)}%\", tamaño, (f'{semanas} semanas, {porcentaje} del total'), costo_fijo, costo_tpte, costo_total])\n",
    "    return resultados[0]\n",
    "\n",
    "def analisis_tamaño_sim(demanda, promedio_bodegas, numero_bodega, delta, caso, N):\n",
    "    resultados = []\n",
    "    distancias_Stgo_a = {1: 371.39, 4: 179.66, 8: 534.03}\n",
    "    distancias_Stgo_f = {1: 372.52, 4: 189.66, 8: 600.62}\n",
    "    if caso == 'Actual':\n",
    "        distancias = distancias_Stgo_a\n",
    "    else:\n",
    "        distancias = distancias_Stgo_f\n",
    "    tamaño = promedio_bodegas[numero_bodega]*delta\n",
    "    tasa = tasa_ocupacion(demanda, promedio_bodegas, numero_bodega, delta)\n",
    "    semanas, porcentaje, camiones = semanas_sobreocupacion_sim(demanda, promedio_bodegas, numero_bodega, delta)\n",
    "    costo_fijo = 36700*tamaño/1000000\n",
    "    costo_tpte = 1200*distancias[numero_bodega]*camiones/1000000\n",
    "    costo_tpte = costo_tpte/N\n",
    "    costo_total = costo_fijo + costo_tpte\n",
    "    resultados.append([delta, tasa, tamaño, porcentaje, costo_fijo, costo_tpte, costo_total])\n",
    "    return resultados[0]\n",
    "\n",
    "def costo_minimo(df_bodegas, promedio_bodegas, numero_bodega, caso, N):\n",
    "    delta = 0\n",
    "    resultado_optimo = None\n",
    "    min = math.inf\n",
    "    optimo = False\n",
    "    while optimo == False:\n",
    "        delta += 0.01\n",
    "        resultado = analisis_tamaño(df_bodegas, promedio_bodegas, numero_bodega, delta, caso, N)\n",
    "        costo = resultado[6]\n",
    "        if costo < min:\n",
    "            min = costo\n",
    "            resultado_optimo = resultado\n",
    "        else:\n",
    "            optimo = True\n",
    "    return resultado_optimo\n",
    "\n",
    "def costo_minimo_sim(df_bodegas, promedio_bodegas, numero_bodega, caso, N):\n",
    "    delta = 0\n",
    "    resultado_optimo = None\n",
    "    min = math.inf\n",
    "    optimo = False\n",
    "    while optimo == False:\n",
    "        delta += 0.01\n",
    "        resultado = analisis_tamaño_sim(df_bodegas, promedio_bodegas, numero_bodega, delta, caso, N)\n",
    "        costo = resultado[6]\n",
    "        if costo < min:\n",
    "            min = costo\n",
    "            resultado_optimo = resultado\n",
    "        else:\n",
    "            optimo = True\n",
    "    return resultado_optimo\n",
    "\n",
    "def tamaños_optimos(df_bodegas, promedio_bodegas, caso):\n",
    "    N = (len(df_bodegas)/3)/54\n",
    "    resultados = list()\n",
    "    for bodega in [1, 4, 8]:\n",
    "        resultado = costo_minimo(df_bodegas, promedio_bodegas, bodega, caso, N)\n",
    "        resultado.insert(0, bodega)\n",
    "        resultados.append(resultado)\n",
    "    df_resultados = pd.DataFrame(resultados, columns=[\"Bodega\", \"Delta\", \"Ocupación\", \"Tamaño\", \"Sobreocupación\", \"CF (MM)\", \"CTpte (MM)\", \"CT (MM)\"])\n",
    "    return df_resultados\n",
    "\n",
    "def tamaños_optimos_sim(df_bodegas, promedio_bodegas, caso):\n",
    "    N = (len(df_bodegas)/3)/54\n",
    "    resultados = list()\n",
    "    for bodega in [1, 4, 8]:\n",
    "        resultado = costo_minimo_sim(df_bodegas, promedio_bodegas, bodega, caso, N)\n",
    "        resultado.insert(0, bodega)\n",
    "        resultados.append(resultado)\n",
    "    df_resultados = pd.DataFrame(resultados, columns=[\"Bodega\", \"Delta\", \"Ocupación\", \"Tamaño\", \"Sobreocupación\", \"CF (MM)\", \"CTpte (MM)\", \"CT (MM)\"])\n",
    "    return df_resultados"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 28,
   "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>Bodega</th>\n",
       "      <th>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.42</td>\n",
       "      <td>0.93</td>\n",
       "      <td>1035.02</td>\n",
       "      <td>0.78</td>\n",
       "      <td>37.99</td>\n",
       "      <td>63.88</td>\n",
       "      <td>101.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>0.15</td>\n",
       "      <td>0.98</td>\n",
       "      <td>1779.72</td>\n",
       "      <td>0.91</td>\n",
       "      <td>65.32</td>\n",
       "      <td>77.07</td>\n",
       "      <td>142.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.39</td>\n",
       "      <td>0.87</td>\n",
       "      <td>2000.46</td>\n",
       "      <td>0.71</td>\n",
       "      <td>73.42</td>\n",
       "      <td>98.37</td>\n",
       "      <td>171.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bodega  Delta  Ocupación  Tamaño  Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0       1   0.42       0.93 1035.02            0.78    37.99       63.88   \n",
       "1       4   0.15       0.98 1779.72            0.91    65.32       77.07   \n",
       "2       8   0.39       0.87 2000.46            0.71    73.42       98.37   \n",
       "\n",
       "   CT (MM)  \n",
       "0   101.86  \n",
       "1   142.39  \n",
       "2   171.79  "
      ]
     },
     "execution_count": 28,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "promedio_bodegas = calcular_promedios(df_bodegas)\n",
    "costos_optimos = tamaños_optimos_sim(df_bodegas, promedio_bodegas, 'Actual')\n",
    "costos_optimos"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "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>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.42</td>\n",
       "      <td>92.84%</td>\n",
       "      <td>1035.02</td>\n",
       "      <td>234 semanas, 78.0% del total</td>\n",
       "      <td>37.99</td>\n",
       "      <td>63.88</td>\n",
       "      <td>101.86</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.15</td>\n",
       "      <td>97.82%</td>\n",
       "      <td>1779.72</td>\n",
       "      <td>273 semanas, 91.3% del total</td>\n",
       "      <td>65.32</td>\n",
       "      <td>77.07</td>\n",
       "      <td>142.39</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.39</td>\n",
       "      <td>87.18%</td>\n",
       "      <td>2000.46</td>\n",
       "      <td>212 semanas, 70.7% del total</td>\n",
       "      <td>73.42</td>\n",
       "      <td>98.37</td>\n",
       "      <td>171.79</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Delta Ocupación  Tamaño                Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0   0.42    92.84% 1035.02  234 semanas, 78.0% del total    37.99       63.88   \n",
       "1   0.15    97.82% 1779.72  273 semanas, 91.3% del total    65.32       77.07   \n",
       "2   0.39    87.18% 2000.46  212 semanas, 70.7% del total    73.42       98.37   \n",
       "\n",
       "   CT (MM)  \n",
       "0   101.86  \n",
       "1   142.39  \n",
       "2   171.79  "
      ]
     },
     "execution_count": 20,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b = tamaños_optimos(df_bodegas, promedio_bodegas, 'Actual')\n",
    "b"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 27,
   "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>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.48</td>\n",
       "      <td>90.54%</td>\n",
       "      <td>1182.88</td>\n",
       "      <td>214 semanas, 71.3% del total</td>\n",
       "      <td>43.41</td>\n",
       "      <td>68.45</td>\n",
       "      <td>111.87</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.17</td>\n",
       "      <td>96.85%</td>\n",
       "      <td>2017.01</td>\n",
       "      <td>262 semanas, 87.6% del total</td>\n",
       "      <td>74.02</td>\n",
       "      <td>82.18</td>\n",
       "      <td>156.21</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.39</td>\n",
       "      <td>87.18%</td>\n",
       "      <td>2000.46</td>\n",
       "      <td>212 semanas, 70.7% del total</td>\n",
       "      <td>73.42</td>\n",
       "      <td>118.04</td>\n",
       "      <td>191.46</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Delta Ocupación  Tamaño                Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0   0.48    90.54% 1182.88  214 semanas, 71.3% del total    43.41       68.45   \n",
       "1   0.17    96.85% 2017.01  262 semanas, 87.6% del total    74.02       82.18   \n",
       "2   0.39    87.18% 2000.46  212 semanas, 70.7% del total    73.42      118.04   \n",
       "\n",
       "   CT (MM)  \n",
       "0   111.87  \n",
       "1   156.21  \n",
       "2   191.46  "
      ]
     },
     "execution_count": 27,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "b20 = tamaños_optimos(df_bodegas, promedio_bodegas, 'Actual')\n",
    "b20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 8,
   "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>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.31</td>\n",
       "      <td>99.61%</td>\n",
       "      <td>1909.98</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>70.10</td>\n",
       "      <td>89.85</td>\n",
       "      <td>159.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.08</td>\n",
       "      <td>96.97%</td>\n",
       "      <td>4660.66</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>171.05</td>\n",
       "      <td>160.22</td>\n",
       "      <td>331.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>94.92%</td>\n",
       "      <td>4398.43</td>\n",
       "      <td>49 semanas, 90.7% del total</td>\n",
       "      <td>161.42</td>\n",
       "      <td>188.11</td>\n",
       "      <td>349.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Delta Ocupación  Tamaño               Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0   0.31    99.61% 1909.98  52 semanas, 96.3% del total    70.10       89.85   \n",
       "1   0.08    96.97% 4660.66  52 semanas, 96.3% del total   171.05      160.22   \n",
       "2   0.23    94.92% 4398.43  49 semanas, 90.7% del total   161.42      188.11   \n",
       "\n",
       "   CT (MM)  \n",
       "0   159.95  \n",
       "1   331.27  \n",
       "2   349.54  "
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c = tamaños_optimos(df_bodegas, promedio_bodegas, 'Futuro')\n",
    "c"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 33,
   "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>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>0.31</td>\n",
       "      <td>99.61%</td>\n",
       "      <td>1909.98</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>70.10</td>\n",
       "      <td>107.82</td>\n",
       "      <td>177.92</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>0.08</td>\n",
       "      <td>96.97%</td>\n",
       "      <td>4660.66</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>171.05</td>\n",
       "      <td>192.27</td>\n",
       "      <td>363.32</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>0.23</td>\n",
       "      <td>94.92%</td>\n",
       "      <td>4398.43</td>\n",
       "      <td>49 semanas, 90.7% del total</td>\n",
       "      <td>161.42</td>\n",
       "      <td>225.74</td>\n",
       "      <td>387.16</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Delta Ocupación  Tamaño               Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0   0.31    99.61% 1909.98  52 semanas, 96.3% del total    70.10      107.82   \n",
       "1   0.08    96.97% 4660.66  52 semanas, 96.3% del total   171.05      192.27   \n",
       "2   0.23    94.92% 4398.43  49 semanas, 90.7% del total   161.42      225.74   \n",
       "\n",
       "   CT (MM)  \n",
       "0   177.92  \n",
       "1   363.32  \n",
       "2   387.16  "
      ]
     },
     "execution_count": 33,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "c20 = tamaños_optimos(df_bodegas, promedio_bodegas, 'Futuro')\n",
    "c20"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 58,
   "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>Bodega</th>\n",
       "      <th>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>1.00</td>\n",
       "      <td>1909.98</td>\n",
       "      <td>0.96</td>\n",
       "      <td>70.10</td>\n",
       "      <td>89.85</td>\n",
       "      <td>159.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>0.08</td>\n",
       "      <td>0.97</td>\n",
       "      <td>4660.66</td>\n",
       "      <td>0.96</td>\n",
       "      <td>171.05</td>\n",
       "      <td>160.22</td>\n",
       "      <td>331.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.23</td>\n",
       "      <td>0.95</td>\n",
       "      <td>4398.43</td>\n",
       "      <td>0.91</td>\n",
       "      <td>161.42</td>\n",
       "      <td>188.11</td>\n",
       "      <td>349.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bodega  Delta  Ocupación  Tamaño  Sobreocupación  CF (MM)  CTpte (MM)  \\\n",
       "0       1   0.31       1.00 1909.98            0.96    70.10       89.85   \n",
       "1       4   0.08       0.97 4660.66            0.96   171.05      160.22   \n",
       "2       8   0.23       0.95 4398.43            0.91   161.42      188.11   \n",
       "\n",
       "   CT (MM)  \n",
       "0   159.95  \n",
       "1   331.27  \n",
       "2   349.54  "
      ]
     },
     "execution_count": 58,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cn = tamaños_optimos_sim(df_bodegas, promedio_bodegas, 'Futuro')\n",
    "cn"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 46,
   "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>Bodega</th>\n",
       "      <th>Delta</th>\n",
       "      <th>Ocupación</th>\n",
       "      <th>Tamaño</th>\n",
       "      <th>Sobreocupación</th>\n",
       "      <th>CF (MM)</th>\n",
       "      <th>CTpte (MM)</th>\n",
       "      <th>CT (MM)</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>1</td>\n",
       "      <td>0.31</td>\n",
       "      <td>99.61%</td>\n",
       "      <td>1909.98</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>70.10</td>\n",
       "      <td>89.85</td>\n",
       "      <td>159.95</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4</td>\n",
       "      <td>0.08</td>\n",
       "      <td>96.97%</td>\n",
       "      <td>4660.66</td>\n",
       "      <td>52 semanas, 96.3% del total</td>\n",
       "      <td>171.05</td>\n",
       "      <td>160.22</td>\n",
       "      <td>331.27</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>8</td>\n",
       "      <td>0.23</td>\n",
       "      <td>94.92%</td>\n",
       "      <td>4398.43</td>\n",
       "      <td>49 semanas, 90.7% del total</td>\n",
       "      <td>161.42</td>\n",
       "      <td>188.11</td>\n",
       "      <td>349.54</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "   Bodega  Delta Ocupación  Tamaño               Sobreocupación  CF (MM)  \\\n",
       "0       1   0.31    99.61% 1909.98  52 semanas, 96.3% del total    70.10   \n",
       "1       4   0.08    96.97% 4660.66  52 semanas, 96.3% del total   171.05   \n",
       "2       8   0.23    94.92% 4398.43  49 semanas, 90.7% del total   161.42   \n",
       "\n",
       "   CTpte (MM)  CT (MM)  \n",
       "0       89.85   159.95  \n",
       "1      160.22   331.27  \n",
       "2      188.11   349.54  "
      ]
     },
     "execution_count": 46,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "cn = tamaños_optimos(df_bodegas, promedio_bodegas, 'Futuro')\n",
    "cn"
   ]
  },
  {
   "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.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}