Every month comes with its routines, and if you're anything like me, you probably have a love-hate relationship with them. For me, one of those tasks was to manually count the number of days I drove to work and the number of days I worked from home. But you know what? I thought to myself: Why not spend time automating this? After all, I own a Volvo V60 that's capable of giving me an excellent log of my trips. So, I decided to write a Python script that does the heavy lifting for me, and now I can focus on the fun stuff—like figuring out how to get my Volvo's API to directly sync with the script.

Why I Needed to Automate This

You might be wondering why this is such a big deal. I mean, it's just a matter of counting days, right? Well, the problem is not the complexity but the repetition of the task. Doing the same manual counting task 12 times a year feels a bit too much like Groundhog Day. Plus, we are living in the 21st century—automation is the name of the game. So, I dived into the Volvo Drive Log and my Python IDE and got to work.

The Script: Python to the Rescue

Before we delve into the details, let me say that Python was an obvious choice for this project. Given its simplicity and a rich ecosystem of libraries, I could easily read Excel files, manipulate data, and even count business days in a given month.

Here's how the script works:

Importing Libraries

import pandas as pd
import numpy as np
from datetime import datetime
from dateutil import relativedelta

In this section, I imported the required libraries. pandas is used for data manipulation, numpy for numerical operations, and datetime for date-time manipulations.

Function: find_dates_from_eindpunt

This is the heart of the script, where most of the logic resides.

def find_dates_from_eindpunt(excel_path, eindpunt_value="Oude Veerseweg", eindpunt_col="Eindpunt", datetime_col="Begonnen"):

Here, I defined the function that takes several parameters:

• excel_path: The path to the Excel file containing the log.

• eindpunt_value: The value to search for in the 'Eindpunt' column.

• eindpunt_col: The name of the column where we'll search for eindpunt_value.

• datetime_col: The name of the column containing the date-time strings.

Reading the Excel File

df = pd.read_excel(excel_path)

With pandas, reading an Excel file is as simple as this one-liner.

Filtering Rows

filtered_df = df[df[eindpunt_col].str.contains(eindpunt_value, case=False, na=False)]

Here, we filter the rows where the 'Eindpunt' column contains the word "Oude Veerseweg."

Checking for Multiple Months

months_set = set(str(date).split("-")[1] for date in dates_set)

This part checks if the data contains entries from multiple months and prints a warning if it does.

The Main Program

In the main section of the code, we take user input for the eindpunt_value and the number of days off. Then, we call the find_dates_from_eindpunt function and print out the results.

Using the Script

Using the script is easy-peasy! Place your Excel file in the right directory, run the script, and answer the prompts. Voila! You'll have your count of days you drove to work, days you worked from home, and even days you took off.

Future Plans

The next big thing on my agenda is to integrate this script with the Volvo API, so I won't even have to download the Excel file manually. A fully automated system is the dream, after all.

Here's the Entire Script for Your Reference

import pandas as pd
import numpy as np
from datetime import datetime
from dateutil import relativedelta

def find_dates_from_eindpunt(excel_path, eindpunt_value="Oude Veerseweg", eindpunt_col="Eindpunt", datetime_col="Begonnen"):
    df = pd.read_excel(excel_path)
    filtered_df = df[df[eindpunt_col].str.contains(eindpunt_value, case=False, na=False)]
    dates_set = set(str(date).split(" ")[0] for date in filtered_df[datetime_col].to_list())
    months_set = set(str(date).split("-")[1] for date in dates_set)
    if len(months_set) > 1:
        print("Warning: More than one month found in the data. Please be cautious.")
    sorted_dates = sorted(dates_set)
    business_days_count = 0
    if sorted_dates:
        first_date = datetime.strptime(sorted_dates[0], '%Y-%m-%d')
        last_date = datetime.strptime(sorted_dates[-1], '%Y-%m-%d')
        month_name = first_date.strftime("%B")
        business_days_count = np.busday_count(sorted_dates[0], sorted_dates[-1]) + 1
        adjusted_count = business_days_count - len(sorted_dates)
        return sorted_dates, adjusted_count, business_days_count, month_name
    else:
        return [], 0, 0, ""

if __name__ == "__main__":
    excel_path = "data/volvo-journal-log.xlsx"
    eindpunt_value = input("Enter the value to search for in the 'Eindpunt' column (default is 'Oude Veerseweg'): ")
    if not eindpunt_value:
        eindpunt_value = "Oude Veerseweg"
    days_off_input = input("Enter the number of days off you had (default is 0): ")
    days_off = int(days_off_input) if days_off_input else 0
    dates, adjusted_count, business_days_count, month_name = find_dates_from_eindpunt(excel_path, eindpunt_value=eindpunt_value)
    adjusted_count -= days_off
    for date in dates:
        print(date)
    print(f"Total days you drove to work in {month_name}: {len(dates)}")
    print(f"Actual number of working days in {month_name}: {business_days_count}")
    print(f"Total number of days you worked from home: {adjusted_count}")
    print(f"Total number of days off: {days_off}")
    print("If you had any additional off days during this period please subtract them yourself.")