customer_database_pred_LSTM.ipynb

{
 "cells": [
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Process Customer LSTM Files and Save to SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 1,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Total Predict Value for Cluster 1: 1146484.342\n",
      "Total Predict Value for Cluster 2: 61263295.7\n",
      "Total Predict Value for Cluster 3: 2830418.276\n"
     ]
    }
   ],
   "source": [
    "import pandas as pd\n",
    "import glob\n",
    "import sqlite3\n",
    "import numpy as np\n",
    "\n",
    "# Function to read and process LSTM Customer CSV files with cluster information\n",
    "def process_lstm_files(file_pattern):\n",
    "    csv_files = glob.glob(file_pattern)\n",
    "    all_data = []\n",
    "\n",
    "    for file in csv_files:\n",
    "        df = pd.read_csv(file)\n",
    "        cluster_name = '1' if 'Cluster1' in file else '2' if 'Cluster2' in file else '3' if 'Cluster3' in file else 'Unknown'\n",
    "        df['Cluster'] = cluster_name\n",
    "        df.loc[df['Revenue'] == df['Predict'], 'Revenue'] = np.nan  # Remove Revenue if it equals Predict\n",
    "        all_data.append(df)\n",
    "\n",
    "    combined_df = pd.concat(all_data, ignore_index=True)\n",
    "    total_predict_per_cluster = combined_df.groupby('Cluster')['Predict'].sum()\n",
    "    for cluster in ['1', '2', '3']:\n",
    "        print(f\"Total Predict Value for Cluster {cluster}: {total_predict_per_cluster.get(cluster, 'No Data')}\")\n",
    "    \n",
    "    return combined_df\n",
    "\n",
    "# Process LSTM files and save to SQLite\n",
    "combined_lstm_df = process_lstm_files('data/LSTM/New/Option4/*.csv')"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save to SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 2,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Preview of table 'predictions_LSTM':\n",
      "    Unnamed: 0        Date     Revenue Train  Test Predict Cluster\n",
      "0           0  2021-12-27   6550.0405  None  None    None       1\n",
      "1           1  2022-01-03  21021.4300  None  None    None       1\n",
      "2           2  2022-01-10  26732.9860  None  None    None       1\n",
      "3           3  2022-01-17  29902.7830  None  None    None       1\n",
      "4           4  2022-01-24  23850.3100  None  None    None       1\n"
     ]
    }
   ],
   "source": [
    "# Function to save DataFrame to SQLite database\n",
    "def save_to_sqlite(df, db_name, table_name):\n",
    "    with sqlite3.connect(db_name) as conn:\n",
    "        conn.execute(f\"DROP TABLE IF EXISTS {table_name}\")\n",
    "        df.to_sql(table_name, conn, if_exists='append', index=False)\n",
    "        df_preview = pd.read_sql_query(f\"SELECT * FROM {table_name} LIMIT 5\", conn)\n",
    "        print(f\"\\nPreview of table '{table_name}':\\n\", df_preview)\n",
    "\n",
    "# Save LSTM data to SQLite\n",
    "save_to_sqlite(combined_lstm_df, 'predictions_LSTM.db', 'predictions_LSTM')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Process Customer Clusters"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 3,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "Cluster 1 count: 489\n",
      "Cluster 2 count: 10672\n",
      "Cluster 3 count: 33\n"
     ]
    }
   ],
   "source": [
    "# Function to process customer cluster assignments\n",
    "def process_customer_clusters(csv_file):\n",
    "    df = pd.read_csv(csv_file, header=0, names=['ID', 'cluster'])\n",
    "    df['cluster'] = df['cluster'].replace({0: 1, 1: 2, 2: 3})\n",
    "    cluster_counts = df['cluster'].value_counts()\n",
    "    print(f\"Cluster 1 count: {cluster_counts.get(1, 0)}\")\n",
    "    print(f\"Cluster 2 count: {cluster_counts.get(2, 0)}\")\n",
    "    print(f\"Cluster 3 count: {cluster_counts.get(3, 0)}\")\n",
    "    return df\n",
    "\n",
    "# Process customer cluster assignments\n",
    "customer_clusters_df = process_customer_clusters('data/clusterAssignments.csv')\n"
   ]
  },
  {
   "cell_type": "markdown",
   "metadata": {},
   "source": [
    "Save Customer Clusters to SQLite"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "metadata": {},
   "outputs": [
    {
     "name": "stdout",
     "output_type": "stream",
     "text": [
      "\n",
      "Preview of table 'customer_cluster':\n",
      "          ID  cluster\n",
      "0  10001245        2\n",
      "1  10001255        2\n",
      "2  10003830        2\n",
      "3  10003951        2\n",
      "4  10004155        2\n"
     ]
    }
   ],
   "source": [
    "# Save customer cluster data to SQLite\n",
    "save_to_sqlite(customer_clusters_df, 'predictions_LSTM.db', 'customer_cluster')\n"
   ]
  }
 ],
 "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.11.9"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 2
}