<?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 Saisathya on Medium]]></title>
        <description><![CDATA[Stories by Saisathya on Medium]]></description>
        <link>https://medium.com/@saisathya3366?source=rss-6816a3561413------2</link>
        <image>
            <url>https://cdn-images-1.medium.com/fit/c/150/150/0*suq9QdnGKMSwJC87</url>
            <title>Stories by Saisathya on Medium</title>
            <link>https://medium.com/@saisathya3366?source=rss-6816a3561413------2</link>
        </image>
        <generator>Medium</generator>
        <lastBuildDate>Wed, 18 Nov 2026 16:49:29 GMT</lastBuildDate>
        <atom:link href="https://medium.com/@saisathya3366/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[Unveiling the Magic Behind the UEFA Champions League Draw: A Beginner’s Adventure into PySpark…]]></title>
            <link>https://saisathya3366.medium.com/unveiling-the-magic-behind-the-uefa-champions-league-draw-a-beginners-adventure-into-pyspark-7065d1180521?source=rss-6816a3561413------2</link>
            <guid isPermaLink="false">https://medium.com/p/7065d1180521</guid>
            <category><![CDATA[uefa-champions-league]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[football]]></category>
            <category><![CDATA[programming]]></category>
            <category><![CDATA[sports]]></category>
            <dc:creator><![CDATA[Saisathya]]></dc:creator>
            <pubDate>Sat, 26 Oct 2024 12:15:17 GMT</pubDate>
            <atom:updated>2024-10-26T12:15:17.464Z</atom:updated>
            <content:encoded><![CDATA[<h3>Unveiling the Magic Behind the UEFA Champions League Draw: A Beginner’s Adventure into PySpark Coding</h3><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*YPqyUGHTAt6WMm-iPKbpww.png" /><figcaption>UEFA Champions League Draw</figcaption></figure><p><em>Picture this: It’s the evening of the live UEFA Champions League draw. Football fans around the globe are glued to their screens, hearts racing as teams are paired up for the season ahead. But have you ever wondered what goes on behind the scenes? How do organizers ensure a fair, exciting, and balanced pairing of 36 elite teams? Buckle up, because we’re about to embark on a thrilling journey that blends the passion of football with the power of PySpark programming!</em></p><p><strong><em>“Curious to peek at the full code? Scroll down to the end, but stay with me for an exciting, step-by-step adventure that unveils every detail!”</em></strong></p><h4>Introduction: Where Football Passion Meets Software</h4><p>Whether you’re a die-hard football fan or someone who’s just discovered the beautiful game, the UEFA Champions League (UCL) stands as one of the pinnacles of sporting excellence. Now, imagine introducing 36 teams into a single league phase, replacing the traditional group stage. Sounds like a game-changer, right? It is! And to orchestrate this intricate dance of teams, UEFA employs sophisticated software powered by PySpark — a tool that’s as fascinating as the game itself.</p><p>But don’t worry if you’re new to both football and coding. This article is your backstage pass to understanding the UCL’s new format and the PySpark code that simulates the draw process. We’ll break it down piece by piece, ensuring that by the end, you’ll not only grasp the essentials but also feel the adrenaline of this computational masterpiece.</p><h3>Chapter 1: Understanding the UEFA Champions League and Its New Format</h3><h3>What is the UEFA Champions League?</h3><p>For the uninitiated, the UEFA Champions League is Europe’s premier club football tournament. It brings together the best teams from various European leagues, competing for the coveted title. Imagine the likes of Real Madrid, Bayern Munich, and Manchester City battling it out for glory — pure football magic!</p><h3>The Traditional vs. New Format</h3><p>Traditionally, the UCL featured 32 teams divided into eight groups of four. Each team played six matches in a double round-robin format — three at home and three away. The top two from each group advanced to the knockout stages, leading to an exhilarating finale.</p><p>But for the 2024/25 season, UEFA shook things up:</p><ul><li><strong>Expanded to 36 Teams:</strong> More clubs get a chance to showcase their prowess.</li><li><strong>Single League Phase:</strong> Gone are the group stages. Instead, all 36 teams enter a single league table, each playing eight matches.</li><li><strong>Dynamic Draw Process:</strong> A sophisticated draw mechanism ensures diverse and fair matchups.</li></ul><p>This overhaul aims to enhance competitiveness, increase revenue, and provide fans with more thrilling encounters.</p><h3>Why the New Format?</h3><p>Think of it as upgrading from a cozy neighborhood league to a bustling international festival. More teams mean more diversity, more rivalries, and more excitement. But with great power comes great responsibility — or in this case, the need for a robust system to manage the complexities of pairing 36 teams while adhering to specific rules.</p><h3>Chapter 2: The Heartbeat of the Draw — Decoding the PySpark Code</h3><p>Now that we’ve set the stage with football excitement, let’s dive into the tech magic that makes the UCL draw possible. We’ll explore a PySpark program designed to simulate the draw process, ensuring every team gets a fair and thrilling set of opponents.</p><p>Setting Up the Playground: Importing Libraries</p><pre>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import col<br>from pyspark.sql import functions as F<br>from pyspark.sql.types import IntegerType<br>from pyspark.sql.functions import udf, col<br>from pyspark.sql.types import StructType, StructField, StringType<br>import random</pre><p><strong>Why These Libraries?</strong></p><ul><li><strong>PySpark Modules:</strong> Essential for handling big data efficiently</li><li><strong>Random Module:</strong> Adds the element of chance, mimicking the unpredictability of real-life draws</li></ul><h3>Categorizing Teams into Pots: The Starting Line</h3><p>UEFA categorizes teams into four pots based on their rankings and qualifications. Let’s see how we represent this:</p><h4>Pot 1: The Elite</h4><pre>pot1_data = [<br>    (&#39;Real Madrid&#39;, &#39;ESP&#39;),<br>    (&#39;Manchester City&#39;, &#39;ENG&#39;),<br>    (&#39;Bayern Munich&#39;, &#39;GER&#39;),<br>    (&#39;Paris Saint-Germain&#39;, &#39;FRA&#39;),<br>    (&#39;Liverpool&#39;, &#39;ENG&#39;),<br>    (&#39;Inter&#39;, &#39;ITA&#39;),<br>    (&#39;Borussia Dortmund&#39;, &#39;GER&#39;),<br>    (&#39;RB Leipzig&#39;, &#39;GER&#39;),<br>    (&#39;Barcelona&#39;, &#39;ESP&#39;)<br>]</pre><p><strong>Why Pot 1?</strong></p><ul><li><strong>Top-Tier Teams:</strong> These are the heavyweights, including title holders and top-ranked clubs. Their performance sets the tone for the competition.</li></ul><h4>Pots 2 to 4: The Rising Stars</h4><p>Similarly, we define data for Pots 2, 3, and 4, each containing nine teams based on their UEFA Club coefficient rankings.</p><pre>pot2_data = [<br>    (&#39;Bayer Leverkusen&#39;, &#39;GER&#39;),<br>    (&#39;Atlético Madrid&#39;, &#39;ESP&#39;),<br>    # ... other teams<br>]<br><br>pot3_data = [<br>    (&#39;Feyenoord&#39;, &#39;NED&#39;),<br>    (&#39;Sporting CP&#39;, &#39;POR&#39;),<br>    # ... other teams<br>]<br><br>pot4_data = [<br>    (&#39;Slovan Bratislava&#39;, &#39;SVK&#39;),<br>    (&#39;Monaco&#39;, &#39;FRA&#39;),<br>    # ... other teams<br>]</pre><p><strong>The Logic Behind Pots:</strong></p><ul><li><strong>Balanced Competition:</strong> By categorizing teams, UEFA ensures a mix of strong and emerging teams, preventing any single pot from being overly dominant.</li></ul><h3>Combining Teams into a Unified DataFrame: The Assembly Line</h3><pre>teams_with_pot = \<br>    [(team, country, &#39;Pot 1&#39;) for team, country in pot1_data] + \<br>    [(team, country, &#39;Pot 2&#39;) for team, country in pot2_data] + \<br>    [(team, country, &#39;Pot 3&#39;) for team, country in pot3_data] + \<br>    [(team, country, &#39;Pot 4&#39;) for team, country in pot4_data]</pre><p><strong>Why Combine?</strong></p><ul><li><strong>Unified Data Structure:</strong> It simplifies data manipulation, allowing us to handle all teams collectively while retaining their pot information.</li></ul><h3>Defining the Schema and Creating the DataFrame: The Blueprint</h3><pre>schema = StructType([<br>    StructField(&quot;team&quot;, StringType(), True),<br>    StructField(&quot;country&quot;, StringType(), True),<br>    StructField(&quot;pot&quot;, StringType(), True)<br>])<br><br>teams_df = spark.createDataFrame(teams_with_pot, schema)</pre><p><strong>Breaking It Down:</strong></p><ul><li><strong>Schema:</strong> Defines the structure — each team has a name, country, and pot.</li><li><strong>DataFrame:</strong> A Spark DataFrame is a powerful structure that allows us to perform complex operations efficiently.</li></ul><h3>Organizing Data for the Draw: Setting the Stage</h3><pre>teams_list = [row[&#39;team&#39;] for row in teams_df.collect()]<br>countries = {row[&#39;team&#39;]: row[&#39;country&#39;] for row in teams_df.collect()}<br>pots = {row[&#39;team&#39;]: row[&#39;pot&#39;] for row in teams_df.collect()}<br>teams_in_pot = {}<br>for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>    teams_in_pot[pot] = [row[&#39;team&#39;] for row in teams_df.filter(col(&#39;pot&#39;) == pot).collect()]</pre><p><strong>What’s Happening?</strong></p><ul><li><strong>teams_list:</strong> A simple list of all team names.</li><li><strong>countries &amp; pots Dictionaries:</strong> Quick reference maps to fetch a team’s country and pot.</li><li><strong>teams_in_pot:</strong> Groups teams by their pots, aiding in the draw process.</li></ul><h3>Tracking Structures: Keeping Score Behind the Scenes</h3><pre>team_match_counts = {team: 0 for team in teams_list}<br>team_pot_match_counts = {team: {pot: 0 for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]} for team in teams_list}<br>team_opponents = {team: set() for team in teams_list}<br>team_home_away_pot_counts = {<br>    team: {<br>        pot: {&#39;Home&#39;: 0, &#39;Away&#39;: 0} for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]<br>    } for team in teams_list<br>}<br>team_total_home_matches = {team: 0 for team in teams_list}<br>team_total_away_matches = {team: 0 for team in teams_list}<br>assigned_matches = []<br>match_id_counter = 1</pre><p><strong>Why So Many Tracking Variables?</strong></p><ul><li><strong>Fairness &amp; Balance:</strong> To ensure each team gets a fair distribution of matches, we track:</li><li><strong>Total Matches:</strong> Each team plays exactly eight matches.</li><li><strong>Pot-Based Matches:</strong> Limit how many times a team faces opponents from the same pot.</li><li><strong>Home/Away Balance:</strong> No team should have more than four home or away matches.</li><li><strong>Unique Opponents:</strong> Prevent duplicate matchups.</li></ul><h3>Setting Up the Output DataFrame: The Trophy Case</h3><pre>columns = [&#39;match_id&#39;, &#39;team&#39;, &#39;team_country&#39;, &#39;team_pot&#39;, &#39;opponent&#39;, &#39;opponent_country&#39;, &#39;opponent_pot&#39;, &#39;home_away&#39;]<br>assigned_matches_df = spark.createDataFrame([], schema=StructType([<br>    StructField(&quot;match_id&quot;, StringType(), True),<br>    StructField(&quot;team&quot;, StringType(), True),<br>    StructField(&quot;team_country&quot;, StringType(), True),<br>    StructField(&quot;team_pot&quot;, StringType(), True),<br>    StructField(&quot;opponent&quot;, StringType(), True),<br>    StructField(&quot;opponent_country&quot;, StringType(), True),<br>    StructField(&quot;opponent_pot&quot;, StringType(), True),<br>    StructField(&quot;home_away&quot;, StringType(), True)<br>]))</pre><p><strong>Purpose:</strong></p><ul><li><strong>Final Matchups:</strong> This DataFrame will store all assigned matches with comprehensive details, ready to be showcased to fans worldwide.</li></ul><h3>Chapter 3: The Art of Matchmaking — Core Logic Explained</h3><h3>Helper Function: Converting Arrays to Strings</h3><pre>def array_to_string(col):<br>    return F.when(F.size(col) &gt; 0, F.concat_ws(&#39;, &#39;, F.array_distinct(col))).otherwise(&#39;&#39;)</pre><p><strong>Why This Function?</strong></p><ul><li><strong>Readability:</strong> Converts lists of opponents into comma-separated strings for a cleaner, more readable output.</li></ul><h3>Finding Potential Opponents: The Matchmaker’s Dilemma</h3><pre>def find_potential_opponents(team, target_pot, home_away):<br>    potentials = []<br>    team_country = countries[team]<br>    team_pot = pots[team]<br>    for opponent in teams_in_pot[target_pot]:<br>        if opponent == team:<br>            continue<br>        opponent_country = countries[opponent]<br>        opponent_pot = pots[opponent]<br>        if opponent_country == team_country:<br>            continue<br>        if team in team_opponents[opponent]:<br>            continue<br>        if team_pot_match_counts[opponent][team_pot] &gt;= 2:<br>            continue<br>        if team_pot_match_counts[team][target_pot] &gt;= 2:<br>            continue<br>        if team_match_counts[opponent] &gt;= 8:<br>            continue<br>        if home_away == &#39;Home&#39;:<br>            if team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] &gt;= 1:<br>                continue<br>            if team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] &gt;= 1:<br>                continue<br>            if team_total_home_matches[team] &gt;= 4:<br>                continue<br>            if team_total_away_matches[opponent] &gt;= 4:<br>                continue<br>        elif home_away == &#39;Away&#39;:<br>            if team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] &gt;= 1:<br>                continue<br>            if team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] &gt;= 1:<br>                continue<br>            if team_total_away_matches[team] &gt;= 4:<br>                continue<br>            if team_total_home_matches[opponent] &gt;= 4:<br>                continue<br>        else:<br>            continue<br>        potentials.append(opponent)<br>    return potentials</pre><p><strong>Breaking It Down:</strong></p><p><strong>Purpose:</strong> Identifies all eligible opponents for a given team from a specific pot, ensuring adherence to UEFA’s strict rules.</p><p><strong>Key Rules Enforced:</strong></p><ul><li><strong>No Same Country Matchups:</strong> Prevents teams from the same country from facing each other.</li><li><strong>No Repeat Matchups:</strong> Ensures teams don’t play against the same opponent more than once.</li><li><strong>Pot Match Limit:</strong> Limits a team to facing a maximum of two opponents from the same pot.</li><li><strong>Total Match Limit:</strong> Each team can only participate in eight matches.</li><li><strong>Home/Away Balance:</strong> Maintains a balance, ensuring no team has more than four home or away matches.</li></ul><h3>Assigning Matches: The Core Logic</h3><pre>def assign_matches_for_team(team):<br>    global match_id_counter<br>    team_pot = pots[team]<br>    print(f&quot;Assigning matches for {team}...&quot;)<br>    for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>        while team_pot_match_counts[team][target_pot] &lt; 2 and team_match_counts[team] &lt; 8:<br>            home_away_assigned = False<br>            if team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] == 0 and team_total_home_matches[team] &lt; 4:<br>                home_away = &#39;Home&#39;<br>                potentials = find_potential_opponents(team, target_pot, home_away)<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = team, opponent<br>                    home_away_assigned = True<br>            if not home_away_assigned and team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] == 0 and team_total_away_matches[team] &lt; 4:<br>                home_away = &#39;Away&#39;<br>                potentials = find_potential_opponents(team, target_pot, home_away)<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = opponent, team<br>                    home_away_assigned = True<br>            if not home_away_assigned:<br>                break<br>            match = (home_team, away_team)<br>            if match in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches] or \<br>               (match[1], match[0]) in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches]:<br>                continue<br>            assigned_matches.append({<br>                &#39;match_id&#39;: match_id_counter,<br>                &#39;home_team&#39;: home_team,<br>                &#39;away_team&#39;: away_team<br>            })<br>            match_id_counter += 1<br>            team_match_counts[team] += 1<br>            team_match_counts[opponent] += 1<br>            team_pot_match_counts[team][target_pot] += 1<br>            team_pot_match_counts[opponent][team_pot] += 1<br>            team_opponents[team].add(opponent)<br>            team_opponents[opponent].add(team)<br>            if home_away == &#39;Home&#39;:<br>                team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] += 1<br>                team_total_home_matches[team] += 1<br>                team_total_away_matches[opponent] += 1<br>            elif home_away == &#39;Away&#39;:<br>                team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] += 1<br>                team_total_away_matches[team] += 1<br>                team_total_home_matches[opponent] += 1</pre><p><strong>Why This Function Rocks:</strong></p><ul><li><strong>Iterative Assignment:</strong> Goes through each pot, attempting to assign up to two opponents per pot per team.</li><li><strong>Home/Away Decision:</strong> Prioritizes home matches, then assigns away matches if possible.</li><li><strong>Randomness with Control:</strong> Uses random.choice to mimic the unpredictability of real draws while adhering to constraints.</li><li><strong>Counter Updates:</strong> Keeps all tracking variables updated to maintain fairness and prevent rule violations.</li></ul><h3>Handling Edge Cases: Ensuring No Team is Left Behind</h3><pre>    if team_match_counts[team] &lt; 8:<br>        print(f&quot;{team} has only {team_match_counts[team]} matches. Attempting to assign remaining matches...&quot;)<br>        while team_match_counts[team] &lt; 8:<br>            home_away_assigned = False<br>            if team_total_home_matches[team] &lt; 4:<br>                home_away = &#39;Home&#39;<br>                potentials = []<br>                for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>                    potentials.extend(find_potential_opponents(team, target_pot, home_away))<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = team, opponent<br>                    home_away_assigned = True<br>            if not home_away_assigned and team_total_away_matches[team] &lt; 4:<br>                home_away = &#39;Away&#39;<br>                potentials = []<br>                for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>                    potentials.extend(find_potential_opponents(team, target_pot, home_away))<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = opponent, team<br>                    home_away_assigned = True<br>            if not home_away_assigned:<br>                print(f&quot;Unable to assign more matches to {team}.&quot;)<br>                break<br>            match = (home_team, away_team)<br>            if match in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches] or \<br>               (match[1], match[0]) in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches]:<br>                continue<br>            assigned_matches.append({<br>                &#39;match_id&#39;: match_id_counter,<br>                &#39;home_team&#39;: home_team,<br>                &#39;away_team&#39;: away_team<br>            })<br>            match_id_counter += 1<br>            team_match_counts[team] += 1<br>            team_match_counts[opponent] += 1<br>            opponent_pot = pots[opponent]<br>            team_pot_match_counts[team][opponent_pot] += 1<br>            team_pot_match_counts[opponent][team_pot] += 1<br>            team_opponents[team].add(opponent)<br>            team_opponents[opponent].add(team)<br>            if home_away == &#39;Home&#39;:<br>                team_home_away_pot_counts[team][opponent_pot][&#39;Home&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] += 1<br>                team_total_home_matches[team] += 1<br>                team_total_away_matches[opponent] += 1<br>            elif home_away == &#39;Away&#39;:<br>                team_home_away_pot_counts[team][opponent_pot][&#39;Away&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] += 1<br>                team_total_away_matches[team] += 1<br>                team_total_home_matches[opponent] += 1</pre><p><strong>What’s the Deal?</strong></p><ul><li><strong>Ensuring Completeness:</strong> Even after initial assignments, some teams might still have fewer than eight matches. This section diligently tries to fill those gaps.</li><li><strong>Flexibility:</strong> It broadens the search across all pots, increasing the chances of finding suitable opponents while still respecting all rules.</li></ul><h3>Finalizing Matches: Wrapping Up the Drama</h3><pre>    rows = []<br>    for match in assigned_matches:<br>        if match[&#39;home_team&#39;] == team or match[&#39;away_team&#39;] == team:<br>            match_id = match[&#39;match_id&#39;]<br>            home_team = match[&#39;home_team&#39;]<br>            away_team = match[&#39;away_team&#39;]<br>            rows.append({<br>                &#39;match_id&#39;: str(match_id),<br>                &#39;team&#39;: home_team,<br>                &#39;team_country&#39;: countries[home_team],<br>                &#39;team_pot&#39;: pots[home_team],<br>                &#39;opponent&#39;: away_team,<br>                &#39;opponent_country&#39;: countries[away_team],<br>                &#39;opponent_pot&#39;: pots[away_team],<br>                &#39;home_away&#39;: &#39;Home&#39;<br>            })<br>            rows.append({<br>                &#39;match_id&#39;: str(match_id),<br>                &#39;team&#39;: away_team,<br>                &#39;team_country&#39;: countries[away_team],<br>                &#39;team_pot&#39;: pots[away_team],<br>                &#39;opponent&#39;: home_team,<br>                &#39;opponent_country&#39;: countries[home_team],<br>                &#39;opponent_pot&#39;: pots[home_team],<br>                &#39;home_away&#39;: &#39;Away&#39;<br>            })<br>    team_matches_df = spark.createDataFrame(rows)<br>    global assigned_matches_df<br>    assigned_matches_df = assigned_matches_df.unionByName(team_matches_df).dropDuplicates([&#39;team&#39;, &#39;opponent&#39;, &#39;home_away&#39;])<br>    print(f&quot;Assigned matches for {team}.&quot;)</pre><p><strong>Why This Step?</strong></p><ul><li><strong>Dual Entries:</strong> Each match is recorded twice — once from the home team’s perspective and once from the away team’s.</li><li><strong>Deduplication:</strong> Ensures that each matchup is unique and appears correctly in the final DataFrame.</li></ul><h3>Organizing and Displaying Fixtures: The Grand Reveal</h3><pre>    all_fixtures = assigned_matches_df.groupBy(&#39;team&#39;).agg(<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 1&#39;) &amp; (assigned_matches_df.home_away == &#39;Home&#39;), assigned_matches_df.opponent))).alias(&#39;Pot1_Home&#39;),<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 1&#39;) &amp; (assigned_matches_df.home_away == &#39;Away&#39;), assigned_matches_df.opponent))).alias(&#39;Pot1_Away&#39;),<br>        # ... similar aggregations for Pots 2, 3, and 4<br>    )<br>    <br>    teams_in_order = [team for team, country in pot1_data + pot2_data + pot3_data + pot4_data]<br>    team_order_mapping = {team: index for index, team in enumerate(teams_in_order)}<br>    <br>    def get_team_order(team):<br>        return team_order_mapping.get(team, -1)<br>    <br>    get_team_order_udf = udf(get_team_order, IntegerType())<br>    <br>    all_fixtures = all_fixtures.withColumn(&#39;team_order&#39;, get_team_order_udf(col(&#39;team&#39;)))<br>    all_fixtures_ordered = all_fixtures.orderBy(&#39;team_order&#39;)<br>    all_fixtures_ordered = all_fixtures_ordered.drop(&#39;team_order&#39;)<br>    all_fixtures_ordered.display()</pre><p><strong>Final Touches:</strong></p><ul><li><strong>Grouping:</strong> Aggregates all matches for each team, categorizing them by pot and home/away status.</li><li><strong>Ordering:</strong> Ensures teams are displayed in the order they were originally defined, providing a clean and organized output.</li><li><strong>Display:</strong> Presents the finalized fixtures, ready for the global audience to digest.</li></ul><h3>Chapter 4: Bringing It All Together — The Full Picture</h3><h3>Why This Code Matters</h3><p>This PySpark program isn’t just a sequence of commands — it’s a meticulously crafted simulation of the UEFA Champions League draw. It ensures:</p><ol><li><strong>Fairness:</strong> Adheres strictly to UEFA’s rules, preventing any favoritism or bias.</li><li><strong>Diversity:</strong> Guarantees a wide range of matchups, enhancing the excitement of the league phase.</li><li><strong>Balance:</strong> Maintains a harmonious distribution of home and away games, crucial for team strategies and fan experiences.</li></ol><h3>How It Works in Practice</h3><p>Imagine the software as a master matchmaker, carefully pairing teams based on their rankings, past performances, and geographical considerations. It navigates through the complex web of rules, ensuring every team gets a fair shake while keeping the competition thrilling and unpredictable.</p><h3>Using This Simulator</h3><p>If you’re a budding programmer or a football enthusiast eager to dabble in coding, this simulator offers a hands-on experience:</p><ul><li><strong>Experiment:</strong> Modify team rankings, pot distributions, or rules to see how it affects the draw.</li><li><strong>Learn:</strong> Gain insights into data manipulation, conditional logic, and the power of PySpark in handling real-world scenarios.</li><li><strong>Engage:</strong> Bridge your passion for football with technical skills, opening doors to exciting interdisciplinary projects.</li></ul><h3><strong>FULL CODE :</strong></h3><pre>from pyspark.sql import SparkSession<br>from pyspark.sql.functions import col<br>from pyspark.sql import functions as F<br>from pyspark.sql.types import IntegerType<br>from pyspark.sql.functions import udf, col<br>from pyspark.sql.types import StructType, StructField, StringType<br>import random<br><br>spark = SparkSession.builder.appName(&quot;UEFA Draw with Function&quot;).getOrCreate()<br><br>pot1_data = [<br>    (&#39;Real Madrid&#39;, &#39;ESP&#39;),<br>    (&#39;Manchester City&#39;, &#39;ENG&#39;),<br>    (&#39;Bayern Munich&#39;, &#39;GER&#39;),<br>    (&#39;Paris Saint-Germain&#39;, &#39;FRA&#39;),<br>    (&#39;Liverpool&#39;, &#39;ENG&#39;),<br>    (&#39;Inter&#39;, &#39;ITA&#39;),<br>    (&#39;Borussia Dortmund&#39;, &#39;GER&#39;),<br>    (&#39;RB Leipzig&#39;, &#39;GER&#39;),<br>    (&#39;Barcelona&#39;, &#39;ESP&#39;)<br>]<br><br>pot2_data = [<br>    (&#39;Bayer Leverkusen&#39;, &#39;GER&#39;),<br>    (&#39;Atlético Madrid&#39;, &#39;ESP&#39;),<br>    (&#39;Atalanta&#39;, &#39;ITA&#39;),<br>    (&#39;Juventus&#39;, &#39;ITA&#39;),<br>    (&#39;Benfica&#39;, &#39;POR&#39;),<br>    (&#39;Arsenal&#39;, &#39;ENG&#39;),<br>    (&#39;Club Brugge&#39;, &#39;BEL&#39;),<br>    (&#39;Shakhtar Donetsk&#39;, &#39;UKR&#39;),<br>    (&#39;AC Milan&#39;, &#39;ITA&#39;)<br>]<br><br>pot3_data = [<br>    (&#39;Feyenoord&#39;, &#39;NED&#39;),<br>    (&#39;Sporting CP&#39;, &#39;POR&#39;),<br>    (&#39;PSV Eindhoven&#39;, &#39;NED&#39;),<br>    (&#39;Dinamo Zagreb&#39;, &#39;CRO&#39;),<br>    (&#39;Salzburg&#39;, &#39;AUT&#39;),<br>    (&#39;Lille&#39;, &#39;FRA&#39;),<br>    (&#39;Red Star Belgrade&#39;, &#39;SRB&#39;),<br>    (&#39;Young Boys&#39;, &#39;SUI&#39;),<br>    (&#39;Celtic&#39;, &#39;SCO&#39;)<br>]<br><br>pot4_data = [<br>    (&#39;Slovan Bratislava&#39;, &#39;SVK&#39;),<br>    (&#39;Monaco&#39;, &#39;FRA&#39;),<br>    (&#39;Sparta Prague&#39;, &#39;CZE&#39;),<br>    (&#39;Aston Villa&#39;, &#39;ENG&#39;),<br>    (&#39;Bologna&#39;, &#39;ITA&#39;),<br>    (&#39;Girona&#39;, &#39;ESP&#39;),<br>    (&#39;Stuttgart&#39;, &#39;GER&#39;),<br>    (&#39;Brest&#39;, &#39;FRA&#39;),<br>    (&#39;Sturm Graz&#39;, &#39;AUT&#39;)<br>]<br><br>teams_with_pot = \<br>    [(team, country, &#39;Pot 1&#39;) for team, country in pot1_data] + \<br>    [(team, country, &#39;Pot 2&#39;) for team, country in pot2_data] + \<br>    [(team, country, &#39;Pot 3&#39;) for team, country in pot3_data] + \<br>    [(team, country, &#39;Pot 4&#39;) for team, country in pot4_data]<br><br>schema = StructType([<br>    StructField(&quot;team&quot;, StringType(), True),<br>    StructField(&quot;country&quot;, StringType(), True),<br>    StructField(&quot;pot&quot;, StringType(), True)<br>])<br><br>teams_df = spark.createDataFrame(teams_with_pot, schema)<br><br>teams_list = [row[&#39;team&#39;] for row in teams_df.collect()]<br>countries = {row[&#39;team&#39;]: row[&#39;country&#39;] for row in teams_df.collect()}<br>pots = {row[&#39;team&#39;]: row[&#39;pot&#39;] for row in teams_df.collect()}<br>teams_in_pot = {}<br>for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>    teams_in_pot[pot] = [row[&#39;team&#39;] for row in teams_df.filter(col(&#39;pot&#39;) == pot).collect()]<br><br>team_match_counts = {team: 0 for team in teams_list}<br>team_pot_match_counts = {team: {pot: 0 for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]} for team in teams_list}<br>team_opponents = {team: set() for team in teams_list}<br>team_home_away_pot_counts = {<br>    team: {<br>        pot: {&#39;Home&#39;: 0, &#39;Away&#39;: 0} for pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]<br>    } for team in teams_list<br>}<br>team_total_home_matches = {team: 0 for team in teams_list}<br>team_total_away_matches = {team: 0 for team in teams_list}<br>assigned_matches = []<br>match_id_counter = 1<br><br>columns = [&#39;match_id&#39;, &#39;team&#39;, &#39;team_country&#39;, &#39;team_pot&#39;, &#39;opponent&#39;, &#39;opponent_country&#39;, &#39;opponent_pot&#39;, &#39;home_away&#39;]<br>assigned_matches_df = spark.createDataFrame([], schema=StructType([<br>    StructField(&quot;match_id&quot;, StringType(), True),<br>    StructField(&quot;team&quot;, StringType(), True),<br>    StructField(&quot;team_country&quot;, StringType(), True),<br>    StructField(&quot;team_pot&quot;, StringType(), True),<br>    StructField(&quot;opponent&quot;, StringType(), True),<br>    StructField(&quot;opponent_country&quot;, StringType(), True),<br>    StructField(&quot;opponent_pot&quot;, StringType(), True),<br>    StructField(&quot;home_away&quot;, StringType(), True)<br>]))<br><br>def array_to_string(col):<br>    return F.when(F.size(col) &gt; 0, F.concat_ws(&#39;, &#39;, F.array_distinct(col))).otherwise(&#39;&#39;)<br><br>def find_potential_opponents(team, target_pot, home_away):<br>    potentials = []<br>    team_country = countries[team]<br>    team_pot = pots[team]<br>    for opponent in teams_in_pot[target_pot]:<br>        if opponent == team:<br>            continue<br>        opponent_country = countries[opponent]<br>        opponent_pot = pots[opponent]<br>        if opponent_country == team_country:<br>            continue<br>        if team in team_opponents[opponent]:<br>            continue<br>        if team_pot_match_counts[opponent][team_pot] &gt;= 2:<br>            continue<br>        if team_pot_match_counts[team][target_pot] &gt;= 2:<br>            continue<br>        if team_match_counts[opponent] &gt;= 8:<br>            continue<br>        if home_away == &#39;Home&#39;:<br>            if team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] &gt;= 1:<br>                continue<br>            if team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] &gt;= 1:<br>                continue<br>            if team_total_home_matches[team] &gt;= 4:<br>                continue<br>            if team_total_away_matches[opponent] &gt;= 4:<br>                continue<br>        elif home_away == &#39;Away&#39;:<br>            if team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] &gt;= 1:<br>                continue<br>            if team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] &gt;= 1:<br>                continue<br>            if team_total_away_matches[team] &gt;= 4:<br>                continue<br>            if team_total_home_matches[opponent] &gt;= 4:<br>                continue<br>        else:<br>            continue<br>        potentials.append(opponent)<br>    return potentials<br><br>def assign_matches_for_team(team):<br>    global match_id_counter<br>    team_pot = pots[team]<br>    print(f&quot;Assigning matches for {team}...&quot;)<br>    for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>        while team_pot_match_counts[team][target_pot] &lt; 2 and team_match_counts[team] &lt; 8:<br>            home_away_assigned = False<br>            if team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] == 0 and team_total_home_matches[team] &lt; 4:<br>                home_away = &#39;Home&#39;<br>                potentials = find_potential_opponents(team, target_pot, home_away)<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = team, opponent<br>                    home_away_assigned = True<br>            if not home_away_assigned and team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] == 0 and team_total_away_matches[team] &lt; 4:<br>                home_away = &#39;Away&#39;<br>                potentials = find_potential_opponents(team, target_pot, home_away)<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = opponent, team<br>                    home_away_assigned = True<br>            if not home_away_assigned:<br>                break<br>            match = (home_team, away_team)<br>            if match in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches] or \<br>               (match[1], match[0]) in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches]:<br>                continue<br>            assigned_matches.append({<br>                &#39;match_id&#39;: match_id_counter,<br>                &#39;home_team&#39;: home_team,<br>                &#39;away_team&#39;: away_team<br>            })<br>            match_id_counter += 1<br>            team_match_counts[team] += 1<br>            team_match_counts[opponent] += 1<br>            team_pot_match_counts[team][target_pot] += 1<br>            team_pot_match_counts[opponent][team_pot] += 1<br>            team_opponents[team].add(opponent)<br>            team_opponents[opponent].add(team)<br>            if home_away == &#39;Home&#39;:<br>                team_home_away_pot_counts[team][target_pot][&#39;Home&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] += 1<br>                team_total_home_matches[team] += 1<br>                team_total_away_matches[opponent] += 1<br>            elif home_away == &#39;Away&#39;:<br>                team_home_away_pot_counts[team][target_pot][&#39;Away&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] += 1<br>                team_total_away_matches[team] += 1<br>                team_total_home_matches[opponent] += 1<br><br>    if team_match_counts[team] &lt; 8:<br>        print(f&quot;{team} has only {team_match_counts[team]} matches. Attempting to assign remaining matches...&quot;)<br>        while team_match_counts[team] &lt; 8:<br>            home_away_assigned = False<br>            if team_total_home_matches[team] &lt; 4:<br>                home_away = &#39;Home&#39;<br>                potentials = []<br>                for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>                    potentials.extend(find_potential_opponents(team, target_pot, home_away))<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = team, opponent<br>                    home_away_assigned = True<br>            if not home_away_assigned and team_total_away_matches[team] &lt; 4:<br>                home_away = &#39;Away&#39;<br>                potentials = []<br>                for target_pot in [&#39;Pot 1&#39;, &#39;Pot 2&#39;, &#39;Pot 3&#39;, &#39;Pot 4&#39;]:<br>                    potentials.extend(find_potential_opponents(team, target_pot, home_away))<br>                if potentials:<br>                    opponent = random.choice(potentials)<br>                    home_team, away_team = opponent, team<br>                    home_away_assigned = True<br>            if not home_away_assigned:<br>                print(f&quot;Unable to assign more matches to {team}.&quot;)<br>                break<br>            match = (home_team, away_team)<br>            if match in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches] or \<br>               (match[1], match[0]) in [(m[&#39;home_team&#39;], m[&#39;away_team&#39;]) for m in assigned_matches]:<br>                continue<br>            assigned_matches.append({<br>                &#39;match_id&#39;: match_id_counter,<br>                &#39;home_team&#39;: home_team,<br>                &#39;away_team&#39;: away_team<br>            })<br>            match_id_counter += 1<br>            team_match_counts[team] += 1<br>            team_match_counts[opponent] += 1<br>            opponent_pot = pots[opponent]<br>            team_pot_match_counts[team][opponent_pot] += 1<br>            team_pot_match_counts[opponent][team_pot] += 1<br>            team_opponents[team].add(opponent)<br>            team_opponents[opponent].add(team)<br>            if home_away == &#39;Home&#39;:<br>                team_home_away_pot_counts[team][opponent_pot][&#39;Home&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Away&#39;] += 1<br>                team_total_home_matches[team] += 1<br>                team_total_away_matches[opponent] += 1<br>            elif home_away == &#39;Away&#39;:<br>                team_home_away_pot_counts[team][opponent_pot][&#39;Away&#39;] += 1<br>                team_home_away_pot_counts[opponent][team_pot][&#39;Home&#39;] += 1<br>                team_total_away_matches[team] += 1<br>                team_total_home_matches[opponent] += 1<br><br>    rows = []<br>    for match in assigned_matches:<br>        if match[&#39;home_team&#39;] == team or match[&#39;away_team&#39;] == team:<br>            match_id = match[&#39;match_id&#39;]<br>            home_team = match[&#39;home_team&#39;]<br>            away_team = match[&#39;away_team&#39;]<br>            rows.append({<br>                &#39;match_id&#39;: str(match_id),<br>                &#39;team&#39;: home_team,<br>                &#39;team_country&#39;: countries[home_team],<br>                &#39;team_pot&#39;: pots[home_team],<br>                &#39;opponent&#39;: away_team,<br>                &#39;opponent_country&#39;: countries[away_team],<br>                &#39;opponent_pot&#39;: pots[away_team],<br>                &#39;home_away&#39;: &#39;Home&#39;<br>            })<br>            rows.append({<br>                &#39;match_id&#39;: str(match_id),<br>                &#39;team&#39;: away_team,<br>                &#39;team_country&#39;: countries[away_team],<br>                &#39;team_pot&#39;: pots[away_team],<br>                &#39;opponent&#39;: home_team,<br>                &#39;opponent_country&#39;: countries[home_team],<br>                &#39;opponent_pot&#39;: pots[home_team],<br>                &#39;home_away&#39;: &#39;Away&#39;<br>            })<br>    team_matches_df = spark.createDataFrame(rows)<br>    global assigned_matches_df<br>    assigned_matches_df = assigned_matches_df.unionByName(team_matches_df).dropDuplicates([&#39;team&#39;, &#39;opponent&#39;, &#39;home_away&#39;])<br>    print(f&quot;Assigned matches for {team}.&quot;)<br>    all_fixtures = assigned_matches_df.groupBy(&#39;team&#39;).agg(<br>        # Home and away for Pot 1<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 1&#39;) &amp; (assigned_matches_df.home_away == &#39;Home&#39;), assigned_matches_df.opponent))).alias(&#39;Pot1_Home&#39;),<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 1&#39;) &amp; (assigned_matches_df.home_away == &#39;Away&#39;), assigned_matches_df.opponent))).alias(&#39;Pot1_Away&#39;),<br>        # Home and away for Pot 2<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 2&#39;) &amp; (assigned_matches_df.home_away == &#39;Home&#39;), assigned_matches_df.opponent))).alias(&#39;Pot2_Home&#39;),<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 2&#39;) &amp; (assigned_matches_df.home_away == &#39;Away&#39;), assigned_matches_df.opponent))).alias(&#39;Pot2_Away&#39;),<br>        <br>        # Home and away for Pot 3<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 3&#39;) &amp; (assigned_matches_df.home_away == &#39;Home&#39;), assigned_matches_df.opponent))).alias(&#39;Pot3_Home&#39;),<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 3&#39;) &amp; (assigned_matches_df.home_away == &#39;Away&#39;), assigned_matches_df.opponent))).alias(&#39;Pot3_Away&#39;),<br>        <br>        # Home and away for Pot 4<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 4&#39;) &amp; (assigned_matches_df.home_away == &#39;Home&#39;), assigned_matches_df.opponent))).alias(&#39;Pot4_Home&#39;),<br>        array_to_string(F.collect_list(F.when((assigned_matches_df.opponent_pot == &#39;Pot 4&#39;) &amp; (assigned_matches_df.home_away == &#39;Away&#39;), assigned_matches_df.opponent))).alias(&#39;Pot4_Away&#39;)<br>    )<br><br>    teams_in_order = [team for team, country in pot1_data + pot2_data + pot3_data + pot4_data]<br>    team_order_mapping = {team: index for index, team in enumerate(teams_in_order)}<br><br>    def get_team_order(team):<br>        return team_order_mapping.get(team, -1)<br><br>    get_team_order_udf = udf(get_team_order, IntegerType())<br><br>    all_fixtures = all_fixtures.withColumn(&#39;team_order&#39;, get_team_order_udf(col(&#39;team&#39;)))<br>    all_fixtures_ordered = all_fixtures.orderBy(&#39;team_order&#39;)<br>    all_fixtures_ordered = all_fixtures_ordered.drop(&#39;team_order&#39;)<br>    all_fixtures_ordered.display()<br></pre><pre>assign_matches_for_team(&#39;Real Madrid&#39;)</pre><p>sample output:</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*uYslCIs2ZDBkxaoTN12t9g.png" /><figcaption>sample draw results for only 4 teams</figcaption></figure><p>REAL UEFA FIXTURES FOR POT 1 TEAMS FOR 2024–2025</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*DBGGutClyT5AdttYVDJ8nQ.png" /><figcaption>POT 1 FIXTURES</figcaption></figure><p>Link for the live match draw: <a href="https://www.youtube.com/watch?v=6oL1GSXQ1Nc">https://www.youtube.com/watch?v=6oL1GSXQ1Nc</a></p><p><em>for exact time to see the rules and the draw you can skip for first 24 mins</em></p><h3>Epilogue: The Symphony of Football and Code</h3><p>As the final whistle blows and the draw concludes, remember the silent symphony of data and logic that orchestrated this event. From categorizing teams into pots to assigning matches with unwavering fairness, the blend of football passion and PySpark prowess ensures that every season is as exhilarating as the last.</p><p>So next time you watch the Champions League, let the knowledge of this intricate dance between teams and code add a new layer of excitement. Whether you’re cheering for your favorite team or aspiring to create your own data-driven masterpieces, the magic of the UEFA Champions League draw is a testament to what passion and technology can achieve together.</p><p><em>Feel the goosebumps? That’s the thrill of understanding the beautiful game’s hidden mechanics — welcome to the world where football meets programming!</em></p><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=7065d1180521" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[The Story of Malmart’s Data Obsession]]></title>
            <link>https://saisathya3366.medium.com/the-story-of-malmarts-data-obsession-394c6d708c7a?source=rss-6816a3561413------2</link>
            <guid isPermaLink="false">https://medium.com/p/394c6d708c7a</guid>
            <category><![CDATA[retail]]></category>
            <category><![CDATA[data]]></category>
            <category><![CDATA[data-engineering]]></category>
            <category><![CDATA[case-study]]></category>
            <category><![CDATA[data-analysis]]></category>
            <dc:creator><![CDATA[Saisathya]]></dc:creator>
            <pubDate>Fri, 23 Feb 2024 10:46:42 GMT</pubDate>
            <atom:updated>2024-02-23T10:46:42.331Z</atom:updated>
            <content:encoded><![CDATA[<figure><img alt="" src="https://cdn-images-1.medium.com/max/1024/1*vxuMhhzwdbvmVYD2LH3h2A.jpeg" /><figcaption>illustrative image for Malmart</figcaption></figure><p>Lets imagine Malmart is an company for its early adoption of data analytics. They have massive amounts of data flowing in:</p><ul><li><strong>Transactions:</strong> Every item scanned at checkout across thousands of stores.</li><li><strong>Inventory:</strong> Real-time information on what’s on shelves, what’s in the backroom, and what’s on order.</li><li><strong>Online Activity:</strong> Website browsing, cart behavior, search terms used by customers.</li><li><strong>Supply Chain:</strong> Data on shipments, warehouse capacity, transportation routes.</li></ul><p><strong>The Data Team in Action</strong></p><ul><li><strong>Data Engineers:</strong> Their mission is to make sure all this data is collected reliably, cleaned, and accessible for analysis. They build massive data warehouses and the pipelines that feed into them.</li><li><strong>Data Analysts:</strong> These are the ‘detectives.’ They discover trends like surprising product combinations that are often bought together (the famous beer and diapers example!). They track localized demand and demographics that inform store-level stocking decisions.</li><li><strong>Data Scientists:</strong> They build the predictive engines. They forecast which products will be holiday hits, helping Malmart order accordingly months in advance. They model the impact of pricing changes or promotions across different regions.</li></ul><p><strong>Data-Driven Solutions</strong></p><ol><li><strong>Intelligent Inventory:</strong> Malmart’s systems analyze sales, seasonality, and even local weather to predict what each store needs. This minimizes stockouts while ensuring they don’t over-order items that might expire.</li><li><strong>Optimized Pricing:</strong> It’s not just about the lowest price. Data tells them the exact price points where certain products fly off the shelves. They can also run targeted promotions based on a customer’s shopping history.</li><li><strong>Perfect Placement:</strong> Stores are laid out strategically. Analyzing what people buy together, they might place seemingly unrelated items close by to encourage more sales (think chips and soda near the party supplies).</li><li><strong>Hyper-Efficient Supply Chain:</strong> Malmart uses its data to map out the most efficient delivery routes, pack trucks optimally, and even predict where they might need more warehouse space in the coming months.</li></ol><p><strong>The Impact</strong></p><ul><li><strong>Customer Satisfaction:</strong> Well-stocked shelves with the right products at the right prices make for happy shoppers.</li><li><strong>Reduced Costs:</strong> Less waste from unsold inventory and smarter logistics significantly boost Malmart’s bottom line.</li><li><strong>Competitive Edge:</strong> Data-driven insights enable Malmart to respond quickly to market changes and stay ahead of the curve.</li></ul><p><strong>Beyond the Basics</strong></p><p>Malmart continues to innovate with data. Here are some more cutting-edge things they might be working on:</p><ul><li><strong>Personalization:</strong> Tailored recommendations based on your buying habits, just like on Amazon.</li><li><strong>In-store Tracking:</strong> Understanding how customers are moving through the aisles could help them optimize store layouts even further.</li><li><strong>Predictive Maintenance:</strong> Analyzing sensor data from store equipment to prevent breakdowns before they happen, saving costs and minimizing disruptions.</li></ul><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=394c6d708c7a" width="1" height="1" alt="">]]></content:encoded>
        </item>
        <item>
            <title><![CDATA[Converting T-SQL Queries to PySpark: Examples and Tips-Part 1]]></title>
            <link>https://saisathya3366.medium.com/converting-t-sql-queries-to-pyspark-examples-and-tips-part-1-c18f0576a402?source=rss-6816a3561413------2</link>
            <guid isPermaLink="false">https://medium.com/p/c18f0576a402</guid>
            <category><![CDATA[sqltopyspark]]></category>
            <category><![CDATA[t-sql]]></category>
            <category><![CDATA[databricks]]></category>
            <category><![CDATA[pyspark]]></category>
            <category><![CDATA[data-engineering]]></category>
            <dc:creator><![CDATA[Saisathya]]></dc:creator>
            <pubDate>Wed, 21 Jun 2023 14:22:01 GMT</pubDate>
            <atom:updated>2023-06-21T14:22:01.177Z</atom:updated>
            <content:encoded><![CDATA[<p>Gendral tips and a series of examples to help you approach these tasks effectively.</p><figure><img alt="" src="https://cdn-images-1.medium.com/max/269/1*4zb3m6u3PFau5oaz52hZrg.jpeg" /><figcaption>pyspark</figcaption></figure><h3>Tips for Converting T-SQL Queries to PySpark</h3><ol><li><strong>Understand the Data Model</strong>: Familiarize yourself with the database schema and understand the relationships between tables. This will help you translate T-SQL joins and conditions into PySpark equivalents.</li><li><strong>Start with Small Queries</strong>: Begin by working on smaller T-SQL queries and gradually move on to more complex ones. This will allow you to experiment with different approaches and techniques, gaining confidence and improving your PySpark skills.</li><li><strong>Reference PySpark Documentation</strong>: PySpark has comprehensive documentation available that covers the various functions, operations, and concepts. Make use of this valuable resource as you navigate through different scenarios and challenges.</li><li><strong>Leverage Online Resources</strong>: There are several online resources, tutorials, and forums where you can find examples, tips, and solutions to common PySpark challenges. Some popular websites include the official Apache Spark documentation, Stack Overflow, and the Databricks community forum.</li><li><strong>Practice and Experiment:</strong> The key to mastering PySpark is practice. Work on various examples, try out different techniques, and explore the PySpark API. The more you practice, the better you will become at transforming T-SQL queries into PySpark code.</li></ol><blockquote>before getting started It’s important to note that these examples assume you have a basic understanding of PySpark and have already set up the necessary SparkSession and JDBC connection to your SQL Server.</blockquote><blockquote>Remember to customize the connection parameters, column names, and conditions based on your specific database schema and requirements.<em>.</em></blockquote><h3>Examples of T-SQL Queries and Their PySpark Equivalents</h3><h3>1.</h3><p><strong>T-SQL Example: Simple SELECT Query</strong></p><pre>SELECT * FROM customers;</pre><p><strong>PySpark Equivalent:</strong></p><pre>from pyspark.sql import SparkSession<br><br>spark = SparkSession.builder.getOrCreate()<br><br># Load the data into a PySpark DataFrame<br>customers_df = spark.read.format(&quot;jdbc&quot;).options(<br>    url=&quot;jdbc:sqlserver://localhost:1433;databaseName=mydatabase&quot;,<br>    driver=&quot;com.microsoft.sqlserver.jdbc.SQLServerDriver&quot;,<br>    dbtable=&quot;customers&quot;,<br>    user=&quot;username&quot;,<br>    password=&quot;password&quot;<br>).load()<br><br># Display the result<br>customers_df.show()</pre><p><strong>Simple SELECT Query:</strong></p><ul><li>T-SQL: The SELECT statement is used to retrieve data from one or more tables.</li><li>PySpark: In PySpark, you can use the select() function to specify the columns you want to include in the result. The show() function is used to display the result.</li></ul><h3>2.</h3><p><strong>T-SQL Example: Filtering with WHERE Clause</strong></p><pre>SELECT * FROM orders WHERE order_date &gt;= &#39;2022-01-01&#39;;</pre><p><strong>PySpark Equivalent:</strong></p><pre># Perform the equivalent PySpark operation<br>filtered_orders_df = orders_df.filter(col(&quot;order_date&quot;) &gt;= &quot;2022-01-01&quot;)<br><br># Display the result<br>filtered_orders_df.show()</pre><p><strong>Filtering with WHERE Clause:</strong></p><ul><li>T-SQL: The WHERE clause is used to filter rows based on specified conditions.</li><li>PySpark: In PySpark, you can use the filter() function or the where() function to apply conditions and filter the DataFrame based on those conditions.</li></ul><h3>3.</h3><p><strong>T-SQL Example: Joins</strong></p><pre>SELECT o.order_id, o.customer_id, c.customer_name<br>FROM orders o<br>JOIN customers c ON o.customer_id = c.customer_id;</pre><p><strong>PySpark Equivalent:</strong></p><pre># Perform the equivalent PySpark join operation<br>joined_df = orders_df.join(customers_df, orders_df.customer_id == customers_df.customer_id) \<br>    .select(orders_df.order_id, orders_df.customer_id, customers_df.customer_name)<br><br># Display the result<br>joined_df.show()</pre><p><strong>Joins</strong>:</p><ul><li>T-SQL: Joins are used to combine rows from different tables based on related columns.</li><li>PySpark: PySpark provides the join() function to perform joins between DataFrames. You specify the join condition using the equality operator (==), and you can select the desired columns using the select() function.</li></ul><h3>4.</h3><p><strong>T-SQL example: Order by</strong></p><pre>SELECT customer_id, first_name, last_name<br>FROM customers<br>WHERE age &gt;= 18<br>ORDER BY last_name ASC;</pre><p><strong>PySpark Equivalent:</strong></p><pre># Perform the equivalent PySpark operations<br>filtered_sorted_customers_df = customers_df.select(&quot;customer_id&quot;, &quot;first_name&quot;, &quot;last_name&quot;) \<br>    .filter(customers_df.age &gt;= 18) \<br>    .orderBy(col(&quot;last_name&quot;).asc())<br><br># Display the result<br>filtered_sorted_customers_df.show()</pre><p><strong>Ordering and Sorting:</strong></p><ul><li>T-SQL: The ORDER BY clause is used to sort the result set based on specified columns.</li><li>PySpark: PySpark provides the orderBy() function to sort the DataFrame based on one or more columns. You can specify ascending or descending order using the asc() and desc() functions.</li></ul><h3>5.</h3><p><strong>T-SQL example: Grouping and filtering with a having clause</strong></p><pre>SELECT category, COUNT(*) as count<br>FROM products<br>GROUP BY category<br>HAVING COUNT(*) &gt; 10;</pre><p><strong>PySpark Equivalent:</strong></p><pre># Perform the equivalent PySpark operations<br>aggregated_filtered_products_df = products_df.groupBy(&quot;category&quot;) \<br>    .agg(count(&quot;*&quot;).alias(&quot;count&quot;)) \<br>    .filter(col(&quot;count&quot;) &gt; 10)<br><br># Display the result<br>aggregated_filtered_products_df.show()</pre><p><strong>HAVING Clause:</strong></p><ul><li>T-SQL: The HAVING clause is used in combination with the GROUP BY clause to filter the grouped rows based on specified conditions. It operates on aggregated values.</li><li>PySpark: PySpark provides the groupBy() function to group the DataFrame based on specified columns. However, the HAVING clause is not directly available in PySpark. Instead, you can use the filter() function or a combination of groupBy() and agg() functions to achieve similar results.</li></ul><blockquote>The examples provided in this article serve as a starting point for your journey into the world of PySpark data engineering.</blockquote><blockquote>Remember, learning PySpark is an iterative process, so keep exploring, asking questions, and expanding your knowledge. Happy coding!</blockquote><img src="https://medium.com/_/stat?event=post.clientViewed&referrerSource=full_rss&postId=c18f0576a402" width="1" height="1" alt="">]]></content:encoded>
        </item>
    </channel>
</rss>