<?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 Octavian Zarzu on Medium]]></title>
        <description><![CDATA[Stories by Octavian Zarzu on Medium]]></description>
        <link>https://medium.com/@octavianzarzu?source=rss-7664a035848d------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/1*yBMYW5NV7SSW4l-0ePRGdw.jpeg</url>
            <title>Stories by Octavian Zarzu on Medium</title>
            <link>https://medium.com/@octavianzarzu?source=rss-7664a035848d------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Sat, 21 Nov 2026 11:06:30 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@octavianzarzu/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[Discovering Chess Openings in Grandmasters’ Games using Python and DuckDB]]></title>
            <link>https://medium.com/@octavianzarzu/discovering-chess-openings-in-grandmasters-games-using-python-and-duckdb-e564d503665e?source=rss-7664a035848d------2</link>
            <guid isPermaLink="false">https://medium.com/p/e564d503665e</guid>
            <category><![CDATA[chess]]></category>
            <category><![CDATA[data-analysis]]></category>
            <category><![CDATA[python]]></category>
            <category><![CDATA[duckdb]]></category>
            <dc:creator><![CDATA[Octavian Zarzu]]></dc:creator>
            <pubDate>Tue, 11 Apr 2023 14:36:42 GMT</pubDate>
            <atom:updated>2023-04-11T14:38:48.830Z</atom:updated>
            <content:encoded><![CDATA[<p>Do today’s grandmasters rely on conventional chess openings? Let’s find out by analyzing the top 100 live blitz players’ games as ranked by chess.com using Python and DuckDB.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*JK-HrEmWf2sEJmCyAN8UbA.png" /></figure><p>The process consists of these steps:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*VmUEeC_T_PPwAtKniWl5FQ.png" /></figure><ul><li>Retrieve the top 100 players’ list.</li><li>Obtain the game records for each player from the past three months.</li><li>Process individual game moves and store each player’s (black and white) moves in separate arrays within a Python dataframe.</li><li>Construct a static DuckDB table featuring well-known chess openings.</li><li>Join the Python dataframe and the DuckDB table based on the chess moves arrays while slicing the arrays to ensure they have similar lengths.</li></ul><p>This should be fun, so let’s dive right into it!</p><h3>Setup</h3><p>We need the following Python packages:</p><ul><li>chessdotcom — for fetching the list of games</li><li>chess — for processing each game moves</li><li>pandas — for storing the games’ moves as a Dataframe</li><li>duckdb — for storing popular chess openings and doing array lookups between a DuckDB table and a Python dataframe</li><li>datetime, io — minor processing</li></ul><pre>import chessdotcom as cdc<br>import datetime<br>import chess.pgn<br>import io<br>import pandas<br>import re<br>import duckdb</pre><h3>Processing games’ moves</h3><p>(To follow along, you can open this <a href="https://colab.research.google.com/drive/1XHoTNyGC3aGkYauaP6j1vuAVZ83C3MTZ#scrollTo=WaMOm_WHLEJn">Google Collab file</a> that contains all the cells executed in this article)</p><p>To kick off our analysis, we’ll begin by fetching the top players. We’ll utilize the <a href="https://chesscom.readthedocs.io/en/latest/#chessdotcom.client.get_leaderboards">get_leaderboards</a> method from the chessdotcom package.</p><pre>num_players=100<br>leaderboards = cdc.get_leaderboards().json<br>top_blitz_players = leaderboards[&#39;leaderboards&#39;][&#39;live_blitz&#39;][:num_players]</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/440/1*vR6rTn9ygbHQP7hb2jVdXg.png" /></figure><p>All we require are the player names. Let’s encapsulate the above code in a function and have it return a list containing these names.</p><pre>def get_top_players(num_players=100):<br>    leaderboards = cdc.get_leaderboards().json<br>    top_blitz_players = leaderboards[&#39;leaderboards&#39;][&#39;live_blitz&#39;][:num_players]<br>    return [player[&#39;username&#39;] for player in top_blitz_players]</pre><p>Moving forward, our goal is to determine the games each player participated in during the past three months. To achieve this, we can use the <a href="https://chesscom.readthedocs.io/en/latest/#chessdotcom.client.get_player_games_by_month">get_player_games_by_month</a> method in the chessdotcom package. This method takes a username and a specific month as input, and returns an object containing all games for that month, including the <a href="https://www.chess.com/terms/chess-pgn">PGN (Portable Game Notation)</a> for each game. We will want to control how many months we want to go back, so let’s define a variable called months_ago to control the timeframe:</p><pre>def get_games_for_player(username, months_ago=3):<br>    now = datetime.datetime.now()<br>    games = []<br><br>    for i in range(months_ago):<br>        month = (now - datetime.timedelta(days=(i * 30)))<br>        games_data = cdc.get_player_games_by_month(username, datetime_obj=month).json<br>        games.extend(games_data.get(&#39;games&#39;, []))<br>        <br>    return games</pre><h4>Finding each game moves</h4><p>Unfortunately, here is where we cannot use the chess.com package anymore, and we need to switch to the chess package. This package contains a <a href="https://python-chess.readthedocs.io/en/latest/pgn.html">read_game</a> function that takes the game PGN (Portable Game Notation) as input and can produce a human-readable representation of each move.</p><p>We will store each player’s moves in two separate lists for black and white pieces:</p><pre>def process_moves(pgn_text):<br>    pgn_text = io.StringIO(pgn_text)<br>    pgn = chess.pgn.read_game(pgn_text)<br><br>    white_moves = []<br>    black_moves = []<br>    turn = 1  # 1 for white, -1 for black<br><br>    board = pgn.board()<br><br>    for move in pgn.mainline_moves():<br>        san_move = board.san(move)<br>        if turn == 1:<br>            white_moves.append(san_move)<br>        else:<br>            black_moves.append(san_move)<br><br>        board.push(move)<br>        turn *= -1<br><br>    return white_moves, black_moves</pre><p>Great! Now, let’s combine all the pieces we’ve created so far and populate a dataframe with the moves from all the games:</p><pre>num_players = 1<br>num_months = 3<br><br>top_players = get_top_players(num_players)<br><br>all_games = []<br><br>for player in top_players:<br>    games = get_games_for_player(player, num_months)<br>    for game in games:<br>        game_id = game[&#39;url&#39;].split(&#39;/&#39;)[-1]<br>        pgn_text = game[&#39;pgn&#39;]<br>        white_moves, black_moves = process_moves(pgn_text)<br><br>        all_games.append({<br>            &#39;game_id&#39;: game_id,<br>            &#39;white&#39;: game[&#39;white&#39;][&#39;username&#39;],<br>            &#39;black&#39;: game[&#39;black&#39;][&#39;username&#39;],<br>            &#39;white_moves&#39;: white_moves,<br>            &#39;black_moves&#39;: black_moves,<br>        })<br><br>all_games_df = pd.DataFrame(all_games)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/963/1*nFsNNARa14ifaWdhO0Q7iw.png" /></figure><h3>Lookup popular chess openings</h3><p>We now have a dataframe containing all the player’s moves for the past three months. We are going to transition to DuckDB to build a static list of popular. Afterward, we’ll perform a join between the above dataframe and the DuckDB table:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/518/1*3c3HOiM71T70N7uxPdr69A.png" /></figure><p>We’ll utilize some of the openings listed <a href="https://www.ragchess.com/best-chess-openings/">here</a> to populate the table.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/610/1*5XE4w3kZJ0gIJ_CkGKtEoQ.png" /></figure><p>and split the openings’ moves between white and black.</p><pre>con = duckdb.connect()<br><br>ctas_popular_openings_query = &quot;&quot;&quot;<br>CREATE TABLE popular_openings AS <br>SELECT * FROM (<br>  VALUES<br>    (&#39;Italian Game&#39;, ARRAY[&#39;e4&#39;, &#39;Nf3&#39;, &#39;Bc4&#39;], ARRAY[&#39;e5&#39;, &#39;Nc6&#39;]),<br>    (&#39;Sicilian Defense&#39;, ARRAY[&#39;e4&#39;], ARRAY[&#39;c5&#39;]),<br>    (&#39;French Defense&#39;, ARRAY[&#39;e4&#39;], ARRAY[&#39;e6&#39;]),<br>    (&#39;Caro-Kann Defense&#39;, ARRAY[&#39;e4&#39;], ARRAY[&#39;c6&#39;]),<br>    (&#39;Ruy Lopez (Spanish Opening)&#39;, ARRAY[&#39;e4&#39;, &#39;Nf3&#39;, &#39;Bb5&#39;], ARRAY[&#39;e5&#39;, &#39;Nc6&#39;]),<br>    (&#39;Slav Defense&#39;, ARRAY[&#39;d4&#39;, &#39;c4&#39;], ARRAY[&#39;d5&#39;, &#39;c6&#39;]),<br>    (&#39;Queens Gambit Declined&#39;, ARRAY[&#39;d4&#39;, &#39;c4&#39;], ARRAY[&#39;d5&#39;, &#39;e6&#39;]),<br>    (&#39;Kings Indian Defense&#39;, ARRAY[&#39;d4&#39;, &#39;c4&#39;, &#39;Nc3&#39;], ARRAY[&#39;Nf6&#39;, &#39;g6&#39;, &#39;Bg7&#39;]),<br>    (&#39;Nimzo-Indian Defense&#39;, ARRAY[&#39;d4&#39;, &#39;c4&#39;, &#39;Nc3&#39;], ARRAY[&#39;Nf6&#39;, &#39;e6&#39;, &#39;Bb4&#39;]),<br>    (&#39;Grunfeld Defense&#39;, ARRAY[&#39;d4&#39;, &#39;c4&#39;, &#39;Nc3&#39;], ARRAY[&#39;Nf6&#39;, &#39;g6&#39;, &#39;d5&#39;]),<br>    (&#39;English Opening&#39;, ARRAY[&#39;c4&#39;], ARRAY[]),<br>    (&#39;Reti Opening&#39;, ARRAY[&#39;Nf3&#39;], ARRAY[]),<br>    (&#39;Dutch Defense&#39;, ARRAY[&#39;d4&#39;], ARRAY[&#39;f5&#39;]),<br>    (&#39;Scandinavian Defense (Center Counter Defense)&#39;, ARRAY[&#39;e4&#39;], ARRAY[&#39;d5&#39;]),<br>    (&#39;Pirc Defense&#39;, ARRAY[&#39;e4&#39;, &#39;d4&#39;], ARRAY[&#39;d6&#39;, &#39;Nf6&#39;]),<br>    (&#39;Alekhines Defense&#39;, ARRAY[&#39;e4&#39;], ARRAY[&#39;Nf6&#39;]),<br>    (&#39;Modern Defense&#39;, ARRAY[&#39;e4&#39;, &#39;d4&#39;], ARRAY[&#39;g6&#39;, &#39;Bg7&#39;]),<br>    (&#39;Kings Gambit&#39;, ARRAY[&#39;e4&#39;, &#39;f4&#39;], ARRAY[&#39;e5&#39;]),<br>    (&#39;Vienna Game&#39;, ARRAY[&#39;e4&#39;, &#39;Nc3&#39;], ARRAY[&#39;e5&#39;]),<br>    (&#39;Philidor Defense&#39;, ARRAY[&#39;e4&#39;, &#39;Nf3&#39;], ARRAY[&#39;e5&#39;, &#39;d6&#39;])<br>) AS t (opening, white_moves, black_moves);<br>&quot;&quot;&quot;<br><br>con.execute(ctas_popular_openings_query)<br></pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/507/1*__QUnmJeXe9O8eSWG2pgCw.png" /></figure><p>Awesome! We are only a step away from answering our initial question: Do top players leverage common chess openings? To answer the question we need to join the two objects we just built.</p><p>But how can we perform the join between the two arrays?</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/824/1*CbbHZm0gEYA1d35GdamMCA.png" /></figure><p>We cannot slice the all_mvoes_df.white_moves (and black_moves) by a static value — 2 or 3 — because some of the openings have a length of 2 moves, whereas others have a length of 3 or even more.</p><p>One option would be to leverage the length of the popular_openings.white_moves (and black_moves) arrays and <strong>slice the all_moves_df dataframe black_ and white_moves arrays by the length of the chess_openings table black_ and white_moves arrays</strong>.</p><pre>join_query = &quot;&quot;&quot;<br>SELECT *<br>FROM all_games_df agdf <br>    INNER JOIN popular_openings po <br>          ON agdf.white_moves[1:len(po.white_moves)] = po.white_moves <br>             AND agdf.black_moves[1:len(po.black_moves)] = po.black_moves<br>&quot;&quot;&quot;<br><br>con.execute(join_query).df()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*mxD8Yg82UB5u_MyjJj4k5A.png" /></figure><p>🤯 So let’s just recap what happened:</p><ul><li>We joined a Python dataframe (all_games_df) with a duckdb table (popular_openings) in the same query!</li><li>We sliced the Dataframe array columns, white_moves, and black_moves by the length of the similarly named arrays from the duckdb table. And then perform the join on the sliced arrays!</li></ul><p>LGTM! So let’s now run the script with the num_players variable set to 100.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/974/1*e9auxZQZ6FfxxO-MJ17PGA.png" /></figure><h4>Notes</h4><p>Two important notes:</p><ul><li>To handle games without associated or valid PGNs, we can add a check in the process_moves() function:</li></ul><pre>main(): pgn_text = game[&#39;pgn&#39;] -&gt; pgn_text = game.get(&#39;pgn&#39;, &#39;&#39;) # replace<br>process_moves(): if pgn is None: return [], [] # add</pre><ul><li>Some of the rows are duplicated in the all_games_df dataframe — a game shows up twice if the two players are in the top 100:</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/579/1*SN4c4lXGhVrhnE8R4H1qsQ.png" /></figure><p>In order to remove the duplicates we will use the <a href="https://duckdb.org/docs/sql/query_syntax/qualify.html">QUALIFY</a> clause, which allows us to filter WINDOW functions results:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/982/1*fjT_EpLF2lJLsPKHQg8LYA.png" /></figure><p>Our dataset has been reduced from 22,377 games to 18,786. Now, let’s merge with dataframe with the popular openings table and perform a LEFT JOIN — this will allow us to examine the number of games that utilize a common opening and those that do not::</p><pre>join_query = &quot;&quot;&quot;<br>SELECT ifnull(po.opening, &#39;No common opening&#39;) as opening, COUNT(*) as count<br>FROM (<br>    SELECT * <br>    FROM all_games_df <br>    QUALIFY ROW_NUMBER() OVER (PARTITION BY game_id) = 1   <br>    ) agdf <br>    LEFT JOIN popular_openings po ON agdf.white_moves[1:len(po.white_moves)] = po.white_moves AND agdf.black_moves[1:len(po.black_moves)] = po.black_moves<br>GROUP BY ALL<br>ORDER BY COUNT(*) DESC<br>&quot;&quot;&quot;<br><br>con.execute(join_query).df()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/378/1*lGJWB9zEf4bgxSlqo-FXJw.png" /></figure><p>Of the 18,876 games, 10,605 (or approximately 56%) rely on one of the 20 chess openings mentioned above.</p><p>Plotting only those games (56%) that started with one of the 20 openings (switching back from left to inner join), we can visualize which ones are the most frequently used openings among them:</p><pre>con.execute(join_query).df().plot(x=&#39;opening&#39;, y=&#39;count&#39;, kind=&#39;barh&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/889/1*w2eBhV3XUnqF1NkBSmEMmA.png" /></figure><h3>Conclusions</h3><p>We’ve done a lot! We utilized two chess-related public Python packages to build a dataframe containing the moves of all games for a specific query— the games played by the first 100 players ranked on chess.com in live blitz over the past 3 months. We then created a table in DuckDB, joined it with the dataframe, slice arrays, and used the QUALIFY clause to eliminate duplicates.</p><p>I have created a <a href="https://colab.research.google.com/drive/1XHoTNyGC3aGkYauaP6j1vuAVZ83C3MTZ?usp=sharing">Google Collab notebook</a> with all the code from above if you want to run it yourself.</p><p>You can look up individual games from the dataset by replacing {game_id} with the actual game_id in the following URL and replay the moves:</p><p>https://www.chess.com/game/live/{game_id}</p><p>For example: <a href="https://www.chess.com/game/live/74367799555">https://www.chess.com/game/live/74367799555</a></p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*P03Cvq-Jsjd06H4uPw1xMw.png" /></figure><p>Have fun and happy SQL’ing!</p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=e564d503665e" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Build and deploy apps with DuckDB and Streamlit in under one hour]]></title>
            <link>https://medium.com/@octavianzarzu/build-and-deploy-apps-with-duckdb-and-streamlit-in-under-one-hour-852cd31cccce?source=rss-7664a035848d------2</link>
            <guid isPermaLink="false">https://medium.com/p/852cd31cccce</guid>
            <dc:creator><![CDATA[Octavian Zarzu]]></dc:creator>
            <pubDate>Mon, 27 Feb 2023 13:07:52 GMT</pubDate>
            <atom:updated>2023-02-27T13:07:52.816Z</atom:updated>
            <content:encoded><![CDATA[<h3>Building and deploying data apps with DuckDB and Streamlit</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lBDkYC-WVISF-QlLsdqRLA.png" /></figure><p>For me, someone primarily comfortable writing SQL, I longed for a setup that would allow me to *quickly* build data apps off some data locally and deploy it to the cloud.</p><p>The setup I chose is composed of the following:</p><ul><li><a href="https://duckdb.org/">DuckDB </a>— an analytics <strong>in-process</strong> SQL database. Think SQLite, but for analytics. Unlike other popular analytics databases, there is no DBMS server to install, update or maintain. All data is stored in a persistent,<strong> </strong>single-file database. DuckDB does not run as a separate process and can query Pandas data directly without importing or copying any data or query foreign data (csv, parquet, json files) without copying <a href="https://duckdb.org/why_duckdb.html#duckdbissimple">[1]</a></li><li><a href="https://streamlit.io/">Streamlit</a> — a framework that turns your data scripts into web apps without any front-end experience.</li></ul><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*s8JxRtipKSMzXvON6pnGRg.png" /></figure><h3>Why Streamlit + DuckDB?</h3><p>Streamlit is a python framework. Therefore, most data-intensive interactions rely on sending queries to a database server and converting the results into a dataframe before visualizing it.</p><p>As for DuckDB, the overhead of communicating with an external service goes away — there is no database server to install, maintain or worry about configuring. This makes it extremely fast since DuckDB can run in-process.</p><p>Furthermore, DuckDB can query pandas dataframes directly and has extensive SQL support.</p><h3>Deploying the app</h3><p>We have two options:</p><p>1. <a href="https://streamlit.io/cloud">Streamlit Community Cloud </a>— Free</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/754/1*AU68JqbefBlZNvnhijRBRQ.png" /></figure><p>2. Using Docker and deploying the docker container on Amazon ECS — Flexible</p><p>The steps to set up the Docker image are outlined <a href="https://docs.streamlit.io/knowledge-base/tutorials/deploy/docker">here</a>, containing a sample <strong>Dockerfile</strong>. All that is left is to push the app code to Github, add a `requirements.txt` to specify all your Python dependencies, build the image and run using the following two commands:</p><pre>docker build -t streamlit .<br>docker run -p 8501:8501 streamlit<br><br>  You can now view your Streamlit app in your browser.<br><br>  URL: http://0.0.0.0:8501</pre><p>Next, you must push the docker container to <a href="https://aws.amazon.com/getting-started/hands-on/deploy-docker-containers/">ECS</a>, and voila, you have the app live!</p><p>We will come back for the actual deployment of our app in a bit. But before that, let&#39;s build the app!</p><h3>Dataset</h3><p><a href="https://www.dropbox.com/scl/fo/mybc5v9s800orsu78b6ao/h?dl=0&amp;rlkey=1an4ndcscd5uw9yi7oxx8ypfn">The dataset</a> contains one-way <em>Expedia flights</em> between 2022–04–16 and 2022–10–05. The data comes in three formats: csv, gzip-compressed, and snappy-compressed parquet files. We will find information related to the starting and destination airport, travel duration, the ticket price, how many seats remain on the flight, the carrier, the type of cabin, and flight type: non-stop or refundable. The snappy-compressed parquet file is 8.85GB.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/998/1*mwPAOlmaCZ7Bi_rSra6xag.png" /></figure><p>🧵 More info on the dataset and its columns can be found <a href="https://github.com/dilwong/FlightPrices">here</a>.</p><h3>Setup</h3><p>💾 <a href="https://github.com/octavianzarzu/flight-prices-streamlit-app-1">Here</a> is the repo if you want to follow along.</p><p>I&#39;ll use <a href="https://code.visualstudio.com/docs/datascience/jupyter-notebooks">the jupyter notebook extension in VS Code</a>, but you can use any IDE you feel comfortable writing python code.</p><p>First, let&#39;s create a folder called flight-prices-streamlit-app and place the dataset file from the previous step inside (itineraries_snappy.parquet). We will create two additional files: 1. a .ipynb file for exploratory analysis and building the duckdb db, and 2. a .py file to run the streamlit app.</p><pre>flight-prices-streamlit-app/<br>- itineraries_snappy.parquet<br>- itineraries.ipynb<br>- app.py</pre><p>Open the itineraries.ipynb file. We will use this notebook to inspect the data we downloaded and insert only the required fields into a table. Next, let&#39;s install the dependencies and connect to a database.</p><pre>!pip install duckdb<br>!pip install streamlit<br><br>import duckdb<br>con = duckdb.connect(database=&#39;itineraries.duckdb&#39;) </pre><p>DuckDB, by default, connects to a transient in-memory database. In other words, when the process is terminated, the data loaded is flushed. Providing the database parameter to the connect method persists all further changes. Two new files will be created.</p><pre>flight-prices-streamlit-app/<br>- itineraries_snappy.parquet<br>- itineraries.ipynb<br>- app.py<br>- <strong>itineraries.duckdb</strong><br>- <strong>itineraries.duckdb.wal</strong></pre><h3>Loading the data</h3><p>🔍 First, let’s have a look at the data:</p><pre>preview_data_query = &quot;FROM &#39;itineraries_snappy.parquet&#39; LIMIT 5&quot;<br>con.execute(preview_data_query).df()</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/998/1*mwPAOlmaCZ7Bi_rSra6xag.png" /></figure><p>DuckDB allows a simpler syntax for data retrieval by skipping the SELECT keyword: the above syntax is equivalent to SELECT * FROM &#39;...&#39; LIMIT 5 .</p><p>Running a SELECT COUNT(*) returns more than 82 million rows.</p><p>We will run our analysis on direct flights, therefore let&#39;s create a table and filter the input data on isNonStop. We will also remove some of the columns. This won&#39;t affect the performance of the queries since DuckDB is a columnar database.</p><pre>load_query = &quot;&quot;&quot;<br>    CREATE OR REPLACE TABLE itineraries <br>    AS SELECT<br>        flightDate,<br>        startingAirport,<br>        destinationAirport,<br>        -- travelDuration, /* inferred from Arrival &amp; Departure */<br>        -- isBasicEconomy, /* not required for analysis */<br>        -- isRefundable, /* not required for analysis */<br>        -- isNonStop, /* applied as a filter on a single value */<br>        -- baseFare, /* not required for analysis */<br>        totalFare,<br>        seatsRemaining,<br>        segmentsAirlineName,<br>        segmentsArrivalTimeRaw,<br>        segmentsDepartureTimeRaw,<br>        segmentsCabinCode<br>    FROM &#39;itineraries_snappy.parquet&#39;<br>    WHERE isNonStop<br>&quot;&quot;&quot;<br><br>con.execute(load_query)</pre><p>The query execution time on my machine was 3.23 minutes. Running a SELECT COUNT(*) against the duckdb table returns a little over <strong>22M rows</strong>. The itineraries.duckdb file size is 496 MB.</p><p>Now that we have a sense of the data size, let’s start building our app.</p><h3>Building the app</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/513/1*u9YnHwqKBR7V7drEvKTmXA.png" /><figcaption>The app layout</figcaption></figure><h4>Filters section</h4><p>Open the app.py file. Let&#39;s import the necessary packages and connect to the itineraries duckdb database we created in read-only mode.</p><pre>import streamlit as st<br>import duckdb<br><br>con = duckdb.connect(database=&#39;itineraries.duckdb&#39;, read_only=True)<br><br>st.set_page_config(layout=&quot;wide&quot;)<br><br>st.title(&#39;Expedia flight data - Non Stop flights only&#39;)</pre><p>We are also setting the page layout wide and adding a title for preview.</p><p>Running the app from a terminal:</p><pre>$ streamlit run app.py<br><br><br>You can now view your Streamlit app in your browser.<br><br>Local URL: http://localhost:8501<br>Network URL: http://192.168.1.110:8501<br><br>2023-02-21 23:55:37.170 Uncaught app exception<br>Traceback (most recent call last):<br>    con = duckdb.connect(database=&#39;itineraries.duckdb&#39;, read_only=True)<br>duckdb.IOException: IO Error: Cannot open file &quot;D:\flight-prices-streamlit-app\itineraries.duckdb&quot;: The process cannot access the file because it is being used by another process.</pre><p>That is because DuckDB can support one read/write process or multiple read-only processes. Let&#39;s go back to itineraries.ipynb and restart the kernel — that should terminate the connection to the itineraries.duckdb. Refreshing the page in the browser should now clear the error and display the following:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*o1qb6WjoZPV_GeIChNIVuQ.png" /></figure><p>Great! Let&#39;s continue our app development in the app.py file by adding a quick container explaining what the app is about and dividing the next div into two columns. We will place into each column a select box for users to filter on the starting and destination airport.</p><p>Once we select the starting airport, the destination airport <strong>only displays</strong> the ones applicable for the starting airport according to the dataset.</p><pre>with st.expander(&#39;About the data&#39;):<br>    st.write(&#39;One-way flights data found on Expedia between 2022-04-16 and 2022-10-05. More info @ https://github.com/dilwong/FlightPrices&#39;)<br>    st.image(&#39;https://1000logos.net/wp-content/uploads/2021/11/Expedia-Logo.png&#39;, width=250)<br><br>st.subheader(&#39;Filters&#39;)<br><br>col_a1, col_a2 = st.columns(2)<br><br>with col_a1: <br>    startingAirports_df = con.execute(&quot;&quot;&quot;<br>        SELECT <br>            DISTINCT startingAirport <br>        FROM itineraries <br>        ORDER BY startingAirport<br>    &quot;&quot;&quot;).df()<br>    startingAirport = st.selectbox(&#39;Starting Airport&#39;, startingAirports_df)<br><br>with col_a2:<br>    destinationAirports_df = con.execute(&quot;&quot;&quot;<br>        SELECT <br>            DISTINCT destinationAirport <br>        FROM itineraries <br>        WHERE startingAirport = ? <br>        ORDER BY destinationAirport DESC<br>    &quot;&quot;&quot;, [startingAirport]).df()<br>    destinationAirport = st.selectbox(&#39;Destination Airport&#39;, destinationAirports_df)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*q6thh3oeydUSzmbBdTHP1w.png" /></figure><p>Let&#39;s add another one based on the two filters above so that users can control the Arrival time interval. We will use a slider. Thus, we need to pick the min and max from the segmentsArrivalTimeRaw columns.</p><pre>(ts_min, ts_max) = con.execute(&quot;&quot;&quot;<br>    SELECT <br>        min(segmentsArrivalTimeRaw::timestamp), <br>        max(segmentsArrivalTimeRaw::timestamp) <br>    FROM itineraries <br>    WHERE startingAirport = ? <br>        AND destinationAirport = ?<br>&quot;&quot;&quot;, [startingAirport, destinationAirport]).fetchone()<br><br>(slider_min, slider_max) = st.slider(<br>     &quot;Arrival time interval&quot;,<br>     min_value = ts_min,<br>     max_value = ts_max,<br>     value = (ts_min, ts_max),<br>     format=&quot;DD/MM/YY - hh:mm&quot;)<br><br>st.write(&quot;Start time:&quot;, slider_min, slider_max)</pre><p>value = (ts_min, ts_max) Specifies the default slider values — we&#39;d want to match the ts_min and ts_max. Finally, we are writing the two values from the slider to the app.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*ZMIWkHvi1ALo32wti-uuQQ.png" /></figure><p>Changing the destination airport from SFO to PHL, adjust the values for the slider instantly.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*_u62Nnsj6zS9q5TzOPwyPw.png" /></figure><p>To understand what I mean by instantly, I will add two timers to the beginning and end of the app and output it.</p><pre>from timeit import default_timer as timer<br><br>start_timer = timer()<br># all<br># the<br># code<br># above<br>end_timer = timer()<br><br>st.write(&quot;Total running time: &quot;, end_timer-start_timer, &quot; seconds&quot;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*r9oIyuljBy7VlY5zcItZ0A.png" /></figure><p>0.46s for three queries on 22M rows:</p><ol><li>Distinct values in column (A)</li><li>Distinct values on a different column (B) where A = ?</li><li>Computing min and max on a timestamp column where A = ? and B = ?</li></ol><p>Changing from ATL to CLT, we get again 0.4s:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*HplKP_zJ_tHdlkdCpKSqyA.png" /></figure><h4>Data preview section</h4><p>Let&#39;s execute two more queries to fetch the total number of rows based on the above filters and the top 10 rows for the selection.</p><pre>st.subheader(&#39;Data preview&#39;)<br><br>main_table_count = con.execute(&quot;&quot;&quot;<br>    SELECT COUNT(*) <br>    FROM itineraries <br>    WHERE startingAirport = ? <br>        AND destinationAirport = ? <br>        AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>    &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).fetchone()[0]<br><br>main_table_head = con.execute(&quot;&quot;&quot;<br>    SELECT * <br>    FROM itineraries <br>    WHERE startingAirport = ? <br>        AND destinationAirport = ? <br>        AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>    LIMIT 10<br>    &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).df()<br><br><br>st.write(&#39;Total number of rows: &#39;, main_table_count) <br>st.write(&#39;First 10 rows: &#39;, main_table_head)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*lSc42xDQkLKIdIwvg3JgLw.png" /></figure><p>The total running time went from 0.46 to 0.72 by adding the above two queries.</p><h4>Table aggregates section</h4><p>For this section, we will compute two aggregates:</p><ul><li>Average full fare by cabin type</li><li>Average duration by an airline company</li></ul><pre>with col_b1: <br>    avg_fare = con.execute(&quot;&quot;&quot;<br>        SELECT <br>            segmentsCabinCode, <br>            ROUND(AVG(totalFare),2) as avg_fare <br>        FROM itineraries <br>        WHERE startingAirport = ? <br>            AND destinationAirport = ? <br>            AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>        GROUP BY ALL <br>        ORDER BY 2<br>        &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).df()<br>    st.write(&#39;Average total fare by cabin type: &#39;, avg_fare)<br><br>with col_b2:<br>    avg_time = con.execute(&quot;&quot;&quot;<br>        SELECT <br>            segmentsAirlineName, <br>            AVG(DATE_DIFF(&#39;minute&#39;, segmentsDepartureTimeRaw::timestamp, segmentsArrivalTimeRaw::timestamp)) as avg_duration <br>        FROM itineraries <br>        WHERE startingAirport = ? <br>            AND destinationAirport = ? <br>            AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>        GROUP BY ALL <br>        ORDER BY 2<br>        &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).df()<br>    st.write(&#39;Average travel duration by airline company: &#39;, avg_time)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*4h1XjUhQprDBE00kyrQqag.png" /></figure><p>Total running time: 1.27 s</p><h3>📈Charts section</h3><p>We will use the altair package for the last section, so let&#39;s first import it.</p><pre>import altair as alt</pre><p>We are going to be looking at the following:</p><ul><li>price by date and carrier</li><li>price by remaining seats — if the price depends on the number of remaining available seats on the flight</li></ul><pre>st.subheader(&#39;Charts&#39;)<br><br>price_by_fareAndCarrier = con.execute(&quot;&quot;&quot;<br>    SELECT <br>        flightDate, <br>        segmentsAirlineName as carrier, <br>        avg(totalFare) as totalFare <br>    FROM itineraries <br>    WHERE startingAirport = ? <br>        AND destinationAirport = ? <br>        AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>    GROUP BY ALL<br>    &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).df()<br><br>chart = alt.Chart(price_by_fareAndCarrier).mark_circle().encode(<br>    x = &#39;flightDate&#39;,<br>    y = &#39;totalFare&#39;,<br>    color = &#39;carrier&#39;<br>).interactive()<br><br>st.altair_chart(chart, theme=&quot;streamlit&quot;, use_container_width=True)<br><br>seatsRemaining_df = con.execute(&quot;&quot;&quot;<br>    SELECT <br>        seatsRemaining, <br>        ROUND(AVG(totalFare),2) AS totalFare<br>    FROM itineraries <br>    WHERE startingAirport = ? <br>        AND destinationAirport = ? <br>        AND segmentsArrivalTimeRaw BETWEEN ? AND ? <br>    GROUP BY ALL<br>    &quot;&quot;&quot;, [startingAirport, destinationAirport, slider_min, slider_max]).df()<br><br>st.line_chart(data = seatsRemaining_df, x = &#39;seatsRemaining&#39;, y = &#39;totalFare&#39;)</pre><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*gm14NCwXLWWjaDz1WkuuZA.png" /></figure><p>The total running time: is 1.66 seconds — right under 2 seconds for all nine queries.</p><h3>Running queries on the .parquet file</h3><p>An alternative would be to run the queries directly on the .parquet file instead of materializing it as a duckdb table.</p><p>To do so, let&#39;s create a new database: itineraries_view.duckdb and instead of creating a table, let&#39;s use the same DDL but create a view:</p><p>itineraries.ipynb</p><pre>con = duckdb.connect(database=&#39;itineraries_view.duckdb&#39;)<br><br>load_query = &quot;&quot;&quot;<br>    CREATE VIEW itineraries <br>    AS SELECT<br>        flightDate,<br>        startingAirport,<br>        destinationAirport,<br>        travelDuration,<br>        isBasicEconomy,<br>        isRefundable,<br>        isNonStop,<br>        baseFare,<br>        totalFare,<br>        seatsRemaining,<br>        segmentsAirlineName,<br>        segmentsArrivalTimeRaw,<br>        segmentsDepartureTimeRaw,<br>        segmentsCabinCode<br>    FROM &#39;itineraries_snappy.parquet&#39;<br>    WHERE isNonStop<br>&quot;&quot;&quot;<br><br>con.execute(load_query)</pre><p>and we will only need to change now in the app.py :</p><pre>con = duckdb.connect(database=&#39;itineraries_view.duckdb&#39;, read_only=True)</pre><p>All queries against itineraries will be redirected to itineraries_view db <em>view </em>called itineraries</p><p>And the results… 🥁</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/361/1*y--nRCYTRYBYocl71IDftA.png" /></figure><h3>Deploying</h3><p>The above results show that we are better off querying a native duckdb table instead of the parquet file for our data app. These results also dictate our deployment strategy:</p><blockquote>If the performance of querying the parquet file had been similar, we could have uploaded the file to a blob storage, like S3, and use an in-memory duckdb database to connect to S3 and read the parquet file from the bucket.</blockquote><p>Instead, we need to store the data in a duckdb table. Unlike other databases to which we could <a href="https://docs.streamlit.io/knowledge-base/tutorials/databases">connect to it remotely via secrets</a>, in the case of SQLite or DuckDB, we can push the .duckdb file alongside the app code to the repo.</p><p>To store files &gt; 100 MB, we will need to use the Git Large File Storage extension.</p><p>First, let&#39;s create a requirements.txt based on the packages used in app.py</p><pre>streamlit<br>duckdb<br>altair</pre><p>and remove/move the itineraries_snappy.parquet file to a different location (or add to .gitingnore) since we don&#39;t want to push that also to git — the data is already ingested and loaded as part of the .duckdb file.</p><pre># initialize git<br>echo &quot;# flight-prices-streamlit-app&quot; &gt;&gt; README.md<br>git init<br><br># create repo on github.com and connect your local project to it<br><br># connect your local project folder to your empty repo on github<br>git remote add origin https://github.com/octavian-zarzu/flight-prices-streamlit-app.git<br><br># large file storage extension<br>git lfs install<br>git lfs track &quot;*.duckdb&quot;<br>git add .gitattributes<br><br>git status<br>git add .<br>git commit -m &quot;initial commit&quot;<br><br>git branch -M main<br><br># go to your repo settings -&gt; check on the checkbox &quot;Include Git LFS objects in archives&quot; <br><br># then push changes to github<br>git push -u origin main</pre><p>Now head to <a href="https://streamlit.io/cloud">https://streamlit.io/cloud</a>, sign in with your GitHub account, and click the new App. Select the repository you just created above, branch: main, main file path: app.py, and click on Deploy!</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/780/1*HY976VuVPz-YvQRwXFTUKw.png" /></figure><p>The app should be live in a few seconds after the requirements are installed. 🔥</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*TsNgrrbBs8J18081s_5tmg.png" /></figure><p>and the response time is still under 2 seconds for the entire app! 👟</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/374/1*9GZQfItBlCm3_emXedt5sw.png" /></figure><h3>Conclusions</h3><p>We’ve just built our first app with streamlit and DuckDB. Takeaways:</p><ul><li>You don’t need any prior Streamlit experience to build a data app. The code changes are intuitive and easy to embed in your Python file. If you want to learn more, you can do the <a href="https://share.streamlit.io/streamlit/30days">30 days Streamlit challenge</a>.</li><li>DuckDB is extremely fast. If you don’t need to update the database concurrently, <a href="https://duckdb.org/">give it a try</a>.</li><li>One improvement: The results/dataframes could have also been cached in Streamlit using the <a href="https://docs.streamlit.io/library/api-reference/performance/st.cache_data">cache_data decorator</a>, but there was no need, given the DuckDB performance.</li><li>Deploying a Streamlit app using the Streamlit Cloud only involves connecting your GitHub account. Pretty neat!</li><li>In our case, <em>reading from a native duckdb table</em> was much faster than <em>reading directly from the parquet file</em>. Once the performance of reading data from remote storage improves, the app&#39;s deployment strategy will allow for a separation of the application code and the database by leveraging an in-memory database and reading directly from the parquet file. Another downside of bundling the database file with the code is that LFS is bounded to 500 MB when pulling data . Therefore if your .duckdb file &gt; 500 MB, the streamlit app won’t load. If you know of a better way, please do share!</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=852cd31cccce" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>