Exporter group branches

View or edit on GitHub

This page is synchronized from trase/models/brazil/soy/exporter_group_branches.ipynb. Last modified on 2025-12-14 23:19 CET by Trase Admin. Please view or edit the original file there; changes should be reflected here after a midnight build (CET time), or manually triggering it with a GitHub action (link).

Brazil Soy Exporter Group Branches

This notebooks aims to show how the supply chains of the five most important exporters in Brazil break down into the branches of our model, over time.

import pandas as pd

from trase.tools import CNX

df = pd.read_sql(
    """
    -- Filter data to 2015 onwards, excluding domestic and unknown
    with filtered_data as (
        select 
            *, 
            case 
                when decision_tree in ('UNKNOWN', 'DOMESTIC') then decision_tree 
                else substr(decision_tree, 0, 2) 
            end as major_branch
        from supply_chains_datasets.brazil_soy_v2_6_1
        where year >= 2015 and exporter_group != 'UNKNOWN' and decision_tree != 'DOMESTIC'
    ),

    -- Get total export volumes per exporter group
    exporter_group_volumes as (
        select exporter_group, sum(volume) as volume
        from filtered_data
        group by exporter_group
    ),

    -- Identify top five exporter groups
    top_exporter_groups as (
        select exporter_group
        from exporter_group_volumes
        order by volume desc
        limit 5
    ),

    -- Get yearly export volumes per exporter group and branch
    yearly_branch_volumes as (
        select 
            year, 
            exporter_group, 
            major_branch, 
            sum(volume) as volume
        from filtered_data
        group by year, exporter_group, major_branch
    ),

    -- Get yearly total export volumes per exporter group
    yearly_group_volumes as (
        select 
            year, 
            exporter_group, 
            sum(volume) as volume
        from filtered_data
        group by year, exporter_group
    )

    -- Calculate branch percentage of yearly exports per exporter group
    select 
        ybv.year,
        ybv.exporter_group,
        ybv.major_branch,
        100 * ybv.volume / ygv.volume as percentage
    from yearly_branch_volumes ybv
    join yearly_group_volumes ygv using (year, exporter_group)
    join top_exporter_groups tg using (exporter_group);
    """,
    CNX.cnx,
)

First, let's look at each exporter group in turn, and see how their supply chain has changed over time

import matplotlib.pyplot as plt

# Pivot data for area plotting
pivoted = df.pivot(
    index="year", columns=["exporter_group", "major_branch"], values="percentage"
)

# Prepare the figure
exporter_groups = df["exporter_group"].unique()
fig, axes = plt.subplots(
    nrows=len(exporter_groups),
    ncols=1,
    figsize=(10, 6 * len(exporter_groups)),
    sharex=True,
)

# Create an area chart for each exporter group
for ax, group in zip(axes, exporter_groups):
    group_data = pivoted[group]
    group_data.plot.area(ax=ax, stacked=True, alpha=0.8, cmap="Set2")
    ax.set_title(f"Exporter Group: {group}")
    ax.set_ylabel("Percentage (%)")
    ax.set_xlabel("Year")
    ax.legend(title="Major Branch")
    ax.grid(axis="y", linestyle="--", alpha=0.7)

plt.tight_layout()
plt.show()

Now let's pick just the latest year and see how the exporter groups compare

# Filter to the latest year
latest_year = df["year"].max()
latest_year_data = df[df["year"] == latest_year]

# Pivot data for stacked bar chart
pivoted = latest_year_data.pivot(
    index="exporter_group", columns="major_branch", values="percentage"
)

# Plot stacked bar chart
fig, ax = plt.subplots(figsize=(10, 6))

# Plot each column (major branch) as a stack
pivoted.plot(kind="bar", stacked=True, ax=ax, colormap="Set2", alpha=0.9)

# Customize plot
ax.set_title(f"Stacked Bar Chart for {latest_year}")
ax.set_xlabel("Exporter Group")
ax.set_ylabel("Percentage (%)")
ax.legend(title="Major Branch")
ax.grid(axis="y", linestyle="--", alpha=0.7)

plt.tight_layout()
plt.show()