Reading a CSV, and checking what arrived

The problem we are solving

So far every value in the table has been typed into the code by hand. For three rows, that works.

Real data does not live in code. It lives in a file — emailed by someone, exported from a system, or saved out of a spreadsheet. And the file has twelve thousand rows, not twelve.

That file usually looks like this: plain text, the column names on the first line, then one row per line, separated by commas:

product,quantity,price
pen,12,15.0
notebook,5,60.0

This is a CSV — comma-separated values. Turning it into a table is one line of pandas, and it is the single most-used line in pandas.

But there is a habit to build immediately after that line, and it is the real subject of this chapter: once a file is read, look at it. How many rows arrived, whether the column names are what you expected, whether the numeric columns really came in as numbers. Skip those thirty seconds and the mistake surfaces much later, when an answer is wrong and nothing has errored.

By the end of this chapter you can

• Read a file with pd.read_csv() and handle the path problem that follows
• Check a file quickly with head(), tail(), shape and columns
• Read info() to see row counts, empty cells and every column's type at once
• Summarise the numeric columns with describe()
• Recognise and fix a FileNotFoundError and a wrong separator

Prerequisites: Series and DataFrame — the two things in pandas.

Make the file first

In your project folder, create a file called sales.csv with exactly these six lines:

product,quantity,price
pen,12,15.0
notebook,5,60.0
bag,2,850.0
bottle,7,120.0
eraser,30,8.0

The first line is not data, it is the column names — the header. The other five lines are five rows.

Reading it, in one line

import pandas as pd

sales = pd.read_csv("sales.csv")

print(sales)

product  quantity  price
0       pen        12   15.0
1  notebook         5   60.0
2       bag         2  850.0
3    bottle         7  120.0
4    eraser        30    8.0

That one line did three things, and all three quietly:

First, it took the first line as the header — so product, quantity and price became column names rather than a row.

Second, it built an index, the 0 to 4 down the left. That was not in the file.

Third, and this is the one that matters, it guessed each column's type. Everything in the file is text — 12 is really two characters. Pandas looked at each column and decided whether it was numbers or text.

The guess is usually right. But when it is wrong, nothing errors — which is exactly why you look.

The four things to check after reading a file

shape — how much arrived

print(sales.shape)

(5, 3)

Five rows, three columns. The file had six lines and this has five rows, because the header is not a row. If the number is far below what you expected, or exactly one too many, that is the first sign something was read wrongly.

head() and tail() — the start and the end

print(sales.head(3))

product  quantity  price
0       pen        12   15.0
1  notebook         5   60.0
2       bag         2  850.0

Printing all twelve thousand rows of a real file fills the terminal and tells you nothing. head() shows the first few — five if you do not say. tail() does the same at the end, and looking at the end is separately useful, because exported files often carry a stray blank or a totals row down there.

info() — everything at a glance

sales.info()

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 5 entries, 0 to 4
Data columns (total 3 columns):
 #   Column    Non-Null Count  Dtype
---  ------    --------------  -----
 0   product   5 non-null      object
 1   quantity  5 non-null      int64
 2   price     5 non-null      float64
dtypes: float64(1), int64(1), object(1)
memory usage: 252.0+ bytes

Notice it is written sales.info() and not print(sales.info()) — this method prints for itself and returns nothing, so wrapping it in print adds a stray None at the end.

This is the most useful thing to run after reading a file, because it carries two important facts together:

Non-Null Count — how many cells in each column are actually filled. All three say 5 non-null here, so nothing is empty. A 4 non-null anywhere means one cell in that column is blank, and that will change your arithmetic.

Dtype — what the guess settled on. quantity came in as int64, price as float64, product as object. That is what we wanted.

(The memory usage figure on the last line may differ on your machine, and does not matter.)

describe() — a summary of the numeric columns

print(sales.describe())

quantity       price
count   5.00000    5.000000
mean   11.20000  210.600000
std    11.12205  360.217712
min     2.00000    8.000000
25%     5.00000   15.000000
50%     7.00000   60.000000
75%    12.00000  120.000000
max    30.00000  850.000000

It picks out the numeric columns and runs eight calculations on each. product is absent, because it is text.

One thing here should catch your eye: the mean of price is 210.6, while the 50% — the median — is only 60. A gap that wide means one value sits far above the rest, and max names it: 850. That is how describe() is read — when the mean and the median pull apart, look there.

A complete example

report.py:

import pandas as pd

sales = pd.read_csv("sales.csv")

print("Rows, columns:", sales.shape)
print()
print(sales.head(3))
print()
print("Columns   :", list(sales.columns))
print("Total qty :", sales["quantity"].sum())
print("Avg price :", round(sales["price"].mean(), 2))

Rows, columns: (5, 3)

    product  quantity  price
0       pen        12   15.0
1  notebook         5   60.0
2       bag         2  850.0

Columns   : ['product', 'quantity', 'price']
Total qty : 56
Avg price : 210.6

A small program, but the shape of it is worth noticing — read, then check, then calculate. shape and head() are not calculations, they are questions: did I read the right file? Are there as many rows as there should be?

And there is a reason for printing list(sales.columns). As a list, each name comes back inside quotes, so a name like 'quantity ' with a trailing space is visible immediately. Printed any other way that space stays invisible and comes back later as a KeyError.

When it breaks

FileNotFoundError: [Errno 2] No such file or directory: 'sales.csv' Python looks in the current folder and did not find it there. This is the same problem as chapter two of the Python course: the terminal is in one place and the file in another. Run pwd to see where you are, dir to see whether the file is there, then cd. On Windows the name may really be sales.csv.txt — turn on file name extensions in Explorer.

The whole file arrived as a single column The separator is not a comma. Files saved in much of Europe use a semicolon, because the comma is the decimal mark there. Write pd.read_csv("sales.csv", sep=";"). For tab-separated files, sep="\t".

A numeric column came in as object Text has got into the column somewhere — an N/A, a -, a blank cell, or a thousands comma (1,200). Run df["col"].unique() to see what is actually in there; the odd value usually stands out. The repair is pd.to_numeric(df["col"], errors="coerce"), which turns anything non-numeric into a blank — covered properly in the chapter on missing data.

UnicodeDecodeError The file is not saved as UTF-8, which happens routinely with files out of a spreadsheet. Try pd.read_csv("sales.csv", encoding="utf-8-sig"), and failing that encoding="latin-1".

The first row of data became the column names, though the file had no header Write pd.read_csv("sales.csv", header=None) and the columns become 0, 1, 2. To name them, add names=["product", "quantity", "price"].

A stray None printed after sales.info() It was written as print(sales.info()). info() prints for itself and returns nothing, so print is printing the None it returned. Write just sales.info().

Your turn

Make a file called students.csv holding at least eight people, in three columns — a name, an age, and a mark.

Then write report.py, which:

1. Reads the file
2. Prints shape and list(columns)
3. Prints head(3) and tail(2)
4. Runs info() and says whether all three column types are what you expected
5. Prints describe(), and separately the mean and maximum of the marks column

Then break it on purpose — three times, noticing what happens each time:

• Put N/A in one cell of the marks column. What does Dtype say in info() now? What happened to the mean?
• Replace every comma in the file with a semicolon. What does shape say now, and why?
• Delete the first line, the header. How many rows arrived, and what are the column names now?

All three happen routinely in real work, and each has one clear symptom. Causing them deliberately today means recognising them on sight next time.