Working with JSON data export files

[SunsetRunner]

@MrMarv

@iccir919

@eglons

@craigvan

There has been a lengthy discussion about the use of the JSON file format over on this thread, which is now closed.  https://community.fitbit.com/t5/Fitbit-com-Dashboard/Resolved-Changes-to-Data-Export-tool/td-p/30687.... The tread has been marked as resolved but only because after a huge outcry the original CSV data export was reinstated. There will no doubt be more so here is another thread so we can learn more about the content, provide further feedback to the Fitbit team and hopefully get things fixed.

The JSON format is no doubt here to stay and if you want heart rate data it is the only way to get it.  However, the current export file is not without its problems, here are some and there will no doubt be more.

• You have to download everything from day one each time you export. For some that is a huge file.
• Inconsistent data formats and types and standards
• Missing data
• No explanation of some of the data e.g “confidence” in the Heart Rate data
• Conversion from JSON to CSV format

Tools

The tools problem can be addressed 2 ways. If you have Excel in Windows there is a JSON to CSV add in. It only does one file at a time.

@ICCIR919 has produced an online tool that converts and merges data files of the same type. It also fixes the timezone problem making time local.

https://iccir919.github.io/fitbit-json-to-csv/index.html

It runs in the web browser on your PC so none of data goes anywhere.

Data Content

@mrmarv has also provided an overview of the data I’ve copied it here so you can see some of the issues for yourself.

Time zones:

     GMT - Distance,  Exercise, Heart Rate, Steps

     Local Time -  Altitude, Calories

     No standard - and a company's reputation often lives or dies on its standards.

Start Dates of the monthly bucketed files - looks like a holdover from the 30 days in a Fitbit month. Maybe starts with the day of incorporation, I don't know. I could be missing something here. Could be a serialized date formula on the system they work on.

Files with a sequential number as part of the key:

     The exercise logs have the oldest logs with a zero and the latest, in my case with 500.

     The food logs have the newest with a zero and the oldest with, in my case 3300

     Standards, people.

Calorie logs - no comment, other than it is in local time zone.

Distance logs:

     GMT

     No clue how they decide to book an entry - the time differential between the zero distance entries is sporadic

     The value appears to be in centimeters - no biggie but interesting that other logs are not in centimeters.

Exercise logs:

     GMT

     My treadmill entries show distance in miles NOT centimeters

     My device initiated walk shows distance in miles NOT centimeters

     Duration is in milliseconds - really?

Food logs:

     ZERO nutritional values for customized foods. And let's face it people, the only way to track nutritional values and get them right is to customize them. Fitbit is not even close to being perfect and neither is MyFitnessPal or FatSecret so don't try and point me there. Especially after the "Nutritional Label Fiasco" a while back.

     Fitbit has already shown weakness in breaking down nutritional values for display on the Web and on their apps. This just shows their developer and project leader's blindness once again.

     How are people going to get data to present to their doctors when Fitbit is no longer reporting on these very important values? Or even evaluate their own food plan? Can you say "Disservice"?

Heart Rate logs:

     GMT

     Confidence level of 1 - 3 - I'd like a definition.

[Sparksg]

That's the point of everyone being upset: 99% (a guesstimate, of course) of the population can no longer see their actual data - by making download in a format unknown to most people in the world and providing exactly zero tools to see or analyze the data in that unintelligible (to most of the population) format.  You may as well have outright encrypted the data and said "If you have the decryption key, you can see it just fine. What? Oh,no, we're not giving you the decryption key to your own data. Perhaps you can rig something together with random bits of visual basic or javascript?   Don't know those?  Oh well (shrug) Enjoy your computer code you don't understand and can't work with"

[Colinm39]

Also I have to download nearly 8 years of data every time, and then find what I want.

I'm sure Fitbit will address the issue once they get through the question of giving us our data.

[chriotte]

Kudos to Fitbit for finally giving easy access to all the data! I agree with people that the JSON format can be a bit tricky to work with, especially with so many individual files...I have started the work on a python script to convert the exported data into an easily usable format that can be analysed further in Python or opened up in other tools such as Excel.

The script is a bit basic at the moment, but with some time, it will mature, and last but not least; collaborators are very welcome to enhance and extend the code by submitting pull requests on GitHub.

The script is outlined in a blog post on my blog DataEspresso 

[comish4lif]

I would also add that I appreciate the folks here that have written scripts and Excel Add-ins to help out with the JSON file format.

Also, I'm not downloading and running scripts and code on my PC from people on any message board. @Fitbit really needs to fix this issue. 

Allow me to export a set of data (by date range) in a flat format that I can import easily.

[TreeSqueak]

I tried the JSON to CSV converted, selected Australia/Melbourne, but all the dates are incorrect, I think it is converting them in the american format, for example, I have dates of 5th October 2019, I assume is supposed to be the 10 of June 2019.

---

Tools

The tools problem can be addressed 2 ways. If you have Excel in Windows there is a JSON to CSV add in. It only does one file at a time.

@ICCIR919 has produced an online tool that converts and merges data files of the same type. It also fixes the timezone problem making time local.

https://iccir919.github.io/fitbit-json-to-csv/index.html

It runs in the web browser on your PC so none of data goes anywhere.

---

Does anyone know how I can fix this?

[ringding]

@chriottethanks for the blog and the code.  I'm currently stuck off work with a relapse of my M.E. and so have been looking back at Fitbit data to try to understand if there were warning signs I missed.

Installing Python and having a play with your code has been fun and informative.  Many years ago I used to use MATLAB and so it was nice to fire up those synapses again.  I'm creating a CSV file with resting HR and active minutes at the moment. 

I'm struggling a little to access the data I really want though and can't get my head round the JSON format.  I specifically want to access the "time in heart rate zones" data but the JSON format seems to be different to other multi value files (e.g. resting heart rate).

Any chance you could give me some pointers on how to handle this?

[Kellyofun]

Hi All,

I'm new to Fitbit. I tried to use that tool in the link to convert JSON to CSV and it unfortunately it doesn't work for me. I've tried to use the conversion tool in excel and I can't get that to work, either.

I saw this update on another thread, but I don't seen to have that option on my screen?

Or is it just not there because I've already downloaded the JSON files, today (that I can't get to open)?

Fitbit Update 11/28/2018: The old data export tool is now back and available for use. You can now choose which way you would like to export your data going forward. If you would like to access the old tool, please go here and select "if you would like to use the old data export tool click here" under Export My Fitbit Data. 

[Colinm39]

@Kellyofun  The link works for me from your post.. This is what it looks like and you can only load 30 days maximum from the Custom Range.. I just accumulate my Activities data every month.

[Kellyofun]

Thanks Colin,

Can you please confirm if you can get heart rate data doing that extract?

It doesn't seem to work for me (or I'm doing something wrong!).

Thanks,

Kelly

P.S. it's raining in the Sunshine State, today! Yay!

[Colinm39]

@Kellyofun   No Heart Rate data... I use this link which is free and written to help Fitbitters..

You have to login and allow the link to work..

You can download the data in CSV format.. If the data included Fitbit tracked you can get HR plot points every 6 seconds, if you use Workout or one of the others like Run and Walk you get a HR plot point every 2 seconds.

Looking at the news tonight you are going to be swamped with rain..

---

@Kellyofun wrote:

Thanks Colin,

Can you please confirm if you can get heart rate data doing that extract?

It doesn't seem to work for me (or I'm doing something wrong!).

Thanks,

Kelly

P.S. it's raining in the Sunshine State, today! Yay!

---

[norman_cates]

I have just found that I can't get my historical fitbit data in a useful format. ie JSON.

I had a skim through this set of posts, but wasn't sure if these tools for converting JSON to CSV are still working or exist.

I'll work my way back up the posts and try things, but jsut wondered if there is a reliable way that is now known.

Thanks

[norman_cates]

So I found the reference to the webpage about converting JSON data to CSV.

Wonderful! 

And I appreciate the attempt to give us our data in the correct timezone. 

But that part of it does not seem to be working...

My data is not coming back with the correct dates...

Don't suppose the author is still willing to make tweaks?

[ringding]

I ended up editting a Python script to process my historical data (in JSON format) to get it in to CSV.  Happy to share what I did, as it's based on a link further up this thread.  I'm not a neat coder mind, so it might be a bit of a bodge.

[norman_cates]

That would be much appreciated. 🙂

[ringding]

I'll send you a DM.

[engineerfit]

extremely dissapointed fitbit makes us jump through hoops to extract heartrate data and highly considering purchasing different wearable which makes this process easier.

[SunsetRunner]

Just came back to the community as I'm reporting my 7th failed Fitbit (why do I bother?)

Did any of the ridiculous anomalies in the data get resolved? I stopped checking as it went on so long boredom set in.

Pete

[norman_cates]

I’ve stopped paying Fitbit or My Fitness Pal any sort of premium fee. Neither are interested in letting us get our data in a useful format. And for me their Premium offerings are not useful. 

I have actually gone to My Net Diary because their food data seems to be more accurate and at least does basic sanity checking unlike MFP. 

AND if you connect My Net Diary to Fitbit it will send the calorie data to Fitbit, AND pull the Fitbit data back to My Net Diary, AND parse the Fitbit calorie expended data AND then let you export that data in  CSV or Excel format which you can then graph. You do need to do some minor tweaking but it’s trivial. 

HOWEVER, to get the Fitbit link in My Net Diary you do need to pay them a a premium membership which is about $60 IIRC a year. For me, totally worth it. 

Just wanted to drop this here in case it’s helpful 

[sofia.yfa]

Having manually checked the altitude field from the JSON export in comparison with my Fitbit dashboard, I've noticed that when I did e.g., 10 floors (visible in the dashboard), the JSON export had a value of 100 (10 times larger). This is quite heuristic, but the pattern persisted for my data points.

[sofia.yfa]

Thanks for raising the issue. Along with my research team, we are analyzing a large number of Fitbit data and we've come across the timezone inconsistencies limitation.

Based on a manual check, comparing Fitbit dashboards and JSON exports, we can verify that the timezones reported above are correct. We have also created a table of all available types and their export timezone.

However, we are still unsure about:

• Device Temperature
• Heart Rate Variability Details
• Heart Rate Variability Histogram
• Estimated Oxygen Variation

Note that we have identified that Heart Rate is indeed in GMT, but we do not want to draw quick conclusions about related types. Any help would be appreciated.