<jupyter_start><jupyter_text>Effect Of DST END<jupyter_code>try:
    engine = sqlalchemy.create_engine("oracle+cx_oracle://tmulla:<EMAIL>:1521/orcl", arraysize=500000)
   
    orders_sql = """
        select c_time, avg(b_collision) b_collision, avg(collision_no) collision_no, avg(a_collision) a_collision, avg(u_collision) u_collision
        from
        ((select c_time, round(avg(collision_no)) b_collision
        from
        (select substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where (collision_date BETWEEN '26-OCT-2011' AND '06-NOV-2011') OR (collision_date BETWEEN '24-OCT-2012' AND '04-NOV-2012')OR (collision_date BETWEEN '23-OCT-2013' AND '03-NOV-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time)
        NATURAL JOIN
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where collision_date IN ('07-NOV-2011', '05-NOV-2012', '04-NOV-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        NATURAL JOIN
        (select c_time, round(avg(collision_no)) a_collision
        from
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where (collision_date BETWEEN '07-NOV-2011' AND '15-NOV-2011') OR (collision_date BETWEEN '05-NOV-2012' AND '13-NOV-2012') OR (collision_date BETWEEN '04-NOV-2013' AND '13-NOV-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time)
         NATURAL JOIN
        (select c_time, round(avg(collision_no)) u_collision
        from
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where  collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time))
        group by c_time
        order by c_time
   """;
    df_orders = pd.read_sql(orders_sql, engine)

    print(df_orders)

    engine.dispose()

except SQLAlchemyError as e:
    print(e)
fig = go.Figure()
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.b_collision,
                    mode='lines',
                    name='Before DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.collision_no,
                    mode='lines',
                    name='At DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.a_collision,
                    mode='lines', name='After DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.u_collision,
                    mode='lines', name='Usual'))

fig.update_layout(
    title="Time of DST",
    xaxis_title="Hours",
    yaxis_title="Number of Collisions",
    legend_title="Effects of DST(NOV)"
)

# fig.show()
img_bytes = fig.to_image(format="png", engine="orca")
Image(img_bytes)<jupyter_output><empty_output><jupyter_text>Effect of DST Start<jupyter_code>try:
    engine = sqlalchemy.create_engine("oracle+cx_oracle://tmulla:<EMAIL>WMJvX0<EMAIL>:1521/orcl", arraysize=500000)
   
    orders_sql = """
        select c_time, avg(b_collision) AS b_collision, avg(collision_no) AS collision_no, avg(a_collision) AS a_collision, avg(u_collision) AS u_collision
        from
        ((select c_time, round(avg(collision_no)) b_collision
        from
        (select substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where (collision_date BETWEEN '04-MAR-2011' AND '13-MAR-2011') OR (collision_date BETWEEN '01-MAR-2012' AND '11-MAR-2012') OR (collision_date BETWEEN '30-APR-2013' AND '11-MAR-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time)
        NATURAL JOIN
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where collision_date IN ('14-MAR-2011', '12-MAR-2012', '11-MAR-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        NATURAL JOIN
        (select c_time, round(avg(collision_no)) a_collision
        from
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where (collision_date BETWEEN '14-MAR-2011' AND '23-MAR-2011') OR (collision_date BETWEEN '12-MAR-2012' AND '21-MAR-2012') OR (collision_date BETWEEN '11-MAR-2013' AND '20-MAR-2013') AND collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time)
        NATURAL JOIN
        (select c_time, round(avg(collision_no)) u_collision
        from
        (select collision_date, substr(collision_time, 1, 2) as c_time, count(*) collision_no
        from gangardiwalam.time
        where  collision_time IS NOT NULL
        group by collision_date, substr(collision_time, 1, 2)
        order by collision_date)
        group by c_time
        order by c_time))
        group by c_time
        order by c_time
   """;
   
    df_orders = pd.read_sql(orders_sql, engine)

    print(df_orders)
    engine.dispose()

except SQLAlchemyError as e:
    print(e)
    
import plotly.express as px
import plotly.graph_objects as go
fig = go.Figure()
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.b_collision,
                    mode='lines',
                    name='Before DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.collision_no,
                    mode='lines',
                    name='At DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.a_collision,
                    mode='lines', name='After DST'))
fig.add_trace(go.Scatter(x=df_orders.c_time, y=df_orders.u_collision,
                    mode='lines', name='Usual'))

fig.update_layout(
    title="Time of DST",
    xaxis_title="Hours",
    yaxis_title="Number of Collisions",
    legend_title="Effects of DST(MAR)"
)

# fig.show()
img_bytes = fig.to_image(format="png", engine="orca")
Image(img_bytes)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1048951048951049, 'fraction_numerical': 0.035421100638491944, 'mean_word_length': 3.0287813839559092, 'pattern_counts': {'":': 0, '<': 12, '<?xml version=': 0, '>': 12, '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': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '220240', 'license': 'MIT', 'n_tokens_mistral': 2465, 'n_tokens_neox': 2226, 'n_words': 514, 'path': 'Day Light Savings Effect on Collisions .ipynb', 'repo_name': 'taher-mulla/California-Traffic-Collisions-Data-Analysis', 'size': '6639'}