Parse what is actually present
Locate the statistics section of wearable CSV exports rather than assuming a fixed header position. Standardize identifiers and date fields.
CASE STUDY 02 / DATA ENGINEERING
Sleep data integration across trips
Making wearable exports, sleep logs, and flight records agree on who, when, and which trip.
24 / 48 / 72 HOUR WINDOWS
Illustrative visual; no research data shown
THE QUESTION
How can heterogeneous sleep records be converted into comparable trip-level features without losing timing context?
THE SETTING
The data preparation work combines actigraphy exports, sleep logbooks, and flight records. Cross-midnight episodes, time zones, repeated trips, and inconsistent export headers make naive joins unreliable.
THE STORY IN PLAIN LANGUAGE
The model can only be as reliable as the time axis underneath it.
A sleep episode may cross midnight, belong to one of several trips, or only partly overlap an analysis window. Resolving those cases produces reusable features for downstream models—and demonstrates the engineering needed to turn research files into an analytical dataset.
Technology behind this workflow 9 tools & formats
The stack follows the analytical task: prepare the data, fit or apply the method, check the result, and communicate the output.
See how the libraries connect to the wider portfolio →THE PROJECT, STEP BY STEP
See how the question became a reviewable result.
02 / DATA ENGINEERINGVISUAL WALKTHROUGH
STEP 01 / 03Parse what is actually present
Wearable + logsTrip/time alignmentWindow features
24 / 48 / 72 hour windowsSTEP 02 / 03Align to the right trip
Wearable + logsTrip/time alignmentWindow features
24 / 48 / 72 hour windowsSTEP 03 / 03Derive and verify features
Wearable + logsTrip/time alignmentWindow features
24 / 48 / 72 hour windowsIllustrative process diagram
Align to the right trip
Match episodes using participant, route, and outbound-date context. Normalize timestamps before calculating overlap with flight-relative windows.
Derive and verify features
Calculate 24-, 48-, and 72-hour sleep summaries. Distinguish time in bed from estimated sleep time where wake-after-sleep-onset data is available.
THE TECHNICAL CASE
Decisions behind the analysis.
The analysis depends on a shared time axis. The difficult work is preserving the identity of a participant and trip while reconciling sleep episodes that cross midnight, time zones, and flight-relative windows.
- Unit of analysis
- A sleep episode linked to participant × route × outbound trip date; features are aggregated to trip level.
- Inputs
- Heterogeneous wearable CSV exports, sleep logbooks, flight records, episode start/end times, and available wake-after-sleep-onset measures.
- Deliverables
- Trip-level sleep summaries over 24-, 48-, and 72-hour windows, with data needed to check alignment and allocation.
Robust parsing
The processing code locates the statistics section inside actigraphy exports instead of assuming one fixed header row. This is a practical defense against changes in export structure, but malformed files still require explicit handling and review.
Composite trip keys
Participant identifiers alone are insufficient when someone contributes several trips. Route and outbound-date context help associate an episode with the correct trip. Matching logic should distinguish no match, one match, and multiple candidates before aggregation.
Normalize before subtracting
Date parsing and time-zone interpretation precede duration calculations. A cross-midnight episode is an interval, not a negative clock-time difference. Flight-relative windows make the feature definition comparable across routes.
Separate opportunity from sleep
Time spent in a sleep episode or in bed is distinct from estimated total sleep time. The TST workflow incorporates wake after sleep onset where available and retains verification variants for comparison.
METHOD IN FOCUS
overlap = max(0, min(episode end, window end) − max(episode start, window start))
When total episode sleep is allocated in proportion to overlap, the estimate assumes sleep is distributed uniformly within the episode. It cannot recover minute-by-minute sleep states.
EXPLORE THE METHOD
Move the sleep-feature window
Synthetic episodes show interval overlap and proportional allocation. Change the window to see which episodes contribute to the derived feature.
QUALITY OF THE EVIDENCE
Checks that change the conclusion.
- Boundary behavior
Check an episode fully outside, fully inside, or partly overlapping a window, including equality at a boundary.
- Key integrity
Reconcile unique trip keys before and after joins; review duplicated matches instead of silently summing them.
- Physical plausibility
Review negative durations, implausibly long episodes, and sleep totals exceeding the available observation window.
- Feature consistency
Investigate missing windows and unexpected decreases between nested 24-, 48-, and 72-hour summaries.
FOR THE TECHNICAL CONVERSATION
Where I would take the discussion next.
A useful technical discussion is how to distinguish missing sleep data from zero sleep, test daylight-saving transitions, and quantify uncertainty introduced by proportional allocation.
WHAT IT DEMONSTRATES
From method to meaning.
The output is an analysis-ready set of trip-level sleep measures for downstream recovery and fatigue research.