Google Calendar Chart Visualization of A Day in the Life of My Diabetic Daughter
My 9 year old daughter Darcie has Type 1 Diabetes, and we use a Google Sheet to track her blood sugar, carb intake, and a couple of other important T1 details. She enters data on a Google Form, and it's populated in the corresponding Google Sheet. We have a number (a large number...) of charts we use to visualize what's going on there. We, along with her endocrinologist, use the log and the charts to help determine what tweaks we need to make to her insulin regimen to keep her safe and healthy.
To see a good visual representation of what goes on with her blood sugar over time, and especially to tell if her school routine, summer routine, and weekend routine are all on track, all along I would have loved to have had a Calendar Chart. We've been working on some pretty cool interactive Calendar Charts over at Tiller, and I'm already totally hooked on using this kind of chart to help me understand my spending habits, so also using them to visualize blood sugar checks is a natural fit. It also defaults to a nice shade of blue that's already close to "diabetes awareness" blue, and November is Diabetes Awareness Month, so I've got all the inspiration I need.
Unfortunately, I've also got a problem. Google Sheets has an awesome data explorer, and also has a number of great built-in charts - but unfortunately a Calendar Chart isn't one of them. I'm a developer at heart (and by profession), so what do I do? Build one! Actually, I built three... Side note, you're talking to a guy that had 157 pages of charts for my daughter's last quarterly endocrinologist appointment. I was heartbroken -- they only wanted to see one. It's possible I get a little carried away with this whole data visualization thing sometimes.
I built three calendar charts, but we'll just stick to one for now. Please do let me know in the comments if you'd like me to go over the other two in a future blog post. This one shows the number of blood sugar checks per day, and the other two show her average blood sugar and maximum blood sugar values per day. Also please let me know if you've got any other ideas of concepts to visualize with this type of chart, or any others for that matter. I have .... more.
The reason this particular chart is useful is that we're particularly interested in drilling down into days that have a very small number of blood sugar checks, or a very large number of blood sugar checks. A Calendar Chart, which shows values as a darker or lighter shade per day, is perfect for that.
Once our awesome Calendar Chart is up, we can easily see which days had few checks (light colors) and which have many checks (dark colors).
A small number of checks on a given day might indicate that we need to work on some habits, or talk to her school about frequency of checks, or more likely: something else entirely. At a glance, I can already tell that the only days below that are really light colored (i.e. very few checks) were on days she was at Camp Leo. The camp is specifically for children with diabetes, and the staff is incredible. They kept track of everything there, and so we didn't bother to keep track in this sheet. We were at family camp during that light-colored weekend in May, and she was at elementary camp at that light few days in July. So -- Great! No light streaks to worry about. This chart already paid for itself.
On the other hand, a large number of checks might indicate a bit of a rough day for blood sugar levels (either high, or low, or both), and drilling down there might help us figure out a little better just how Darcie responds to each of the 22+ (more like 200...) factors that affect blood sugar that might be in play that day (at that latitude, during that phase of the moon, while the wind is from the south, ...)
We can click on a given day to get a detailed list of the checks she did, to help us figure out what's going on. Let's take a look at this dark block on a Friday in late September to see if we can figure out why we needed to do 14 checks that day:
Ah ha! She had a mild nighttime low, a test that morning, a stubborn low just after lunch, and cross country practice. The rest were just normal checks that she does every day. Chart just paid for itself again (for clarity, Google Charts is free, and it's simple for a developer to get started - so it pays for itself pretty quickly).
For the benefit of anyone reading this who may not already be intimately familiar with Type 1 Diabetes, I'll explain why each of those was important:
Mild Nighttime Low: We often have to check Darcie's blood sugar at night, sometimes multiple times, because undetected nighttime lows can be very dangerous, and occasionally fatal. If her numbers haven't been great all day and/or she's had any stress, activity, or diet that's not part of her routine, we'll check her at night. 79 is very mild, and we treated with 13g of carbohydrates with no insulin.
Test: When her blood sugar is high, like many diabetics (and probably the rest of us, too!), she doesn't do as well academically and cognitively, so she has to test to see whether she should take a test or not. In this case, she was indeed high (likely due to some of the carbs from the Luna bar that we used to treat the nighttime low coming on board about the same time as the carbs from toast and a waffle).
Stubborn Low: Beats the heck out of me what happened here. We even reduced the insulin at lunch to try to prevent a low. Diabetes has a mind of it's own. As mentioned above, lows are dangerous, and so this one got some attention. 15 grams of fast-acting carbohydrates and re-check, then 6 grams more and a recheck, then about 15 grams of carbs in a slower-acting format (i.e. a half Luna bar). Good to go.
Cross Country Practice: Running makes Darcie (and many other folks with Type 1 Diabetes) go low, so we have to check and treat a lot while running. That's just part of the deal. If you're an amateur or aspiring athlete who happens to have diabetes, and are interested in finding connections, learning about activities, and sharing unique experiences, take a look at Team ConnecT1D. I personally had an opportunity to spend a weekend with a handful of these folks this summer, and it was a blast.
Now that I've shown how I use these wonderful charts, next post I will show you, in all my nerdy software developer glory, exactly how I built these wonderful charts. Building your own is not necessarily something I'd recommend unless you also do indeed happen to be a developer, or want to be, but if you are (or do want to be), it's pretty easy stuff once you get through a few "ah-ha!" moments and dodge a couple of dead ends. I hope to make that part easy for you.
Whether you're a developer, parent of a kid with Type 1 Diabetes, or any, all, or none of the above, pretty much everyone needs to track spending -- we're also building some pretty cool stuff for that over at Tiller, including some interactive charts very similar to these. I'd certainly recommend signing up for a 30-day free trial to check us out!
To see a good visual representation of what goes on with her blood sugar over time, and especially to tell if her school routine, summer routine, and weekend routine are all on track, all along I would have loved to have had a Calendar Chart. We've been working on some pretty cool interactive Calendar Charts over at Tiller, and I'm already totally hooked on using this kind of chart to help me understand my spending habits, so also using them to visualize blood sugar checks is a natural fit. It also defaults to a nice shade of blue that's already close to "diabetes awareness" blue, and November is Diabetes Awareness Month, so I've got all the inspiration I need.
Unfortunately, I've also got a problem. Google Sheets has an awesome data explorer, and also has a number of great built-in charts - but unfortunately a Calendar Chart isn't one of them. I'm a developer at heart (and by profession), so what do I do? Build one! Actually, I built three... Side note, you're talking to a guy that had 157 pages of charts for my daughter's last quarterly endocrinologist appointment. I was heartbroken -- they only wanted to see one. It's possible I get a little carried away with this whole data visualization thing sometimes.
I built three calendar charts, but we'll just stick to one for now. Please do let me know in the comments if you'd like me to go over the other two in a future blog post. This one shows the number of blood sugar checks per day, and the other two show her average blood sugar and maximum blood sugar values per day. Also please let me know if you've got any other ideas of concepts to visualize with this type of chart, or any others for that matter. I have .... more.
The reason this particular chart is useful is that we're particularly interested in drilling down into days that have a very small number of blood sugar checks, or a very large number of blood sugar checks. A Calendar Chart, which shows values as a darker or lighter shade per day, is perfect for that.
Once our awesome Calendar Chart is up, we can easily see which days had few checks (light colors) and which have many checks (dark colors).
A small number of checks on a given day might indicate that we need to work on some habits, or talk to her school about frequency of checks, or more likely: something else entirely. At a glance, I can already tell that the only days below that are really light colored (i.e. very few checks) were on days she was at Camp Leo. The camp is specifically for children with diabetes, and the staff is incredible. They kept track of everything there, and so we didn't bother to keep track in this sheet. We were at family camp during that light-colored weekend in May, and she was at elementary camp at that light few days in July. So -- Great! No light streaks to worry about. This chart already paid for itself.
On the other hand, a large number of checks might indicate a bit of a rough day for blood sugar levels (either high, or low, or both), and drilling down there might help us figure out a little better just how Darcie responds to each of the 22+ (more like 200...) factors that affect blood sugar that might be in play that day (at that latitude, during that phase of the moon, while the wind is from the south, ...)
We can click on a given day to get a detailed list of the checks she did, to help us figure out what's going on. Let's take a look at this dark block on a Friday in late September to see if we can figure out why we needed to do 14 checks that day:
For the benefit of anyone reading this who may not already be intimately familiar with Type 1 Diabetes, I'll explain why each of those was important:
Mild Nighttime Low: We often have to check Darcie's blood sugar at night, sometimes multiple times, because undetected nighttime lows can be very dangerous, and occasionally fatal. If her numbers haven't been great all day and/or she's had any stress, activity, or diet that's not part of her routine, we'll check her at night. 79 is very mild, and we treated with 13g of carbohydrates with no insulin.
Test: When her blood sugar is high, like many diabetics (and probably the rest of us, too!), she doesn't do as well academically and cognitively, so she has to test to see whether she should take a test or not. In this case, she was indeed high (likely due to some of the carbs from the Luna bar that we used to treat the nighttime low coming on board about the same time as the carbs from toast and a waffle).
Stubborn Low: Beats the heck out of me what happened here. We even reduced the insulin at lunch to try to prevent a low. Diabetes has a mind of it's own. As mentioned above, lows are dangerous, and so this one got some attention. 15 grams of fast-acting carbohydrates and re-check, then 6 grams more and a recheck, then about 15 grams of carbs in a slower-acting format (i.e. a half Luna bar). Good to go.
Cross Country Practice: Running makes Darcie (and many other folks with Type 1 Diabetes) go low, so we have to check and treat a lot while running. That's just part of the deal. If you're an amateur or aspiring athlete who happens to have diabetes, and are interested in finding connections, learning about activities, and sharing unique experiences, take a look at Team ConnecT1D. I personally had an opportunity to spend a weekend with a handful of these folks this summer, and it was a blast.
Now that I've shown how I use these wonderful charts, next post I will show you, in all my nerdy software developer glory, exactly how I built these wonderful charts. Building your own is not necessarily something I'd recommend unless you also do indeed happen to be a developer, or want to be, but if you are (or do want to be), it's pretty easy stuff once you get through a few "ah-ha!" moments and dodge a couple of dead ends. I hope to make that part easy for you.
Whether you're a developer, parent of a kid with Type 1 Diabetes, or any, all, or none of the above, pretty much everyone needs to track spending -- we're also building some pretty cool stuff for that over at Tiller, including some interactive charts very similar to these. I'd certainly recommend signing up for a 30-day free trial to check us out!
Comments
Post a Comment