<jupyter_start><jupyter_text>lcld_analysisThis notebook demonstrates how to import and analyse the 'low-carbon-london-data' data from the [SmartMeter Energy Consumption Data in London Households](https://data.london.gov.uk/dataset/smartmeter-energy-use-data-in-london-households) dataset.  Step 1: import packages<jupyter_code>import sqlite3
import csv, os, time<jupyter_output><empty_output><jupyter_text>See: https://docs.python.org/3/library/sqlite3.html for more details on the sqllite3 package  Step 2: Create a connection to a new sqlite databaseThis creates the database file if it doesn't already exist.<jupyter_code>conn = sqlite3.connect('low-carbon-london-data.sqlite')
c = conn.cursor()<jupyter_output><empty_output><jupyter_text>You should now see the file in your local folder.  Step 3: Remove the lcld table if it existsIf the database already exists, this removes the 'lcld' table so that it can be created anew.<jupyter_code>try:
    c.execute("DROP TABLE lcld;")
    conn.commit()
except sqlite3.OperationalError:
    pass<jupyter_output><empty_output><jupyter_text>Step 4: Create a new table named 'lcld' and set the column names and data types:<jupyter_code>c.execute("""CREATE TABLE lcld
                (LCLid TEXT, 
                stdorToU TEXT, 
                DateTime TEXT, 
                KWH_hh REAL, 
                Acorn TEXT, 
                Acorn_grouped TEXT);""")
conn.commit()<jupyter_output><empty_output><jupyter_text>Step 5: Set an index on column 'LCLid'This will speed up any querys which use this column.<jupyter_code>c.execute("CREATE INDEX index_LCLid ON lcld(LCLid);")
conn.commit()
c.execute("PRAGMA index_list('lcld');").fetchall()<jupyter_output><empty_output><jupyter_text>Step 6: Imports the data from the 11GB csv file.This takes a long time to run (i.e. 70 minutes), and the size of the 'low-carbon-london-data.sqlite' created above will increase to approx 17 GB.**NOTE: The filepath `fp` variable should be modified to the directory location of the csv file on the local computer.****NOTE: The filepath `rows_to_import` variable should be modified the number of rows to import - for all rows set as `None`**<jupyter_code>fp=os.path.join(os.pardir,os.pardir,os.pardir,os.pardir,'_Data','SmartMeter_Energy_Consumption_Data')
filename='Power-Networks-LCL-June2015(withAcornGps)v2.csv'

rows_to_import=None

start_file_size=os.stat('low-carbon-london-data.sqlite').st_size
start_time=time.perf_counter()

with open (os.path.join(fp,filename),'r') as infile:
    
    content = csv.DictReader(infile, delimiter=',')
    
    for i,line in enumerate(content):
                
        try:
            d=float(line['KWH/hh (per half hour) '])
        except ValueError:
            d=None
                
        c.execute("INSERT INTO lcld VALUES (:a, :b, :c, :d, :e, :f)",
                      {'a':line['LCLid'], 
                       'b':line['stdorToU'],
                       'c':line['DateTime'],
                       'd':d, 
                       'e':line['Acorn'],
                       'f':line['Acorn_grouped']}
                      )
        
        if not rows_to_import is None: 
            if i == rows_to_import: 
                break

conn.commit()

end_file_size=os.stat('low-carbon-london-data.sqlite').st_size
end_time=time.perf_counter()

print('Number of rows imported:', i)
print('Time taken (minutes):', (end_time-start_time)/60)
print('Increase in file size (GB):', (end_file_size-start_file_size)/1000000000)<jupyter_output>Number of rows imported: 167932473
Time taken (minutes): 51.483184316666666
Increase in file size (GB): 16.744681472<jupyter_text>Analysis:The database is now ready for analysis.  Read the data into a pandas dataframeSee this tutorial for the SQL: https://www.sqlitetutorial.net/sqlite-select/<jupyter_code>import pandas as pd
start_time=time.perf_counter()
sql="SELECT * FROM lcld LIMIT 10"
df=pd.read_sql(sql,conn,parse_dates=['DateTime'])
end_time=time.perf_counter()
print('Time taken (minutes):', (end_time-start_time)/60)
df<jupyter_output>Time taken (minutes): 0.00011864166666176364<jupyter_text>Get the data from house 'MAC000002'See this tutorial: https://www.sqlitetutorial.net/sqlite-where/<jupyter_code>start_time=time.perf_counter()
sql="SELECT * FROM lcld WHERE LCLid='MAC000002'"
df=pd.read_sql(sql,conn,parse_dates=['DateTime'])
end_time=time.perf_counter()
print('Time taken (minutes):', (end_time-start_time)/60)
df
%matplotlib inline
df['KWH_hh'].plot()<jupyter_output><empty_output><jupyter_text>Get the number of rows for each houseSee this tutorial: https://www.sqlitetutorial.net/sqlite-group-by/This takes a while to run as it has to query every row in the database. Use `LIMIT` when first developing this query.<jupyter_code>start_time=time.perf_counter()
sql="SELECT LCLid, COUNT(LCLid) AS NUMBER_OF_ROWS FROM lcld GROUP BY LCLid"
df=pd.read_sql(sql,conn,parse_dates=['DateTime'])
end_time=time.perf_counter()
print('Time taken (minutes):', (end_time-start_time)/60)
df<jupyter_output>Time taken (minutes): 4.40291904166667<jupyter_text>Get average power consumption for each houseAgain, this takes a while to run.<jupyter_code>start_time=time.perf_counter()
sql="SELECT LCLid, avg(KWH_hh) AS AVERAGE_POWER FROM lcld GROUP BY LCLid"
df=pd.read_sql(sql,conn)
end_time=time.perf_counter()
print('Time taken (minutes):', (end_time-start_time)/60)
df<jupyter_output>Time taken (minutes): 6.128021239999998
{'fraction_non_alphanumeric': 0.11650128629180448, 'fraction_numerical': 0.02682837192208747, 'mean_word_length': 4.284466019417476, 'pattern_counts': {'":': 0, '<': 37, '<?xml version=': 0, '>': 37, 'https://': 5, 'lorem ipsum': 0, 'www.': 3, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 2, 'word_list_counts': {'cursed_substrings.json': 1, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '10', 'dir': 'jupyter-structured-clean-dedup', 'id': '310407', 'license': 'MIT', 'n_tokens_mistral': 1963, 'n_tokens_neox': 1792, 'n_words': 450, 'path': 'Analysis/lcld_analysis.ipynb', 'repo_name': 'building-energy/low-carbon-london-data', 'size': '5442'}