Data in files

Problem

Text, but you need numbers

The warehouse exports its products to a file. Each row is one product: name, price and stock, separated with commas. The first row says what is in each column:

products.csv

Name,Price,Stock
Bread,1.20,30
Milk,2.10,18
Coffee beans,18.90,12

You can already read the file. But to the program, the row is just text:

line = "Bread,1.20,30"
print(len(line))
print(line[6])

Output

13
1

It does not know that 1.20 is a price and 30 is a count. To work out what the goods are worth, you have to turn each row into something with fields: a Product object.

The idea

CSV: row to object

A file like this is called CSV (comma-separated values). Excel reads it, databases read it, any program reads it. A row becomes an object in three steps: split, turning each part into the type you need, and the constructor.

It is most convenient to make this a method in the class itself that does not need an object, but creates one: @staticmethod.

class Product:
    def __init__(self, name, price, stock):
        self.name = name
        self.price = price
        self.stock = stock

    @staticmethod
    def from_csv(line):
        f = line.split(",")
        if len(f) != 3:
            raise ValueError(f"expected 3 columns, got {len(f)}")
        return Product(f[0].strip(), float(f[1]), int(f[2]))

    def to_csv(self):
        return f"{self.name},{self.price},{self.stock}"

p = Product.from_csv("Coffee beans,18.90,12")
print(f"{p.name}: €{p.price:.2f} x {p.stock} = €{p.price * p.stock:.2f}")

Output

Coffee beans: €18.90 x 12 = €226.80

from_csv is the only place that knows what the row looks like. If the format changes, you change only that. The check for the number of columns raises ValueError with a clear message, instead of the program stopping later with an index out of the list. float and int raise ValueError by themselves if the text is not a number, and they remove the spaces around the number themselves.

What from_csv does with the row, step by step, with its body spelled out line by line:

line = "Coffee beans,18.90,12"
f = line.split(",")
name = f[0]
price = float(f[1])
stock = int(f[2])
p = Product(name, price, stock)
print(f"{p.name}: €{p.price} x {p.stock}")
print(f"€{p.price * p.stock:.2f}")

Step by step, in words

1. The row from the file is one string. To the program, the 18.90 in it is just four characters.
2. split cuts the row at every comma: a list of three strings.
3. The name stays a string.
4. float turns the string "18.90" into the number 18.9. Now you can do math with it.
5. int makes the whole number 12 from "12".
6. Product(...) creates a new object, __init__ puts the three values in its fields, and p points to it.
7. p.price prints as 18.9: the zero at the end was only in the text.
8. The price times the stock: now it is math with numbers.

See it

The whole file

You load all the products into a list and work out the value of the goods:

try:
    with open("products.csv", "w", encoding="utf-8") as file:
        file.write("Name,Price,Stock\nBread,1.20,30\nMilk,2.10,18\nCoffee beans,18.90,12\n")
    with open("products.csv", encoding="utf-8") as file:
        lines = file.read().splitlines()
    products = []
    header = True
    for line in lines:
        if header:
            header = False
            continue
        products.append(Product.from_csv(line))
    value = 0
    for p in products:
        value += p.price * p.stock
    print(f"{len(products)} products, stock worth €{value:.2f}")
except OSError as e:
    print(f"Cannot read products.csv: {e}")

Output

3 products, stock worth €300.60

• products starts as an empty list, and append adds each new product at its end. len(products) is how many there are.
• header is True only on the first pass. Then the row is skipped with continue.

There is more about lists in the module "Collections".

The idea

Bad rows

People and other programs write the files. Sooner or later a row comes with a missing column, a word instead of a number, or an empty row:

products.csv

Name,Price,Stock
Bread,1.20,30
Milk,2.10
Coffee,seven,4

Tea,3.50,25

One error should not stop the loading of everything else. Each row is in its own try, and the message gives the row number, so that someone can fix it:

try:
    with open("products.csv", "w", encoding="utf-8") as file:
        file.write("Name,Price,Stock\nBread,1.20,30\nMilk,2.10\nCoffee,seven,4\n\nTea,3.50,25\n")
    with open("products.csv", encoding="utf-8") as file:
        lines = file.read().splitlines()
    products = []
    row = 0
    for line in lines:
        row += 1
        if row == 1 or not line.strip():
            continue
        try:
            products.append(Product.from_csv(line))
        except ValueError as e:
            print(f"Row {row}: {e}")
    print(f"Loaded: {len(products)}")
except OSError as e:
    print(f"Cannot read products.csv: {e}")

Output

Row 3: expected 3 columns, got 2
Row 4: could not convert string to float: 'seven'
Loaded: 2

• Empty rows are skipped: line.strip() removes the spaces, and if nothing is left, not gives True.
• One except ValueError catches both errors: the one that from_csv raises itself, and the one from float("seven"). Python has no separate kind of exception for a bad number. The message tells you which one happened.
• The program says "Loaded: 2" and carries on with them.

Try it

Empty columns

In the first row the price is empty, in the second the stock, and the third row is completely empty:

What will the program print?

print(len("Coffee,,4".split(",")))
print(len("Coffee,7.50,".split(",")))
print(len("".split(",")))

Answer

3
3
0

3
2
0

3
3
1

Check

The idea

Objects to CSV

The other direction is a method that builds the row:

def to_csv(self):
    return f"{self.name},{self.price},{self.stock}"

You write the header row, and then one row for each object:

stock = [Product("Bread", 1.2, 30), Product("Milk", 2.1, 18)]
try:
    with open("stock.csv", "w", encoding="utf-8") as out:
        out.write("Name,Price,Stock\n")
        for p in stock:
            out.write(p.to_csv() + "\n")
    with open("stock.csv", encoding="utf-8") as file:
        print(file.read(), end="")
except OSError as e:
    print(f"Cannot write stock.csv: {e}")

Output

Name,Price,Stock
Bread,1.2,30
Milk,2.1,18

from_csv can read this file back. Saving and reading have to use the same format.

In Java, number formatting follows the computer's language settings, and in German or Bulgarian it writes a comma that pushes the columns apart. The f-string in Python does not follow them: {price:.2f} writes a point on every computer.

line = f"Bread,{1.2:.2f},30"
print(f"{line}  -> {len(line.split(','))} columns")

Output

Bread,1.20,30  -> 3 columns

The real trap is a comma in the value itself: "Cheddar, aged" becomes two columns. The csv module from the standard library knows the rule: such a value goes in quotes. csv.writer adds them when it saves, and csv.reader removes them when it reads and gives each row as a list:

import csv

with open("cheese.csv", "w", encoding="utf-8", newline="") as out:
    writer = csv.writer(out, lineterminator="\n")
    writer.writerow(["Name", "Price", "Stock"])
    writer.writerow(["Cheddar, aged", 9.8, 5])
with open("cheese.csv", encoding="utf-8", newline="") as file:
    print(file.read(), end="")
with open("cheese.csv", encoding="utf-8", newline="") as file:
    for row in csv.reader(file):
        print(row)

Output

Name,Price,Stock
"Cheddar, aged",9.8,5
['Name', 'Price', 'Stock']
['Cheddar, aged', '9.8', '5']

• newline="" has to be on every file you read or write with csv: the module takes care of the new lines itself.
• lineterminator="\n" writes the rows with \n. Without it, csv.writer puts \r\n, as on Windows and in the CSV standard.

Try it

Find the bug

The program should load the three products from products.csv, but it stops on the very first row:

Error

ValueError: could not convert string to float: 'Price'

Pick the line with the bug.

1row = 02loaded = 03for line in lines:4 row += 15 if row == 0:6 continue7 p = Product.from_csv(line)8 loaded += 19print(f"Loaded: {loaded}")

Check

The idea

Columns by name

Another program exports the same data, but with the columns in a different order and with one more. If you write f[1] for the price, with a new format you will read the wrong column and not notice. It is safer to find the column by its name in the header row:

def column(header, name):
    for i in range(len(header)):
        if header[i].strip().lower() == name.lower():
            return i
    raise ValueError(f"No column {name}")


try:
    with open("export.csv", "w", encoding="utf-8") as file:
        file.write("Stock,Name,Code,Price\n30,Bread,1001,1.20\n18,Milk,1002,2.10\n")
    name_col = -1
    price_col = -1
    with open("export.csv", encoding="utf-8") as file:
        for line in file.read().splitlines():
            f = line.split(",")
            if name_col == -1:
                name_col = column(f, "name")
                price_col = column(f, "price")
                continue
            print(f"{f[name_col]}: €{f[price_col]}")
except OSError as e:
    print(e)

Output

Bread: €1.20
Milk: €2.10

The first pass only finds where the columns are. After that, each row is read by those indexes. If the column is not there, column throws an exception right away, instead of reading wrong data.

More precisely, if you are curious

• Columns by name with csv. csv.DictReader does the search by name for you: the first row gives the names, and each next row is a dictionary (dict) from the column name to the value. Dictionaries are in the module "Collections".

import csv

with open("export.csv", "w", encoding="utf-8") as file:
    file.write("Stock,Name,Code,Price\n30,Bread,1001,1.20\n18,Milk,1002,2.10\n")
with open("export.csv", encoding="utf-8", newline="") as file:
    for row in csv.DictReader(file):
        print(f"{row['Name']}: €{row['Price']}")

• Another separator. Excel on a computer with German or Bulgarian settings often saves CSV with ;, exactly because the comma is the decimal mark. Then you read with csv.reader(file, delimiter=";").
• Serialization. The pickle module writes a whole object to a file. The file is binary: a person cannot read it, and you should not load a pickle file from an unknown source, because loading it can run code. Text formats such as CSV and JSON (the json module) are more predictable, and that is why they are used more.

Exercise

Data that comes alive

Solve the tasks in your editor. The program first creates the input file itself.

1. CSV analysis. Create people.csv with the contents below. Print the average age (integer division), who is the oldest, and how many people there are from each city, in the order the cities appear. Keep the cities in one list, and the counts in another, at the same positions.

people.csv

Name,Age,City
Ivan,34,Sofia
Maria,28,Plovdiv
Georgi,41,Sofia
Elena,25,Varna

Output

Average age: 32
Oldest: Georgi, 41
People from Sofia: 2
People from Plovdiv: 1
People from Varna: 1

2. Saving the hero. A Hero class with a name, level, health and gold. The method save(path) saves the hero as key=value lines, and the static method load(path) creates a new Hero from such a file. Create "Arin" with level 6, 100 health and 120 gold, raise his level by 1, take away 15 health, give him 220 gold, save him, print the file and load it back.

Output

name=Arin
level=7
health=85
gold=340
Loaded: Arin, level 7, health 85, gold 340

3. Duplicate text. Create text.txt with the text below. Split it into sentences at the period (and the spaces after it), and print each sentence that appears more than once, one time.

text.txt

This is text. This is also text. This is text.

Output

Duplicate text: "This is text."

More tasks

• The most expensive product. Load products.csv from the lesson, and print the product with the highest price and how many products have stock under 20.
• Fixed rows. Load the file with the bad rows, and write the good ones to products-clean.csv, and the bad ones, with the row number and the reason, to rejected.txt.
• Sales report. A file sales.csv with the columns Date,Product,Count,Price. The rows are sorted by date. Print the revenue for each day with an f-string in columns.

Test

Check yourself

1. What is the first row of most CSV files?

The first record with dataA header row with the names of the columnsThe number of rows

2. from_csv raises ValueError for the wrong number of columns, and float("seven") raises for a bad number. How many except blocks do you need to catch both?

Two, in the right orderNone: float does not raiseOne: except ValueError catches both

3. What is len("Coffee,7.50,".split(","))?

132

4. The product name is "Cheddar, aged". How do you write it to CSV without it becoming two columns?

With an f-string: f"{name},{price}"You cannot, CSV does not allow a commaWith csv.writer: it puts the value in quotes

Check

Start the lesson over