The avatar for @danielgavrilov
Daniel Gavrilov
Published
Edited
Aug 5, 2022
1
Insert cell
Insert cell
Insert cell
Insert cell
duckdb
SELECT
COALESCE(
qcl.path_in_schema,
fbsc.path_in_schema,
scl.path_in_schema
) AS path_in_schema,
qcl.type AS qcl_type,
fbsc.type AS fbsc_type,
scl.type AS scl_type,
FROM parquet_metadata('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet') AS qcl
FULL JOIN parquet_metadata('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet') AS fbsc ON fbsc.path_in_schema = qcl.path_in_schema
FULL JOIN parquet_metadata('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet') AS scl ON scl.path_in_schema = qcl.path_in_schema
Insert cell
Insert cell
duckdb
SELECT
qcl.item_code AS qcl_item_code,
fbsc.item_code AS fbsc_item_code,
scl.item_code AS scl_item_code,
scl.cpc_code AS scl_cpc_code,
COALESCE(fbsc.fao_item, qcl.fao_item, scl.fao_item) AS fao_item,
qcl.fao_item IS NOT NULL AS qcl,
fbsc.fao_item IS NOT NULL AS fbsc,
scl.fao_item IS NOT NULL AS scl,
qcl.item_description AS qcl_description,
fbsc.item_description AS fbsc_description,
scl.item_description AS scl_description,
FROM (
SELECT DISTINCT item_code, fao_item, item_description
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet')
) AS fbsc
FULL JOIN (
SELECT DISTINCT item_code, fao_item, item_description
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet')
) AS qcl ON qcl.fao_item = fbsc.fao_item
FULL JOIN (
SELECT DISTINCT
scl_item_codes.item_code::string AS item_code,
scl.item_code as cpc_code,
fao_item,
COALESCE(NULLIF(item_description, ''), scl_item_codes.description) AS item_description
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet') AS scl
-- NOTE: We have to join `scl_item_codes` because the original table uses CPC codes.
LEFT JOIN scl_item_codes
ON scl_item_codes.cpc_code = scl.item_code
) AS scl ON scl.fao_item = fbsc.fao_item OR scl.fao_item = qcl.fao_item
ORDER BY fao_item ASC
Insert cell
Insert cell
duckdb
SELECT DISTINCT
scl_item_codes.item_code::string AS item_code,
scl.item_code as cpc_code,
fao_item,
scl_item_codes.description
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet') AS scl
-- NOTE: We have to join `scl_item_codes` because the original table uses CPC codes.
JOIN scl_item_codes
ON scl_item_codes.cpc_code = scl.item_code
Insert cell
Insert cell
Insert cell
duckdb
SELECT
COALESCE(fbsc.fao_element, qcl.fao_element, scl.fao_element) AS fao_element,
COALESCE(fbsc.unit, qcl.unit, scl.unit) AS unit,
qcl.fao_element IS NOT NULL AS qcl,
fbsc.fao_element IS NOT NULL AS fbsc,
scl.fao_element IS NOT NULL AS scl
FROM (
SELECT DISTINCT fao_element, unit
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet')
) AS fbsc
FULL JOIN (
SELECT DISTINCT fao_element, unit
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet')
) AS qcl ON qcl.fao_element = fbsc.fao_element AND qcl.unit = fbsc.unit
FULL JOIN (
SELECT DISTINCT fao_element, unit
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet')
) AS scl ON (scl.fao_element = fbsc.fao_element AND scl.unit = fbsc.unit) OR (scl.fao_element = qcl.fao_element AND scl.unit = qcl.unit)
ORDER BY fao_element ASC
Insert cell
Insert cell
Insert cell
duckdb
SELECT
COALESCE(fbsc.year, qcl.year, scl.year) AS year,
qcl.year IS NOT NULL AS qcl,
fbsc.year IS NOT NULL AS fbsc,
scl.year IS NOT NULL AS scl
FROM (
SELECT DISTINCT year
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet')
) AS fbsc
FULL JOIN (
SELECT DISTINCT year
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet')
) AS qcl ON qcl.year = fbsc.year
FULL JOIN (
SELECT DISTINCT year
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet')
) AS scl ON scl.year = fbsc.year OR scl.year = qcl.year
ORDER BY year ASC
Insert cell
Insert cell
Insert cell
duckdb
SELECT
COALESCE(fbsc.country, qcl.country, scl.country) AS country,
qcl.country IS NOT NULL AS qcl,
fbsc.country IS NOT NULL AS fbsc,
scl.country IS NOT NULL AS scl
FROM (
SELECT DISTINCT country
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet')
) AS fbsc
FULL JOIN (
SELECT DISTINCT country
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet')
) AS qcl ON qcl.country = fbsc.country
FULL JOIN (
SELECT DISTINCT country
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_scl/faostat_scl.parquet')
) AS scl ON scl.country = fbsc.country OR scl.country = qcl.country
ORDER BY country ASC
Insert cell
Insert cell
Insert cell
duckdb
SELECT DISTINCT fao_element, unit
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_qcl/faostat_qcl.parquet')
Insert cell
duckdb
SELECT
DISTINCT fao_country, fao_item
-- fao_item, fao_country, fao_element, value, unit
FROM read_parquet('https://owid-catalog.nyc3.digitaloceanspaces.com/garden/faostat/2022-05-17/faostat_fbsc/faostat_fbsc.parquet')
WHERE year = 2019 AND fao_element = 'Stock Variation'
AND fao_item IN ('Sweet potatoes', 'Yams', 'Sugar cane', 'Sugar beet', 'Sunflower seed', 'Cottonseed', 'Sesame seed', 'Palm kernels', 'Bananas', 'Dates', 'Cloves', 'Wine', 'Sugar non-centrifugal', 'Infant food')
AND value > 0
Insert cell
Insert cell
scl_item_codes = (await FileAttachment("scl_item_codes.csv").csv()).map(
(row) => ({
item_code: row["Item Code"],
cpc_code: row["CPC Code"].slice(1).padStart(8, "0"),
item: row["Item"],
description: scl_item_by_item_code[row["Item Code"]]?.Description
})
)
Insert cell
duckdb
SELECT * FROM scl_item_codes
Insert cell
Insert cell
scl_items = (
await (
await fetch(
"https://fenixservices.fao.org/faostat/api/v1/en/definitions/domain/SCL/items?output_type=objects"
)
).json()
).data
Insert cell
Inputs.table(scl_items)
Insert cell
scl_item_by_item_code = {
const result = {};
scl_items.forEach((item) => {
if (item["Item Code"] in result) {
throw new Error(
`Duplicate Item Code in FAO API response: ${item["Item Code"]}`
);
}
result[item["Item Code"]] = item;
});
return result;
}
Insert cell
Insert cell
duckdb = {
const client = new DuckDBClient();
await client.insertCSV(
"scl_item_codes",
encoder.encode(d3.csvFormat(scl_item_codes))
);
return client;
}
Insert cell
encoder = new TextEncoder()
Insert cell
Insert cell
import { DuckDBClient } from "@cmudig/duckdb"
Insert cell
import { Copier } from "@mbostock/copier"
Insert cell
lodash = require("lodash@4")
Insert cell

Purpose-built for displays of data

Observable is your go-to platform for exploring data and creating expressive data visualizations. Use reactive JavaScript notebooks for prototyping and a collaborative canvas for visual data exploration and dashboard creation.
Learn more