<?xml version="1.0" encoding="UTF-8"?><rss xmlns:dc="http://purl.org/dc/elements/1.1/" xmlns:content="http://purl.org/rss/1.0/modules/content/" xmlns:atom="http://www.w3.org/2005/Atom" version="2.0" xmlns:cc="http://cyber.law.harvard.edu/rss/creativeCommonsRssModule.html">
    <channel>
        <title><![CDATA[Stories by Jimin Kang on Medium]]></title>
        <description><![CDATA[Stories by Jimin Kang on Medium]]></description>
        <link>https://medium.com/@jimin.kang821?source=rss-6c200219f0de------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*LO3EXMP8BhmACG8B1Zbs_Q.jpeg</url>
            <title>Stories by Jimin Kang on Medium</title>
            <link>https://medium.com/@jimin.kang821?source=rss-6c200219f0de------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Fri, 20 Nov 2026 09:50:15 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@jimin.kang821/feed" rel="self" type="application/rss+xml"/>
        <webMaster><![CDATA[yourfriends@medium.com]]></webMaster>
        <atom:link href="http://medium.superfeedr.com/" rel="hub"/>
        <item>
            <title><![CDATA[From Shoe Specs to Recommendations: Building a Running Shoe Recommender]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://levelup.gitconnected.com/from-shoe-specs-to-recommendations-building-a-running-shoe-recommender-ccaf73f3bcf9?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/1122/1*iFSY-I7ri2hqmSMN9ZZzpQ.gif" width="1122"></a></p><p class="medium-feed-snippet">Let&#x2019;s build an app to help us choose the right running shoes.</p><p class="medium-feed-link"><a href="https://levelup.gitconnected.com/from-shoe-specs-to-recommendations-building-a-running-shoe-recommender-ccaf73f3bcf9?source=rss-6c200219f0de------2">Continue reading on Level Up Coding »</a></p></div>]]></description>
            <link>https://levelup.gitconnected.com/from-shoe-specs-to-recommendations-building-a-running-shoe-recommender-ccaf73f3bcf9?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/ccaf73f3bcf9</guid>
            <category><![CDATA[recommendation-system]]></category>
            <category><![CDATA[running]]></category>
            <category><![CDATA[react]]></category>
            <category><![CDATA[fullstack-development]]></category>
            <category><![CDATA[fastapi]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Mon, 19 Oct 2026 15:49:10 GMT</pubDate>
            <atom:updated>2026-10-19T15:49:10.280Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Python Protocols: Writing Flexible and Decoupled Python Apps]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://levelup.gitconnected.com/python-protocols-writing-flexible-and-decoupled-python-apps-605d20ab3c07?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/710/1*3Cam8NP7UsLDLWS1yAr7SA.gif" width="710"></a></p><p class="medium-feed-snippet">A look into the what and why behind python protocols by building a simple to-do app.</p><p class="medium-feed-link"><a href="https://levelup.gitconnected.com/python-protocols-writing-flexible-and-decoupled-python-apps-605d20ab3c07?source=rss-6c200219f0de------2">Continue reading on Level Up Coding »</a></p></div>]]></description>
            <link>https://levelup.gitconnected.com/python-protocols-writing-flexible-and-decoupled-python-apps-605d20ab3c07?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/605d20ab3c07</guid>
            <category><![CDATA[python-protocols]]></category>
            <category><![CDATA[software-development]]></category>
            <category><![CDATA[interfaces]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[software-design-patterns]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Mon, 28 Sep 2026 19:12:52 GMT</pubDate>
            <atom:updated>2026-09-28T19:12:52.399Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Tries in Practice: Building an Autocomplete Search Bar]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://levelup.gitconnected.com/lets-build-an-autocomplete-search-bar-d59ca5cb29ad?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/825/1*bI-kH5wyU3D5n0RU1-CBCw.gif" width="825"></a></p><p class="medium-feed-snippet">Walk through implementation of an autocomplete search bar powered by a a fuzzy prefix-matching Trie.</p><p class="medium-feed-link"><a href="https://levelup.gitconnected.com/lets-build-an-autocomplete-search-bar-d59ca5cb29ad?source=rss-6c200219f0de------2">Continue reading on Level Up Coding »</a></p></div>]]></description>
            <link>https://levelup.gitconnected.com/lets-build-an-autocomplete-search-bar-d59ca5cb29ad?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/d59ca5cb29ad</guid>
            <category><![CDATA[prefix-matching]]></category>
            <category><![CDATA[trie]]></category>
            <category><![CDATA[software-engineering]]></category>
            <category><![CDATA[autocomplete-search]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Fri, 28 Aug 2026 04:09:22 GMT</pubDate>
            <atom:updated>2026-09-28T02:27:17.287Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[From Strava Data to Race Insights: Building a Tool to Understand Training and Race Performance]]></title>
            <link>https://medium.com/@jimin.kang821/from-strava-data-to-race-insights-building-a-tool-to-understand-training-and-race-performance-49d377de6f54?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/49d377de6f54</guid>
            <category><![CDATA[strava]]></category>
            <category><![CDATA[data-visualization]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[running]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Sat, 04 Jul 2026 21:44:36 GMT</pubDate>
            <atom:updated>2026-07-04T21:44:36.692Z</atom:updated>
            <content:encoded><![CDATA[<p>Build an app that pulls Strava data to visualize how your training impacts your race performance.</p><h4>Table of Contents</h4><ul><li><strong>Overview</strong></li><li><strong>Tools of Choice</strong></li><li><strong>Implementation</strong></li><li><strong>Wrap Up</strong></li><li><strong>Resources</strong></li></ul><h4>Overview</h4><p>Are you a runner who’s interested in analyzing how your training affects your race performance?</p><p>Do you log your activities on Strava, but find that it provides little insight on your training beyond the mileage plot displayed on your Profile page?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/759/1*G9ifdzk0rgvdvd9lOrB5-Q.png" /><figcaption>Not so insightful… :(</figcaption></figure><p>In this article, we’ll build an application that visualizes the relationship between your training and race performance. This app will do so by creating two reports:</p><ol><li>PDF with two plots:</li></ol><ul><li><strong>Weekly Mileage vs. Race Performance</strong></li><li><strong>Weekly Cross Training Volume vs. Race Performance</strong></li></ul><p>2. Excel workbook containing these plots along with their underlying data.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DvpkKfSzMLR5E1A0CAxN8Q.png" /><figcaption>Weekly Mileage vs. 5K Race Times. Talk about non-linear development!</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*QyDwoqCyOMYV2es_6K-K0g.png" /><figcaption>That’s a lot of swimming…</figcaption></figure><p>For most runners, their training can be analyzed comprehensively by looking at their running and cross training activities. These plots were created with the following reasons in mind:</p><ul><li><strong>Does more running equate to faster racing?</strong> For most people, probably. However, everybody responds differently.</li><li><strong>What types of cross training help running performance the most?</strong> This differs greatly per individual — some may respond favorably to particular types of cross training over others. For instance, I find that swimming facilitates good recovery from running for me, whereas I don’t much cardiovascular benefits from cycling and it tends to leave me feeling fatigued.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/447/0*ZNhNXSE6yx_EYgEW" /></figure><p>Let’s dive into the app!</p><h4>Tools of Choice</h4><p>This app uses the following tools:</p><ul><li><strong>Language:</strong> Python — contains libraries that make data manipulation &amp; report generation simple. Additionally, the library we’ll use to fetch Strava data, stravalib, is written in Python.</li><li><strong>Data fetching:</strong> <a href="https://stravalib.readthedocs.io/en/latest/index.html#">stravalib</a>— python package that allows us to access the Strava API. We’ll use it to get data for all activities, such as best segments over particular distances, which we’ll need to compute race PRs (personal records).</li><li><strong>Data manipulation/enrichment:</strong> <a href="https://pandas.pydata.org/">pandas</a> to transform raw data from the Strava API into dataframes used to create the plots (e.g. aggregate weekly mileage &amp; cross training).</li><li><strong>Report generation:</strong> <a href="https://matplotlib.org/">matplotlib</a>to create the plots and write them to PDF, and <a href="https://openpyxl.readthedocs.io/en/stable/">openpyxl</a>to write the plots and their dataframes to excel.</li></ul><h4>Implementation</h4><p>Now, let’s get into the details about how this app was created. We’ll start from writing functions to fetch raw activity data from Strava, then transition to transforming/aggregating that data, and then finally creating the plots for our reports.</p><p>Feel free to skip/skim this section as needed. This will be a high-level overview of the code so that somebody familiar with python can understand it, and will NOT be a line-by-line breakdown of each function. Of course, we’ll highlight key components of each function as appropriate.</p><p><strong>Data Fetching</strong></p><p>First, let’s fetch data from Strava!</p><p>The first thing we need to do is get all activities:</p><ul><li>We’ll write a function that calls the stravalib <a href="https://stravalib.readthedocs.io/en/latest/reference/api/stravalib.client.Client.get_activities.html">get_activities()</a>method, and then transform that response to a dictionary that stores all activities classified by sport type.</li><li>We’ll take a date range over which to fetch activities from and pass that to get_activities().</li><li>We’ll write the data fetched from Strava to local JSON files. This will allow us to fetch activity data from these local files on subsequent runs if we wish, instead of calling the Strava API. This will be convenient if we want to generate the plots on a different race distance using the same data. We’ll define this option to fetch data locally as a command line argument, which we’ll go over later.</li></ul><pre>from strava_config import ACCESS_TOKEN, REFRESH_TOKEN, EXPIRES_AT<br><br>def get_strava_client():<br>    &quot;&quot;&quot;<br>    Create a Strava client from the access &amp; refresh tokens.<br>    &quot;&quot;&quot;<br>    client = Client(access_token=ACCESS_TOKEN, refresh_token=REFRESH_TOKEN, token_expires=int(EXPIRES_AT))   <br>    return client<br>def write_dict_to_json(data: dict, output_dir: Path, file_name: str): <br>    &quot;&quot;&quot;<br>    Write the dictionary to a JSON file in the specified output directory.<br>    Args:<br>        data (dict): dictionary to write to JSON<br>        output_dir (Path): output directory path <br>        file_name (str): file name to write to (excluding the extension)<br>    &quot;&quot;&quot;<br>    filename = f&quot;{file_name}.json&quot;<br>    output_dir.mkdir(parents=True, exist_ok=True)<br>    output_file_path = output_dir / filename<br>    <br>    with open(output_file_path, &quot;w&quot;) as f:<br>        json.dump(data, f)<br>    <br>    print(f&quot;Successfully wrote to &#39;{output_file_path}&#39;.&quot;)<br><br>def get_all_activities(start_date: date | None, end_date: date | None) -&gt; dict:<br>    &quot;&quot;&quot;<br>    Get all historical activities for an athlete within the date range.<br>    Store activities by type:<br>    {<br>        &quot;run&quot;: [<br>            {<br>                &quot;name&quot;: &quot;Bucktown 5k&quot;,<br>                &quot;distance&quot;: 5040.5,<br>                &quot;id&quot;: 1<br>                ...<br>            },<br>            {<br>                &quot;name&quot;: &quot;Boston 5k&quot;,<br>                &quot;distance&quot;: 5040.5,<br>                &quot;id&quot;: 123<br>                ...<br>            },<br>        ],<br>        &quot;swim&quot;: [<br>            &quot;name&quot;: &quot;Afternoon Swim&quot;,<br>            &quot;distance&quot;: 1600.2,<br>            &quot;moving_time&quot;: 1917,<br>            &quot;id&quot;: 18521721976,<br>            ...<br>        ]<br>        ...<br>    }<br>    Returns:<br>        dict: dictionary of activities<br>    &quot;&quot;&quot;<br>    print(&quot;Loading activities from the Strava API...&quot;)<br>    <br>    # Fetch all Strava activities<br>    client = get_strava_client()<br>    activities = client.get_activities(<br>        before=datetime(end_date.year, end_date.month, end_date.day) if end_date else None, <br>        after=datetime(start_date.year, start_date.month, start_date.day) if start_date else None<br>    )<br>    <br>    # Classify activities into the following sport types<br>    activities_map = {<br>        &quot;run&quot;: [],<br>        &quot;swim&quot;: [],<br>        &quot;ride&quot;: [],<br>        &quot;other&quot;: []<br>    }<br>    <br>    for i, activity in enumerate(activities):<br>        # Extract select attributes from Stravalib&#39;s SummaryActivity model<br>        to_include = [<br>            &quot;id&quot;, &quot;type&quot;, &quot;workout_type&quot;, &quot;sport_type&quot;, &quot;name&quot;, &quot;distance&quot;, &quot;elapsed_time&quot;, &quot;moving_time&quot;, &quot;start_date&quot;<br>        ]<br>        activity_dict = json.loads(activity.model_dump_json(include=to_include)) # dump to JSON first to convert non-JSON serializable types (i.e. datetime) to string<br>        <br>        match activity.sport_type:<br>            case &quot;Run&quot;:<br>                activities_map[&quot;run&quot;].append(activity_dict)<br>            case &quot;Swim&quot;:<br>                activities_map[&quot;swim&quot;].append(activity_dict)<br>            case &quot;Ride&quot;:<br>                activities_map[&quot;ride&quot;].append(activity_dict)<br>            case _:<br>                activities_map[&quot;other&quot;].append(activity_dict)<br>    # write data locally as JSON, so that we can pull activities from files in subsequent runs if needed<br>    write_dict_to_json(activities_map, Path(DATA_DIR), &#39;activities&#39;)    <br>    return activities_map</pre><p>Next, we need to compute race PRs.</p><p>This consists of identifying which activities are “races”, and then fetching the best efforts for each “race”.</p><p>For each running activity, Strava tracks the best efforts across a set of distances, including but not limited to: 1 mile, 2 mile, 5K, 10K, Half-Marathon, Marathon.</p><p>We’ll write a function that extracts the top N race times for each of those distances into a dictionary like the following:</p><pre>{<br>    &quot;1 mile&quot;: [<br>        {<br>            &quot;elapsed_time&quot;: 5:03,<br>            &quot;date&quot;: 2026-03-01,<br>        },<br>        {<br>            &quot;elapsed_time&quot;: 5:14,<br>            &quot;date&quot;: 2024-02-14,<br>        },<br>        ...<br>    ],<br>    &quot;5K&quot;: [<br>        {<br>            &quot;elapsed_time&quot;: 18:10,<br>            &quot;date&quot;: 2026-03-15,<br>        },<br>        {<br>            &quot;elapsed_time&quot;: 18:30,<br>            &quot;date&quot;: 2024-11-15,<br>        },<br>        ...<br>    ],<br>    ...,<br>    &quot;Marathon&quot;: [<br>        {<br>            &quot;elapsed_time&quot;: 03:18:30,<br>            &quot;date&quot;: 2025-11-25,<br>        },<br>        {<br>            &quot;elapsed_time&quot;: 03:38:30,<br>            &quot;date&quot;: 2024-06-15,<br>        },<br>        ...<br>    ]<br>}</pre><p>But first, let’s write a function to get the best efforts for a single activity.</p><pre>def get_activity_details(activity_id: str) -&gt; dict:<br>    &quot;&quot;&quot;<br>    Get activity details, like best efforts.<br>    Note: Each call to client.get_activity() is a single API call.  <br>    <br>    Args:<br>        activity_id (str): _description_<br>    Returns:<br>        dict: _description_<br>    &quot;&quot;&quot;<br>    # Get activity details and its best efforts<br>    client = get_strava_client()<br>    detailed_activity = client.get_activity(activity_id=activity_id, include_all_efforts=True)<br>    <br>    # Extract select attributes from Stravalib&#39;s DetailedActivity model<br>    to_include = [<br>        &quot;name&quot;, &quot;distance&quot;, &quot;elapsed_time&quot;, &quot;best_efforts&quot;<br>    ]<br>    detailed_activity_dict = json.loads(detailed_activity.model_dump_json(include=to_include)) <br>    return detailed_activity_dict</pre><p>Let’s write another function that creates the dictionary of race PRs by calling the function above on each activity that’s identified as a “race”.</p><pre>def get_personal_records(activities_map: dict, top_N: int = 0) -&gt; dict:<br>    &quot;&quot;&quot;<br>    Given all activities, find the top N running race PRs for all distances in RACE_TYPES.<br>    Endpoint /activities/{id} returns an activity&#39;s &quot;best_efforts&quot; for each distance in RACE_TYPES.<br>    To limit the number of API requests we&#39;ll make, we&#39;ll only get details for races (i.e workout_type = 1).<br><br>    Args:<br>        activities_map (dict): activities dictionary<br>        top_N (int, optional): number of PRs to track for each distance. Defaults to 0.<br><br>    Returns:<br>        dict: dictionary of PRs<br>    &quot;&quot;&quot;<br>    print(&quot;Extracting race PRs from activity data...&quot;)<br>    <br>    pr_map = {}<br>    <br>    # Filter thru the &quot;run&quot; activities<br>    running_activities = activities_map[&quot;run&quot;]<br><br>    for run_activity in running_activities:<br>        # Only fetch details for races (i.e. &quot;workout_type&quot; == 1)<br>        if run_activity[&quot;workout_type&quot;] == 1:<br>            activity_details = get_activity_details(run_activity[&quot;id&quot;])<br><br>            # Get the activity&#39;s best efforts: each activity will contain best efforts for all distances in RACE_TYPES shorter than the activity<br>            best_efforts = activity_details[&quot;best_efforts&quot;]<br><br>            for best_effort in best_efforts:<br>                distance = best_effort[&quot;name&quot;]<br><br>                # Add activity name &amp; date to the best_effort<br>                best_effort[&quot;activity_name&quot;] = run_activity[&quot;name&quot;]<br>                best_effort[&quot;date&quot;] = run_activity[&quot;start_date&quot;]<br><br>                # Add the best effort to the list of PRs for that distance<br>                # Then, sort the PRs by elapsed time (fastest first)<br>                pr_map.setdefault(distance, []).append(best_effort) # adds the distance to the PR Map if it&#39;s the first race of its distance type<br>                pr_map[distance] = sorted(pr_map[distance], key=lambda x: x[&quot;elapsed_time&quot;])<br><br>                # Filter to the top N PRs for each distance, if specified<br>                if top_N &gt; 0:<br>                    pr_map[distance] = pr_map[distance][:top_N]<br>            <br>    write_dict_to_json(pr_map, Path(DATA_DIR), &#39;personal_records&#39;)<br>    return pr_map</pre><p>Note that similarly to get_all_activities(), this function also writes the personal records to local JSON files.</p><p><strong>Data Manipulation/Enrichment</strong></p><p>Now that we’ve fetched all the data we need, let’s transform it into the dataframes required for our reports.</p><p>We want to generate the following PDF:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uMab_MvIS-sqsdZD3ayIKw.png" /><figcaption>Page 1 of the PDF: Weekly Mileage Analysis</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*SI4XOCssUo3POanuIGHcIw.png" /><figcaption>Page 2 of the PDF: Weekly Cross Training Analysis</figcaption></figure><p>To create this report, we need to:</p><ul><li>Create dataframes that compute weekly mileage &amp; cross training volume.</li><li>Create dataframes containing basic statistics (e.g. average, min/max, rate of change) on weekly mileage &amp; cross training volume.</li></ul><p>Let’s write a function to create a dataframe that contains total mileage by week.</p><pre>def create_weekly_mileage_df(running_activities: dict) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;<br>    Create a dataframe containing total mileage per week.<br><br>    Args:<br>        running_activities (dict): dictionary of running activities<br><br>    Returns:<br>        pd.DataFrame: DataFrame containing weekly mileage<br>    &quot;&quot;&quot;<br>    df = pd.DataFrame(running_activities)<br><br>    # Convert &#39;date&#39; column to datetime &amp; &#39;distance&#39; to miles<br>    df[&#39;start_date&#39;] = pd.to_datetime(df[&#39;start_date&#39;])<br>    df[&#39;distance (miles)&#39;] = df[&#39;distance&#39;] / MILE_TO_METERS<br><br>    # Group mileage by week: pd.Grouper will create week bins for every week between the start &amp; end dates<br>    weekly_totals = df.groupby(pd.Grouper(key=&#39;start_date&#39;, freq=WEEK_FREQ))[&#39;distance (miles)&#39;].sum().reset_index()<br><br>    # Format dates for better readability in the plot<br>    weekly_totals[&#39;date&#39;] = weekly_totals[&#39;start_date&#39;].dt.strftime(&quot;%Y-%m-%d&quot;)<br>    weekly_totals[&#39;year-week&#39;] = weekly_totals[&#39;start_date&#39;].dt.strftime(DATE_FORMAT)<br>    <br>    # Reorganize column order to write to the excel workbook<br>    weekly_totals = weekly_totals.reindex(columns=[&#39;date&#39;, &#39;year-week&#39;, &#39;distance (miles)&#39;])<br><br>    return weekly_totals</pre><p>Similarly, let’s write a function to create a dataframe that groups total cross training volume by week.</p><ul><li>We’ll add the option to group by sport type, as this will allow us to display the distribution of cross training volume by sport for each week. In other words, our bar plot will show how much time the athlete spent on each type of cross training activity (ex: swim, bike, lift, etc.) per week.</li></ul><pre>def create_weekly_xtraining_df(all_activities: dict, group_by_sport_type: bool = False) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;<br>    Create a DataFrame with all cross training (i.e. non-running) activities.<br>    DataFrame will group training volume by week, and sport type if specified.<br><br>    Args:<br>        all_activities (dict): dictionary containing all Strava activities<br>        group_by_sport_type (bool, optional): Flag to group training volume by sport type. Defaults to False.<br><br>    Returns:<br>        pd.DataFrame: DataFrame containing weekly cross-training volume, optionally grouped by sport type<br>    &quot;&quot;&quot;<br>    # Store cross training activities in a DataFrame<br>    xtraining_activities_list = []<br>    for activity_type, activity_list in all_activities.items():<br>        if activity_type != &quot;run&quot;:<br>            xtraining_activities_list.extend(activity_list)<br>    df = pd.DataFrame(xtraining_activities_list)<br><br>    # Convert &#39;date&#39; column to datetime<br>    df[&#39;start_date&#39;] = pd.to_datetime(df[&#39;start_date&#39;])<br><br>    # Group cross training volume by week and optionally by sport type<br>    if group_by_sport_type:<br>        weekly_totals = df.groupby([&#39;sport_type&#39;, pd.Grouper(key=&#39;start_date&#39;, freq=WEEK_FREQ)])[&#39;elapsed_time&#39;].sum().reset_index()<br>    else: <br>        weekly_totals = df.groupby(pd.Grouper(key=&#39;start_date&#39;, freq=WEEK_FREQ))[&#39;elapsed_time&#39;].sum().reset_index()<br><br>    # Format &#39;date&#39; column and create a new column for year-week<br>    weekly_totals[&#39;date&#39;] = weekly_totals[&#39;start_date&#39;].dt.strftime(&quot;%Y-%m-%d&quot;)<br>    weekly_totals[&#39;year-week&#39;] = weekly_totals[&#39;start_date&#39;].dt.strftime(DATE_FORMAT)<br>    <br>    # Specify column order to write to the excel workbook<br>    first_cols = [&#39;date&#39;, &#39;year-week&#39;]<br>    weekly_totals = weekly_totals[first_cols + [col for col in weekly_totals.columns if col not in first_cols]]<br><br>    return weekly_totals</pre><p>Now, let’s write functions to compute basic statistics for weekly mileage and cross training volume.</p><pre>def mileage_vs_race_statistics(running_activities: dict) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;<br>    Compute summary statistics analyzing weekly mileage.<br>    Eventually, this may include statistics to quantify the relationship between weekly mileage vs. race times.<br>    <br>    Args:<br>        running_activities (dict): dictionary of running activities<br><br>    Returns:<br>        pd.DataFrame: DataFrame containing weekly mileage data<br>    &quot;&quot;&quot;<br>    weekly_mileage_df = create_weekly_mileage_df(running_activities)<br><br>    # Computing change in weekly mileage requires multiple weeks of data<br>    if len(weekly_mileage_df.index) &gt; 1:<br>        avg_mileage_delta, _ = np.polyfit(weekly_mileage_df.index, weekly_mileage_df[&#39;distance (miles)&#39;], 1)<br>        avg_mileage_delta_str = f&quot;{avg_mileage_delta:.1f}&quot;<br>    else:<br>        avg_mileage_delta_str = &quot;N/A&quot;<br>    <br>    summary_statistics = {<br>        &quot;Average Weekly Mileage&quot;: f&quot;{weekly_mileage_df[&#39;distance (miles)&#39;].mean():.1f}&quot;,<br>        &quot;Lowest Weekly Mileage&quot;: f&quot;{weekly_mileage_df[&#39;distance (miles)&#39;].min():.1f}&quot;,<br>        &quot;Highest Weekly Mileage&quot;: f&quot;{weekly_mileage_df[&#39;distance (miles)&#39;].max():.1f}&quot;,<br>        &quot;Average Change in Weekly Mileage&quot;: avg_mileage_delta_str,<br>    }<br>    return pd.DataFrame([summary_statistics])<br><br><br>def xtraining_vs_race_statistics(activities: dict) -&gt; pd.DataFrame:<br>    &quot;&quot;&quot;<br>    Compute summary statistics analyzing cross training activity vs. race times.<br>    Eventually, this may include statistics to quantify the relationship between cross-training volume and race performance.<br><br>    Args:<br>        activities (dict): dictionary of activities<br><br>    Returns:<br>        pd.DataFrame: DataFrame containing weekly cross-training statistics<br>    &quot;&quot;&quot;<br>    xtraining_df = create_weekly_xtraining_df(activities)<br><br>    # Summary statistics should show elapsed time in hh:mm:ss format<br>    xtraining_df[&#39;elapsed_time&#39;] = pd.to_timedelta(xtraining_df[&#39;elapsed_time&#39;], unit=&#39;s&#39;)<br>    <br>    def convert_timedelta_to_hhmmss(td: timedelta) -&gt; str:<br>        &quot;&quot;&quot;<br>        Convert timedelta to a string that follows the format {hh:mm:ss}.<br>        I.e. the days should be omitted (ex: 1 day 00:00:00 -&gt; 24:00:00)<br>        &quot;&quot;&quot;<br>        # Calculate absolute pieces from total seconds<br>        total_seconds = int(td.total_seconds())<br>        hours = total_seconds // 3600<br>        minutes = (total_seconds % 3600) // 60<br>        seconds = total_seconds % 60<br><br>        # Format with 2-digit zero padding<br>        formatted_time = f&quot;{hours:02d}:{minutes:02d}:{seconds:02d}&quot;<br>        return formatted_time<br><br>    summary_statistics = {<br>        &quot;Average Weekly X-Training Volume&quot;: convert_timedelta_to_hhmmss(xtraining_df[&#39;elapsed_time&#39;].mean()),<br>        &quot;Lowest Weekly X-Training Volume&quot;: convert_timedelta_to_hhmmss(xtraining_df[&#39;elapsed_time&#39;].min()),<br>        &quot;Highest Weekly X-Training Volume&quot;: convert_timedelta_to_hhmmss(xtraining_df[&#39;elapsed_time&#39;].max())<br>    }<br>    return pd.DataFrame([summary_statistics])</pre><p><strong>Plotting</strong></p><p>We’ve transformed the data into dataframes, now let’s bring them to life with some plots.</p><p>Recall from the pics above that our PDF report will be two pages, where each page contains:</p><ul><li>Plot: weekly mileage/cross training vs. race performance.</li><li>Table: summary statistics for weekly mileage/cross training.</li></ul><p>Let’s create the first page of the PDF report, which will contain weekly mileage vs. race performance data.</p><p>Our function for creating this PDF page, visualize_weekly_mileage_vs_race(), will take the following inputs:</p><ul><li>Dictionary of all running activities.</li><li>Dictionary of personal records.</li><li>The race distance to analyze.</li></ul><p>It will return the following:</p><ul><li>Matplotlib Figure with two subplots: the weekly mileage plot &amp; the summary statistics table.</li><li>The dataframe the plot was generated from.</li></ul><pre>def visualize_weekly_mileage_vs_race(running_activities: dict, personal_records: dict, race: str):<br>    &quot;&quot;&quot;<br>    Plot weekly mileage against race times.<br><br>    Args:<br>        running_activities (dict): all running activities<br>        personal_records (dict): all races/PRs<br>        race (str): race distance to analyze<br><br>    Returns:<br>        _type_: _description_<br>    &quot;&quot;&quot;<br>    weekly_totals_df = create_weekly_mileage_df(running_activities)<br>    <br>    # 1. CREATE THE MATPLOTLIB FIGURE &amp; SPECIFY THE REPORT LAYOUT<br>    # The Figure will contain two subplots: one for the weekly mileage plot, another for the table of summary statistics<br>    fig = plt.figure(<br>        figsize=FIG_SIZE,<br>        constrained_layout=True<br>    )<br>    gs = GridSpec(<br>        2, # 2 rows: weekly mileage plot &amp; summary statistics<br>        1,<br>        figure=fig,<br>        height_ratios=[2, 1] # Weekly mileage plot will take up 2x as much space as the statistics table<br>    )<br><br><br>    # 2. CREATE THE WEEKLY MILEAGE PLOT<br>    # Place the plot at the top of the report<br>    ax1 = fig.add_subplot(gs[0])<br>    <br>    # Plot bar chart of weekly mileage<br>    ax1.bar(weekly_totals_df[&#39;year-week&#39;], weekly_totals_df[&#39;distance (miles)&#39;], color=&#39;darkturquoise&#39;, edgecolor=&#39;black&#39;) # Matplotlib colors: https://stackoverflow.com/questions/22408237/named-colors-in-matplotlib<br>    ax1.set_xlabel(&#39;Date (Year-Week)&#39;)<br><br>    # Set x-axis tick frequency to every 5 weeks to avoid crowding the plot <br>    indices = range(0, len(weekly_totals_df), WEEKLY_MILEAGE_PLOT_TICK_FREQUENCY) # indices of dates to include<br>    ax1.set_xticks(indices)<br>    ax1.set_xticklabels(weekly_totals_df[&#39;year-week&#39;].iloc[indices], rotation=90) <br>    <br>    # Set the first y-axis for weekly mileage<br>    ax1.set_ylabel(&#39;Miles&#39;, color=&#39;darkturquoise&#39;)<br>    ax1.tick_params(axis=&#39;y&#39;, labelcolor=&#39;darkturquoise&#39;)<br><br>    # Create a secondary y-axis to plot race times against a shared x-axis (time)<br>    ax2 = ax1.twinx()<br><br>    # Create a line plot for race times<br>    pr_df = create_pr_df(personal_records, race)<br>    ax2.plot(pr_df[&#39;year-week&#39;], pr_df[&#39;time (seconds)&#39;], &#39;o--r&#39;, label=&#39;Race Time&#39;, color=RACE_LINE_PLOT_COLOR)<br>    ax2.set_ylabel(&#39;Race Time (hh:mm:ss)&#39;, color=RACE_LINE_PLOT_COLOR)<br>    ax2.tick_params(axis=&#39;y&#39;, labelcolor=RACE_LINE_PLOT_COLOR)<br>    <br>    # Convert race time to mm:ss format<br>    ax2.yaxis.set_major_formatter(ticker.FuncFormatter(seconds_to_hhmmss))<br><br>    # Annotate the race times on the plot<br>    for _, row in pr_df.iterrows():<br>        ax2.annotate(<br>            f&quot;({row[&#39;date&#39;]}, {seconds_to_hhmmss(row[&#39;time (seconds)&#39;], None)})&quot;,<br>            (row[&#39;year-week&#39;], row[&#39;time (seconds)&#39;]),<br>            xytext=(0, 5), # Offset: 0 points horizontally, 5 points vertically up<br>            textcoords=&#39;offset points&#39;, # Treat xytext as pixel offset<br>            fontsize=8,<br>            color=RACE_LINE_PLOT_COLOR,<br>            weight=&#39;bold&#39;,<br>            ha=&quot;center&quot;, # Centers the text horizontally over the point<br>            va=&quot;bottom&quot;, # Positions the bottom of the text box above the point<br>        )<br>        <br>    # Add a legend &amp; title<br>    handles1, labels1 = ax1.get_legend_handles_labels()<br>    handles2, labels2 = ax2.get_legend_handles_labels()<br>    ax1.legend(<br>        handles1 + handles2,<br>        labels1 + labels2,<br>        loc=&quot;lower center&quot;,<br>        bbox_to_anchor=(0.5, 1.1),<br>        ncol=4,<br>        frameon=True<br>    )<br><br>    plt.title(f&#39;Weekly Mileage vs. Race Times ({race})&#39;)<br><br>    <br>    # 3. COMPUTE SUMMARY STATISTICS <br>    # Place the summary statistics at the bottom of the report<br>    ax_table = fig.add_subplot(gs[1])<br>    ax_table.axis(&quot;off&quot;)<br>       <br>    summary_stats_df = mileage_vs_race_statistics(running_activities)<br>    table = ax_table.table(<br>        cellText=summary_stats_df.values,<br>        colLabels=summary_stats_df.columns,<br>        loc=&quot;center&quot;,<br>        cellLoc=&quot;center&quot;<br>    )<br><br>    # Set the font size and bold the header row of the table<br>    table.auto_set_font_size(False)<br>    table.set_fontsize(10)<br>    table.scale(1.2, 2.0)<br>    for (row, col), cell in table.get_celld().items():<br>        if row == 0:<br>            cell.set_text_props(weight=&#39;bold&#39;)<br><br>    return fig, weekly_totals_df</pre><p>There’s several things going on here, so let’s highlight the key steps:</p><ol><li>We’re using Matplotlib’s <a href="https://matplotlib.org/stable/api/_as_gen/matplotlib.gridspec.GridSpec.html">GridSpec</a> to specify the layout of the Matplotlib figure to align with our PDF structure.</li></ol><ul><li>Top subplot: weekly mileage plot.</li><li>Bottom subplot: table with weekly mileage summary statistics.</li><li>Spacing: the weekly mileage plot will take twice as much space as the summary statistics table.</li></ul><p>2. Then, we’re creating the weekly mileage vs. race performance plot.</p><ul><li>First, create the bar chart displaying weekly mileage totals.</li><li>Next, create a line plot to display race PRs on top of the weekly mileage totals.</li><li>Finally, format the plot: annotate the race times, add legend &amp; title.</li></ul><p>3. Lastly, create the table for weekly mileage summary statistics and place it in the bottom subplot.</p><p>Now, let’s create the second page of the PDF report, which will hold weekly cross training vs. race performance data.</p><p>Our function for creating this page, visualize_weekly_xtraining_vs_race(), will take the following inputs:</p><ul><li>Dictionary containing all training activities</li><li>Dictionary containing personal records</li><li>The race distance to analyze.</li></ul><p>Similar to visualize_weekly_mileage_vs_race(), this function will return:</p><ul><li>Matplotlib Figure object containing the weekly cross training volume plot &amp; summary statistics table</li><li>The underlying dataframe.</li></ul><p>This function is similar to visualize_weekly_mileage_vs_race(). However, we’re defining this separately because the timeframe for cross-training activities &amp; races may not overlap, whereas the races will always be captured in the timeframe of the running activities. So, visualize_weekly_xtraining_vs_race() will compute the timeframe that captures both the cross training activities and the races.</p><pre>def visualize_weekly_xtraining_vs_race(activities_dict: dict, personal_records: dict, race: str):<br>    &quot;&quot;&quot;<br>    Visualize weekly cross training volume against race times.<br>    Cross training volume will be displayed as a bar chart, and race times will be plotted as a line chart.<br>    Each bar will display the distribution of that week&#39;s cross training volume broken down by activity (ex: swim, bike, lift, etc.)<br><br>    Args:<br>        activities_dict (dict): all activities<br>        personal_records (dict): all PRs/races<br>        race (str): race distance to analyze<br><br>    Returns:<br>        _type_: _description_<br>    &quot;&quot;&quot;<br>    <br>    # 1. Create a pivot table containing cross training volume grouped by sport type &amp; week<br>    weekly_xtraining_df = create_weekly_xtraining_df(activities_dict, group_by_sport_type=True)<br>    weekly_xtraining_df_pivoted = weekly_xtraining_df.pivot(index=&#39;year-week&#39;, columns=&#39;sport_type&#39;, values=&#39;elapsed_time&#39;)<br>    <br>    # 2. CREATE THE MATPLOTLIB FIGURE &amp; SPECIFY THE REPORT LAYOUT<br>    # The Figure will contain two subplots: one for the weekly x-training plot, another for the table of summary statistics<br>    fig = plt.figure(<br>        figsize=FIG_SIZE,<br>        constrained_layout=True<br>    )<br>    gs = GridSpec(<br>        2, # 2 rows: weekly x-training plot &amp; summary statistics<br>        1,<br>        figure=fig,<br>        height_ratios=[2, 1] # Weekly x-training plot will take up 2x as much space as the statistics table<br>    )<br>    <br>    # 3. PLOT WEEKLY X-TRAINING VOLUME<br>    ax1 = fig.add_subplot(gs[0]) <br>    <br>    # Create the x-axis (time): determine the time range that captures all of the x-training activities and the races.<br>    # Then, plot the x-training activities &amp; races onto their appropriate locations on the time axis.<br>    pr_df = create_pr_df(personal_records, race)<br>    <br>    # Convert week strings to dates<br>    xtraining_dates = pd.to_datetime(<br>        weekly_xtraining_df_pivoted.index + &quot;-0&quot;, # need this day suffix to convert date string (%Y-W%U) to datetime (%Y-W%U-%w)<br>        format=&quot;%Y-W%U-%w&quot;<br>    )<br>    pr_dates = pd.to_datetime(<br>        pr_df[&quot;year-week&quot;] + &quot;-0&quot;,<br>        format=&quot;%Y-W%U-%w&quot;<br>    )<br>    <br>    # Find min &amp; max weeks across all x-training &amp; race activities, then generate every week in between<br>    start_week = min(<br>        xtraining_dates.min(),<br>        pr_dates.min()<br>    )<br>    end_week = max(<br>        xtraining_dates.max(),<br>        pr_dates.max()<br>    )<br>    all_weeks = pd.date_range(<br>        start=start_week,<br>        end=end_week,<br>        freq=WEEK_FREQ<br>    )<br>    <br>    # Convert weeks into their date strings<br>    week_labels = [<br>        d.strftime(DATE_FORMAT)<br>        for d in all_weeks<br>    ]<br>    <br>    # Map weeks to their xposition on the plot<br>    week_to_xpos = {<br>        week: i<br>        for i, week in enumerate(week_labels)<br>    }<br>    <br>    # Reindex the pivot table to include all weeks<br>    weekly_xtraining_df_pivoted = (<br>        weekly_xtraining_df_pivoted<br>        .reindex(week_labels)<br>        .fillna(0)<br>    )<br>    <br>    # Create the bar chart of weekly x-training volume<br>    weekly_xtraining_df_pivoted.plot(kind=&quot;bar&quot;, stacked=True, edgecolor=&quot;black&quot;, ax=ax1)<br>    ax1.set_xlabel(&#39;Date (Year-Week)&#39;)<br><br>    # Set the weekly frequency on the x-axis<br>    frequency = 4 # number of weeks between x ticks<br>    indices = range(0, len(weekly_xtraining_df_pivoted.index), frequency) # indices of dates to include<br>    ax1.set_xticks(indices)<br>    ax1.set_xticklabels(weekly_xtraining_df_pivoted.index[indices], rotation=90) <br><br>    # Format y-axis (elapsed time) in terms of hours:minutes:seconds <br>    ax1.set_ylabel(&#39;Elapsed Time (hh:mm:ss)&#39;, color=&#39;darkturquoise&#39;)<br>    ax1.tick_params(axis=&#39;y&#39;, labelcolor=&#39;darkturquoise&#39;)<br>    ax1.yaxis.set_major_formatter(ticker.FuncFormatter(seconds_to_hhmmss))<br><br>    # 4. PLOT RACE TIMES<br>    # Create a secondary y-axis (on the right of the plot) to plot race times against a shared x-axis (time)<br>    ax2 = ax1.twinx()<br><br>    # Map weeks in PR data to align with the index positions of the cross training dataframe.<br>    # When plotting the xtraining pivot table, pandas creates its own internal x-axis that maps integers to weeks (i.e. 0 -&gt; 2024-W01, 1 -&gt; 2024-02, etc.)<br>    # We need to map each week to its corresponding x-position on the plot to ensure the PRs are plotted at the correct weeks.<br>    pr_df[&#39;xpos&#39;] = pr_df[&#39;year-week&#39;].map(week_to_xpos)<br>    <br>    # Plot race times as dots attached by dashed line<br>    ax2.plot(pr_df[&#39;xpos&#39;], pr_df[&#39;time (seconds)&#39;], &#39;o--r&#39;, label=&#39;Race Time&#39;, color=RACE_LINE_PLOT_COLOR) # plot points as circles connected by dashed line<br>    ax2.set_ylabel(&#39;Race Time (hh:mm:ss)&#39;, color=RACE_LINE_PLOT_COLOR)<br>    ax2.tick_params(axis=&#39;y&#39;, labelcolor=RACE_LINE_PLOT_COLOR)<br>    <br>    # Convert elapsed time to mm:ss format<br>    ax1.yaxis.set_major_formatter(ticker.FuncFormatter(seconds_to_hhmmss))<br>    ax2.yaxis.set_major_formatter(ticker.FuncFormatter(seconds_to_hhmmss))<br><br>    # Annotate the race times<br>    for _, row in pr_df.iterrows():<br>        ax2.annotate(<br>            f&quot;({row[&#39;date&#39;]}, {seconds_to_hhmmss(row[&#39;time (seconds)&#39;], None)})&quot;,<br>            (row[&#39;xpos&#39;], row[&#39;time (seconds)&#39;]),<br>            xytext=(0, 5), # Offset: 0 points horizontally, 5 points vertically up<br>            textcoords=&#39;offset points&#39;, # Treat xytext as pixel offset<br>            fontsize=8,<br>            color=RACE_LINE_PLOT_COLOR,<br>            weight=&#39;bold&#39;,<br>            ha=&quot;center&quot;, # Centers the text horizontally over the point<br>            va=&quot;bottom&quot;, # Positions the bottom of the text box above the point<br>        )<br>        <br>    # Add legend to distinguish sport types &amp; race times<br>    handles1, labels1 = ax1.get_legend_handles_labels()<br>    handles2, labels2 = ax2.get_legend_handles_labels()<br>    ax1.legend(<br>        handles1 + handles2,<br>        labels1 + labels2,<br>        loc=&quot;lower center&quot;,<br>        bbox_to_anchor=(0.5, 1.1),<br>        ncol=4,<br>        frameon=True<br>    )<br><br>    # Add plot title<br>    plt.title(f&#39;Weekly Cross Training Volume vs. Race Times ({race})&#39;)<br><br>    # 5. COMPUTE SUMMARY STATISTICS<br>    ax_table = fig.add_subplot(gs[1])<br>    ax_table.axis(&quot;off&quot;)<br>    <br>    summary_stats_df = xtraining_vs_race_statistics(activities_dict)<br>    table = ax_table.table(<br>        cellText=summary_stats_df.values,<br>        colLabels=summary_stats_df.columns,<br>        loc=&quot;center&quot;,<br>        cellLoc=&quot;center&quot;<br>    )<br><br>    # Set the font size and bold the header row of the table<br>    table.auto_set_font_size(False)<br>    table.set_fontsize(10)<br>    table.scale(1.2, 2.0)<br>    for (row, col), cell in table.get_celld().items():<br>        if row == 0:<br>            cell.set_text_props(weight=&#39;bold&#39;)<br><br>    return fig, weekly_xtraining_df_pivoted</pre><p>Let’s highlight the key steps of this function.</p><ol><li>Create a pivot table to group cross training volume by sport type &amp; week.</li><li>Specify the PDF page layout via Matplotlib GridSpec.</li><li>Plot weekly cross training volume.</li></ol><ul><li>Find the shared timeframe that captures all cross-training &amp; race activities, and reindex the dataframe with that new timeframe.</li><li>Create the bar chart from the updated dataframe.</li></ul><p>4. Plot the race times as a line plot overlaid on top of the cross training volume bar chart.</p><p>5. Create the table for weekly cross training summary statistics and place it at the bottom subplot.</p><p><strong>Generating Reports</strong></p><p>We’ve written the functions that generate the figures and data required for our reports. Let’s write that data out to PDF and Excel!</p><p>Our PDF report will be two pages, where each page contains a plot and its corresponding table.</p><ul><li>Each plot and table pair will be captured in a Matplotlib Figure (as seen in our function visualize_weekly_mileage_vs_race() and visualize_weekly_xtraining_vs_race().</li><li>We’ll capture the list of Figures to write to the PDF in a <a href="https://docs.python.org/3/library/dataclasses.html">dataclass</a>, PdfConfig.</li><li>We’ll use Matplotlib’s <a href="https://matplotlib.org/stable/api/backend_pdf_api.html#matplotlib.backends.backend_pdf.PdfPages">PdfPages</a> to write out each Figure to PDF.</li></ul><pre>from dataclasses import dataclass<br>from matplotlib.figure import Figure<br>    <br>@dataclass<br>class PdfConfig:<br>    &quot;&quot;&quot;<br>    Dataclass for a PDF report.<br>    Contains all the figures to include in the report, where each figure is written to a separate page.<br>    &quot;&quot;&quot;<br>    figs: list[Figure]<br><br>def generate_pdf(filename: str, pdf_config: PdfConfig):<br>    &quot;&quot;&quot;<br>    Create a PDF report with the specified filename.<br><br>    Args:<br>        filename (str): name of the PDF report<br>        pdf_config (PdfConfig): Class containing the figures to include in the PDF<br>    &quot;&quot;&quot;<br>    filename = filename.strip().split(&quot;.&quot;)[0] + &quot;.pdf&quot; # if filename included extension, remove it and enforce .pdf<br>    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)<br>    output_path = OUTPUT_DIR / filename<br>    <br>    with PdfPages(output_path) as pdf:<br>        for fig in pdf_config.figs:    <br>            pdf.savefig(fig)<br>            plt.close(fig)<br><br>def generate_reports(activities: dict, pr_map: dict, race: str, filename: str):<br>    &quot;&quot;&quot;<br>    Generate a PDF report &amp; Excel workbook containing the plots &amp; underlying data.<br>    Args:<br>        activities (dict): all activities<br>        pr_map (dict): all races/PRs<br>        race (str): race distance to analyze<br>        filename (str): name of the PDF report<br>    &quot;&quot;&quot;<br>    # Create dataframes &amp; figures<br>    mileage_vs_race_fig, mileage_vs_race_df = visualize_weekly_mileage_vs_race(activities[&quot;run&quot;], pr_map, race)<br>    xtraining_vs_race_fig, xtraining_vs_race_df = visualize_weekly_xtraining_vs_race(activities, pr_map, race)<br>    <br>    # Create PDF report<br>    generate_pdf(<br>        filename=filename, <br>        pdf_config=PdfConfig(figs=[mileage_vs_race_fig, xtraining_vs_race_fig])<br>    )<br>    print(f&quot;PDF report `{filename}.pdf` created!&quot;)<br><br>    # TODO: Create excel workbook</pre><p>Next, let’s create the excel workbook. It will contain 3 worksheets named the following:</p><ul><li><strong>Mileage</strong>: contains the Weekly Mileage vs. Race Performance plot along with its underlying dataframe.</li><li><strong>X-Training</strong>: contains the Weekly Cross Training vs. Race Performance plot along with its underlying dataframe.</li><li><strong>Race PRs</strong>: contains the dataframe containing race data.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*OKrltcq1wTiD0pq4_Q-ACQ.png" /><figcaption>Worksheet: Mileage Analysis</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*kFvEb2lMUoFKeoUsBzM4pQ.png" /><figcaption>Worksheet: Cross Training Analysis</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/595/1*5eklSPRNLnyJb0jq_m7I_w.png" /><figcaption>Worksheet: Race PRs</figcaption></figure><p>Since each worksheet will contain similar content (dataframe &amp; its corresponding plot, if applicable), we’ll bundle this content in a dataclass, WorkbookSheetConfig.</p><ul><li>Grouping this information in a dataclass isn’t necessary, but it’s certainly nicer than passing around tuples of (str, DataFrame, Figure), and allows us to give a name to this group of information that logically makes sense.</li></ul><p>We’ll use the <a href="https://pandas.pydata.org/docs/reference/api/pandas.ExcelWriter.html">pandas.ExcelWriter</a> class to write dataframes to Excel, and <a href="https://openpyxl.readthedocs.io/en/stable/">openpyxl</a> to write the Matplotlib Figures as images to the workbook and format cells.</p><pre>from dataclasses import dataclass<br>import pandas as pd<br>from matplotlib.figure import Figure<br><br>@dataclass<br>class WorkbookSheetConfig:<br>    &quot;&quot;&quot;<br>    Dataclass for an excel worksheet.<br>    Each worksheet requires the name, the data to write, and the figure that visualizes that data (if applicable).<br>    &quot;&quot;&quot;<br>    name: str<br>    data: pd.DataFrame<br>    fig: Figure | None = None<br><br>def generate_xlsx(filename: str, xlsx_config: list[WorkbookSheetConfig]):<br>    &quot;&quot;&quot;<br>    Create an excel workbook under the specified filename.<br>    Args:<br>        filename (str): name of the excel workbook to create<br>        xlsx_config (list[WorkbookSheetConfig]): Data to include in each worksheet in the excel workbook<br>    &quot;&quot;&quot;<br>    filename_xlsx = filename.strip().split(&quot;.&quot;)[0] + &quot;.xlsx&quot; # if filename included extension, remove it and enforce .xlsx<br>    OUTPUT_DIR.mkdir(parents=True, exist_ok=True)<br>    output_path = OUTPUT_DIR / filename_xlsx <br>    <br>    # Write data to the workbook: plots will be placed to the right of the dataframe, if applicable<br>    with pd.ExcelWriter(<br>        output_path,<br>        engine=&quot;openpyxl&quot;<br>    ) as writer:<br>        <br>        XTRAINING_SHEET_NAME = &quot;X-Training&quot;<br>        <br>        # Create each worksheet in the excel workbook<br>        for xlsx_sheet in xlsx_config:<br>            # Excel doesn&#39;t accept timezone aware columns: we&#39;ll remove timezone info, but preserve local time representation<br>            xlsx_sheet.data = xlsx_sheet.data.apply(<br>                lambda col: col.dt.tz_localize(None)<br>                if isinstance(col.dtype, DatetimeTZDtype)<br>                else col<br>            )<br>            <br>            # For X-Training data, we&#39;ll create an extra header row to annotate all the sport type columns<br>            if xlsx_sheet.name == XTRAINING_SHEET_NAME:<br>                xlsx_sheet.data.to_excel(<br>                    writer, <br>                    sheet_name=xlsx_sheet.name, <br>                    index=True,<br>                    startrow=1 # Write data on the second row to make room for header row above sport-type columns<br>                )<br>                <br>                ws = writer.sheets[xlsx_sheet.name]<br>                <br>                # Get sport-type columns to merge<br>                first_data_col = 2  # column B -&gt; Index occupies column A<br>                last_data_col = len(xlsx_sheet.data.columns) + 1<br>                # Merge cells above the sport type columns and add label: &quot;Elapsed Time by Sport Type&quot;<br>                ws.merge_cells(<br>                    start_row=1,<br>                    start_column=first_data_col,<br>                    end_row=1,<br>                    end_column=last_data_col<br>                )<br>                cell = ws.cell(row=1, column=first_data_col)<br>                cell.value = &quot;Elapsed Time by Sport Type (seconds)&quot;<br>                cell.font = Font(bold=True)<br>                cell.alignment = Alignment(horizontal=&quot;center&quot;)<br>                for cell in ws[2]:<br>                    cell.font = Font(bold=True)<br>            else:<br>                # For all other sheets, just write the data on the first row and bold the header<br>                xlsx_sheet.data.to_excel(<br>                    writer, <br>                    sheet_name=xlsx_sheet.name, <br>                    index=False<br>                )<br>                <br>                ws = writer.sheets[xlsx_sheet.name]<br>                <br>                for cell in ws[1]: # Header row is row 1 by default<br>                    cell.font = Font(bold=True)<br>            <br>            # If there&#39;s a figure to include, write it to the right of the data<br>            if xlsx_sheet.fig:<br>                # save figure to image (PNG)<br>                buffer = BytesIO()<br>                xlsx_sheet.fig.savefig(buffer, format=&quot;png&quot;, bbox_inches=&quot;tight&quot;) # svg for max zoom?<br>                buffer.seek(0)<br>                img = XLImage(buffer)<br>                <br>                # Write the image to the excel sheet: leave 2 blank columns between table and plot<br>                ncols = len(xlsx_sheet.data.columns)<br>                image_col = get_column_letter(ncols + 3)<br>                ws.add_image(img, f&quot;{image_col}1&quot;) # write the image to the specific cell<br><br><br>def generate_reports(activities: dict, pr_map: dict, race: str, filename: str):<br>    &quot;&quot;&quot;<br>    Generate a PDF report &amp; Excel workbook containing the plots &amp; underlying data.<br>    Args:<br>        activities (dict): all activities<br>        pr_map (dict): all races/PRs<br>        race (str): race distance to analyze<br>        filename (str): name of the PDF report &amp; Excel workbook<br>    &quot;&quot;&quot;<br>    # Create dataframes &amp; figures<br>    mileage_vs_race_fig, mileage_vs_race_df = visualize_weekly_mileage_vs_race(activities[&quot;run&quot;], pr_map, race)<br>    xtraining_vs_race_fig, xtraining_vs_race_df = visualize_weekly_xtraining_vs_race(activities, pr_map, race)<br>    pr_df = create_pr_df(pr_map, race)<br>    <br>    # Create PDF report<br>    generate_pdf(<br>        filename=filename, <br>        pdf_config=PdfConfig(figs=[mileage_vs_race_fig, xtraining_vs_race_fig])<br>    )<br>    print(f&quot;PDF report `{filename}.pdf` created!&quot;)<br>    <br>    # Create Excel workbook<br>    generate_xlsx(<br>        filename=filename,<br>        xlsx_config=[<br>            WorkbookSheetConfig(name=&quot;Mileage&quot;, data=mileage_vs_race_df, fig=mileage_vs_race_fig),<br>            WorkbookSheetConfig(name=&quot;X-Training&quot;, data=xtraining_vs_race_df, fig=xtraining_vs_race_fig),<br>            WorkbookSheetConfig(name=f&quot;{race} PRs&quot;, data=pr_df)<br>        ]<br>    )<br>    print(f&quot;Excel workbook `{filename}.xlsx` created!&quot;)</pre><p><strong>Program Flow</strong></p><p>Now that we have all the raw functions to generate the reports, let’s tie this up in an executable program.</p><p>Our execution flow consists of 3 main steps:</p><ol><li>Defining &amp; validating command line arguments.</li><li>Loading activity and race data from Strava or local files.</li><li>Generate the reports from that data.</li></ol><p>We’ll define the following command line arguments:</p><ul><li>distance: race distance to analyze.</li><li>num-races: number of races to analyze for the distance.</li><li>start-date: start of the date range captured by the plots.</li><li>end-date: end of the date range captured by the plots.</li><li>use-cached: optional boolean flag to indicate to fetch activity &amp; race data from local JSON files. If not supplied, then data will be loaded from the Strava API.</li><li>output: file name for the reports (PDF &amp; Excel) w/o the extension.</li></ul><p>We’ll use python’s <a href="https://docs.python.org/3/library/argparse.html">argparse</a> to define these options and parse them. argparse is good for defining CLI arguments as it automatically creates help menus, handles positional vs. optional arguments, and checks data types.</p><pre>import argparse<br><br>def parse_date(date_str: str) -&gt; datetime.date:<br>    &quot;&quot;&quot;Define date string format required for input arguments.&quot;&quot;&quot;<br>    return datetime.strptime(date_str, &quot;%Y-%m-%d&quot;).date()<br><br>def main():<br>    # 1. Define CLI args and validate them<br>    parser = argparse.ArgumentParser(description=&quot;Strava Analyzer: visualize the relationship between your training &amp; running performance!&quot;)<br>    parser.add_argument(&quot;-d&quot;, &quot;--distance&quot;, type=str, required=True, choices=RACE_TYPES, help=&quot;The race distance you want to analyze (ex: 1 mile, 5K, 10K, Half Marathon, Marathon).&quot;)<br>    parser.add_argument(&quot;-n&quot;, &quot;--num-races&quot;, type=int, default=5, help=&quot;The fastest N races you want to analyze for the distance (default: 5).&quot;)<br>    parser.add_argument(&quot;--start-date&quot;, type=parse_date, help=&quot;Start date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--end-date&quot;, type=parse_date, help=&quot;End date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--use-cached&quot;, action=&#39;store_true&#39;, help=&quot;Fetch activity &amp; PR data from local JSON files instead of the Strava API (default: False).&quot;)<br>    parser.add_argument(&quot;-o&quot;, &quot;--output&quot;, type=str, help=&quot;Filename of the analysis (default: {distance}_analysis_{timestamp}.pdf/.xlsx).&quot;)<br>    args = parser.parse_args()   <br><br>    # TODO: Load activity &amp; race data<br>    <br>    # TODO: Generate the reports</pre><p>Once we’ve parsed the user inputs to these arguments, we’ll validate them. Validation will consist of checking:</p><ul><li>The start date comes before the end date.</li><li>The start &amp; end date are at least a week apart — the plots can’t be generated otherwise. Ideally, the user would specify a date range that spans at least a couple months, as it would be difficult to demonstrate any real relationship between weekly mileage/cross training against race times for shorter time periods.</li></ul><p>Additionally, if the user didn’t specify a filename for the reports, we’ll set it to a default name, &lt;distance&gt;_analysis_&lt;timestamp&gt;.pdf/xlsx. For example, if I created reports to analyze my 5K races at the time of this writing (07/04/26), the default filename would be 5K_analysis_20260704.pdf/xlsx.</p><pre>import argparse<br>from datetime import datetime<br>from constants import *   <br><br>def parse_date(date_str) -&gt; datetime.date:<br>    &quot;&quot;&quot;Define date string format required for input arguments.&quot;&quot;&quot;<br>    return datetime.strptime(date_str, &quot;%Y-%m-%d&quot;).date()<br><br>def validate_input_arguments(args, parser):<br>    &quot;&quot;&quot;Validate the input arguments specified by the user.&quot;&quot;&quot;<br>    if args.start_date and args.end_date:<br>        if args.start_date &gt; args.end_date:<br>            parser.error(&quot;Start date must be before the end date.&quot;)<br>            <br>        # Start &amp; end dates should be at least a week apart to generate plots showing weekly mileage/x-training trends<br>        if (args.end_date - args.start_date) &lt; timedelta(weeks=1):<br>            raise ValueError(f&quot;Specified date range ({args.start_date} - {args.end_date}) is less than a week apart! Plots require at least a week of data.&quot;) <br><br><br>def main():<br>    # 1. Define CLI args and validate them<br>    parser = argparse.ArgumentParser(description=&quot;Strava Analyzer: visualize the relationship between your training &amp; running performance!&quot;)<br>    parser.add_argument(&quot;-d&quot;, &quot;--distance&quot;, type=str, required=True, choices=RACE_TYPES, help=&quot;The race distance you want to analyze (ex: 1 mile, 5K, 10K, Half Marathon, Marathon).&quot;)<br>    parser.add_argument(&quot;-n&quot;, &quot;--num-races&quot;, type=int, default=5, help=&quot;The fastest N races you want to analyze for the distance (default: 5).&quot;)<br>    parser.add_argument(&quot;--start-date&quot;, type=parse_date, help=&quot;Start date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--end-date&quot;, type=parse_date, help=&quot;End date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--use-cached&quot;, action=&#39;store_true&#39;, help=&quot;Fetch activity &amp; PR data from local JSON files instead of the Strava API (default: False).&quot;)<br>    parser.add_argument(&quot;-o&quot;, &quot;--output&quot;, type=str, help=&quot;Filename of the analysis (default: {distance}_analysis_{timestamp}.pdf/.xlsx).&quot;)<br>    args = parser.parse_args()   <br>    <br>    validate_input_arguments(args, parser)<br><br>    # Determine file name for reports<br>    RACE_TYPE = args.distance<br>    TOP_N = args.num_races <br>    if args.output is None:<br>        day_timestamp = datetime.now().strftime(&quot;%Y%m%d&quot;)<br>        OUTPUT_FILE = f&quot;{args.distance}_analysis_{day_timestamp}&quot;<br>    else:<br>        OUTPUT_FILE = args.output<br><br>    # TODO: Load activity &amp; race data<br>    <br>    # TODO: Generate the reports</pre><p>Next, we’ll load activity &amp; race data. If the user omits the use-cached flag, we’ll load activity data from the Strava API. Otherwise, we’ll <em>attempt</em> to load the data from local JSON files.</p><ul><li>If the these files don’t exist (they will only exist if they’ve been created from previous runs that loaded data from the Strava API), or if they have less than the user-specified number of PRs to include for each race distance, then we’ll load data from the Strava API.</li></ul><p>Finally, we’ll generate the reports from the activity &amp; race data for the user-specified race distance. These reports will be written to the filename that we determined with the appropriate extension (.pdf &amp; .xlsx).</p><pre>import argparse<br>from datetime import datetime<br>from constants import *   <br>import json<br><br>def parse_date(date_str) -&gt; datetime.date:<br>    &quot;&quot;&quot;Define date string format required for input arguments.&quot;&quot;&quot;<br>    return datetime.strptime(date_str, &quot;%Y-%m-%d&quot;).date()<br><br>def validate_input_arguments(args, parser):<br>    &quot;&quot;&quot;Validate the input arguments specified by the user.&quot;&quot;&quot;<br>    if args.start_date and args.end_date:<br>        if args.start_date &gt; args.end_date:<br>            parser.error(&quot;Start date must be before the end date.&quot;)<br>            <br>        # Start &amp; end dates should be at least a week apart to generate plots showing weekly mileage/x-training trends<br>        if (args.end_date - args.start_date) &lt; timedelta(weeks=1):<br>            raise ValueError(f&quot;Specified date range ({args.start_date} - {args.end_date}) is less than a week apart! Plots require at least a week of data.&quot;) <br><br>def validate_pr_map(pr_map: dict, top_N: int) -&gt; bool:<br>    &quot;&quot;&quot;<br>    Check if the PR dictionary contains the top N PRs for each distance.<br>    <br>    Args:<br>        pr_map (dict): PR dictionary<br>        top_N (int): number of PRs the PR dictionary should contain for each distance<br>    Returns:<br>        bool: True/False if the PR dictionary contains sufficient data<br>    &quot;&quot;&quot;<br>    # The PR map contains the top N PRs for each distance if at least one of the distances has at least N data points.<br>    max_num_races = 0<br>    for distance, races in pr_map.items():<br>        max_num_races = max(max_num_races, len(races))<br>        if max_num_races &gt;= top_N:<br>            return True<br>    return False<br><br>def get_activities_and_prs(start_date: date | None, end_date: date | None, top_N: int = 0) -&gt; tuple[dict, dict]:<br>    &quot;&quot;&quot;<br>    Get all activities and the top N race PRs within the specified date range<br>    Args:<br>        start_date (date | None): start of the date range<br>        end_date (date | None): end of the date range<br>        top_N (int, optional): number of PRs to record for each race distance. Defaults to 0.<br>    Returns:<br>        tuple[dict, dict]: dict of activities &amp; dict of race PRs<br>    &quot;&quot;&quot;<br>    activities_map = get_all_activities(start_date, end_date)<br>    pr_map = get_personal_records(activities_map, top_N)<br>    return activities_map, pr_map<br><br><br>def main():<br>    # 1. Define CLI args and validate them<br>    parser = argparse.ArgumentParser(description=&quot;Strava Analyzer: visualize the relationship between your training &amp; running performance!&quot;)<br>    parser.add_argument(&quot;-d&quot;, &quot;--distance&quot;, type=str, required=True, choices=RACE_TYPES, help=&quot;The race distance you want to analyze (ex: 1 mile, 5K, 10K, Half Marathon, Marathon).&quot;)<br>    parser.add_argument(&quot;-n&quot;, &quot;--num-races&quot;, type=int, default=5, help=&quot;The fastest N races you want to analyze for the distance (default: 5).&quot;)<br>    parser.add_argument(&quot;--start-date&quot;, type=parse_date, help=&quot;Start date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--end-date&quot;, type=parse_date, help=&quot;End date (YYYY-MM-DD)&quot;)<br>    parser.add_argument(&quot;--use-cached&quot;, action=&#39;store_true&#39;, help=&quot;Fetch activity &amp; PR data from local JSON files instead of the Strava API (default: False).&quot;)<br>    parser.add_argument(&quot;-o&quot;, &quot;--output&quot;, type=str, help=&quot;Filename of the analysis (default: {distance}_analysis_{timestamp}.pdf/.xlsx).&quot;)<br>    args = parser.parse_args()   <br>    <br>    validate_input_arguments(args, parser)<br><br>    # Determine file name for reports<br>    RACE_TYPE = args.distance<br>    TOP_N = args.num_races <br>    if args.output is None:<br>        day_timestamp = datetime.now().strftime(&quot;%Y%m%d&quot;)<br>        OUTPUT_FILE = f&quot;{args.distance}_analysis_{day_timestamp}&quot;<br>    else:<br>        OUTPUT_FILE = args.output<br><br>    # 2. Load activity &amp; race data<br>    if not args.use_cached:<br>        # Load data from the Strava API, if not operating in cached mode<br>        activities_map, pr_map = get_activities_and_prs(args.start_date, args.end_date, TOP_N)<br>    else:<br>        # Otherwise, load activities &amp; races from local JSON files.<br>        # If the files don&#39;t exist or don&#39;t contain sufficient PR data (i.e. less than TOP_N PRs per race distance), then we&#39;ll load activities from the Strava API<br>        print(f&quot;Loading activities &amp; PRs from JSON files in &#39;{DATA_DIR}&#39;...&quot;)<br>        try:<br>            with open(f&quot;{DATA_DIR}/{ACTIVITIES_FILE}.json&quot;) as f:<br>                activities_map = json.load(f)<br>            with open(f&quot;{DATA_DIR}/{PR_FILE}.json&quot;) as f:<br>                pr_map = json.load(f)<br>            <br>            if not validate_pr_map(pr_map, TOP_N):<br>                print(&quot;Local PR data doesn&#39;t contain sufficient data, calling Strava API to fetch all activities.&quot;)<br>                activities_map, pr_map = get_activities_and_prs(args.start_date, args.end_date, TOP_N)<br>            <br>        except FileNotFoundError as e:<br>            print(f&quot;Activity data not found from local JSON files: {e}.&quot;)<br>            print(&quot;Loading data from Strava API instead!&quot;)<br>            activities_map, pr_map = get_activities_and_prs(args.start_date, args.end_date, TOP_N)<br>    <br>    # 3. Create the reports (PDF &amp; Excel)<br>    generate_reports(activities_map, pr_map, RACE_TYPE, OUTPUT_FILE)</pre><h4>Wrap Up</h4><p>Thanks for reading! To briefly recap, we wrote a program to visualize the relationship between our training and race performance by:</p><ul><li>Fetching data from Strava</li><li>Transforming it to compute useful metrics (i.e. weekly mileage/cross training volume)</li><li>Creating plots to visualize race performance with respect to training volume over time.</li></ul><p>If you’re an avid runner who’s curious about how your training is impacting your racing, try it out and let me know what you think!</p><p>You can find the Git repository here:</p><p><a href="https://github.com/jimin-kang/running-performance-analyzer/tree/main#">GitHub - jimin-kang/running-performance-analyzer: Visualize your running performance against your training activities</a></p><p>Although (I think) these plots are insightful, there’s only so much static reports can give you. Next steps for this app could include:</p><ul><li>Migrating this to an interactive dashboard.</li><li>Including more detailed analysis behind running training beyond weekly mileage and its affect on race performance (ex: frequency of track workouts and/or long runs).</li><li>Analyze/quantify the relationship between training and race performance instead of simply visualizing.</li></ul><h4>Resources</h4><p><strong>Strava API</strong></p><ul><li><a href="https://developers.strava.com/docs/getting-started/#basic">Getting started</a></li><li><a href="https://developers.strava.com/docs/authentication/">Authentication</a></li><li><a href="https://developers.strava.com/docs/reference/">API Reference</a></li></ul><p><strong>stravalib</strong></p><ul><li><a href="https://stravalib.readthedocs.io/en/v2.2/get-started/index.html">Getting started</a></li><li><a href="https://stravalib.readthedocs.io/en/v2.2/get-started/authenticate-with-strava.html">Authentication</a></li><li><a href="https://stravalib.readthedocs.io/en/v2.2/reference.html">API Reference</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=49d377de6f54" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Creating a Data Pipeline to Monitor Local Crime Trends]]></title>
            <link>https://blog.dataengineerthings.org/creating-a-data-pipeline-to-monitor-local-crime-trends-e2493c20da39?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/e2493c20da39</guid>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[data-pipeline]]></category>
            <category><![CDATA[data-visualization]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Wed, 28 Jan 2026 15:22:04 GMT</pubDate>
            <atom:updated>2026-01-28T15:22:04.056Z</atom:updated>
            <content:encoded><![CDATA[<p>You’re curious about examining crime trends in your local area. You know that <a href="https://data.cambridgema.gov/Public-Safety/Daily-Police-Log/3gki-wyrb/about_data">relevant data</a> exists, and you have some basic analytical skills that you can use to analyze this data. However, this data is changing frequently, and you want to keep your analysis updated with the most recent crime incidents without doing repeating your analysis. How can we automate this process?</p><p>Well if you’ve stumbled upon this article, you’re in luck! Together, we’ll walk though how to create a data pipeline to extract local police log data, and connect this to a visualization platform to examine local crime trends over time. For this article, we’ll extract data on incidents reported to the Cambridge (MA) Police Department (CPD), and then visualize this data as a dashboard in <a href="https://www.metabase.com/">Metabase</a>.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*CTFg8THOR462UAItDZG1NA.png" /><figcaption>The final dashboard we’ll create to summarize recent and historical trends in CPD incidents.</figcaption></figure><h4><strong>Contents:</strong></h4><ul><li><strong>Background Knowledge</strong></li><li><strong>Data of Interest</strong></li><li><strong>ETL Pipeline</strong></li><li><strong>Visualizing in Metabase</strong></li><li><strong>Wrap-up and Future Work</strong></li><li><strong>Sources</strong></li></ul><h4><strong>Background Knowledge</strong></h4><p>Before we dive into the pipeline, it’ll be helpful to review the following concepts, or keep these links as reference as you read.</p><ul><li>Extract-Transform-Load: <a href="https://aws.amazon.com/what-is/etl/">ETL</a></li><li>Docker Compose concepts: <a href="https://docs.docker.com/reference/compose-file/services/">services</a>, <a href="https://docs.docker.com/reference/compose-file/networks/">networks</a>, and <a href="https://docs.docker.com/reference/compose-file/volumes/">volumes</a></li></ul><h4>Data of Interest</h4><p>The data we’ll be working with contains a collection of police log entries, where each entry is a single incident reported to/by the CPD. Each entry contains comprehensive information describing the incident, including but not limited to:</p><ul><li>Date &amp; time of the incident</li><li>Type of incident that occurred</li><li>The street where the incident took place</li><li>A plaintext description of what happened</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*99BuzBLM1ttigFJ8QWe4aQ.png" /><figcaption>A glimpse at the Cambridge Daily Police Log.</figcaption></figure><p>Check out the <a href="https://data.cambridgema.gov/Public-Safety/Daily-Police-Log/3gki-wyrb/about_data">portal</a> for more information about the dataset.</p><p>For monitoring crime trends in Cambridge, MA, creating a data pipeline to extract this data is appropriate, as the data is updated daily (according to their website). If the data was updated less frequently (e.g. annually), then creating a data pipeline to automate this process wouldn’t save us much effort. We could simply revisit the data portal at the end of each year, download the .csv, and complete our analysis.</p><p>Now that we’ve found the appropriate dataset, let’s walk through the implementation.</p><h4><strong>ETL Pipeline</strong></h4><p>To go from raw CPD log data to a Metabase dashboard, our project will consist of the following major steps:</p><ul><li>Extract the data by using its corresponding API.</li><li>Transforming it to prepare it for storage.</li><li>Loading it into a PostgreSQL database.</li><li>Visualizing the data in Metabase.</li></ul><p>The data flow of our system will look like the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/738/1*V68QhEaz78dxqGGTP9w6CQ.png" /><figcaption>System data flow from data extraction to Metabase visualization.</figcaption></figure><p>Our pipeline follows an ETL workflow, which means that we’ll transform the data before importing it into PostgreSQL. This requires loading data into memory while executing data transformations, which may be problematic for large datasets that are too big to fit in memory. In this case, we may consider an <a href="https://rivery.io/blog/etl-vs-elt/">ELT workflow</a>, where we transform the data in the same infrastructure where it’s stored. Since our dataset is small (&lt;10k rows), this shouldn’t be a problem, and we’ll take advantage of the fact that <a href="https://rivery.io/blog/etl-vs-elt/">pandas</a> makes data transformation easy.</p><h4><strong>ETL: Extract</strong></h4><p>We’ll extract the CPD log data by making a request for the dataset to the <a href="https://dev.socrata.com/foundry/data.cambridgema.gov/3gki-wyrb">Socrata Open Data API</a>. We’ll use <a href="https://github.com/afeld/sodapy">sodapy </a>— a python client for the API — to make the request.</p><p>We’ll encapsulate this extraction code in its own file — <strong>extract.py</strong>.</p><pre>import pandas as pd<br>from sodapy import Socrata<br>from dotenv import load_dotenv<br>import os<br>from prefect import task<br><br>@task(retries=3, retry_delay_seconds=[10, 10, 10]) # retry API request in case of failure<br>def extract_data():<br>    &#39;&#39;&#39;<br>    Extract incident data reported to the Cambridge Police Department using the Socrata Open Data API.<br>    Return the incident data as a Pandas DataFrame.<br>    &#39;&#39;&#39;<br>    # fetch Socrata app token from .env<br>    # include this app token when interacting with the Socrata API to avoid request throttling, so we can fetch all the incidents<br>    load_dotenv()<br>    APP_TOKEN = os.getenv(&quot;SOCRATA_APP_TOKEN&quot;) <br><br>    # create Socrata client to interact with the Socrata API (https://github.com/afeld/sodapy)<br>    client = Socrata(<br>        &quot;data.cambridgema.gov&quot;, <br>        APP_TOKEN, <br>        timeout=30 # increase timeout from 10s default - sometimes, it takes longer to fetch all the results<br>    )<br><br>    # fetch all data, paginating over results<br>    DATASET_ID = &quot;3gki-wyrb&quot; # unique identifier for Cambridge Police Log data (https://data.cambridgema.gov/Public-Safety/Daily-Police-Log/3gki-wyrb/about_data)<br>    results = client.get_all(DATASET_ID)<br><br>    # Convert to pandas DataFrame<br>    results_df = pd.DataFrame.from_records(results)<br><br>    return results_df</pre><p>Notes about the code:</p><ul><li><a href="https://dev.socrata.com/docs/app-tokens.html">Socrata throttles requests</a> if you don’t include an app token that uniquely identifies your application. To fetch all the results, we’ll include this token in our request and put this in a <a href="https://dotenvx.com/docs/env-file">.env</a> file to keep this out of our source code.</li><li>We’ll specify a 30 second timeout (instead of the 10 second default timeout) when making our request to the Socrata API. From experience using the API, fetching all the results could sometimes take longer than 10 seconds, and 30 seconds was typically enough to avoid timeout errors.</li><li>We’ll load the fetched results into a pandas DataFrame, since we’ll validate and transform this data using pandas.</li></ul><h4><strong>ETL: Validate</strong></h4><p>Now, we’ll do some basic data quality checks on the data.</p><p>The data is already fairly clean (which makes sense as it’s provided by the Cambridge Police Department). So, our data quality checks will act more as a “sanity check” that we didn’t ingest anything unexpected.</p><p>We’ll validate the following:</p><ul><li>All the expected columns (as specified <a href="https://data.cambridgema.gov/Public-Safety/Daily-Police-Log/3gki-wyrb/about_data">here</a>) are present.</li><li>All IDs are numeric.</li><li>Datetimes follow <a href="https://www.iso.org/iso-8601-date-and-time-format.html">ISO 8601 format</a>.</li><li>There are no missing values in columns that should contain data. Specifically, each incident should have a Datetime, ID, Type, and Location.</li></ul><p>We’ll put this validation code in its own file — <strong>validate.py</strong>.</p><pre>from datetime import datetime<br>from collections import Counter<br>import pandas as pd<br>from prefect import task<br><br>### UTILITIES<br>def check_valid_schema(df):<br>    &#39;&#39;&#39;<br>    Check whether the DataFrame content contains the expected columns for the Cambridge Police dataset. <br>    Otherwise, raise an error.<br>    &#39;&#39;&#39;<br>    SCHEMA_COLS = [&#39;date_time&#39;, &#39;id&#39;, &#39;type&#39;, &#39;subtype&#39;, &#39;location&#39;, &#39;last_updated&#39;, &#39;description&#39;]<br>    if Counter(df.columns) != Counter(SCHEMA_COLS):<br>        raise ValueError(&quot;Schema does not match with the expected schema.&quot;)<br>    <br>def check_numeric_id(df):<br>    &#39;&#39;&#39;<br>    Convert &#39;id&#39; values to numeric.<br>    If any &#39;id&#39; values are non-numeric, replace them with NaN, so they can be removed downstream in the data transformations.<br>    &#39;&#39;&#39;<br>    df[&#39;id&#39;] = pd.to_numeric(df[&#39;id&#39;], errors=&#39;coerce&#39;)<br>    return df<br><br>def verify_datetime(df):<br>    &#39;&#39;&#39;<br>    Verify &#39;date_time&#39; values follow ISO 8601 format (https://www.iso.org/iso-8601-date-and-time-format.html).<br>    Raise a ValueError if any of the &#39;date_time&#39; values are invalid.<br>    &#39;&#39;&#39;<br>    df.apply(lambda row: datetime.fromisoformat(row[&#39;date_time&#39;]), axis=1) <br>    <br>def check_missing_values(df):<br>    &#39;&#39;&#39;<br>    Check whether there are any missing values in columns that require data.<br>    For police logs, each incident should have a datetime, ID, incident type, and location.<br>    &#39;&#39;&#39;<br>    REQUIRED_COLS = [&#39;date_time&#39;, &#39;id&#39;, &#39;type&#39;, &#39;location&#39;]<br>    for col in REQUIRED_COLS:<br>        if df[col].isnull().sum() &gt; 0:<br>            raise ValueError(f&quot;Missing values are present in the &#39;{col}&#39; attribute.&quot;)<br><br>### VALIDATION LOGIC<br>@task<br>def validate_data(df):<br>    &#39;&#39;&#39;<br>    Check the data satisfies the following data quality checks:<br>    - schema is valid<br>    - IDs are numeric<br>    - datetime follows ISO 8601 format<br>    - no missing values in columns that require data<br>    &#39;&#39;&#39;<br>    check_valid_schema(df)<br><br>    df = check_numeric_id(df)<br><br>    verify_datetime(df)<br>    <br>    check_missing_values(df)<br>        <br>    return df</pre><p>When implementing these data quality checks, it’s important to think about how to handle data quality checks that fail.</p><ul><li>Do we want our pipeline to fail loudly (e.g. raise an error/crash)?</li><li>Should our pipeline handle failures silently? For instance, mark data identified to be invalid so that it can be removed downstream?</li></ul><p>We’ll raise an error if:</p><ul><li>The ingested data doesn’t follow the expected schema. It doesn’t make sense to process the data if it doesn’t contain what we expect.</li><li>Datetime doesn’t follow ISO 8601 format. There’s no standard way to convert incorrect datetime values to its corresponding correct datetime format.</li><li>The incident contains missing values for any one of datetime, ID, type, and location. Without these values, the incident cannot be described comprehensively.</li></ul><p>For records that have non-numeric IDs, we’ll fill them with NaN placeholders and then remove them downstream in the transformation step. These records do not break our analysis if we simply remove them.</p><h4><strong>ETL: Transform</strong></h4><p>Now, we’ll do some transformations on our data to prepare it for storage in PostgreSQL.</p><p>We’ll do the following transformations:</p><ul><li>Remove duplicate rows — we’ll use the ‘ID’ column to identify duplicates.</li><li>Remove invalid rows — some of the rows that failed the data quality checks were marked with an NaN ‘ID’, so we’ll remove these.</li><li>Split the datetime column into separate year, month, day, and time columns. In our final analysis, we may want to analyze crime trends by these different time intervals, so we’ll create these additional columns here to simplify our queries downstream.</li></ul><p>We’ll put this transformation code in its own file — <strong>transform.py</strong>.</p><pre>import pandas as pd<br>from prefect import task<br><br>### UTILITIES<br>def remove_duplicates(df):<br>    &#39;&#39;&#39;<br>    Remove duplicate rows from dataframe based on &#39;id&#39; column. Keep the first occurrence.<br>    &#39;&#39;&#39;<br>    return df.drop_duplicates(subset=[&quot;id&quot;], keep=&#39;first&#39;)<br><br>def remove_invalid_rows(df):<br>    &#39;&#39;&#39;<br>    Remove rows where the &#39;id&#39; is NaN, as these IDs were identified as non-numeric.<br>    &#39;&#39;&#39;<br>    return df.dropna(subset=&#39;id&#39;)<br><br>def split_datetime(df):<br>    &#39;&#39;&#39;<br>    Split the date_time column into separate year, month, day, and time columns.<br>    &#39;&#39;&#39;<br>    # convert to datetime<br>    df[&#39;date_time&#39;] = pd.to_datetime(df[&#39;date_time&#39;])<br><br>    # extract year/month/day/time<br>    df[&#39;year&#39;] = df[&#39;date_time&#39;].dt.year<br>    df[&#39;month&#39;] = df[&#39;date_time&#39;].dt.month<br>    df[&#39;day&#39;] = df[&#39;date_time&#39;].dt.day<br>    df[&#39;hour&#39;] = df[&#39;date_time&#39;].dt.hour<br>    df[&#39;minute&#39;] = df[&#39;date_time&#39;].dt.minute<br>    df[&#39;second&#39;] = df[&#39;date_time&#39;].dt.second<br><br>    return df<br><br>### TRANSFORMATION LOGIC<br>@task<br>def transform_data(df):<br>    &#39;&#39;&#39;<br>    Apply the following transformations to the passed in dataframe:<br>    - deduplicate records (keep the first)<br>    - remove invalid rows<br>    - split datetime into year, month, day, and time columns<br>    &#39;&#39;&#39; <br><br>    df = remove_duplicates(df)<br><br>    df = remove_invalid_rows(df)<br><br>    df = split_datetime(df)<br><br>    return df</pre><h4><strong>ETL: Load</strong></h4><p>Now our data is ready to import into into PostgreSQL.</p><p>Before we can import our data, we need to create our PostgreSQL instance. We’ll create one locally using a <a href="https://docs.docker.com/compose/intro/compose-application-model/">compose file</a>. This file allows us to define &amp; configure all the services that our application needs.</p><pre>services: <br>  postgres_cpd: # postgres instance for CPD ETL pipeline<br>    image: postgres:16<br>    container_name: postgres_cpd_dev<br>    environment:<br>      POSTGRES_USER: ${POSTGRES_USER}<br>      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}<br>      POSTGRES_DB: cpd_db<br>    ports:<br>      - &quot;5433:5432&quot; # Postgres is already on port 5432 on my local machine<br>    volumes: <br>      - pgdata_cpd:/var/lib/postgresql/data<br>    restart: unless-stopped<br>  pgadmin:<br>    image: dpage/pgadmin4<br>    container_name: pgadmin_dev<br>    environment:<br>      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}<br>      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}<br>    ports:<br>      - &quot;8081:80&quot;<br>    depends_on: # don&#39;t start pg_admin until our postgres instance is running<br>      - postgres_cpd <br><br>volumes:<br>  pgdata_cpd: # all data for our postgres_cpd service will be stored here<br></pre><p>There are two main services defined here:</p><ul><li><strong>postgres_cpd</strong> — This is our PostgreSQL instance where we’ll store our data.</li><li><strong>pgadmin </strong>—DB admin platform which provides a GUI we can use to query data in our PostgreSQL database. Not functionally required, but useful for checking the data in our database. For more information on connecting to your PostgreSQL database in pgAdmin, click <a href="https://www.enterprisedb.com/postgres-tutorials/connecting-postgresql-using-psql-and-pgadmin">here</a>.</li></ul><p>Let’s highlight some important configuration for our postgres_cpd service:</p><ul><li><strong>container_name: postgres_cpd_dev</strong> -&gt; Our service will run in a container (i.e. an isolated process) named <strong>postgres_cpd_dev</strong>. Docker generates random container names if you don’t specify this, so assigning a name will make it more straightforward to interact with the container.</li><li><strong>environment:</strong> -&gt; We create a Postgres user from credentials stored in our .env file. Additionally, we create a default database, <strong>cpd_dev</strong>.</li><li><strong>ports:</strong> -&gt; Our PostgreSQL service will listen on port 5432 within the container. However, we’ll map port 5433 on the host machine to port 5432 in the container, allowing us to connect to PostgreSQL from our host machine via port 5433.</li><li><strong>volumes:</strong> -&gt; Our service will store all its data (e.g. configuration, data files) under the following directory within the container: <strong>/var/lib/postgresql/data</strong>. We’ll mount this container directory to a named Docker volume stored on our local machine, <strong>pgdata_cpd</strong>. This allows us to persist the database data beyond the lifetime of the container.</li></ul><p>Now that we’ve created our PostgreSQL instance, we can execute queries against it. Importing our data into PostgreSQL requires executing two queries against the database:</p><ul><li>Creating the table that will store the data.</li><li>Loading our transformed data into that table.</li></ul><p>Each time we execute a query against our PostgreSQL instance, we need to do the following:</p><ul><li>Establish our connection to PostgreSQL.</li><li>Execute the query.</li><li>Commit the changes &amp; close the connection.</li></ul><pre>from prefect import task<br>from sqlalchemy import create_engine<br>import psycopg2<br>from dotenv import load_dotenv<br>import os<br><br># read content from .env, which contains our Postgres credentials<br>load_dotenv()<br><br>def create_postgres_table():<br>    &#39;&#39;&#39;<br>    Create the cpd_incidents table in Postgres DB (cpd_db) if it doesn&#39;t exist.<br>    &#39;&#39;&#39;<br>    # establish connection to DB<br>    conn = psycopg2.connect(<br>        host=&quot;localhost&quot;,<br>        port=&quot;5433&quot;,<br>        database=&quot;cpd_db&quot;,<br>        user=os.getenv(&quot;POSTGRES_USER&quot;),<br>        password=os.getenv(&quot;POSTGRES_PASSWORD&quot;)<br>    )<br><br>    # create cursor object to execute SQL<br>    cur = conn.cursor()<br>    <br>    # execute query to create the table<br>    create_table_query = &#39;&#39;&#39;<br>        CREATE TABLE IF NOT EXISTS cpd_incidents (<br>            date_time TIMESTAMP,<br>            id INTEGER PRIMARY KEY,<br>            type TEXT,<br>            subtype TEXT,<br>            location TEXT,<br>            description TEXT,<br>            last_updated TIMESTAMP,<br>            year INTEGER,<br>            month INTEGER,<br>            day INTEGER,<br>            hour INTEGER, <br>            minute INTEGER,<br>            second INTEGER<br>        )<br>    &#39;&#39;&#39;<br>    cur.execute(create_table_query)<br><br>    # commit changes<br>    conn.commit()<br><br>    # close cursor and connection<br>    cur.close()<br>    conn.close()<br><br>@task<br>def load_into_postgres(df):<br>    &#39;&#39;&#39;<br>    Loads the transformed data passed in as a DataFrame <br>    into the &#39;cpd_incidents&#39; table in our Postgres instance.<br>    &#39;&#39;&#39;<br>    # create table to insert data into as necessary<br>    create_postgres_table()<br><br>    # create Engine object to connect to DB<br>    engine = create_engine(f&quot;postgresql://{os.getenv(&quot;POSTGRES_USER&quot;)}:{os.getenv(&quot;POSTGRES_PASSWORD&quot;)}@localhost:5433/cpd_db&quot;)<br>        <br>    # insert data into Postgres DB into the &#39;cpd_incidents&#39; table<br>    df.to_sql(&#39;cpd_incidents&#39;, engine, if_exists=&#39;replace&#39;)</pre><p>Things to note about the code above:</p><ul><li>Similar to how we fetched our app token for extracting our data, we’ll fetch our Postgres credentials from a .env file.</li><li>To load the DataFrame containing our transformed data into Postgres, we’ll use the <a href="https://pandas.pydata.org/docs/reference/api/pandas.DataFrame.to_sql.html">pandas.DataFrame.to_sql()</a>. It’s a simple way to insert DataFrame data into any database supported by <a href="https://www.sqlalchemy.org/">SQLAlchemy</a>.</li></ul><p><strong>Defining our ETL Pipeline:</strong></p><p>We’ve implemented the individual components of the ETL process. Now, we’re ready to encapsulate these components into a pipeline.</p><p>There are many tools available to use for orchestrating pipelines defined in python. Two popular options are Apache Airflow and Prefect.</p><ul><li><a href="https://airflow.apache.org/">Apache Airflow</a> requires you to define data pipelines as directed acyclic graphs (DAGs) of executable tasks. However, Airflow setup isn’t simple as you have to <a href="https://airflow.apache.org/docs/apache-airflow/stable/installation/index.html#using-pypi">manage the infrastructure</a> where Airflow is deployed, and <a href="https://airflow.apache.org/docs/apache-airflow/stable/howto/docker-compose/index.html#special-case-adding-dependencies-via-requirements-txt-file">installing dependencies into the Airflow environment</a> can be a headache.</li><li><a href="https://docs.prefect.io/v3/how-to-guides/cloud/connect-to-cloud">Prefect</a> allows you to define data pipelines as <a href="https://docs.prefect.io/v3/how-to-guides/workflows/write-and-run">workflows</a> by simply adding <a href="https://www.geeksforgeeks.org/python/decorators-in-python/">decorators</a> to the python functions that define your pipeline. Additionally, <a href="https://docs.prefect.io/v3/how-to-guides/cloud/connect-to-cloud">Prefect Cloud</a> takes care of the infrastructure where your pipeline is executed, and <a href="https://docs.prefect.io/v3/how-to-guides/deployments/create-deployments">deploying pipelines</a> is extremely simple.</li></ul><p>For it’s simplicity, we’ll proceed with defining our pipeline using Prefect. We need to do the following to get started:</p><ul><li>Install Prefect in our development environment.</li><li>Get a Prefect API server. Since we don’t want to manage our own infrastructure to run Prefect, we’ll sign up for for Prefect Cloud.</li></ul><p>For more information on Prefect setup, check out the <a href="https://docs.prefect.io/v3/get-started/install">docs</a>.</p><p>Next, we must add the following decorators to our code:</p><ul><li><strong>@task</strong> -&gt; Add this to each function that implements a component of our ETL pipeline (i.e. our extract, validate, transform, and load functions).</li><li><strong>@flow </strong>-&gt; Add this decorator to the function that encapsulates the ETL components into an executable pipeline.</li></ul><p>If you look back at our extract, validate, transform, and load code, you’ll see that we added the <strong>@task</strong> decorator to these functions.</p><p>Now, let’s define our ETL pipeline that executes these tasks. We’ll put the following in a separate file, <strong>etl_pipeline.py</strong>.</p><pre>from extract import extract_data<br>from validate import validate_data<br>from transform import transform_data<br>from load import load_into_postgres<br>from prefect import flow<br><br>@flow(name=&quot;cpd_incident_etl&quot;, log_prints=True) # Our pipeline will appear as &#39;cpd_incident_etl&#39; in the Prefect UI. All print outputs will be displayed in Prefect.<br>def etl():<br>    &#39;&#39;&#39;<br>    Execute the ETL pipeline:<br>    - Extract CPD incident data from the Socrata API<br>    - Validate and transform the extracted data to prepare it for storage<br>    - Import the transformed data into Postgres <br>    &#39;&#39;&#39;<br>    print(&quot;Extracting data...&quot;)<br>    extracted_df = extract_data()<br><br>    print(&quot;Performing data quality checks...&quot;)<br>    validated_df = validate_data(extracted_df)<br><br>    print(&quot;Performing data transformations...&quot;)<br>    transformed_df = transform_data(validated_df)<br><br>    print(&quot;Importing data into Postgres...&quot;)<br>    load_into_postgres(transformed_df)<br><br>    print(&quot;ETL complete!&quot;)<br><br>if __name__ == &quot;__main__&quot;:<br>    # CPD data is expected to be updated daily (https://data.cambridgema.gov/Public-Safety/Daily-Police-Log/3gki-wyrb/about_data)<br>    # Thus, we&#39;ll execute our pipeline on a daily basis (at midnight)<br>    etl.serve(name=&quot;cpd-pipeline-deployment&quot;, cron=&quot;0 0 * * *&quot;) </pre><p>Things to note about the code:</p><ul><li><strong>@flow(name=”cpd_incident_etl”, log_prints=True)</strong> -&gt; this names our pipeline “cpd_incident_etl”, which will be reflected in the Prefect UI. The output of all our print statements will be logged in Prefect.</li><li><strong>etl.serve(name=”cpd-pipeline-deployment”, cron=”0 0 * * *”)</strong> -&gt; this creates a deployment of our pipeline, named “cpd-pipeline-deployment”, that runs every day at midnight.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*h_NpdHpIJhNU5JA_hvOEtQ.png" /><figcaption>Our flow will show up on Prefect’s home page.</figcaption></figure><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*Zj8lngjB-RIHa3KGH0_Msw.png" /><figcaption>The “Deployments” tab in the Prefect UI shows us our deployed flows.</figcaption></figure><h4><strong>Visualizing in Metabase</strong></h4><p>Now that we’ve created our pipeline to load our data into PostgreSQL, it’s time to visualize it.</p><p>There are many approaches we could take to visualize our data. Some notable options include:</p><ul><li><a href="https://streamlit.io/">Streamlit</a> — Python framework designed for building dynamic data applications.</li><li>BI tool (ex: <a href="https://www.tableau.com/">Tableau</a>, <a href="https://superset.apache.org/">Apache Superset</a>, <a href="https://www.metabase.com/">Metabase</a>).</li></ul><p>Both are good options. For now, we’ll use Metabase to make a simple dashboard.</p><ul><li>Metabase makes visualizing data simple, and easy to combine within dashboards.</li><li>Connecting Metabase to our data sources and deploying it is fairly simple, compared to other BI tools (ex: Apache Superset).</li></ul><p>In the future, if we want to have more customization over our visuals/reports, we can consider using other tools. For now, Metabase will do for creating a POC.</p><p>Metabase allows you to <a href="https://www.metabase.com/docs/latest/installation-and-operation/installing-metabase">choose</a> between using its cloud version or managing a self-hosted instance. For this project, we’ll create a self-hosted instance of Metabase by defining it in our compose file.</p><ul><li>Since we’re self-hosting, we also have to define the <a href="https://www.metabase.com/docs/latest/installation-and-operation/configuring-application-database?use_case=bi">Metabase application database</a>, which contains the metadata that Metabase needs to query your data sources (in our case, <strong>postgres_cpd</strong>).</li></ul><pre>services: <br>  postgres_cpd: # postgres instance for CPD ETL pipeline<br>    image: postgres:16<br>    container_name: postgres_cpd_dev<br>    environment:<br>      POSTGRES_USER: ${POSTGRES_USER}<br>      POSTGRES_PASSWORD: ${POSTGRES_PASSWORD}<br>      POSTGRES_DB: cpd_db<br>    ports:<br>      - &quot;5433:5432&quot; # Postgres is already on port 5432 on my local machine<br>    volumes: <br>      - pgdata_cpd:/var/lib/postgresql/data<br>    restart: unless-stopped<br>    networks:<br>      - metanet1<br>  pgadmin:<br>    image: dpage/pgadmin4<br>    container_name: pgadmin_dev<br>    environment:<br>      PGADMIN_DEFAULT_EMAIL: ${PGADMIN_DEFAULT_EMAIL}<br>      PGADMIN_DEFAULT_PASSWORD: ${PGADMIN_DEFAULT_PASSWORD}<br>    ports:<br>      - &quot;8081:80&quot;<br>    depends_on:<br>      - postgres_cpd<br>    networks:<br>      - metanet1<br>  metabase: # taken from https://www.metabase.com/docs/latest/installation-and-operation/running-metabase-on-docker<br>    image: metabase/metabase:latest<br>    container_name: metabase<br>    hostname: metabase<br>    volumes:<br>      - /dev/urandom:/dev/random:ro<br>    ports:<br>      - &quot;3000:3000&quot;<br>    environment:<br>      MB_DB_TYPE: postgres<br>      MB_DB_DBNAME: metabaseappdb<br>      MB_DB_PORT: 5432 <br>      MB_DB_USER: ${METABASE_DB_USER}<br>      MB_DB_PASS: ${METABASE_DB_PASSWORD}<br>      MB_DB_HOST: postgres_metabase # must match container name of postgres_mb (Metabase Postgres instance)<br>    networks:<br>      - metanet1<br>    healthcheck:<br>      test: curl --fail -I http://localhost:3000/api/health || exit 1<br>      interval: 15s<br>      timeout: 5s<br>      retries: 5<br>  postgres_mb: # postgres instance for managing Metabase instance<br>    image: postgres:16<br>    container_name: postgres_metabase # other services must use this name to communicate with this container<br>    hostname: postgres_metabase # internal identifier, doesn&#39;t impact communication with other services (helpful for logs)<br>    environment:<br>      POSTGRES_USER: ${METABASE_DB_USER}<br>      POSTGRES_DB: metabaseappdb<br>      POSTGRES_PASSWORD: ${METABASE_DB_PASSWORD}<br>    ports:<br>      - &quot;5434:5432&quot; <br>    volumes:<br>      - pgdata_mb:/var/lib/postgresql/data<br>    networks:<br>      - metanet1<br><br># Here, we&#39;ll define separate volumes to isolate DB configuration &amp; data files for each Postgres database.<br># Our Postgres DB for our application should store its config/data separately from the Postgres DB our Metabase service relies on.<br>volumes:<br>  pgdata_cpd:<br>  pgdata_mb:<br><br># define the network over which all the services will communicate<br>networks:<br>  metanet1:<br>    driver: bridge # TO DO: &#39;bridge&#39; is the default network - services will be able to communicate with each other using their service names</pre><p>To create our Metabase instance, we made the following changes to our compose file:</p><ul><li>Added two services: <strong>metabase</strong> (our Metabase instance) and <strong>postgres_mb </strong>(our Metabase instance’s application database).</li><li>Defined an additional volume, <strong>pgdata_mb</strong>. This will store the data for the Metabase application database (<strong>postgres_mb</strong>).</li><li>Defined the network over which the services will communicate, <strong>metanet1</strong>.</li></ul><p>Without going into too much detail, let’s break down the <strong>metabase</strong> and <strong>postgres_mb</strong> services.</p><p>Our Metabase instance (<strong>metabase</strong>):</p><ul><li>This service will be exposed on port 3000 on the host machine and within the container. If we’re running this service on our local machine, we’ll be able to access it at <a href="http://localhost:3000/">localhost:3000</a>.</li><li>We connect Metabase to it’s application database by ensuring that the <strong>MB_DB_HOST</strong>, <strong>MB_DB_PORT</strong>, and <strong>MB_DB_NAME</strong> environment variables match up with the container name, ports, and database name listed under the <strong>postgres_mb</strong> service.</li></ul><p>For more information on how to run Metabase in Docker, check out the <a href="https://www.metabase.com/docs/latest/installation-and-operation/running-metabase-on-docker">docs</a>.</p><p>After setting up Metabase, you’ll be prompted to connect Metabase to your data source.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/751/1*8n6Wio9dwXm-oMOZ6T7n2Q.png" /><figcaption>Metabase can connect to a wide variety of data sources.</figcaption></figure><p>After selecting a PostgreSQL data source, we can specify the following connection string to connect Metabase to our PostgreSQL instance, substituting your credentials as necessary:</p><pre>postgresql://{POSTGRES_USER}:{POSTGRES_PASSWORD}@postgres_cpd:5432/cpd_db</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/726/1*QlS7_ZGS1jAYOHVdfOEfKQ.png" /><figcaption>Connecting to PostgreSQL in Metabase by specifying your connection string.</figcaption></figure><p>After setting up the connection, we can create our dashboard. You can create a <a href="https://www.metabase.com/dashboards/">wide variety of visuals</a> in Metabase, so we won’t go into the specifics here.</p><p>Let’s revisit the example dashboard that we displayed at the beginning of this post. This dashboard nicely summarizes recent and historical trends in reported CPD incidents.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*x5NK_SxJOP7u-H0RRosW_g.png" /><figcaption>CPD Incident Trends dashboard created in Metabase.</figcaption></figure><p>From this dashboard, we can see the following:</p><ul><li>Most incidents are reported to the CPD in the mid-late afternoon.</li><li>An overwhelming majority of reported incidents are of the “INCIDENT” type.</li><li>The number of reported incidents peaked around August-October of 2025, and has been decreasing steadily ever since.</li></ul><p>Luckily for us, Metabase will query our database whenever we load this dashboard, so we won’t have to worry about this dashboard displaying stale data.</p><p>Check out the Git repo here if you want to dive deeper into the implementation.</p><p><a href="https://github.com/jimin-kang/cpd-pipeline#">GitHub - jimin-kang/cpd-pipeline: Data pipeline that extracts Cambridge Police Department incident data and displays them in Metabase.</a></p><h4>Wrap-up and Future Work:</h4><p>Thanks for reading! Let’s briefly recap what we built:</p><ul><li>We built a data pipeline to extract, transform, and load Cambridge Police Log data into a self-hosted PostgreSQL database.</li><li>We deployed this pipeline using Prefect and scheduled it to run daily.</li><li>We created a self-hosted instance of Metabase, connected it to our PostgreSQL database, and created a dashboard to visualize recent and historical crime trends in Cambridge, MA.</li></ul><p>There are many ways to build upon this project, including but not limited to:</p><ul><li>Creating additional visualizations (geospatial heatmap) to visualize crime frequencies in different areas within Cambridge. This would require transforming our street location data into latitude/longitude coordinates.</li><li>Deploying our self-hosted pipeline and services off of our local machine.</li><li>Consider joining this data with other datasets for insightful cross-domain analysis. For instance, perhaps we could join this dataset to demographic/census data (using street location) to see whether areas of different demographic makeup within Cambridge have different incident rates.</li></ul><p>If you have any other ideas for how to extend upon this project, or you would’ve built things differently, I’d love to hear it in the comments!</p><h4>Sources:</h4><p>Prefect:</p><ul><li><a href="https://docs.prefect.io/v3/examples/run-api-sourced-etl">Example API-sourced ETL</a></li><li><a href="https://docs.prefect.io/v3/how-to-guides/deployments/create-deployments">Deploying Workflows</a></li></ul><p>Metabase:</p><ul><li><a href="https://www.metabase.com/learn/metabase-basics/overview/tour-of-metabase">A Tour of Metabase</a></li><li><a href="https://www.metabase.com/docs/latest/installation-and-operation/running-metabase-on-docker">Metabase Docker Setup</a></li><li><a href="https://www.metabase.com/learn/metabase-basics/administration/administration-and-operation/metabase-and-your-db?use_case=bi">Metabase Architecture</a></li></ul><p>Docker:</p><ul><li><a href="https://docs.docker.com/reference/compose-file">Compose Reference</a></li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e2493c20da39" width="1" height="1" alt=""><hr><p><a href="https://blog.dataengineerthings.org/creating-a-data-pipeline-to-monitor-local-crime-trends-e2493c20da39">Creating a Data Pipeline to Monitor Local Crime Trends</a> was originally published in <a href="https://blog.dataengineerthings.org/">Data Engineer Things</a> on Medium, where people are continuing the conversation by highlighting and responding to this story.</p>]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Integrating DataHub into Jira: A Practical Guide Using DataHub Actions]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://blog.dataengineerthings.org/integrating-datahub-into-jira-a-practical-guide-using-datahub-actions-ae7b0da1c308?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/640/1*juDJJR0N92UPb-HqyAFQ-A.png" width="640"></a></p><p class="medium-feed-snippet">Integrate metadata changes in DataHub into Jira workflows using DataHub Actions Framework.</p><p class="medium-feed-link"><a href="https://blog.dataengineerthings.org/integrating-datahub-into-jira-a-practical-guide-using-datahub-actions-ae7b0da1c308?source=rss-6c200219f0de------2">Continue reading on Data Engineer Things »</a></p></div>]]></description>
            <link>https://blog.dataengineerthings.org/integrating-datahub-into-jira-a-practical-guide-using-datahub-actions-ae7b0da1c308?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/ae7b0da1c308</guid>
            <category><![CDATA[data-governance]]></category>
            <category><![CDATA[data-management]]></category>
            <category><![CDATA[jira]]></category>
            <category><![CDATA[datahub]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Thu, 18 Sep 2025 15:58:07 GMT</pubDate>
            <atom:updated>2025-09-18T15:58:07.956Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Applications of Density Estimation to Legal Theory]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/data-science-collective/applications-of-density-estimation-to-legal-theory-b9f773f77a45?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/753/1*Qwst2HhNE2T8NEfvJbgs8A.png" width="753"></a></p><p class="medium-feed-snippet">A brief analysis using density estimation to compare the two-verdict and three-verdict systems.</p><p class="medium-feed-link"><a href="https://medium.com/data-science-collective/applications-of-density-estimation-to-legal-theory-b9f773f77a45?source=rss-6c200219f0de------2">Continue reading on Data Science Collective »</a></p></div>]]></description>
            <link>https://medium.com/data-science-collective/applications-of-density-estimation-to-legal-theory-b9f773f77a45?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/b9f773f77a45</guid>
            <category><![CDATA[density-estimation]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[data-science]]></category>
            <category><![CDATA[legal-theory]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Thu, 12 Jun 2025 20:57:37 GMT</pubDate>
            <atom:updated>2025-06-12T20:57:37.731Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Non-Parametric Density Estimation: Theory and Applications]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://medium.com/data-science-collective/non-parametric-density-estimation-theory-and-applications-6b31eeb0ee20?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/1344/1*I5mDKX_fD7_QAzbWYhzTDg.png" width="1344"></a></p><p class="medium-feed-snippet">A theoretical and practical introduction to non-parametric density estimation.</p><p class="medium-feed-link"><a href="https://medium.com/data-science-collective/non-parametric-density-estimation-theory-and-applications-6b31eeb0ee20?source=rss-6c200219f0de------2">Continue reading on Data Science Collective »</a></p></div>]]></description>
            <link>https://medium.com/data-science-collective/non-parametric-density-estimation-theory-and-applications-6b31eeb0ee20?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/6b31eeb0ee20</guid>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[histograms]]></category>
            <category><![CDATA[density-estimation]]></category>
            <category><![CDATA[statistics]]></category>
            <category><![CDATA[kernel-density-estimation]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Tue, 13 May 2025 02:57:25 GMT</pubDate>
            <atom:updated>2025-05-13T02:57:25.832Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[MapReduce: How it Powers Scalable Data Processing]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://levelup.gitconnected.com/mapreduce-how-it-powers-scalable-data-processing-68e68c5e7172?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/1306/1*soLVG_G77-NwEH52m0_3Zw.jpeg" width="1306"></a></p><p class="medium-feed-snippet">An overview of the MapReduce programming model and how it can be used to optimize large-scale data processing.</p><p class="medium-feed-link"><a href="https://levelup.gitconnected.com/mapreduce-how-it-powers-scalable-data-processing-68e68c5e7172?source=rss-6c200219f0de------2">Continue reading on Level Up Coding »</a></p></div>]]></description>
            <link>https://levelup.gitconnected.com/mapreduce-how-it-powers-scalable-data-processing-68e68c5e7172?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/68e68c5e7172</guid>
            <category><![CDATA[distributed-computing]]></category>
            <category><![CDATA[mapreduce]]></category>
            <category><![CDATA[distributed-systems]]></category>
            <category><![CDATA[parallel-computing]]></category>
            <category><![CDATA[big-data]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Mon, 21 Apr 2025 07:00:40 GMT</pubDate>
            <atom:updated>2025-05-05T13:53:26.554Z</atom:updated>
        </item>
        <item>
            <title><![CDATA[Concurrent Programming: Producer-Consumer Problem]]></title>
            <description><![CDATA[<div class="medium-feed-item"><p class="medium-feed-image"><a href="https://levelup.gitconnected.com/concurrent-programming-producer-consumer-problem-d28926815305?source=rss-6c200219f0de------2"><img src="https://cdn-images-1.medium.com/max/730/1*X8xGMbjA7hbQI8frqlmpSg.png" width="730"></a></p><p class="medium-feed-snippet">An overview of a couple synchronization methods to approach this central problem in concurrent programming.</p><p class="medium-feed-link"><a href="https://levelup.gitconnected.com/concurrent-programming-producer-consumer-problem-d28926815305?source=rss-6c200219f0de------2">Continue reading on Level Up Coding »</a></p></div>]]></description>
            <link>https://levelup.gitconnected.com/concurrent-programming-producer-consumer-problem-d28926815305?source=rss-6c200219f0de------2</link>
            <guid isPermaLink="false">https://medium.com/p/d28926815305</guid>
            <category><![CDATA[producer-consumer-problem]]></category>
            <category><![CDATA[multithreading]]></category>
            <category><![CDATA[concurrency]]></category>
            <category><![CDATA[computer-science]]></category>
            <dc:creator><![CDATA[Jimin Kang]]></dc:creator>
            <pubDate>Fri, 14 Feb 2025 01:58:10 GMT</pubDate>
            <atom:updated>2025-02-14T01:58:10.429Z</atom:updated>
        </item>
    </channel>
</rss>