<jupyter_start><jupyter_text>Group About: World Energy Analysis  Cloud Link : http://355.199.170.186:7390 Name : <NAME>, <NAME>  World Energy DatasetThis assignment has used the dataset related to the different distribution of multiple kinds of energy among the countries & locations. We'll be using 12 different comma seperated datasets each defining the Production or the consumption of an energy through various time periods. Following are the used datasets:    1. Petroleum Production              2. Petroleum Consumption    3. Biofuel Production                4. Biofuel Consumption    5. Electricity Production            6. Electricity Consumption    7. Coal Production                   8. Coal Consumption    9. Gas Production                    10. Gas Consumption    11. Total Energy Production          12. Total Energy ConsumptionEach of these datasets have Country as a common field with their values defined over a period of time. Also the units of these values have been recorded in another csv file named 'Units'.  Creating a database in SQLA database in SQL has been created labelled as 'energy.db' which will be consisting of 13 tables as mentioned above.<jupyter_code>import sqlite3
import csv
import pandas as pd
import time<jupyter_output><empty_output><jupyter_text>We need a connection object that represents our database.<jupyter_code>#conn = sqlite3.connect(':memory:')
#energy = 'data/energy.db'
conn = sqlite3.connect('energy.db')
c = conn.cursor()<jupyter_output><empty_output><jupyter_text>Implementation of common QueriesOnce the database has been created each of the csv files are then created as tables and added to the above database.<jupyter_code>#c.execute("DROP TABLE UnitTable;")
# c.execute("""CREATE TABLE UnitTable (
#             Variable text primary key, 
#             Unit text);""")
# with open('Units.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['Variable'], i['Unit']) for i in dr]
    
# c.executemany("INSERT INTO UnitTable (Variable, Unit) VALUES (?, ?);", to_db)
conn.commit()
unitresult = c.execute("SELECT * FROM UnitTable;")
unit = unitresult.fetchall()
unit<jupyter_output><empty_output><jupyter_text>The Petroleum Production Table<jupyter_code>#c.execute("DROP TABLE PetroProd;")
# c.execute("""CREATE TABLE PetroProd (
#             Country text primary key, 
#             Aug_2016 real, 
#             Sep_2016 real, 
#             Oct_2016 real, 
#             Nov_2016 real, 
#             Dec_2016 real, 
#             Jan_2017 real, 
#             Feb_2017 real, 
#             Mar_2017 real, 
#             Apr_2017 real, 
#             May_2017 real, 
#             Jun_2017 real, 
#             Jul_2017 real);""")
# with open('Total Petroleum Production.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['Country'], i['2016M8'], i['2016M9'], i['2016M10'], i['2016M11'], i['2016M12'], i['2017M1'], i['2017M2'], i['2017M3'], i['2017M4'], i['2017M5'], i['2017M6'], i['2017M7']) for i in dr]
    
# c.executemany("INSERT INTO PetroProd (Country, Aug_2016, Sep_2016, Oct_2016, Nov_2016, Dec_2016, Jan_2017, Feb_2017, Mar_2017, Apr_2017, May_2017, Jun_2017, Jul_2017) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_PetroP = c.execute("SELECT * FROM PetroProd;")
PetroP = result_PetroP.fetchall()
PetroP<jupyter_output><empty_output><jupyter_text>The Petroleum Consumption Table<jupyter_code>#c.execute("DROP TABLE PetroCons;")
# c.execute("""CREATE TABLE PetroCons (
#             Country text primary key, 
#             Feb_2016 real, 
#             Mar_2016 real, 
#             Apr_2016 real, 
#             May_2016 real, 
#             Jun_2016 real, 
#             Jul_2016 real, 
#             Aug_2016 real, 
#             Sep_2016 real, 
#             Oct_2016 real, 
#             Nov_2016 real, 
#             Dec_2016 real, 
#             Jan_2017 real);""")
# with open('Total Petroleum Consumption.csv') as fin:
#     dr = csv.DictReader(fin)
#     to_db = [(i['Country'], i['2016M2'], i['2016M3'], i['2016M4'], i['2016M5'], i['2016M6'], i['2016M7'], i['2016M8'], i['2016M9'], i['2016M10'], i['2016M11'], i['2016M12'], i['2017M1']) for i in dr]
    
# c.executemany("INSERT INTO PetroCons (Country, Feb_2016, Mar_2016, Apr_2016, May_2016, Jun_2016, Jul_2016, Aug_2016, Sep_2016, Oct_2016, Nov_2016, Dec_2016, Jan_2017) VALUES (?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_PetroC = c.execute("SELECT * FROM PetroCons;")
PetroC = result_PetroC.fetchall()
PetroC<jupyter_output><empty_output><jupyter_text>The Biofuel Production Table<jupyter_code>#c.execute("DROP TABLE BiofuelProd;")
# c.execute("""CREATE TABLE BiofuelProd (
#             Country text primary key, 
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Biofuel Production_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO BiofuelProd (Country, Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_BioP = c.execute("SELECT * FROM BiofuelProd;")
BioP = result_BioP.fetchall()
BioP<jupyter_output><empty_output><jupyter_text>The Biofuel Consumption Table<jupyter_code>#c.execute("DROP TABLE BiofuelCons;")
# c.execute("""CREATE TABLE BiofuelCons (
#             Country text primary key, 
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Biofuels Consumption_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO BiofuelCons (Country, Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_BioC = c.execute("SELECT * FROM BiofuelCons;")
BioC = result_BioC.fetchall()
BioC<jupyter_output><empty_output><jupyter_text>The Electricity Production Table<jupyter_code>#c.execute("DROP TABLE ElectProd;")
# c.execute("""CREATE TABLE ElectProd (
#             Country text primary key,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Electricity Gerneration_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO ElectProd (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_ElectP = c.execute("SELECT * FROM ElectProd;")
ElectP = result_ElectP.fetchall()
ElectP<jupyter_output><empty_output><jupyter_text>The Electricity Consumption Table<jupyter_code>#c.execute("DROP TABLE ElectCons;")
# c.execute("""CREATE TABLE ElectCons (
#             Country text primary key,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Electricity Net Consumption_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO ElectCons (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_ElectC = c.execute("SELECT * FROM ElectCons;")
ElectC = result_ElectC.fetchall()
ElectC<jupyter_output><empty_output><jupyter_text>The Coal Production Table<jupyter_code>#c.execute("DROP TABLE CoalProd;")
# c.execute("""CREATE TABLE CoalProd (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Coal Primary Production_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO CoalProd (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_CoalP = c.execute("SELECT * FROM CoalProd;")
CoalP = result_CoalP.fetchall()
CoalP<jupyter_output><empty_output><jupyter_text>The Coal Consumption Table<jupyter_code>#c.execute("DROP TABLE CoalCons;")
# c.execute("""CREATE TABLE CoalCons (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Coal Primary Consumption_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO CoalCons (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_CoalC = c.execute("SELECT * FROM CoalCons;")
CoalC = result_CoalC.fetchall()
CoalC<jupyter_output><empty_output><jupyter_text>The Gas Production Table<jupyter_code>#c.execute("DROP TABLE GasProd;")
# c.execute("""CREATE TABLE GasProd (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Dry Natural Gas Production_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO GasProd (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_GasP = c.execute("SELECT * FROM GasProd;")
GasP = result_GasP.fetchall()
GasP<jupyter_output><empty_output><jupyter_text>The Gas Consumption Table<jupyter_code>#c.execute("DROP TABLE GasCons;")
# c.execute("""CREATE TABLE GasCons (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Dry Natural Gas Consumption_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO GasCons (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_GasC = c.execute("SELECT * FROM GasCons;")
GasC = result_GasC.fetchall()
GasC<jupyter_output><empty_output><jupyter_text>The Total Energy Production Table<jupyter_code>#c.execute("DROP TABLE TotalProd;")
# c.execute("""CREATE TABLE TotalProd (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Energy Production_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO TotalProd (Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_TotalP = c.execute("SELECT * FROM TotalProd;")
TotalP = result_TotalP.fetchall()
TotalP<jupyter_output><empty_output><jupyter_text>The Total Energy Consumption Table<jupyter_code>#c.execute("DROP TABLE TotalCons;")
# c.execute("""CREATE TABLE TotalCons (
#             Country text,
#             Y1990 real,
#             Y2000 real,
#             Y2005 real,
#             Y2010 real,
#             Y2011 real,
#             Y2012 real,
#             Y2013 real,
#             Y2014 real);""")
# with open('Total Energy Consumption_f.csv') as fin:
#     dr = csv.DictReader(fin)
    
#     to_db = [(i['location'], i['1990'],i['2000'], i['2005'], i['2010'], i['2011'], i['2012'], i['2013'], i['2014']) for i in dr]
    
# c.executemany("INSERT INTO TotalCons(Country,Y1990,Y2000, Y2005, Y2010, Y2011, Y2012, Y2013, Y2014) VALUES (?,?, ?, ?, ?, ?, ?, ?, ?);", to_db)
conn.commit()
result_TotalC = c.execute("SELECT * FROM TotalCons;")
TotalC = result_TotalC.fetchall()
TotalC<jupyter_output><empty_output><jupyter_text>List of Queries to be performed with their time implementation* **Query 1 : Displaying all the data from total energy production and total energy consumption with SELECT and WHERE Query*** **Query 2 : Using SUM & AVG as calculation function along with Select and Order to get an Average Consumption per Country*** **Query 3 : Using GROUP BY and Comparison in the WHERE clause to select those countries which have consumption rate greater than the production rate in the year 1990*** **Query 4 : Applying ORDER BY queries to get the list in ascending/descending order*** **Query 5 : Getting a Difference in the utilization of the Total Energy*** **Query 6 : INNER JOIN, between the two tables*** **Query 7 : Subquery implemented resulting into finding out those countries having the production rate greater than 0 in year 2010**  QUERY 1<jupyter_code>tic = time.time()
TotalConsProd= pd.read_sql('''SELECT c.Country, c.Y1990 as 'Consumption 1990',p.Y1990 as'Production 1990' , 
                                                c.Y2000 as 'Consumption 2000',p.Y2000 as'Production 2000' ,
                                                c.Y2005 as 'Consumption 2005',p.Y2005 as'Production 2005' ,
                                                c.Y2010 as 'Consumption 2010',p.Y2010 as'Production 2010' ,
                                                c.Y2011 as 'Consumption 2011',p.Y2011 as'Production 2011' ,
                                                c.Y2012 as 'Consumption 2012',p.Y2012 as'Production 2012' ,
                                                c.Y2013 as 'Consumption 2013',p.Y2013 as'Production 2013' ,
                                                c.Y2014 as 'Consumption 2014',p.Y2014 as'Production 2014'
                            FROM TotalCons c,TotalProd p
                            WHERE c.Country = p.Country;''', conn)
toc = time.time()
tq1 = toc-tic
print ("Query run time:", tq1)
TotalConsProd<jupyter_output>Query run time: 0.06092381477355957<jupyter_text>QUERY 2<jupyter_code>tic = time.time()
TotalConsAVG= pd.read_sql_query('SELECT Country, SUM(Y1990+Y2000+Y2005+Y2010+Y2011+Y2012+Y2013+Y2014)/9 as `Total_Energy_Avg_Cons`'
                       'FROM TotalCons '
                       'GROUP BY Country '
                       'ORDER BY -Total_Energy_Avg_Cons', conn)
toc = time.time()
tq2 = toc-tic
print ("Query run time:", tq2)
TotalConsAVG<jupyter_output>Query run time: 0.0015578269958496094<jupyter_text>QUERY 3<jupyter_code>tic = time.time()
TotalCons_Prod1990= pd.read_sql_query('''SELECT TotalCons.Country,TotalCons.Y1990 AS 'Consumption1990',TotalProd.Y1990 AS'Production1990' 
                                      FROM TotalCons, TotalProd 
                                      WHERE TotalCons.Country=TotalProd.Country 
                                      AND TotalCons.Y1990 > TotalProd.Y1990 
                                      Group BY TotalCons.Country''', conn)
toc = time.time()
tq3 = toc-tic
print ("Query run time:", tq3)
TotalCons_Prod1990<jupyter_output>Query run time: 0.0016813278198242188<jupyter_text>QUERY 4<jupyter_code>tic = time.time()
TotalCons_Prod1990= pd.read_sql_query('''SELECT TotalCons.Country,TotalCons.Y1990 AS 'Consumption1990',TotalProd.Y1990 AS'Production1990' 
                                      FROM TotalCons, TotalProd 
                                      WHERE TotalCons.Country=TotalProd.Country 
                                      AND TotalCons.Y1990 > TotalProd.Y1990 
                                      Group BY TotalCons.Country 
                                      ORDER BY TotalProd.Y1990 DESC''', conn)
toc = time.time()
tq4a = toc-tic
print ("Query run time:", tq4a)
TotalCons_Prod1990
tic = time.time()
TotalCons_Prod1990= pd.read_sql_query('''SELECT TotalCons.Country,TotalCons.Y1990 AS 'Consumption1990',TotalProd.Y1990 AS'Production1990' 
                                       FROM TotalCons, TotalProd 
                                       WHERE TotalCons.Country=TotalProd.Country 
                                       AND TotalCons.Y1990 < TotalProd.Y1990 
                                       Group BY TotalCons.Country''', conn)
toc = time.time()
tq4b = toc-tic
print ("Query run time:", tq4b)
TotalCons_Prod1990<jupyter_output>Query run time: 0.0014498233795166016<jupyter_text>QUERY 5<jupyter_code>tic = time.time()
TotalCons_Prod1990= pd.read_sql_query('''SELECT TotalCons.Country,TotalCons.Y1990 AS 'Consumption 1990',TotalProd.Y1990 AS'Production 1990',
                                                                  TotalCons.Y1990-TotalProd.Y1990 AS 'Diffrence in 1990' 
                                        FROM TotalCons, TotalProd 
                                        WHERE TotalCons.Country=TotalProd.Country 
                                        AND TotalCons.Y1990 > TotalProd.Y1990 
                                        Group BY TotalCons.Country 
                                        LIMIT 30''', conn)
toc = time.time()
tq5 = toc-tic
print ("Query run time:", tq5)
TotalCons_Prod1990<jupyter_output>Query run time: 0.0015172958374023438<jupyter_text>QUERY 6<jupyter_code>tic = time.time()
x= pd.read_sql_query('''SELECT c.Country,c.Y2010 ,p.Y2010 
                                      FROM TotalCons c
                                      INNER JOIN TotalProd p
                                      ON c.Country = p.Country''', conn)
toc = time.time()
tq6 = toc-tic
print ("Query run time:", tq6)
x<jupyter_output>Query run time: 0.0015366077423095703<jupyter_text>QUERY 7<jupyter_code>tic = time.time()
y= pd.read_sql_query('''SELECT Country
                        FROM TotalCons
                        WHERE Y2010 IN (SELECT Y2010 
                                        FROM TotalProd
                                        WHERE Y2010 >0)''', conn)
toc = time.time()
tq7 = toc-tic
print ("Query run time:", tq7)
y<jupyter_output>Query run time: 0.0010294914245605469<jupyter_text>Time Comparison<jupyter_code>print("Query 1: SELECT Query -",tq1*1000)
print("Query 2: SUM & AVG Query -",tq2*1000)
print("Query 3: GROUP BY Query -",tq3*1000)
print("Query 4: ORDER BY Query -",tq4a*1000)
print("Query 5: Difference Query -",tq5*1000)
print("Query 6: INNER JOIN Query -",tq6*1000)
print("Query 7: SUBQUERY -",tq7*1000)<jupyter_output>Query 1: SELECT Query - 60.92381477355957
Query 2: SUM & AVG Query - 1.5578269958496094
Query 3: GROUP BY Query - 1.6813278198242188
Query 4: ORDER BY Query - 1.6937255859375
Query 5: Difference Query - 1.5172958374023438
Query 6: INNER JOIN Query - 1.5366077423095703
Query 7: SUBQUERY - 1.0294914245605469
{'fraction_non_alphanumeric': 0.13578210894552722, 'fraction_numerical': 0.09800099950024987, 'mean_word_length': 2.6088367899008116, 'pattern_counts': {'":': 0, '<': 88, '<?xml version=': 0, '>': 91, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 1, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 50, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '23', 'dir': 'jupyter-structured-clean-dedup', 'id': '591461', 'license': 'MIT', 'n_tokens_mistral': 7966, 'n_tokens_neox': 6329, 'n_words': 1614, 'path': "SQL_information_collection'/SQL_Energy.ipynb", 'repo_name': 'umairakhtar/potential-world-energy', 'size': '20019'}