💼
Real-world scenario
Thomas (Kochi): quarterly close with 4 date formats

Thomas is a chartered accountant in Kochi, recently pivoted to financial data analysis at a Bangalore fintech. This week: quarterly close. The CFO asks him to merge ledger exports from 3 legacy systems for a consolidated reconciliation report.

Problem: each system exports dates differently. SouthBank's Mumbai branch sends "2026-03-15". The Hyderabad branch exports "15/03/2026". A third exports "March 15, 2026". The amounts are strings with commas: "₹12,400.50". Every analysis operation—sorting by date, summing by month, filtering by transaction type—hangs until the types are fixed.

3
Legacy systems
4
Date formats
₹18 cr
Q1 volume

The transformation: from object to proper dtypes

The left panel shows raw data: all columns are object dtype (pandas' catch-all for "I don't know"). The right panel shows after type conversion: numbers are numeric, dates are datetime, categories are category. This small change unlocks everything.

Before: df.info() output
Column dtype txn_id object amount_inr object txn_date object branch object is_reconciled object Memory: 2.4 MB
Problem: Can't sort dates, can't sum amounts, category takes 3MB.
After: df.info() output
Column dtype txn_id object amount_inr float64 txn_date datetime64[ns] branch category is_reconciled bool Memory: 0.8 MB
Fixed: Sort, aggregate, filter all work. 68% memory saved.
The key insight: dtypes aren't cosmetic. Every operation—groupby, merge, sorting, filtering—depends on correct types. Get this right once, and analysis flows; get it wrong, and you're fighting the data.

Why dtype matters: 5 operations that break silently

When you have the wrong dtype, pandas doesn't crash—it gives you wrong answers silently.

Operation Expected dtype What fails if you use object
Sort by date datetime64[ns] Alphabetical sort: "2026-01-30" comes before "2026-01-05" ❌
Sum amounts int64 / float64 String concatenation: "100" + "200" = "100200" ❌
Filter by category category Case sensitivity: "Kochi" ≠ "kochi" ❌
Extract month from date datetime64[ns] No .dt accessor: "2026-03-15".dt.month → AttributeError ❌
Memory usage for 10M rows category (if <100 unique) Object wastes 40–50x more RAM than category ❌

Example that shows the danger: You have transaction amounts as strings: "1000" and "999". Sorting them alphabetically gives "1000" < "999" — wrong. In SQL reports, you'd catch this. In pandas without proper types, it silently corrupts your analysis.

Casting: 3 essential conversions

1. Object → float (removing currency symbols and commas)

Transaction amounts often come as strings with ₹ prefix and comma grouping. Strip, convert, handle nulls.

📄
Messy ledger data to follow along
Download messy_ledger.csv with 4 date formats and amount strings.
Before conversion Type
₹12,400.00 object
7,500.50 object
25,000 object
clean_amounts.py Remove ₹ and commas, convert to float
After conversion Type
12400.0 float64
7500.5 float64
25000.0 float64

2. Object → category (memory explosion for low-cardinality columns)

Columns like branch with only 5 unique values (Kochi, Hyderabad, Bangalore, Chennai, Delhi) repeat thousands of times. Using category cuts memory by 95% because pandas stores a number (0–4) instead of the full string each row.

optimize_branch.py Convert branch to category
Memory usage: 10,000 branch entries as object vs category
Memory usage comparison: object vs category
Why this matters: For 10 million rows across multiple branches, category saves 40+ MB. In analytics at scale, that's the difference between a 2GB notebook and a 50GB memory hog.

3. Object → bool (standardizing inconsistent Y/N patterns)

The is_reconciled column has mixed formats: "Y", "N", "yes", "no", "TRUE", "FALSE". Convert to clean boolean.

clean_bool.py Standardize reconciliation flag
The payoff: Now you can write clean code: df[df['is_reconciled'] == True] instead of hunting for all the case variants.

The date parsing problem: 4 formats in one column

This is Thomas's core pain point. The three systems export dates in 4 different formats, all as strings.

Raw txn_date Source system Problem
2026-03-15 Mumbai API ISO format
15/03/2026 Hyderabad export DD/MM/YYYY (European)
March 15, 2026 Chennai human English month name
15-Mar-26 Legacy Unix Abbreviated month

Without a solution, every date operation fails:

naive_sort.py This will give wrong order

The solution: pd.to_datetime with format='mixed' and dayfirst=True

Pandas can auto-detect most formats if you hint that day comes before month (crucial in India).

parse_dates.py Mixed date format handling
After parsing Type Sortable?
2026-03-15 datetime64[ns] ✓
2026-03-15 datetime64[ns] ✓
2026-03-15 datetime64[ns] ✓
2026-03-15 datetime64[ns] ✓

What if one date is totally garbage? errors='coerce' turns it into NaT (Not a Time), pandas' null for dates. You can then find and investigate:

find_bad_dates.py Spot unparseable dates
Thomas's Q1 ledger: transaction count by day (after parsing dates correctly)
Daily transaction volume for Q1
Why this histogram matters: Only with proper datetime dtype can you extract the day, group by date, and see transaction patterns. Before the fix, every analysis would fail silently or be alphabetically wrong.

The silent timezone bug

Many ledger systems log timestamps as UTC. But transaction cutoff for daily/monthly closes is in local time (IST for Kochi, 5.5 hours ahead of UTC). A transaction at 23:55 UTC might be 5:25 AM IST the next day—pushing it into a different period.

Timestamp (UTC) IST (Kochi) Period
2026-03-15 23:55:00 UTC 2026-03-16 05:25:00 IST April reporting?
2026-03-16 18:30:00 UTC 2026-03-17 00:00:00 IST March reporting
handle_timezone.py Localize UTC to IST
The lesson: Always confirm timezone with your backend team. If logs are UTC but business rules are IST, fix it before any time-based grouping.

Extracting date parts for grouping and analysis

Once dates are properly parsed, pandas gives you the .dt accessor to slice them by year, month, day of week, etc.

extract_parts.py Unlock the .dt accessor on datetime columns

Example: Which day of the week has the highest transaction volume?

day_of_week_analysis.py Group by day name
Transaction count by day of week (after extracting with .dt accessor)
Transaction amount by day of week
This insight is only possible because dates are properly typed: You can extract the day name, group by it, and spot business patterns (paydays, weekends). Try this with string dates and you'll get AttributeError.

Cheatsheet: dtype conversions at a glance

Task Code Result dtype
String → int df['col'] = df['col'].astype('int64') int64
String → float (remove commas) pd.to_numeric(df['col'].str.replace(',',''), errors='coerce') float64
String → bool (Y/N → True/False) df['col'].str.lower().isin(['y', 'yes', 'true']) bool
String → category (low cardinality) df['col'] = df['col'].astype('category') category
String → datetime (ISO) pd.to_datetime(df['col'], format='%Y-%m-%d') datetime64[ns]
String → datetime (mixed formats) pd.to_datetime(df['col'], format='mixed', dayfirst=True, errors='coerce') datetime64[ns]
Datetime → year df['col'].dt.year int64
Datetime → month df['col'].dt.month int64
Datetime → day name df['col'].dt.day_name() object
Datetime → quarter df['col'].dt.quarter int64
Datetime → month-year period df['col'].dt.to_period('M') period[M]
UTC → IST timezone df['col'].dt.tz_localize('UTC').dt.tz_convert('Asia/Kolkata') datetime64[ns, Asia/Kolkata]

Mini challenge: clean the messy ledger

Load messy_ledger.csv and answer: what was the total ₹ spent in March?

Your data has:

  • amount_inr as string with ₹ and commas
  • txn_date in 4 different formats
  • is_reconciled with mixed "Y", "N", "yes", "no"
  • branch with only 5 unique values

Steps:

  1. Clean amount_inr to float64
  2. Parse txn_date to datetime64[ns]
  3. Filter for March 2026
  4. Sum the amounts

df['txn_date'].dt.month == 3 will only work if txn_date is datetime. Use pd.to_datetime with format='mixed' and dayfirst=True.

5 common pitfalls

1. Using errors='raise' (the default) loses bad rows silently
When errors='raise', a single bad date kills the whole parse. Always use errors='coerce' to turn unparseable values into NaN, then investigate with df[df['col'].isna()].
2. Forgetting dayfirst=True in Indian/European date formats
Without dayfirst=True, pandas assumes American MM/DD/YYYY. The date "15/03/2026" becomes invalid (month 15 doesn't exist) and gets NaT. Always specify dayfirst=True for India.
3. Not converting to category for low-cardinality columns
If a column has <100 unique values across 1M+ rows, convert it to category immediately. You save 10–50 MB per column and operations speed up.
4. Converting to category then trying to assign a new value
Once a column is category, you can only assign values that are in the category list. If you need to add "New Branch", extend categories first: df['branch'] = df['branch'].cat.add_categories(['New Branch'])
5. Using astype('int') on a column with NaN
NaN is float, not int. Convert float first, then to int (or use nullable Int64): df['col'].astype('float64').astype('Int64')

Interview prep: 5 questions

String comparison is alphabetical: "1000" < "999" because character '1' comes before '9'. Numeric comparison correctly gives 999 < 1000. Always convert amounts to numeric types (float64, int64) before sorting or aggregating.

It converts unparseable values to NaN (or NaT for dates) instead of raising an exception. This lets your pipeline continue; you investigate bad rows separately. Without it, a single typo ("2026-02-30") crashes the entire parse.

Yes, absolutely. 10M object values (8 bytes + string overhead ≈ 40 bytes each) uses ~400 MB. Category stores integers (0–49) plus a lookup table, using ~40 MB. That's a 10x compression. Plus, groupby, merge, and comparisons are faster on category.

Use pd.to_datetime(df['col'], format='mixed', dayfirst=True, errors='coerce'). The format='mixed' infers each value's format independently. dayfirst=True handles DD/MM formats. Any unparseable rows become NaT for manual review.

The next day. 2026-03-15 23:55 UTC = 2026-03-16 05:25 IST. Always convert UTC to the business timezone before filtering or grouping by date. Use df['ts'].dt.tz_localize('UTC').dt.tz_convert('Asia/Kolkata'), then extract date or day. Missing this causes off-by-one errors in daily closes.

Knowledge check: 5 quick questions

Question 1 of 5
What's the correct way to clean an amount string "₹12,400.50" into a float?
Question 2 of 5
You have 5 million rows with a "branch" column containing only 12 unique values (Kochi, Mumbai, Bangalore, etc.). What's the best dtype?
Question 3 of 5
You're parsing dates with multiple formats: "2026-03-15", "15/03/2026", "March 15, 2026". What's the correct approach?
Question 4 of 5
After parsing dates to datetime64[ns], which of these is NOT a valid operation?
Question 5 of 5
Your timestamps are in UTC, but the business day closes at midnight IST (UTC+5:30). A transaction at "2026-03-15 23:55:00 UTC" should count toward which date's report?
0
/ 5
Well done! You've mastered dtype conversion and date parsing.

Also used in fintech

Domain connections

🏦
SouthBank India (Thomas): ledger reconciliation
Every ledger export has dates in a different system's format. Parse them once, reconcile instantly.
💳
Razorpay: payment settlement amounts
Amounts come from 5 payment gateways with different decimal precision. Normalize to float64, sum by merchant.
📱
M-Pesa: daily transaction volumes
Logs are UTC, but Kenyan business hours are EAT (UTC+3). Convert timezone before daily close.

LLM-assisted parsing: when format='mixed' fails

Occasionally, a date is in a completely non-standard format: "15th of March, 2026" or "2026.03.15" (Chinese style). If format='mixed' gives NaT, you can ask an LLM to rewrite it to ISO format, then parse:

# For each NaT row, send the string to Claude:
# "Rewrite this date in ISO format YYYY-MM-DD: 15th of March, 2026"
# → "2026-03-15"
# Then re-parse
df.loc[mask, 'date'] = cleaned_dates.apply(lambda x: pd.to_datetime(x, format='%Y-%m-%d'))

This is rare—format='mixed' handles 99% of real-world messiness—but useful for legacy hand-entered data.

claude-3-5-sonnet date-inference fallback-parsing
Africa & India's Best AI Academy
🤖 AI-first 🎓 Placement Assistance

Ready to go further?

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 →