Skip to content

Instantly share code, notes, and snippets.

@TimKraemer
Last active May 10, 2024 13:17
Show Gist options
  • Save TimKraemer/0cdc58703b2303a2f48edb6f8f17e254 to your computer and use it in GitHub Desktop.
Save TimKraemer/0cdc58703b2303a2f48edb6f8f17e254 to your computer and use it in GitHub Desktop.
python script to create a driver's log book "Fahrtenbuch" for Excel based on Google's Maps Timeline takeout data
import json
import sys
from datetime import datetime
import csv
import os
from geopy.geocoders import Nominatim # pip install geopy
def convert_date(date_str):
# Parse the ISO 8601 format date string
date_obj = datetime.fromisoformat(date_str.rstrip("Z"))
# Format the date into the desired format
formatted_date = date_obj.strftime("%d.%m.%Y, %H:%M")
return formatted_date
def generate_csv(directory):
for filename in os.listdir(directory):
# we're going to ignore all non-json files
fna = filename.split(".")
if len(fna) > 1 and fna[len(fna) - 1] == "json":
# for every json file...
with open(os.path.join(directory, filename), 'rb') as jsonFile:
# parse json
data = json.load(jsonFile)
# we're only interested in the activities from our timeline
activities = list(
filter(lambda tl_obj: "activitySegment" in tl_obj, data["timelineObjects"]))
# specifically we want all "IN_PASSENGER_VEHICLE" activities
trips = list(
filter(lambda activity: activity["activitySegment"]["activityType"] == "IN_PASSENGER_VEHICLE", activities))
# we're using geopy's Nominatim reverse-location service (which uses openstreetmap's API)
locator = Nominatim(user_agent="trip2excel")
# csv first row as header (remove this, if you're only interested in the data)
csv_data = [("start address", "destination address",
"distance", "start time", "destination time")]
for trip in trips:
try:
# we're using the Nominatim's raw format, since we're going to format the address later
raw = locator.reverse("{}, {}".format(
(trip["activitySegment"]["startLocation"]
["latitudeE7"] / 10000000),
(trip["activitySegment"]["startLocation"]
["longitudeE7"] / 10000000)
)).raw
# format will be: building road house_number postcode town/suburb
start_address = "{} {} {} {} {}".format(
("{}".format(raw.get("address").get("building")) if raw.get("address").get(
"building") else ""),
(raw.get("address").get("road")),
(raw.get("address").get("house_number")),
(raw.get("address").get("postcode")),
(raw.get("address").get("town")
or raw.get("address").get("suburb"))
)
except KeyError:
# if there is no address for the given gps coordinates
print("unknown start_address")
start_address = ""
try:
# same as start_address // FIXME: avoid code-duplication
raw = locator.reverse("{}, {}".format(
(trip["activitySegment"]["endLocation"]
["latitudeE7"] / 10000000),
(trip["activitySegment"]["endLocation"]
["longitudeE7"] / 10000000)
)).raw
destination_address = "{} {} {} {} {}".format(
("{}".format(raw.get("address").get("building")) if raw.get("address").get(
"building") else ""),
(raw.get("address").get("road")),
(raw.get("address").get("house_number")),
(raw.get("address").get("postcode")),
(raw.get("address").get("town")
or raw.get("address").get("suburb"))
)
except KeyError:
print("unknown destination_address")
destination_address = ""
try:
# render the distance as kilometer
distance = "{}km".format(
trip["activitySegment"]["distance"] / 1000)
except KeyError:
print("unknown distance")
distance = ""
try:
# some date formatting - german style...
start_time = convert_date(
trip["activitySegment"]["duration"]["startTimestamp"])
except KeyError:
print("unknown start_time")
start_time = ""
try:
destination_time = convert_date(
trip["activitySegment"]["duration"]
["endTimestamp"])
except KeyError:
print("unknown destination_time")
destination_time = ""
# creating a csv row
csv_data.append((
start_address,
destination_address,
distance,
start_time,
destination_time
))
# some status reports for the terminal, since location retrieving takes some time...
print(
f'Processed trip {len(csv_data) - 1} of {len(trips)} in file {filename}')
# create .csv file with utf-8 encoding and some M$ Excel defaults...
with open(os.path.join(sys.argv[1], filename).split('.json')[0] + '.csv', 'w', newline='\n',
encoding='utf-8-sig') as csvFile:
writer = csv.writer(csvFile, dialect='excel', delimiter=';', quotechar='\'',
quoting=csv.QUOTE_MINIMAL)
writer.writerows(csv_data)
if __name__ == '__main__':
# as a first parameter we're expecting a directory filled with .json files from google's takeout location
# history archive request it here: https://takeout.google.com/settings/takeout/custom/location_history
if len(sys.argv) < 2:
print("Error: No directory specified. Please provide the directory as a parameter.")
sys.exit(1)
directory = sys.argv[1]
if not os.path.isdir(directory):
print(f"Error: The specified directory '{directory}' does not exist.")
sys.exit(1)
generate_csv(directory)
@TimKraemer
Copy link
Author

TimKraemer commented Aug 15, 2022

  1. Google Takeout runterladen, entpacken
  2. das Script mit dem gerade entpackten Ordner als Parameter aufrufen:
    z.B. python trip2excel.py DEIN_ORDNER

allerdings ist das Ding über zwei Jahre alt, ich weiß nicht ob sich mittlerweile das Format von Google Takeout verändert hat, falls ja würde ich mich über einen Patch freuen

@TimKraemer
Copy link
Author

updated the script to deal with google's current timestamp format

Sign up for free to join this conversation on GitHub. Already have an account? Sign in to comment