Ravindra BagaleCourses & study guides Track your progress

Guides

How to Read CSV and Excel Files with pandas

By , Data Science & Data Analytics trainer LinkedIn Published: Last updated:

Read this guide in: English · मराठी · हिंदी

pd.read_csv loads a text table. pd.read_excel loads a sheet from .xlsx. Then head() and shape prove the file landed. Shop example total stays 190 when the file is right.

Chala mitrano! The file sits on disk until pandas reads it. Today CSV and Excel with calm checks — shop sales and an Infosys-style hours sheet. Samajla ka?

Quick answer

Vertical load path:

  1. Put shop_sales.csv in your project folder.
  2. df = pd.read_csv("shop_sales.csv")
  3. df.head() and df.shape
  4. For Excel: pip install openpyxl then pd.read_excel("client_hours.xlsx")
  5. Fix sep / header / sheet_name when the preview looks wrong.
  6. Confirm a known total (example 190).
file on disk → read_csv/read_excel → head → shape → trust

Language: English · मराठी · हिंदी

What do I need before this guide?

  • Working Jupyter + pandas (install guide).
  • A sample CSV you own.
  • Course: read CSV and Excel.

Before and after (look at the tables first)

Before read_csv CSV and Excel still on disk.

Before — CSV / Excel still only on disk.

After read_csv Both files loaded; sum 190.

After — read_csv / read_excel; sum 190.

Before: files only on disk. After: in-memory tables with sales sum 190.

Why read carefully?

  1. Wrong delimiter merges all columns into one.
  2. Wrong header row turns numbers into column names.
  3. Wrong Excel sheet silently loads the empty tab.

Why you care: cleaning cannot fix a file you never loaded correctly.

read_csv — shop sales

Suppose shop_sales.csv:

city,channel,sales
Mumbai,Online,120
Pune,Online,70

import pandas as pd
df = pd.read_csv("shop_sales.csv")
print(df.head())
print(df["sales"].sum())  # 190

Useful knobs (when needed)

  1. sep=";" — European Excel CSV exports.
  2. encoding="utf-8" or "latin-1" — weird characters.
  3. header=0 — default first row as names.
  4. nrows=100 — peek at huge files.
  5. usecols=["city", "sales"] — load fewer columns.

read_excel — Infosys-style client sheet

Suppose client_hours.xlsx sheet hours:

client hours
A 100
B 90 
xlsx = pd.read_excel("client_hours.xlsx", sheet_name="hours")
print(xlsx.head())
print(xlsx["hours"].sum())  # 190

Notes:

  1. Install openpyxl for many .xlsx files.
  2. sheet_name=0 reads the first sheet.
  3. Merged title rows above the table may need skiprows=....

What you see after a good load

  1. Column names match the file.
  2. shape row count matches Excel’s data rows (not title banners).
  3. A hand-check total matches pandas.

Netflix-style watches CSV (missing later)

You can load first, clean later:

title,watches
Show A,100
Show B,
Show C,90
  1. read_csv succeeds.
  2. Empty watches becomes NaN.
  3. Next cleaning guide handles fillna/dropna.

Common errors

Symptom Cause Fix
FileNotFoundError Wrong folder os.getcwd(); move file or fix path
One giant column Wrong sep sep=";" or check delimiter
Unnamed: 0 Extra index column saved index_col=0 or drop column
Excel empty Wrong sheet Pass sheet_name

Ravindra Bagale's Tip

After every load, print shape, columns.tolist(), and one sum. Three lines. Saves one hour of fake debugging. Samajla ka?

Practice task

  1. Save a two-row shop CSV that totals 190.
  2. Load with read_csv and prove the sum.
  3. Save the same numbers in Excel; load with read_excel.
  4. Break the CSV delimiter on purpose, then fix with sep.

Learn it properly

Course lessons:

Related guides: DataFrame beginners · Filter rows and columns

Samajla ka? read_csv and read_excel pull files into DataFrames. head + sum prove trust. Then filter. Aata pudhe jaauya.

Frequently asked questions

Do I need openpyxl?

Yes for many modern .xlsx files with read_excel.

Unicode / encoding errors on CSV?

Try encoding="utf-8" or encoding="latin-1" after you confirm the file source.

My header is wrong

Use header=0 (default) or header=1 if titles sit above the real columns; skiprows can help.

Semicolon CSV from Excel?

sep=";" if commas are not the delimiter.

Large files?

Start with nrows=100 while exploring; full load later.

Course lesson?

read_csv and Excel in the pandas chapter — linked below.