🔢 Week 2 – NumPy & Pandas · Day 11
Day 11: Read CSV, Excel, JSON & Explore
⏱ 90 min📶 Intermediate🎯 100% self-study
Day 11 / 60
✅ Today you will learn
- read_csv options that save hours
- Read and write Excel sheets
- Read JSON from APIs (json_normalize)
- A 5-minute checklist for any new dataset
- Save cleaned results to CSV / Excel
🏢 In the real world
Your manager mails a 40 MB Excel file at 10 a.m. and asks “What’s in it?” by 11. A repeatable read-and-explore routine lets you answer with confidence – rows, columns, types, missing values, ranges and the first surprises.
read_csv – the most used function
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
print(sales.shape)
print(sales.head(3))(2000, 13)
order_id order_date city region category product quantity unit_price discount payment_mode customer_age customer_gender rating
0 BK100359 2024-01-01 Delhi North Fashion Jeans 2 1220 0.0 UPI 48.0 Male 5.0
1 BK100422 2024-01-01 Mumbai West Fashion Sneakers 1 2327 0.1 UPI 44.0 Female 4.0
2 BK100744 2024-01-01 Mumbai West Grocery Tea 1kg 5 525 0.0 UPI 18.0 Male 4.5
| Option | What it does | Example |
|---|---|---|
| usecols | Read only some columns (less memory) | usecols=["city", "unit_price"] |
| nrows | Read the first n rows (quick peek) | nrows=1000 |
| parse_dates | Convert date text to real dates | parse_dates=["order_date"] |
| dtype | Force a type, e.g. keep IDs as text | dtype={"pincode": str} |
| sep | Other separators | sep=";" or sep="\t" |
| na_values | Extra words that mean missing | na_values=["NA", "-", "?"] |
| encoding | Files from Excel/Windows | encoding="latin-1" |
Python
import pandas as pd
small = pd.read_csv("sales_data.csv", usecols=["order_date", "city", "category", "unit_price"],
parse_dates=["order_date"], nrows=5)
print(small)
print(small.dtypes) order_date city category unit_price
0 2024-01-01 Delhi Fashion 1220
1 2024-01-01 Mumbai Fashion 2327
2 2024-01-01 Mumbai Grocery 525
3 2024-01-01 Mumbai Books & Stationery 154
4 2024-01-01 Bengaluru Fashion 515
order_date datetime64[ns]
city object
category object
unit_price int64
dtype: object
Python
import pandas as pd
from io import StringIO
raw = """id;city;pincode;amount
1;Pune;411001;450
2;Agra;-;?
3;Delhi;110001;1299"""
df = pd.read_csv(StringIO(raw), sep=";", dtype={"pincode": str}, na_values=["-", "?"])
print(df)
print(df.dtypes) id city pincode amount
0 1 Pune 411001 450.0
1 2 Agra NaN NaN
2 3 Delhi 110001 1299.0
id int64
city object
pincode object
amount float64
dtype: object
💡 Keep ID codes as text
Pincodes, phone numbers and IDs like 012345 lose their leading zero if read as numbers. Use dtype={"pincode": str}.
Excel files
Python
import pandas as pd
sales = pd.read_csv("sales_data.csv")
with pd.ExcelWriter("report.xlsx") as xl: # needs: pip install openpyxl
sales[sales["region"] == "North"].to_excel(xl, sheet_name="North", index=False)
sales[sales["region"] == "South"].to_excel(xl, sheet_name="South", index=False)
sheets = pd.read_excel("report.xlsx", sheet_name=None) # all sheets -> dict
for name, df in sheets.items():
print(name, df.shape)
north = pd.read_excel("report.xlsx", sheet_name="North", usecols="A:D", nrows=3)
print(north)North (508, 13)
South (654, 13)
order_id order_date city region
0 BK100359 2024-01-01 Delhi North
1 BK100281 2024-01-02 Jaipur North
2 BK100294 2024-01-02 Delhi North
JSON and APIs
Python
import pandas as pd
api_response = [
{"id": 1, "customer": {"name": "Riya", "city": "Pune"}, "items": 3, "total": 1450},
{"id": 2, "customer": {"name": "Om", "city": "Delhi"}, "items": 1, "total": 15999},
]
flat = pd.json_normalize(api_response) # nested keys -> columns
print(flat)
flat.to_json("orders.json", orient="records", indent=2)
print(pd.read_json("orders.json").shape) id items total customer.name customer.city
0 1 3 1450 Riya Pune
1 2 1 15999 Om Delhi
(2, 5)
Python – reading straight from a URL (works the same way)
url = "https://example.com/data/sales.csv" # any public CSV link df = pd.read_csv(url) # APIs: import requests; data = requests.get(api_url).json(); pd.json_normalize(data)
The 5-minute exploration checklist
- 1
df.shape– how big? - 2
df.head()/df.sample(5)– what does a row look like? - 3
df.info()– column types and missing counts - 4
df.describe()– ranges of numbers; spot impossible values - 5
df.isna().sum()anddf.nunique()– gaps and categories - 6
df["col"].value_counts()– the big categories
Python
import pandas as pd
df = pd.read_csv("telecom_churn.csv")
print("Shape:", df.shape)
print(df.sample(3, random_state=1))
df.info()Shape: (3000, 13)
customer_id gender senior_citizen tenure_months contract internet_service tech_support payment_method monthly_charges total_charges \
1957 CUST11958 Female 1 41 Monthly NaN No internet UPI AutoPay 186.0 7475.0
2087 CUST12088 Male 0 33 Monthly Fiber No Bank Transfer 687.0 22982.0
1394 CUST11395 Female 0 60 Monthly NaN No internet Cash 277.0 16529.0
complaints data_usage_gb churn
1957 0 4.2 No
2087 2 61.6 No
1394 1 4.6 No
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 3000 entries, 0 to 2999
Data columns (total 13 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 customer_id 3000 non-null object
1 gender 3000 non-null object
2 senior_citizen 3000 non-null int64
3 tenure_months 3000 non-null int64
4 contract 3000 non-null object
5 internet_service 2375 non-null object
6 tech_support 3000 non-null object
7 payment_method 3000 non-null object
8 monthly_charges 3000 non-null float64
9 total_charges 2975 non-null float64
10 complaints 3000 non-null int64
11 data_usage_gb 3000 non-null float64
12 churn 3000 non-null object
dtypes: float64(3), int64(3), object(7)
memory usage: 304.8+ KB
Python
import pandas as pd
df = pd.read_csv("telecom_churn.csv")
print(df.describe().round(1))
print("\nMissing values:\n", df.isna().sum()[df.isna().sum() > 0])
print("\nUnique values per column:\n", df.nunique()) senior_citizen tenure_months monthly_charges total_charges complaints data_usage_gb
count 3000.0 3000.0 3000.0 2975.0 3000.0 3000.0
mean 0.2 36.1 672.0 23946.4 0.9 57.8
std 0.4 20.6 263.2 17373.9 1.0 42.4
min 0.0 1.0 149.0 189.0 0.0 1.0
25% 0.0 18.0 459.0 9853.0 0.0 26.0
50% 0.0 35.0 679.0 20170.0 1.0 55.6
75% 0.0 54.0 881.0 35249.5 1.0 83.2
max 1.0 72.0 1408.0 84303.0 8.0 298.5
Missing values:
internet_service 625
total_charges 25
dtype: int64
Unique values per column:
customer_id 3000
gender 2
senior_citizen 2
tenure_months 72
contract 3
internet_service 2
tech_support 3
payment_method 4
monthly_charges 961
total_charges 2894
complaints 8
data_usage_gb 1112
churn 2
dtype: int64
Python
import pandas as pd
df = pd.read_csv("telecom_churn.csv")
print(df["contract"].value_counts())
print(df["churn"].value_counts(normalize=True).round(3))
print(df.describe(include="object"))contract
Monthly 1643
One year 757
Two year 600
Name: count, dtype: int64
churn
No 0.709
Yes 0.291
Name: proportion, dtype: float64
customer_id gender contract internet_service tech_support payment_method churn
count 3000 3000 3000 2375 3000 3000 3000
unique 3000 2 3 2 3 4 2
top CUST10001 Male Monthly Fiber No UPI AutoPay No
freq 1 1520 1643 1327 1548 911 2126
🔎 First surprises
In 5 minutes we already know: 3,000 customers, 25 missing total_charges, 29% churned, and most customers are on monthly contracts. Write such notes down – they become the “Insights” slide of your project (Day 57).
Saving results
Python
import pandas as pd
df = pd.read_csv("telecom_churn.csv")
monthly = df[df["contract"] == "Monthly"]
monthly.to_csv("monthly_customers.csv", index=False)
print("Saved", len(monthly), "rows;", round(len(monthly) / len(df) * 100, 1), "% of customers")
print(pd.read_csv("monthly_customers.csv").columns.tolist()[:5])Saved 1643 rows; 54.8 % of customers
['customer_id', 'gender', 'senior_citizen', 'tenure_months', 'contract']
🏅 Job skills unlocked today
✔ Read CSV with the right options✔ Read and write multi-sheet Excel✔ Flatten JSON from APIs✔ Explore any dataset with a checklist
Practice tasks
🏋️ Practice – do it yourself
Task 1: Read only
city, area_sqft and price_lakh from house_prices.csv and print the 3 largest houses.Show solution
Python
import pandas as pd
h = pd.read_csv("house_prices.csv", usecols=["city", "area_sqft", "price_lakh"])
print(h.nlargest(3, "area_sqft")) city area_sqft price_lakh
290 Hyderabad 3080 199.97
742 Pune 2790 158.92
56 Bengaluru 2740 145.01
Task 2: Run the 5-minute checklist on
loan_applications.csv. What share of applications were approved?Show solution
Python
import pandas as pd
loans = pd.read_csv("loan_applications.csv")
print(loans.shape, loans.isna().sum().sum())
print(loans["approved"].mean().round(3))(1200, 7) 0
0.462
Task 3: Save Electronics orders from
sales_data.csv into an Excel file with one sheet per region.Quick quiz
🧠 Quick Quiz – test yourself
1. Which option reads only the first 100 rows?
nrows limits how many rows are read.
2. Why use dtype={"pincode": str}?
Codes are labels, not numbers.
3. pd.read_excel(file, sheet_name=None) returns…
Each key is a sheet name, each value a DataFrame.
4. Which function turns nested JSON into columns?
customer.name, customer.city become columns.
5. to_csv(..., index=False) avoids…
Without it the row numbers are written as a column.
Interview corner
💼 Interview Corner
What is the first thing you do with a new dataset?
Check its size and a sample of rows, then info() for types and missing values, describe() for ranges and impossible values, nunique()/value_counts() for categories, and write down questions and data-quality issues before any modelling.
How do you read a file too big for memory with pandas?
Use usecols and suitable dtypes (category, int32) to shrink it, or read in chunks with chunksize and aggregate each chunk; for very large data use a database, Parquet files or tools like Polars/Spark.
Summary
- read_csv with usecols, nrows, parse_dates, dtype and na_values.
- read_excel / to_excel handle multi-sheet workbooks.
- json_normalize flattens API data.
- Checklist: shape → head → info → describe → isna/nunique → value_counts.
➡️ Next
Day 12: select exactly the rows and columns you need with loc, iloc, boolean filters, query and sorting.
Frequently Asked Questions
Excel file says “Missing optional dependency openpyxl”?
Run pip install openpyxl once. Old .xls files need pip install xlrd.
UnicodeDecodeError while reading a CSV?
The file is not UTF-8. Try encoding="latin-1" or encoding="cp1252" (common for Excel exports on Windows).
What is Parquet?
A compressed, column-based file format that is much smaller and faster than CSV. Companies use it for big data: df.to_parquet() and pd.read_parquet().
