aidR: Tidepool export format

library(aidR)
library(readxl)

Overview

A Tidepool export is a single multi-sheet .xlsx workbook with one sheet per data type. The sheets Basal and Bolus are always expected to be present; the remaining sheets (CGM, Device Event, Food, Bolus Calculator, Basal Schedules, BG Targets, Carb Ratios, Insulin Sensitivities, SMBG, Upload) are optional and are read only if the workbook contains them.

Because Tidepool aggregates data uploaded from many different pumps, meters, and CGM devices, the exact set of sheets and the columns within them vary between exports. Sheet names and headers, however, are always in English. This example shows a CGM sheet:

# A tibble: 36,873 × 14
   `Zulu Time`         `Local Time`        `Device Time`      
   <dttm>              <dttm>              <dttm>             
 1 2019-05-31 18:21:14 2019-05-31 14:21:14 2019-05-31 14:21:14
 2 2019-05-31 18:16:14 2019-05-31 14:16:14 2019-05-31 14:16:14
 3 2019-05-31 18:11:14 2019-05-31 14:11:14 2019-05-31 14:11:14
 4 2019-05-31 18:06:14 2019-05-31 14:06:14 2019-05-31 14:06:14
 5 2019-05-31 18:01:14 2019-05-31 14:01:14 2019-05-31 14:01:14
 6 2019-05-31 17:56:14 2019-05-31 13:56:14 2019-05-31 13:56:14
 7 2019-05-31 17:51:14 2019-05-31 13:51:14 2019-05-31 13:51:14
 8 2019-05-31 17:46:14 2019-05-31 13:46:14 2019-05-31 13:46:14
 9 2019-05-31 17:41:14 2019-05-31 13:41:14 2019-05-31 13:41:14
10 2019-05-31 17:36:15 2019-05-31 13:36:15 2019-05-31 13:36:15
# ℹ 36,863 more rows
# ℹ 11 more variables: `Tidepool Data Type` <chr>, Value <dbl>, Units <chr>,
#   `Timezone Offset` <dbl>, `Conversion Offset` <dbl>,
#   `Clock Drift Offset` <dbl>, `Device Id` <chr>, Id <chr>, `Upload Id` <chr>,
#   Payload <chr>, Annotations <chr>

How aidR detects this format

aidR checks that the file is a readable Excel workbook containing a sheet named Basal and a sheet named Bolus. Because Tidepool’s sheet names are language-independent, each sheet is then looked up by name; any optional sheet that is absent simply yields no data.

Sheets and the data types they produce

To inspect the raw data, we parse the export with clean = FALSE, which returns the full information provided by Tidepool:

data <- parse_data(id = "1", paths = path, clean = FALSE)
#> Parsing data for id 1...

Each entry below describes one sheet and the information it provides.

  • CGM: CGM data.
head(data$cgm)
# A tibble: 6 × 14
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 18:21:14 2019-05-31 14:21:14 2019-05-31 14:21:14
2 2019-05-31 18:16:14 2019-05-31 14:16:14 2019-05-31 14:16:14
3 2019-05-31 18:11:14 2019-05-31 14:11:14 2019-05-31 14:11:14
4 2019-05-31 18:06:14 2019-05-31 14:06:14 2019-05-31 14:06:14
5 2019-05-31 18:01:14 2019-05-31 14:01:14 2019-05-31 14:01:14
6 2019-05-31 17:56:14 2019-05-31 13:56:14 2019-05-31 13:56:14
# ℹ 11 more variables: `Tidepool Data Type` <chr>, Value <dbl>, Units <chr>,
#   `Timezone Offset` <dbl>, `Conversion Offset` <dbl>,
#   `Clock Drift Offset` <dbl>, `Device Id` <chr>, Id <chr>, `Upload Id` <chr>,
#   Payload <chr>, Annotations <chr>
  • Basal: basal rates, including their duration.
head(data$basal)
# A tibble: 6 × 22
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 18:01:17 2019-05-31 14:01:17 2019-05-31 14:01:17
2 2019-05-31 17:21:17 2019-05-31 13:21:17 2019-05-31 13:21:17
3 2019-05-31 17:16:15 2019-05-31 13:16:15 2019-05-31 13:16:15
4 2019-05-31 17:01:14 2019-05-31 13:01:14 2019-05-31 13:01:14
5 2019-05-31 16:41:14 2019-05-31 12:41:14 2019-05-31 12:41:14
6 2019-05-31 16:21:15 2019-05-31 12:21:15 2019-05-31 12:21:15
# ℹ 19 more variables: `Tidepool Data Type` <chr>, `Delivery Type` <chr>,
#   `Duration (mins)` <dbl>, `Expected Duration (mins)` <lgl>, Rate <dbl>,
#   Percent <dbl>, `Schedule Name` <chr>, `Suppressed Schedule Name` <lgl>,
#   `Suppressed Delivery Type` <chr>, `Suppressed Type` <chr>,
#   `Suppressed Rate` <dbl>, `Timezone Offset` <dbl>,
#   `Conversion Offset` <dbl>, `Clock Drift Offset` <dbl>, `Device Id` <chr>,
#   Id <chr>, `Upload Id` <chr>, Payload <chr>, Annotations <chr>
  • Bolus: insulin boluses, including the amounts delivered as a normal bolus and as an extended bolus.
head(data$bolus)
# A tibble: 6 × 19
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 12:42:27 2019-05-31 08:42:27 2019-05-31 08:42:27
2 2019-05-30 18:14:41 2019-05-30 14:14:41 2019-05-30 14:14:41
3 2019-05-30 15:36:13 2019-05-30 11:36:13 2019-05-30 11:36:13
4 2019-05-30 11:13:40 2019-05-30 07:13:40 2019-05-30 07:13:40
5 2019-05-30 01:46:46 2019-05-29 21:46:46 2019-05-29 21:46:46
6 2019-05-30 00:27:12 2019-05-29 20:27:12 2019-05-29 20:27:12
# ℹ 16 more variables: `Tidepool Data Type` <chr>, `Sub Type` <chr>,
#   `Duration (mins)` <dbl>, `Expected Duration (mins)` <dbl>, Extended <dbl>,
#   `Expected Extended` <dbl>, Normal <dbl>, `Expected Normal` <dbl>,
#   `Timezone Offset` <dbl>, `Conversion Offset` <dbl>,
#   `Clock Drift Offset` <dbl>, `Device Id` <chr>, Id <chr>, `Upload Id` <chr>,
#   Payload <chr>, Annotations <lgl>
  • SMBG: self-monitored blood glucose (SMBG).
head(data$SMBG)
# A tibble: 6 × 15
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 12:42:22 2019-05-31 08:42:22 2019-05-31 08:42:22
2 2019-05-30 18:14:36 2019-05-30 14:14:36 2019-05-30 14:14:36
3 2019-05-30 15:36:08 2019-05-30 11:36:08 2019-05-30 11:36:08
4 2019-05-30 15:36:05 2019-05-30 11:36:05 2019-05-30 11:36:05
5 2019-05-30 11:13:37 2019-05-30 07:13:37 2019-05-30 07:13:37
6 2019-05-30 01:46:35 2019-05-29 21:46:35 2019-05-29 21:46:35
# ℹ 12 more variables: `Tidepool Data Type` <chr>, `Sub Type` <chr>,
#   Value <dbl>, Units <chr>, `Timezone Offset` <dbl>,
#   `Conversion Offset` <dbl>, `Clock Drift Offset` <dbl>, `Device Id` <chr>,
#   Id <chr>, `Upload Id` <chr>, Payload <chr>, Annotations <lgl>
  • Bolus Calculator or Food: carbohydrate intake. Tidepool exports carry one or the other depending on the uploading device, never both, and aidR uses whichever is present. If both or neither appear, aidR raises an error. The example contains a Bolus Calculator sheet:
head(data$bolus_calculator)
# A tibble: 6 × 26
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 12:42:27 2019-05-31 08:42:27 2019-05-31 08:42:27
2 2019-05-30 18:14:41 2019-05-30 14:14:41 2019-05-30 14:14:41
3 2019-05-30 15:36:13 2019-05-30 11:36:13 2019-05-30 11:36:13
4 2019-05-30 11:13:40 2019-05-30 07:13:40 2019-05-30 07:13:40
5 2019-05-30 01:46:46 2019-05-29 21:46:46 2019-05-29 21:46:46
6 2019-05-30 00:27:12 2019-05-29 20:27:12 2019-05-29 20:27:12
# ℹ 23 more variables: `Tidepool Wizard Type` <chr>, `Bg Input` <dbl>,
#   `Insulin Sensitivity` <dbl>, `Carb Input` <dbl>,
#   `Insulin Carb Ratio` <dbl>, `Insulin On Board` <dbl>, Bolus <chr>,
#   Units <chr>, `Recommended Carb` <dbl>, `Recommended Correction` <dbl>,
#   `Recommended Net` <dbl>, `BG Target` <dbl>, `BG Target High` <lgl>,
#   `BG Target Low` <lgl>, `BG Target Range Setting` <lgl>,
#   `Timezone Offset` <dbl>, `Conversion Offset` <dbl>, …
  • Device Event: device-level events such as alarms, suspends, resumes, and cannula/reservoir changes.
head(data$device_event)
# A tibble: 6 × 28
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 17:16:15 2019-05-31 13:16:15 2019-05-31 13:16:15
2 2019-05-31 16:41:14 2019-05-31 12:41:14 2019-05-31 12:41:14
3 2019-05-31 16:16:15 2019-05-31 12:16:15 2019-05-31 12:16:15
4 2019-05-31 12:43:48 2019-05-31 08:43:48 2019-05-31 08:43:48
5 2019-05-31 11:41:15 2019-05-31 07:41:15 2019-05-31 07:41:15
6 2019-05-31 10:06:15 2019-05-31 06:06:15 2019-05-31 06:06:15
# ℹ 25 more variables: `Tidepool Data Type` <chr>, `Sub Type` <chr>,
#   `Alarm Type` <chr>, Value <lgl>, Units <lgl>, `Duration (mins)` <dbl>,
#   `Expected Duration (mins)` <lgl>, `Change From` <chr>, `Change To` <chr>,
#   `Change Agent` <chr>, From <lgl>, To <lgl>, Status <chr>,
#   `Prime Target` <chr>, Volume <dbl>, `Reason Suspended` <chr>,
#   `Reason Resumed` <chr>, `Timezone Offset` <dbl>, `Conversion Offset` <dbl>,
#   `Clock Drift Offset` <dbl>, `Device Id` <chr>, Id <chr>, …
  • Basal Schedules, BG Targets, Carb Ratios, Insulin Sensitivities: pump therapy settings as they changed over time.
head(data$basal_schedules)
# A tibble: 6 × 22
  `Zulu Time`         `Local Time`        `Device Time`      
  <dttm>              <dttm>              <dttm>             
1 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
2 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
3 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
4 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
5 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
6 2019-05-31 18:21:40 2019-05-31 14:21:40 2019-05-31 14:21:40
# ℹ 19 more variables: `Tidepool Data Type` <chr>, `Active Schedule` <chr>,
#   `Schedule Name` <chr>, `Basal Schedule Start` <dttm>,
#   `Basal Schedule Rate` <dbl>, `Timezone Offset` <dbl>,
#   `Conversion Offset` <dbl>, `Clock Drift Offset` <lgl>, `Device Id` <chr>,
#   Id <chr>, `Upload Id` <chr>, Bolus <chr>, Manufacturers <chr>, Model <int>,
#   `Serial Number` <int>, Payload <lgl>, Annotations <lgl>, Basal <lgl>,
#   Display <lgl>
  • Upload: metadata about each upload to Tidepool (device, time, software version), useful for tracing which device a given stretch of data came from.
head(data$upload)
# A tibble: 6 × 20
  `Zulu Time`         `Local Time`        `Device Time` `Computer Time`    
  <dttm>              <dttm>              <lgl>         <dttm>             
1 2019-05-31 18:21:54 2019-05-31 14:21:54 NA            2019-05-31 14:21:54
2 2019-05-31 14:24:11 2019-05-31 10:24:11 NA            2019-05-31 10:24:11
3 2019-05-23 16:12:18 2019-05-23 12:12:18 NA            2019-05-23 12:12:18
4 2019-05-23 14:32:27 2019-05-23 10:32:27 NA            2019-05-23 10:32:27
5 2019-05-13 15:11:32 2019-05-13 11:11:32 NA            2019-05-13 11:11:32
6 2019-05-10 14:41:01 2019-05-10 10:41:01 NA            2019-05-10 10:41:01
# ℹ 16 more variables: Timezone <chr>, `Time Processing` <chr>,
#   `Tidepool Data Type` <chr>, Version <chr>, `Device Serial Number` <chr>,
#   `Device Model` <chr>, `Device Manufacturers` <chr>, `Device Tags` <chr>,
#   `Timezone Offset` <dbl>, `Conversion Offset` <dbl>, `Device Id` <chr>,
#   Id <chr>, `By User` <chr>, `Upload Id` <chr>, Payload <lgl>,
#   Annotations <lgl>

Cleaning files

When parse_data is run with clean = TRUE, the cleaned data types are built from the following sheets and attributes:

Cleaned data type Used sheets Used attributes from clean = FALSE
cgm CGM data$cgm
basal Basal data$basal
bolus Bolus data$bolus
carbs Bolus Calculator or Food data$carbs
SMBG SMBG data$SMBG
total_basal Basal data$basal
total_bolus Bolus data$bolus

Notes

  • CGM and SMBG values are reported in mg/dL or mmol/L depending on the account settings; aidR converts them to mg/dL automatically.
  • Timestamps carry a per-row UTC offset, so timezone_offset is populated (unlike most other export formats, where only local wall-clock time is available).
  • Tidepool reports no daily insulin aggregate, so total_basal and total_bolus are summed from the cleaned basal and bolus data and carry source = "summed".
  • If carbohydrates are taken from the Bolus Calculator sheet, its Bolus column can be matched to the the Id column in the Bolus sheet to link bolus and carbohydrate entries. Carbohydrates from the Food sheet cannot be matched to boluses, and their link_id is NA.