Finding a sum of numbers from a column in text file python

Question

I want to know how to find the total sum of number from a column in a text file in python.

My file:

123  Hammer  20  36
124  Knife   10  10
125  Rod     90  20

I want to add the 4th column in the text file. That is the column that starts with 36. The function should return the sum of the column that is 66.

I don't have any code yet as I am still stuck in this problem and thinking of a way to solve it.

I am having error in some places i think it is due to my code. I need assistance in it.

# Creating class Hardware to store the linked list data in to the class, Properties like get and set
class Hardware:
    def __init__(self,barcode,description,price,quantity): # initating data
        self.barcode=barcode
        self.description=description
        self.price=price
        self.quantity=quantity
        self.next=None

    def getData(self):#function to get data
        return self.barcode,self.description,self.price,self.quantity

    def getNext(self):#function to get the next data
        return self.next

    def setData(self,newBarcode,newDescription,newPrice,newQuantity):#function to set the data
        self.barcode=newBarcode
        self.description=newDescription
        self.price=newPrice
        self.quantity=newQuantity

    def setNext(self,newNext):#function to set the next data
        self.next=newNext

# LinkedList class for manipulation of data that is add, display and update
class LinkedListHardware:
    def __init__(self): # initating data
        self.head=None

    def isEmpty(self): #checks if the data is empty
        return self.head==None

    def ReadFile(self): # Reads a text file called Hardware
        Hardwarefile=open('Hardware.txt','r')
        return Hardwarefile.read()

    def add(self,itemBarcode,itemDescription,itemPrice,itemQuantity): #Adds data to the linked list and also writes to the file
        temp=Hardware(itemBarcode,itemDescription,itemPrice,itemQuantity)
        temp.setNext(self.head)
        self.head=temp
        HardwareItems=(itemBarcode,itemDescription,itemPrice,itemQuantity)
        Hardwarefile=open('Hardware.txt','a+')
        Hardwarefile.write('\n')
        for items in HardwareItems:
            Hardwarefile.write(str(items)+'\t')
        return "Added Successfully"
        Hardwarefile.close()


    def display(self,itemBarcode): # Displays a line based on the Barcode the user enters
        current=self.head
        with open('Hardware.txt','r') as f:
            found=False
            for line in f:
                lines=line.split()
                if itemBarcode in lines:
                    found=True
                    return line
            if not found:
                 return "No such Barcode"
            f.close()        

    def update(self,itemBarcode,itemDescription,itemPrice,itemQuantity): #update data of hardware item
            current=self.head
            with open('Hardware.txt','r+') as f:
                found=False
                for lines in f:
                    line=lines.split()
                    if itemBarcode not in line:
                        array=[]
                        array.append(line)
                        st=[x[0]+'\t'+x[1]+'\t'+x[2]+'\t'+x[3] for x in array]
                        st=''.join(st)

                        with open('Hardwareupdate.txt','a+') as outfile:
                            outfile.write('\n'+str(st))
                            found=True
                        HardwareItems=(itemBarcode,itemDescription,itemPrice,itemQuantity)
                        Hardwarefile=open('Hardwareupdate.txt','a+')
                        Hardwarefile.write('\n')
                        for items in HardwareItems:

                            Hardwarefile.write(str(items)+'\t')

                return "Added Successfully"


            if not found:
                    return "No such Transaction"



# Creating class Transaction to store the linked list data in to the class, Properties like get and set
class Transaction:
    def __init__(self,invoiceNumber,Barcode,totalPrice,Quantity): # initating data
        self.invoiceNumber=invoiceNumber
        self.Barcode=Barcode
        self.totalPrice=totalPrice
        self.Quantity=Quantity

        self.next=None

    def getData(self): #function to get data
        return self.invoiceNumber,self.Barcode,self.totalPrice,self.Quantity

    def getNext(self): #function to get the next data
        return self.next

    def setData(self,newinvoiceNumber,newBarcode,newtotalPrice,newQuantity): #function to set the data
        self.invoiceNumber=newinvoiceNumber
        self.Barcode=newBarcode
        self.totalPrice=newtotalPrice
        self.Quantity=newQuantity


    def setNext(self,newNext): #function to set the next data
        self.next=newNext

# LinkedList class for manipulation of data that is add, search, update and delete
class LinkedListTransaction:

    # initating data
    def __init__(self):
        self.head=None

    #checks if the data is empty
    def isEmpty(self):
        return self.head==None

    #Adds data to the linked list and also writes to the file
    def add(self,iteminvoiceNumber,itemBarcode,itemQuantity,itemtotalPrice):
        temp=Transaction(iteminvoiceNumber,itemBarcode,itemQuantity,itemtotalPrice)
        temp.setNext(self.head)
        self.head=temp
        TransactionItems=(iteminvoiceNumber,itemBarcode,itemQuantity,itemtotalPrice)
        Transactionfile=open('Transaction.txt','a+')
        Transactionfile.write('\n')
        for items in TransactionItems:
            if iteminvoiceNumber != ' ':
                Transactionfile.write(str(items)+'\t')
        return "Added Successfully"

    # Displays the record of data by receving the item invoice number
    def display(self,iteminvoiceNumber):
            current=self.head
            with open('Transaction.txt','r') as f:
                found=False
                for line in f:
                    lines=line.split()
                    if iteminvoiceNumber in lines:
                        found=True
                        return line
                if not found:
                     return "No such Transaction"

    # Removes sepcific data from the file (rewrites data that does not contain the data which user entered and writes to another file)
    def remove(self,iteminvoiceNumber):
        current=self.head
        with open('Transaction.txt','r') as f:
            found=False
            for lines in f:
                line=lines.split()

                if iteminvoiceNumber not in line:
                    array=[]
                    array.append(line)
                    st=[x[0]+'\t'+x[1]+'\t'+x[2]+'\t'+x[3] for x in array]
                    st=''.join(st)
                    with open('Transactionlatest.txt','a+') as outfile:
                        outfile.write('\n'+str(st))
                        found=True

            if not found:
                    return "No such Transaction"

    # Updates Data from the text file. Copies data to new file and then update whatever user enters
    def update(self,iteminvoiceNumber,itemBarcode,itemQuantity,itemtotalPrice):
            current=self.head
            with open('Transaction.txt','r+') as f:
                found=False
                for lines in f:
                    line=lines.split()
                    if iteminvoiceNumber not in line:
                        array=[]
                        array.append(line)
                        st=[x[0]+'\t'+x[1]+'\t'+x[2]+'\t'+x[3] for x in array]
                        st=''.join(st)

                        with open('Transactionupdate.txt','a+') as outfile:
                            outfile.write('\n'+str(st))
                            found=True
                        TransactionItems=(iteminvoiceNumber,itemBarcode,itemQuantity,itemtotalPrice)
                        Transactionfile=open('Transactionupdate.txt','a+')
                        Transactionfile.write('\n')
                        for items in TransactionItems:

                            Transactionfile.write(str(items)+'\t')

                return "Added Successfully"


            if not found:
                    return "No such Transaction"


    def TotalSales(self):
        mysum=0
        with open('Transaction.txt','r') as f:
            for line in f:
                mysum+=int(line.split()[3])
            return mysum





HardwareList=LinkedListHardware()

TransactionList=LinkedListTransaction()
print(TransactionList.TotalSales())
print(TransactionList.remove(input()))
'''
print("Hardware Shop\n")
print("[1]Update hardware item quantity in hand or price per unit\n")
print("[2]Add hardware item\n")
print("[3]Display hardware item\n")
print("[4]Add Sales Transaction\n")
print("[5]Remove Sales Transaction\n")
print("[6]Edit Sales Transaction\n")
print("[7]All Sales Transaction\n")        

MainInput=input("Enter Selection: ")

HardwareList=LinkedListHardware()

TransactionList=LinkedListTransaction()


if MainInput=='1':
    print("\nUpdate hardware item quantity in hand or price per unit")
    print(HardwareList.update(input("Enter Barcode: "),input("Enter Description: "),input("Enter Price: "),input("Enter Quantity: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")


if MainInput=='2':
    print("\nAdd hardware item")
    print(HardwareList.add(input("Enter Barcode: "),input("Enter Description: "),input("Enter Price: "),input("Enter Quantity: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")

if MainInput=='3':
    print("\nDisplay hardware item")
    print(HardwareList.display(input("Enter Barcode: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")

if MainInput=='4':
    print("\nAdd Sales Transaction")
    print(TransactionList.add(input("Enter Invoice Number: "),input("Enter Hardware Barcode: "),input("Enter Quantity: "),input("Enter Total Price: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")

if MainInput=='5':
    print("\nRemove Sales Transaction")
    print(TransactionList.remove(input("Enter Invoice Number: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")

if MainInput=='6':
    print("\nEdit Sales Transaction")
    print(TransactionList.update(input("Enter Invoice Number: "),input("Enter Hardware Barcode: "),input("Enter Quantity: "),input("Enter Total Price: ")))
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")

if MainInput=='7':
    print("\nAll Sales Transaction")
    print(TransactionList.TotalSales())
    SubInput=int(input("Enter 0 to exit and 1 to continue "))
    if SubInput==0:
        quit()
    if SubInput==1:
        print("Hardware Shop\n")
        print("[1]Update hardware item quantity in hand or price per unit\n")
        print("[2]Add hardware item\n")
        print("[3]Display hardware item\n")
        print("[4]Add Sales Transaction\n")
        print("[5]Remove Sales Transaction\n")
        print("[6]Edit Sales Transaction\n")
        print("[7]All Sales Transaction\n")        

        MainInput=input("Enter Selection: ")



'''

The error is receive

mysum+=int(line.split()[3])
IndexError: list index out of range

Answer 1

you can use Panda for that:

http://pandas.pydata.org/pandas-docs/stable/io.html

http://pandas.pydata.org/pandas-docs/stable/generated/pandas.DataFrame.sum.html

import pandas as pd
df = pd.read_csv('myfilename.csv')
df[4] = df[3].sum(axis=0)

Answer 2

This should work:

mysum = 0
with open('myfilename','r') as f:
    for line in f:
        mysum += int(line.split()[3])

line.split() will turn "123 Hammer 20 36" into ["123", "Hammer", "20", "36"]. We take the fourth value 36 using the index [3]. This is still a string, and can be converted to an integer using int or a decimal (floating-point) number using float.

EDIT:

to check for empty lines add the condition if line: in the for loop. In your particular case you might do something like:

for line in f:
    words = line.split()
    if len(words)>3:
        mysum += int(words[3])

Answer 3

This is python that opens a file, maps each line to the integer at the end, and then sums the integers:

first open the file:

with open("my_file.txt") as f:
    # .readlines() method gives you a list of the lines in the file.
    lines = f.readlines()

Next we have to write a function that returns the last column of any given line as an integer.

def extract_last_int(line):
    return int(line.split()[-1])

# apply 'extract_last_int' to each line, and then sum the results.
print sum(map(extract_last_int, lines))

• the .split() method will by default split on spaces, giving you a list of each column in this line.

• [-1] tells python to get the last element of the list.

• map takes a function and a list and calls the function once for each item of the list. The return value for map is a list of the results of each of these calls. In this case, the return value of map is a list of integers which are in the last column of the file.

• finally, sum adds all these integers together.

That's it!

If you're curious, you can do the same thing with awk in one line:

awk '{i+=$NF} END {print i}' my_file.txt