Creating a table from a text file and adding a column

Question

I'm having some trouble adding certain values from a table created on Python.
My text file looks like:

E5345,22/09/2015,C106,815,A,400
E5348,23/09/2015,C109,370,A,200
E5349,25/09/2015,C110,480,A,250
E5353,28/09/2015,C114,272,A,200
E5363,01/10/2015,C124,930,A,500
E5364,02/10/2015,C125,915,A,800
E5365,02/10/2015,C126,1486,A,1486
E5366,03/10/2015,C127,576,E,0
E5367,03/10/2015,C128,427,A,350
E5373,10/10/2015,C134,1023,A,550

I wish to create a table on python using these values and create another column, titled Outstanding Amount. I've managed to do this, but I would like to find the total sum of the outstanding. This is found by doing the fourth column (finaltotal) subtract the sixth column. (amountpaid).
My code so far is:

headers = ["| Estimate Number", "| Date", "| Customer Number", "| Final Total", "| Status", "| Amount Paid", "| Outstanding Amount"]
print("    ".join(headers))
print ("\n-------------------------------------------------------------------------------------------------------------------------")
data = open("paintingJobs.txt", "r")
info=data.readlines()
data.close()
for li in info:
        line = li.strip().split(",")
        status=line[4]
        finaltotal=int(line[3])
        amountpaid=int(line[5])
        subtotal = int(line[3]) - int(line[5])
        outstanding = (finaltotal) - (amountpaid)
        line.append(str(subtotal))
        if (amountpaid) < (finaltotal) and status == "A":
                for i, word in enumerate(line):
                    print(word.ljust(len(headers[i - (i > 20)])), end="     " * ((i - (i > 20)) != len(headers) - 1))
                print()
                print ("-------------------------------------------------------------------------------------------------------------------------")

    print ("\nThe total revenue is")

My ideal output is:

| Estimate Number    | Date    | Customer Number    | Final Total    | Status    | Amount Paid    | Outstanding Amount

-------------------------------------------------------------------------------------------------------------------------
E5345                 22/09/2015     C106                  815               A            400               415
-------------------------------------------------------------------------------------------------------------------------
E5348                 23/09/2015     C109                  370               A            200               170
-------------------------------------------------------------------------------------------------------------------------
E5349                 25/09/2015     C110                  480               A            250               230
-------------------------------------------------------------------------------------------------------------------------
E5353                 28/09/2015     C114                  272               A            200               72
-------------------------------------------------------------------------------------------------------------------------
E5355                 29/09/2015     C116                  530               A            450               80
-------------------------------------------------------------------------------------------------------------------------
E5363                 01/10/2015     C124                  930               A            500               430
-------------------------------------------------------------------------------------------------------------------------
E5364                 02/10/2015     C125                  915               A            800               115
-------------------------------------------------------------------------------------------------------------------------
E5367                 03/10/2015     C128                  427               A            350               77
-------------------------------------------------------------------------------------------------------------------------
E5373                 10/10/2015     C134                  1023              A            550               473
-------------------------------------------------------------------------------------------------------------------------

The total outstanding is
£2062

The total outstanding is found by finding the sum of the outstanding amounts. Outstanding amount is found by finaltotal minus amountpaid. Any help would be much appreciated.

Answer 1

Why don't you use the Pandas library? Suppose your initial table is in a csv file called example.csv. You can do this in very few steps, like so:

from pandas import read_csv

df = read_csv('~/Documents/example.csv', header=None)
df.columns = ["Estimate Number", "Date", "Customer Number", "Final Total", "Status", "Amount Paid"]
df['Outstanding Amount'] = df['Final Total'] - df['Amount Paid']

Then you have options for your output. You could output back to a csv file:

df.to_csv()

or print it:

table_as_text = df.to_string()
print(table_as_text)

which will give you:

Estimate Number        Date Customer Number  Final Total Status    Amount Paid  Outstanding Amount
0           E5345  22/09/2015            C106          815      A          400                 415
1           E5348  23/09/2015            C109          370      A          200                 170
2           E5349  25/09/2015            C110          480      A          250                 230
3           E5353  28/09/2015            C114          272      A          200                  72
4           E5363  01/10/2015            C124          930      A          500                 430
5           E5364  02/10/2015            C125          915      A          800                 115
6           E5365  02/10/2015            C126         1486      A         1486                   0
7           E5366  03/10/2015            C127          576      E            0                 576
8           E5367  03/10/2015            C128          427      A          350                  77
9           E5373  10/10/2015            C134         1023      A          550                 473

And to compute the sum, you would do:

outstanding_amount = df["Outstanding Amount"].sum()
print(outstanding_amount)

Check out http://pandas.pydata.org/pandas-docs/stable. Hope that helps!

Answer 2

Just use a variable outside the loop to keep track of the total. I'm excluding people that overpay, but if you want to deduct that from the total you can remove the if statement.

total = 0
for li in info:
    ...
    outstanding = (finaltotal) - (amountpaid)
    if outstanding > 0:
         total += outstanding

print 'The total outstanding is {}'.format(total)