1 INDICADORES_GENERAL.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "id": "917a6792",
   "metadata": {},
   "source": [
    "### ETL Gestión financiera - Mes a mes"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "98c2aa0f",
   "metadata": {},
   "source": [
    "En este notebook se realiza la ETL para los ingresos, gastos e indicadores de la compañía mes a mes de manera general. Cada mes se ejecuta la función final y genera una nueva fila con la información financiera general del mes para ser cargada con el script de Python a Azure Storage. "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "d9cef3b0",
   "metadata": {},
   "outputs": [],
   "source": [
    "import numpy as np\n",
    "import pandas as pd"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "id": "1485118b",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Se usa para que se muestren todas las columnas del dataframe\n",
    "pd.options.display.max_columns=None"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "id": "90240ca0",
   "metadata": {},
   "outputs": [],
   "source": [
    "df=pd.read_csv('Auxiliar_General.csv')\n",
    "df_enero=pd.read_csv('enero.csv')\n",
    "df_febrero=pd.read_csv('febrero.csv')\n",
    "df_marzo=pd.read_csv('marzo.csv')\n",
    "df_abril=pd.read_csv('abril.csv')\n",
    "\n",
    "con=pd.read_csv('Tabla_Conceptos_act.csv')\n",
    "\n",
    "df_mayo=pd.read_csv('mayo.csv')\n",
    "df_junio=pd.read_csv('junio.csv')\n",
    "df_julio=pd.read_csv('julio.csv')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 9,
   "id": "41772002",
   "metadata": {},
   "outputs": [],
   "source": [
    "def financieras(df,con):\n",
    "    \n",
    "    #Crea la columna movimiento que corresponde a los débitos menos los créditos\n",
    "    df['Movimiento']=df.apply(\n",
    "    lambda row: row.Débitos - row.Créditos , axis=1)\n",
    "    \n",
    "    #Convierte la columna fecha a tipo datetime\n",
    "    df['Fecha'] = pd.to_datetime(df['Fecha'])\n",
    "    \n",
    "    #Crea una nueva columna con la información de mes y año de la fecha registrada\n",
    "    df['Mes']=df['Fecha'].dt.strftime('%m')\n",
    "    \n",
    "    #Convierte la columna 'Cuenta' a tipo object para poder realizar la busqueda de las cuentas \n",
    "    df['Cuenta']=df['Cuenta'].apply(str)\n",
    "    \n",
    "    #Crea un nuevo dataframe que resultada de la busqueda de las cuentas que empiezan con los dígitos: 4,5 o 7\n",
    "    df_fil=df[df.Cuenta.str.startswith('4') | df.Cuenta.str.startswith('5') | df.Cuenta.str.startswith('7')].reset_index(drop=True)\n",
    "    \n",
    "    #Elimina unas cuentas especificas que no hacen parte de los resultados esperados\n",
    "    df_fil.drop(df_fil[df_fil['Cuenta']=='7904950000'].index, inplace=True)\n",
    "    df_fil.drop(df_fil[df_fil['Cuenta']=='5905010000'].index, inplace=True)\n",
    "    df_fil=df_fil.reset_index(drop=True)\n",
    "    \n",
    "    #Crea una tabla dinamica desagregada por cuentas y año-mes\n",
    "    pivotTable=pd.pivot_table(df_fil, values='Movimiento', index= 'Cuenta',\n",
    "                   columns= 'Mes',\n",
    "                   aggfunc= np.sum,\n",
    "                   margins= False)\n",
    "    \n",
    "    #Convierte la columna 'Cuenta' de la tabla 'con' a tipo object\n",
    "    con['Cuenta']=con['Cuenta'].apply(str)\n",
    "    \n",
    "    #Une la tabla pivotTable y la tabla con\n",
    "    df_com=pd.merge(pivotTable, con, on='Cuenta', how='left')\n",
    "    \n",
    "    return df_com "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 11,
   "id": "fe14a5ca",
   "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>Cuenta</th>\n",
       "      <th>01</th>\n",
       "      <th>02</th>\n",
       "      <th>03</th>\n",
       "      <th>04</th>\n",
       "      <th>Nombre Cuenta</th>\n",
       "      <th>Conceptos PYG</th>\n",
       "      <th>Conceptos PFC</th>\n",
       "      <th>Rubro General</th>\n",
       "      <th>Rubro AOM</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>TIPO</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>4390900000</td>\n",
       "      <td>-1.245513e+09</td>\n",
       "      <td>-1.282834e+09</td>\n",
       "      <td>-1.529181e+09</td>\n",
       "      <td>-1.299023e+09</td>\n",
       "      <td>SERVICIOS BPO OPERACIÓN CENTRO</td>\n",
       "      <td>Ingresos Operacionales</td>\n",
       "      <td>Otros Ingresos con Empresas que No Consolidan</td>\n",
       "      <td>Ingresos operacionales</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Ingresos_operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>4390900905</td>\n",
       "      <td>-4.056660e+08</td>\n",
       "      <td>-4.048993e+08</td>\n",
       "      <td>-6.652674e+08</td>\n",
       "      <td>-3.774175e+08</td>\n",
       "      <td>BPO</td>\n",
       "      <td>Ingresos Operacionales</td>\n",
       "      <td>Otros Ingresos con Empresas que No Consolidan</td>\n",
       "      <td>Ingresos operacionales</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Ingresos_operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>4802010200</td>\n",
       "      <td>-1.296298e+07</td>\n",
       "      <td>-1.714181e+07</td>\n",
       "      <td>-1.901679e+07</td>\n",
       "      <td>-1.766267e+07</td>\n",
       "      <td>INT SOBRE DEP EN INST FINANCIE</td>\n",
       "      <td>Ingresos Financieros</td>\n",
       "      <td>Rendimientos Financieros</td>\n",
       "      <td>Ingresos financieros</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Ingresos_no_operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>4806010100</td>\n",
       "      <td>NaN</td>\n",
       "      <td>-9.488950e+04</td>\n",
       "      <td>-2.272474e+05</td>\n",
       "      <td>-6.260598e+05</td>\n",
       "      <td>OT ING-DIF CAMBIO-EFECT-REALIZ</td>\n",
       "      <td>Ingresos Financieros</td>\n",
       "      <td>Rendimientos Financieros</td>\n",
       "      <td>Ingresos financieros</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Ingresos_no_operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>4806020200</td>\n",
       "      <td>-4.259000e+04</td>\n",
       "      <td>3.418400e+04</td>\n",
       "      <td>-1.830320e+05</td>\n",
       "      <td>-4.312500e+04</td>\n",
       "      <td>OT ING-DIF CAMBIO-CXC-VALORADA</td>\n",
       "      <td>Ingresos No Operacionales</td>\n",
       "      <td>DC Ing-Cuentas x cobrar y pagar</td>\n",
       "      <td>Ingresos financieros</td>\n",
       "      <td>NO</td>\n",
       "      <td>NO</td>\n",
       "      <td>Ingresos_no_operacionales</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",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>128</th>\n",
       "      <td>7904070100</td>\n",
       "      <td>1.716604e+07</td>\n",
       "      <td>1.716604e+07</td>\n",
       "      <td>1.211985e+07</td>\n",
       "      <td>1.491367e+07</td>\n",
       "      <td>AMORTIZ LICENCIAS-CTOS</td>\n",
       "      <td>Depreciación y amortización - provisión</td>\n",
       "      <td>Total Deprec y Amortizaciones</td>\n",
       "      <td>Gastos operacionales</td>\n",
       "      <td>NO</td>\n",
       "      <td>SI</td>\n",
       "      <td>Gastos_Operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>129</th>\n",
       "      <td>7904070101</td>\n",
       "      <td>7.910750e+05</td>\n",
       "      <td>7.910750e+05</td>\n",
       "      <td>7.910750e+05</td>\n",
       "      <td>7.910750e+05</td>\n",
       "      <td>AMORTIZ SOFTWARE-CTOS</td>\n",
       "      <td>Depreciación y amortización - provisión</td>\n",
       "      <td>Total Deprec y Amortizaciones</td>\n",
       "      <td>Gastos operacionales</td>\n",
       "      <td>NO</td>\n",
       "      <td>SI</td>\n",
       "      <td>Gastos_Operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>130</th>\n",
       "      <td>7904070200</td>\n",
       "      <td>5.688988e+07</td>\n",
       "      <td>5.688988e+07</td>\n",
       "      <td>5.688988e+07</td>\n",
       "      <td>5.688988e+07</td>\n",
       "      <td>AMORTIZ CENTRO DE CONTROL</td>\n",
       "      <td>Depreciación y amortización - provisión</td>\n",
       "      <td>Total Deprec y Amortizaciones</td>\n",
       "      <td>Gastos operacionales</td>\n",
       "      <td>NO</td>\n",
       "      <td>SI</td>\n",
       "      <td>Gastos_Operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>131</th>\n",
       "      <td>7904081001</td>\n",
       "      <td>4.982051e+06</td>\n",
       "      <td>5.131335e+06</td>\n",
       "      <td>6.116726e+06</td>\n",
       "      <td>5.196092e+06</td>\n",
       "      <td>GRAVAMEN A LOS MOVIMIENTOS FIN</td>\n",
       "      <td>Contribuciones e impuestos</td>\n",
       "      <td>Impto- Tasas y Contribuciones</td>\n",
       "      <td>Gastos operacionales</td>\n",
       "      <td>Impuestos y contribuciones</td>\n",
       "      <td>SI</td>\n",
       "      <td>Gastos_Operacionales</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>132</th>\n",
       "      <td>7904081003</td>\n",
       "      <td>1.651000e+07</td>\n",
       "      <td>1.691900e+07</td>\n",
       "      <td>2.200500e+07</td>\n",
       "      <td>1.681600e+07</td>\n",
       "      <td>IMPUESTO IND Y COMERCIO</td>\n",
       "      <td>Contribuciones e impuestos</td>\n",
       "      <td>Impto- Tasas y Contribuciones</td>\n",
       "      <td>Gastos operacionales</td>\n",
       "      <td>Impuestos y contribuciones</td>\n",
       "      <td>SI</td>\n",
       "      <td>Gastos_Operacionales</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>133 rows × 12 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "         Cuenta            01            02            03            04  \\\n",
       "0    4390900000 -1.245513e+09 -1.282834e+09 -1.529181e+09 -1.299023e+09   \n",
       "1    4390900905 -4.056660e+08 -4.048993e+08 -6.652674e+08 -3.774175e+08   \n",
       "2    4802010200 -1.296298e+07 -1.714181e+07 -1.901679e+07 -1.766267e+07   \n",
       "3    4806010100           NaN -9.488950e+04 -2.272474e+05 -6.260598e+05   \n",
       "4    4806020200 -4.259000e+04  3.418400e+04 -1.830320e+05 -4.312500e+04   \n",
       "..          ...           ...           ...           ...           ...   \n",
       "128  7904070100  1.716604e+07  1.716604e+07  1.211985e+07  1.491367e+07   \n",
       "129  7904070101  7.910750e+05  7.910750e+05  7.910750e+05  7.910750e+05   \n",
       "130  7904070200  5.688988e+07  5.688988e+07  5.688988e+07  5.688988e+07   \n",
       "131  7904081001  4.982051e+06  5.131335e+06  6.116726e+06  5.196092e+06   \n",
       "132  7904081003  1.651000e+07  1.691900e+07  2.200500e+07  1.681600e+07   \n",
       "\n",
       "                      Nombre Cuenta                            Conceptos PYG  \\\n",
       "0    SERVICIOS BPO OPERACIÓN CENTRO                   Ingresos Operacionales   \n",
       "1    BPO                                              Ingresos Operacionales   \n",
       "2    INT SOBRE DEP EN INST FINANCIE                     Ingresos Financieros   \n",
       "3    OT ING-DIF CAMBIO-EFECT-REALIZ                     Ingresos Financieros   \n",
       "4    OT ING-DIF CAMBIO-CXC-VALORADA                Ingresos No Operacionales   \n",
       "..                              ...                                      ...   \n",
       "128  AMORTIZ LICENCIAS-CTOS          Depreciación y amortización - provisión   \n",
       "129  AMORTIZ SOFTWARE-CTOS           Depreciación y amortización - provisión   \n",
       "130  AMORTIZ CENTRO DE CONTROL       Depreciación y amortización - provisión   \n",
       "131  GRAVAMEN A LOS MOVIMIENTOS FIN               Contribuciones e impuestos   \n",
       "132  IMPUESTO IND Y COMERCIO                      Contribuciones e impuestos   \n",
       "\n",
       "                                     Conceptos PFC           Rubro General  \\\n",
       "0    Otros Ingresos con Empresas que No Consolidan  Ingresos operacionales   \n",
       "1    Otros Ingresos con Empresas que No Consolidan  Ingresos operacionales   \n",
       "2                         Rendimientos Financieros    Ingresos financieros   \n",
       "3                         Rendimientos Financieros    Ingresos financieros   \n",
       "4                  DC Ing-Cuentas x cobrar y pagar    Ingresos financieros   \n",
       "..                                             ...                     ...   \n",
       "128                  Total Deprec y Amortizaciones    Gastos operacionales   \n",
       "129                  Total Deprec y Amortizaciones    Gastos operacionales   \n",
       "130                  Total Deprec y Amortizaciones    Gastos operacionales   \n",
       "131                  Impto- Tasas y Contribuciones    Gastos operacionales   \n",
       "132                  Impto- Tasas y Contribuciones    Gastos operacionales   \n",
       "\n",
       "                      Rubro AOM EBITDA                       TIPO  \n",
       "0                            NO     NO     Ingresos_operacionales  \n",
       "1                            NO     NO     Ingresos_operacionales  \n",
       "2                            NO     NO  Ingresos_no_operacionales  \n",
       "3                            NO     NO  Ingresos_no_operacionales  \n",
       "4                            NO     NO  Ingresos_no_operacionales  \n",
       "..                          ...    ...                        ...  \n",
       "128                          NO     SI       Gastos_Operacionales  \n",
       "129                          NO     SI       Gastos_Operacionales  \n",
       "130                          NO     SI       Gastos_Operacionales  \n",
       "131  Impuestos y contribuciones     SI       Gastos_Operacionales  \n",
       "132  Impuestos y contribuciones     SI       Gastos_Operacionales  \n",
       "\n",
       "[133 rows x 12 columns]"
      ]
     },
     "execution_count": 11,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "financieras(df,con)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "b2c895b1",
   "metadata": {},
   "source": [
    "### Calculos derivados de esta información:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 12,
   "id": "e3eafb6f",
   "metadata": {},
   "outputs": [],
   "source": [
    "def calculos(df,con,n):\n",
    "    \n",
    "    df_com=financieras(df,con)\n",
    "    \n",
    "    #Agrupa la tabla df_tipo solo por tipo , tomando como columnas el mes, que corresponde a df_columns[1]\n",
    "    df_tipo=df_com.groupby('TIPO')[df_com.columns[1]].sum()\n",
    "    \n",
    "    #Renombra el indice\n",
    "    df_tipo=df_tipo.rename_axis('')\n",
    "    \n",
    "    #El indice se nombra como 'Mes' y lo separa como una columna\n",
    "    df_tipo=df_tipo.rename_axis('Mes').reset_index()\n",
    "    \n",
    "    #_________________________________________________________________________\n",
    "    \n",
    "    #Agrupa la tabla df_tipo solo por EBITDA , tomando como columnas el mes, que corresponde a df_columns[1]\n",
    "    df_eb=df_com.groupby('EBITDA')[df_com.columns[1]].sum()\n",
    "    \n",
    "    #Renombra el indice\n",
    "    df_eb=df_eb.rename_axis('')\n",
    "    \n",
    "    #El indice lo nombre como 'Mes' y lo separa como una columna\n",
    "    df_eb=df_eb.rename_axis('Mes').reset_index()  \n",
    "    #_________________________________________________________________________\n",
    "    \n",
    "    #Une por filas la tabla df_tipo y df_eb\n",
    "    df_com=pd.concat([df_tipo,df_eb],ignore_index=True)\n",
    "    \n",
    "    #La columna 'Mes' pasa a ser el indice\n",
    "    df_com.set_index('Mes',inplace=True)\n",
    "    \n",
    "    #Se transpone el dataframe\n",
    "    df_com=df_com.transpose()\n",
    "    \n",
    "    #Elimina la columna 'NO'\n",
    "    df_com=df_com.drop(['NO'], axis=1)\n",
    "    \n",
    "    #Cambia el nombre de la columna 'SI'\n",
    "    df_com=df_com.rename(columns={'SI':'SUMA_SI_EBITDA'})\n",
    "    \n",
    "    # CALCULOS IMPORTANTES:\n",
    "    \n",
    "    df_com['UTILIDAD_NETA']=df_com['Gastos_Administrativos']+df_com['Gastos_Operacionales']+df_com['Gastos_no_operacionales']+df_com['Impuesto_Renta']+df_com['Ingresos_no_operacionales']+df_com['Ingresos_operacionales']\n",
    "    \n",
    "    df_com['UTILIDAD_BRUTA']=df_com['Gastos_Operacionales']+df_com['Ingresos_operacionales']\n",
    "    \n",
    "    df_com['UTILIDAD_OPERATIVA']=df_com['Gastos_Administrativos']+df_com['Gastos_Operacionales']+df_com['Ingresos_operacionales']\n",
    "    \n",
    "    df_com['UAI']=df_com['Gastos_Administrativos']+df_com['Gastos_Operacionales']+df_com['Gastos_no_operacionales']+df_com['Ingresos_no_operacionales']+df_com['Ingresos_operacionales']\n",
    "\n",
    "    df_com['MARGEN_BRUTO']=df_com['UTILIDAD_BRUTA']/df_com['Ingresos_operacionales']\n",
    "    df_com['MARGEN_OPERATIVO']=df_com['UTILIDAD_OPERATIVA']/df_com['Ingresos_operacionales']\n",
    "    df_com['MARGEN_NETO']=df_com['UTILIDAD_NETA']/df_com['Ingresos_operacionales']\n",
    "    \n",
    "    df_com['EBITDA']=df_com['UTILIDAD_OPERATIVA']-df_com['SUMA_SI_EBITDA']\n",
    "    df_com['MARGEN_EBITDA']=df_com['EBITDA']/df_com['Ingresos_operacionales']\n",
    "    \n",
    "    df_com['PartitionKey']='2022'\n",
    "    df_com['RowKey']=n\n",
    "    \n",
    "    \n",
    "    if n==1:\n",
    "        df_com['MES']='01 Enero'\n",
    "        \n",
    "    elif n==2:\n",
    "        df_com['MES']='02 Febrero'\n",
    "        \n",
    "    elif n==3:\n",
    "        df_com['MES']='03 Marzo'\n",
    "    \n",
    "    elif n==4:\n",
    "        df_com['MES']='04 Abril'\n",
    "        \n",
    "    elif n==5:\n",
    "        df_com['MES']='05 Mayo'\n",
    "        \n",
    "    elif n==6:\n",
    "        df_com['MES']='06 Junio'\n",
    "        \n",
    "    elif n==7:\n",
    "        df_com['MES']='07 Julio'\n",
    "        \n",
    "    elif n==8:\n",
    "        df_com['MES']='08 Agosto'\n",
    "    \n",
    "    elif n==9:\n",
    "        df_com['MES']='09 Septiembre'\n",
    "    \n",
    "    elif n==10:\n",
    "        df_com['MES']='10 Octubre'\n",
    "    \n",
    "    elif n==11:\n",
    "        df_com['MES']='11 Noviembre'\n",
    "        \n",
    "    elif n==12:\n",
    "        df_com['MES']='12 Diciembre'\n",
    "        \n",
    "    \n",
    "        \n",
    "    df_com=df_com[['PartitionKey', 'RowKey','MES','Gastos_Administrativos', 'Gastos_Operacionales',\n",
    "       'Gastos_no_operacionales', 'Impuesto_Renta',\n",
    "       'Ingresos_no_operacionales', 'Ingresos_operacionales',\n",
    "       'UTILIDAD_NETA', 'UTILIDAD_BRUTA', 'UTILIDAD_OPERATIVA', 'UAI', 'EBITDA',\n",
    "       'MARGEN_BRUTO', 'MARGEN_OPERATIVO', 'MARGEN_NETO',\n",
    "       'MARGEN_EBITDA']]\n",
    "\n",
    "           \n",
    "    return df_com"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 13,
   "id": "f36f2a59",
   "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>Mes</th>\n",
       "      <th>PartitionKey</th>\n",
       "      <th>RowKey</th>\n",
       "      <th>MES</th>\n",
       "      <th>Gastos_Administrativos</th>\n",
       "      <th>Gastos_Operacionales</th>\n",
       "      <th>Gastos_no_operacionales</th>\n",
       "      <th>Impuesto_Renta</th>\n",
       "      <th>Ingresos_no_operacionales</th>\n",
       "      <th>Ingresos_operacionales</th>\n",
       "      <th>UTILIDAD_NETA</th>\n",
       "      <th>UTILIDAD_BRUTA</th>\n",
       "      <th>UTILIDAD_OPERATIVA</th>\n",
       "      <th>UAI</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>MARGEN_BRUTO</th>\n",
       "      <th>MARGEN_OPERATIVO</th>\n",
       "      <th>MARGEN_NETO</th>\n",
       "      <th>MARGEN_EBITDA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>01</th>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>01 Enero</td>\n",
       "      <td>3.195952e+08</td>\n",
       "      <td>976323949.4</td>\n",
       "      <td>9327004.69</td>\n",
       "      <td>147290102.0</td>\n",
       "      <td>-13005574.15</td>\n",
       "      <td>-1.651179e+09</td>\n",
       "      <td>-2.116482e+08</td>\n",
       "      <td>-674854948.6</td>\n",
       "      <td>-3.552598e+08</td>\n",
       "      <td>-3.589383e+08</td>\n",
       "      <td>-4.724733e+08</td>\n",
       "      <td>0.408711</td>\n",
       "      <td>0.215155</td>\n",
       "      <td>0.12818</td>\n",
       "      <td>0.286143</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Mes PartitionKey  RowKey       MES  Gastos_Administrativos  \\\n",
       "01          2022       1  01 Enero            3.195952e+08   \n",
       "\n",
       "Mes  Gastos_Operacionales  Gastos_no_operacionales  Impuesto_Renta  \\\n",
       "01            976323949.4               9327004.69     147290102.0   \n",
       "\n",
       "Mes  Ingresos_no_operacionales  Ingresos_operacionales  UTILIDAD_NETA  \\\n",
       "01                -13005574.15           -1.651179e+09  -2.116482e+08   \n",
       "\n",
       "Mes  UTILIDAD_BRUTA  UTILIDAD_OPERATIVA           UAI        EBITDA  \\\n",
       "01     -674854948.6       -3.552598e+08 -3.589383e+08 -4.724733e+08   \n",
       "\n",
       "Mes  MARGEN_BRUTO  MARGEN_OPERATIVO  MARGEN_NETO  MARGEN_EBITDA  \n",
       "01       0.408711          0.215155      0.12818       0.286143  "
      ]
     },
     "execution_count": 13,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "calculos(df_enero,con,1)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "19d73a01",
   "metadata": {},
   "source": [
    "La siguiente linea toma la información de cada mes, para la cual realiza todos los calculos, y luego une todas estas tablas en una sola tabla: "
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 14,
   "id": "7005e6c9",
   "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>Mes</th>\n",
       "      <th>PartitionKey</th>\n",
       "      <th>RowKey</th>\n",
       "      <th>MES</th>\n",
       "      <th>Gastos_Administrativos</th>\n",
       "      <th>Gastos_Operacionales</th>\n",
       "      <th>Gastos_no_operacionales</th>\n",
       "      <th>Impuesto_Renta</th>\n",
       "      <th>Ingresos_no_operacionales</th>\n",
       "      <th>Ingresos_operacionales</th>\n",
       "      <th>UTILIDAD_NETA</th>\n",
       "      <th>UTILIDAD_BRUTA</th>\n",
       "      <th>UTILIDAD_OPERATIVA</th>\n",
       "      <th>UAI</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>MARGEN_BRUTO</th>\n",
       "      <th>MARGEN_OPERATIVO</th>\n",
       "      <th>MARGEN_NETO</th>\n",
       "      <th>MARGEN_EBITDA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>1</td>\n",
       "      <td>01 Enero</td>\n",
       "      <td>3.195952e+08</td>\n",
       "      <td>9.763239e+08</td>\n",
       "      <td>9327004.69</td>\n",
       "      <td>1.472901e+08</td>\n",
       "      <td>-13005574.15</td>\n",
       "      <td>-1.651179e+09</td>\n",
       "      <td>-2.116482e+08</td>\n",
       "      <td>-6.748549e+08</td>\n",
       "      <td>-3.552598e+08</td>\n",
       "      <td>-3.589383e+08</td>\n",
       "      <td>-4.724733e+08</td>\n",
       "      <td>0.408711</td>\n",
       "      <td>0.215155</td>\n",
       "      <td>0.128180</td>\n",
       "      <td>0.286143</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>2</td>\n",
       "      <td>02 Febrero</td>\n",
       "      <td>4.071303e+08</td>\n",
       "      <td>1.198039e+09</td>\n",
       "      <td>9734716.72</td>\n",
       "      <td>3.473190e+07</td>\n",
       "      <td>-17202513.68</td>\n",
       "      <td>-1.687733e+09</td>\n",
       "      <td>-5.529979e+07</td>\n",
       "      <td>-4.896942e+08</td>\n",
       "      <td>-8.256390e+07</td>\n",
       "      <td>-9.003169e+07</td>\n",
       "      <td>-1.962205e+08</td>\n",
       "      <td>0.290149</td>\n",
       "      <td>0.048920</td>\n",
       "      <td>0.032766</td>\n",
       "      <td>0.116263</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>3</td>\n",
       "      <td>03 Marzo</td>\n",
       "      <td>5.708849e+08</td>\n",
       "      <td>9.974558e+08</td>\n",
       "      <td>9204173.93</td>\n",
       "      <td>2.575872e+08</td>\n",
       "      <td>-19427065.34</td>\n",
       "      <td>-2.194449e+09</td>\n",
       "      <td>-3.787438e+08</td>\n",
       "      <td>-1.196993e+09</td>\n",
       "      <td>-6.261081e+08</td>\n",
       "      <td>-6.363310e+08</td>\n",
       "      <td>-7.477065e+08</td>\n",
       "      <td>0.545464</td>\n",
       "      <td>0.285315</td>\n",
       "      <td>0.172592</td>\n",
       "      <td>0.340726</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2022</td>\n",
       "      <td>4</td>\n",
       "      <td>04 Abril</td>\n",
       "      <td>6.463163e+08</td>\n",
       "      <td>8.992648e+08</td>\n",
       "      <td>18597909.81</td>\n",
       "      <td>5.408471e+07</td>\n",
       "      <td>-18331855.21</td>\n",
       "      <td>-1.676441e+09</td>\n",
       "      <td>-7.650891e+07</td>\n",
       "      <td>-7.771759e+08</td>\n",
       "      <td>-1.308597e+08</td>\n",
       "      <td>-1.305936e+08</td>\n",
       "      <td>-2.494170e+08</td>\n",
       "      <td>0.463587</td>\n",
       "      <td>0.078058</td>\n",
       "      <td>0.045638</td>\n",
       "      <td>0.148778</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Mes PartitionKey  RowKey         MES  Gastos_Administrativos  \\\n",
       "0           2022       1    01 Enero            3.195952e+08   \n",
       "1           2022       2  02 Febrero            4.071303e+08   \n",
       "2           2022       3    03 Marzo            5.708849e+08   \n",
       "3           2022       4    04 Abril            6.463163e+08   \n",
       "\n",
       "Mes  Gastos_Operacionales  Gastos_no_operacionales  Impuesto_Renta  \\\n",
       "0            9.763239e+08               9327004.69    1.472901e+08   \n",
       "1            1.198039e+09               9734716.72    3.473190e+07   \n",
       "2            9.974558e+08               9204173.93    2.575872e+08   \n",
       "3            8.992648e+08              18597909.81    5.408471e+07   \n",
       "\n",
       "Mes  Ingresos_no_operacionales  Ingresos_operacionales  UTILIDAD_NETA  \\\n",
       "0                 -13005574.15           -1.651179e+09  -2.116482e+08   \n",
       "1                 -17202513.68           -1.687733e+09  -5.529979e+07   \n",
       "2                 -19427065.34           -2.194449e+09  -3.787438e+08   \n",
       "3                 -18331855.21           -1.676441e+09  -7.650891e+07   \n",
       "\n",
       "Mes  UTILIDAD_BRUTA  UTILIDAD_OPERATIVA           UAI        EBITDA  \\\n",
       "0     -6.748549e+08       -3.552598e+08 -3.589383e+08 -4.724733e+08   \n",
       "1     -4.896942e+08       -8.256390e+07 -9.003169e+07 -1.962205e+08   \n",
       "2     -1.196993e+09       -6.261081e+08 -6.363310e+08 -7.477065e+08   \n",
       "3     -7.771759e+08       -1.308597e+08 -1.305936e+08 -2.494170e+08   \n",
       "\n",
       "Mes  MARGEN_BRUTO  MARGEN_OPERATIVO  MARGEN_NETO  MARGEN_EBITDA  \n",
       "0        0.408711          0.215155     0.128180       0.286143  \n",
       "1        0.290149          0.048920     0.032766       0.116263  \n",
       "2        0.545464          0.285315     0.172592       0.340726  \n",
       "3        0.463587          0.078058     0.045638       0.148778  "
      ]
     },
     "execution_count": 14,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabla2=pd.concat([calculos(df_enero,con,1),calculos(df_febrero,con,2),calculos(df_marzo,con,3),calculos(df_abril,con,4)],\n",
    "                axis=0,ignore_index=True)\n",
    "\n",
    "\n",
    "tabla2"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 15,
   "id": "9de1a571",
   "metadata": {},
   "outputs": [],
   "source": [
    "vwNewIndicadoresGeneral=tabla2\n",
    "vwNewIndicadoresGeneral.to_csv('vwNewIndicadoresGeneral.csv', index=False)"
   ]
  },
  {
   "cell_type": "markdown",
   "id": "24f5c826",
   "metadata": {},
   "source": [
    "La siguiente línea ejecuta las funciones para la carga de información de mayo, junio y julio:"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 19,
   "id": "6978c1a7",
   "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>Mes</th>\n",
       "      <th>PartitionKey</th>\n",
       "      <th>RowKey</th>\n",
       "      <th>MES</th>\n",
       "      <th>Gastos_Administrativos</th>\n",
       "      <th>Gastos_Operacionales</th>\n",
       "      <th>Gastos_no_operacionales</th>\n",
       "      <th>Impuesto_Renta</th>\n",
       "      <th>Ingresos_no_operacionales</th>\n",
       "      <th>Ingresos_operacionales</th>\n",
       "      <th>UTILIDAD_NETA</th>\n",
       "      <th>UTILIDAD_BRUTA</th>\n",
       "      <th>UTILIDAD_OPERATIVA</th>\n",
       "      <th>UAI</th>\n",
       "      <th>EBITDA</th>\n",
       "      <th>MARGEN_BRUTO</th>\n",
       "      <th>MARGEN_OPERATIVO</th>\n",
       "      <th>MARGEN_NETO</th>\n",
       "      <th>MARGEN_EBITDA</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2022</td>\n",
       "      <td>5</td>\n",
       "      <td>05 Mayo</td>\n",
       "      <td>4.783152e+08</td>\n",
       "      <td>9.706309e+08</td>\n",
       "      <td>9894111.36</td>\n",
       "      <td>1.683606e+08</td>\n",
       "      <td>-42852169.46</td>\n",
       "      <td>-1.820043e+09</td>\n",
       "      <td>-2.356946e+08</td>\n",
       "      <td>-8.494124e+08</td>\n",
       "      <td>-3.710972e+08</td>\n",
       "      <td>-4.040553e+08</td>\n",
       "      <td>-4.921517e+08</td>\n",
       "      <td>0.466699</td>\n",
       "      <td>0.203895</td>\n",
       "      <td>0.129499</td>\n",
       "      <td>0.270407</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2022</td>\n",
       "      <td>6</td>\n",
       "      <td>06 Junio</td>\n",
       "      <td>6.690979e+08</td>\n",
       "      <td>8.676537e+08</td>\n",
       "      <td>28999646.18</td>\n",
       "      <td>-9.622601e+07</td>\n",
       "      <td>-39754968.68</td>\n",
       "      <td>-1.308140e+09</td>\n",
       "      <td>1.216301e+08</td>\n",
       "      <td>-4.404864e+08</td>\n",
       "      <td>2.286115e+08</td>\n",
       "      <td>2.178562e+08</td>\n",
       "      <td>1.044684e+08</td>\n",
       "      <td>0.336727</td>\n",
       "      <td>-0.174761</td>\n",
       "      <td>-0.092979</td>\n",
       "      <td>-0.079860</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2022</td>\n",
       "      <td>7</td>\n",
       "      <td>07 Julio</td>\n",
       "      <td>5.630262e+08</td>\n",
       "      <td>1.125923e+09</td>\n",
       "      <td>22781144.86</td>\n",
       "      <td>2.502536e+07</td>\n",
       "      <td>-40313186.54</td>\n",
       "      <td>-1.720966e+09</td>\n",
       "      <td>-2.452345e+07</td>\n",
       "      <td>-5.950429e+08</td>\n",
       "      <td>-3.201677e+07</td>\n",
       "      <td>-4.954881e+07</td>\n",
       "      <td>-1.656834e+08</td>\n",
       "      <td>0.345761</td>\n",
       "      <td>0.018604</td>\n",
       "      <td>0.014250</td>\n",
       "      <td>0.096274</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "</div>"
      ],
      "text/plain": [
       "Mes PartitionKey  RowKey       MES  Gastos_Administrativos  \\\n",
       "0           2022       5   05 Mayo            4.783152e+08   \n",
       "1           2022       6  06 Junio            6.690979e+08   \n",
       "2           2022       7  07 Julio            5.630262e+08   \n",
       "\n",
       "Mes  Gastos_Operacionales  Gastos_no_operacionales  Impuesto_Renta  \\\n",
       "0            9.706309e+08               9894111.36    1.683606e+08   \n",
       "1            8.676537e+08              28999646.18   -9.622601e+07   \n",
       "2            1.125923e+09              22781144.86    2.502536e+07   \n",
       "\n",
       "Mes  Ingresos_no_operacionales  Ingresos_operacionales  UTILIDAD_NETA  \\\n",
       "0                 -42852169.46           -1.820043e+09  -2.356946e+08   \n",
       "1                 -39754968.68           -1.308140e+09   1.216301e+08   \n",
       "2                 -40313186.54           -1.720966e+09  -2.452345e+07   \n",
       "\n",
       "Mes  UTILIDAD_BRUTA  UTILIDAD_OPERATIVA           UAI        EBITDA  \\\n",
       "0     -8.494124e+08       -3.710972e+08 -4.040553e+08 -4.921517e+08   \n",
       "1     -4.404864e+08        2.286115e+08  2.178562e+08  1.044684e+08   \n",
       "2     -5.950429e+08       -3.201677e+07 -4.954881e+07 -1.656834e+08   \n",
       "\n",
       "Mes  MARGEN_BRUTO  MARGEN_OPERATIVO  MARGEN_NETO  MARGEN_EBITDA  \n",
       "0        0.466699          0.203895     0.129499       0.270407  \n",
       "1        0.336727         -0.174761    -0.092979      -0.079860  \n",
       "2        0.345761          0.018604     0.014250       0.096274  "
      ]
     },
     "execution_count": 19,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "tabla3=pd.concat([calculos(df_mayo,con,5),calculos(df_junio,con,6),calculos(df_julio,con,7)],axis=0,ignore_index=True)\n",
    "tabla3"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 20,
   "id": "bf99b765",
   "metadata": {},
   "outputs": [],
   "source": [
    "tabla3.to_csv('vwNewIndicadoresGeneralMay_July.csv', index=False)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "id": "bb5c8968",
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "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.9.12"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}