Using TripIt Data to Track Your Plane Flights

I will use TripIt to get most of my flight information. This was inspired by a talk by Hadley Wickham. The point of the talk was data visualization, but one of his examples was based on relationship between when he is traveling and number of commits on GitHub. For that example he used the history of his trips on TripIt, and that’s the data I need to correct the time stamps.

OK, here we go. Remember that I warned you that adjusting the time stamps would involve complications.

I started with Hadley’s code to fetch history from TripIt. There’s a TripIt developer page that points you to more information. TripIt has an OAuth API for full-strength applications. That was more than I needed. Hadley used a simpler form of authorization. Here’s the TripIt description:

TripIt API offers a very simple way of authenticating the API that should only be used for testing and development purposes…. Note that this authentication scheme is off by default for every TripIt user. If you want to have this turned on for your account so you can use it for development purposes please send email to support@tripit.com.

I sent them an email to request simple authentication, and TripIt support responded the next day. From then on I could use httr functions to get the data from TripIt via information from documentation of the TripIt API.

I did one GET call from httr to get a list of my TripIt trips. Next I used the purrr package to extract data from the nested JSON lists returned by TripIt. In particular, I used the map function to get TripIt “air” objects for each trip ID. Individual airplane flights are “segments” with each air object. For example, a trip might be two connecting flights to get to the destination and two flights to return home, each represented by a “segment”. I always feel like I’m a few steps away from thorough understanding of purrr and tend to rely on a certain amount of trial and error to get a sequence of flatten and map call that extract what I need. The code is available from a GitHub repo I made to support this post.

I end up with a data frame that has the scheduled departure and arrival for each flight and conveniently provides the time zone for each airport. Note that in practice this data might not be perfect. It is scheduled flights only and would not account for cancelled flights or even the time of a delayed flight. So keep that in mind before you try to use this data to examine something detailed such as whether your heart rate is elevated during takeoffs and landings.

I took a quick detour and explored whether I could use the FlightAware API to get the actual arrival times. It is now easy to get free access to limited FlightAware data. But the API calls are oriented to retrieving current rather than historical data so I can’t use it to find out about my past flights.

Once I have the trip ID’s, I use trip ID to fetch the flight information. I used the RStudio View() function to examine the results I got back from calls to the TripIt API. I get one trip at a time, fetch the air segments, and then bind them together with purrr::map_dfr. I fetched more than the minimum columns I needed partly out of curiosity over what was in the TripIt data. It’s interesting to see all my flight information in one table, although much is not directly relevant to the task at hand.

# get list of trips
trips_list <- GET_tripit("https://api.tripit.com/v1/list/trip/past/true/false")
trip_ids <- trips_list$Trip %>% map_chr("id")
GET_air <- function(trip_id) {
atrip <-
GET_tripit(
paste0(
"https://api.tripit.com/v1/get/trip/id/",
trip_id,
"/include_objects/true"
) )
air_trip <- atrip[["AirObject"]][["Segment"]]
flights <- dplyr::tibble(
trip_id = trip_id,
trip_start = atrip[["Trip"]][["start_date"]],
start_date = air_trip %>% purrr::map("StartDateTime") %>% map_chr("date"),
start_time = air_trip %>% purrr::map("StartDateTime") %>% map_chr("time"),
start_timezone = air_trip %>% purrr::map("StartDateTime") %>% map_chr("timezone"),
start_city = air_trip %>% purrr::map_chr("start_city_name"),
end_date = air_trip %>% purrr::map("EndDateTime") %>% map_chr("date"),
end_time = air_trip %>% purrr::map("EndDateTime") %>% map_chr("time"),
end_timezone = air_trip %>% purrr::map("EndDateTime") %>% map_chr("timezone"),
end_city = air_trip %>% purrr::map_chr("end_city_name"),
airline = air_trip %>% purrr::map_chr("marketing_airline"),
code = air_trip %>% purrr::map_chr("marketing_airline_code"),
number = air_trip %>% purrr::map_chr("marketing_flight_number"),
aircraft = air_trip %>% purrr::map_chr("aircraft_display_name"),
distance = air_trip %>% purrr::map_chr("distance"),
duration = air_trip %>% purrr::map_chr("duration")
)
}
GET_air_mem <- memoise::memoise(GET_air)
# I used memoise because TripIt typically doesn't let me fetch
# all the data in a single call. With memoise, for each call
# I only fetch the items it doesn't remember.
flying <- trip_ids %>% map_dfr(GET_air_mem)

I used memoise with the GET_air_mem function because TripIt generally doesn’t give me all my flights on one try. It seems to limit how much data it will return. Because of memoise the Get_air_mem remembers the results of previous successful fetches from TripIt and only asks for things flights it doesn’t already know about.

I ended up with a tibble with one row per flight. I selected the two columns I needed and used bind_rows to combine them with the manual data I created above. I focused on the ending time and location of each flight. That’s when I assume my watch gets changed to a new time zone and life in that time zone begins. Life in that time zone ends when life in the next time zone begins. I wrote a short function to convert the local scheduled time in the flight information into UTC time comparable to what is in the health export.

So after I bind together rows from TripIt and the manual rows above, I set utc_until = lead(utc_arrive), until_timezone = lead(local_timezone). I set the last utc_until to now()

local_to_utc <- function(dt, timezone) {
# UTC of this local time. Note: not vectorized
if (is.character(dt)) dt <- as_datetime(dt)
tz(dt) <- timezone
xt <- with_tz(dt, "UTC")
tz(xt) <- "UTC"
return(xt)
}
arrivals <- flying %>%
mutate(local_start = ymd_hms(paste0(start_date, start_time)),
local_arrive = ymd_hms(paste0(end_date, end_time))) %>%
filter(start_timezone != end_timezone) %>% # only trips that change time zone matter
select(local_arrive, local_timezone = end_timezone) %>%
# manual additions here
bind_rows(manual_timezone_changes) %>%
mutate(utc_arrive = map2_dbl(local_arrive, local_timezone, local_to_utc) %>% as_datetime) %>%
arrange(utc_arrive) %>%
mutate(utc_until = lead(utc_arrive), until_timezone = lead(local_timezone))
arrivals$utc_until[nrow(arrivals)] <- with_tz(now(), "UTC")
arrivals$until_timezone[nrow(arrivals)] <- Sys.timezone() # I don't really need this.
# Check that I have arrivals set up properly so that my last arrival is in my home time zone, otherwise warn
if (arrivals$local_timezone[nrow(arrivals)] != Sys.timezone()) warning("Expected to end in Sys.timezone:",
Sys.timezone(), " rather than ",
arrivals$local_timezone[nrow(arrivals)])

Assign a Time Zone to Each Row of the Data

The next step was to associate a time zone with each of the 4+ million rows in health_df. I thought this would be difficult and very slow. I was wrong! The fuzzyjoin package by Dave Robinson came to the rescue. I can’t do a simple join between flights and data rows because there is not an exact match for the time stamps. Instead I want to join the time stamps in health_df with a start and end time stamp for each row in the arrivals table. It turns out fuzzyjoin has that covered.

The fuzzyjoin package provides a variety of special joins that do not rely on an exact match. In this case, what I needed was the interval_left_join function which joins tables based on overlapping intervals. The help for interval_left_join explains that this function requires the IRanges package available from Bioconductor and points to instructions for installation. This was the first time I have used anything from the Bioconductor repository. I’m impressed by the speed of interval_left_join. I thought it would be impractical to run it on the full dataset, but it feels fast. I also used it to relate rows in health_df to rows in workout_df, but I’ll describe that in Part II.

health_df <- health_df %>%
mutate(utc_start = as_datetime(startDate),
utc_end = as_datetime(endDate)) %>%
filter(!is.na(utc_start)) %>%
interval_left_join(arrivals %>%
select(utc_arrive, utc_until, timezone = local_timezone),
by = c("utc_start" = "utc_arrive", "utc_start" = "utc_until"))

Do a quick report to check whether the assignment of time zone makes sense. If data documenting the transition from one time zone to another is missing the whole table will be off kilter. If there’s something wrong with the arrivals table, it should show up as an unexpected result in Table 3.

# shows trip by trip to check whether timezone assignment works as expected:
# health_df %>% #filter(timezone != "America/New_York") %>%
# mutate(date = as_date(utc_start)) %>%
# group_by(timezone, utc_arrive) %>% summarise(arrive = min(utc_start), leave = max(utc_start),
# days = length(unique(date)),
# n = n())
# Do a quick check whether the distribution of time zones makes sense
health_df %>% mutate(date = as_date(utc_start)) %>% group_by(timezone) %>%
summarise(dates = length(unique(date)), observations = n()) %>%
kable(format.args = list(decimal.mark = " ", big.mark = ","),
table.attr='class="myTable"',
caption = "Frequency of Days by Time Zone")

Using Row-by-Row Time Zone Data to Adjust Time Stamps

Now that I have time zone information attached to each row of health_df, I can translate the UTC time into the local time as it appeared on my watch. In practice I find it quite hard to wrap my head around exactly what is happening with all these time manipulations. Sometimes it feels like a science fiction time travel story².

Remember that for the datetime class in R, time zone is an attribute that applies to an entire vector. That’s a limitation we need to work around. First we will convert the character version of the time stamp (with universal time offset) to a datetime class value with UTC as the time zone.

Next I create a function exported_time_to_local which will convert the time as it appears in the Apple Health Export to the time as it appeared at the time the data was originally added to the health data. As a side effect the lubridate conversion functions will also adjust for daylight savings changes. This function will apply to data for a single time zone only so that it can be vectorized. This is important because it will be applied to millions of rows.

utc_dt_to_local <- function(dt, time_zone) {
# Adjust a vector of datetime from time zone where data was exported
# to a particular time_zone that that applies to the whole vector.
tz(dt) <- "UTC"
local <- with_tz(dt, time_zone) # now adjust utc to the time zone I want
tz(local) <- "UTC"
# I mark the vector as UTC because I will be row_bind-ing vectors
# together and all need to have the same time zone attribute.
# Although the vector is marked as UTC,
# in the end I will treat the hour as being whatever the local
# time was that I experienced then.
return(local)
}

Next I will apply the utc_dt_to_local function to the character time stamps in the Apple Health Export. The function needs to be applied to a vector with the same time zone for all elements in the vector. By doing group_by(start_time_zone) before I use the function inside mutate, the function will be applied with a different time zone for each group. That way the function is vectorized for each group and is reasonably fast. I did not group the time zones separately for the start date and the end date. Usually they would be in the same tine zone, and even if they are not I want to handle them as if they were. All the time stamp vectors end up having the time zone attribute of “UTC”. Remember that time zone is a single attribute that has to apply to the whole vector. In this case it is labelled UTC, but each time stamp describes the local time when and where the observation was recorded. So 16:42 means 4:42 PM in the time zone (and daylight savings status) at the place and time of the measurement that goes with that time stamp.

health_df <- health_df %>%
group_by(timezone) %>%
# assume end_date is in the same time zone as start_date
mutate(local_start = utc_dt_to_local(utc_start, first(timezone)),
local_end = utc_dt_to_local(utc_end, first(timezone))) %>%
# mutate(end_time_zone = get_my_time_zone(endDate)) %>%
# group_by(end_time_zone) %>%
# mutate(end_date = exported_time_to_local(endDate, first(end_time_zone))) %>%
ungroup() %>%
mutate(date = as_date(utc_start),
start_time = as.integer(difftime(local_start, floor_date(local_start, "day"), unit = "secs")) %>% hms::hms()) %>%
arrange(type, utc_start) %>%
ungroup()
# Here I'll adjust time for workout_df as well
workout_df <- workout_df %>%
mutate(utc_start = as_datetime(startDate),
utc_end = as_datetime(endDate)) %>%
filter(!is.na(utc_start)) %>%
interval_left_join(arrivals %>%
select(utc_arrive, utc_until, timezone = local_timezone),
by = c("utc_start" = "utc_arrive", "utc_start" = "utc_until"))
workout_df <- workout_df %>%
group_by(timezone) %>%
mutate(local_start = utc_dt_to_local(utc_start, first(timezone)),
local_end = utc_dt_to_local(utc_end, first(timezone))) %>%
arrange(utc_start) %>%
ungroup()
# I'm going to focus on health_df and workout_df, but I could adjust times in the other df's as well

At this point we have two sets of date and time stamps for health_df and workout_df. The prefix “utc” is for universal time and should be used for sorting by time. The prefix “local” has local time as was shown on the watch when the measurement was recorded. Local time and date is what we need if we want to examine time during the day. For health_df there is also a column called start_time which is just the time of day, without date, expressed as hh:mm:ss.

Figure 1 is a density plot that shows the difference between looking at time of day in terms of UTC versus local time. The first takeaway from this plot is that the frequency of measurements is strongly related to time of day. There are relatively few measurements during the night period when I am usually asleep in bed. There is also a deep dip around lunch time. The Apple Watch makes more measurements when I am being active. I’ll look at that in more detail in Part II.

The two distributions (UTC and local time) mostly overlap, but there are some systematic differences. I live in the Eastern Time Zone which is five hours later than UTC during standard time and four hours later during daylight savings. As a compromise I subtracted 4.5 hours from the UTC time of day to make the distributions fit on the same scale. The red scale shows the distribution of observations in the export data in terms of UTC time of day and the blue scale shows local time. The blue scale corresponds more to my subjective experience of time of day. You can see that the red distribution is “fatter” during the period from 4AM to 8AM than is the blue scale. That’s primarily because of walking trips in England that are not in the Eastern Time. Observations that appear to be at 4AM in terms of UTC – 4.5 are probably happening in British Summer Time (because I’m on a hike in England during the summer) which is actually UTC + 1. In terms of my experience of local time, they are happening when my watch says 9AM. (The dataset also includes a 10 day walking holiday in Greece which is another two hours farther east than England.)

If one looks at sleep time between 00:00 and 06:00, the Local Time distribution (blue) shows a consistent pattern of fewer observations because of less activity. A larger proportion of my activity outside of the Eastern Time Zone was in Europe rather than in Pacific Time and that’s why the UTC distribution seems shifted to the left relative to Local Time. Also, Local Time is responding to daylight savings changes and UTC is not. I think that explains why the peaks before and after lunch time are higher in the Local Time distribution than in UTC. I’m actually fairly rigid about when I take time for lunch (and presumably reduce my physical activity) and taking daylight savings into account makes that more clear.

In summary, if I did not correct for time zone and daylight savings the general bi-modal pattern would still be apparent. But the translation into local time makes the time of day pattern more clear and more accurate, especially if one focuses on hours when I would expect to be asleep.

Figure 1: Density Plot by Time of Observation