preprocessing.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [],
   "source": [
    "import pandas as pd\n",
    "import numpy as np\n",
    "import warnings\n",
    "from matplotlib import pyplot as plt\n",
    "from datasetsforecast.losses import rmse\n",
    "from sklearn.metrics import mean_absolute_error\n",
    "from sklearn.ensemble import RandomForestRegressor\n",
    "from xgboost import XGBRegressor\n",
    "from catboost import CatBoostRegressor\n",
    "\n",
    "warnings.filterwarnings(\"ignore\")\n",
    "\n",
    "# Helper functions\n",
    "def plot(df, cv, model_name):\n",
    "    plt.figure(figsize=(10, 6))\n",
    "    plt.plot(df['ds'], df['y'], label='Actual', marker='o', linestyle='-', color='black')\n",
    "    plt.plot(cv['ds'], cv[model_name], label=f'{model_name} Predictions', marker='x', linestyle='--')\n",
    "    plt.title(f\"Actual vs. {model_name} Predictions\")\n",
    "    plt.xlabel('Date')\n",
    "    plt.ylabel('Value')\n",
    "    plt.legend()\n",
    "    plt.show()\n",
    "\n",
    "def clean_columns(feature_names):\n",
    "    replace_map = {\n",
    "        '[': '', ']': '',  \n",
    "        '/': '_',         \n",
    "        '\"': '',          \n",
    "        '{': '', '}': '', \n",
    "        ':': '_',         \n",
    "        ',': '_',         \n",
    "        ' ': '_',          \n",
    "        '(': '_',         \n",
    "        ')': '_',         \n",
    "        '<': '',          \n",
    "    }\n",
    "    \n",
    "    cleaned_names = []\n",
    "    for name in feature_names:\n",
    "        for old_char, new_char in replace_map.items():\n",
    "            name = name.replace(old_char, new_char)\n",
    "        name = ''.join(char if char.isalnum() else ' ' for char in name.lower())\n",
    "        name = ' '.join(name.split())\n",
    "        cleaned_names.append(name)\n",
    "    return cleaned_names\n",
    "\n",
    "# Load and preprocess macro data\n",
    "start_date = '2009-06-01'\n",
    "macro_data = pd.read_excel(\"macro_data.xlsx\")\n",
    "macro_data = macro_data.drop(0)\n",
    "macro_data = macro_data.rename(columns={macro_data.columns[0]: 'ds'})\n",
    "macro_data['ds'] = pd.to_datetime(macro_data['ds'], format='%Y-%m-%d')\n",
    "macro_data = macro_data.fillna(method='ffill')\n",
    "macro_data = macro_data[macro_data['ds'] >= start_date]\n",
    "macro_data = macro_data.fillna(0)\n",
    "\n",
    "# Load and preprocess target price data\n",
    "target_data = pd.read_excel(\"target_rice_data.xlsx\", sheet_name=\"Monthly Prices\", header=4)\n",
    "target_data = target_data.rename(columns={target_data.columns[0]: 'ds'})\n",
    "target_data = target_data[['ds', 'Rice, Viet Namese 5%']]\n",
    "target_data = target_data.drop([0, 1])\n",
    "target_data['ds'] = target_data['ds'].str.replace('M', '-')\n",
    "target_data['ds'] = target_data['ds'] + '-01'\n",
    "target_data['ds'] = pd.to_datetime(target_data['ds'], format='%Y-%m-%d')\n",
    "target_data['Rice, Viet Namese 5%'] = pd.to_numeric(target_data['Rice, Viet Namese 5%'], errors='coerce')\n",
    "target_data = target_data[target_data['ds'] >= start_date]\n",
    "\n",
    "# Merge data and prepare final dataframes\n",
    "df = pd.merge(target_data, macro_data, on='ds', how='inner')\n",
    "df = df.rename(columns={'Rice, Viet Namese 5%': 'y'})\n",
    "df = df.sort_values(by='ds', ascending=True)\n",
    "\n",
    "# Prepare feature matrix X and target vector y\n",
    "X = df.drop(['ds', 'y'], axis=1)\n",
    "X = X.fillna(0)\n",
    "X.columns = clean_columns(X.columns)\n",
    "y = df['y'].astype(float)\n",
    "y = y.fillna(0)\n",
    "\n",
    "# Create price only dataframe\n",
    "price_only_df = df[['ds', 'y']].copy()\n",
    "price_only_df['y'] = price_only_df['y'].astype(float)\n",
    "price_only_df['unique_id'] = 'Stats_Model'\n",
    "price_only_df['ds'] = price_only_df['ds'] + pd.offsets.MonthEnd(0)\n",
    "price_only_df = price_only_df.fillna(method='ffill')"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "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>ds</th>\n",
       "      <th>y</th>\n",
       "      <th>unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-06-30</td>\n",
       "      <td>404.2850</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-07-31</td>\n",
       "      <td>410.2280</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>395.7175</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>392.8580</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-10-31</td>\n",
       "      <td>414.1075</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>616.1500</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>629.4100</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>632.9000</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>625.5100</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>614.2600</td>\n",
       "      <td>Stats_Model</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>177 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ds         y    unique_id\n",
       "0   2009-06-30  404.2850  Stats_Model\n",
       "1   2009-07-31  410.2280  Stats_Model\n",
       "2   2009-08-31  395.7175  Stats_Model\n",
       "3   2009-09-30  392.8580  Stats_Model\n",
       "4   2009-10-31  414.1075  Stats_Model\n",
       "..         ...       ...          ...\n",
       "172 2023-10-31  616.1500  Stats_Model\n",
       "173 2023-11-30  629.4100  Stats_Model\n",
       "174 2023-12-31  632.9000  Stats_Model\n",
       "175 2024-01-31  625.5100  Stats_Model\n",
       "176 2024-02-29  614.2600  Stats_Model\n",
       "\n",
       "[177 rows x 3 columns]"
      ]
     },
     "execution_count": 2,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "price_only_df"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "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>ds</th>\n",
       "      <th>y</th>\n",
       "      <th>industrial production index</th>\n",
       "      <th>ipi mining and quarrying mq</th>\n",
       "      <th>ipi manufacturing</th>\n",
       "      <th>industrial production index computer electronic and optical products</th>\n",
       "      <th>ipi electricity and gas eg</th>\n",
       "      <th>industrial production ytd coal</th>\n",
       "      <th>industrial production ytd electricity</th>\n",
       "      <th>industrial production index 1</th>\n",
       "      <th>...</th>\n",
       "      <th>no of tourist arrivals</th>\n",
       "      <th>freight carried ytd railway</th>\n",
       "      <th>freight carried ytd maritime transport</th>\n",
       "      <th>freight carried ytd road</th>\n",
       "      <th>freight carried ytd airway</th>\n",
       "      <th>no of passengers carried ytd railway</th>\n",
       "      <th>no of passengers carried ytd maritime transport</th>\n",
       "      <th>no of passengers carried ytd road</th>\n",
       "      <th>no of passengers carried ytd airway</th>\n",
       "      <th>unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-06-30</td>\n",
       "      <td>404.2850</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>21351.800000</td>\n",
       "      <td>37600.000000</td>\n",
       "      <td>90.680349</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3945.800</td>\n",
       "      <td>21281.500000</td>\n",
       "      <td>2.259892e+05</td>\n",
       "      <td>61.200000</td>\n",
       "      <td>5565.100</td>\n",
       "      <td>3044.600000</td>\n",
       "      <td>8.688895e+05</td>\n",
       "      <td>5239.300000</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-07-31</td>\n",
       "      <td>410.2280</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>24752.900000</td>\n",
       "      <td>44900.000000</td>\n",
       "      <td>93.312570</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4631.700</td>\n",
       "      <td>26454.300000</td>\n",
       "      <td>2.649756e+05</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>6863.700</td>\n",
       "      <td>3492.800000</td>\n",
       "      <td>1.017431e+06</td>\n",
       "      <td>6230.800000</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>395.7175</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28689.020093</td>\n",
       "      <td>52310.000000</td>\n",
       "      <td>93.579757</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5403.100</td>\n",
       "      <td>31556.300000</td>\n",
       "      <td>3.048197e+05</td>\n",
       "      <td>79.100000</td>\n",
       "      <td>7792.100</td>\n",
       "      <td>3934.200000</td>\n",
       "      <td>1.167531e+06</td>\n",
       "      <td>7315.100000</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>392.8580</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>31566.100000</td>\n",
       "      <td>62300.000000</td>\n",
       "      <td>94.303088</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6081.300</td>\n",
       "      <td>33318.400000</td>\n",
       "      <td>3.449744e+05</td>\n",
       "      <td>89.800000</td>\n",
       "      <td>8597.200</td>\n",
       "      <td>4381.400000</td>\n",
       "      <td>1.322135e+06</td>\n",
       "      <td>8271.900000</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-10-31</td>\n",
       "      <td>414.1075</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35165.500000</td>\n",
       "      <td>66500.000000</td>\n",
       "      <td>94.862443</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6621.100</td>\n",
       "      <td>36745.900000</td>\n",
       "      <td>3.859084e+05</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>9552.400</td>\n",
       "      <td>4868.200000</td>\n",
       "      <td>1.478455e+06</td>\n",
       "      <td>9104.200000</td>\n",
       "      <td>price_and_macro</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",
       "      <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>172</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>616.1500</td>\n",
       "      <td>162.38</td>\n",
       "      <td>77.84</td>\n",
       "      <td>175.91</td>\n",
       "      <td>251.58</td>\n",
       "      <td>200.67</td>\n",
       "      <td>40636.886564</td>\n",
       "      <td>222730.523024</td>\n",
       "      <td>95.556571</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>3714.727</td>\n",
       "      <td>98276.466406</td>\n",
       "      <td>1.376555e+06</td>\n",
       "      <td>259.906032</td>\n",
       "      <td>5250.421</td>\n",
       "      <td>9858.183725</td>\n",
       "      <td>3.471429e+06</td>\n",
       "      <td>47401.916650</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>629.4100</td>\n",
       "      <td>162.42</td>\n",
       "      <td>77.13</td>\n",
       "      <td>176.36</td>\n",
       "      <td>239.48</td>\n",
       "      <td>197.90</td>\n",
       "      <td>44750.835434</td>\n",
       "      <td>244653.296420</td>\n",
       "      <td>95.556571</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>4108.327</td>\n",
       "      <td>106611.721174</td>\n",
       "      <td>1.575268e+06</td>\n",
       "      <td>292.839442</td>\n",
       "      <td>5672.898</td>\n",
       "      <td>10471.142545</td>\n",
       "      <td>3.887923e+06</td>\n",
       "      <td>51540.354055</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>632.9000</td>\n",
       "      <td>162.61</td>\n",
       "      <td>72.04</td>\n",
       "      <td>177.93</td>\n",
       "      <td>231.25</td>\n",
       "      <td>192.70</td>\n",
       "      <td>48246.408316</td>\n",
       "      <td>266675.857576</td>\n",
       "      <td>95.556571</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>4551.927</td>\n",
       "      <td>116128.106245</td>\n",
       "      <td>1.747256e+06</td>\n",
       "      <td>327.870938</td>\n",
       "      <td>6095.519</td>\n",
       "      <td>11093.694000</td>\n",
       "      <td>4.288059e+06</td>\n",
       "      <td>56032.621065</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>625.5100</td>\n",
       "      <td>155.22</td>\n",
       "      <td>75.31</td>\n",
       "      <td>167.08</td>\n",
       "      <td>201.14</td>\n",
       "      <td>197.93</td>\n",
       "      <td>4028.839775</td>\n",
       "      <td>22503.970018</td>\n",
       "      <td>95.556571</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>410.300</td>\n",
       "      <td>10838.875931</td>\n",
       "      <td>1.504101e+05</td>\n",
       "      <td>36.801353</td>\n",
       "      <td>422.318</td>\n",
       "      <td>1931.176183</td>\n",
       "      <td>3.516843e+05</td>\n",
       "      <td>4148.214000</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>614.2600</td>\n",
       "      <td>127.22</td>\n",
       "      <td>62.61</td>\n",
       "      <td>136.02</td>\n",
       "      <td>169.66</td>\n",
       "      <td>164.09</td>\n",
       "      <td>7121.885696</td>\n",
       "      <td>41017.093270</td>\n",
       "      <td>95.556571</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>607.600</td>\n",
       "      <td>21805.627651</td>\n",
       "      <td>2.984771e+05</td>\n",
       "      <td>76.546814</td>\n",
       "      <td>1254.620</td>\n",
       "      <td>4037.153667</td>\n",
       "      <td>7.331434e+05</td>\n",
       "      <td>9291.999360</td>\n",
       "      <td>price_and_macro</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>177 rows × 120 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ds         y  industrial production index  \\\n",
       "0   2009-06-30  404.2850                         0.00   \n",
       "1   2009-07-31  410.2280                         0.00   \n",
       "2   2009-08-31  395.7175                         0.00   \n",
       "3   2009-09-30  392.8580                         0.00   \n",
       "4   2009-10-31  414.1075                         0.00   \n",
       "..         ...       ...                          ...   \n",
       "172 2023-10-31  616.1500                       162.38   \n",
       "173 2023-11-30  629.4100                       162.42   \n",
       "174 2023-12-31  632.9000                       162.61   \n",
       "175 2024-01-31  625.5100                       155.22   \n",
       "176 2024-02-29  614.2600                       127.22   \n",
       "\n",
       "     ipi mining and quarrying mq  ipi manufacturing  \\\n",
       "0                           0.00               0.00   \n",
       "1                           0.00               0.00   \n",
       "2                           0.00               0.00   \n",
       "3                           0.00               0.00   \n",
       "4                           0.00               0.00   \n",
       "..                           ...                ...   \n",
       "172                        77.84             175.91   \n",
       "173                        77.13             176.36   \n",
       "174                        72.04             177.93   \n",
       "175                        75.31             167.08   \n",
       "176                        62.61             136.02   \n",
       "\n",
       "     industrial production index computer electronic and optical products  \\\n",
       "0                                                 0.00                      \n",
       "1                                                 0.00                      \n",
       "2                                                 0.00                      \n",
       "3                                                 0.00                      \n",
       "4                                                 0.00                      \n",
       "..                                                 ...                      \n",
       "172                                             251.58                      \n",
       "173                                             239.48                      \n",
       "174                                             231.25                      \n",
       "175                                             201.14                      \n",
       "176                                             169.66                      \n",
       "\n",
       "     ipi electricity and gas eg  industrial production ytd coal  \\\n",
       "0                          0.00                    21351.800000   \n",
       "1                          0.00                    24752.900000   \n",
       "2                          0.00                    28689.020093   \n",
       "3                          0.00                    31566.100000   \n",
       "4                          0.00                    35165.500000   \n",
       "..                          ...                             ...   \n",
       "172                      200.67                    40636.886564   \n",
       "173                      197.90                    44750.835434   \n",
       "174                      192.70                    48246.408316   \n",
       "175                      197.93                     4028.839775   \n",
       "176                      164.09                     7121.885696   \n",
       "\n",
       "     industrial production ytd electricity  industrial production index 1  \\\n",
       "0                             37600.000000                      90.680349   \n",
       "1                             44900.000000                      93.312570   \n",
       "2                             52310.000000                      93.579757   \n",
       "3                             62300.000000                      94.303088   \n",
       "4                             66500.000000                      94.862443   \n",
       "..                                     ...                            ...   \n",
       "172                          222730.523024                      95.556571   \n",
       "173                          244653.296420                      95.556571   \n",
       "174                          266675.857576                      95.556571   \n",
       "175                           22503.970018                      95.556571   \n",
       "176                           41017.093270                      95.556571   \n",
       "\n",
       "     ...  no of tourist arrivals  freight carried ytd railway  \\\n",
       "0    ...                     0.0                     3945.800   \n",
       "1    ...                     0.0                     4631.700   \n",
       "2    ...                     0.0                     5403.100   \n",
       "3    ...                     0.0                     6081.300   \n",
       "4    ...                     0.0                     6621.100   \n",
       "..   ...                     ...                          ...   \n",
       "172  ...                  1468.8                     3714.727   \n",
       "173  ...                  1468.8                     4108.327   \n",
       "174  ...                  1468.8                     4551.927   \n",
       "175  ...                  1468.8                      410.300   \n",
       "176  ...                  1468.8                      607.600   \n",
       "\n",
       "     freight carried ytd maritime transport  freight carried ytd road  \\\n",
       "0                              21281.500000              2.259892e+05   \n",
       "1                              26454.300000              2.649756e+05   \n",
       "2                              31556.300000              3.048197e+05   \n",
       "3                              33318.400000              3.449744e+05   \n",
       "4                              36745.900000              3.859084e+05   \n",
       "..                                      ...                       ...   \n",
       "172                            98276.466406              1.376555e+06   \n",
       "173                           106611.721174              1.575268e+06   \n",
       "174                           116128.106245              1.747256e+06   \n",
       "175                            10838.875931              1.504101e+05   \n",
       "176                            21805.627651              2.984771e+05   \n",
       "\n",
       "     freight carried ytd airway  no of passengers carried ytd railway  \\\n",
       "0                     61.200000                              5565.100   \n",
       "1                     70.000000                              6863.700   \n",
       "2                     79.100000                              7792.100   \n",
       "3                     89.800000                              8597.200   \n",
       "4                    100.000000                              9552.400   \n",
       "..                          ...                                   ...   \n",
       "172                  259.906032                              5250.421   \n",
       "173                  292.839442                              5672.898   \n",
       "174                  327.870938                              6095.519   \n",
       "175                   36.801353                               422.318   \n",
       "176                   76.546814                              1254.620   \n",
       "\n",
       "     no of passengers carried ytd maritime transport  \\\n",
       "0                                        3044.600000   \n",
       "1                                        3492.800000   \n",
       "2                                        3934.200000   \n",
       "3                                        4381.400000   \n",
       "4                                        4868.200000   \n",
       "..                                               ...   \n",
       "172                                      9858.183725   \n",
       "173                                     10471.142545   \n",
       "174                                     11093.694000   \n",
       "175                                      1931.176183   \n",
       "176                                      4037.153667   \n",
       "\n",
       "     no of passengers carried ytd road  no of passengers carried ytd airway  \\\n",
       "0                         8.688895e+05                          5239.300000   \n",
       "1                         1.017431e+06                          6230.800000   \n",
       "2                         1.167531e+06                          7315.100000   \n",
       "3                         1.322135e+06                          8271.900000   \n",
       "4                         1.478455e+06                          9104.200000   \n",
       "..                                 ...                                  ...   \n",
       "172                       3.471429e+06                         47401.916650   \n",
       "173                       3.887923e+06                         51540.354055   \n",
       "174                       4.288059e+06                         56032.621065   \n",
       "175                       3.516843e+05                          4148.214000   \n",
       "176                       7.331434e+05                          9291.999360   \n",
       "\n",
       "           unique_id  \n",
       "0    price_and_macro  \n",
       "1    price_and_macro  \n",
       "2    price_and_macro  \n",
       "3    price_and_macro  \n",
       "4    price_and_macro  \n",
       "..               ...  \n",
       "172  price_and_macro  \n",
       "173  price_and_macro  \n",
       "174  price_and_macro  \n",
       "175  price_and_macro  \n",
       "176  price_and_macro  \n",
       "\n",
       "[177 rows x 120 columns]"
      ]
     },
     "execution_count": 3,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create with_macro_df by joing price_only_df and macro_data on 'ds'. First shift 'ds' of macro_data to end of month\n",
    "#drop unique_id from price_only_df\n",
    "price_only_df = price_only_df.drop(columns=['unique_id'])\n",
    "macro_data['ds'] = macro_data['ds'] + pd.offsets.MonthEnd(0)\n",
    "#join price_only_df and macro_data on 'ds'\n",
    "with_macro_df = pd.merge(price_only_df, macro_data, on='ds', how='inner')\n",
    "with_macro_df.columns = clean_columns(with_macro_df.columns)\n",
    "with_macro_df['unique_id'] = 'price_and_macro'\n",
    "with_macro_df"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "# Process News dataset"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [],
   "source": [
    "# default library imports\n",
    "import os\n",
    "import tempfile\n",
    "import json\n",
    "from collections import Counter\n",
    "from math import sqrt\n",
    "from datetime import datetime"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "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>Date</th>\n",
       "      <th>Analysis</th>\n",
       "      <th>Net Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2024-03-26</td>\n",
       "      <td>[{'global_supply': -80}, {'export_restrict': -...</td>\n",
       "      <td>-190.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2024-03-20</td>\n",
       "      <td>[{'supply': -70}, {'demand': 50}, {'weather': ...</td>\n",
       "      <td>20.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2024-03-14</td>\n",
       "      <td>[{'trade': 70}, {'import_increase': 85}, {'dro...</td>\n",
       "      <td>220.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2024-03-05</td>\n",
       "      <td>[{'trade': 80, 'export_restrict': -40, 'el_nin...</td>\n",
       "      <td>80.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2024-02-28</td>\n",
       "      <td>[{'trade': 70}, {'export_restrict': -30}, {'do...</td>\n",
       "      <td>430.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3276</th>\n",
       "      <td>2008-01-09</td>\n",
       "      <td>[{'rain': -80}, {'landslide': -80}, {'harvest_...</td>\n",
       "      <td>-230.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3277</th>\n",
       "      <td>2008-01-07</td>\n",
       "      <td>[{'production_increase': 80}, {'export_decreas...</td>\n",
       "      <td>-150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3278</th>\n",
       "      <td>2008-01-05</td>\n",
       "      <td>[{'export_restrict': -60}, {'demand': 70}, {'s...</td>\n",
       "      <td>-75.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3279</th>\n",
       "      <td>2008-01-04</td>\n",
       "      <td>[{'trade': 50}, {'export_agreement': 100}]</td>\n",
       "      <td>150.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3280</th>\n",
       "      <td>2008-01-03</td>\n",
       "      <td>[]</td>\n",
       "      <td>0.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>3281 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            Date                                           Analysis  \\\n",
       "0     2024-03-26  [{'global_supply': -80}, {'export_restrict': -...   \n",
       "1     2024-03-20  [{'supply': -70}, {'demand': 50}, {'weather': ...   \n",
       "2     2024-03-14  [{'trade': 70}, {'import_increase': 85}, {'dro...   \n",
       "3     2024-03-05  [{'trade': 80, 'export_restrict': -40, 'el_nin...   \n",
       "4     2024-02-28  [{'trade': 70}, {'export_restrict': -30}, {'do...   \n",
       "...          ...                                                ...   \n",
       "3276  2008-01-09  [{'rain': -80}, {'landslide': -80}, {'harvest_...   \n",
       "3277  2008-01-07  [{'production_increase': 80}, {'export_decreas...   \n",
       "3278  2008-01-05  [{'export_restrict': -60}, {'demand': 70}, {'s...   \n",
       "3279  2008-01-04         [{'trade': 50}, {'export_agreement': 100}]   \n",
       "3280  2008-01-03                                                 []   \n",
       "\n",
       "      Net Sentiment  \n",
       "0            -190.0  \n",
       "1              20.0  \n",
       "2             220.0  \n",
       "3              80.0  \n",
       "4             430.0  \n",
       "...             ...  \n",
       "3276         -230.0  \n",
       "3277         -150.0  \n",
       "3278          -75.0  \n",
       "3279          150.0  \n",
       "3280            0.0  \n",
       "\n",
       "[3281 rows x 3 columns]"
      ]
     },
     "execution_count": 5,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Load News Data\n",
    "data = pd.read_excel(\"news_data_loaded.xlsx\")\n",
    "# Normalize the 'Analysis' data\n",
    "def normalize_analysis(entry):\n",
    "    if isinstance(entry, str):\n",
    "        try:\n",
    "            # Attempt to parse the string as JSON, replacing single quotes with double quotes\n",
    "            parsed = json.loads(entry.replace(\"\\n\", \"\").replace(\"'\", '\"'))\n",
    "            # If parsed data is a dictionary, wrap it in a list; otherwise, return as-is\n",
    "            return [parsed] if isinstance(parsed, dict) else parsed\n",
    "        except json.JSONDecodeError:\n",
    "            # Return an empty list if JSON parsing fails\n",
    "            return []\n",
    "    elif isinstance(entry, list):\n",
    "        # Ensure all elements in the list are dictionaries, replace non-dicts with empty dicts\n",
    "        return [elem if isinstance(elem, dict) else {} for elem in entry]\n",
    "    # Return an empty list for any other data types\n",
    "    return []\n",
    "\n",
    "# Apply the normalization function to the 'Analysis' column\n",
    "data['Analysis'] = data['Analysis'].apply(normalize_analysis)\n",
    "\n",
    "# Drop rows with dates before 2008 and keep only Date and Analysis columns\n",
    "data = data[data['Date'] >= '2008-01-01'][['Date', 'Analysis']]\n",
    "\n",
    "data\n",
    "\n",
    "def sum_scores(analysis):\n",
    "    return sum(next(iter(item.values())) for item in analysis if isinstance(item, dict) and item)\n",
    "data['Net Sentiment'] = data['Analysis'].apply(sum_scores)\n",
    "data\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 6,
   "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>Date</th>\n",
       "      <th>Analysis</th>\n",
       "      <th>Net Sentiment</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2008-02-29</td>\n",
       "      <td>[{'trade': 50}, {'export_agreement': 100}, {'e...</td>\n",
       "      <td>1296.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2008-03-31</td>\n",
       "      <td>[{'export': 25}, {'trade': 40}, {'export_restr...</td>\n",
       "      <td>630.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2008-04-30</td>\n",
       "      <td>[{'export': 50}, {'agricultural_production': 6...</td>\n",
       "      <td>725.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2008-05-31</td>\n",
       "      <td>[{'trade': 60}, {'export_restrict': -90}, {'do...</td>\n",
       "      <td>445.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2008-06-30</td>\n",
       "      <td>[{'trade': -80}, {'export_restrict': -90}, {'l...</td>\n",
       "      <td>1203.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>190</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>[{'rice_price': 70}, {'export_volume': 60}, {'...</td>\n",
       "      <td>2285.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>191</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>[{'production': 30, 'export': 50, 'demand': 40...</td>\n",
       "      <td>40.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>192</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>[{'export': 90}, {'trade': 80}, {'import': 60}...</td>\n",
       "      <td>65.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>193</th>\n",
       "      <td>2024-03-31</td>\n",
       "      <td>[{'rice_demand': 50}, {'rice_supply': -15}, {'...</td>\n",
       "      <td>670.0</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>194</th>\n",
       "      <td>2024-04-30</td>\n",
       "      <td>[{'trade': 80, 'export_restrict': -40, 'el_nin...</td>\n",
       "      <td>130.0</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>195 rows × 3 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "          Date                                           Analysis  \\\n",
       "0   2008-02-29  [{'trade': 50}, {'export_agreement': 100}, {'e...   \n",
       "1   2008-03-31  [{'export': 25}, {'trade': 40}, {'export_restr...   \n",
       "2   2008-04-30  [{'export': 50}, {'agricultural_production': 6...   \n",
       "3   2008-05-31  [{'trade': 60}, {'export_restrict': -90}, {'do...   \n",
       "4   2008-06-30  [{'trade': -80}, {'export_restrict': -90}, {'l...   \n",
       "..         ...                                                ...   \n",
       "190 2023-12-31  [{'rice_price': 70}, {'export_volume': 60}, {'...   \n",
       "191 2024-01-31  [{'production': 30, 'export': 50, 'demand': 40...   \n",
       "192 2024-02-29  [{'export': 90}, {'trade': 80}, {'import': 60}...   \n",
       "193 2024-03-31  [{'rice_demand': 50}, {'rice_supply': -15}, {'...   \n",
       "194 2024-04-30  [{'trade': 80, 'export_restrict': -40, 'el_nin...   \n",
       "\n",
       "     Net Sentiment  \n",
       "0           1296.0  \n",
       "1            630.0  \n",
       "2            725.0  \n",
       "3            445.0  \n",
       "4           1203.0  \n",
       "..             ...  \n",
       "190         2285.0  \n",
       "191           40.0  \n",
       "192           65.0  \n",
       "193          670.0  \n",
       "194          130.0  \n",
       "\n",
       "[195 rows x 3 columns]"
      ]
     },
     "execution_count": 6,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "\n",
    "data['Date'] = pd.to_datetime(data['Date']).sort_values()\n",
    "data['Date'] = data['Date'] + pd.DateOffset(months=1)\n",
    "\n",
    "# Set 'Date' as the index, resample to monthly frequency, and sum the values\n",
    "data.set_index('Date', inplace=True)\n",
    "data = data.resample('M').sum().reset_index()\n",
    "data\n",
    "\n"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "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>ds</th>\n",
       "      <th>Net Sentiment</th>\n",
       "      <th>y</th>\n",
       "      <th>unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-06-30</td>\n",
       "      <td>1065.90</td>\n",
       "      <td>404.2850</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-07-31</td>\n",
       "      <td>813.00</td>\n",
       "      <td>410.2280</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>805.00</td>\n",
       "      <td>395.7175</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>-1985.50</td>\n",
       "      <td>392.8580</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-10-31</td>\n",
       "      <td>-162.03</td>\n",
       "      <td>414.1075</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>...</th>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "      <td>...</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>172</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>-250.00</td>\n",
       "      <td>616.1500</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>1090.00</td>\n",
       "      <td>629.4100</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>2285.00</td>\n",
       "      <td>632.9000</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>40.00</td>\n",
       "      <td>625.5100</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>65.00</td>\n",
       "      <td>614.2600</td>\n",
       "      <td>sentiment_and_price</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>177 rows × 4 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ds  Net Sentiment         y            unique_id\n",
       "0   2009-06-30        1065.90  404.2850  sentiment_and_price\n",
       "1   2009-07-31         813.00  410.2280  sentiment_and_price\n",
       "2   2009-08-31         805.00  395.7175  sentiment_and_price\n",
       "3   2009-09-30       -1985.50  392.8580  sentiment_and_price\n",
       "4   2009-10-31        -162.03  414.1075  sentiment_and_price\n",
       "..         ...            ...       ...                  ...\n",
       "172 2023-10-31        -250.00  616.1500  sentiment_and_price\n",
       "173 2023-11-30        1090.00  629.4100  sentiment_and_price\n",
       "174 2023-12-31        2285.00  632.9000  sentiment_and_price\n",
       "175 2024-01-31          40.00  625.5100  sentiment_and_price\n",
       "176 2024-02-29          65.00  614.2600  sentiment_and_price\n",
       "\n",
       "[177 rows x 4 columns]"
      ]
     },
     "execution_count": 7,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "#create sent_only_df by joining data and with_macro_df on 'ds'\n",
    "sent_only_df = pd.merge(data, with_macro_df, left_on='Date', right_on='ds', how='inner').drop('ds', axis=1)\n",
    "#Only keep Date, Net Sentiment, y\n",
    "sent_only_df = sent_only_df[['Date', 'Net Sentiment', 'y']]\n",
    "sent_only_df = sent_only_df.fillna(method='ffill')\n",
    "sent_only_df['unique_id'] = 'sentiment_and_price'\n",
    "#rename Date to ds\n",
    "sent_only_df = sent_only_df.rename(columns={'Date': 'ds'})\n",
    "sent_only_df\n"
   ]
  },
  {
   "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>ds</th>\n",
       "      <th>net sentiment</th>\n",
       "      <th>y</th>\n",
       "      <th>industrial production index</th>\n",
       "      <th>ipi mining and quarrying mq</th>\n",
       "      <th>ipi manufacturing</th>\n",
       "      <th>industrial production index computer electronic and optical products</th>\n",
       "      <th>ipi electricity and gas eg</th>\n",
       "      <th>industrial production ytd coal</th>\n",
       "      <th>industrial production ytd electricity</th>\n",
       "      <th>...</th>\n",
       "      <th>no of tourist arrivals</th>\n",
       "      <th>freight carried ytd railway</th>\n",
       "      <th>freight carried ytd maritime transport</th>\n",
       "      <th>freight carried ytd road</th>\n",
       "      <th>freight carried ytd airway</th>\n",
       "      <th>no of passengers carried ytd railway</th>\n",
       "      <th>no of passengers carried ytd maritime transport</th>\n",
       "      <th>no of passengers carried ytd road</th>\n",
       "      <th>no of passengers carried ytd airway</th>\n",
       "      <th>unique_id</th>\n",
       "    </tr>\n",
       "  </thead>\n",
       "  <tbody>\n",
       "    <tr>\n",
       "      <th>0</th>\n",
       "      <td>2009-06-30</td>\n",
       "      <td>1065.90</td>\n",
       "      <td>404.2850</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>21351.800000</td>\n",
       "      <td>37600.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>3945.800</td>\n",
       "      <td>21281.500000</td>\n",
       "      <td>2.259892e+05</td>\n",
       "      <td>61.200000</td>\n",
       "      <td>5565.100</td>\n",
       "      <td>3044.600000</td>\n",
       "      <td>8.688895e+05</td>\n",
       "      <td>5239.300000</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>1</th>\n",
       "      <td>2009-07-31</td>\n",
       "      <td>813.00</td>\n",
       "      <td>410.2280</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>24752.900000</td>\n",
       "      <td>44900.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>4631.700</td>\n",
       "      <td>26454.300000</td>\n",
       "      <td>2.649756e+05</td>\n",
       "      <td>70.000000</td>\n",
       "      <td>6863.700</td>\n",
       "      <td>3492.800000</td>\n",
       "      <td>1.017431e+06</td>\n",
       "      <td>6230.800000</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>2</th>\n",
       "      <td>2009-08-31</td>\n",
       "      <td>805.00</td>\n",
       "      <td>395.7175</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>28689.020093</td>\n",
       "      <td>52310.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>5403.100</td>\n",
       "      <td>31556.300000</td>\n",
       "      <td>3.048197e+05</td>\n",
       "      <td>79.100000</td>\n",
       "      <td>7792.100</td>\n",
       "      <td>3934.200000</td>\n",
       "      <td>1.167531e+06</td>\n",
       "      <td>7315.100000</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>3</th>\n",
       "      <td>2009-09-30</td>\n",
       "      <td>-1985.50</td>\n",
       "      <td>392.8580</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>31566.100000</td>\n",
       "      <td>62300.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6081.300</td>\n",
       "      <td>33318.400000</td>\n",
       "      <td>3.449744e+05</td>\n",
       "      <td>89.800000</td>\n",
       "      <td>8597.200</td>\n",
       "      <td>4381.400000</td>\n",
       "      <td>1.322135e+06</td>\n",
       "      <td>8271.900000</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>4</th>\n",
       "      <td>2009-10-31</td>\n",
       "      <td>-162.03</td>\n",
       "      <td>414.1075</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>0.00</td>\n",
       "      <td>35165.500000</td>\n",
       "      <td>66500.000000</td>\n",
       "      <td>...</td>\n",
       "      <td>0.0</td>\n",
       "      <td>6621.100</td>\n",
       "      <td>36745.900000</td>\n",
       "      <td>3.859084e+05</td>\n",
       "      <td>100.000000</td>\n",
       "      <td>9552.400</td>\n",
       "      <td>4868.200000</td>\n",
       "      <td>1.478455e+06</td>\n",
       "      <td>9104.200000</td>\n",
       "      <td>full_data</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",
       "      <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>172</th>\n",
       "      <td>2023-10-31</td>\n",
       "      <td>-250.00</td>\n",
       "      <td>616.1500</td>\n",
       "      <td>162.38</td>\n",
       "      <td>77.84</td>\n",
       "      <td>175.91</td>\n",
       "      <td>251.58</td>\n",
       "      <td>200.67</td>\n",
       "      <td>40636.886564</td>\n",
       "      <td>222730.523024</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>3714.727</td>\n",
       "      <td>98276.466406</td>\n",
       "      <td>1.376555e+06</td>\n",
       "      <td>259.906032</td>\n",
       "      <td>5250.421</td>\n",
       "      <td>9858.183725</td>\n",
       "      <td>3.471429e+06</td>\n",
       "      <td>47401.916650</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>173</th>\n",
       "      <td>2023-11-30</td>\n",
       "      <td>1090.00</td>\n",
       "      <td>629.4100</td>\n",
       "      <td>162.42</td>\n",
       "      <td>77.13</td>\n",
       "      <td>176.36</td>\n",
       "      <td>239.48</td>\n",
       "      <td>197.90</td>\n",
       "      <td>44750.835434</td>\n",
       "      <td>244653.296420</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>4108.327</td>\n",
       "      <td>106611.721174</td>\n",
       "      <td>1.575268e+06</td>\n",
       "      <td>292.839442</td>\n",
       "      <td>5672.898</td>\n",
       "      <td>10471.142545</td>\n",
       "      <td>3.887923e+06</td>\n",
       "      <td>51540.354055</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>174</th>\n",
       "      <td>2023-12-31</td>\n",
       "      <td>2285.00</td>\n",
       "      <td>632.9000</td>\n",
       "      <td>162.61</td>\n",
       "      <td>72.04</td>\n",
       "      <td>177.93</td>\n",
       "      <td>231.25</td>\n",
       "      <td>192.70</td>\n",
       "      <td>48246.408316</td>\n",
       "      <td>266675.857576</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>4551.927</td>\n",
       "      <td>116128.106245</td>\n",
       "      <td>1.747256e+06</td>\n",
       "      <td>327.870938</td>\n",
       "      <td>6095.519</td>\n",
       "      <td>11093.694000</td>\n",
       "      <td>4.288059e+06</td>\n",
       "      <td>56032.621065</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>175</th>\n",
       "      <td>2024-01-31</td>\n",
       "      <td>40.00</td>\n",
       "      <td>625.5100</td>\n",
       "      <td>155.22</td>\n",
       "      <td>75.31</td>\n",
       "      <td>167.08</td>\n",
       "      <td>201.14</td>\n",
       "      <td>197.93</td>\n",
       "      <td>4028.839775</td>\n",
       "      <td>22503.970018</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>410.300</td>\n",
       "      <td>10838.875931</td>\n",
       "      <td>1.504101e+05</td>\n",
       "      <td>36.801353</td>\n",
       "      <td>422.318</td>\n",
       "      <td>1931.176183</td>\n",
       "      <td>3.516843e+05</td>\n",
       "      <td>4148.214000</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "    <tr>\n",
       "      <th>176</th>\n",
       "      <td>2024-02-29</td>\n",
       "      <td>65.00</td>\n",
       "      <td>614.2600</td>\n",
       "      <td>127.22</td>\n",
       "      <td>62.61</td>\n",
       "      <td>136.02</td>\n",
       "      <td>169.66</td>\n",
       "      <td>164.09</td>\n",
       "      <td>7121.885696</td>\n",
       "      <td>41017.093270</td>\n",
       "      <td>...</td>\n",
       "      <td>1468.8</td>\n",
       "      <td>607.600</td>\n",
       "      <td>21805.627651</td>\n",
       "      <td>2.984771e+05</td>\n",
       "      <td>76.546814</td>\n",
       "      <td>1254.620</td>\n",
       "      <td>4037.153667</td>\n",
       "      <td>7.331434e+05</td>\n",
       "      <td>9291.999360</td>\n",
       "      <td>full_data</td>\n",
       "    </tr>\n",
       "  </tbody>\n",
       "</table>\n",
       "<p>177 rows × 121 columns</p>\n",
       "</div>"
      ],
      "text/plain": [
       "            ds  net sentiment         y  industrial production index  \\\n",
       "0   2009-06-30        1065.90  404.2850                         0.00   \n",
       "1   2009-07-31         813.00  410.2280                         0.00   \n",
       "2   2009-08-31         805.00  395.7175                         0.00   \n",
       "3   2009-09-30       -1985.50  392.8580                         0.00   \n",
       "4   2009-10-31        -162.03  414.1075                         0.00   \n",
       "..         ...            ...       ...                          ...   \n",
       "172 2023-10-31        -250.00  616.1500                       162.38   \n",
       "173 2023-11-30        1090.00  629.4100                       162.42   \n",
       "174 2023-12-31        2285.00  632.9000                       162.61   \n",
       "175 2024-01-31          40.00  625.5100                       155.22   \n",
       "176 2024-02-29          65.00  614.2600                       127.22   \n",
       "\n",
       "     ipi mining and quarrying mq  ipi manufacturing  \\\n",
       "0                           0.00               0.00   \n",
       "1                           0.00               0.00   \n",
       "2                           0.00               0.00   \n",
       "3                           0.00               0.00   \n",
       "4                           0.00               0.00   \n",
       "..                           ...                ...   \n",
       "172                        77.84             175.91   \n",
       "173                        77.13             176.36   \n",
       "174                        72.04             177.93   \n",
       "175                        75.31             167.08   \n",
       "176                        62.61             136.02   \n",
       "\n",
       "     industrial production index computer electronic and optical products  \\\n",
       "0                                                 0.00                      \n",
       "1                                                 0.00                      \n",
       "2                                                 0.00                      \n",
       "3                                                 0.00                      \n",
       "4                                                 0.00                      \n",
       "..                                                 ...                      \n",
       "172                                             251.58                      \n",
       "173                                             239.48                      \n",
       "174                                             231.25                      \n",
       "175                                             201.14                      \n",
       "176                                             169.66                      \n",
       "\n",
       "     ipi electricity and gas eg  industrial production ytd coal  \\\n",
       "0                          0.00                    21351.800000   \n",
       "1                          0.00                    24752.900000   \n",
       "2                          0.00                    28689.020093   \n",
       "3                          0.00                    31566.100000   \n",
       "4                          0.00                    35165.500000   \n",
       "..                          ...                             ...   \n",
       "172                      200.67                    40636.886564   \n",
       "173                      197.90                    44750.835434   \n",
       "174                      192.70                    48246.408316   \n",
       "175                      197.93                     4028.839775   \n",
       "176                      164.09                     7121.885696   \n",
       "\n",
       "     industrial production ytd electricity  ...  no of tourist arrivals  \\\n",
       "0                             37600.000000  ...                     0.0   \n",
       "1                             44900.000000  ...                     0.0   \n",
       "2                             52310.000000  ...                     0.0   \n",
       "3                             62300.000000  ...                     0.0   \n",
       "4                             66500.000000  ...                     0.0   \n",
       "..                                     ...  ...                     ...   \n",
       "172                          222730.523024  ...                  1468.8   \n",
       "173                          244653.296420  ...                  1468.8   \n",
       "174                          266675.857576  ...                  1468.8   \n",
       "175                           22503.970018  ...                  1468.8   \n",
       "176                           41017.093270  ...                  1468.8   \n",
       "\n",
       "     freight carried ytd railway  freight carried ytd maritime transport  \\\n",
       "0                       3945.800                            21281.500000   \n",
       "1                       4631.700                            26454.300000   \n",
       "2                       5403.100                            31556.300000   \n",
       "3                       6081.300                            33318.400000   \n",
       "4                       6621.100                            36745.900000   \n",
       "..                           ...                                     ...   \n",
       "172                     3714.727                            98276.466406   \n",
       "173                     4108.327                           106611.721174   \n",
       "174                     4551.927                           116128.106245   \n",
       "175                      410.300                            10838.875931   \n",
       "176                      607.600                            21805.627651   \n",
       "\n",
       "     freight carried ytd road  freight carried ytd airway  \\\n",
       "0                2.259892e+05                   61.200000   \n",
       "1                2.649756e+05                   70.000000   \n",
       "2                3.048197e+05                   79.100000   \n",
       "3                3.449744e+05                   89.800000   \n",
       "4                3.859084e+05                  100.000000   \n",
       "..                        ...                         ...   \n",
       "172              1.376555e+06                  259.906032   \n",
       "173              1.575268e+06                  292.839442   \n",
       "174              1.747256e+06                  327.870938   \n",
       "175              1.504101e+05                   36.801353   \n",
       "176              2.984771e+05                   76.546814   \n",
       "\n",
       "     no of passengers carried ytd railway  \\\n",
       "0                                5565.100   \n",
       "1                                6863.700   \n",
       "2                                7792.100   \n",
       "3                                8597.200   \n",
       "4                                9552.400   \n",
       "..                                    ...   \n",
       "172                              5250.421   \n",
       "173                              5672.898   \n",
       "174                              6095.519   \n",
       "175                               422.318   \n",
       "176                              1254.620   \n",
       "\n",
       "     no of passengers carried ytd maritime transport  \\\n",
       "0                                        3044.600000   \n",
       "1                                        3492.800000   \n",
       "2                                        3934.200000   \n",
       "3                                        4381.400000   \n",
       "4                                        4868.200000   \n",
       "..                                               ...   \n",
       "172                                      9858.183725   \n",
       "173                                     10471.142545   \n",
       "174                                     11093.694000   \n",
       "175                                      1931.176183   \n",
       "176                                      4037.153667   \n",
       "\n",
       "     no of passengers carried ytd road  no of passengers carried ytd airway  \\\n",
       "0                         8.688895e+05                          5239.300000   \n",
       "1                         1.017431e+06                          6230.800000   \n",
       "2                         1.167531e+06                          7315.100000   \n",
       "3                         1.322135e+06                          8271.900000   \n",
       "4                         1.478455e+06                          9104.200000   \n",
       "..                                 ...                                  ...   \n",
       "172                       3.471429e+06                         47401.916650   \n",
       "173                       3.887923e+06                         51540.354055   \n",
       "174                       4.288059e+06                         56032.621065   \n",
       "175                       3.516843e+05                          4148.214000   \n",
       "176                       7.331434e+05                          9291.999360   \n",
       "\n",
       "     unique_id  \n",
       "0    full_data  \n",
       "1    full_data  \n",
       "2    full_data  \n",
       "3    full_data  \n",
       "4    full_data  \n",
       "..         ...  \n",
       "172  full_data  \n",
       "173  full_data  \n",
       "174  full_data  \n",
       "175  full_data  \n",
       "176  full_data  \n",
       "\n",
       "[177 rows x 121 columns]"
      ]
     },
     "execution_count": 8,
     "metadata": {},
     "output_type": "execute_result"
    }
   ],
   "source": [
    "# Merge data with pure_stats_df and apply forward fill\n",
    "full_df= pd.merge(data, with_macro_df, left_on='Date', right_on='ds', how='inner').drop('ds', axis=1)\n",
    "full_df= full_df.fillna(method='ffill')\n",
    "\n",
    "# Rename columns and drop unnecessary ones\n",
    "full_df = full_df.rename(columns={'Date': 'ds'}).drop('Analysis', axis=1)\n",
    "full_df['unique_id'] = 'full_data'\n",
    "#rename Net Sentiment to sentiment\n",
    "full_df = full_df.rename(columns={'Net Sentiment': 'net sentiment'})\n",
    "full_df \n"
   ]
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "nixtla",
   "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.10.13"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}