Lesson 3 of 3 in this module

3/20 in track

Reading CSV, Excel, and JSON

pandas10 min

Overview

pd.read_csv, read_excel, and read_json load files into DataFrames. In this tab the frames are already loaded.

On this page8 sections

What you will do

Someone sends you a file called orders.csv and asks a quick question about it. It is not in Python yet, and it is not clear what any of the columns mean.

Before anyone can count paid orders, the file has to be loaded into a table pandas can work with.

Where does every pandas job start?

With a table that is not in Python yet. It is a file somebody sent you.

A CSV is a text file where each line is a row and commas separate the columns. After read_csv, the first line becomes the column names.

On the left a plain-text CSV file with a header line and 3 order lines. An arrow labelled pd.read_csv points to a DataFrame table on the right.
A CSV file is just text. read_csv turns the first line into column names and each later line into a row.

The left side is what you would see opening orders.csv in a text editor. Pandas adds the 0, 1, 2 row labels on its own.

Which reader do you call?

Pandas has one reader per file type. Whichever you call, you get a DataFrame back.

3 file types, one destination.

ReaderReadsOptions you will reach forGives back
pd.read_csv(path)Text tables: .csv, .tsvsep, header, dtype, parse_dates, encodingDataFrame
pd.read_json(path)JSON, row-shapedorient, linesDataFrame
pd.read_excel(path)Workbooks: .xlsx (needs the openpyxl package)sheet_name, headerDataFrame

JSON is a text format of nested key and value pairs that web services love.

Why does ShopKart get so many kinds of file?

Data never starts inside a clean Python table. Each partner sends what its own system exports.

  1. Merchant catalog: CSV
  2. Payment gateway: daily logs
  3. Logistics: tab-delimited
  1. Website tracking: JSON

Before you can filter, total or join anything, that text has to become a DataFrame. Loading is where every pipeline begins.

Words you will see in this lesson

Six words. The rest of the lesson uses only these.

WordWhat it means
read_csvThe pandas function that loads a CSV file into a DataFrame
CSVA text file where each line is a row and commas separate columns
JSONA text format for nested data, written with braces and keys
dtypeThe type of a column, such as number or text
HeaderThe first line that names the columns
EncodingThe way characters are stored in a file, such as UTF-8

Why this skill

What goes wrong without anyone noticing?

Loading mistakes rarely crash. You just carry the wrong shape into every later step.

Three quiet failures.

The file hasWhat you getThe fix
Semicolons between columnsOne giant columnsep=";"
A stray word in a number columnThe whole column loads as textna_values, or clean first
Dates written as 2026-08-02Plain text, not datesparse_dates

A loud crash halts the pipeline and pages an engineer. A quiet mistake keeps running and feeds wrong numbers into dashboards for weeks.

A silent wrong load is worse than a crash. Always look at what you loaded.

How the code works

What are the steps of a good load?

The same 4 steps every time, whatever the file.

  1. Pick the reader for the file type.
  2. Pass options if the file is not the usual comma-separated shape.
  3. Look at the shape, the column names and the types before you do anything else.
  4. Only then start filtering and cleaning.

Why look before cleaning? A load can succeed and still be wrong. If the separator is a semicolon and you used the default comma, pandas happily gives you one column with whole lines inside it. Two seconds of looking at df.shape and df.head() would have caught it.

In this tab the studio already loaded df_orders. You can still practise real reading, because read_csv accepts text in memory: wrap a string in io.StringIO and it behaves like an open file.

What do the options mean?

The first argument is where the data is: a path, a web address, or a text buffer. The options say how to read it.

The options you will use most.

OptionWhat it tells pandasExample
sepWhat separates the columnssep=";"
headerWhich line holds the namesheader=None
dtypePin a column to a typedtype={"order_id": "string"}
parse_datesTurn text into datesparse_dates=["created_at"]
usecolsKeep only these columnsusecols=["order_id"]
na_valuesExtra tokens that mean missingna_values=["N/A"]
skiprowsIgnore lines at the topskiprows=2
encodingHow the text was savedencoding="latin1"
chunksizeRead in batches, not all at oncechunksize=50000

How does pandas know a column is a number?

A text file has no schema. Pandas samples the values and guesses a type for each column.

Predict: what happens to a column of 101 and 102 when one value is A-7?

PythonOne odd value in an id column
import io

print(pd.read_csv(io.StringIO("id\n101\n102\nA-7\n"))["id"].tolist())
['101', '102', 'A-7']

One odd value turned the whole column into text. Text takes several times the memory of numbers. In a scheduled job, pin the types yourself with dtype.

Worked examples

What does a good load look like?

We use 3 lines of the orders file as text.

Predict: what shape and what dtype for order_total will read_csv produce?

Pythonread_csv on text, then check the shape and the type of a number column
import io

text = """order_id,user_id,order_status,order_total
ORD-0001,U004,paid,612.07
ORD-0002,U073,refunded,446.70
ORD-0003,U078,paid,1104.32
"""
orders = pd.read_csv(io.StringIO(text))
print("orders shape:", orders.shape)
print("order_total dtype:", orders["order_total"].dtype)
result = orders
orders shape: (3, 4)
order_total dtype: float64

result: 3 rows, 4 columns, and the total column came in as a real number.

order_iduser_idorder_statusorder_total
0ORD-0001U004paid612.07
1ORD-0002U073refunded446.70
2ORD-0003U078paid1104.32

3 rows, 4 columns, and float64 means pandas saw decimals, so you can add them. That is a good load.

What if the file uses semicolons?

Some systems, especially in countries that write decimals with a comma, separate columns with a semicolon.

Predict: what shape do you get without sep and with sep=";"?

PythonThe same text read without and with sep
import io

text = """order_id;order_status;order_total
ORD-0001;paid;612.07
ORD-0002;refunded;446.70
"""
wrong = pd.read_csv(io.StringIO(text))
right = pd.read_csv(io.StringIO(text), sep=";")
print("wrong shape and cols:", wrong.shape, wrong.columns.tolist())
print("right shape and cols:", right.shape, right.columns.tolist())
wrong shape and cols: (2, 1) ['order_id;order_status;order_total']
right shape and cols: (2, 3) ['order_id', 'order_status', 'order_total']
What the wrong read gives you
order_id;order_status;order_totalORD-0001;paid;612.07ORD-0002;refunded;446.70

1 column, and its name is the whole header line. 2 rows of data where you wanted 2 rows by 3 columns.

Pandas looked for commas, found none, and treated each line as one value.

One very wide column means the separator is wrong.

What about tabs and spaces?

Name the separator. A tab is written "\t" and a run of spaces is written "\s+".

PythonTab-separated and space-separated text
import io

print(pd.read_csv(io.StringIO("a\tb\n1\t2\n"), sep="\t").shape)
print(pd.read_csv(io.StringIO("a  b\n1   2\n"), sep=r"\s+").shape)
(1, 2)
(1, 2)

When you are unsure, look at the raw file first, for example with head on the command line, before you write the reader.

How does JSON become rows?

A JSON list of objects with the same keys lines up as rows. Each object is a row and each key is a column.

Predict: how will read_json lay out 2 order objects?

PythonEach JSON object becomes 1 row
import io

js = '[{"order_id": "ORD-0001", "order_status": "paid"}, {"order_id": "ORD-0002", "order_status": "refunded"}]'
df_js = pd.read_json(io.StringIO(js))
print(df_js)
   order_id order_status
0  ORD-0001         paid
1  ORD-0002     refunded

What is JSON Lines and how do you read it?

Services often write one JSON document per line, called NDJSON or JSON Lines. Pass lines=True.

Predict: what shape comes back, and what happens if you forget lines=True?

PythonNewline-delimited JSON records
import io

ndjson = '{"order_id": "ORD-0001", "status": "paid"}\n{"order_id": "ORD-0002", "status": "refunded"}\n'
df_ndjson = pd.read_json(io.StringIO(ndjson), lines=True)
print("NDJSON parsed shape:", df_ndjson.shape)
NDJSON parsed shape: (2, 2)

Without lines=True, read_json treats the text as one document and stops with ValueError: Trailing data.

How do you inspect a table that is already loaded?

In the studio you inspect what is there. Three small checks tell you most of what you need.

Predict: what columns, shape and order_total dtype does df_orders have?

PythonColumn names, size, one type, and a 5-row preview
print("df_orders columns:", df_orders.columns.tolist())
print("df_orders shape:", df_orders.shape)
print("order_total dtype:", df_orders["order_total"].dtype)
result = df_orders.head(5)
df_orders columns: ['order_id', 'user_id', 'order_status', 'order_total', 'currency', 'created_at', 'updated_at']
df_orders shape: (180, 7)
order_total dtype: float64

The columns say what you can select. The shape says whether all 180 rows came in. float64 means the amounts are real decimals. head(5) is a small peek.

How do you pin types and keep only some columns?

A file may hold 40 columns when you need two. usecols keeps just those, and dtype stops the guessing.

Predict: which dtypes will the pinned table report?

PythonPinning schemas and selecting columns with usecols and dtype
import io

orders_text = """order_id,user_id,order_status,order_total
ORD-0001,U004,paid,612.07
ORD-0002,U073,refunded,446.70
"""
df_pinned = pd.read_csv(
    io.StringIO(orders_text),
    usecols=["order_id", "order_total"],
    dtype={"order_id": "string", "order_total": "float64"},
)
print(df_pinned["order_id"].dtype, df_pinned["order_total"].dtype)
string float64

Pinning also guards long ids: an id read as a number can lose digits, so pin it as string.

How do you turn text dates into real dates?

By default created_at is text, so calendar tools fail on it. parse_dates converts it as it loads.

Predict: which of these two reads lets .dt.hour work?

PythonText dates versus parsed dates
import io

text_dates = "order_id,created_at\nORD-0001,2026-08-02 16:33:28\n"
plain = pd.read_csv(io.StringIO(text_dates))
dated = pd.read_csv(io.StringIO(text_dates), parse_dates=["created_at"])
print(dated["created_at"].dt.hour.iloc[0])
print(plain["created_at"].dt.hour.iloc[0])
16
AttributeError: Can only use .dt accessor with datetimelike values

Only the parsed column supports calendar tools such as .dt.hour.

What if missing values are written as N/A or MISSING?

Old systems write missing numbers as words. Unhandled, those words turn the column into text.

PythonTurning custom tokens into real nulls
import io

text_sentinels = """order_id,order_status,order_total
ORD-101,paid,120.50
ORD-102,pending,N/A
ORD-103,paid,MISSING
"""
df_sentinels = pd.read_csv(io.StringIO(text_sentinels), na_values=["N/A", "MISSING"])
print("na_values null count:\n", df_sentinels.isna().sum())

Predict: how many nulls will order_total report?

na_values null count:
 order_id        0
order_status    0
order_total     2
dtype: int64

Both tokens became real nulls, and order_total is a number column again.

What if the file starts with a banner?

Billing exports often put comment lines above the real header. Skip them with skiprows.

PythonBypassing metadata banners with skiprows
import io

text_with_banner = """# Export Date: 2026-10-09
# Source System: ShopKart Billing
order_id,order_status,order_total
ORD-0001,paid,612.07
ORD-0002,refunded,446.70
"""
df_skipped = pd.read_csv(io.StringIO(text_with_banner), skiprows=2)
print("skipped shape and columns:", df_skipped.shape, df_skipped.columns.tolist())

Predict: which columns survive after skipping the two banner lines?

skipped shape and columns: (2, 3) ['order_id', 'order_status', 'order_total']

What if the file is too big for memory?

chunksize makes read_csv return batches. You total each batch and never hold the whole file.

PythonStreaming with a chunksize iterator
import io

text = "order_id,order_total\nORD-0001,612.07\nORD-0002,446.70\nORD-0003,1104.32\n"
running_total = 0.0
total_rows = 0
for chunk in pd.read_csv(io.StringIO(text), chunksize=2):
    total_rows += len(chunk)
    running_total += chunk["order_total"].sum()
print("chunked total rows:", total_rows, "total sum:", round(running_total, 2))

Predict: how many rows and what sum come back from 2-row chunks?

chunked total rows: 3 total sum: 2163.09

The same 3 rows and the same total as the full read, in 2 batches.

What if the text was saved in the wrong encoding?

Older exports often use Latin-1 instead of UTF-8. An accented letter then stops the read.

PythonNaming the encoding
import io

raw = "name\nJosé\n".encode("latin1")
print(pd.read_csv(io.BytesIO(raw), encoding="latin1"))
   name
0  José

Without encoding="latin1", the same bytes raise UnicodeDecodeError, because byte 0xe9 is not valid UTF-8.

What do you check after every load?

A read that raises no error is not a read that returned the right data.

  • The row count is not zero, and matches what you expect: df.shape.
  • The columns you need are in df.columns.
  • A key column has no gaps: df["order_id"].isna().sum() == 0.
  • The types look right: df.dtypes.

Each check catches a different silent failure. A zero row count means the path or filter was wrong. A missing column usually means a header typo or the wrong separator. A gap in a key column means broken rows. A date stored as object, which is pandas for text, means date maths will fail later.

Watch outCheck the shape right after loading

A wrong separator, a wrong header line or the wrong text encoding will not always raise an error. They can give you a table of the wrong shape. If the column count is not what you expect, or 1 column holds whole lines of text, stop and fix the read_csv options before anything else.

How does a whole load replay?

From a file to a trusted table
  1. Pick the reader. The file is semicolon-separated text, so read_csv.

    1. orders.csv
    2. pd.read_csv
  2. Pass options that match the file: sep, dtype, parse_dates, na_values.

    1. sep=";"
    2. dtype
    3. parse_dates
  3. Check shape, columns and dtypes before anything else.

    1. df.shape
    2. df.columns
    3. df.dtypes
  4. Only now filter and clean.

    1. Clean table

Five things to know from this lesson:

  1. Each file type has one reader, and each returns a DataFrame.
  2. A wrong sep gives one giant column. Check the shape to catch it.
  3. Pin types with dtype, dates with parse_dates, and missing tokens with na_values.
  4. Use usecols, skiprows and chunksize to read only what you need.
  5. After every load, check rows, columns, gaps and types before moving on.
RememberWorth remembering

After every load, look at the shape, the column names and the dtypes before you do anything else. Most bad numbers later trace back to a read that quietly guessed wrong. read_csv on an io.StringIO text also works in this tab.

Common beginner questions

Why can't I read a real file in this tab?

The editor runs in your browser and has no folder of your files to open. You can still practise the reading itself with io.StringIO as above. On your laptop, the first line of a script is usually pd.read_csv with the file's path.

Should I use CSV or Excel for a data pipeline?

CSV when you have the choice. It is plain text, so it is easy to look at, compare and store. Excel files can hide several sheets, merged cells and formatting that get in the way. Use read_excel when that is what the source sends.

Will read_json always give a clean table?

Only when the JSON is already row-shaped, meaning a list of objects with the same keys. Nested objects arrive as one cell holding a whole dictionary and need to be flattened. The PySpark track covers doing that at a larger scale.

What comes next

Once the table is loaded, the next lesson is about cleaning it: blank cells, numbers stored as text, and words spelled in different ways.

Practice

The exercise asks you to print the column names of df_orders as a Python list using df_orders.columns.tolist() and keep the first 5 rows in result using df_orders.head(5). Make sure the grid ends up with exactly 5 rows.

Going further: a zip code is text, not a number

pandas guesses a type for each column while it reads. For identifiers that look like numbers (zip codes, phone numbers, product codes) the guess is wrong, and the damage is silent. Predict what happens to the leading zero.

PythonLeading zeros
import io
import pandas as pd

csv = "customer,zip\nAsha,02134\nBen,10001\n"

guessed = pd.read_csv(io.StringIO(csv))
as_text = pd.read_csv(io.StringIO(csv), dtype={"zip": str})

print(guessed["zip"].tolist())
print(as_text["zip"].tolist())
[2134, 10001]
['02134', '10001']

The guessed column became the numbers 2134 and 10001. The leading zero is gone, and it cannot be recovered from the number alone. Declaring dtype={'zip': str} keeps the original text.

A second habit: peek before you load. nrows reads only the first rows, so you can inspect columns and types on a huge file in a moment.

PythonPeek at the first rows
import io
import pandas as pd

csv = "id,amount\n1,10\n2,20\n3,30\n4,40\n"
print(pd.read_csv(io.StringIO(csv), nrows=2))
   id  amount
0   1      10
1   2      20

Try a new scenario

A file has a column order_id with values such as 000123 and 000124, and a column amount that sometimes contains 'N/A'. Which read_csv arguments protect you, and what does the amount column become?

Open the answer

dtype keeps the ids as text and na_values tells pandas that 'N/A' means missing. The result is ['000123', '000124'] [10.5, nan]: the ids keep their zeros and amount stays a number column with one missing value (nan). Without na_values the whole amount column would become text because of the single 'N/A'.

Why read only some columns?

Fewer columns means less memory and faster loading. With a wide file, usecols can turn a failing load into a quick one.

Close the lesson and explain it

Without scrolling up, explain why an identifier should be read as text, and what nrows is for.

Practicals · load into the editor

After you read the theory, run these in the pane on the right. They execute in this tab, no cluster.

Finished this lesson?

Mark it as read when you could explain it to someone else.

Rate:
Was this useful?