In an earlier blog post, I introduced the topic of my JMP Discovery Summit 2014 e-poster titled “Analysis of Personal Diet and Fitness Data With JMP” and shared my interests in quantified self (QS) analysis projects. For my poster project, I exported two different types of data files from the web-based Activity Manager software by BodyMedia® and wrote JMP Scripting Language (JSL) scripts to import, format and clean my data for further analysis and visualization in JMP. I hope you were able to join me to hear more at the conference in Cary, but if not, you can sign in to the JMP User Community and check out a PDF of my e-poster in the Discovery Summit 2014 section. (Membership in the User Community is free and is a great way to learn from JMP users around the world!)
Today, I’ll share how I used JSL to import and combine a set of multi-worksheet Excel files containing activity and calorie summary information. The Activity Manager software also exports PDF Food Log files, which I’ll cover in my next blog post. By the way, I have uploaded an add-in to the JMP File Exchange that you can use to import your own BodyMedia® Activity Summary and Food Log files into JMP. It also includes a bonus add-in that imports CSV files that you can download from the popular MyFitnessPal food logging website using a Chrome extension.
In early August, I exported nearly 50 Activity Summary and 50 Food Log files covering the time period from 12/20/10 to 7/28/14. Activity Summary data is saved in Excel workbooks that contain six different worksheets, but I was interested in importing data from just the first five: Summary, Activity, Meals, Sleep and Weight. You can see the details of the contents of the other worksheets in my e-poster.
Most of my Activity Summary Excel files contained four weeks of data, although some covered fewer weeks. All files had column headers on line 5 and most had 28 lines of data on the first four tabs, though the number of rows on the fifth tab (Weight) varied. Before 2013, I entered weight measurements manually into the Activity Manager. In January 2013, I began to use a Withings Smart Body Analyzer scale, which uploads weight measurements wirelessly into its own web-based software and shares them automatically via a third-party integration with the Activity Manager.
I decided to script the import process after interactively importing my data files twice, about a year apart. Interactive import was more time-consuming the second time because I had even more files to work with. Since I accumulate new data every day, my number of files will always grow over time. After the second import, I formatted my columns and added formulas to the combined data table, only to compare it to the table from my first import and realize that I had forgotten several important steps.
While I backtracked to complete the steps I'd missed, I began to consider how scripting could make reprocessing all my files much simpler and faster. If the import process was easier, I would look at new data much more often. Fortunately, I found that even a novice scripter like me could write the JSL to import and combine my files.
I started with Source scripts (automatically added to JMP data tables during interactive import) and JSL examples from online documentation and discussion forums. I looped over all my files using a strategy patterned after a text file import example in a SESUG paper written by JMP developer Michael Hecht. As the longtime Mac expert at JMP, Michael is well-known for his high-quality and elegant code in all languages, so why not borrow from a master?
I ran into some snags while working to combine my data into a single unified table. One that I had to handle prior to import was that the Date column on the Sleep tab contained an overnight range rather than a single day like all the other worksheets did, preventing me from merging it directly without a preprocessing step. For example, a value of 12/20/2012-12/21/2012 indicated the night of sleep that started on 12/20/14 and ended the morning of 12/21/14. I parsed out the second day’s date using the JMP Formula Editor and the JMP Word character pattern function.
After creating the formula interactively, I added code to my script to make these steps easily repeatable with help from the JSL Get Script command. Running the command MyColName << Get Script; on the Date column from the Sleep tab printed a key snippet of JSL to JMP’s log, which I added to my script to automate this step. In my final table, the hr:m value displayed in the Sleep column represented how much I had slept before awakening that morning.
There was extra information in my input files that I didn’t want included in my final JMP table. Calorie totals, averages and targets were summarized at the bottom of all of my worksheets (lines 36-39 in the picture of the table above). I added steps to my script to filter out these summaries, the lines of information above the column headers and also blank rows. In total, the version of my activity data table that I used for my Discovery Summit poster contained 1,316 rows of daily data on calories eaten and burned, activity measures, sleep and weight measurements.
My data table wasn’t quite analysis-ready yet. The numeric columns in my table containing durations and percentages that were not formatted correctly upon import. I added formats, missing value codes and modeling types as column properties interactively. I used the Transform column feature on my Date column to quickly add new Date Time variables like Year, Month, Week and Day to my table, and then added to my script to automate those steps. I also added a new formula column to the table (Calories Burned-Calories Consumed) to represent my caloric deficit/excess for a given day.
If you have BodyMedia® Activity Summary files saved in Excel format, you can download my add-in from the File Exchange to perform a point-and-click import of your own files into JMP. This add-in supports the Activity Summary file type I just described, BodyMedia® food log files saved as text, and also food log text files that you can export from the popular (and free) MyFitnessPal with the help of a Chrome extension.
Special thanks to JMP testing manager Audrey Shull and technical writer Melanie Drake for scripting suggestions and add-in testing help! Stay tuned for my next blog post, where I’ll describe how I automated the import of my BodyMedia® food log files using JSL.