how do I calculate average value of columns of a file in Python?

Question

import pandas as pd

my_file = pd.read_csv("file.tsv", sep="\t")

my_file.head()

my_file['item_price'].mean()

I could easily do this by using Pandas. However how could I do it without using Pandas?

>how do I loop file's columns

>how do I add them up?

order_id quantity item_name item_price

0 1 pizza 34.2

1 1 chips 2.3

2 2 cola 2.0

3 3 burger 10.2

4 1 tart 6.43

Answer 1

In order to do this without Pandas, it helps to think about what Pandas is actually doing for you behind the scenes. It opens the file, splits every line into pieces, keeps track of which piece belongs to which column, turns those pieces from text into real numbers, and then averages them. Without Pandas we just do those same steps ourselves.

The one thing to know before we start is that when you read a file in Python, every line comes in as a string. So 34.2 in your file is the text "34.2" and not a number yet, which is usually where people get stuck, because you cannot add up strings.

Here is one way to do it:

total = 0

count = 0

with open("file.tsv") as f:

header = f.readline().strip().split("\t")

col = header.index("item_price")

for line in f:

parts = line.strip().split("\t")

total += float(parts[col])

count += 1

average = total / count

print(average)

Let me walk through what is happening. That first readline pulls off the header row by itself, and splitting it on the tab character gives us a list of the column names. Then header.index("item_price") tells us what position that column sits in. That is really the answer to your first question. You do not loop through the columns, you find the index of the one you want a single time and then use it on every row.

After that, the for loop goes through the remaining lines one at a time. For each line, split gives us a list of that row's values, parts[col] pulls out just the price, and float() turns that piece of text into a number we can actually work with. That is your second question, adding them up is just total += that number, and count goes up by one each time so we know how many rows we added.

So, our final answer is that the average is total divided by count, and if you ever want a different column you only have to change the name inside header.index.

Answer 2

I would recommend creating a class...

class Order:

def __init__(self, id, qty, name, price):

self.__orderID = id

self.__quantity = qty

self.__itemName = name

self.__price = price

def GetName(self):

return self.__itemName

def GetPrice(self):

return self.__price

def GetQty(self):

return self.__quantity

def GetID(self):

return self.__orderID

def GetSubtotal(self):

return float(self.__quantity) * float(self.__price)

def Serialize(self):

outbuff = str(self.__orderID) + " "

outbuff = outbuff + str(self.__quantity) + " "

outbuff = outbuff + str(self.__itemName) + " "

outbuff = outbuff + str(self.__price)

return(outbuff)

############################################################

#creates the objects

N=5

orderRec1 = Order(0,1,"pizza",34.2)

orderRec2 = Order(1,1,"chips",2.30)

orderRec3 = Order(2,2,"soda",2)

orderRec4 = Order(3,3,"burger",10.2)

orderRec5 = Order(4,1,"tart",6.43)

#prints them out

print(orderRec1.Serialize())

print(orderRec2.Serialize())

print(orderRec3.Serialize())

print(orderRec4.Serialize())

print(orderRec5.Serialize())

#stores them in the array list

orders = [orderRec1,orderRec2,orderRec3,orderRec4,orderRec5]

grandTotal = 0

for order in orders:

curSubtotal = order.GetSubtotal()

grandTotal = float(grandTotal)+ float(curSubtotal)

print("the grand total is " + str(grandTotal))

print(" the average is " + str(float(grandTotal)/int(N)))