Your First pandas DataFrame: Loading CSV and Excel Files and Exploring Data

If you've spent years managing data in Excel or querying databases with SQL, you already understand data at a conceptual level — tables, rows, columns, filters, aggregations. The frustrating part of learning Python for data work isn't the concepts; it's the gap between "I know what I want to do" and "I have no idea how to express that in code." This lesson closes that gap for the most fundamental operation in any data workflow: getting data into Python and understanding what you have before you do anything else.

By the end of this lesson, you'll be able to load CSV and Excel files into pandas DataFrames, immediately audit the shape and quality of your data, understand what's happening under the hood when pandas reads a file (and why the defaults sometimes bite you), and use the exploration toolkit that every experienced data analyst reaches for before writing a single line of analysis. These are skills you'll use on every project, every single day.

What you'll learn:

• How to install and import pandas, and what a DataFrame actually is at a conceptual and technical level
• How to load CSV files with pd.read_csv() including the parameters that matter most in real-world messy files
• How to load Excel files with pd.read_excel() and navigate multi-sheet workbooks
• How to audit the shape, types, and completeness of a freshly loaded DataFrame
• How to sample, filter, and inspect subsets of data as part of a disciplined first-look workflow
• The most common loading errors and how to diagnose and fix them

---

Prerequisites

This lesson assumes you have Python and a working Jupyter or VS Code environment already set up. If you haven't done that yet, start with Setting Up Python for Data Analysis: Python, VS Code, Jupyter, and Virtual Environments before continuing. You should also be comfortable with Python variables, lists, and basic data structures — if that feels shaky, Python Basics for Excel Users: Variables, Lists, Dictionaries, and Loops will bring you up to speed quickly.

You'll need pandas installed. If it's not already:

pip install pandas openpyxl

Note that openpyxl is the engine pandas uses to read modern .xlsx files — you need it separately even though pandas itself doesn't mention it until the moment you try to open an Excel file and get a confusing error.

---

What Is a DataFrame, Really?

Before we start loading files, you need an accurate mental model of what a DataFrame is — not a hand-wavy analogy, but a real understanding of the structure. That model will pay dividends every time you hit an unexpected behavior.

A pandas DataFrame is a two-dimensional, labeled data structure. It has:

• An index — row labels, which by default are integers starting at 0 but can be any hashable type (strings, dates, compound keys)
• Columns — each column is a pandas Series object, which is a one-dimensional labeled array
• Data types per column — unlike Excel, where a column can contain mixed types silently, each pandas Series has a declared dtype

Under the hood, pandas stores the data in NumPy arrays (or, for newer dtypes, Arrow-backed arrays). When you have columns of the same numeric dtype, pandas may store them in a single contiguous block in memory for efficiency. This block-based storage is why certain operations are blazingly fast and why mixing dtypes in a DataFrame has performance implications.

import pandas as pd
import numpy as np

# Build a DataFrame from scratch to see its anatomy
data = {
    'order_id': [1001, 1002, 1003, 1004],
    'customer': ['Acme Corp', 'BrightPath LLC', 'Acme Corp', 'Zenith Industries'],
    'amount': [4250.00, 875.50, 12000.00, 330.00],
    'order_date': ['2024-01-15', '2024-01-15', '2024-01-17', '2024-01-18'],
    'status': ['shipped', 'pending', 'shipped', 'cancelled']
}

df = pd.DataFrame(data)
print(df)

Output:

   order_id           customer     amount  order_date     status
0      1001          Acme Corp    4250.00  2024-01-15    shipped
1      1002     BrightPath LLC     875.50  2024-01-15    pending
2      1003          Acme Corp   12000.00  2024-01-17    shipped
3      1004  Zenith Industries     330.00  2024-01-18  cancelled

Notice the leftmost column of numbers — that's the index. It's not a column in the traditional sense; it's the row label. This distinction matters enormously once you start filtering and merging data. Right now, recognize it for what it is.

Each named column (order_id, customer, amount, etc.) is a Series. You can access a single column and it comes back as a Series with its own index:

print(df['amount'])
print(type(df['amount']))

Output:

0     4250.0
1      875.5
2    12000.0
3      330.0
Name: amount, dtype: float64

That dtype: float64 is pandas telling you this column is stored as 64-bit floating-point numbers. The dtype system is one of the things that makes pandas far more rigorous than Excel — and also one of the things that causes the most confusion for newcomers when a number column loads as object type because of a stray dollar sign or comma.

---

Loading CSV Files with `pd.read_csv()`

pd.read_csv() is the function you'll call more than almost any other in your data career. It looks simple — point it at a file, get a DataFrame — but it has over 50 parameters, and the ones you don't know about are the ones that will silently corrupt your data.

The Basics

Let's say you have a sales export called sales_2024.csv from your CRM. The simplest possible call:

df = pd.read_csv('sales_2024.csv')

This works when the file is in the same directory as your notebook or script. For files elsewhere:

df = pd.read_csv('/Users/yourname/data/sales_2024.csv')
# or on Windows:
df = pd.read_csv(r'C:\Users\yourname\data\sales_2024.csv')
# or with pathlib, which is cleaner:
from pathlib import Path
df = pd.read_csv(Path('data') / 'sales_2024.csv')

The Parameters That Actually Matter

The pandas documentation lists every parameter, but let's focus on the ones you'll encounter in real-world files.

sep — the delimiter

CSV stands for "comma-separated values," but in practice, files often use tabs, semicolons (especially European exports that use commas as decimal separators), or pipes. If your DataFrame loads as a single column with what looks like the entire row jammed in, your delimiter is wrong.

# Tab-separated file
df = pd.read_csv('export.tsv', sep='\t')

# Semicolon-separated (common in European locale exports)
df = pd.read_csv('eu_sales.csv', sep=';')

# Let pandas detect the delimiter automatically (uses Python's csv.Sniffer)
df = pd.read_csv('mystery.csv', sep=None, engine='python')

header and skiprows

Real data files are rarely pristine. Finance exports often have a report title in the first few rows before the actual headers. System-generated files sometimes have metadata blocks. Use skiprows to skip garbage at the top:

# Skip the first 3 rows, then treat row 4 as the header
df = pd.read_csv('finance_report.csv', skiprows=3)

# If the file has no header row at all, tell pandas to generate integer column names
df = pd.read_csv('raw_feed.csv', header=None)

# Supply your own column names
df = pd.read_csv('raw_feed.csv', header=None, 
                 names=['transaction_id', 'account', 'amount', 'timestamp'])

usecols — selective column loading

If you're loading a 200-column CRM export but only need 8 columns, load only those 8. This isn't just about convenience — it's a significant memory and performance win for large files.

# By column name (after pandas reads the header)
df = pd.read_csv('crm_export.csv', 
                 usecols=['customer_id', 'company_name', 'arr', 'renewal_date', 'csm_owner'])

# By position (useful when column names are ugly or you're being defensive)
df = pd.read_csv('crm_export.csv', usecols=[0, 1, 5, 12, 18])

dtype — forcing column types at load time

This is where most beginners get burned. pandas infers dtypes by sampling the file. If a column contains mostly integers but has a few blanks or a "N/A" string, pandas might load it as float64 or object. Worse, a ZIP code column containing values like 07030 will be loaded as integer 7030, silently dropping the leading zero.

df = pd.read_csv('customers.csv', dtype={
    'zip_code': str,           # Preserve leading zeros
    'customer_id': str,        # IDs shouldn't be treated as numbers
    'segment_code': str        # Categorical codes that look numeric
})

parse_dates — handling date columns

Dates are where pandas really diverges from Excel. In Excel, dates are secretly stored as numbers with formatting applied. In pandas, a date column loaded without instruction becomes an object dtype (effectively a string), and you can't do any date arithmetic on it.

# Tell pandas which columns contain dates
df = pd.read_csv('sales_2024.csv', parse_dates=['order_date', 'ship_date'])

# For files where date format is unambiguous, this is enough
# For ambiguous formats like 01/02/2024 (is that Jan 2 or Feb 1?), be explicit
df = pd.read_csv('sales_2024.csv', 
                 parse_dates=['order_date'],
                 date_format='%m/%d/%Y')

na_values — teaching pandas what "missing" looks like in your data

pandas knows to treat empty cells, NaN, None, NULL, and a few others as missing by default. But your data might use "N/A", "#N/A", "-", "n/a", or "Missing" to signal missing values, and pandas will load those as strings.

df = pd.read_csv('survey_results.csv', 
                 na_values=['N/A', 'n/a', 'Missing', '-', 'NONE', ''])

nrows and chunksize — working with large files

For a 10 million row file, loading everything into memory to inspect the structure is wasteful. Load a sample first:

# Just the first 1000 rows to explore structure
df_sample = pd.read_csv('massive_log_file.csv', nrows=1000)

# For truly large files, process in chunks
for chunk in pd.read_csv('massive_log_file.csv', chunksize=100_000):
    # process each chunk
    print(chunk.shape)

Putting It Together: A Realistic `read_csv()` Call

Here's what a careful, production-quality load of a real-world file actually looks like — not the tutorial version with a clean sample:

from pathlib import Path
import pandas as pd

df = pd.read_csv(
    Path('data') / 'q1_2024_orders.csv',
    sep=',',
    skiprows=2,                          # Skip report header rows
    usecols=['Order ID', 'Customer', 'Product', 'Qty', 'Unit Price', 
             'Order Date', 'Ship Date', 'Region', 'Status'],
    dtype={
        'Order ID': str,                 # Don't treat IDs as integers
        'Qty': 'Int64',                  # Nullable integer type
    },
    parse_dates=['Order Date', 'Ship Date'],
    na_values=['N/A', '-', 'TBD', ''],
    thousands=',',                       # Numbers formatted as 1,234
    encoding='utf-8'
)

This is more verbose than pd.read_csv('file.csv'), but it's explicit about every decision that could go wrong — which means when something does go wrong in production at 3 AM, you know exactly where to look.

---

Loading Excel Files with `pd.read_excel()`

Excel files are more complex than CSVs because they're not plain text. A .xlsx file is actually a ZIP archive containing XML files, images, styles, and multiple sheets. Reading Excel files is slower and more memory-intensive than reading CSVs, and you should be aware of that trade-off. If someone keeps sending you Excel files and you have any influence over the process, pushing for CSV exports will save you pain.

That said, Excel is reality. Let's deal with it properly.

The Engine Question

Before you can call pd.read_excel(), you need an underlying library to parse the Excel format. Pandas delegates this to:

• openpyxl — for .xlsx files (the modern format). This is what you should install.
• xlrd — for old .xls files (Excel 97–2003 format). Note that xlrd version 2.0+ dropped support for .xlsx, so if you have old code using xlrd with .xlsx, it will break.
• odf — for OpenDocument .ods files.

pip install openpyxl    # For .xlsx (use this)
pip install xlrd        # For .xls only if needed

Basic Excel Loading

# Read the first sheet by default
df = pd.read_excel('monthly_report.xlsx')

# Specify a sheet by name
df = pd.read_excel('monthly_report.xlsx', sheet_name='January')

# Specify a sheet by position (0-indexed)
df = pd.read_excel('monthly_report.xlsx', sheet_name=0)

Reading Multiple Sheets

When you pass a list of sheet names (or None for all sheets), pandas returns an OrderedDict where keys are sheet names and values are DataFrames:

# Load two specific sheets
sheets = pd.read_excel('monthly_report.xlsx', 
                       sheet_name=['January', 'February', 'March'])

# Load ALL sheets
all_sheets = pd.read_excel('monthly_report.xlsx', sheet_name=None)

# sheets is now a dict
print(type(all_sheets))        # <class 'dict'>
print(list(all_sheets.keys())) # ['January', 'February', 'March', ...]

# Access individual DataFrames
df_jan = all_sheets['January']
df_feb = all_sheets['February']

# Combine all sheets into one DataFrame (if they have the same structure)
df_all = pd.concat(all_sheets.values(), ignore_index=True)

Navigating Real Excel Files

Excel files in the wild are not clean. They have merged cells, totals rows, report headers, and formatting that makes them beautiful for humans and painful for code. Here's how to handle the common scenarios:

Dealing with non-header rows at the top:

# Skip 5 rows of report header, then read the actual table
df = pd.read_excel('finance_export.xlsx', skiprows=5)

Dealing with totals rows at the bottom:

# Skip the last 3 rows (subtotals and grand total)
df = pd.read_excel('finance_export.xlsx', skiprows=5, skipfooter=3)

Specifying the header row explicitly:

# Row 7 (0-indexed as 6) is the actual column header row
df = pd.read_excel('dashboard_export.xlsx', header=6)

Reading a specific rectangular range:

# Only read columns B through H (Excel letters map to usecols)
df = pd.read_excel('report.xlsx', usecols='B:H')

# Or by column index (0-indexed)
df = pd.read_excel('report.xlsx', usecols=[1, 2, 3, 4, 5, 6, 7])

Handling merged cells:

Merged cells are Excel's gift to data analysts' nightmares. When pandas reads a merged cell, it puts the value in the first cell of the merged range and NaN in all subsequent cells. You'll often need to forward-fill these:

df = pd.read_excel('report_with_merged_headers.xlsx')

# If the 'Region' column is merged vertically (one label spanning multiple rows)
df['Region'] = df['Region'].ffill()

A Complete, Defensive Excel Load

import pandas as pd
from pathlib import Path

df = pd.read_excel(
    Path('data') / 'q1_revenue_report.xlsx',
    sheet_name='Revenue Detail',
    engine='openpyxl',
    skiprows=4,            # Skip the title and subtitle rows
    skipfooter=2,          # Skip the grand total row
    usecols='A:J',         # Only the columns we need
    dtype={
        'Account Number': str,
        'Cost Center': str
    },
    parse_dates=['Invoice Date', 'Due Date'],
    na_values=['N/A', '-', 'TBD']
)

# Clean up column names (remove whitespace from headers, common in Excel exports)
df.columns = df.columns.str.strip()

print(f"Loaded {df.shape[0]} rows and {df.shape[1]} columns")
print(df.dtypes)

---

Your First-Look Exploration Toolkit

You've loaded the data. Now what? Before writing any analysis code, you need to understand your data — its shape, its quality, its quirks. Experienced analysts have a standard sequence of checks they run on every new dataset. This section gives you that sequence.

Shape and Structure

# How big is this thing?
print(df.shape)             # (rows, columns) as a tuple
print(f"Rows: {df.shape[0]:,}")
print(f"Columns: {df.shape[1]}")

# What are the column names?
print(df.columns.tolist())

# Column names, dtypes, and non-null counts in one shot
df.info()

df.info() is underrated. Here's what it actually tells you:

<class 'pandas.core.frame.DataFrame'>
RangeIndex: 8432 entries, 0 to 8431
Data columns (total 9 columns):
 #   Column       Non-Null Count  Dtype         
---  ------       --------------  -----         
 0   Order ID     8432 non-null   object        
 1   Customer     8427 non-null   object        
 2   Product      8432 non-null   object        
 3   Qty          8398 non-null   Int64         
 4   Unit Price   8432 non-null   float64       
 5   Order Date   8432 non-null   datetime64[ns]
 6   Ship Date    8201 non-null   datetime64[ns]
 7   Region       8432 non-null   object        
 8   Status       8432 non-null   object        
dtypes: Int64(1), datetime64[ns](2), float64(1), object(5)
memory usage: 682.5 KB

In about two seconds, you now know:

• You have 8,432 rows and 9 columns
• Customer has 5 missing values (8432 - 8427 = 5)
• Qty has 34 missing values — and it's using the nullable Int64 type, not regular int64 (which can't hold NaN at all)
• Ship Date has 231 missing values — probably unshipped orders
• Order Date and Ship Date are properly parsed as dates, not strings
• The whole DataFrame is using about 683 KB in memory

Looking at the Data Itself

# First 5 rows (default)
df.head()

# First 10 rows
df.head(10)

# Last 5 rows — important for spotting totals rows that shouldn't be there
df.tail()

# A random sample — often more representative than head() for large files
df.sample(10, random_state=42)

# A random sample as a fraction of the total
df.sample(frac=0.01, random_state=42)  # 1% of rows

Always call df.tail() in addition to df.head(). Excel exports frequently have a summary row at the bottom that has leaked into your DataFrame. If you only look at the first 5 rows, you'll miss it, and it will corrupt your averages and totals.

df.sample() is more useful than it looks. If your data has a pattern in the first rows (e.g., it's sorted by date, so the first rows are all from January), head() gives you a skewed view. A random sample gives you a more representative picture of what the data actually looks like throughout.

Descriptive Statistics

# Statistical summary of numeric columns
df.describe()

Output:

              Qty   Unit Price
count  8398.000000  8432.000000
mean     12.453191   234.562100
std       8.912043   412.341200
min       1.000000     0.990000
25%       5.000000    24.990000
50%      11.000000    89.990000
75%      18.000000   249.990000
max     999.000000  8999.990000

Look at that max for Qty: 999. Is that a real order, or a data entry error? Is a Unit Price of $0.99 legitimate, or a free item that should be excluded from average price calculations? The min and max values alone surface outliers and data quality issues in seconds.

For non-numeric columns:

# Include object (string) columns in describe
df.describe(include='object')

Output:

       Order ID     Customer  Product Region   Status
count      8432         8427     8432   8432     8432
unique     8432          312      847      4        4
top     ORD-001  Acme Corp.    Widget  North  shipped
freq           1          89       312     2312     5401

This tells you: 312 unique customers, 847 unique products, only 4 distinct regions, 4 distinct statuses. The top and freq rows show the most common value in each column. Acme Corp. appears 89 times — they're your biggest customer by order volume.

Value Counts: Understanding Categorical Columns

# How many orders in each status?
df['Status'].value_counts()

Output:

shipped      5401
pending      1823
processing    901
cancelled     307
Name: Status, dtype: int64

# As percentages
df['Status'].value_counts(normalize=True).round(3) * 100

Output:

shipped       64.1
pending       21.6
processing    10.7
cancelled      3.6
Name: Status, dtype: float64

# Don't forget to check for NaN values separately
df['Status'].value_counts(dropna=False)

dropna=False is critical. By default, value_counts() silently ignores missing values. If your Status column has 50 nulls, you'll never see them unless you explicitly include them. This is one of the most common ways analysts inadvertently undercount missing data.

Checking for Duplicates

# Are there any duplicate rows?
print(df.duplicated().sum())

# Check for duplicates on specific columns (e.g., is Order ID truly unique?)
print(df.duplicated(subset=['Order ID']).sum())

# Show the actual duplicate rows
df[df.duplicated(subset=['Order ID'], keep=False)].sort_values('Order ID')

A duplicate Order ID in what should be a unique identifier column is a serious data quality problem — it means either the source system has issues or your join/merge upstream created row multiplication. You need to find it and understand it before any analysis.

Understanding Missing Data in Depth

# Count missing values per column
df.isnull().sum()

# As a percentage
(df.isnull().sum() / len(df) * 100).round(2)

# Identify rows where ANY column is missing
df[df.isnull().any(axis=1)]

# Identify rows where a SPECIFIC column is missing
df[df['Ship Date'].isnull()].head()

The last two queries are particularly useful. When you find rows with missing values, look at them. Sometimes the missingness is informative — in this case, orders with a null Ship Date are probably just orders that haven't shipped yet, so they shouldn't be dropped; they should be kept and handled appropriately.

Checking Data Types Carefully

print(df.dtypes)

This shows you the dtype of each column. Here's what to look for:

What you see	What it might mean
object on a column that should be numeric	The column has non-numeric characters (e.g., "$", ",", "N/A")
object on a date column	parse_dates wasn't specified or the format wasn't recognized
float64 on a column that should be integer	The column has NaN values (plain int64 can't hold NaN)
int64 on an ID or ZIP code column	Leading zeros will be stripped, IDs can't be joined to string keys

# Check a column that should be numeric but loaded as object
print(df['Unit Price'].dtype)       # object — suspicious
print(df['Unit Price'].head(10))    # Look at actual values

If you see values like '$234.50' or '1,234.00', that's why it loaded as object. You'll need to clean before converting:

df['Unit Price'] = (
    df['Unit Price']
    .str.replace('$', '', regex=False)
    .str.replace(',', '', regex=False)
    .astype(float)
)

A Complete First-Look Workflow

Here's the function I'd recommend building and reusing on every new dataset:

def first_look(df, name="DataFrame"):
    """
    Run a standard first-look audit on a freshly loaded DataFrame.
    """
    print(f"{'='*60}")
    print(f" FIRST LOOK: {name}")
    print(f"{'='*60}")
    
    print(f"\n📐 Shape: {df.shape[0]:,} rows × {df.shape[1]} columns")
    
    print(f"\n📋 Column names:")
    print(df.columns.tolist())
    
    print(f"\n🔍 Data types and null counts:")
    df.info()
    
    print(f"\n📊 Missing values:")
    missing = df.isnull().sum()
    missing_pct = (missing / len(df) * 100).round(2)
    missing_summary = pd.DataFrame({'count': missing, 'pct': missing_pct})
    print(missing_summary[missing_summary['count'] > 0])
    
    print(f"\n🔁 Duplicate rows: {df.duplicated().sum():,}")
    
    print(f"\n📈 Numeric summary:")
    print(df.describe().round(2))
    
    print(f"\n🔤 Categorical summary:")
    cat_cols = df.select_dtypes(include='object').columns
    for col in cat_cols:
        n_unique = df[col].nunique()
        print(f"  {col}: {n_unique} unique values | "
              f"top: {df[col].value_counts().index[0]!r} "
              f"({df[col].value_counts().iloc[0]} times)")
    
    print(f"\n👁️  First 3 rows:")
    print(df.head(3))
    
    print(f"\n👁️  Last 3 rows:")
    print(df.tail(3))
    
    print(f"\n{'='*60}")

# Usage
first_look(df, name="Q1 2024 Orders")

This function runs in seconds and gives you a structured, repeatable audit of any dataset. Build it once, use it everywhere.

---

Advanced Exploration: Indexing and Selecting Data

Once you've done the first-look audit, you need to actually look at specific slices of data to validate your understanding. This requires knowing how pandas selection works.

Column Selection

# Single column — returns a Series
orders = df['Order ID']

# Multiple columns — returns a DataFrame
subset = df[['Customer', 'Order Date', 'Amount']]

# All columns matching a pattern
date_cols = df.filter(like='Date')
numeric_cols = df.select_dtypes(include='number')

Row Selection with `.loc` and `.iloc`

This is where the index pays off. .loc is label-based; .iloc is position-based.

# .iloc — purely by integer position (like array indexing)
df.iloc[0]           # First row
df.iloc[0:5]         # First 5 rows
df.iloc[0:5, 0:3]    # First 5 rows, first 3 columns

# .loc — by label (index label for rows, column name for columns)
df.loc[0]            # Row with index label 0 (same as iloc[0] when index is default)
df.loc[0:4]          # Rows with index labels 0 through 4 INCLUSIVE
df.loc[0:4, 'Customer':'Unit Price']  # Row labels 0-4, columns Customer to Unit Price

Boolean Filtering for Exploration

# Show only shipped orders
df[df['Status'] == 'shipped']

# Orders over $1,000
df[df['Unit Price'] > 1000]

# Orders in the North region that are still pending
df[(df['Region'] == 'North') & (df['Status'] == 'pending')]

# Orders from a specific list of customers
key_customers = ['Acme Corp', 'BrightPath LLC', 'Zenith Industries']
df[df['Customer'].isin(key_customers)]

# Orders from the last 30 days (assuming Order Date is datetime)
cutoff = pd.Timestamp.now() - pd.Timedelta(days=30)
df[df['Order Date'] >= cutoff]

These filters aren't just for analysis — use them during exploration to answer specific questions about data quality: "Are all the nulls in Ship Date actually from pending orders?" or "Do any shipped orders have null quantities?"

# Investigate: shipped orders with null quantity
df[(df['Status'] == 'shipped') & (df['Qty'].isnull())]

If that returns any rows, you have a data quality problem worth flagging before the analysis goes any further.

---

Hands-On Exercise

Work through this exercise using a realistic dataset. If you don't have one handy, download a CSV from Kaggle (any sales, HR, or financial dataset works well) or use the sample data creation code below.

Setup — create sample data files:

import pandas as pd
import numpy as np
from pathlib import Path

# Create a messy CSV with real-world quirks
np.random.seed(42)
n = 500

data = {
    'Account #': [f'ACC-{i:04d}' for i in range(n)],
    'Company Name': np.random.choice(
        ['Acme Corp', 'BrightPath LLC', 'Zenith Industries', 'NovaTech', None], n,
        p=[0.3, 0.25, 0.2, 0.24, 0.01]
    ),
    'Revenue ($)': [f'${v:,.2f}' for v in np.random.exponential(50000, n)],
    'Employees': np.random.choice(
        list(range(10, 500)) + [None, None, None], n
    ),
    'Founded': pd.date_range('1990-01-01', periods=n, freq='W').strftime('%m/%d/%Y'),
    'Segment': np.random.choice(['Enterprise', 'Mid-Market', 'SMB', 'N/A'], n),
    'Region': np.random.choice(['North', 'South', 'East', 'West'], n),
}

df_export = pd.DataFrame(data)

# Save with a report header (simulating a real export)
with open(Path('data') / 'accounts.csv', 'w') as f:
    f.write("Exported from CRM System\n")
    f.write("Report Date: 2024-03-15\n")
    f.write("\n")
    df_export.to_csv(f, index=False)

print("Sample file created: data/accounts.csv")

Your tasks:

1. Load accounts.csv correctly, handling the 3-row header, the Revenue ($) dollar-sign formatting, the N/A segment values, and the Account # column that should stay as a string.

2. Run a complete first-look audit. Document your findings: how many rows? How many columns? Which columns have missing data, and how much?

3. Identify all the dtype problems — which columns loaded as the wrong type, and why?

4. Clean the Revenue ($) column so it's a proper float64 and verify with df.dtypes.

5. Convert Founded to datetime and calculate the age of each account in years.

6. Find all rows where Company Name is null. Are they in any particular segment or region? Use boolean filters to investigate.

7. Build a frequency table for the Segment column including null values. What percentage of accounts have N/A as their segment?

8. Find the top 5 companies by Revenue and display their Region and Employees alongside.

---

Common Mistakes & Troubleshooting

"FileNotFoundError: [Errno 2] No such file or directory"

This is almost always a path problem. Check:

import os
print(os.getcwd())          # What directory is Python looking in?
print(os.listdir('.'))      # What files are actually in that directory?

In Jupyter, os.getcwd() is the directory where the notebook file lives. If your data is in a data/ subfolder, you need pd.read_csv('data/yourfile.csv'), not just pd.read_csv('yourfile.csv').

"UnicodeDecodeError" When Reading a CSV

This means the file was saved with a different character encoding than UTF-8 (which pandas assumes by default). Common culprits are Windows-1252 or Latin-1 for files exported from Windows applications.

# Try these in order
df = pd.read_csv('file.csv', encoding='latin-1')
df = pd.read_csv('file.csv', encoding='windows-1252')
df = pd.read_csv('file.csv', encoding='utf-8-sig')  # For files with BOM

"Missing optional dependency 'openpyxl'"

You need to install it separately:

pip install openpyxl

Then restart your kernel/interpreter. pandas caches imports at startup.

DataFrame Has One Column Containing the Whole Row

Your delimiter is wrong. Check what character actually separates the values in the file, then specify it:

# Quick way to check: open the raw file
with open('mystery.csv', 'r') as f:
    print(f.readline())  # See exactly what the first line looks like

Column Names Have Leading/Trailing Spaces

Extremely common with Excel exports. df['Revenue'] fails with KeyError because the actual column name is ' Revenue '.

# Fix it immediately after loading
df.columns = df.columns.str.strip()

Date Column Loaded as Object Despite `parse_dates=True`

pandas couldn't parse the format automatically. Look at the actual values and specify the format:

print(df['Order Date'].head())          # What does it actually look like?
# If you see '15-Jan-2024':
df['Order Date'] = pd.to_datetime(df['Order Date'], format='%d-%b-%Y')

DataFrame Shows "Unnamed: 0" as First Column

A previous df.to_csv() call saved the index. Load with:

df = pd.read_csv('file.csv', index_col=0)

Or when saving, use df.to_csv('file.csv', index=False).

---

Summary & Next Steps

You now have the foundation for every pandas workflow. Let's consolidate what you've built:

Loading CSV files:

• pd.read_csv() with skiprows, usecols, dtype, parse_dates, and na_values gives you precise control over how data enters Python
• The separator, encoding, and thousands separator are the most common sources of loading bugs
• Use pathlib.Path for portable, OS-agnostic file paths

Loading Excel files:

• Install openpyxl alongside pandas for .xlsx support
• Use sheet_name=None to get all sheets as a dict, then pd.concat() to combine them
• Watch for merged cells, report headers, and totals rows that Excel makes beautiful and pandas makes painful
• Clean column names with df.columns.str.strip() immediately after loading

First-look exploration:

• df.info() for shape, dtypes, and null counts in one shot
• df.describe() for statistical summary of numerics
• df.tail() alongside df.head() to catch totals rows
• df.sample() for an unbiased view of the data
• value_counts(dropna=False) for categorical distributions including nulls
• df.duplicated() for identifying key integrity problems
• Boolean filters to investigate suspicious patterns

The first-look workflow isn't just a checklist — it's a discipline. The analysts who skip it are the ones who spend three hours debugging an aggregation that's wrong because of 30 corrupted rows they never noticed.

From here, the natural next steps in your data analysis journey are cleaning and transforming that data once you understand what's wrong with it, and then aggregating and summarizing to answer the actual business questions. But you can't do either of those well without first mastering exactly what you've learned here: getting data in cleanly, and understanding what you have before you touch it.