atlas-db 0.2.10

turn apple health export.xml into parquet

Atlas

Atlas lets you explore your Apple Health data.

---

Installation

Install expanse using pip:

pip install atlas-db

Explore

First we create the .parquet file from the export.xml file.

atlas parquet export.xml -o ah.parquet

We can explore the data in many ways.

It is just a table/dataframe/parquet file with 5 columns.

But here we'll use clickhouse local:

clickhouse local

Let's take a look at the table.

DESCRIBE TABLE `ah.parquet`

┌─name────┬─type────────────────────┬─default_type─┬─default_expression─┬─comment─┬─codec_expression─┬─ttl_expression─┐
│ type    │ Nullable(String)        │              │                    │         │                  │                │
│ start   │ Nullable(DateTime64(6)) │              │                    │         │                  │                │
│ end     │ Nullable(DateTime64(6)) │              │                    │         │                  │                │
│ created │ Nullable(DateTime64(6)) │              │                    │         │                  │                │
│ value   │ Nullable(String)        │              │                    │         │                  │                │
└─────────┴─────────────────────────┴──────────────┴────────────────────┴─────────┴──────────────────┴────────────────┘

What kind of "types" do we have and how many?

SELECT
    type,
    COUNT(*) AS count
FROM `ah.parquet`
GROUP BY type
ORDER BY count DESC

┌─type───────────────────────────┬──count─┐
│ ActiveEnergyBurned             │ 879902 │
│ HeartRate                      │ 451854 │
│ BasalEnergyBurned              │ 289031 │
│ DistanceWalkingRunning         │ 260500 │
│ StepCount                      │ 217384 │
│ PhysicalEffort                 │  69747 │
│ AppleExerciseTime              │  61363 │
│ AppleStandTime                 │  58309 │
│ EnvironmentalAudioExposure     │  44535 │
│ SleepAnalysis                  │  36599 │
│ WalkingStepLength              │  28281 │
│ WalkingSpeed                   │  28281 │
│ RespiratoryRate                │  27829 │
│ AppleStandHour                 │  25877 │
│ FlightsClimbed                 │  22690 │
│ WalkingDoubleSupportPercentage │  21900 │
│ WalkingAsymmetryPercentage     │  13820 │
│ HeartRateVariabilitySDNN       │  11961 │
│ OxygenSaturation               │   4912 │
│ StairDescentSpeed              │   4718 │
│ StairAscentSpeed               │   4249 │
│ DistanceCycling                │   2890 │
│ TimeInDaylight                 │   2403 │
│ HeadphoneAudioExposure         │   2323 │
│ RestingHeartRate               │   1399 │
│ WalkingHeartRateAverage        │   1176 │
│ DistanceSwimming               │    455 │
│ SwimmingStrokeCount            │    455 │
│ AppleSleepingWristTemperature  │    442 │
│ RunningSpeed                   │    391 │
│ VO2Max                         │    366 │
│ RunningPower                   │    173 │
│ DietaryCaffeine                │    171 │
│ AppleWalkingSteadiness         │    138 │
│ SixMinuteWalkTestDistance      │    122 │
│ HeartRateRecoveryOneMinute     │     76 │
│ RunningVerticalOscillation     │     74 │
│ RunningGroundContactTime       │     67 │
│ RunningStrideLength            │     54 │
│ MindfulSession                 │     34 │
│ HighHeartRateEvent             │     18 │
│ AudioExposureEvent             │     14 │
│ BodyMass                       │     14 │
│ Height                         │      5 │
│ Fatigue                        │      1 │
│ HKDataTypeSleepDurationGoal    │      1 │
└────────────────────────────────┴────────┘

What's our total step count?

[!NOTE]
The value column is type Nullable(String) so we have to cast toFloat64 to sum up the step values.

SELECT sum(toFloat64(value))
FROM `ah.parquet`
WHERE type = 'StepCount'

┌─sum(toFloat64(value))─┐
│              30295811 │
└───────────────────────┘

30.295.811 (30.29 million) steps. That's a lot of steps!

How to get the Apple Health export.xml file

• open the Apple Health app on iOS
• tap on your profile picture (or initials) at the top right
• tap on Export All Health Data
• tap on Export
• wait a few seconds to a few minutes (~3min for 10 years of data)
• get the export.zip archive via Airdrop to a Mac (or save to Files)

[!NOTE]
The export.xml file is in the export.zip archive.

You can expand the export.zip file by double-clicking on it.

This creates a directory named apple_health_export and in it is the export.xml file.

See: Apple Support on how to export Apple Health and Fitness in XML format

Usage

expanse parquet export.xml

Features

• turn export.xml into a simple parquet file