---
title: "aidR: Tidepool export format"
output: rmarkdown::html_vignette
vignette: >
  %\VignetteIndexEntry{aidR: Tidepool export format}
  %\VignetteEngine{knitr::rmarkdown}
  %\VignetteEncoding{UTF-8}
---
```{r, include = FALSE}
knitr::opts_chunk$set(
  collapse = TRUE,
  comment = "#>"
)
```
```{r setup, message = FALSE}
library(aidR)
library(readxl)
```
```{r, include=FALSE}
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:

```{r, comment = "", echo = FALSE}
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:
```{r}
data <- parse_data(id = "1", paths = path, clean = FALSE)
```
Each entry below describes one sheet and the information it provides.

- **`CGM`**: CGM data.
```{r, comment=""}
head(data$cgm)
```
- **`Basal`**: basal rates, including their duration.
```{r, comment=""}
head(data$basal)
```
- **`Bolus`**: insulin boluses, including the amounts delivered as a normal bolus and as an extended bolus.
```{r, comment=""}
head(data$bolus)
```
- **`SMBG`**: self-monitored blood glucose (SMBG).
```{r, comment=""}
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:
```{r, comment=""}
head(data$bolus_calculator)
```
- **`Device Event`**: device-level events such as alarms, suspends, resumes, and cannula/reservoir changes.
```{r, comment=""}
head(data$device_event)
```
- **`Basal Schedules`**, **`BG Targets`**, **`Carb Ratios`**, **`Insulin Sensitivities`**: pump therapy settings as they changed over time.
```{r, comment=""}
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.
```{r, comment=""}
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`.