I track the hours that I have worked on something in an Excel sheet (actually Google Spreadsheet, but semantics). This is tracked in the following format:
Date | Start (time) | End (time) | Description
I can download this as a CSV and my program will run through it and tell me how many hours I've worked in total and per month. It can also calculate the tax that will go over it. (Keep in mind, these are Dutch taxes).
CSV example:
Date,Start,End,Description
2016-11-11,16:00,16:45,Demo
hours.py:
#!/usr/bin/env python
# -*- encoding: utf-8 -*-
"""
This script reads the worked hours from CSV file generated from
Google docs / Excel and generates statistics from it.
The CSV file should be in format:
Date,Start,End,Description
"""
import argparse
import csv
import os
from models import Stats
def hours_minutes(td):
"""Converts timedelta object into hours and minutes"""
return (td.seconds // 3600) + (td.days * 24), (td.seconds // 60) % 60
def calculate_salary(rate, td, tax=0):
"""
Calculates the salary based on the amount of time worked.
Tax is deduced from the total amount, not the hourly rate.
:rate: The hourly rate
:td: timedelta with the amount of time worked.
"""
hours, minutes = hours_minutes(td)
total = hours * rate
if minutes: # to prevent ZeroDivisionError
total += (minutes / 60.0) * rate
if tax:
total = total - (total / 100 * tax)
return total
def print_salary(args, total_time, prepend=''):
"""
Prints the total salary and the total salary with the taxes taken off.
:args: The command line args from argparse
:total_time: Timedelta that will be used to print the number of hours
:prepend: String to prepend to the print out. For example '\t' for indent.
"""
print(prepend + 'Total salary: €{salary:.2f}'.format(
salary=calculate_salary(args['rate'], total_time)))
print(prepend + 'Total salary taxed: €{salary:.2f} tax: {tax:.2f}%'.format(
salary=calculate_salary(args['rate'], total_time, args['tax']),
tax=args['tax']))
def print_date(month, total_time):
"""Print the total time worked for a specific date"""
hours, mins = hours_minutes(total_time)
print('Time worked in {date}: {hours}:{mins:02d}'
' hours ({time})'.format(
date=month,
hours=hours,
mins=mins,
time=total_time)
)
def parse_csv(args):
"""
Opens the CSV file and gathers statistics.
"""
stats = Stats()
with open(args['file']) as csvfile:
reader = csv.DictReader(csvfile)
for row in reader:
stats.add(
(row['Date'], row['Start'], row['End'], row['Description'])
)
return stats
def get_parser():
"""Creates and returns the parser for command line arguments."""
parser = argparse.ArgumentParser(
description='Read hour sheet CSV and generate statistics')
parser.add_argument('file', metavar='FILE', type=str,
help='The CSV file to read the data from.')
parser.add_argument('-r', '--rate', type=float, dest='rate',
help='The hourly rate. Tax will be subtracted later. '
'If given, returns hours * rate')
parser.add_argument('-t', '--tax', type=float, dest='tax', default=36.3668,
help='The tax rate to use. Default is 21 percent.'
' Tax is deducted from the hourly wage, rather '
'than the total')
parser.add_argument('-m', '--monthly', type=bool, dest='monthly',
default=False, help='Sorts the CSV data into monthly '
'statistics, and saves the results in '
'monthly_stats.txt')
parser.add_argument('-g', '--get-date', type=str, dest='month',
help='Get the statistics of a specific month'
' Expects \'year-month-day\' format')
return parser
def command_line_runner():
"""
Using the given CSV file and any command line options, sort all the
worked hours into years, months and days. Print out all that is
requested by the user. Total time will always be printed
"""
parser = get_parser()
args = vars(parser.parse_args())
if not os.path.isfile(args['file']):
print('CSV file does not exist. Stopping.')
return
stats = parse_csv(args)
total_time = stats.get_total_time()
hours, minutes = hours_minutes(total_time)
print('Total time worked: {hours}:{mins:02d} hours ({time})'.format(
hours=hours, mins=minutes, time=total_time))
if args['rate']:
print('-' * 40)
print_salary(args, total_time)
if args['monthly']:
print('-' * 40)
for month in stats.get_months():
total_time = month.get_total_time()
print_date(month.month, total_time)
if args['rate']:
print_salary(args, total_time, '\t')
if args['month']:
print('-' * 40)
try:
total_time = stats.get_stats_for_date(args['month'])
print_date(args['month'], total_time)
if args['rate']:
print_salary(args, total_time, '\t')
except ValueError:
print('No work found for {}'.format(args['month']))
if __name__ == '__main__':
command_line_runner()
models.py:
"""
All the classes needed to store statistics
"""
from datetime import datetime, timedelta
import operator
from functools import reduce
class DateMixin:
def _calculate_time(self, start, end):
"""
Calculates the difference between the two, and returns
the difference between the two as a timedelta
:start: Start time in format hours:minutes
:end: End time in format hours:minutes
"""
time_format = '%H:%M'
tdelta = (datetime.strptime(end, time_format) -
datetime.strptime(start, time_format))
if tdelta.days < 0:
tdelta = timedelta(days=0, seconds=tdelta.seconds,
microseconds=tdelta.microseconds)
return tdelta
def _string_to_date(self, string, format='%Y-%m-%d'):
"""
Turns a string into a date. Accepts a format,
but defaults to year-month-day
"""
return datetime.strptime(string, format).date()
class DailyStats(DateMixin):
"""Represents a single day, which holds all the entries worked on
this specific day."""
def __init__(self, row):
self.days = []
self.day = row[0]
self.add(row)
def add(self, row):
"""
Add a row with the same day to this day's stats. Raises ValueError
if the given row's day, does not match the day of this object.
"""
if not row[0] == self.day:
raise ValueError(
'Cannot add day {given}; not the same'
'day as this object {day}'.format(
given=row[0],
day=self.day
)
)
self.days.append(row)
def get_total_time(self):
"""
Get all the time that was worked on this day. Returns a timedelta
"""
return reduce(
operator.add,
[self._calculate_time(x[1], x[2]) for x in self.days])
def __str__(self):
return self.day
def __repr__(self):
return self.__str__()
class MonthlyStats(DateMixin):
"""Represents a single month, which holds all the days worked."""
def __init__(self, row):
"""
Create a new Monthly statistic. Accepts a row to add to the stats.
"""
self.days = []
self.month = row[0][:7]
self.add(row)
def add(self, row):
"""
Add a row to this month. Raises ValueError if the given row's month,
does not match the month of this object.
"""
if not self.month == row[0][:7]:
raise ValueError(
'Cannot add row to this month, as {given} '
'does not match {month}'.format(
given=row[0][:7],
month=self.month
)
)
exists = self.get(row[0])
if exists:
exists[0].add(row)
else:
day = DailyStats(row)
self.days.append(day)
def get_total_time(self):
"""Returns the total time worked this month"""
return reduce(operator.add, [x.get_total_time() for x in self.days])
def get(self, day):
"""Given a date string in year-month-day format, get the day."""
return [x for x in self.days if x.day == day]
def __str__(self):
return self.month
def __repr__(self):
return self.__str__()
class YearlyStats(DateMixin):
"""Represents a single year, which holds all the month objects."""
def __init__(self, row):
self.months = []
self.year = self._string_to_date(row[0]).year
self.add(row)
def add(self, row):
"""
Add a row to this year. Raises ValueError if the given row's year,
does not match the year of this object.
"""
date = self._string_to_date(row[0])
if not self.year == date.year:
raise ValueError(
'Cannot add row to this year, as {given} '
'does not match {month}'.format(
given=date.year,
month=self.year
)
)
exists = self.get(row[0][:7])
if exists:
exists.add(row)
else:
month = MonthlyStats(row)
self.months.append(month)
def get_total_time(self):
"""Returns the total time worked in this year"""
return reduce(operator.add, [x.get_total_time() for x in self.months])
def get(self, month):
"""Given a string in year-month format, get the month"""
res = [x for x in self.months if x.month == month]
if res:
return res[0]
def __str__(self):
return str(self.year)
def __repr__(self):
return self.__str__()
class Stats(DateMixin):
"""
The most top-level statistics class. Holds all the years in which
were worked, and provides top-level methods for fetching relevant data.
"""
def __init__(self):
self.years = []
def add(self, row):
"""
Expects a row from the CSV file as a tuple
in the format (Date, Start, End, Description)
Adds this row to the list of statistics
"""
exists = self.get(row[0][:4])
if exists:
exists.add(row)
else:
year = YearlyStats(row)
self.years.append(year)
def get_total_time(self):
"""Returns the total time worked"""
return reduce(operator.add, [x.get_total_time() for x in self.years])
def get(self, year):
"""Return a given year"""
res = [x for x in self.years if str(x.year) == str(year)]
if res:
return res[0]
def get_stats_for_date(self, date):
"""
Given a full or partial date in year-month-day format, return
the corresponding Stats object.
"""
stats = self.get(date[:4]) # stats == year
if stats and len(date) > 4:
stats = stats.get(date[:7]) # stats == year+month
if stats and len(date) > 7:
stats = stats.get(date) # stats == day
if stats: # return here, because DailyStats.get returns list
return reduce(
operator.add, [x.get_total_time() for x in stats])
if stats is None or not stats:
raise ValueError('Not found')
return stats.get_total_time()
def get_months(self):
"""Return every worked month in a single list."""
return [month for year in self.years for month in year.months]
def get_days(self):
"""Return every worked day in a single list."""
return [day for year in self.years
for month in year.months for day in month.days]
