Icon

Sep2026-Coffee

<p><strong>KNIME SEPTEMBER 2026 Monthly Challenge</strong></p><p>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: <strong>Pure Producer</strong>, <strong>Pure Consumer</strong>, or <strong>Trade Hub</strong>.</p><p>Use the USDA coffee data to classify each country into one of these profiles using four metrics:</p><ul><li><p><strong>Production Self-Sufficiency Ratio:</strong> The share of supply grown domestically (PSR = Production / Total Supply).</p></li><li><p><strong>Import Dependency Ratio:</strong> The share of supply coming from imports (IDR = Imports / Total Supply).</p></li><li><p><strong>Export Intensity Ratio:</strong> The share of distributed coffee that leaves the country (EIR = Exports / Total Distribution).</p></li><li><p><strong>Domestic Consumption Share:</strong> The share of distributed coffee consumed domestically (DCS = Domestic Consumption / Total Distribution).</p></li></ul><p>Since coffee production is volatile due to weather and harvest cycles, focus on the <strong>last ten years</strong> (2017-2026) and classify based on 10-year averages to capture each country’s structural role rather than a single good or bad year.</p><p>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.&nbsp;</p><ul><li><p><strong>Pure Producer:</strong> 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).</p></li><li><p><strong>Pure Consumer:</strong> 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).</p></li><li><p><strong>Trade Hub:</strong> 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.</p></li></ul><p><strong><em>Note:</em></strong> Not every country will fit a profile, there are countries that fall in between.</p><p><strong>Author:</strong> Elisabeth Richter</p><p><strong>Dataset:</strong> Available from the USDA </p>

KNIME SEPTEMBER 2026 Monthly Challenge

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 using 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.

Author: Elisabeth Richter

Dataset: Available from the USDA

Global COFFEE Trade and Consumption

September 2026 Monthly Challenge

USDA Foreign Agricultural Service provides data on the production, supply and distribution of various commodities. This workflow analyses the trading profiles of 94 Countries from 2017 to 2026.

The workflow uses K-Means algorithm for clustering countries into 4 profiles:

  • Pure Producer: Countries that rely mainly on domestic coffee production, import very little, and export a large share of the coffee they distribute. Domestic consumption is relatively low.

  • Pure Consumer: Countries with little or no domestic coffee production that depend heavily on imports and consume most of the coffee domestically rather than re-exporting it.

  • Hub Trade: Countries with both high import dependency and high export intensity. Large volumes of coffee pass through these countries, regardless of whether they produce coffee themselves.

  • Unclassified: Countries that do not exhibit a sufficiently strong structural pattern to align with the Pure Producer, Pure Consumer, or Trade Hub profiles.

You can find the dataset here.

Extract & Prepare

To focus on each country’s recent structural role in the coffee market, the analysis uses only the last ten years of data, from 2017 to 2026. Records with Total Supply = 0 were removed to avoid invalid ratio calculations.

For each country, four structural ratios were calculated:

  • 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

Ten-year averages were then calculated for each ratio.

Finally, three profile scores were created to summarize how strongly each country matches the three target profiles:

  • Producer Score = min(PSR, EIR)

  • Consumer Score = min(IDR, DCS)

  • Hub Score = min(IDR, EIR)

Using the minimum ensures that a country scores highly only when both defining characteristics of the profile are strong.

Clustering & Profile Assignment

The four 10-year average ratios — PSR, IDR, EIR, and DCS — were used as inputs to a K-Means model with four clusters.

The clusters were then mapped to Pure Producer, Pure Consumer, Trade Hub, and Unclassified based on the highest average profile scores within each cluster.

Cluster IDs were stored as flow variables and automatically replaced with the final profile names.

Profile View

USDA Coffee Production, Supply & Distribution report
CSV Reader
Pick max of each score
GroupBy
Removing countries with 0 Supply
Row Filter
Put max score in variable
Table Row to Variable
Filtering market year > 2017and Attributes required for calculations
Rule-based Row Filter
Map profile to max score
Rule Engine
Pivot
Calculating Ratios
Expression
Pure Producer Cluster
k-Means
Joiner
Component
Calculates mean scores for each cluster
GroupBy

Nodes

Extensions

Links

Download

To use this workflow in KNIME, download it from the below URL and open it in KNIME:

Download Workflow
Created by: guyla
Created at: 2026-09-11
On NodePit since: 2026-09-14
Last update: 2026-09-16
Created with KNIME version: v5.12.0

Deploy, schedule, execute, and monitor your KNIME workflows locally, in the cloud or on-premises – with our brand new NodePit Runner.

Try NodePit Runner!