#FoodTipsMonth

Enjoy your meal!

April is the #FoodTipsMonth and time for the first IronViz (about food of course).for IronViz on Tableau Public. So I decided to do a small vizualisation about food. Moreover, one of the datasources for IronViz contest is Open Food Facts.

via GIPHY

Data

Open Food Facts is a french project so I told me "Yeah! Let's use this datasource!". Moreover, I met Stéphane Gigandet, the creator, once in 2012. At this moment, he was just presenting his project. And now, WAOUH, almost 50,000 products around the world and almost 40,000 for France! So we (my colleagues and me) chose to download the database and see what is in the file. My colleague (and padawan! :) ) Giselle Marin used Trifacta to do some data-preparation quickly. She gave me a CSV file that I used to create my vizualisation.

First version

When I opened the file with Tableau, I had no particular idea of what I want to show... I discovered the file quickly through Tableau. Some drag & drop, some charts and I was more sure about what to do. My first idea was to show products in function of nutriments. But I was not really satisfied with that... The second and final idea came when I was on the Internet looking for informations about nutriments. I thought "What if I use RDA (Recommended Dietary Allowances)? What can I do with that?". And that is why I try to create a vizualisation combining food and nutriments.
When I had this idea, I started to looking for informations about RDA. I hesitated between the creation of a second data-source or just hard-cording the different RDA for each nutriment. I chose to hard-code RDA (because, yes, I can't use Tableau 10 to do the vizualisation on Tableau Public... and can't use cross database features... I was so sad...). That was how I created the first version of my vizualisation: a tool which gives you informations about what you're eating.

Second version

The second version was more complex and more complete. Indeed, I was not really satisfied with the first version. I was thinking that it is too simple and not enough interesting. Lucky me, I had an entire week-end to think about it. And the next monday, I came to my office with a complete new idea. Why not add Body Mass Index (BMI) and personal energy needs? And why not add sports which allow you to eliminate energy? That is why I created a second version. I looked for informations about BMI on the Internet. I found what I want on CDC website and, of course, Wikipedia (in french!). These informations allow me to use equations to calculate BMI in function of parameters.
I also looked for informations about energy needs. I found a website which is describing all equation to calculate energy needs in function of the age, the gender, the weight, the height. This is a canadian website named Santé Canada. Almost all of the equations wrote on the website was used in my vizualisation. About sports to eliminate energy, I found informations on a website about diet! You can find informations I used on the website RegimesMaigrir.

Calculations

I almost created 30 calculated fields using parameters, LoD and table calculations. For example, this is the calculated field used to give the energy needs:

//Besoin énergétique estimé (kcal/jour)
if [Sexe]=1
then
    if [Age]>=19
    then 354 - (6.91*[Age]) + [CA]*((9.36*[Weight]) + (726*[Height]))
    elseif [Age]>=9 and [Age]<19 -="" 135.3="" 25="" eight="" elseif="" ge="" then="">=3 and [Age]<9 -="" 135.3="" 20="" eight="" elseif="" ge="" then="">1 and [Age]<3 -="" 100="" 20="" 84="" eight="" else="" end="" ge="" if="" then="">=19
    then 662 - (9.53*[Age]) + [CA]*((15.91*[Weight]) + (539.6*[Height]))
    elseif [Age]>=9 and [Age]<19 -="" 25="" 88.5="" eight="" elseif="" ge="" then="">=3 and [Age]<9 -="" 20="" 88.5="" eight="" elseif="" ge="" then="">1 and [Age]<3 -="" 100="" 20="" 84="" code="" eight="" else="" end="" then="">

This calculated field is using 4 parameters. Each parameters is filled by the user: age, weigth, height and sexe. It is also using an other calculated field named [CA] (a coefficient based on the activity of the user). The LoD calculations are used to give me the average nutriments content for a product. Indeed, in the database, each product is sliced in several code and each code has its own nutriments teneur. To make it easier, I chose to use the average of codes for each product. Moreover, my vizualisation is based on 5 parameters allowing the users to choose between 1 and 5 products, and 5 other parameters for the quantities of each product. So I had to consider each product choosen and each quantity to give the right result. Here it is an example for the sugar content:

if min([product_name])=[Product 1] then
    sum({fixed [product_name]:avg([sugars_100g])})*[Quantity P1]/100
elseif min([product_name])=[Product 2] then
    sum({fixed [product_name]:avg([sugars_100g])})*[Quantity P2]/100
elseif min([product_name])=[Product 3] then
    sum({fixed [product_name]:avg([sugars_100g])})*[Quantity P3]/100
elseif min([product_name])=[Product 4] then
    sum({fixed [product_name]:avg([sugars_100g])})*[Quantity P4]/100
elseif min([product_name])=[Product 5] then
    sum({fixed [product_name]:avg([sugars_100g])})*[Quantity P5]/100
end

After all these calculations, I used table calculations to display the percentage compared to RDA. Indeed, I had to calculate the sum of nutriments content for all 5 products (and considering quantities) and compare them to the RDA. About the energy, I had to compare to the energy needs based on the parameters age, gender, weight, height and activity.
All these calculations give the result displaying on the vizualisation!

And about sports?

Data about sports are in the same data-source. I chose to use an UNION to create the datasource. As Joshua Milligan (aka VizPainter) explains in his article I used UNION fetaure to had data to my primary source. This is much better than data-blending. Indeed, that is allowing me to use a calculated field based on energy content of the meal chosen by the user and to return the corresponding time for each sport. Here it is the calculated field:

lookup([window_sum([_energy]])],first())
/
(sum(if [POIDS_KG]=50 then [CALORIES_BRULEES_KCAL] end)*[Weight]/50)

The first part of the calculated field returns the value of energy content. I used a lookup() because of the UNION. Indeed, the UNION add the lines corresponding to sports data but returns NULL values for nutriments content. So in my chart, if I plot the sports in rows I have all my sports plus a row as NULL. This NULL row had the data about nutriments! That is why I used the lookup() function: to return in each row of my chart the energy content (the first() function return the first line of my chart, in my case the NULL row). After this I just have to hide the NULL row! When I hide a row it does not filter the data! ;-)

The vizualisation

The vizualisation is really simple. I don't choose to put a lot of design because I thought it is more important to be simple and efficient. But I am still mulling it over to find an other design.

Feel free to leave a message or a note about this work.

Thank you!