Line 34: |
Line 34: |
| ==== csvkit tips ==== | | ==== csvkit tips ==== |
| [https://csvkit.readthedocs.io/en/latest/ csvkit] is a very efficient tool to manipulate huge amounts of CSV data. Here are some useful tips to manipulate Open Food Facts CSV export. | | [https://csvkit.readthedocs.io/en/latest/ csvkit] is a very efficient tool to manipulate huge amounts of CSV data. Here are some useful tips to manipulate Open Food Facts CSV export. |
| + | |
| + | ''Converting whole Open Food Facts "CSV" export to regular CSV''. Open Food Facts export use tabs as separator: it should be called TSV (tab separated values) instead of CSV (comma separated values). <code>csvkit</code> can convert TSV file into CSV very easily: |
| + | |
| + | <code>$ csvclean -t en.openfoodfacts.org.products.csv > myCSV.csv</code> |
| | | |
| ''Selecting 2 column''s. Selecting two or three columns can be useful for some usages. Extracting two columns produce a smaller CSV file which can be opened by common softwares such as Libre Office or Excel. The following command creates a CSV file (brands.csv) containing two columns from Open Food Facts (code and brands). (It generally takes more than 2 minutes, depending on your computer.) | | ''Selecting 2 column''s. Selecting two or three columns can be useful for some usages. Extracting two columns produce a smaller CSV file which can be opened by common softwares such as Libre Office or Excel. The following command creates a CSV file (brands.csv) containing two columns from Open Food Facts (code and brands). (It generally takes more than 2 minutes, depending on your computer.) |
Line 85: |
Line 89: |
| | | |
| Beware, each line is a product and some variable need to be unnest: tidyverser::unnest_wider() | | Beware, each line is a product and some variable need to be unnest: tidyverser::unnest_wider() |
| + | |
| + | === JSONL delta exports === |
| + | Every day, Open Food Facts exports all the products created during the last 24 hours. The documentation of this export can be found in the /data page. |
| + | |
| + | If you don't have MongoDB and just want to use these delta exports to build an up-to-date database, you can merge each export with the help of <code>[https://stedolan.github.io/jq/manual/v1.6/ jq]</code> tool. |
| + | |
| + | $ gunzip products_1638076899_1638162314.json.gz # will decompress the file |
| + | $ wc -l products_1638076899_1638162314.json # will count the number of products in this export (in JSONL each line is a JSON object) |
| + | $ jq -c '. + .' 2021-11-30.json products_1638162314_1638248379.json > 2021-12-01.json # merge the delta with previous complete data |
| | | |
| === JSONL export === | | === JSONL export === |
Line 119: |
Line 132: |
| | | |
| Filtering barcodes which are different from a code containing 1 to 13 digits: | | Filtering barcodes which are different from a code containing 1 to 13 digits: |
− | zcat openfoodfacts-products.jsonl.gz | jq -r '. | select(.code|test("^[0-9]{1,13}$") | not) | .code' > ean_gt_13.csv | + | $ zcat openfoodfacts-products.jsonl.gz | jq -r '. | select(.code|test("^[0-9]{1,13}$") | not) | .code' > ean_gt_13.csv |
| These operations can be quite long (more than 10 minutes depending on your computer and your selection). | | These operations can be quite long (more than 10 minutes depending on your computer and your selection). |