Restructred the file structure and Fixed the Notebook File with Comments

7 files changed, 101 insertions, 87 deletions

diff --git a/database_files/GlobalLandTemperaturesByCity.csv b/GlobalLandTemperaturesByCity.csv
index b980b00..b980b00 100644
--- a/database_files/GlobalLandTemperaturesByCity.csv
+++ b/GlobalLandTemperaturesByCity.csv
diff --git a/dashboard/app.py b/app.py
index 4732794..5c97d70 100644
--- a/dashboard/app.py
+++ b/app.py
@@ -19,7 +19,7 @@ def get_list_of_dict(keys, list_of_tuples):
      return list_of_dict
 
 def get_cities_list():
-    conn = sql.connect("./database_files/pythonproject.db")
+    conn = sql.connect("database.db")
     sql_statement = 'SELECT DISTINCT(City_Name) as City_Name, Lat, Long FROM City_table join Loc_Table on City_Table.City_Id = Loc_table.City_Id'
     df=pd.read_sql_query(sql_statement, conn).to_records(index=False)
     return df
diff --git a/database_files/pythonproject.db b/database.db
index 055da59..055da59 100644
--- a/database_files/pythonproject.db
+++ b/database.db
diff --git a/database_files/dbcreation.ipynb b/dbcreation.ipynb
index 95e6ba0..7f0f642 100644
--- a/database_files/dbcreation.ipynb
+++ b/dbcreation.ipynb
@@ -2,18 +2,31 @@
  "cells": [
   {
    "cell_type": "code",
-   "execution_count": 1,
-   "id": "50043e80",
+   "execution_count": null,
+   "id": "660de5e8",
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Imports\n",
+    "\n",
     "from IPython.display import display, HTML\n",
     "import pandas as pd\n",
     "import sqlite3\n",
     "from sqlite3 import Error\n",
+    "import os"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "1dc168cb",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "# Function to create a connection to the database\n",
     "\n",
     "def create_connection(db_file, delete_db=False):\n",
-    "    import os\n",
+    "    \n",
     "    if delete_db and os.path.exists(db_file):\n",
     "        os.remove(db_file)\n",
     "\n",
@@ -24,16 +37,35 @@
     "    except Error as e:\n",
     "        print(e)\n",
     "\n",
-    "    return conn\n",
-    "\n",
+    "    return conn"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "910875f4",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "# Function to create a database\n",
     "\n",
     "def create_table(conn, create_table_sql):\n",
     "    try:\n",
     "        c = conn.cursor()\n",
     "        c.execute(create_table_sql)\n",
     "    except Error as e:\n",
-    "        print(e)\n",
-    "        \n",
+    "        print(e)"
+   ]
+  },
+  {
+   "cell_type": "code",
+   "execution_count": null,
+   "id": "36907a1c",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "# Execute SQL statement\n",
+    "\n",
     "def execute_sql_statement(sql_statement, conn):\n",
     "    cur = conn.cursor()\n",
     "    cur.execute(sql_statement)\n",
@@ -231,10 +263,10 @@
     }
    ],
    "source": [
-    "import pandas as pd\n",
+    "# Read the Non-Normalized Data\n",
+    "\n",
     "df=pd.read_csv(\"GlobalLandTemperaturesByCity.csv\")\n",
     "data=df[df.dt>\"1949-12-31\"]\n",
-    "data\n",
     "    "
    ]
   },
@@ -1256,10 +1288,11 @@
     }
    ],
    "source": [
+    "# Extract cities and their latitude and longitude\n",
+    "\n",
     "data_for_loc_table=data[['City','Latitude','Longitude']]\n",
     "data_for_loc_table=data_for_loc_table.to_records(index=False)\n",
-    "result_data_for_loc_table=list(data_for_loc_table)\n",
-    "result_data_for_loc_table"
+    "result_data_for_loc_table=list(data_for_loc_table)"
    ]
   },
   {
@@ -1269,15 +1302,13 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Extract Countries\n",
+    "\n",
     "records=data.to_records(index=False)\n",
     "result=list(records)\n",
     "req_resut=[]\n",
-    "#j=0\n",
     "for i in result:\n",
-    "   # j=j+1\n",
-    "    req_resut.append((i[4],))\n",
-    "    #if j==10:\n",
-    "      # break\n"
+    "    req_resut.append((i[4],))\n"
    ]
   },
   {
@@ -1456,26 +1487,9 @@
     }
    ],
    "source": [
-    "req_resut=list((set(req_resut)))\n",
-    "req_resut"
-   ]
-  },
-  {
-   "cell_type": "code",
-   "execution_count": 6,
-   "id": "0551a70c",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "#req_result_2=[]\n",
-    "#j=0\n",
-    "#for i in result:\n",
-    "#    j=j+1\n",
-    "    \n",
-    "#    req_result_2.append((i[3],i[4]))\n",
-    "#    if j==10:\n",
-    " #       break\n",
-    "#print(req_result_2)\n"
+    "# Convert the list of cities into a list with unique names\n",
+    "\n",
+    "req_resut=list((set(req_resut)))"
    ]
   },
   {
@@ -1485,7 +1499,8 @@
    "metadata": {},
    "outputs": [],
    "source": [
-    "conn = create_connection('pythonproject.db')\n",
+    "# Create a connection to the database\n",
+    "conn = create_connection('database.db')\n",
     "cur=conn.cursor()"
    ]
   },
@@ -1507,28 +1522,12 @@
     }
    ],
    "source": [
+    "# Drop City Table and the Country Table if they exist and create a new Country Table and Insert the data\n",
+    "\n",
     "execute_sql_statement(\"DROP TABLE IF EXISTS City_Table\",conn)\n",
     "execute_sql_statement(\"DROP TABLE IF EXISTS Country_Table\",conn)\n",
-    "#execute_sql_statement(\"DROP TABLE IF EXISTS City_Table\",conn)\n",
-    "#execute_sql_statement(\"DROP TABLE IF EXISTS Temperature_Table\",conn)\n",
     "create_table(conn,'''CREATE TABLE Country_Table (PK INTEGER  PRIMARY KEY, Country_Name VARCHAR)''')\n",
-    "#create_table(conn,'''CREATE TABLE City_Table (PK INTEGER  PRIMARY KEY, City_Name VARCHAR , Country_Name VARCHAR,\n",
-    "#FOREIGN KEY(Country_Name) REFERENCES Country_Table(Country_Name))''')\n",
-    "#create_table(conn,'''CREATE TABLE Temperature_Table (PK INTEGER  PRIMARY KEY, DATE VARCHAR , Avg_Temp VARCHAR , City_Name VARCHAR , Country_Name VARCHAR,\n",
-    "#FOREIGN KEY(City_Name) REFERENCES City_Table(City_Name),FOREIGN KEY(Country_Name) REFERENCES Country_Table(Country_Name))''')\n",
-    "# for i in result:\n",
-    "#     a,b,c=i[4],i[5],i[6]\n",
-    "    #print(type(a),type(b),type(c))\n",
-    "    #print(a,b,c)\n",
-    "    #print(\"\"\"INSERT INTO Country_Table ('Country_Name','LAT','LONG') Values ('\"\"\"+i[4]+\"'\"+\",'\"+i[5]+\"','\"+i[6]+\"')\")\n",
-    "    #print(a,b,c)\n",
-    "    #print(f'''INSERT INTO Country_Table ('Country_Name','LAT','LONG') Values ('{i[4]}','{i[5]}','{i[6]}')''')\n",
-    "cur.executemany(\"INSERT INTO Country_Table ('Country_Name') Values (?)\",req_resut)\n",
-    "#cur.executemany(\"INSERT INTO City_Table ('City_Name','Country_Name') Values (?,?)\",req_result_2)\n",
-    "#cur.executemany(\"INSERT INTO Temperature_Table ('DATE','Avg_Temp','City_Name','Country_Name') Values (?,?,?,?)\",req_result_3)\n",
-    "\n",
-    "\n",
-    "    \n"
+    "cur.executemany(\"INSERT INTO Country_Table ('Country_Name') Values (?)\",req_resut)"
    ]
   },
   {
@@ -1538,11 +1537,11 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Make a dictionary of the country data and their IDs\n",
     "abc=execute_sql_statement(\"Select * from Country_Table\", conn)\n",
     "abc1={}\n",
     "for i in abc:\n",
-    "    abc1[i[1]]=i[0]\n",
-    "#abc1\n"
+    "    abc1[i[1]]=i[0]"
    ]
   },
   {
@@ -1553,15 +1552,8 @@
    "outputs": [],
    "source": [
     "req_result_2=[]\n",
-    "#j=0\n",
     "for i in result:\n",
-    "    \n",
-    "    #j=j+1\n",
-    "    req_result_2.append((i[3],abc1[i[4]]))\n",
-    "   # if j==10:\n",
-    "       #break\n",
-    "#print(req_result_2)\n",
-    "\n"
+    "    req_result_2.append((i[3],abc1[i[4]]))"
    ]
   },
   {
@@ -1571,6 +1563,7 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Create the City Table\n",
     "create_table(conn,'''CREATE TABLE City_Table (City_Id INTEGER  PRIMARY KEY, City_Name VARCHAR , Country_ID INTEGER,\n",
     "FOREIGN KEY(Country_ID) REFERENCES Country_Table(PK))''')"
    ]
@@ -1582,15 +1575,12 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Extract the data for City Table\n",
     "city=[]\n",
     "for i in result:\n",
     "   # j=j+1\n",
     "    city.append((i[3],abc1[i[4]]))\n",
-    "city_country_unique=list((set(city)))\n",
-    "\n",
-    "#for i in city_country_unique:\n",
-    " #   i[1]=abc1[i[1]]\n",
-    "#city_country_unique   "
+    "city_country_unique=list((set(city)))"
    ]
   },
   {
@@ -1611,6 +1601,7 @@
     }
    ],
    "source": [
+    "# Insert the data for City Table\n",
     "cur.executemany(\"INSERT INTO City_Table ('City_Name','Country_ID') Values (?,?)\",city_country_unique)"
    ]
   },
@@ -1747,6 +1738,20 @@
   },
   {
    "cell_type": "code",
+   "execution_count": null,
+   "id": "3692d2ea",
+   "metadata": {},
+   "outputs": [],
+   "source": [
+    "# Build a dictionary of the city data and their IDs\n",
+    "city_index=execute_sql_statement(\"Select * from City_Table\", conn)\n",
+    "city_indexes={}\n",
+    "for i in abc:\n",
+    "    abc1[i[1]]=i[0]"
+   ]
+  },
+  {
+   "cell_type": "code",
    "execution_count": 17,
    "id": "9dd4ef62",
    "metadata": {},
@@ -1859,6 +1864,7 @@
     }
    ],
    "source": [
+    "# Displaying the country data\n",
     "df1=pd.read_sql_query(\"Select * from Country_Table\", conn)\n",
     "display(df1)"
    ]
@@ -1890,6 +1896,8 @@
     }
    ],
    "source": [
+    "# Join the data from the City Table and the Country Table and display the data\n",
+    "\n",
     "execute_sql_statement(\"select * from City_Table join Country_Table on Country_Table.PK = City_Table.Country_ID AND Country_Table.Country_Name='India' LIMIT 10\",conn)"
    ]
   },
@@ -2911,6 +2919,8 @@
     }
    ],
    "source": [
+    "# City IDs Dictionary\n",
+    "\n",
     "city_table_values=execute_sql_statement(\"select * from City_Table \",conn)\n",
     "city_ids={}\n",
     "for i in city_table_values:\n",
@@ -2926,6 +2936,8 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Create the Temperature Table\n",
+    "\n",
     "create_table(conn,'''CREATE TABLE Temperature (PK INTEGER  PRIMARY KEY , Date VARCHAR, Avg_Temperature VARCHAR, City_ID INTEGER,\n",
     "FOREIGN KEY(City_ID) REFERENCES City_Table(City_Id))''')"
    ]
@@ -2937,12 +2949,13 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Allocate the data to the Temperature Table and their corresponding City IDs\n",
+    "\n",
     "temp_table_values=[]\n",
     "j=1\n",
     "for i in result:\n",
     "    j=j+1\n",
-    "    temp_table_values.append((i[0],i[1],city_ids[i[3]]))\n",
-    "#temp_table_values"
+    "    temp_table_values.append((i[0],i[1],city_ids[i[3]]))"
    ]
   },
   {
@@ -2963,6 +2976,8 @@
     }
    ],
    "source": [
+    "# Insert the data into the Temperature Table\n",
+    "\n",
     "cur.executemany(\"INSERT INTO Temperature ('Date','Avg_Temperature','City_ID') Values (?,?,?)\",temp_table_values)"
    ]
   },
@@ -3104,6 +3119,8 @@
     }
    ],
    "source": [
+    "# Displaying the Temperature Table\n",
+    "\n",
     "df1=pd.read_sql_query(\"Select * from Temperature\", conn)\n",
     "display(df1)"
    ]
@@ -3115,6 +3132,8 @@
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Create the Location Table\n",
+    "\n",
     "create_table(conn,'''CREATE TABLE Loc_Table (ID INTEGER  PRIMARY KEY , City_ID INTEGER, LAT FLOAT,LONG FLOAT,\n",
     "FOREIGN KEY(City_ID) REFERENCES City_Table(City_ID))''')"
    ]
@@ -4137,6 +4156,8 @@
     }
    ],
    "source": [
+    "# Extract the data for the Location Table, Database consists of N,W,E,S notation, we are going to convert them to the decimal notation\n",
+    "\n",
     "#Lat= North is positive, South is negative\n",
     "#Long= West is negative, East is positive\n",
     "data_to_check_loc=result_data_for_loc_table\n",
@@ -4176,6 +4197,8 @@
     }
    ],
    "source": [
+    "# Insert the data into the Location Table\n",
+    "\n",
     "cur.executemany(\"INSERT INTO Loc_Table ('City_ID','LAT','LONG') Values (?,?,?)\",data_to_input_loc_table)"
    ]
   },
@@ -4317,6 +4340,8 @@
     }
    ],
    "source": [
+    "# Displaying the Location Table\n",
+    "\n",
     "df1=pd.read_sql_query(\"Select * from Loc_Table \", conn)\n",
     "display(df1)"
    ]
@@ -4335,24 +4360,13 @@
   },
   {
    "cell_type": "code",
-   "execution_count": 15,
-   "id": "3fd0999a",
-   "metadata": {},
-   "outputs": [],
-   "source": [
-    "city_index=execute_sql_statement(\"Select * from City_Table\", conn)\n",
-    "city_indexes={}\n",
-    "for i in abc:\n",
-    "    abc1[i[1]]=i[0]"
-   ]
-  },
-  {
-   "cell_type": "code",
    "execution_count": 29,
    "id": "cbc5ad63",
    "metadata": {},
    "outputs": [],
    "source": [
+    "# Commit the changes\n",
+    "\n",
     "conn.commit()"
    ]
   }
diff --git a/dashboard/requirements.txt b/requirements.txt
index 6ec5840..6ec5840 100644
--- a/dashboard/requirements.txt
+++ b/requirements.txt
diff --git a/dashboard/static/css/main.css b/static/css/main.css
index 93d5ae3..93d5ae3 100644
--- a/dashboard/static/css/main.css
+++ b/static/css/main.css
diff --git a/dashboard/templates/index.html b/templates/index.html
index 94dccc2..94dccc2 100644
--- a/dashboard/templates/index.html
+++ b/templates/index.html