Python from zeroBeginnerLesson 1067 min read

Working with data files

Most real data comes as tables: marks lists, timetables, price lists. Learn to read CSV data with Python's csv module and calculate totals and averages from it.

✦ Ask the tutor🧪 Lab · 15 min📝 Lesson test
Lesson in motion

In 60 seconds

Working with data files

Most real data comes as tables: marks lists, timetables, price lists. Learn to read CSV data with Python's csv module and calculate totals and averages from it.

1/4
In simple words
A class register has rows and columns: one row per student, one column per subject. A CSV file is that same table saved as plain text, with commas between the columns. Python can read it and do the adding up for you, even for thousands of rows.

What a CSV file looks like

CSV stands for comma-separated values. The first line is usually the header: the column names. Every line after it is one row. Spreadsheet apps can save a sheet as CSV, and many websites let you download data this way.
A small CSV of markstext
name,maths,science,english
Asha,78,85,90
Ravi,64,72,58
Imran,91,88,76

No files here? Use StringIO

The browser runner in this course cannot open files from your computer. So we put the CSV text into a string, and wrap it with io.StringIO. That makes the string behave like an open file. On your own computer you would use open("marks.csv") instead, and the rest of the code stays the same.

Reading rows with csv.DictReader

The csv module comes with Python. csv.DictReader reads the header, then gives you each row as a dictionary, with the column names as keys.
Python: read marks from CSV textpython
import csv
import io

data = """name,maths,science,english
Asha,78,85,90
Ravi,64,72,58
Imran,91,88,76"""

reader = csv.DictReader(io.StringIO(data))
for row in reader:
    print(row["name"], "scored", row["maths"], "in maths")
Watch out
Everything read from a CSV is text, even the numbers. row["maths"] is "78", not 78. Convert with int() or float() before you add or compare, or you will get a TypeError, or text stuck together like "7864".

Calculating from the rows

Python: class average in mathspython
import csv
import io

data = """name,maths,science,english
Asha,78,85,90
Ravi,64,72,58
Imran,91,88,76"""

total = 0
count = 0
for row in csv.DictReader(io.StringIO(data)):
    total += int(row["maths"])
    count += 1
print(f"Maths average: {total / count:.1f}")

Train times: working with hours and minutes

Times like 06:10 are text too. A simple trick: turn them into minutes after midnight, then subtract. This made-up timetable shows how.
Python: journey times from a timetablepython
import csv
import io

timetable = """train,departs,arrives
Morning Express,06:10,09:45
Coastal Passenger,07:30,12:05
Evening Mail,17:20,21:00"""

def to_minutes(hhmm):
    hours, minutes = hhmm.split(":")
    return int(hours) * 60 + int(minutes)

for row in csv.DictReader(io.StringIO(timetable)):
    mins = to_minutes(row["arrives"]) - to_minutes(row["departs"])
    print(f"{row['train']}: {mins // 60} h {mins % 60} min")
This simple version assumes each train arrives on the same day it leaves. A train that runs past midnight would need an extra rule. Spotting limits like this is part of good programming.

The usual steps for any data task

  1. 1

    Look at the data

    What are the columns? Are there blank or odd values?
  2. 2

    Read it

    csv.DictReader gives one dictionary per row.
  3. 3

    Convert

    Turn number text into int or float.
  4. 4

    Calculate

    Totals, averages, the highest, counts: using loops, lists and dictionaries.
  5. 5

    Check the answer

    Work out one row by hand and compare.

Where this leads

You have now used every building block that data work needs: variables, decisions, loops, lists, dictionaries, functions and reading tables. Machine learning starts exactly here: a table of examples, read row by row, turned into numbers. When you are ready, continue with the Machine learning fundamentals track, where you will use tables like these to teach a computer to make predictions. There you will also meet pandas, a library that reads a CSV into a table in one line.
Try this. Add a fourth student to the marks CSV and run the maths average again. Then change the code to find the science average. Well done: you just did real data analysis! 🎉

Watch and read more

csv: CSV file reading and writingpython.org · official docs · readingio: StringIO and other streamspython.org · official docs · reading

Lab

Two functions that read a marks CSV and give each student's total and any subject's average.

~15 min

The problem

Finish the two functions. Each one takes CSV text like marks_csv (a name column, then one column per subject):

  1. totals(csv_text) returns a dictionary of each student's total over all subjects, for example {"Asha": 253, …}. Tip: in each row, add up every column except name, converting each value with int().
  2. subject_average(csv_text, subject) returns the class average for one subject, such as "maths", as a number.

Your functions should work for any CSV in this shape, even with different subjects or more students. Press Run, check one answer by hand, then press Check. Congratulations on finishing the track! 🎉

Practice lab

Edit the code and run it. Python runs in your browser — nothing is sent to our server.

Output

No output. Add print(...) to see values.

You are done when

Hard questions

Try to answer before you reveal. If you can answer these, you understood the lesson.

Q1Your friend's code gives totals like <code>"788590"</code>. What went wrong, and how do you fix it?Reveal

They added the values as text, so + joined the strings. Every value from a CSV is a string. Convert each one first: total += int(row[subject]).

Q2One student's science mark is missing (an empty cell). What should subject_average do, and why not just count it as 0?Reveal

Counting a missing mark as 0 would unfairly pull the average down, as if the student scored nothing. A better rule is to skip empty cells: add up only the real marks and divide by how many real marks there were. Whatever rule you choose, write it down, because it changes the answer.

Please sign in to continue.

Questions people ask

What if a value in the CSV has a comma inside it, like an address?

Proper CSV puts such values inside double quotes: "12, MG Road". The csv module handles this for you. That is one reason to use it instead of splitting each line on commas yourself.

What is the difference between csv.reader and csv.DictReader?

csv.reader gives each row as a list, so you use positions: row[1]. csv.DictReader gives each row as a dictionary, so you use names: row["maths"]. Names are easier to read and do not break if the columns move.

How do I handle an empty cell?

An empty cell comes in as an empty string "", and int("") gives an error. Check first, for example if row["maths"] != "":, and decide what the right rule is: skip it, or count it as missing. Do not quietly treat it as 0 unless that is really correct.

Can I read Excel files the same way?

Not with the csv module. The simplest way is to save the sheet as CSV from the spreadsheet app. Libraries such as pandas can read Excel files directly, but that needs extra packages.

Lesson test

5 questions. Get 3 right (60%) to pass and complete this lesson.

Sign in to take the test and save your progress