I’ve had a secret project going since June 2002, entering every component and portion size of my food intake – and exercise – religiously into web site www.weightlossresources.co.uk. Hence when Google decided to run an online course on “Making Sense of Data”, I asked Rebecca Walton at the company if she would be able to get daily intake summary for me in machine readable form: Calorie Intake, Carbs, Protein, Fat weights in grams, plus Exercise calories for every day since I started. Some 3,500 days worth of data. She emailed the spreadsheet to me less than two hours later – brilliant service.

Over that time, i’ve also religiously weighed myself almost every Monday morning, and entered that into the site too. I managed to scrape those readings off the site, and after a few hours work, combined all the data into a single Google Spreadsheet; that’s a free product these days, and has come on leaps and bounds in the last year (i’d not used Excel in anger at all now since late 2012).

With that, I then used the data for the final project of the course, loading the data into Google’s new Fusion Tables Analytics tool on which the course was based.

I’m currently in a 12 week competition at my local gym, based on a course of personal training and bi-weekly progress measures on a Boditrax machine. Effectively a special set of bathroom scales that can shoot electrical signals up one foot and down to the other, and to indicate your fat, muscle and water content. The one thing i’ve found strange is that a lot of the work i’m given is on weights, resulting in a muscle build up, a drop in fat – but at the same time, minimal weight loss. I’m usually reminded that muscle weighs much more than fat; my trainer tells me that the muscle will up my metabolism and contribute to more effective weight loss in future weeks.

Nevertheless, I wanted to analyse all my data and see if I could draw any historical inferences from it that could assist my mission to lose weight this side of the end of the competition (at the end of April). My main questions were:

1. Is my weekly weight loss directly proportional to the number of calories I consume?
2. Does the level of exercise I undertake likewise have a direct effect on my weight loss?
3. Are there any other (nutritional) factors that directly influence my weekly weight loss?

Using the techniques taught in this course, I managed to work out answers to these. I ended up throwing scatter plots like this:

Looking at it, you could infer there was a trend. Sticking a ruler on it sort of suggests that I should be keeping my nett calories consumption around the 2,300 mark to achieve a 2lb/week loss, which is some 200 calories under what i’d been running at with the www.weightlossresources.co.uk site. So, one change to make.

Unlike Tableau Desktop Professional, the current iteration of Google Fusion Tables can’t throw a straight trend line through a scatter chart. You instead have to do a bit of a hop, skip and jump in the spreadsheet you feed in first, using the Google Spreadsheet trend() function – and then you end up with something that looks like this:

The main gotcha there is that every data element in the source data has to be used to draw the trend line. In my case, there were some days when i’d recorded my breakfast food intake, and then been maxed out with work all day – and hence created some outliers I needed to filter out before throwing the trend line. In my case, having the outliers present made the line much shallower than it should have been. Hence one enhancement request for Fusion Tables – please add a “draw a trend line” option that I can invoke to draw a straight line through after filtering out unwanted data. That said, the ability of Fusion Tables to draw data using Maps is fantastic – just not applicable in this, my first, use case.

Some kinks, but a fantastic, easy to use analytics tool – and available as a free add-on to anyone using Google Drive. But the real kudos has to go to Google Spreadsheets; it’s come on leaps and bounds and i’d no longer routinely need Excel any more – and it already now does a lot more. It simply rocks.

The end results of the exercise were:

1. I need to drop my daily nett calorie intake from 2,511 to 2,300 or so to maintain a 2lb/week loss.
2. Exercise cals by themselves do not directly influence weight loss performance; there is no direct correlation here at all.
3. Protein and Fat intake from food have no discernable effect on changes to my weight. However, the level of Carbs I consume have a very material effect; less carbs really help. Reducing the proportion of my carbs intake from the recommended 50% (vs Protein at 20% and Fat at 30%) has a direct correlation to more consistent 2lbs/week losses.
   

One other learning (from reverse engineering the pie charts in www.weightlossresources.co.uk web site) was that 1g of carbs contains approx 3.75 cals, 1g of Protein maps to 4.0 cals, and 1g of fat to 9.0 cals – and hence why the 30% of a balanced diet attributable to fat consumption is, at face value, quite high.

And then I got certified:

So, job done.  One more little exercise to test a theory that dieting one week most often gives the most solid results over a week later, but that can wait for another day (has no material effect if i’m being good every week!). Overall, happy that I can use Google’s tools to do ad-hoc data analysis whenever useful for the future. And a big thankyou to Rebecca Walton and her staff at www.weightlossresources.co.uk, and to Amit, Max and the rest of the staff at Google for an excellent course. Thank you.

Now, back to learning  the structure and nuances of Amazon and Google public Cloud services – a completely different personal simplification project.

-ends-

Footnote: If you ever need to throw a trend line in Google Spreadsheets – at least until that one missing capability makes it into the core product – the process using a simplified sheet is as follows:

Scatter plot initially looks like this:

Add an “=trend()” function to the top empty cell only:

That then writes all the trendline y positions of for all x co-ordinates right down all entries in one take:

which then, when replotted, looks like this. The red dots represent the trend line:

Done!