2f:T56f,# Handling `OpenFoodFacts` as `Parquet` files: a few examples 😎 An exercise to reproduce a few examples from [this training I made](https://inseefrlab.github.io/funathon2023_sujet4) as a serverless alternative to the `Streamlit` app proposed.

${fas`exclamation-triangle`} Warning

You might need to wait a few seconds the first time, I have not optimized anything.

This an exploratory notebook. I put some things here as elements of discussion for this [${fab`github`} issue](https://github.com/openfoodfacts/openfoodfacts-server/issues/7660).

The `coicop` column is not coming from OpenFoodFacts but coming from a machine learning algorithm proposed in [this tutorial](https://inseefrlab.github.io/funathon2023_sujet4). It is not robust at all, it is here for demonstrative purposes.

30:[] 2d:{"title":"Handling OpenFoodFacts as Parquet files: a few examples 😎","cells":[{"id":0,"value":"$2f","pinned":false,"hidden":false,"mode":"md"},{"id":87,"value":"## Summary statistics directly from Parquet file\n\n```sql\nSELECT\n MEDIAN(\"energy-kcal_100g\") AS energy,\n COUNT(*) AS n,\n coicop FROM off\nWHERE \"energy-kcal_100g\" IS NOT NULL\nGROUP BY coicop\n```","pinned":false,"hidden":false,"mode":"md"},{"id":19,"value":"\tagregation_rapide = db.query(\n `SELECT MEDIAN(\"energy-kcal_100g\") AS energy, COUNT(*) AS n, coicop FROM off WHERE \"energy-kcal_100g\" IS NOT NULL GROUP BY coicop`\n )","pinned":true,"hidden":false,"mode":"ojs"},{"id":42,"value":"Plot.plot({\n marks: [\n Plot.barX(agregation_rapide.filter(d => d.n > 50000), { x: \"energy\", y: \"coicop\", tip: true }),\n Plot.ruleX([0])\n ]\n})","pinned":true,"hidden":false,"mode":"ojs"},{"id":21,"value":"Inputs.table(\n agregation_rapide.filter(d => d.n > 50000)\n )","pinned":true,"hidden":false,"mode":"ojs"},{"id":117,"value":"## Finding a product with barcode","pinned":true,"hidden":false,"mode":"md"},{"id":129,"value":"ean = \"5000112602999\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":141,"value":"`SELECT product_name from off WHERE code = ${ean}`","pinned":true,"hidden":false,"mode":"ojs"},{"id":124,"value":"Inputs.table(\n db.query(`SELECT product_name from off WHERE code = ${ean}`)\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":104,"value":"## Finding a product from name","pinned":true,"hidden":false,"mode":"md"},{"id":3,"value":"url = \"https://minio.lab.sspcloud.fr/projet-funathon/2023/sujet4/diffusion/openfood.parquet\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":113,"value":"product_name = \"Cristaline bouteille\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":110,"value":"//db.query(`SELECT product_name from off WHERE jaro_winkler_similarity('${product_name}',product_name) > 0.9 AND \"energy-kcal_100g\" IS NOT NULL`)","pinned":true,"hidden":false,"mode":"ojs"},{"id":5,"value":"db = {\n const configuredClient = await DuckDBClient.of({}) ;\n await configuredClient.query(\n `\n CREATE OR REPLACE VIEW off AS\n SELECT * FROM read_parquet('${url}')\n `\n )\n\n return configuredClient ;\n\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":10,"value":"Inputs.table(\n db.query(`SELECT * FROM off LIMIT 10`)\n)","pinned":true,"hidden":false,"mode":"ojs"},{"id":63,"value":"## Dependencies","pinned":false,"hidden":false,"mode":"md"},{"id":59,"value":"import { style as faStyle, fa, fab, fas } from \"@airbornemint/fontawesome\"","pinned":true,"hidden":false,"mode":"ojs"},{"id":67,"value":"faStyle({regular: true, solid: true, brands: true})","pinned":true,"hidden":false,"mode":"ojs"},{"id":69,"value":"tachyons = {\n const src = \"https://unpkg.com/tachyons@4.10.0/css/tachyons.min.css\";\n const n = html``;\n const api = {\n attach: () => document.querySelector(\"head\").prepend(n),\n detach: () => n.parentNode && n.parentNode.removeChild(n)\n };\n api.attach();\n invalidation.then(api.detach);\n return api;\n}","pinned":true,"hidden":false,"mode":"ojs"},{"id":71,"value":"ns = Inputs.text().classList[0]","pinned":true,"hidden":false,"mode":"ojs"},{"id":65,"value":"html``","pinned":true,"hidden":false,"mode":"ojs"}],"files":"$W30","stdlib":"1","titling":"auto"}