Formatting data in a CSV file (calculating average) in python

Question

import csv
with open('Class1scores.csv') as inf:
    for line in inf:
        parts = line.split() 
        if len(parts) > 1:   
            print (parts[4])   


f = open('Class1scores.csv')
csv_f = csv.reader(f)
newlist = []
for row in csv_f:

    row[1] = int(row[1])
    row[2] = int(row[2])
    row[3] = int(row[3])

    maximum = max(row[1:3])
    row.append(maximum)
    average = round(sum(row[1:3])/3)
    row.append(average)
    newlist.append(row[0:4])

averageScore = [[x[3], x[0]] for x in newlist]
print('\nStudents Average Scores From Highest to Lowest\n')

Here the code is meant to read the CSV file and in the first three rows (row 0 being the users name) it should add all the three scores and divide by three but it doesn't calculate a proper average, it just takes the score from the last column.

Answer 1

Basically you want statistics of each row. In general you should do something like this:

import csv

with open('data.csv', 'r') as f:
    rows = csv.reader(f)
    for row in rows:
        name = row[0]
        scores = row[1:]

        # calculate statistics of scores
        attributes = {
           'NAME': name,
           'MAX' : max(scores),
           'MIN' : min(scores),
           'AVE' : 1.0 * sum(scores) / len(scores)
        }

        output_mesg ="name: {NAME:s} \t high: {MAX:d} \t low: {MIN:d} \t ave: {AVE:f}"
        print(output_mesg.format(**attributes))

Try not to consider if doing specific things is inefficient locally. A good Pythonic script should be as readable as possible to every one.

In your code, I spot two mistakes:

1. Appending to row won't change anything, since row is a local variable in for loop and will get garbage collected.

2. row[1:3] only gives the second and the third element. row[1:4] gives what you want, as well as row[1:]. Indexing in Python normally is end-exclusive.

And some questions for you to think about:

If I can open the file in Excel and it's not that big, why not just do it in Excel? Can I make use of all the tools I have to get work done as soon as possible with least effort? Can I get done with this task in 30 seconds?

Answer 2

Here is one way to do it. See both parts. First, we create a dictionary with names as the key and a list of results as values.

import csv


fileLineList = []
averageScoreDict = {}

with open('Class1scores.csv', newline='') as csvfile:
    reader = csv.reader(csvfile)
    for row in reader:
        fileLineList.append(row)

for row in fileLineList:
    highest = 0
    lowest = 0
    total = 0
    average = 0
    for column in row:
        if column.isdigit():
            column = int(column)
            if column > highest:
                highest = column
            if column < lowest or lowest == 0:
                lowest = column
            total += column    
    average = total / 3
    averageScoreDict[row[0]] = [highest, lowest, round(average)]

print(averageScoreDict)

Output:

{'Milky': [7, 4, 5], 'Billy': [6, 5, 6], 'Adam': [5, 2, 4], 'John': [10, 7, 9]}

Now that we have our dictionary, we can create your desired final output by sorting the list. See this updated code:

import csv
from operator import itemgetter


fileLineList = []
averageScoreDict = {} # Creating an empty dictionary here.

with open('Class1scores.csv', newline='') as csvfile:
    reader = csv.reader(csvfile)
    for row in reader:
        fileLineList.append(row)

for row in fileLineList:
    highest = 0
    lowest = 0
    total = 0
    average = 0
    for column in row:
        if column.isdigit():
            column = int(column)
            if column > highest:
                highest = column
            if column < lowest or lowest == 0:
                lowest = column
            total += column    
    average = total / 3
    # Here is where we put the emtpy dictinary created earlier to good use.
    # We assign the key, in this case the contents of the first column of
    # the CSV, to the list of values. 
    # For the first line of the file, the Key would be 'John'.
    # We are assigning a list to John which is 3 integers: 
    #   highest, lowest and average (which is a float we round)
    averageScoreDict[row[0]] = [highest, lowest, round(average)]

averageScoreList = []

# Here we "unpack" the dictionary we have created and create a list of Keys.
# which are the names and single value we want, in this case the average.
for key, value in averageScoreDict.items():
    averageScoreList.append([key, value[2]])

# Sorting the list using the value instead of the name.
averageScoreList.sort(key=itemgetter(1), reverse=True)    

print('\nStudents Average Scores From Highest to Lowest\n')
print(averageScoreList)

Output:

Students Average Scores From Highest to Lowest [['John', 9], ['Billy', 6], ['Milky', 5], ['Adam', 4]]