Skip to content
Kuza Ndoto Academy

Loading Data

Beginner → IntermediateChapter 3 · Page 8 of 204 min readUpdated 15 Nov 2026

Real data usually arrives as a file: a CSV export, an Excel workbook or JSON from a web API. pandas reads each of these into a DataFrame with a single function call, and a few well-chosen options save you a lot of cleaning later.

Reading CSV files

CSV (comma-separated values) is the most common data format. Each line is a row and commas separate the columns. Reading one is usually as simple as:

import pandas as pd

sales = pd.read_csv("sales.csv")                 # a file next to your notebook
sales = pd.read_csv("data/2026/sales.csv")       # a file in a sub-folder
sales = pd.read_csv("https://example.com/sales.csv")   # a file on the web

So that you can run the examples on this page without downloading anything, we'll put the CSV text in a string and wrap it in io.StringIO, which makes a string behave like a file.

import io
import pandas as pd

csv_text = """date,product,county,units,price
2026-01-05,Maize flour,Nairobi,30,180
2026-01-05,Sugar,Kisumu,12,160
2026-01-06,Cooking oil,Nairobi,8,350
2026-01-06,Rice,Nakuru,15,220
"""

sales = pd.read_csv(io.StringIO(csv_text), parse_dates=["date"])
print(sales)
print(sales.dtypes)
date product county units price 0 2026-01-05 Maize flour Nairobi 30 180 1 2026-01-05 Sugar Kisumu 12 160 2 2026-01-06 Cooking oil Nairobi 8 350 3 2026-01-06 Rice Nakuru 15 220 date datetime64[ns] product object county object units int64 price int64 dtype: object

pandas used the first line as column names and guessed each column's type. Thanks to parse_dates, the date column is a real date type (datetime64) rather than text, so you can later sort by date or extract the month.

Useful read_csv options

OptionWhat it doesExample
sepColumn separator if it isn't a commasep=";"
usecolsLoad only some columnsusecols=["date", "units"]
parse_datesConvert columns to datesparse_dates=["date"]
index_colUse a column as the row indexindex_col="date"
dtypeForce column typesdtype={"phone": str}
na_valuesExtra markers that mean "missing"na_values=["-", "none"]
nrowsRead only the first n rows (quick look at a huge file)nrows=1000
encodingText encoding if you get a decode errorencoding="latin-1"
Tip

Read phone numbers, ID numbers and codes such as "0712..." as text with dtype={"phone": str}. Otherwise pandas treats them as numbers and drops the leading zero.

Handling missing-value markers

By default, pandas treats empty fields and common markers such as NA, N/A and n/a as missing (NaN, "not a number"). Add your own with na_values:

messy = """name,amount
Achieng,1200
Brian,-
Fatuma,
"""
df = pd.read_csv(io.StringIO(messy), na_values=["-"])
print(df)
name amount 0 Achieng 1200.0 1 Brian NaN 2 Fatuma NaN

Both the dash and the empty field became NaN. Because NaN is a float, the whole amount column is now float64, which is why 1200 shows as 1200.0. What to do with those gaps is the subject of Cleaning Data.

Reading Excel files

Excel files need the extra package openpyxl (pip install openpyxl). Choose a sheet by name or position:

jan = pd.read_excel("sales.xlsx", sheet_name="January")
first = pd.read_excel("sales.xlsx", sheet_name=0)
all_sheets = pd.read_excel("sales.xlsx", sheet_name=None)   # a dict of DataFrames

With sheet_name=None you get a dictionary whose keys are sheet names and whose values are DataFrames, which is handy when each month is on its own sheet.

Reading JSON

JSON is the usual format for web APIs (see Working with CSV & JSON). A list of records, one dictionary per row, becomes a DataFrame directly:

json_text = '[{"product": "Sugar", "units": 12}, {"product": "Rice", "units": 15}]'
df = pd.read_json(io.StringIO(json_text))
print(df)
product units 0 Sugar 12 1 Rice 15

If you already have the data as Python dictionaries (for example from requests), use pd.DataFrame(records), or pd.json_normalize(records) when the records contain nested dictionaries.

First checks after loading

Every time you load data, take a quick look before analysing:

  • df.shape: did you get the number of rows and columns you expected?
  • df.head(): do the column names and values look right?
  • df.dtypes: are numbers numeric and dates dates, or did they load as text?
  • df.isna().sum(): how many values are missing in each column?

Saving your results

sales.to_csv("clean_sales.csv", index=False)
sales.to_excel("clean_sales.xlsx", index=False)
Common mistake

Forgetting index=False writes the row numbers as an extra column. When you read the file back, you get a mystery column called Unnamed: 0. Other frequent problems: a file that uses semicolons (fix with sep=";"), a UnicodeDecodeError (try encoding="latin-1"), and a FileNotFoundError because the path is relative to the folder you are running from, not the folder the notebook is saved in.

Key points

  • pd.read_csv(), pd.read_excel() and pd.read_json() load files into DataFrames.
  • Options like parse_dates, dtype, na_values and usecols fix problems at load time.
  • Always check shape, head(), dtypes and missing values right after loading.
  • Save with to_csv(..., index=False) to avoid an extra index column.
Up next · pandasCleaning DataFix missing, duplicate and messy values.
→

Tip: use the ← and → keys to move between pages.