Week 6: Data formats

STAT163 — Data Manipulation Essentials

Oleh Omelchenko

2026-10-05

Pre-lecture quiz

Today

  • 1. Files: CSV, Excel, Parquet
  • 2. Data from an API

1. Files: CSV, Excel, Parquet

A CSV file is text

the first lines of a CSV file of New York taxi trips (11 of 19 columns)

VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,trip_distance,…,fare_amount
2,2023-01-01 00:32:10,2023-01-01 00:40:36,1.0,0.97,1.0,N,161,141,2,9.3
2,2023-01-01 00:55:08,2023-01-01 01:01:27,1.0,1.1,1.0,N,43,237,1,7.9
2,2023-01-01 00:25:04,2023-01-01 00:37:49,1.0,2.51,1.0,N,48,238,1,14.9

Your ideas

Almost every program can save a table as CSV and open one. Why is it so easy to share?

Any program reads it: Excel, pandas, R, a database, a text editor.

You read it Open it in a text editor and you see the values.

You fix it Change one value by hand and save.

Column types in a CSV file

trips.csv with five columns

VendorID,tpep_pickup_datetime,tpep_dropoff_datetime,passenger_count,store_and_fwd_flag
2,2023-01-01 00:32:10,2023-01-01 00:40:36,1.0,N
2,2023-01-01 00:55:08,2023-01-01 01:01:27,1.0,N
2,2023-01-01 00:25:04,2023-01-01 00:37:49,1.0,N
1,2023-01-01 00:03:48,2023-01-01 00:13:25,0.0,N
2,2023-01-01 00:10:29,2023-01-01 00:21:19,1.0,N
2,2023-01-01 00:50:34,2023-01-01 01:02:52,1.0,N
2,2023-01-01 00:09:22,2023-01-01 00:19:49,1.0,N
2,2023-01-01 00:27:12,2023-01-01 00:49:56,1.0,N

Your list

The file holds only characters. What does pandas have to guess to make a table from it?

trips = pd.read_csv("trips.csv")
trips.dtypes
VendorID                   int64
tpep_pickup_datetime      object
tpep_dropoff_datetime     object
passenger_count          float64
store_and_fwd_flag        object

The pickup time is text. Each type is guessed from the characters in the column, and dates are not guessed.

Reading one column of a CSV file

trips.csv, all 19 columns: 322 MB

VendorID tpep_pickup_datetime passenger_count … fare_amount …
0 2 2023-01-01 00:32:10 1.0 … 9.30 …
1 2 2023-01-01 00:55:08 1.0 … 7.90 …
2 2 2023-01-01 00:25:04 1.0 … 14.90 …
… … … … … … …
3066765 2 2023-01-31 23:07:32 NaN … 15.97 …

3066766 rows × 19 columns

Decide before we check

You need only fare_amount. How much of the 322 MB file does a program have to read?

2,2023-01-01 00:32:10,2023-01-01 00:40:36,1.0,0.97,1.0,N,161,141,2,9.3,1.0,0.5,0.0,0.0,1.0,14.3,2.5,0.0 2,2023-01-01 00:55:08,2023-01-01 01:01:27,1.0,1.1,1.0,N,43,237,1,7.9,1.0,0.5,4.0,0.0,1.0,16.9,2.5,0.0 1,2023-01-01 00:03:48,2023-01-01 00:13:25,0.0,1.9,1.0,N,138,7,1,12.1,7.25,0.5,0.0,0.0,1.0,20.85,0.0,1.25 The fare starts at a different character in each line. Only the commas tell where it is.

All 322 MB. pd.read_csv(..., usecols=["fare_amount"]) keeps one column, but it still reads every line to find it.

Excel files

$ % .0 .00 123 ▾ fx 9.3 A B C D 1 pickup time passengers fare flag 2 1/1/2023 0:32:10 1 $9.30 N 3 1/1/2023 0:55:08 1 $7.90 N 4 1/1/2023 0:25:04 1 $14.90 N 5 1/1/2023 0:03:48 0 $12.10 N
$ % .0 .00 123 ▾ fx 9.3 A B C D 1 pickup time passengers fare flag 2 1/1/2023 0:32:10 1 $9.30 N 3 1/1/2023 0:55:08 1 $7.90 N 4 1/1/2023 0:25:04 1 $14.90 N 5 1/1/2023 0:03:48 0 $12.10 N Automatic Plain text Number Percent Scientific ✓ Currency Date Time Date time Duration

Your list

The same trips in a spreadsheet. What does it keep that the CSV file does not?

Each cell has its own format. The cell shows $9.30 and holds the number 9.3.

A type in each cell A number, a date or text, set when the value is typed.

Formatting and formulas Colours, widths, totals that update.

Several sheets Many tables in one file.

Made for people Every office computer opens it, and anyone can edit it by hand.

When automatic formatting changes the data

01 CSV file 20 genes

02 Google Sheets import

03 CSV file download

04 Gene roles match by name

01 CSV file 20 genes

02 Google Sheets import

03 CSV file download

04 Gene roles match by name

01 CSV file 20 genes

02 Google Sheets import

03 CSV file download

04 Gene roles match by name

01 CSV file 20 genes

02 Google Sheets import

03 CSV file download

04 Gene roles match by name

gene-results.csv, the first 10 of 20 genes

gene log2_fold_change p_value
TP53 1.82 0.0004
SEPT2 -0.95 0.012
EGFR 2.31 0.00002
MARCH1 1.12 0.031
BRCA1 -1.47 0.0021
DEC1 0.88 0.044
MYC 3.05 0.000001
SEPT9 -1.21 0.0067
GAPDH 0.04 0.91
MARCH5 0.73 0.048

Decide before we check

We import this file into Google Sheets. What happens to the gene column?

fx 3/1/2026 A B C 1 gene log2_fold_change p_value 2 TP53 1.82 0.0004 3 SEPT2 -0.95 0.012 4 EGFR 2.31 0.00002 5 March1 1.12 0.031 6 BRCA1 -1.47 0.0021 7 Dec1 0.88 0.044 8 MYC 3.05 0.000001 9 SEPT9 -1.21 0.0067 10 GAPDH 0.04 0.91 11 March5 0.73 0.048

Three names are now dates. The formula bar shows what March1 holds: 1 March 2026.

Text sits on the left of a cell, numbers and dates on the right.

the downloaded CSV file, the first 10 genes

gene log2_fold_change p_value
TP53 1.82 0.0004
SEPT2 -0.95 0.012
EGFR 2.31 0.00002
March1 1.12 0.031
BRCA1 -1.47 0.0021
Dec1 0.88 0.044
MYC 3.05 0.000001
SEPT9 -1.21 0.0067
GAPDH 0.04 0.91
March5 0.73 0.048

The downloaded file holds what the sheet showed. MARCH1 is now March1.

Decide before we check

A second table gives the role of each gene. We match the two tables by the gene name. What happens to March1?

the downloaded file gene roles gene p_value gene role TP53 0.0004 SEPT2 0.012 EGFR 0.00002 March1 0.031 BRCA1 0.0021 Dec1 0.044 MYC 0.000001 SEPT9 0.0067 GAPDH 0.91 March5 0.048 BRCA1 DNA repair DEC1 tumour suppressor EGFR growth signalling GAPDH energy metabolism MARCH1 immune response MARCH5 mitochondria MYC growth signalling SEPT2 cell division SEPT9 cell division TP53 tumour suppressor ✗ ✗ ✗

March1 is not MARCH1, so it has no match. No program shows an error.

Over all 20 genes, five find no match. All five had p_value < 0.05, so a count of these genes by role drops from 16 to 11.

Automatic formatting in published research

  • One in five papers with gene lists in Excel files, 2005 to 2015, had names turned into dates.
  • A check of papers from 2014 to 2020 found it in three in ten.
  • In 2020 the genes were renamed: SEPT1 is now SEPTIN1, MARCH1 is now MARCHF1.

A type for the whole column

A CSV file keeps no types, so every reader guesses them. A spreadsheet keeps a type in each cell, set from what you type. Where else could a file keep the types?

Once for each column, apart from the values, written by the program that saves the file. The column names and types together are the schema of the file.

A Parquet file

CSV: stored row by row pickup passengers fare 00:32:101.09.3 00:55:081.07.9 00:25:041.014.9 00:03:480.012.1 Parquet: stored column by column pickup passengers fare 00:32:101.09.3 00:55:081.07.9 00:25:041.014.9 00:03:480.012.1 datetime float float

a Parquet file is close to a dictionary of columns

{"passengers": [1.0, 1.0, 1.0, 0.0],
 "fare": [9.3, 7.9, 14.9, 12.1]}

Schema on read CSV. The reader guesses the types every time.

Schema on write Parquet. The types are fixed when the file is written.

Reading one column of a Parquet file

pd.read_parquet("trips.parquet", columns=["fare_amount"])

Decide before we check

The same table as Parquet. How much of the file does a program read for fare_amount?

megabytes read

CSV, any column322 MB
Parquet, all columns48 MB
Parquet, fare_amount3 MB

On a laptop: about 3 seconds to read the CSV file, under a tenth of a second for the fares from Parquet.

Drawbacks of Parquet

the Parquet file opened in a text editor

PAR1.....4L.................cb..F(..¨n±§.......Ø.8..
.*,..®ö..............................(..............
..................<ý}|.ç}/.Ï5×ì^³ììάV/³Òjwôb.1.."°2
.v...Ô1´éÓ:m..,ǸO]{e|.%.ѵ¬ÌÊ5F.ô~.O.ÁÍéÉ9÷Ýç.Ô=.9q
͵,F¤!@Nîû)i.3²\Ë9uÍʤf.²>Ïï7˹ÿ0.iwæzù½|.ï.I.Nü×OdB
Bªº\ï.X./.Â,9EëÛ¥..N±.I%ÝæzÂËidm...??`,.W5¥0..2aIMÄ
.(3¤@.mH!f..î.âÄ«.e.ë...H.&)¤×.W.)¶HMï.QËÂ.õ.%.*ÔË´K
t`¥..ôà.×½f©Cç.7õd8c6ÝÙ.¿%ÍR.$.a$%ª7K.%½&)Àòë%.n.è¤!
1ý.ÍH¡É.d(%E¨Hó9½I¢Ô..¯O®."7+..«.Ö¹AbéºÕ..z.X.¹...e.
Ò.x.¬.Fà}=°..Ï£.%Úÿ.©...&÷ÆóÓzÊ6R.wùÂ(_\..é.'f¥P.¥`Ý
4mK´Ë.BÅ.É.°ÌZn%.i.À.i.Äö¤3ð¡>.6.>"+°ûÑ.ÇwÒû¥@G.~7..
:(gQ.³%..Ë.f%V¢{[a³°äÐ.ï.n.Â.K4cºska..ÀIô{¶.0þ.öÀaÏ

Your list

Parquet is smaller, faster and keeps the types. Why do people still send CSV and Excel files?

Hard for people

No text editor or spreadsheet shows it. You cannot fix one value by hand.

Easy for programs

pandas, R, SQL databases and data warehouses all read and write it. In pandas, pd.read_parquet and df.to_parquet work like the CSV functions you know.

Choosing a format

Choose a format and say what you lose

  1. A month of sales goes to the accountant.
  2. Three years of taxi trips stay with you for your own analysis.
  3. A city council publishes its energy use for anyone to download.
  4. A delivery app sends each new order to the restaurant's program.
CSV code,qty,date 01001,11.2,01.02.2023 00451,3,15.02.2023 only characters, no types Excel 1001 11.2 2023-02-01 00451 3 2023-02-15 number, text number date a type in each cell Parquet code: text qty: float date: date 01001 00451 11.2 3.0 2023-02-01 2023-02-15 one type per column, kept in the file JSON [{"code": "01001", "qty": 11.2, "date": "01.02.2023"}, {…}] text, numbers, true/false, null, records, lists. A date is text.

2. Data from an API

What an API is

Your ideas

Where have you met an API? What did the program send, and what came back?

An API is an address that a program sends a question to. The question and the answer travel over HTTP, as a web page does to your browser. The answer can be a page, an image or a file. For data it is most often JSON, so a lot of data reaches you as JSON and has to become a table.

the full answer from an example library API

url = "https://api.example-library.org/books"
requests.get(url).json()
{
  "page": 1, "pages": 2, "total": 5,
  "books": [
    {
      "id": 1, "title": "Поштовх", "pages": 312,
      "publisher": {"name": "Наш формат", "city": "Київ"},
      "authors": ["Талер", "Санстейн"]
    },
    {
      "id": 2, "title": "Чистий код", "pages": 448,
      "publisher": {"name": "Фабула", "city": "Харків"},
      "authors": ["Мартін"]
    },
    {
      "id": 3, "title": "Фрікономіка", "pages": 248,
      "publisher": {"name": "Наш формат", "city": "Київ"},
      "authors": ["Левітт", "Дабнер"]
    }
  ]
}

JSON and a table

page 1 of the answer from the library API

{
  "page": 1, "pages": 2, "total": 5,
  "books": [
    {
      "id": 1, "title": "Поштовх", "pages": 312,
      "publisher": {"name": "Наш формат", "city": "Київ"},
      "authors": ["Талер", "Санстейн"]
    },
    {
      "id": 2, "title": "Чистий код", "pages": 448,
      "publisher": {"name": "Фабула", "city": "Харків"},
      "authors": ["Мартін"]
    },
    {
      "id": 3, "title": "Фрікономіка", "pages": 248,
      "publisher": {"name": "Наш формат", "city": "Київ"},
      "authors": ["Левітт", "Дабнер"]
    }
  ]
}

Your list

What can this JSON answer hold that a CSV file cannot? And what does it still not keep?

Records inside records publisher holds its own name and city.

Lists authors holds one, two or three names.

Data about the answer page, pages and total describe the answer itself.

A few types Text, numbers, true, false and null. No dates: a date comes as text.

Why data comes through an API

Your list

A national library could publish its whole catalogue as one CSV file, updated once a day. Why does it answer questions through an API instead?

Too big Millions of books, and you need the ten by one author.

Out of date Books are added and lent every minute. The API answers with the data as it is now.

Not all public Who borrowed what is private. Each user gets only what they may see.

An answer in pages

url = "https://api.example-library.org/books"
page_1 = requests.get(url).json()
len(page_1["books"])
3
{
  "page": 1, "pages": 2, "total": 5,
  "books": [
    {"id": 1, "title": "Поштовх", "pages": 312, …},
    {"id": 2, "title": "Чистий код", "pages": 448, …},
    {"id": 3, "title": "Фрікономіка", "pages": 248, …}
  ]
}

Decide before we check

Does the library have three books? Find the answer in the output on the left.

page_2 = requests.get(url, params={"page": 2}).json()
{
  "page": 2, "pages": 2, "total": 5,
  "books": [
    {
      "id": 4, "title": "Шум", "pages": 362,
      "publisher": {"name": "Наш формат", "city": "Київ"},
      "authors": ["Канеман", "Сібоні", "Санстейн"]
    },
    {
      "id": 5, "title": "Чистий Agile", "pages": 224,
      "publisher": {"name": "Фабула", "city": "Харків"},
      "authors": ["Мартін"]
    }
  ]
}

When the data behind an API changes

Monday

{"page": 1, "pages": 2, "total": 5, "books": […]}

the same request, the next Monday

{"page": 1, "pages": 2, "total": 6, "books": […]}

Your guesses

Your report from Monday says 5 books. A week later the same code says 6. List every reason you can think of. What should you have saved on Monday, so that your numbers can be checked?

One row per book or per author

books = pd.json_normalize(page_1["books"] + page_2["books"])
by_author = books.explode("authors")

books

id publisher.name authors
1 Наш формат [Талер, Санстейн]
2 Фабула [Мартін]
3 Наш формат [Левітт, Дабнер]
4 Наш формат [Канеман, Сібоні, Санстейн]
5 Фабула [Мартін]

by_author

id publisher.name authors
1 Наш формат Талер
1 Наш формат Санстейн
2 Фабула Мартін
3 Наш формат Левітт
3 Наш формат Дабнер
4 Наш формат Канеман
4 Наш формат Сібоні
4 Наш формат Санстейн
5 Фабула Мартін

In which table is each answer easier to find?

  • How many books does the library have? books: 5
  • How many books did Наш формат publish? books: 3
  • Who wrote the most books? by_author: Санстейн and Мартін, 2 each
  • How many authors are there? by_author: 7

Other problems with APIs

Your list

Your analysis asks an API for new data every hour. What else can go wrong?

A limit Many APIs answer only so many questions an hour. Past the limit you get an error.

A key A key raises the limit. It works like a password: keep it out of code you share.

Changes A field is renamed or removed, and your code breaks.

Shutdown The service closes or starts to charge. You keep only the answers you saved.

Summary

  • CSV and Excel are made for people. Parquet is made for programs.
  • In a spreadsheet, the type of a cell comes from what you type.
  • Save the raw answer of an API, with the date you got it.

Sources

Ziemann, Eren, El-Osta, "Gene name errors are widespread in the scientific literature", Genome Biology 17:177, 2016. Abeysooriya et al., PLoS Computational Biology, 2021. Bruford et al., "Guidelines for human gene nomenclature", Nature Genetics, 2020.

Data: NYC TLC trip records (yellow taxi, January 2023). Google Sheets behaviour checked 4 October 2026. The library API on the API slides is made up for this lecture. The books in it are real Ukrainian editions from Наш формат and Фабула.

McKinney, Python for Data Analysis, 3rd ed. — Ch. 6. McGregor, Practical Python Data Wrangling and Data Quality — Ch. 4, 5.

Questions