aidR: Tidepool export format
In aidR: Reading and Standardizing Automated Insulin Delivery Data

knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)

library(aidR)
library(readxl)

path <- system.file("extdata", "tidepool_example.xlsx", package = "aidR")
file <- read_excel(path, sheet = "CGM", guess_max = 100000)

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:

print(file, n = 10)

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)

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

CGM: CGM data.

head(data$cgm)

Basal: basal rates, including their duration.

head(data$basal)

Bolus: insulin boluses, including the amounts delivered as a normal bolus and as an extended bolus.

head(data$bolus)

SMBG: self-monitored blood glucose (SMBG).

head(data$SMBG)

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)

Device Event: device-level events such as alarms, suspends, resumes, and cannula/reservoir changes.

head(data$device_event)

Basal Schedules, BG Targets, Carb Ratios, Insulin Sensitivities: pump therapy settings as they changed over time.

head(data$basal_schedules)

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)

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.