Pandas: Read CSV and Excel Files

Analysis begins the moment you load a file, and in the real world that file is almost always a CSV or an Excel workbook. Pandas reads both with a single function call, then hands you a DataFrame ready to inspect, filter and summarize. This tutorial covers the reading functions you will use on nearly every project, plus the options that separate a clean load from a messy one.

Getting the read right matters more than beginners expect. If a numeric column loads as text, or missing values are not recognized, every statistic downstream is wrong. Learning the handful of important options up front saves hours of debugging later.

Reading a CSV file

The workhorse is pd.read_csv. Point it at a file path and it returns a DataFrame, treating the first line as column headers and inferring each column's type:

import pandas as pd

df = pd.read_csv("employees.csv")
print(df.head())

Assume employees.csv holds this small illustrative sample:

name,city,age,salary
Aarti,Hyderabad,24,48000
Bhaskar,Hyderabad,31,65000
Chitra,Bengaluru,28,52000
Devan,Pune,45,90000

The resulting DataFrame looks like this:

      name       city  age  salary
0    Aarti  Hyderabad   24   48000
1  Bhaskar  Hyderabad   31   65000
2   Chitra  Bengaluru   28   52000
3    Devan       Pune   45   90000

Pandas correctly read the header row and inferred age and salary as integers. Always confirm with df.dtypes that the types are what you expect before analyzing.

Common read_csv options

Real files are rarely perfectly clean. These are the options you will reach for most often:

df = pd.read_csv(
    "employees.csv",
    usecols=["name", "salary"],   # keep only these columns
    na_values=["unknown", "-"],   # treat these as missing
    thousands=",",                # 1,00,000 style numbers -> numbers
)

• usecols loads only the columns you name, which is faster and lighter on wide files.
• sep changes the delimiter; use sep=";" for semicolon files or sep="\t" for tab-separated data.
• na_values lists extra markers that should become NaN, on top of the blanks pandas already recognizes.
• nrows reads just the first N rows, handy for peeking at a huge file before committing to the full load.
• skiprows ignores leading junk rows such as report titles above the real header.

For a file with no header row at all, pass header=None and optionally supply names=[...] so your columns are not just numbers.

Reading an Excel file

Spreadsheets work almost identically through pd.read_excel. The one extra requirement is the openpyxl package, installed once:

pip install openpyxl

df = pd.read_excel("report.xlsx", sheet_name="Q1")
print(df.head())

sheet_name selects the tab. Pass a name like "Q1", a zero-based position like 0 for the first sheet, or None to read every sheet at once into a dictionary of DataFrames. Everything you learned about usecols, na_values and headers applies to read_excel too, so the mental model transfers directly.

Verifying the load

Never trust a load blindly. Run the same quick inspection every time:

print(df.shape)      # did every row and column arrive?
print(df.dtypes)     # are numbers numeric, dates dates?
print(df.isna().sum())  # how many missing per column?

(4, 4)
name      object
city      object
age        int64
salary     int64
dtype: object
name      0
city      0
age       0
salary    0
dtype: int64

Those three lines catch the vast majority of load problems: wrong row counts from a bad delimiter, text-typed numbers from stray characters, and unexpected missing values.

Practical usage

A typical analyst script starts with a read, an inspection block, and a short cleaning section, before any real analysis. Because read_csv and read_excel are just function calls, you can rerun the whole pipeline whenever the source file updates, which is the entire point of doing analysis in code rather than a spreadsheet. When a monthly export lands, one command reproduces last month's report against the new data.

Common mistakes

• Assuming the first row is data, not a header. If your file has no header and you forget header=None, pandas silently promotes your first data row to column names, corrupting the table.
• Wrong delimiter. Loading a semicolon or tab file without setting sep dumps every row into a single column. If shape shows one column when you expected many, the delimiter is wrong.
• Unrecognized missing markers. A column full of "NA"-style text stays as object and refuses to compute a mean. Use na_values so those become real NaN.
• Encoding errors. Files exported from some systems raise a UnicodeDecodeError. Pass encoding="latin-1" or encoding="utf-8-sig" to fix the common cases.
• Ignoring dtypes after loading. Always check that numeric columns are numeric; a hidden currency symbol or comma keeps them as text.

In interviews

Interviewers frequently ask "how would you load and validate a messy CSV?" A strong answer walks through read_csv with sep, na_values and usecols, then the verification triad of shape, dtypes and isna().sum(). You may also be asked how you would handle a 10 GB file that does not fit in memory, where the good answer mentions nrows for sampling and chunksize for streaming through it in pieces. Showing that you validate a load rather than trust it marks you as someone who has debugged real data.

Where this fits in your learning path

Loading data is step one of the data analytics path, right after the pandas introduction. Once your data is in a DataFrame, the next skills are selecting the rows you care about with filtering rows and parsing any date columns correctly with datetime handling. Reliable data loading is a core expectation of the data analyst role.