📞
Real-world scenario
Grace (Safaricom): 2M call records that break Excel

Grace works as a business analyst at Safaricom in Nairobi. For 4 years, she has analysed customer behaviour in Excel: pivot tables, VLOOKUPs, macros. But this week, the data warehouse dumped a CSV with 2 million call detail records (CDRs)—every call, every text, every data session for March 2026.

She opens the file in Excel. It crashes.

She opens it with her laptop's default text editor. 40 MB of raw comma-separated values scrolls endlessly. She can't see any patterns. Then she learns Python, loads the data with pandas in 3 seconds, and spots the insight in minutes.

2M
Call records
40 MB
File size
3 sec
Load time

The transformation: from raw text to queryable table

This is the core of data analysis—turning chaos into insight. Below, the same CSV data appears twice: left as it lives in a text editor, right as pandas transforms it.

caller_id,callee_id,timestamp,duration_s 254712345678,254787654321,2026-03-15 08:15:30,45 254712345679,254787654322,2026-03-15 08:42:15,120 254712345680,254787654323,2026-03-15 09:05:00,78 254712345681,254787654324,2026-03-15 09:33:45,200 254712345682,254787654325,2026-03-15 10:12:10,65 254712345683,254787654326,2026-03-15 10:50:30,145
→
pd.read_csv()
caller_id callee_id timestamp duration_s
254712345678 254787654321 2026-03-15 08:15:30 45
254712345679 254787654322 2026-03-15 08:42:15 120
254712345680 254787654323 2026-03-15 09:05:00 78
254712345681 254787654324 2026-03-15 09:33:45 200
254712345682 254787654325 2026-03-15 10:12:10 65
The punchline: Raw text becomes labelled columns. Pandas infers data types (dates as dates, numbers as numbers, text as text). Now you can slice, filter, group, and analyse with one line of Python.

read_csv essentials: the 3-second load

The simplest case: a well-formed CSV in the same folder as your Python file.

📄
Sample data to follow along
Download safaricom_cdrs.csv and place next to your script.
load.py The simplest read_csv
caller_id callee_id timestamp duration_s network_type region_code
0 254712345678 254787654321 2026-03-15 08:15:30 45 4G 001
1 254712345679 254787654322 2026-03-15 08:42:15 120 4G 042
2 254712345680 254787654323 2026-03-15 09:05:00 78 3G 001
3 254712345681 254787654324 2026-03-15 09:33:45 200 4G 007
4 254712345682 254787654325 2026-03-15 10:12:10 65 4G 001
What happened: Pandas read the CSV, auto-detected headers from row 0, inferred each column's data type, and loaded all 60 rows into a DataFrame. You can now use df.head(), df.describe(), df.groupby(), and all other pandas tools on the loaded data.

The 8 parameters that fix 95% of messy CSVs

Real-world CSV files are messy. Headers in the wrong row. Semicolons instead of commas. Dates stored as text. Leading zeros stripped. Here are the 8 most important parameters to fix these issues:

Parameter What it does Example
sep= Column separator (default: ','). Use for European CSVs with semicolons. sep=';'
header= Which row is the header (default: 0). Set to 1 if header is on row 2. header=1
names= Supply your own column names instead of reading from file. names=['a','b','c']
usecols= Load only these columns (saves memory on huge files). usecols=['date','amount']
dtype= Force a column's data type (e.g., preserve leading zeros). dtype={'id': str}
parse_dates= Parse specified columns as datetime, not string. parse_dates=['date']
na_values= Treat these strings as null (NaN). Useful for N/A, --, null variants. na_values=['N/A','--']
nrows= Load only the first N rows. Great for previewing huge files. nrows=1000

3 real scenarios where these parameters save the day

Scenario A: Big file preview (2M rows in 45 seconds without parameters, 3 seconds with nrows)

Grace exports 2 million call records from the Safaricom warehouse. She wants to preview them before full processing. Using nrows=10000 loads 10,000 rows in milliseconds, not minutes.

preview.py Preview big files fast with nrows
Load time comparison: Excel (crashes) vs pandas with nrows=10k vs full load
Bar chart comparing load times: CSV takes 12.5 seconds versus Parquet's 0.8 seconds, showing 15x speedup
Why this matters: With nrows=10000, Grace can iterate fast—explore, test logic, fix issues on a small sample. Then load the full 2M rows once, confident the code works. Saves hours of waiting.

Scenario B: Mixed dtypes (region_code loses leading zeros without dtype=str)

Safaricom region codes are strings like "007" (Kisumu), "001" (Nairobi). But pandas sees numbers and converts them to integers: 007 → 7, losing the leading zero. Later, a join fails because the warehouse expects strings.

dtypes.py Force string types to preserve leading zeros
Approach region_code (row 0) region_code (row 2) region_code (row 3) Matches warehouse?
Without dtype=str 1 1 7 ❌ No
With dtype=str '001' '001' '007' ✓ Yes
The fix: Use dtype={'region_code': str} to tell pandas "treat this as text, not numbers". Same pattern works for any column that should be a string: customer IDs, ZIP codes, product codes.

Scenario C: Parsing dates (timestamp loaded as string, not datetime64)

The timestamp column "2026-03-15 08:15:30" loads as a string by default. You can't extract the hour, groupby date, or use time-series operations. But parse_dates=['timestamp'] converts it to a proper datetime object in one line.

dates.py Parse dates for time-series analysis
Average call duration by hour of day (Safaricom CDR data)
Line chart of average call duration by hour showing peak at 8pm (20:00) with 445 seconds average, lowest at 4am with 32.5 seconds
The insight: People make longer calls during peak hours (8pm: 485 seconds average) and shorter calls at night (4am: 35 seconds). With timestamps parsed as datetime, this analysis takes 2 lines of code. Without it, you're stuck manipulating strings.

Reading Excel files with read_excel

When your data lives in .xlsx or .xls instead of CSV, the API is identical—just switch read_csv to read_excel. Pandas auto-detects the Excel engine and loads the sheet.

excel.py Loading Excel workbooks
Note on engines: pandas uses different libraries to read Excel files. On first run, you may see: ImportError: openpyxl is required for .xlsx files. Install it with pip install openpyxl and you're done. (For .xls, pandas uses xlrd automatically.)

Writing data back: CSV vs Excel

After cleaning and analysing, save your work. Both read_csv and read_excel have mirror functions: to_csv and to_excel.

write.py Save cleaned data
Format Command Best for
CSV df.to_csv('file.csv') Data pipelines, version control, large files, interoperability
Excel df.to_excel('file.xlsx') Reports for non-technical stakeholders, multiple sheets, formatting
Pro tip: Use index=False when writing to CSV or Excel. Otherwise, pandas adds a row-number column that clutters your output.

Cheatsheet: read_csv parameters at a glance

Parameter Description Example
filepath_or_buffer Path to the CSV file 'data.csv'
sep Column separator (default: ',') sep=';'
delimiter Alias for sep delimiter='\t'
header Row number of column names (default: 0) header=1
names List of column names names=['a', 'b', 'c']
usecols Columns to load (list or callable) usecols=['date', 'amount']
dtype Data type for each column dtype={'id': str}
parse_dates Columns to parse as datetime parse_dates=['date']
na_values Strings to recognize as NaN na_values=['N/A', '--']
nrows Load only first N rows nrows=1000
skiprows Skip first N rows skiprows=5
encoding File encoding (default: 'utf-8') encoding='latin-1'
🎯 Mini Challenge: Analyse Safaricom peak hours

Task: Load safaricom_cdrs.csv, parse the timestamp column as datetime, and find which hour of the day has the longest average call duration. (Bonus: which region has the most 4G traffic?)

Hint 1: Use parse_dates=['timestamp'] when reading the CSV.

Hint 2: Extract hour with df['timestamp'].dt.hour

Hint 3: Group by hour: df.groupby('hour')['duration_s'].mean()

Hint 4 (bonus): Filter by network_type first: df[df['network_type'] == '4G'].groupby('region_code').size()

Common pitfalls: what breaks and how to fix it

Pitfall 1: Mixed dtypes silently corrupts data
If region_code is "007" but loads as 7, later joins fail. Fix: dtype={'region_code': str}
Pitfall 2: Timestamps are strings, not dates
Can't extract year/month/hour or do time-series operations. Fix: parse_dates=['timestamp']
Pitfall 3: European CSV with semicolons crashes
Default sep=',' fails on European files. Fix: sep=';'
Pitfall 4: Headers are on row 2, not row 1
The data ends up in column names. Fix: header=1 (0-indexed)
Pitfall 5: openpyxl not installed for .xlsx files
ImportError on read_excel. Fix: pip install openpyxl

Also used in...

Domain Spotlight

💳
Fintech: M-Pesa transactions
Load daily transaction CSVs, parse dates, filter by region, compute daily volume trends.
🛒
E-commerce: Jumia order data
Read product catalogs (SKU as string), parse order timestamps, preserve category codes with leading zeros.
🏥
Public health: Clinic visit records
CSV exports from health data systems, dates in various formats, missing values as "unknown" or "N/A".

How AI systems use this

Data loading is step 1 of every machine learning pipeline. Models like scikit-learn, TensorFlow, and transformers all expect clean, typed data. A badly loaded CSV (strings instead of dates, wrong dtypes) breaks the model downstream. Large language models fine-tuned on structured data use the same patterns—load, parse, validate, feed to the model.

Data Pipelines ETL Model Training Data Validation

Interview questions: test your understanding

A pandas DataFrame—a 2D table with rows and columns, labelled and indexed.
Use skiprows=5. This is common with Excel reports that have headers or metadata at the top.
Without it, "007" loads as the integer 7, losing the leading zero. Setting dtype=str preserves "007" as a string. Critical for codes, IDs, and postal codes.
When loading a huge file for the first time. Preview the data, test your logic on a small sample, catch errors early. Once confident, load the full dataset.
Use parse_dates=['date'] combined with format='%d/%m/%Y'. Without the format hint, pandas tries to guess and may parse as month/day/year, reversing the date.

Quiz: 5 questions to test your knowledge

Question 1 of 5
You're loading a Safaricom CSV where the separator is a semicolon (;) instead of a comma. Which parameter do you use?
Question 2 of 5
The timestamp column "2026-03-15 08:15:30" loads as a string. To enable hour extraction with .dt.hour, what do you use?
Question 3 of 5
A 2 MB CSV has 100,000 rows. You want to preview the first 5,000 rows without waiting. Which parameter helps?
Question 4 of 5
Region codes are stored as "001", "042", "007". Without specifying dtype, pandas converts them to 1, 42, 7. What's the fix?
Question 5 of 5
You've cleaned a DataFrame and want to save it to Excel as "report.xlsx" without the row index. What's the correct command?
0
/5
Well done! You've mastered CSV and Excel loading.
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready to analyse real data?

Join the Best AI Program: The AI-Era Analyst — 5 months, fully online, with placement assistance. Week 1 is free.

Explore The AI-Era Analyst →