Output keeps repeating numerous times when creating table from text file

Question

I'm having some trouble create a table using values from a text file. My text file looks like this:

e432,6/5/3,6962,c8429,A,4324
e340,2/3/5,566623,c1210,A,3201
e4202,6/5/3,4232,c8419,E,4232
e3230,2/3/5,66632,c1120,A,53204
e4202,6/5/3,61962,c8429,A,4322

I would like to generate a table containing arrays where: the last column (amountpaid) value is less than the third column's (final total), and if the fifth column's (status) equals 'A'. Outstanding total is found when subtracting final total and amount paid.

My code to generate a table is:

data = open("pJoptionc.txt", "r")
info=data.readlines()
data.close
for li in info:
        status=li.split(",")[4]
        finaltotal=int(li.split(",")[2])
        amountpaid=int(li.split(",")[5])
        totalrev=0
        headers = ["Estimate Number", "Date", "Final Total", "Customer Number", "Status", "Amount Paid", "Outstanding Amount"]
        print("    ".join(headers))
        for line in open("pJoptionc.txt", "r"):
            line = line.strip().split(",")
            line.append(str(int(line[2]) - int(line[5])))
            if line[2] == line[5] or line[4] in ("E"):
                continue
            for i, word in enumerate(line):
                print(word.ljust(len(headers[i - (i > 4)])), end="    " * ((i - (i > 4)) != len(headers) - 1))
            print()
            outstandingrev =(finaltotal) - (amountpaid) 
    totalrev += int(outstandingrev)
    print("The total amount of outstanding revenue is...")
    print("£",totalrev)

My desired output is

Estimate Number    Date    Final Total    Customer Number    Status    Amount Paid    Outstanding Amount
e432               6/5/3    6962           c8429              A         4324           2638           
e340               2/3/5    566623         c1210              A         3201           563422         
e3230              2/3/5    66632          c1120              A         53204          13428          
e4202              6/5/3    61962          c8429              A         4322           57640          
The total amount of outstanding revenue is...
£ 13428

However, when I run the code, the output is the table repeated over and over, and negative values are in the outstanding amount column. I'm using python 3.4.3.

Answer 1

The table is repeating over and over again because you read the file at the top of your script and then for every line in the file you reopen the file again.

Here is one approach using namedtuple:

from collections import namedtuple

# define a named tuple for storing each line (record)
fields = ['Estimate_Number', 'Date', 'Final_Total', 'Customer_Number', 'Status', 'Amount_Paid']
record = namedtuple('record', fields)

# first print the header
for field in fields:
    print('%-17s' % field.replace('_', ' '), end='')
print('Outstanding')

totalOutstanding = 0

# open the file using a context manager (the with keyword)
with open("pJoptionc.txt", "r") as data:

    for line in data:

        # strip the newline character, split on the comma and create a record
        row = record(*line.strip().split(','))

        # we can access each field by name now to make the code more readable  
        outstanding = int(row.Final_Total) - int(row.Amount_Paid)

        # print the output
        if outstanding:
            for field in fields:
                print('%-17s' % getattr(row, field), end='')
            print(outstanding)
            totalOutstanding += outstanding

print('Total Outstanding is... %s' % totalOutstanding)

References:

• namedtuple
• string formatting
• argument unpacking

Answer 2

well you read the file again in the loop so it repeats for every line you have in the table. try doing this: (note this time there is only 1 for loop on the file lines)

headers = ["Estimate Number", "Date", "Final Total", "Customer Number", "Status", "Amount Paid", "Outstanding Amount"]
print("    ".join(headers))
data = open("pJoptionc.txt", "r")
info=data.readlines()
data.close()
totalrev=0
for li in info:
    line = li.strip().split(",")
    status=line[4]
    finaltotal=int(line[2])
    amountpaid=int(line[5])

    line.append(str(int(line[2]) - int(line[5])))
    if line[2] == line[5] or line[4] in ("E"):
        continue
    for i, word in enumerate(line):
        print(word.ljust(len(headers[i - (i > 4)])), end="    " * ((i - (i > 4)) != len(headers) - 1))
    print()
    outstandingrev =(finaltotal) - (amountpaid) 
    totalrev += int(outstandingrev)
print("The total amount of outstanding revenue is...")
print("£",totalrev)

**side note: you might want to check out the csv module: https://docs.python.org/3/library/csv.html