<jupyter_start><jupyter_text>This is a sample notebook, showing how you can create charts from Data Lake queries.It creates a chart of the min and max prices of AAPL trades for each hour on a given day.<jupyter_code>import datetime

import pandas as pd
import matplotlib.pyplot as plt
import matplotlib.dates as mdates
import maystreet_data as md

def fetch_min_max():
    """
    Query the Data Lake for min/max prices grouped by hour of the day.

    Returns a Pandas dataframe with timestamp (as a Python datetime), min_price and max_price.
    """

    query = """
    SELECT 
        DATE_TRUNC('hour', TO_TIMESTAMP(ExchangeTimestamp / 1000000000)) AS hour_ts,
        MIN(price) as min_price,
        MAX(price) as max_price
    FROM 
        "prod_lake"."p_mst_data_lake".mt_trade
    WHERE 
        y = '2022'
        AND m = '01'
        AND d = '19'
        AND product = 'AAPL'
    GROUP BY 1
    ORDER BY 1
    """

    data = pd.DataFrame(md.query(md.DataSource.DATA_LAKE, query))
    data['timestamp'] = [datetime.datetime.fromtimestamp(x / 1000) for x in data['hour_ts']]

    return data

min_max_data = fetch_min_max()
# Here we plot the data fetched in the previous cell.
# This is standard matplotlib.

plt.rcParams['figure.figsize'] = [10, 5]

fig, ax = plt.subplots()

# Explicitly set background color: when using a Dark theme dark text is not very readable
fig.patch.set_facecolor((1, 1, 1))

ax.grid(True)

ax.set_title('AAPL min/max price per hour, 2022/01/19')
ax.set_ylabel('Price')
ax.set_xlabel('Hour')

ax.set_ylim(min_max_data['min_price'].min() - 1, min_max_data['max_price'].max() + 1)

ax.xaxis.set_major_formatter(mdates.DateFormatter('%H'))

ax.bar(
    x=min_max_data['timestamp'], 
    height=min_max_data['max_price'] - min_max_data['min_price'], 
    bottom=min_max_data['min_price'], 
    width=0.01, 
    zorder=2,
)

plt.show()<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.11210526315789474, 'fraction_numerical': 0.023157894736842106, 'mean_word_length': 3.5917874396135265, 'pattern_counts': {'":': 0, '<': 5, '<?xml version=': 0, '>': 5, 'https://': 0, 'lorem ipsum': 0, 'www.': 0, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '1', 'dir': 'jupyter-structured-clean-dedup', 'id': '210034', 'license': 'MIT', 'n_tokens_mistral': 712, 'n_tokens_neox': 651, 'n_words': 186, 'path': 'hourly-price-band.ipynb', 'repo_name': 'MayStreet/workbench-data-lake-springboard', 'size': '1900'}