Global Coffee Trade and Consumption
The United States Department of Agriculture tracks coffee production, trade, and consumption across 94 countries going back to 1960. Each country plays a different structural role in the global coffee supply chain, falling into one of three trade profiles: Pure Producer, Pure Consumer, or Trade Hub.
Use the USDA coffee data to classify each country into one of these profiles based on four metrics:
Production Self-Sufficiency Ratio: The share of supply grown domestically (PSR = Production / Total Supply).
Import Dependency Ratio: The share of supply coming from imports (IDR = Imports / Total Supply).
Export Intensity Ratio: The share of distributed coffee that leaves the country (EIR = Exports / Total Distribution).
Domestic Consumption Share: The share of distributed coffee consumed domestically (DCS = Domestic Consumption / Total Distribution).
Since coffee production is volatile due to weather and harvest cycles, focus on the last ten years (2017-2026) and classify based on 10-year averages to capture each country’s structural role rather than a single good or bad year.
Design and apply your own threshold rules for "high" and "low" (e.g., quartiles, fixed cutoffs, clustering), following the profile definitions below. Document your choice and visualize the classification results appropriately.
Pure Producer: Mainly grow their own coffee with little to no imports (high PSR / low IDR) and mainly export it with comparatively little domestic consumption (high EIR / low DCS).
Pure Consumer: Cannot grow coffee (or grow negligible amounts), so they mainly import coffee (high IDR / low PSR) and mainly consume the imported coffee domestically rather than re-exporting it (high DCS / low EIR).
Trade Hub: Have relatively high imports and exports compared to their total supply and distribution (high IDR and high EIR). They may or may not grow coffee itself but it is mainly defined by the sheer volume of coffee passing through the country.
Note: Not every country will fit a profile, there are countries that fall in between.
Dataset: Available from the USDA: https://apps.fas.usda.gov/psdonline/app/index.html#/app/downloads
---
Solution:
We start by reading in the USDA PSD coffee dataset using a CSV Reader node.
Preprocessing of the data: The raw data comes in a format where every attribute (production, imports, exports, domestic consumption, total supply, total distribution) is in its own row. A Pivot node converts these attributes into columns, so that each country/year combination becomes a single row with all attributes next to each other. A Row Filter node then filters the data to the last ten years (2017–2026) and removes countries with zero supply and years without data. Coffee production is highly volatile due to weather and harvest cycles. Focusing on the last ten years ensures you're capturing each country's structural role rather than a single good or bad year. A GroupBy node aggregates the remaining rows per country and calculates the ten-year average. Finally, a Column Renamer node cleans up the aggregation column names for clarity.
Calculating the metrics: An Expression node creates the four metrics from the averaged attributes:
Production Self-Sufficiency Ratio: PSR = Production / Total Supply
Import Dependency Ratio: IDR = Imports / Total Supply
Export Intensity Ratio: EIR = Exports / Total Distribution
Domestic Consumption Share: DCS = Domestic Consumption / Total Distribution
Assigning the profiles: For the "high"/"low" classification we decided on fixed cutoffs rather than quartiles, since the ratios are already normalized to a 0–1 range and the profiles are defined by clear structural thresholds rather than by how a country ranks against the others. A second Expression node applies the following rules in this order:
Pure Producer: PSR > 0.7 (more than 70% of available coffee comes from domestic growth) and EIR > 0.50 (more than 50% of distributed coffee is exported).
Pure Consumer: PSR < 0.05 (zero/almost zero production of coffee) and DCS > 0.50 (more than 50% of distributed coffee is consumed domestically).
Trade Hub: EIR > 0.5 (more than 50% of distributed coffee is exported) and IDR > 0.5 (more than 50% of available coffee comes from imports.
Unclassified: All countries that don’t fit any of the above. Some countries naturally fall in between, for example countries that grow a large share of their coffee but still consume most of it domestically.
The results are written into a new column (Profile), and the table goes into the component that builds the dashboard.
Building the interactive composite view: The composite view shows two parts: the top part summarizes how the countries are distributed across the profiles, whereas the bottom part allows for selecting a profile and lists the individual countries belonging to it.
Profile overview: A GroupBy node counts the number of countries per profile. A RowID node assigns the profile as the RowID, so that the counts can be addressed by profile name, and a Table Column to Variable node turns them into flow variables. A Variable Expression node then adds the colors and assembles the display strings, and four Text View nodes show the resulting counts for Pure Producers, Pure Consumers, Trade Hubs, and Unclassified, each in its own color.
Profile selection and country list: A Table Creator node lists the filter options (the three profiles plus “Unclassified”), which are fed into a Value Selection Widget node so that the user can pick a profile. The selected value is used as a flow variable in a Row Filter node to keep only the countries of the chosen profile. In parallel, a Table Row to Variable node extracts the selected profile and passes it on as the table title, so that the title updates according to the selection. The filtered table is displayed in a Table View node listing all countries of the chosen profile together with their four metrics.
Headers and layout: Lastly, one Text View node displays the header of the composite view, and four further Text View nodes act as dividers to visually separate the profile counters from each other and from the country table below.