Loading Data
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)
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
| Option | What it does | Example |
|---|---|---|
sep | Column separator if it isn't a comma | sep=";" |
usecols | Load only some columns | usecols=["date", "units"] |
parse_dates | Convert columns to dates | parse_dates=["date"] |
index_col | Use a column as the row index | index_col="date" |
dtype | Force column types | dtype={"phone": str} |
na_values | Extra markers that mean "missing" | na_values=["-", "none"] |
nrows | Read only the first n rows (quick look at a huge file) | nrows=1000 |
encoding | Text encoding if you get a decode error | encoding="latin-1" |
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)
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)
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)
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()andpd.read_json()load files into DataFrames.- Options like
parse_dates,dtype,na_valuesandusecolsfix problems at load time. - Always check
shape,head(),dtypesand missing values right after loading. - Save with
to_csv(..., index=False)to avoid an extra index column.
Tip: use the ← and → keys to move between pages.
