<jupyter_start><jupyter_text>"Parking Zone Prediction"> "Awesome summary"- toc: true- branch: master- badges: true- comments: true- categories: [fastpages, jupyter]- hide: false- search_exclude: false- metadata_key1: metadata_value1- metadata_key2: metadata_value2  Import modules<jupyter_code>import sqlite3
import pandas as pd
from datetime import datetime, timedelta
import plotly.graph_objects as go<jupyter_output><empty_output><jupyter_text>Define functions<jupyter_code>def parse_date(date_string):
    return datetime.strptime(date_string, '%Y-%m-%d').date()<jupyter_output><empty_output><jupyter_text>Load dataset<jupyter_code>sqliteConnection = sqlite3.connect('parking_zone_db1.db')
sql_query = """SELECT * FROM zone_db5"""
df = pd.read_sql_query(sql_query, sqliteConnection)
df.head()
df.tail()<jupyter_output><empty_output><jupyter_text>Feature engineering First, we will extract relevant features, such as year, month, day of the week, hour and minute from the `Date_Time` column. These may be useful features for a machine learning model.<jupyter_code># Convert `Date_Time` into a datetime object
df['Date'] = pd.to_datetime(df['Date_Time'], format='%Y-%m-%d %H:%M:%S')

# Extract various features
df['Year'] = df['Date'].dt.year
df['Month'] = df['Date'].dt.month
df['Day'] = df['Date'].dt.day
df['Day of Week'] = df['Date'].dt.weekday
df['Hour'] = df['Date'].dt.hour
df['Minute'] = df['Date'].dt.minute

# Replace numbers by names of the months
df['Month'].replace({1: 'Jan', 2: 'Feb', 3: 'Mar', 4: 'Apr', 5: 'May', 6: 'Jun',
                      7: 'Jul', 8: 'Aug', 9: 'Sep', 10: 'Oct', 11: 'Nov', 12: 'Dec'}, inplace=True)

# Replace numbers by days of the week
df['Day of Week'].replace({0: 'Mon', 1: 'Tue', 2: 'Wed', 
                            3: 'Thu', 4: 'Fri', 5: 'Sat', 6: 'Sun'}, inplace=True)

# Rearrange columns
df = df[['Date', 'Year', 'Month', 'Day', 'Day of Week', 'Hour', 'Minute', 'Zone']]

# Sanity check
df.head(10)<jupyter_output><empty_output><jupyter_text>Availability of parking lots could also be influenced by whether a particular date is the eve of a public holiday, or a public holiday itself. Hence, we will create another feature to indicate `1` if it is an eve or a public holiday, and a `0` otherwise.<jupyter_code>ph = ['2021-05-01', '2021-05-13', '2021-05-26']
def create_parsed_eve_and_ph(list_of_ph):
    
    ph_parsed = []
    eve_parsed = []
    
    for i in list_of_ph:
        parsed_date = parse_date(i)
        ph_parsed.append(parsed_date)
        eve_parsed.append(parsed_date - timedelta(1))
        
    return ph_parsed, eve_parsed
ph_parsed, eve_parsed = create_parsed_eve_and_ph(ph)
print(ph_parsed)
print(eve_parsed)
def create_ph_features(data, list_of_ph):
  
    ph_parsed, eve_parsed = create_parsed_eve_and_ph(list_of_ph)
    
    eve = []
    ph = []
    
    for row in data.itertuples():
        date = row.Date

        if date in eve_parsed:
            eve.append(1)
        else:
            eve.append(0)

        if date in ph_parsed:
            ph.append(1)
        else:
            ph.append(0)

    data['PH'] = ph
    data['Eve'] = eve
    
    return data
df = create_ph_features(df, ph)
df.info()<jupyter_output><class 'pandas.core.frame.DataFrame'>
RangeIndex: 70 entries, 0 to 69
Data columns (total 10 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   Date         70 non-null     datetime64[ns]
 1   Year         70 non-null     int64         
 2   Month        70 non-null     object        
 3   Day          70 non-null     int64         
 4   Day of Week  70 non-null     object        
 5   Hour         70 non-null     int64         
 6   Minute       70 non-null     int64         
 7   Zone         70 non-null     object        
 8   PH           70 non-null     int64         
 9   Eve          70 non-null     int64         
dtypes: datetime64[ns](1), int64(6), object(3)
memory usage: 5.6+ KB<jupyter_text>Convert `Month` to ordered categorical data type<jupyter_code>month_order = ['Jan', 'Feb', 'Mar', 'Apr', 'May', 'Jun',
              'Jul', 'Aug', 'Sep', 'Oct', 'Nov', 'Dec']
df['Month'] = df['Month'].astype('category') 
df['Month'].cat.set_categories(new_categories=month_order, ordered=True, inplace=True)
df<jupyter_output><empty_output><jupyter_text>Convert `Day of Week` to ordered categorical data type<jupyter_code>day_order = ['Sun', 'Mon', 'Tue', 'Wed', 'Thu', 'Fri', 'Sat'] 
df['Day of Week'] = df['Day of Week'].astype('category') 
df['Day of Week'].cat.set_categories(new_categories=day_order, ordered=True, inplace=True)
df<jupyter_output><empty_output><jupyter_text>Convert `Hour` to ordered categorical data type<jupyter_code>hour_order = list(range(24))
df['Hour'] = df['Hour'].astype('category') 
df['Hour'].cat.set_categories(new_categories=hour_order, ordered=True, inplace=True)
df<jupyter_output><empty_output><jupyter_text>Exploratory data analysis  By month<jupyter_code>def plot_data(data, variable):
    
    grouped_data = data.groupby(by=variable).size().reset_index(name="Count")

    fig = go.Figure()
    
    fig.add_trace(
        go.Bar(
        x = grouped_data[variable],
        y = grouped_data['Count']
        )
    )

    fig.update_layout(
        title="",
        xaxis_title=variable,
        yaxis_title="Number of Parking Sessions")

    fig.show()
plot_data(data=df, variable="Month")<jupyter_output><empty_output><jupyter_text>By day of the week<jupyter_code>plot_data(data=df, variable="Day of Week")<jupyter_output><empty_output><jupyter_text>By hour of the day<jupyter_code>plot_data(data=df, variable="Hour")<jupyter_output><empty_output><jupyter_text>By parking zone<jupyter_code>plot_data(data=df, variable="Zone")<jupyter_output><empty_output><jupyter_text>Heatmap<jupyter_code>def plot_heatmap(data, variable1, variable2, colorscale="Purples"):
    
    heatmap_data = data.groupby(by=[variable1, variable2])["Date"].count().to_frame('Count').reset_index()
    heatmap_data = heatmap_data.pivot(index=variable1, columns=variable2, values="Count")

    fig = go.Figure(data=go.Heatmap(
            z=heatmap_data,
            x=heatmap_data.columns,
            y=heatmap_data.index,
            colorscale=colorscale))

    fig.update_layout(
        title="",
        xaxis_title=variable2,
        yaxis_title=variable1, 
        xaxis_nticks=36)

    fig.show()
plot_heatmap(data=df, variable1="Day of Week", variable2="Hour", colorscale="Greens")
plot_heatmap(data=df, variable1="Day of Week", variable2="Month", colorscale="Blues")


date_time_str = '18/09/19 01:55:19'

date_time_obj = datetime.strptime(date_time_str, '%d/%m/%y %H:%M:%S')


print("The type of the date is now",  type(date_time_obj))
print("The date is", date_time_obj)
try:
    sqliteConnection = sqlite3.connect('parking_zone_db1.db')
    cursor = sqliteConnection.cursor()
    print("Connected to SQLite")

    # Deleting single record now
#     sql_delete_query = """DELETE from SqliteDb_developers where id = 6"""
#     sql_query = """SELECT * FROM zone_db5"""
    sql_query = """DELETE FROM zone_db5 WHERE Date_Time = '2021-04-20 16:09'"""
    cursor.execute(sql_query)
    sqliteConnection.commit()
#     print("Record deleted successfully")
#     df = pd.read_sql_query(sql_query, sqliteConnection)
    print("All records...")
    cursor.close()

except sqlite3.Error as error:
    print("Failed to delete record from sqlite table", error)

finally:
    if sqliteConnection:
        sqliteConnection.close()
        print("the sqlite connection is closed")
        
df
import time
import datetime

  
string = "2021-05-01"
  
element = datetime.datetime.strptime(string,"%Y-%m-%d")
  
timestamp = datetime.datetime.timestamp(element)
print(timestamp)


# date_string = "2021-05-01"
# format_date = datetime.strptime(date_string, '%Y-%m-%d')
# print(type(format_date))<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.13942611553533055, 'fraction_numerical': 0.023385159903931235, 'mean_word_length': 3.6788882318154936, 'pattern_counts': {'":': 0, '<': 53, '<?xml version=': 0, '>': 54, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '13', 'dir': 'jupyter-structured-clean-dedup', 'id': '299898', 'license': 'Apache-2.0', 'n_tokens_mistral': 2856, 'n_tokens_neox': 2691, 'n_words': 611, 'path': '_drafts/2021-06-06-Playground.ipynb', 'repo_name': 'zeyalt/datafied', 'size': '7911'}