The bus company problem
A bus company sends your school a list of seats for a trip. The list is a spreadsheet file.
Your school app needs the same list, but it can only read one other format. So somebody must change the list from one format to the other.
Which format is best for each job? And what can go wrong when you change it? Let us find out.
Key words
| Word | What it means | Example |
|---|---|---|
| Data format | The way data is written in a file. | CSV, JSON, XML |
| CSV | Rows with commas between the values. Everything is text. | Sam,16,12.5 |
| JSON | Curly brackets { }, names and values. It has numbers, text, true and false, and lists. | {"age": 16} |
| XML | Tags inside tags. Everything is text. | <age>16</age> |
| Transformation | Changing data from one form to another. | CSV to JSON |
| Integer | A whole number. | 16 |
| Float | A number with a decimal point. | 12.5 |
| Casting | Changing a value from one kind to another, such as text to a number. | int("16") |
| Flat data | Data in simple rows and columns, with nothing inside a cell. | A spreadsheet |
| Nested data | Data with other data inside it, such as a list inside a record. | A student with a list of trips |
| Dictionary | In Python, a set of names with a value for each name. | {"name": "Sam"} |
| Parse | Read text in a format and turn it into data your program can use. | Turn CSV lines into a list |
The cells students fill in are left blank in this preview.
Try it: the same data three times
csv_text = "name,age\nSam,16\nAisha,17"
json_text = '[{"name": "Sam", "age": 16}, {"name": "Aisha", "age": 17}]'
xml_text = '<students><student><name>Sam</name><age>16</age></student><student><name>Aisha</name><age>17</age></student></students>'
print("CSV ", len(csv_text), "characters")
print("JSON ", len(json_text), "characters")
print("XML ", len(xml_text), "characters")
# Which one is the smallest? Which one is the biggest?
What each format keeps
| Question | CSV | JSON | XML |
|---|---|---|---|
| Does it keep numbers and True or False? | No, all text | Yes | No, all text |
| Can it hold a list inside a record? | No, flat only | Yes | Yes |
| How big is the file? | Smallest | Middle | Biggest |
| Does it open in a spreadsheet? | Yes | No | Not easily |
| Common use | Tables and files saved from a spreadsheet | Web and apps | Documents and older systems |
The cells students fill in are left blank in this preview.
Quick check: reading the table
Have a go
Quick check: reading the table
3 questionsYour answers are marked here and are not kept. A college account keeps every student’s marks.
Changing format needs care
When you change a format you do a transformation. You parse the old format, then write the new one.
CSV has only text. So going from CSV to JSON, you must cast the age to an integer and the money to a float. If you forget, the data keeps "16" as text.
Try it: CSV text to a list
text = "name,age\nSam,16\nAisha,17"
people = []
lines = text.split("\n")
for i in range(1, len(lines)):
parts = lines[i].split(",")
people.append({"name": parts[0], "age": int(parts[1])})
print(people)
# range(1, len(lines)) skips line 0, the heading line.
# This list has the same shape as JSON. Remove int( ) and see what changes.
Extra: Python's own csv and json tools
# Python has tools that parse CSV and write JSON for you.
import csv
import json
text = "name,age\nSam,16\nAisha,17"
people = []
for row in csv.DictReader(text.split("\n")):
people.append({"name": row["name"], "age": int(row["age"])})
print(json.dumps(people))
# json.dumps writes real JSON text. Remove int( ) and see the quotes come back.
Nested data does not fit in a CSV
data-callouts.warning.label
A CSV is flat. If a student has a list of trips, a CSV cannot keep the list. You must join the trips into one cell or make a row for each trip. Both lose some of the shape.