Monitoring health data - populating Food tables

This article continue the discussion about the building of the system to collect my health numbers.

There are already many data stored in LibreOffice Calc tables and other LibreOffice documents.
Because of that when/where possible macro will be used to generate the mySQL commands to populate the tables.

Populate T_FOOD_TYPE

Populating this table it requires more work because it is necessary to find the information about each specific food used. In order to simplify the input mechanism, a new spreadsheet that collect the information to be put in the database, is created.
Unfortunately there is no easy way to populate the spreadsheet, i.e. it must be done manually, copying the data from the existing LibreOffice word documents as described in the Monitoring health data - my way  #2 article.

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food type tab, is structured as the database, raw.
A hash key is prepared as index, using the Food name, the brand and the number of carbs.
This should allow to generate a unique key for the each record.

Here the fields of the tab:

Index (food type)

Name

1 Serving

Serving unit

Serv X cont

Carbs

Calories

Protein

Fat

Sodium

Potassium

Brand

Notes

The index in the cell A2 use the Hash macro (it is implied the third Party Hash macro is installed):

=TEXTHASH(CONCATENATE(B2,L2,F2),"MD5")

The goal is to have a unique index and to do so the hash is calculated using the name of the food (B2), the value in carbs (F2) and the brand (L2).
The field in the table are mostly following the SQL table structure (see Monitoring health data - an improvement).
For extra info there are these fields:

• Serv X cont
  Serving per container - the number of serving you can expect from the container or how many servings can be obtained from a recipe
• Notes
  Just extra notes about the food

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (O1) contains the starting structure of the statement :

Insert into t_food_type values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($O$1,"""",A2,"""", ",", """", B2, """", ",", C2, ",", """", D2, """", ",", F2, ",", G2, ",", H2, ",", I2, ",", J2, ",", K2, ",",  """", L2, """", ",", """", M2, """", ");")))

The meaning is :

• if the field "Name" exists (not blank) then 
• create a string using the field O1, plus the other fields, starting from the field A2 (hash key) and ending with the field M2 (Notes)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food_type values ("ce7993e549b4d010c825766dfd034a99","Pork Patties",2,"patty",2,270,11,24,500,0,"Jimmy Dean","");
Insert into t_food_type values ("981a21f5a752cad048a834b9fd6cd258","Cooked Ham",4,"slices",1,60,8,2,580,0,"Land O'Frost","");
Insert into t_food_type values ("1a52451ea65def964ddb3c2be50811ce","Big Cup Noodles Homestyle Beef Flavor Ramen Noodle",1,"1/2 container",24,190,3,8,760,0,"Nissin","");
Insert into t_food_type values ("74ccbb6a8f0e507375ca5f727074a4e1","Texas Style Angus Beef Chili with Black Beans Soup",1,"cup",21,210,15,5,790,0,"Campbell","");
Insert into t_food_type values ("76d5f224ea4a78e3d737caa4eba0d1e","iesta Chicken Lime Tortilla Soup with White Meat Chicken",1,"cup",13,110,9,1.5,790,0,"Campbell","");

.............................................................

.............................................................

After the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD_TYPE table.

Populate T_FOOD

The same mechanism used  to populate the T_FOOD_TYPE table, is used to populate the T_FOOD table too.

Like before, a new spreadsheet that collect the information to be put in the database is created (still refer to the Monitoring health data - my way  #2 article)

Let's examine the spreadsheet used to format the insert statement.

The spreadsheet has two tabs:

• Food type
• Food

The Food tab, is structured as the database, raw.

Here the table structure:

Index

Date Meal

Type Meal

FoodIndex

Food name

Servings

Calculated carbs

Calculated salt

The Calculated carbs and Calculated Salt are populated using data from the previous table.

Calculated carbs is :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 6, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of carbs per serving and multiply for the number of serving (field Servings on this tab).

Similarly for the Calculated salt :

=VLOOKUP(D2,'Food type'.$A$2:$M$156, 10, 0)*F2

The meaning is to look in the all previous tab (Food type) of the spreadsheet for the FoodIndex key, retrieve the number of mg of salt per serving and multiply for the number of serving (field Servings on this tab).
Just to have a quick idea about the amount of salt of the selected food.

The Index is an hash key using the Date Meal, the FoodIndex and the Food name fields. 

This should allow to generate a unique key for the each record.
The cell A2 contains this formula :

=TEXTHASH(CONCATENATE(B2,C2,D2,E2),"MD5")

Again the purpose is to obtain a unique key for each entry, to do so it used the date of the meal (B2), the type of the meal (C2, e.g. Breakfast or Lunch or Dinner or Snack), the food index (D2, foreign key) and the food name (E2).
Note that the food name field is populated from the other Calc table (see above).

After the table, there is the formula for the formatting of the SQL insert statement.

First of all, a cell (I1) contains the starting structure of the statement :

Insert into t_food values (

Then below, this formula is applied :

=IF(B2="","",(CONCATENATE($H$1, """", A2, """", ", str_to_date('",(TEXT(B2,"mm/dd/yy")), "', ", """", "%m/%d/%Y", """", "), ",        """",C2,"""",", ","""",D2,"""", ", " ,F2, ");")))

The meaning is :

• if the field "Date Meal" exists (not blank) then 
• create a string using the field H1, plus the other fields, starting from the field A2 (hash key) and ending with the field F2 (Servings)
• otherwise create a blank entry

The formula is then copied in all subsequent cells.

Here an example of the insert statement generation starting with the data on the table :

Insert into t_food values (84280b208c8c61a7aab5c4be5fb25385, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "ac118ebecb401ae2e9fe42b8f7b1da8c", 2);
Insert into t_food values (16df74f3237c526a71186884b0cd7aa1, str_to_date('12/29/14', "%m/%d/%Y"), "Breakfast", "254a86d2153bfbc149f51d63c7113952", 1);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "1b4f22d0d92b9185406cf6d815fd2c4c", 1);
Insert into t_food values (34c6041c3126488e599aa467e8a0d4de, str_to_date('12/29/14', "%m/%d/%Y"), "Lunch", "af3a803a27460603422bf47322ba6d4", 2);
Insert into t_food values (532061254f4761e69bcf627c4916f3c2, str_to_date('12/29/14', "%m/%d/%Y"), "Dinner", "1b4f22d0d92b9185406cf6d815fd2c4c", 2);
....................................................................
....................................................................

Basically after the table is manually populated, we end up with a list of insert capable to fill up the T_FOOD table.

Monitoring health data - populating measurement tables

This article continue the discussion about the building of the system to collect my health numbers.

There are already many data stored in LibreOffice Calc tables and other LibreOffice documents.
Because of that when/where possible macro will be used to generate the mySQL commands to populate the tables.

The T_MES_TYPE table will be populated manually, but T_MES, T_FOOD_TYPE and T_FOOD can use some kind of automation mechanism to do so.

Populate T_MES_TYPE

Here a list of mySQL commands to populate the T_MES_TYPE table.
It is easy to populate the table, just open a mySQL session in a terminal, connect to the database and then cut & paste the lines below.

connect name_database;
insert into t_mes_type values (1, "Glicemy", "ml/dg");
insert into t_mes_type values (2, "Weight", "lb");
insert into t_mes_type values (3, "Carbs", "count");
insert into t_mes_type values (4, "Blood Systolic", "mmHg");
insert into t_mes_type values (5, "Blood Diastolic", "mmHg"); 
insert into t_mes_type values (6, "Heart beat", "bpm"); 

insert into t_mes_type values (7, "Ketons", "count"); 

insert into t_mes_type values (8, "Exercise elliptical", "min"); 

insert into t_mes_type values (9, "Exercise walking", "miles"); 

insert into t_mes_type values (10, "Exercise hiking", "miles"); 

Populate T_MES table from Spreadsheet

Since already exists many data stored in the LibreOffice spreadsheet, here there is  a procedure to populate the data stored in the tables. 

Populate T_MES - glucose level

• Open the spreadsheet
• Create a separate tab and rename it to give a meaning (i.e. Export SQL glucose)
• In the cell A1 copy this fixed string : Insert into t_mes values ( 
• In the cell J2 use the Hash macro (it is implied the third Party Hash macro is installed):
  
  =TEXTHASH(CONCATENATE('All times'.A2,'All times'.F2,'All times'.G2),"MD5")
• Copy in the other Jx fields the formula above
• In the cell A2 copy this formula :
  
  =IF (OR('All times'.G2 = 0, 'All times'.G2 = ""), "",(CONCATENATE($A$1, """", J2,"""", ", str_to_date('", (TEXT('All times'.A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")", ", str_to_date('", (TEXT('All times'.F2,"hh:mm:ss")), "',", """", "%H:%i:%s", """", ")", ", 1, ", 'All times'.G2, ");")))
• Copy all the result strings  in the mysql

Here an example of a correct entry generated :

Insert into t_mes values ("31ba5af2e19e9c8e9bc35bbd1f588fb1", str_to_date('10/28/14',"%m/%d/%Y"), str_to_date('19:45:00',"%H:%i:%s"), 1, 150);

Invalid entries will be blanket

Populate T_MES - weight

• Open the spreadsheet
• Create a separate tab and rename it to give a meaning (i.e. Export SQL weight)
• In the cell A1 copy this fixed string : Insert into t_mes values ( 
• In the cell J2 use the Hash macro (it is implied the third Party Hash macro is installed):
  
  =TEXTHASH(CONCATENATE('All times'.A2,'All times'.F2,'All times'.H2),"MD5")
• Copy in the other Jx fields the formula above
• In the cell A2 copy this formula :
  
  =IF ( OR ('All times'.H2 = 0, 'All times'.H2 = ""), "", (CONCATENATE($A$1, ,"""",J2,"""", ", str_to_date('", (TEXT('All times'.A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")", ", str_to_date('", (TEXT("08:00:00","hh:mm:ss")), "',", """", "%H:%i:%s", """", ")", ", 2, ", 'All times'.H2, ");")))
• Copy the content of the cell A2 for the remains rows up tp the number of rows present in the tab All times (see the limit in the tab All times)
• Copy all the result strings  in the mysql

Here an example of  a correct entry generated :

Insert into t_mes values ("4af7c8660faee64b3e126c3c1717b510", str_to_date('10/28/14',"%m/%d/%Y"), str_to_date('08:00:00',"%H:%i:%s"), 2, 177.6);


Invalid entries will be blanketed

Populate T_MES - Ketons

• Open the spreadsheet
• Go in the tab of the month (to be repeated for each month)
• In the cell U1 copy this fixed string : Insert into t_mes values ( 
• In the cell T2 column create the index using the hash formula:
  
  =TEXTHASH(CONCATENATE(A2,F2,K2),"MD5")
• Copy in the other Tx fields the formula above
• In the cell U2 copy this formula :
  
  =IF (   K2 = "",    "",       (CONCATENATE($U$1,                 """",T2,"""",         ", str_to_date('", (TEXT(A2,"mm/dd/yy")), "',", """", "%m/%d/%Y", """", ")",         ", str_to_date('", (TEXT("09:00:00","hh:mm:ss")), "',", """", "%H:%i:%s", """", ")",         ", 7, ", K2, ");")))
• Copy the content of the cell U2 for the remains rows up tp the number of rows present in the monthly tab 
• Copy all the result strings  in the mysql

Here an example of a generated entry (invalid entries are blanketed).

Insert into t_mes values ("fe8565c8659775e73957e9c0485101f4", str_to_date('03/01/15',"%m/%d/%Y"), str_to_date('09:00:00',"%H:%i:%s"), 7, 0);

Alternatively (I prefer this way) is possible to prepare ASCII files with the generated commands and feed mySQL with these files.
In this way, the files allows to recreate the database from the scratch if necessary, a form of backup, and also is easy to duplicate the database on different machines.
The files can be edited to add comments and explain the purpose of specific commands/fields.

Monitoring health data - my way - #2

On this article I'll discuss how I track data related to the food.
A very important thing to do if you are dieting or keeping track of your health numbers, is to monitor the carbs and other nutrient intake, i.e. how many carbs you are eating.
In order to do so, it is important to know exactly what and when you are eating.

It seems easy but is not.
Why is not easy ? Well, let see some reasons (just a few) :

• not all the food you are eating is labeled
• the food is not  "homogeneous", the same food can be present in different forms
• often a processed food have different characteristics  than a less processed food
• often are indicated base chemical compost rather than more "human" description
• different companies label the food in different way
• all the companies who sell food, always try to hide or manipulate information in order to make more palatable the food they sell. The rule seems to be, if you can't be vague, obscure with chemistry and strange words
• hidden basic ingredients in processed food (like salt or sugar) often requires to "split" a food in it's basic elements
• etc.

So monitoring what you want to eat is not an easy business, it takes time and organization.

Here  is "my way". Repeating myself, this is how  I do that, I'm not stating it is the right way to do it.

It is good for me.

I maintain basically 3 documents :

1. a "food information" document (word processor)
2. a "food journal" document (word processor)
3. the spreadsheet where I track also the sugar level and other measurements (spreadsheet)

Food information

The food information document contains information about the food I eat.

Usually I save a picture of the food or box, for an easy visual reference, and then some nutrient information.
Plus I add some notes to clarify how many carbs are actually present, just to simplify when I have to figure out quickly how much carbs I'm having.

The main problem is due by the fact that manufacturers give the information often in a very complicated way.

For example, many "soups" containers  contains 2 servings, but often the nutrient information are for about 1 serving.
Often you find 2.5 servings per container, other times 1 ... doesn't exists a standard.
Some manufactures express the "serving" in grams, other in oz, other per cup, other per bowl other as a generic "serving", or specific food description.

So in order to figure out what you are eating, to calculate carbs and other measurement, you need to spend time researching information.

Thus I decided to "save" these researches, the results, in a document.
Each entry has a picture of the product, a copy of the nutrition chart, a thumb up or down to indicate that tastes good, and eventually some notes.

Here an example of entry:

Again, the main purpose of this document is to have a quick recap of the food I usually ate, in order to avoid to do the same research over and over.

On this document are also documented some "home made recipes", in order to calculate the nutrition info for that.
What I usually do, is to put down the basic nutrients of each component of the recipe, then calculate the total amount of carbs (and other nutrients) and then divide in cups or bowls.

Food journal

Once you figure out how many carbs (and other nutrients) are in the food, the second step is to write down somewhere what you ate on a specific day.
This is the Food Journal document.
For each day there are 4 entries :

• Breakfast
• Lunch
• Dinner
• Snacks

Each entry will collect information about what I'm eating. Quantity, food, estimated carbs, often divided by food.
With these info I can sum up the number of carbs for each "period" and transpose that value in the spreadsheet.

Here an example of entry :

February 26, 2015

• Breakfast
  1 low carb tortilla (3g) with cheese, 4 slices capocollo (0g), eggs beater
  Coffee
• Lunch
  1 serving Chicken Enchilada bake (15g)
  Pringles chip onion sour – single serve (10g)
  Cornetti single serving (3 g)
• Dinner
  Panera Bread – Chicken Kale and sweet potato soup – 1 cup (9g)
  1 Morey's grilled salmon (1g) with zucchini (5g)
• Snacks
  10 am – 1 pouch Skinny zero popcorn (5g)
  3 pm - Chiquita – Bites juicy red apple (6g) with string cheese
  9 pm – low carb cake (4 g)

More details are put in this document, more is easy to calculate the carbs but there is always a trade between efficiency and time spent doing that.
Being too precise requires a lot of time, so know what to write as amount of information is something that is coming with the experience of doing that daily.

Note that the food journal reports mainly the carbs intake and not all the others nutrient,
This because the carbs intake is the main key in my case, however, other articles will explain how to use the nutrient information and create a daily journal.

Spreadsheet

In the end the goal is to come up with some carbs number to put in the spreadsheet I described in the previous article (Monitoring health data - my way #1).
The day is divided in 6 time areas :

• breakfast
• morning snack
• lunch
• afternoon snack
• dinner
• night snack

For each area the goal is to assign a value (if any).
See the previous document for details.

Hashing with LibreOffice (Updates)

For a project, I needed to hash some data from a spreadsheet.
I'm using LibreOffice Calc (4.3.1.2 for Windows - 4.2.7.2 for Linux) and there is no direct support for the hash.