Northwest US, Canada, Euro Roadtrip Costs

trip.csv

trip_costs.py

#!/usr/bin/env python

import pandas
import numpy as np

# load in the data and change the Amount from a string to a float
data = pandas.read_csv('trip.csv')
data['Amount'] = data['Amount'].astype(float)

# this fails if someone didn't pay but only owed, not the case here though
peeps = list(data['Who Paid'].unique())

# make the debtor columns boolean
for person in peeps:
    data[person].replace({'TRUE': True, np.nan: False}, inplace=True)

# These are average conversion factors over the trip time for (E)uros,
# (C)anadian Dollars, (D)ollars, and Swiss (F)ranks.
conversions = {'E': 1.2401,
               'C': 1.0002,
               'D': 1.0,
               'F': 1.0323}

# intialize with zero debt the dictionary top level gives the debtor, where as
# the second level is who they owe and how much
debts = {person: {p : 0.0 for p in peeps} for person in peeps}

# add columns giving the number of debtors in the row and the total amount
# spent in dollars
numDebtors = []
spentDollars = []
for row in data.iterrows():
    r = row[1]
    x = 0
    numDebtors.append(sum([x + 1 for person in peeps if r[person] == True]))
    spentDollars.append(r['Amount'] * conversions[r['Currency']])
data['Number of Debtors'] = numDebtors
data['Amount in Dollars'] = spentDollars

# tally the debts for each person
for row in data.iterrows():
    r = row[1]
    for person in peeps:
        if r[person] == True:
            if person != r['Who Paid']:
                debts[person][r['Who Paid']] += r['Amount in Dollars'] / r['Number of Debtors']

# now cancel debts to people who owe each other
for owed in peeps:
    for debtor in peeps:
        if debts[owed][debtor] > debts[debtor][owed]:
            debts[owed][debtor] = debts[owed][debtor] - debts[debtor][owed]
            debts[debtor][owed] = 0.0
        else:
            pass