4,535,554 rows from raw text data.10,000 random samples (and a smaller portions of columns) was created.code / barcodeproduct_namebrandsquantity or serving informationThis is useful precisely because it is real data entered and maintained by people. Some fields are complete and standardized; others may be missing, inconsistent, or questionable.
The spreadsheet cannot answer a question until we understand what its cells represent.
Important:
A blank cell is not automatically zero.
At first glance, one row may look like “one food.”
A better description is:
One row is one product record identified by a product code/barcode.
When we later count rows, we are counting records, not necessarily distinct foods in the everyday sense.
A barcode may contain only digits.
But ask what arithmetic would mean:
Those calculations make no sense.
So a barcode is better treated as Text / an identifier, even if every character is a digit.
This is the same reason ZIP codes and student IDs should usually not be treated as measurements.
Compare these possible fields:
| Field | Looks like | What it really means |
|---|---|---|
code | digits | identifier |
product_name | text | product label/name |
quantity | 340 g, 12 oz, etc. | amount plus a unit |
| sugar per 100 g | number | standardized measurement |
| serving size | number/text | amount whose meaning depends on the unit |
The number alone is not enough. Units are part of the data.
A value of 12 could mean 12 g, 12 oz, 12 servings, or something else entirely.
Good:
“For products with a reported sugar-per-100-g value, how much sugar would a 30 g portion contain?”
Less useful:
“Do something with this spreadsheet.”
The first question tells us:
SLO1: comprehend the problem before designing the solution.
This dataset may already look like a clean rectangle. That does not mean the data are clean.
Look for problems such as:
The hard part is often not fixing the spreadsheet layout. It is deciding what the values mean.
Suppose sugars_100g is blank for a product.
Possible meanings include:
It does not necessarily mean the food contains zero sugar.
This becomes important when Excel performs arithmetic: a formula can produce a perfectly valid-looking number from an invalid assumption.
A field measured per 100 g gives us a common denominator.
That means two products with different package sizes can still be compared using the same unit.
By contrast:
quantity = 12 ozquantity = 340 gcannot be compared directly without interpreting and converting the units.
Standardization is part of the model, not merely formatting.
Open Food Facts is useful because it reflects how real datasets are created:
A value being present in a spreadsheet does not guarantee that it is correct.
Does this value make sense in the real world?
Keep different jobs separate when possible.
| Sheet role | Purpose |
|---|---|
Raw | Preserve the original downloaded data |
Work | Make formatting changes and add calculations |
Notes | Record the question, definitions, assumptions, and observations |
The Raw sheet is evidence of where you started.
Do not “clean” the only copy and then forget what was changed.
A community-contributed product database reflects what has been entered into it.
Ask:
A large dataset can still be an incomplete picture of the world.
If the dataset contains 10,000 rows, we can safely say:
“This extract contains 10,000 product records.”
We should not automatically say:
Those claims require information the rows do not provide.
Work with the provided Open Food Facts workbook.
Raw.Work.Notes.Notes!A1, write: For products with a reported sugar-per-100-g value, how much sugar would a 30 g portion contain? Work sheet, identify: Do not delete rows simply because they look inconvenient.
Suppose a product reports sugar as grams per 100 g.
Insert a new column named something like:
Sugar_in_30g
For the first product row with a valid sugar value, create a formula equivalent to:
1
=sugar_per_100g_cell*30/100
Then use the fill handle to copy the formula down the column.
This uses only:
Now inspect the results carefully.
Find a row where the original sugar-per-100-g value is blank.
Ask:
This is the key lesson:
A spreadsheet can calculate correctly from data that do not support the conclusion.
We will learn more sophisticated formula techniques later. For now, recognizing the problem is the important skill.
Create a worksheet named Example.
Work.Compare the jobs:
Raw preserves the sourceWork supports calculationExample communicates a small result to a human readerIn the Notes sheet, write two sentences:
Can answer:
“For records with valid sugar-per-100-g data, we can calculate the sugar content of a standardized 30 g portion.”
Cannot answer from this dataset alone:
“Which product is healthiest?”
Why not?
“Healthiest” requires a definition and possibly variables that are not represented by one sugar calculation.