Create_Push_Synthetic_Data.ipynb

{
 "cells": [
  {
   "cell_type": "code",
   "execution_count": 1,
   "id": "80a71eb6",
   "metadata": {},
   "outputs": [],
   "source": [
    "import datetime as df\n",
    "import requests\n",
    "import random\n",
    "import math\n",
    "import mysql.connector\n",
    "from datetime import datetime, timedelta\n",
    "import json\n",
    "from config import api_key, db_credentials"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 4,
   "id": "df8e8119",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Connecting to our database ThermoStatData\n",
    "def connect_to_database():\n",
    "    host = db_credentials['host']\n",
    "    user = db_credentials['user']\n",
    "    password = db_credentials['password']\n",
    "    database = db_credentials['database']\n",
    "    cnx = mysql.connector.connect(\n",
    "        host=host,\n",
    "        user=user,\n",
    "        password=password,\n",
    "    )\n",
    "\n",
    "    return cnx\n",
    "\n",
    "cnx = connect_to_database()\n",
    "cursor = cnx.cursor()"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 5,
   "id": "9e330d91",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating Synthetic Data \n",
    "\n",
    "monthly_avg_temps = [(0, 8), (0, 9), (2, 12), (6, 16), (11, 21), \n",
    "                     (14, 25), (16, 27), (16, 27), (13, 23), (10, 19), \n",
    "                     (5, 13), (1, 9)]\n",
    "\n",
    "\n",
    "def select_internal_temp(temp):\n",
    "    if -30 <= temp <= -20:\n",
    "        return round(random.uniform(27, 30), 2)\n",
    "    elif -20 < temp <= -10:\n",
    "        return round(random.uniform(25, 30), 2)\n",
    "    elif -10 < temp <= 0:\n",
    "        return round(random.uniform(20, 27), 2)\n",
    "    elif 0 < temp <= 10:\n",
    "        return round(random.uniform(20, 22), 2)\n",
    "    elif 10 < temp <= 20:\n",
    "        return round(random.uniform(19, 21), 2)\n",
    "    elif 20 < temp <= 27:\n",
    "        return 22\n",
    "    elif 27 < temp <= 32:\n",
    "        return round(random.uniform(17, 19), 2)\n",
    "    elif 32 < temp <= 39:\n",
    "        return round(random.uniform(16, 18), 2)\n",
    "    elif temp > 40:\n",
    "        return 16\n",
    "    else:\n",
    "        print(f\"Error: unexpected temperature {temp}\")\n",
    "        return -999\n",
    "\n",
    "\n",
    "\n",
    "def generate_data(start_date, end_date):\n",
    "    \n",
    "    # Connect to the database\n",
    "    cnx = connect_to_database()\n",
    "    cursor = cnx.cursor()\n",
    "    \n",
    "    current_date = start_date\n",
    "    data = []\n",
    "    while current_date <= end_date:\n",
    "        month = current_date.month - 1\n",
    "        avg_temp = (monthly_avg_temps[month][0] + monthly_avg_temps[month][1]) / 2\n",
    "        # use a normal distribution to generate a temperature around the average\n",
    "        temp = round(random.gauss(avg_temp, 2), 2)\n",
    "        # \"feels like\" temperature can be up to 2 degrees different\n",
    "        feels_like = round(temp + random.uniform(-2, 2), 2)\n",
    "        humidity = random.randint(30, 100)\n",
    "\n",
    "        if temp > 5:\n",
    "            # It can't snow if it's above 5 degrees.\n",
    "            weather = random.choice(['Sunny', 'Rainy', 'Cloudy', 'Windy', 'Stormy','Broken clouds'])\n",
    "        else:\n",
    "            weather = random.choice(['Sunny', 'Rainy', 'Cloudy', 'Snowy','Stormy','Broken Clouds'])\n",
    "\n",
    "        wind_speed = round(random.uniform(0, 30), 2)\n",
    "        \n",
    "        internal_temp = select_internal_temp(feels_like)\n",
    "        \n",
    "        data.append((current_date, temp, feels_like, humidity, weather, wind_speed, internal_temp))\n",
    "        current_date += timedelta(days=1)\n",
    "    return data\n",
    "\n",
    "\n",
    "# Push Synthetic Data into Database\n",
    "\n",
    "def push_data_to_db(data):\n",
    "   \n",
    "    # Connect to the database\n",
    "    cnx = connect_to_database()\n",
    "    cursor = cnx.cursor()\n",
    "    \n",
    "    cursor.execute(\"USE ThermoStatData\")\n",
    "\n",
    "    \n",
    "    # Create the table if it does not exist\n",
    "    cursor.execute(\"\"\"CREATE TABLE IF NOT EXISTS nine (\n",
    "      date DATE,\n",
    "      temperature FLOAT,\n",
    "      feels_like FLOAT,\n",
    "      humidity INT,\n",
    "      weather VARCHAR(255),\n",
    "      wind_speed FLOAT,\n",
    "      internal_temp FLOAT\n",
    "    )\n",
    "    \"\"\")        \n",
    "    add_weather = (\"INSERT INTO nine \"\n",
    "                   \"(date, temperature, feels_like, humidity, weather, wind_speed, internal_temp) \"\n",
    "                   \"VALUES (%s, %s, %s, %s, %s, %s, %s)\")\n",
    "\n",
    "    cursor.executemany(add_weather, data)\n",
    "\n",
    "    cnx.commit()\n",
    "    cursor.close()\n",
    "    cnx.close()\n",
    "\n",
    "start_date = datetime(2019, 5, 5)\n",
    "end_date = datetime(2023, 5, 5)\n",
    "data = generate_data(start_date, end_date)\n",
    "push_data_to_db(data)"
   ]
  },
  {
   "cell_type": "code",
   "execution_count": 7,
   "id": "7e05a775",
   "metadata": {},
   "outputs": [],
   "source": [
    "# Creating CSV file from data created in DB (Not necessary but for our reference if we want to train model with CSV)\n",
    "import csv\n",
    "\n",
    "def fetch_data_from_db():\n",
    "    \n",
    "    # Connect to the database\n",
    "    cnx = connect_to_database()\n",
    "    cursor = cnx.cursor()\n",
    "    \n",
    "    query = (\"SELECT * FROM nine\")\n",
    "    cursor.execute(query)\n",
    "\n",
    "    with open('nine.csv', 'w', newline='') as file:\n",
    "        writer = csv.writer(file)\n",
    "        # Write the header\n",
    "        writer.writerow([\"Date\", \"Temperature\", \"Feels Like\", \"Humidity\", \"Weather\", \"Wind Speed\", \"Internal Temp\"])\n",
    "        # Write the data\n",
    "        for (date, temperature, feels_like, humidity, weather, wind_speed, internal_temp) in cursor:\n",
    "            writer.writerow([date, temperature, feels_like, humidity, weather, wind_speed, internal_temp])\n",
    "\n",
    "    cursor.close()\n",
    "    cnx.close()\n",
    "\n",
    "fetch_data_from_db()"
   ]
  }
 ],
 "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.8.8"
  }
 },
 "nbformat": 4,
 "nbformat_minor": 5
}