1_IMPUTATION_INPUT.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Packages"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "from glob import glob\n",
    "import numpy as np\n",
    "from sklearn.experimental import enable_iterative_imputer\n",
    "from sklearn.impute import IterativeImputer\n",
    "from sklearn.impute import KNNImputer"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## Import DF"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stderr",
     "output_type": "stream",
     "text": [
      "<ipython-input-2-1a9e6611e4c9>:19: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = dfg.groupby('Country Code').apply(impute_interpolate)\n",
      "<ipython-input-2-1a9e6611e4c9>:23: FutureWarning: Dropping of nuisance columns in rolling operations is deprecated; in a future version this will raise TypeError. Select only valid columns before calling the operation. Dropped columns were Index(['Country Code'], dtype='object')\n",
      "  return group.fillna(group.rolling(window, min_periods=1).mean())\n",
      "<ipython-input-2-1a9e6611e4c9>:25: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = grouped.apply(lambda group: fillna_with_moving_average(group, window=3))\n",
      "c:\\Users\\War\\anaconda3\\lib\\site-packages\\sklearn\\impute\\_iterative.py:685: ConvergenceWarning: [IterativeImputer] Early stopping criterion not reached.\n",
      "  warnings.warn(\"[IterativeImputer] Early stopping criterion not\"\n",
      "<ipython-input-2-1a9e6611e4c9>:19: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = dfg.groupby('Country Code').apply(impute_interpolate)\n",
      "<ipython-input-2-1a9e6611e4c9>:23: FutureWarning: Dropping of nuisance columns in rolling operations is deprecated; in a future version this will raise TypeError. Select only valid columns before calling the operation. Dropped columns were Index(['Country Code'], dtype='object')\n",
      "  return group.fillna(group.rolling(window, min_periods=1).mean())\n",
      "<ipython-input-2-1a9e6611e4c9>:25: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = grouped.apply(lambda group: fillna_with_moving_average(group, window=3))\n",
      "<ipython-input-2-1a9e6611e4c9>:19: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = dfg.groupby('Country Code').apply(impute_interpolate)\n",
      "<ipython-input-2-1a9e6611e4c9>:23: FutureWarning: Dropping of nuisance columns in rolling operations is deprecated; in a future version this will raise TypeError. Select only valid columns before calling the operation. Dropped columns were Index(['Country Code'], dtype='object')\n",
      "  return group.fillna(group.rolling(window, min_periods=1).mean())\n",
      "<ipython-input-2-1a9e6611e4c9>:25: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = grouped.apply(lambda group: fillna_with_moving_average(group, window=3))\n",
      "<ipython-input-2-1a9e6611e4c9>:19: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = dfg.groupby('Country Code').apply(impute_interpolate)\n",
      "<ipython-input-2-1a9e6611e4c9>:23: FutureWarning: Dropping of nuisance columns in rolling operations is deprecated; in a future version this will raise TypeError. Select only valid columns before calling the operation. Dropped columns were Index(['Country Code'], dtype='object')\n",
      "  return group.fillna(group.rolling(window, min_periods=1).mean())\n",
      "<ipython-input-2-1a9e6611e4c9>:25: FutureWarning: Not prepending group keys to the result index of transform-like apply. In the future, the group keys will be included in the index, regardless of whether the applied function returns a like-indexed object.\n",
      "To preserve the previous behavior, use\n",
      "\n",
      "\t>>> .groupby(..., group_keys=False)\n",
      "\n",
      "To adopt the future behavior and silence this warning, use \n",
      "\n",
      "\t>>> .groupby(..., group_keys=True)\n",
      "  dfg_imputed = grouped.apply(lambda group: fillna_with_moving_average(group, window=3))\n"
     ]
    }
   ],
   "source": [
    "gastos = ['%deEjecución',\n",
    " '(USDpercapitanominal)',\n",
    " 'Clasificaciónfuncional(%delPIB',\n",
    " 'Clasificaciónfuncional(%delTot']\n",
    "\n",
    "\n",
    "\n",
    "for gasto in gastos:\n",
    "    #### Interpolation and Moving Average Imputation\n",
    "\n",
    "    writer = pd.ExcelWriter(f'0_INPUT//{gasto}.xlsx', engine='xlsxwriter')\n",
    "\n",
    "    dfg = pd.read_excel(\"0_INPUT//INPUT.xlsx\", sheet_name=f\"{gasto}\")\n",
    "    dfg = dfg.replace(0, np.nan)\n",
    "\n",
    "\n",
    "    def impute_interpolate(series):\n",
    "        return series.interpolate(limit_direction='both')\n",
    "    dfg_imputed = dfg.groupby('Country Code').apply(impute_interpolate)\n",
    "\n",
    "\n",
    "    def fillna_with_moving_average(group, window):\n",
    "        return group.fillna(group.rolling(window, min_periods=1).mean())\n",
    "    grouped = dfg_imputed.groupby('Country Code')\n",
    "    dfg_imputed = grouped.apply(lambda group: fillna_with_moving_average(group, window=3))\n",
    "\n",
    "    dfg_imputed.to_excel(writer, sheet_name=\"INTERPOLATION\", index = False)\n",
    "\n",
    "\n",
    "    #### Multivariate chains\n",
    "    # Create the IterativeImputer object\n",
    "    imputer = IterativeImputer(max_iter=10, random_state=0)\n",
    "\n",
    "    # Define the columns to exclude during imputation\n",
    "    exclude_columns = ['Time', 'Country Code']\n",
    "\n",
    "    # Select only the columns with data that you want to impute\n",
    "    df_to_impute_ce = dfg_imputed.drop(columns=exclude_columns)\n",
    "\n",
    "    # Identify and store the columns with all values as NaN\n",
    "    nan_cols = df_to_impute_ce.columns[df_to_impute_ce.isna().all()].tolist()\n",
    "    nan_cols_positions = [df_to_impute_ce.columns.get_loc(col) for col in nan_cols]\n",
    "\n",
    "    # Remove the columns with all values as NaN\n",
    "    df_to_impute_ce = df_to_impute_ce.dropna(axis=1, how='all')\n",
    "\n",
    "    # Fit and transform the data for imputation\n",
    "    df_imputed_ce = imputer.fit_transform(df_to_impute_ce)\n",
    "\n",
    "    # Convert the resulting array back to a DataFrame, using only the imputed columns\n",
    "    df_imputed_ce = pd.DataFrame(df_imputed_ce, columns=df_to_impute_ce.columns)\n",
    "\n",
    "    # Insert back the columns with all values as NaN in their original positions\n",
    "    for col, pos in zip(nan_cols, nan_cols_positions):\n",
    "        df_imputed_ce.insert(pos, col, dfg_imputed[col])\n",
    "\n",
    "    # Reinsert the panel identification columns\n",
    "    df_imputed_ce[exclude_columns] = dfg_imputed[exclude_columns]\n",
    "    df_imputed_ce[nan_cols] = dfg_imputed[nan_cols]\n",
    "    df_imputed_ce = df_imputed_ce[list(dfg_imputed.columns)]\n",
    "    \n",
    "\n",
    "    # Seleccionar las celdas con valores entre 200 y 1000 y dividirlos por 10\n",
    "\n",
    "    if gasto == '%deEjecución':\n",
    "    \n",
    "        # Obtener las columnas que deben ser transformadas (excluyendo 'Time' y 'Country Code')\n",
    "        columns_to_transform = [col for col in df_imputed_ce.columns if col not in ['Time', 'Country Code']]\n",
    "\n",
    "        # Seleccionar las celdas con valores entre 200 y 1000 y dividirlos por 10\n",
    "        mask1 = (df_imputed_ce[columns_to_transform] > 200) & (df_imputed_ce[columns_to_transform] <= 1000)\n",
    "        for column in columns_to_transform:\n",
    "            df_imputed_ce.loc[mask1[column], column] /= 10\n",
    "\n",
    "        # Seleccionar las celdas con valores mayores a 1000 y dividirlos por 100\n",
    "        mask2 = df_imputed_ce[columns_to_transform] > 1000\n",
    "        for column in columns_to_transform:\n",
    "            df_imputed_ce.loc[mask2[column], column] /= 100\n",
    "\n",
    "\n",
    "\n",
    "    df_imputed_ce.to_excel(writer, sheet_name=\"CHAINS\", index = False)\n",
    "\n",
    "    #### KNN\n",
    "    # Create the KNNImputer object with desired settings\n",
    "    imputer = KNNImputer(n_neighbors=5, weights='uniform', metric='nan_euclidean', copy=True, add_indicator=False)\n",
    "\n",
    "    # Define the columns to exclude during imputation\n",
    "    exclude_columns = ['Time', 'Country Code']\n",
    "\n",
    "    # Identify and store the columns with all values as NaN\n",
    "    nan_cols = dfg_imputed.columns[dfg_imputed.isna().all()].tolist()\n",
    "\n",
    "    # Select only the columns with data to impute\n",
    "    df_to_impute = dfg_imputed.drop(columns=exclude_columns + nan_cols)\n",
    "\n",
    "    # Remove the columns with all values as NaN\n",
    "    df_to_impute = df_to_impute.dropna(axis=1, how='all')\n",
    "\n",
    "    # Perform imputation by fitting and transforming the data\n",
    "    df_imputed = pd.DataFrame(imputer.fit_transform(df_to_impute), columns=df_to_impute.columns)\n",
    "\n",
    "    # Reinsert the excluded columns and rearrange the columns to match the original DataFrame\n",
    "    df_imputed[exclude_columns] = dfg_imputed[exclude_columns]\n",
    "    df_imputed[nan_cols] = dfg_imputed[nan_cols]\n",
    "    df_imputed = df_imputed[list(dfg_imputed.columns)]\n",
    "\n",
    "\n",
    "    # Obtener las columnas que deben ser transformadas (excluyendo 'Time' y 'Country Code')\n",
    "    columns_to_transform = [col for col in df_imputed.columns if col not in ['Time', 'Country Code']]\n",
    "\n",
    "\n",
    "    if gasto == '%deEjecución':\n",
    "\n",
    "        # Seleccionar las celdas con valores entre 200 y 1000 y dividirlos por 10\n",
    "        mask1 = (df_imputed[columns_to_transform] > 200) & (df_imputed[columns_to_transform] <= 1000)\n",
    "        for column in columns_to_transform:\n",
    "            df_imputed.loc[mask1[column], column] /= 10\n",
    "\n",
    "        # Seleccionar las celdas con valores mayores a 1000 y dividirlos por 100\n",
    "        mask2 = df_imputed[columns_to_transform] > 1000\n",
    "        for column in columns_to_transform:\n",
    "            df_imputed.loc[mask2[column], column] /= 100\n",
    "\n",
    "    \n",
    "    df_imputed.to_excel(writer, sheet_name=\"KNN\", index = False)\n",
    "\n",
    "\n",
    "    writer.close()\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Summary"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "\n",
    "# Interpolation and Moving Average Imputation\n",
    "\n",
    "This procedure involves two main steps: interpolation and filling with a moving average. \n",
    "\n",
    "## Interpolation\n",
    "\n",
    "The first step is interpolation, which is used to estimate unknown values that fall between two known values. It's performed on each country group separately. \n",
    "\n",
    "Mathematically, linear interpolation between two points $(x_1, y_1)$ and $(x_2, y_2)$ for a given $x$ is given by the formula:\n",
    "\n",
    "$$y = y_1 + \\frac{(y_2 - y_1)}{(x_2 - x_1)}(x - x_1)$$\n",
    "\n",
    "## Moving Average Imputation\n",
    "\n",
    "The second step involves replacing remaining missing values with a moving average. A simple moving average of a time series $x_1, x_2, ..., x_n$ over a fixed window size $w$ is a series of unweighted means of the previous $w$ data. \n",
    "\n",
    "The formula for the moving average $MA$ over window size $w$ is:\n",
    "\n",
    "$$MA = \\frac{1}{w}(x_1 + x_2 + ... + x_w)$$\n",
    "\n",
    "This means we calculate the mean of the preceding $w$ data points. If a data point is missing in this window, it will be filled with the moving average calculated over this window. This process is repeated for all data points in the time series, and for each country separately, taking into account the panel data structure.\n",
    "\"\"\""
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Multivariate Imputation by Chained Equations (MICE)\n",
    "\n",
    "### 1. Initialization\n",
    "\n",
    "In the initialization step, we fill missing values with a simple statistic such as the mean. If we represent our data as a matrix $X$ of size $n$ x $m$ (where $n$ is the number of samples and $m$ is the number of features), and we denote by $X_{missing}$ the subset of $X$ that contains missing values, we could write:\n",
    "\n",
    "$$\n",
    "X_{missing}^{(0)} = mean(X_{observed})\n",
    "$$\n",
    "\n",
    "Where $X_{missing}^{(0)}$ represents the initial imputed values for the missing data, and $mean(X_{observed})$ is the mean of the observed (non-missing) values in $X$.\n",
    "\n",
    "### 2. Imputation\n",
    "\n",
    "For each feature $X_i$ with missing data, we create a regression model using the observed part of $X_i$ as the target variable and all the other features (with the imputed values for the current iteration for the missing parts) as the input variables. Let's denote by $X_{-i}$ the matrix obtained from $X$ by removing the column $i$. The prediction step could be written as follows:\n",
    "\n",
    "$$\n",
    "X_{i,missing}^{(k)} = model(X_{-i,observed}, X_{i,observed}).predict(X_{-i,missing})\n",
    "$$\n",
    "\n",
    "Where:\n",
    "- $X_{i,missing}^{(k)}$ is the imputed values for the missing part of feature $X_i$ at iteration $k$.\n",
    "- $model(X_{-i,observed}, X_{i,observed})$ is a model (such as a linear regression or a decision tree) trained on the observed part of the data.\n",
    "- $X_{-i,missing}$ is the part of the data that is used to make predictions for the missing values in $X_i$.\n",
    "\n",
    "### 3. Iteration\n",
    "\n",
    "We repeat the imputation step a fixed number of times $K$, to build variability in the imputations.\n",
    "\n",
    "### 4. Analysis\n",
    "\n",
    "After the final round of imputation, we analyze each \"completed\" dataset separately. This could be any type of analysis, from simple descriptive statistics to complex machine learning models.\n",
    "\n",
    "### 5. Pooling\n",
    "\n",
    "The results of the multiple analyses are combined into one final result. This is typically done using Rubin's rules, which compute the final estimate as the mean of the estimates from each imputed dataset, and the variance as a combination of the within-imputation variance (the average of the variances from each imputed dataset) and the between-imputation variance (the variance of the estimates from each imputed dataset).\n",
    "\n",
    "For an estimate $\\theta$ computed on each imputed dataset, we have:\n",
    "\n",
    "$$\n",
    "\\bar{\\theta} = \\frac{1}{K}\\sum_{k=1}^{K}\\theta^{(k)}\n",
    "$$\n",
    "\n",
    "$$\n",
    "Var(\\bar{\\theta}) = \\frac{1}{K}\\sum_{k=1}^{K}Var(\\theta^{(k)}) + \\frac{1 + 1/K}{K} \\sum_{k=1}^{K}(\\theta^{(k)} - \\bar{\\theta})^2\n",
    "$$\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "## K-Nearest Neighbors (KNN) Imputation\n",
    "\n",
    "K-Nearest Neighbors (KNN) imputation is a method used to fill missing values in a dataset by estimating them based on the values of their neighboring data points. It is a non-parametric imputation technique that relies on similarity between samples to impute missing values. Here's a step-by-step explanation of the KNN imputation process:\n",
    "\n",
    "1. Determine the Number of Neighbors (K)\n",
    "\n",
    "   - The first step in KNN imputation is to determine the number of neighbors (K) to consider when imputing a missing value. The choice of K is crucial and depends on the dataset and problem at hand. It is typically selected based on empirical evaluation or domain knowledge.\n",
    "\n",
    "2. Find the K Nearest Neighbors\n",
    "\n",
    "   - For each sample with missing values, the algorithm identifies the K nearest neighbors based on some similarity metric (e.g., Euclidean distance, Manhattan distance). The neighbors are selected from the samples with complete information for the feature being imputed.\n",
    "\n",
    "   The KNN similarity equation can be written as:\n",
    "\n",
    "   $$\n",
    "   \\text{Similarity} = \\frac{1}{1 + d(X_i, X_j)}\n",
    "   $$\n",
    "\n",
    "   Where:\n",
    "   - $X_i$ represents the sample with missing values.\n",
    "   - $X_j$ represents the samples with complete information.\n",
    "   - $d$ is the similarity metric (e.g., Euclidean distance, Manhattan distance).\n",
    "\n",
    "3. Impute the Missing Value\n",
    "\n",
    "   - Once the K nearest neighbors are identified, the missing value is imputed by aggregating the values of the corresponding feature from the neighbors. The imputation can be done by taking the mean, median, or some other measure of central tendency of the neighbor values.\n",
    "\n",
    "   The imputation equation can be written as:\n",
    "\n",
    "   $$\n",
    "   X_{\\text{missing}}^{(0)} = \\text{mean}(X_{\\text{observed}})\n",
    "   $$\n",
    "\n",
    "   Where:\n",
    "   - $X_{\\text{missing}}^{(0)}$ represents the initial imputed values for the missing data.\n",
    "   - $\\text{mean}(X_{\\text{observed}})$ is the mean of the observed (non-missing) values in $X$.\n",
    "\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "| Imputation Method                                 | Advantages                                                                                                                                     | Disadvantages                                                                                                              |\n",
    "| ------------------------------------------------ | ---------------------------------------------------------------------------------------------------------------------------------------------- | -------------------------------------------------------------------------------------------------------------------------- |\n",
    "| Multivariate Imputation by Chained Equations (MICE) | - Handles missing data in multiple variables simultaneously                                                                                    | - Requires specifying the imputation model for each variable                                                              |\n",
    "|                                                  | - Preserves the correlations between variables during imputation                                                                                | - Relatively computationally expensive                                                                                      |\n",
    "|                                                  | - Can handle different types of variables (e.g., continuous, categorical)                                                                      | - May introduce additional variability due to the random imputation process                                                  |\n",
    "|                                                  | - Provides estimates of uncertainty through multiple imputations and pooling techniques (e.g., Rubin's rules)                                    | - Assumes Missing at Random (MAR) mechanism                                                                                  |\n",
    "|                                                  | - Can be used with various imputation models (e.g., linear regression, decision trees)                                                         |                                                                                                                            |\n",
    "|                                                  | - Allows flexibility in handling non-linear relationships and interactions between variables                                                  |                                                                                                                            |\n",
    "| K-Nearest Neighbors (KNN) Imputation                 | - Simple and intuitive approach                                                                                                                | - Requires determining the number of neighbors (K)                                                                           |\n",
    "|                                                  | - Preserves local data patterns and relationships during imputation                                                                             | - Sensitive to the choice of similarity metric and distance measure                                                         |\n",
    "|                                                  | - Can handle different types of variables (e.g., continuous, categorical)                                                                      | - Computationally expensive for large datasets or high-dimensional data                                                     |\n",
    "|                                                  | - Does not assume any specific distribution or model for the data                                                                                | - May produce biased imputations if the nearest neighbors are not truly similar to the missing sample                        |\n",
    "|                                                  | - Can be combined with other imputation methods or used as a preprocessing step                                                                 | - Missingness in one variable may affect the imputations of other variables due to their interdependencies                  |\n",
    "|                                                  | - Allows flexibility in choosing the imputation strategy (e.g., mean, median, mode, etc.)                                                      |                                                                                                                            |\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": null,
   "metadata": {},
   "outputs": [],
   "source": []
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "base",
   "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.5"
  },
  "orig_nbformat": 4
 },
 "nbformat": 4,
 "nbformat_minor": 2
}