infusion_fetched_data.ipynb

{
 "cells": [
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "# DATA INTEGRATION MODULE"
  },
  {
   "cell_type": "code",
   "metadata": {
    "ExecuteTime": {
     "end_time": "2024-08-24T11:43:42.790134Z",
     "start_time": "2024-08-24T11:43:42.777141Z"
    }
   },
   "source": [
    "import pandas as pd\n",
    "#ignore all warnings\n",
    "import warnings\n",
    "warnings.filterwarnings(\"ignore\")\n"
   ],
   "outputs": [],
   "execution_count": 1
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ECB EST€R "
  },
  {
   "cell_type": "code",
   "metadata": {},
   "source": [
    "import pandas as pd\n",
    "\n",
    "est_data = pd.read_csv('FETCHED_DATA/ECB_API/EST.csv')\n",
    "est_data['TIME_PERIOD'] = pd.to_datetime(est_data['TIME_PERIOD'])\n",
    "\n",
    "#rename value to EST_VALUE\n",
    "est_data_cleaned= est_data.rename(columns={'value':'EST_VALUE'})\n",
    "\n",
    "# Pivot the dataframe\n",
    "est_data_cleaned = est_data_cleaned.pivot(index='TIME_PERIOD', columns=['BENCHMARK_ITEM', 'DATA_TYPE_EST'], values='EST_VALUE').reset_index()\n",
    "est_data_cleaned.columns = ['_'.join(col).strip() if isinstance(col, tuple) else col for col in est_data_cleaned.columns.values]\n",
    "\n",
    "# Fill NaN values using forward fill then backward fill\n",
    "est_data_cleaned = est_data_cleaned.fillna(method='ffill').fillna(method='bfill')\n",
    "\n",
    "#rename columns\n",
    "est_data_cleaned = est_data_cleaned.rename(columns={\n",
    "    'TIME_PERIOD_':'TIME_PERIOD', \n",
    "    'EU000A2QQF08_CI':'ESTR_EU000A2QQF08_CI', \n",
    "    'EU000A2X2A25_NT':'ESTR_EU000A2X2A25_NT', \n",
    "    'EU000A2X2A25_TT':'ESTR_EU000A2X2A25_TT' \n",
    "})\n",
    "\n",
    "#filtered columns\n",
    "est_data_cleaned = est_data_cleaned.filter(items=['TIME_PERIOD', 'ESTR_EU000A2QQF08_CI', 'ESTR_EU000A2X2A25_NT', 'ESTR_EU000A2X2A25_TT'])\n",
    "\n",
    "#sort by TIME_PERIOD\n",
    "est_data_cleaned = est_data_cleaned.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "# Subtract values in the 'ESTR_EU000A2QQF08_CI' column from 100, which is the base rate since 2019.\n",
    "est_data_cleaned['ESTR_EU000A2QQF08_CI'] = est_data_cleaned['ESTR_EU000A2QQF08_CI'] - 100\n",
    "\n",
    "est_data_cleaned.head()"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ECB CISS  "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Read the CSV file into a DataFrame\n",
    "ciss_data = pd.read_csv('FETCHED_DATA/ECB_API/ECB_CISS.csv')\n",
    "\n",
    "# Convert the TIME_PERIOD column to datetime format, handling errors\n",
    "ciss_data['TIME_PERIOD'] = pd.to_datetime(ciss_data['TIME_PERIOD'], errors='coerce')\n",
    "\n",
    "# Filter the data to include only rows where REF_AREA = 'U2'\n",
    "ciss_data_filtered = ciss_data[ciss_data['REF_AREA'] == 'U2']\n",
    "\n",
    "# Exclude rows where PROVIDER_FM_ID is 'SOV_EW' or 'SOV_GDPW'\n",
    "ciss_data_filtered = ciss_data_filtered[~ciss_data_filtered['PROVIDER_FM_ID'].isin(['SS_CIN', 'SOV_EW', 'SOV_GDPW'])]\n",
    "\n",
    "# Pivot the DataFrame, setting TIME_PERIOD as the index and\n",
    "# PROVIDER_FM_ID and DATA_TYPE_FM as the columns, with values from CISS_VALUE\n",
    "ecb_ciss_si_data_cleaned = ciss_data_filtered.pivot(index='TIME_PERIOD', columns=['PROVIDER_FM_ID', 'DATA_TYPE_FM'], values='value').reset_index()\n",
    "\n",
    "# Merge the multi-level columns into a single level, removing any whitespace\n",
    "ecb_ciss_si_data_cleaned.columns = ['_'.join(col).strip() if isinstance(col, tuple) else col for col in ecb_ciss_si_data_cleaned.columns.values]\n",
    "\n",
    "# Fill all NaN values using forward fill then backward fill\n",
    "ecb_ciss_si_data_cleaned = ecb_ciss_si_data_cleaned.fillna(method='ffill').fillna(method='bfill')\n",
    "\n",
    "# Rename the column 'TIME_PERIOD_' back to 'TIME_PERIOD' if necessary\n",
    "ecb_ciss_si_data_cleaned = ecb_ciss_si_data_cleaned.rename(columns={'TIME_PERIOD_': 'TIME_PERIOD', 'SS_BM_CON':'CISS_EA20_SS_BM', 'SS_FI_CON':'CISS_EA20_SS_FI', 'SS_FX_CON':'CISS_EA20_SS_FX', 'SS_MM_CON':'CISS_EA20_SS_MM'})\n",
    "\n",
    "#filtered columns\n",
    "ecb_ciss_si_data_cleaned = ecb_ciss_si_data_cleaned.filter(items=['TIME_PERIOD', 'CISS_EA20_SS_BM', 'CISS_EA20_SS_FI', 'CISS_EA20_SS_FX', 'CISS_EA20_SS_MM'])\n",
    "\n",
    "# Sort the DataFrame by TIME_PERIOD\n",
    "ecb_ciss_si_data_cleaned = ecb_ciss_si_data_cleaned.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "# Return the first 5 rows of the cleaned DataFrame\n",
    "ecb_ciss_si_data_cleaned.head()\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "ecb_ciss = pd.read_csv('FETCHED_DATA/ECB_API/ECB_CISS.csv')\n",
    "ea20_ecb_ciss = ecb_ciss[ecb_ciss['REF_AREA'] == 'U2']\n",
    "ea20_ecb_ciss = ea20_ecb_ciss[ea20_ecb_ciss['FREQ']== 'D']\n",
    "\n",
    "ea20_ecb_ciss = ea20_ecb_ciss[ea20_ecb_ciss['PROVIDER_FM_ID']== 'SS_CIN']\n",
    "#convert TIME_PERIOD to proper datetime format (current format is YYYY-MM-DD)\n",
    "ea20_ecb_ciss['TIME_PERIOD'] = pd.to_datetime(ea20_ecb_ciss['TIME_PERIOD'])\n",
    "ea20_ecb_ciss_cleaned = ea20_ecb_ciss[['TIME_PERIOD', 'value']]\n",
    "#rename value to CISS_VALUE\n",
    "ea20_ecb_ciss_cleaned = ea20_ecb_ciss_cleaned.rename(columns={'value': 'CISS_EA20_SS_CIN'})\n",
    "#sort by TIME_PERIOD\n",
    "ea20_ecb_ciss_cleaned = ea20_ecb_ciss_cleaned.sort_values(by='TIME_PERIOD')\n",
    "ea20_ecb_ciss_cleaned.head()"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### WHO "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the data from the CSV file\n",
    "covid_data_ea20 = pd.read_csv('FETCHED_DATA/WHO_API/COVID_DATA_EA20.csv')\n",
    "\n",
    "# Rename 'Date_reported' to 'TIME_PERIOD'\n",
    "covid_data_ea20 = covid_data_ea20.rename(columns={'Date_reported': 'TIME_PERIOD'})\n",
    "\n",
    "# Convert 'TIME_PERIOD' to datetime format\n",
    "covid_data_ea20['TIME_PERIOD'] = pd.to_datetime(covid_data_ea20['TIME_PERIOD'])\n",
    "\n",
    "# Sort by 'TIME_PERIOD'\n",
    "covid_data_ea20 = covid_data_ea20.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "# Drop the 'Country' column\n",
    "covid_data_ea20 = covid_data_ea20.drop(columns=['Country'])\n",
    "\n",
    "# Rename columns\n",
    "covid_data_ea20 = covid_data_ea20.rename(columns={\n",
    "    'New_cases': 'Covid_EA20_New_Cases',\n",
    "    'Cumulative_cases': 'Covid_EA20_Cumulative_Cases',\n",
    "    'New_deaths': 'Covid_EA20_New_Deaths',\n",
    "    'Cumulative_deaths': 'Covid_EA20_Cumulative_Deaths'\n",
    "})\n",
    "\n",
    "# Display the first few rows of the DataFrame for verification\n",
    "covid_data_ea20.head()"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ESTAT | INFLATION "
  },
  {
   "cell_type": "code",
   "metadata": {},
   "source": [
    "from datetime import datetime   \n",
    "inflation = pd.read_csv('FETCHED_DATA/ESTAT_API/ESTAT_INFLATION_2024.csv')\n",
    "inflation = inflation[inflation['geo'] == 'EA20']\n",
    "#convert TIME_PERIOD to proper datetime format (current format is YYYY)\n",
    "\n",
    "inflation = inflation[inflation['TIME_PERIOD'] >= 2020]\n",
    "# Generate date range from 1-1-2020 to now\n",
    "start_date = '2020-01-01'\n",
    "end_date = datetime.now().strftime('%Y-%m-%d')\n",
    "date_range = pd.date_range(start=start_date, end=end_date, freq='D')\n",
    "\n",
    "# Create a new dataframe with daily dates and corresponding values\n",
    "rows = []\n",
    "for index, row in inflation.iterrows():\n",
    "    year = row['TIME_PERIOD']\n",
    "    value = row['value']\n",
    "    year_dates = date_range[(date_range.year == year)]\n",
    "    for date in year_dates:\n",
    "        rows.append({\n",
    "            'date': date,\n",
    "            'geo': row['geo'],\n",
    "            'unit': row['unit'],\n",
    "            'freq': row['freq'],\n",
    "            'coicop': row['coicop'],\n",
    "            'value': value\n",
    "        })\n",
    "\n",
    "daily_inflation_ea20 = pd.DataFrame(rows)\n",
    "\n",
    "#rename date to TIME_PERIOD\n",
    "daily_inflation_ea20 = daily_inflation_ea20.rename(columns={'date': 'TIME_PERIOD'})\n",
    "daily_inflation_ea20 = daily_inflation_ea20[['TIME_PERIOD', 'value']]\n",
    "#rename value to INFLATION_VALUE\n",
    "daily_inflation_ea20 = daily_inflation_ea20.rename(columns={'value': 'INFLATION_EA20_VALUE'})\n",
    "\n",
    "daily_inflation_ea20.head()"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ESTAT | GDP "
  },
  {
   "cell_type": "code",
   "metadata": {},
   "source": [
    "estat_gdp = pd.read_csv('FETCHED_DATA/ESTAT_API/ESTAT_GDP_2024.csv')\n",
    "estat_gdp = estat_gdp[estat_gdp['geo'] == 'EA20']\n",
    "estat_gdp = estat_gdp[estat_gdp['unit'] == 'CLV_PCH_PRE']\n",
    "\n",
    "estat_gdp = estat_gdp[estat_gdp['TIME_PERIOD'] >= 2020]\n",
    "# Generate date range from 1-1-2020 to now\n",
    "start_date = '2020-01-01'\n",
    "end_date = '2024-06-30'\n",
    "date_range = pd.date_range(start=start_date, end=end_date, freq='D')\n",
    "\n",
    "# Create a new dataframe with daily dates and corresponding values\n",
    "rows = []\n",
    "for index, row in estat_gdp.iterrows():\n",
    "    year = row['TIME_PERIOD']\n",
    "    value = row['value']\n",
    "    year_dates = date_range[(date_range.year == year)]\n",
    "    for date in year_dates:\n",
    "        rows.append({\n",
    "            'date': date,\n",
    "            'geo': row['geo'],\n",
    "            'freq': row['freq'],\n",
    "            'value': value\n",
    "        })\n",
    "\n",
    "daily_gdp_ea20 = pd.DataFrame(rows)\n",
    "\n",
    "#rename date to TIME_PERIOD\n",
    "daily_gdp_ea20 = daily_gdp_ea20.rename(columns={'date': 'TIME_PERIOD'})\n",
    "daily_gdp_ea20 = daily_gdp_ea20[['TIME_PERIOD', 'value']]\n",
    "#rename value to GDP_VALUE\n",
    "daily_gdp_ea20 = daily_gdp_ea20.rename(columns={'value': 'GDP_GROWTH_EA20'})\n",
    "# sort by TIME_PERIOD\n",
    "daily_gdp_ea20 = daily_gdp_ea20.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "daily_gdp_ea20.head()"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ESTAT | UNEMPLOYMENT "
  },
  {
   "cell_type": "code",
   "metadata": {},
   "source": [
    "estat_unemployment_ea20 = pd.read_csv('FETCHED_DATA/ESTAT_API/ESTAT_UNEMPLOYMENT.csv')\n",
    "# Filter the data for 'EA20' and dates after 2020-01-01\n",
    "filtered_data = estat_unemployment_ea20[\n",
    "    (estat_unemployment_ea20['geo'] == 'EA20') & \n",
    "    (estat_unemployment_ea20['TIME_PERIOD'] >= '2020-01') &\n",
    "    (estat_unemployment_ea20['s_adj'] != 'NSA') &\n",
    "    (estat_unemployment_ea20['indic'] != 'LM-UN-F-GT25') &\n",
    "    (estat_unemployment_ea20['indic'] != 'LM-UN-F-LE25') &\n",
    "    (estat_unemployment_ea20['indic'] != 'LM-UN-M-GT25') &\n",
    "    (estat_unemployment_ea20['indic'] != 'LM-UN-M-LE25') \n",
    "]\n",
    "\n",
    "# Convert TIME_PERIOD to datetime\n",
    "filtered_data['TIME_PERIOD'] = pd.to_datetime(filtered_data['TIME_PERIOD'])\n",
    "\n",
    "# Generate a daily date range from 2020-01-01 to the current date\n",
    "start_date = '2020-01-01'\n",
    "end_date = datetime.now().strftime('%Y-%m-%d')\n",
    "date_range = pd.date_range(start=start_date, end=end_date, freq='D')\n",
    "\n",
    "# Create a pivot table with the necessary columns\n",
    "pivot_data = filtered_data.pivot_table(index='TIME_PERIOD', columns=['indic', 'unit', 's_adj'], values='value').reset_index()\n",
    "\n",
    "# Flatten the multi-level column headers\n",
    "pivot_data.columns = ['_'.join(col).strip() if isinstance(col, tuple) else col for col in pivot_data.columns.values]\n",
    "\n",
    "# Remove the extra underscore if any\n",
    "pivot_data.columns = [col.replace('__', '') if isinstance(col, str) else col for col in pivot_data.columns]\n",
    "\n",
    "# Reindex the dataframe to include all daily dates\n",
    "pivot_data = pivot_data.set_index('TIME_PERIOD').reindex(date_range).fillna(method='ffill').reset_index()\n",
    "pivot_data = pivot_data.rename(columns={'index': 'TIME_PERIOD'})\n",
    "\n",
    "pivot_data = pivot_data.rename(columns={'LM-UN-F-TOT_PC_ACT_SA': 'UN_EA20_F_TOT'})\n",
    "pivot_data = pivot_data.rename(columns={'LM-UN-M-TOT_PC_ACT_SA': 'UN_EA20_M_TOT'})\n",
    "pivot_data = pivot_data.rename(columns={'LM-UN-T-GT25_PC_ACT_SA': 'UN_EA20_GT25_TOT'})\n",
    "pivot_data = pivot_data.rename(columns={'LM-UN-T-LE25_PC_ACT_SA': 'UN_EA20_LE25_TOT'})\n",
    "pivot_data = pivot_data.rename(columns={'LM-UN-T-TOT_PC_ACT_SA': 'UN_EA20_T_TOT'})\n",
    "\n",
    "#sort by TIME_PERIOD\n",
    "estat_unemployment_ea20 = pivot_data.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "estat_unemployment_ea20"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "### ESTAT | CONSUMER CONFIDENCE "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "from datetime import datetime   \n",
    "\n",
    "estat_consumer_confidence_ea20 = pd.read_csv('FETCHED_DATA/ESTAT_API/ESTAT_CONSUMER_CONFIDENCE.csv')\n",
    "# Filter the data for 'EA20' and dates after 2020-01-01\n",
    "filtered_data = estat_consumer_confidence_ea20[\n",
    "    (estat_consumer_confidence_ea20['geo'] == 'EA20') & \n",
    "    (estat_consumer_confidence_ea20['TIME_PERIOD'] >= '2020-01') &\n",
    "    (estat_consumer_confidence_ea20['s_adj'] != 'NSA') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-FS-LY') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-GES-LY') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-MP-PR') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-PT-LY') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-SFSH') &\n",
    "    (estat_consumer_confidence_ea20['indic'] != 'BS-UE-NY')\n",
    "]\n",
    "\n",
    "# Convert TIME_PERIOD to datetime\n",
    "filtered_data['TIME_PERIOD'] = pd.to_datetime(filtered_data['TIME_PERIOD'])\n",
    "\n",
    "# Generate a daily date range from 2020-01-01 to the current date\n",
    "start_date = '2020-01-01'\n",
    "end_date = datetime.now().strftime('%Y-%m-%d')\n",
    "date_range = pd.date_range(start=start_date, end=end_date, freq='D')\n",
    "\n",
    "# Create a pivot table with the necessary columns\n",
    "pivot_data = filtered_data.pivot_table(index='TIME_PERIOD', columns=['indic', 'unit', 's_adj'], values='value').reset_index()\n",
    "\n",
    "# Flatten the multi-level column headers\n",
    "pivot_data.columns = ['_'.join(col).strip() if isinstance(col, tuple) else col for col in pivot_data.columns.values]\n",
    "\n",
    "# Remove the extra underscore if any\n",
    "pivot_data.columns = [col.replace('__', '') if isinstance(col, str) else col for col in pivot_data.columns]\n",
    "\n",
    "# Reindex the dataframe to include all daily dates\n",
    "pivot_data = pivot_data.set_index('TIME_PERIOD').reindex(date_range).fillna(method='ffill').reset_index()\n",
    "pivot_data = pivot_data.rename(columns={'index': 'TIME_PERIOD'})\n",
    "\n",
    "pivot_data = pivot_data.rename(columns={'BS-CSMCI_BAL_SA': 'BS_EA20_CSMCI'})\n",
    "pivot_data = pivot_data.rename(columns={'BS-FS-NY_BAL_SA': 'BS_EA20_FS_NY'})\n",
    "pivot_data = pivot_data.rename(columns={'BS-MP-NY_BAL_SA': 'BS_EA20_MP_NY'})\n",
    "pivot_data = pivot_data.rename(columns={'BS-GES-NY_BAL_SA': 'BS_EA20_GES_NY'})\n",
    "pivot_data = pivot_data.rename(columns={'BS-PT-NY_BAL_SA': 'BS_EA20_PT_NY'})\n",
    "pivot_data = pivot_data.rename(columns={'BS-SV-NY_BAL_SA': 'BS_EA20_SV_NY'})\n",
    "\n",
    "#sort by TIME_PERIOD\n",
    "estat_consumer_confidence_ea20 = pivot_data.sort_values(by='TIME_PERIOD')\n",
    "\n",
    "estat_consumer_confidence_ea20\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ESTAT | GOVYC "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "govdebt_df_ea20 = pd.read_csv('FETCHED_DATA/ESTAT_API/ESTAT_GOVYC_2024.csv')\n",
    "#keep only geo EA\n",
    "govdebt_df_ea20 = govdebt_df_ea20[govdebt_df_ea20['geo'] == 'EA']\n",
    "govdebt_df_ea20 = govdebt_df_ea20[['TIME_PERIOD', 'value']]\n",
    "#rename value to GOVDEBT_VALUE\n",
    "govdebt_df_ea20 = govdebt_df_ea20.rename(columns={'value': 'GOVDEBT_EA20'})\n",
    "\n",
    "# Convert TIME_PERIOD to datetime (using the first day of each month)\n",
    "govdebt_df_ea20['TIME_PERIOD'] = pd.to_datetime(govdebt_df_ea20['TIME_PERIOD'], format='%Y-%m')\n",
    "\n",
    "# Generate a daily date range from 2020-01-01 to now\n",
    "start_date = '2020-01-01'\n",
    "end_date = datetime.now().strftime('%Y-%m-%d')\n",
    "date_range = pd.date_range(start=start_date, end=end_date, freq='D')\n",
    "\n",
    "# Reindex the dataframe to include all daily dates\n",
    "govdebt_df_ea20 = govdebt_df_ea20.set_index('TIME_PERIOD').reindex(date_range).fillna(method='ffill').reset_index()\n",
    "govdebt_df_ea20 = govdebt_df_ea20.rename(columns={'index': 'TIME_PERIOD'})\n",
    "\n",
    "govdebt_df_ea20"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ECB | EXCHANGE RATES EUR to USD, CNY"
  },
  {
   "cell_type": "code",
   "metadata": {},
   "source": [
    "exr = pd.read_csv(\"FETCHED_DATA/ECB_API/EXCHANGE_RATES_EUR_to_['USD', 'CNY'].csv\")\n",
    "pivot_exchange_df = exr.pivot(index='TIME_PERIOD', columns='CURRENCY', values='value').reset_index()\n",
    "pivot_exchange_df = pivot_exchange_df.rename(columns={'USD': 'ECB_EXCHANGE_RATES_USD_EUR', 'CNY': 'ECB_EXCHANGE_RATES_CNY_EUR'})\n",
    "pivot_exchange_df = pivot_exchange_df[['TIME_PERIOD', 'ECB_EXCHANGE_RATES_USD_EUR', 'ECB_EXCHANGE_RATES_CNY_EUR']]\n",
    "#make TIME_PERIOD to datetime\n",
    "pivot_exchange_df['TIME_PERIOD'] = pd.to_datetime(pivot_exchange_df['TIME_PERIOD'])\n",
    "pivot_exchange_df"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### ALPHA VANTAGE | GLOBAL PRICES for OIL, GAS, WHEAT, CORN"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# List of commodity files\n",
    "commodity_files = {\n",
    "    'oil': 'FETCHED_DATA/ALPHA_VANTAGE_API/oil_prices.csv',\n",
    "    'gas': 'FETCHED_DATA/ALPHA_VANTAGE_API/gas_prices.csv',\n",
    "    'corn': 'FETCHED_DATA/ALPHA_VANTAGE_API/corn_prices.csv',\n",
    "    'wheat': 'FETCHED_DATA/ALPHA_VANTAGE_API/wheat_prices.csv'\n",
    "}\n",
    "\n",
    "commodity_data = []\n",
    "\n",
    "for commodity, file in commodity_files.items():\n",
    "    df = pd.read_csv(file, parse_dates=['date'])\n",
    "    df.set_index('date', inplace=True)\n",
    "    df.index.name = 'TIME_PERIOD'\n",
    "    # Strip whitespace from column names\n",
    "    df.columns = df.columns.str.strip()\n",
    "    \n",
    "    # Select specific columns and rename them\n",
    "    columns_to_select = {\n",
    "        '4. close': f'{commodity.capitalize()}_Close_Price',\n",
    "        '5. volume': f'{commodity.capitalize()}_Volume'\n",
    "    }\n",
    "    df = df[list(columns_to_select.keys())]\n",
    "    df.rename(columns=columns_to_select, inplace=True)\n",
    "    commodity_data.append(df)\n",
    "\n",
    "# Concatenate all the selected dataframes\n",
    "commodity_merged_df = pd.concat(commodity_data, axis=1)\n",
    "\n",
    "# Display the head of the dataframe to verify the changes\n",
    "commodity_merged_df\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### PYTRENDS "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the PY_GOOGLE_TRENDS.csv file\n",
    "trends_df = pd.read_csv(\"FETCHED_DATA/PY_GOOGLE_TRENDS_API/PY_GOOGLE_TRENDS.csv\")\n",
    "\n",
    "# Rename the 'date' column to 'TIME_PERIOD' to match other analyses\n",
    "trends_df = trends_df.rename(columns={'date': 'TIME_PERIOD'})\n",
    "\n",
    "# Convert the 'TIME_PERIOD' column to datetime format\n",
    "trends_df['TIME_PERIOD'] = pd.to_datetime(trends_df['TIME_PERIOD'])\n",
    "\n",
    "# Reorder columns to have 'TIME_PERIOD' as the first column\n",
    "columns_order = ['TIME_PERIOD'] + [col for col in trends_df.columns if col != 'TIME_PERIOD']\n",
    "trends_df = trends_df[columns_order]\n",
    "\n",
    "# Rename the columns as per your preference\n",
    "new_column_names = {\n",
    "    '\"Russia Ukraine War\"': 'Russia_Ukraine_War_Trend',\n",
    "    '\"Israel Gaza War\"': 'Israel_Gaza_War_Trend',\n",
    "    '\"Financial Crisis\"': 'Financial_Crisis_Trend',\n",
    "    '\"Climate Change\"': 'Climate_Change_Trend',\n",
    "    '\"Covid-19\"': 'Covid19_Trend'\n",
    "}\n",
    "\n",
    "trends_df = trends_df.rename(columns=new_column_names)\n",
    "\n",
    "# Print the first few rows to verify the changes\n",
    "trends_df.head(2000)"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "#### YOUTUBE "
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "import pandas as pd\n",
    "\n",
    "# Load the YOUTUBE_DAILY_RESULTS.csv file\n",
    "videocount_df = pd.read_csv(\"FETCHED_DATA/YOUTUBE_API/YOUTUBE_DAILY_RESULTS.csv\")\n",
    "\n",
    "# Rename the 'Date' column to 'TIME_PERIOD' to match other analyses\n",
    "videocount_df = videocount_df.rename(columns={'Date': 'TIME_PERIOD'})\n",
    "\n",
    "# Convert the 'TIME_PERIOD' column to datetime format\n",
    "videocount_df['TIME_PERIOD'] = pd.to_datetime(videocount_df['TIME_PERIOD'])\n",
    "\n",
    "# Reorder columns to have 'TIME_PERIOD' as the first column\n",
    "columns_order = ['TIME_PERIOD'] + [col for col in videocount_df.columns if col != 'TIME_PERIOD']\n",
    "videocount_df = videocount_df[columns_order]\n",
    "\n",
    "# Rename the columns as per your preference\n",
    "new_column_names = {\n",
    "    '\"Russia Ukraine War\"_total_results': 'Russia_Ukraine_War_VideoCount',\n",
    "    '\"Israel Gaza War\"_total_results': 'Israel_Gaza_War_VideoCount',\n",
    "    '\"Financial Crisis\"_total_results': 'Financial_Crisis_VideoCount',\n",
    "    '\"Climate Change\"_total_results': 'Climate_Change_VideoCount',\n",
    "    '\"Covid-19\"_total_results': 'Covid19_VideoCount'\n",
    "}\n",
    "\n",
    "videocount_df = videocount_df.rename(columns=new_column_names)\n",
    "\n",
    "# Print the first few rows to verify the changes\n",
    "videocount_df\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "markdown",
   "source": "## EA20 INFUSION DATASET"
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": [
    "#outter join on TIME_PERIOD\n",
    "merged_data_ea20 = pd.merge(est_data_cleaned, ea20_ecb_ciss_cleaned, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, ecb_ciss_si_data_cleaned, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, daily_inflation_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, daily_gdp_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, govdebt_df_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, estat_unemployment_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, estat_consumer_confidence_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, pivot_exchange_df, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, commodity_merged_df, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, covid_data_ea20, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, trends_df, on='TIME_PERIOD', how='outer')\n",
    "merged_data_ea20 = pd.merge(merged_data_ea20, videocount_df, on='TIME_PERIOD', how='outer')\n",
    "\n",
    "\n",
    "columns_to_ffill_bfill = [\n",
    "    'ESTR_EU000A2QQF08_CI', 'ESTR_EU000A2X2A25_NT', 'ESTR_EU000A2X2A25_TT', \n",
    "    'CISS_EA20_SS_CIN', 'CISS_EA20_SS_BM', 'CISS_EA20_SS_FI', 'CISS_EA20_SS_FX', \n",
    "    'CISS_EA20_SS_MM', 'ECB_EXCHANGE_RATES_USD_EUR', 'ECB_EXCHANGE_RATES_CNY_EUR',\n",
    "    'Oil_Close_Price', 'Oil_Volume', 'Gas_Close_Price', 'Gas_Volume', 'Corn_Close_Price',\n",
    "    'Corn_Volume', 'Wheat_Close_Price', 'Wheat_Volume'\n",
    "]\n",
    "\n",
    "for column in columns_to_ffill_bfill:\n",
    "    merged_data_ea20[column] = merged_data_ea20[column].fillna(method='ffill')\n",
    "    merged_data_ea20[column] = merged_data_ea20[column].fillna(method='bfill')\n",
    "\n",
    "# Filter to keep only data up to 2024-06-30\n",
    "merged_data_ea20 = merged_data_ea20[merged_data_ea20['TIME_PERIOD'] <= '2024-06-30']\n",
    "\n",
    "print(\"Column names in merged_data_ea20:\")\n",
    "print(merged_data_ea20.columns)\n",
    "\n",
    "def interpolate_weekly(data, columns):\n",
    "    for column in columns:\n",
    "        data[column] = data[column].interpolate(method='linear', limit_direction='both')\n",
    "    return data\n",
    "\n",
    "covid_columns = ['Covid_EA20_New_Cases', 'Covid_EA20_Cumulative_Cases', 'Covid_EA20_New_Deaths', 'Covid_EA20_Cumulative_Deaths']\n",
    "\n",
    "for column in covid_columns:\n",
    "    merged_data_ea20[column] = pd.to_numeric(merged_data_ea20[column], errors='coerce')\n",
    "\n",
    "merged_data_ea20 = interpolate_weekly(merged_data_ea20, covid_columns)\n",
    "\n",
    "#save to EA folder\n",
    "merged_data_ea20.to_csv('INFUSED_DATA/merged_data_EA20.csv', index=False)\n",
    "\n",
    "merged_data_ea20\n"
   ],
   "outputs": [],
   "execution_count": null
  },
  {
   "metadata": {},
   "cell_type": "code",
   "source": "",
   "outputs": [],
   "execution_count": null
  }
 ],
 "metadata": {
  "kernelspec": {
   "display_name": "Python 3",
   "language": "python",
   "name": "python3"
  },
  "language_info": {
   "codemirror_mode": {
    "name": "ipython",
    "version": 3
   },
   "file_extension": ".py",
   "mimetype": "text/x-python",
   "name": "python",
   "nbconvert_exporter": "python",
   "pygments_lexer": "ipython3",
   "version": "3.12.2"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}