Your First Data Analysis Project: CSV to Insights Guide

You Don’t Need a PhD to Extract Meaning from Data

Most beginner data analysis tutorials show you pristine datasets and clean workflows. Then you try it on real data and everything breaks. The CSV won’t load because of encoding issues. Half the columns are empty. The dates are in three different formats. Your groupby returns NaN everywhere.

This is the gap between “following a tutorial” and “actually doing analysis.” I’m going to walk through a complete first project using a realistic messy dataset, showing you exactly where things go wrong and how to fix them. By the end, you’ll have a repeatable workflow that works on real data, not just Kaggle competition sets.

We’ll use a dataset I pulled from a fictional e-commerce API: customer orders with timestamps, prices, product categories, and user feedback scores. It has all the problems you’ll encounter in the wild.

Getting Your Data Into Pandas (And Why It Fails)

The first obstacle is usually loading the file. Here’s what beginners try:

import pandas as pd

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

This works maybe 60% of the time. The other 40%, you get:

UnicodeDecodeError: 'utf-8' codec can't decode byte 0x92 in position 1247

Real-world CSVs often use latin-1 or cp1252 encoding, especially if they came from Excel or older systems. The fix:

df = pd.read_csv('orders.csv', encoding='latin-1')
print(f"Loaded {len(df)} rows, {len(df.columns)} columns")
print(df.head())

Now you see your data. But look closely at the first few rows:

   order_id  customer_id order_date    total_price  category    rating
0  10001     C2847       2024-03-15    45.99        Electronics  4
1  10002     C1923       3/16/2024     23.50        Books       NaN
2  10003     C2847       2024-03-15    450.00       Electronics  5
3  10004     C4521       03-17-2024    12.99        Home        3

Notice the inconsistent date formats? That’s going to cause problems later. And that NaN in the rating column — there are probably more scattered throughout.

Let’s check data types:

print(df.dtypes)

order_id         int64
customer_id     object
order_date      object  # Should be datetime!
total_price    float64
category        object
rating         float64
dtype: object

Pandas parsed order_date as a string (object type) because of the mixed formats. This is the single most common issue in first projects.

Cleaning: The Unglamorous 60% of the Work

Data cleaning isn’t optional. The quality of your insights is capped by the quality of your data.

First, fix the dates. The pd.to_datetime() function has a dayfirst parameter, but with mixed formats, you need infer_datetime_format=True (deprecated in pandas 2.0+) or just format='mixed' in newer versions:

# For pandas 2.0+
df['order_date'] = pd.to_datetime(df['order_date'], format='mixed')

# Verify it worked
print(df['order_date'].dtype)  # datetime64[ns]
print(df['order_date'].min(), df['order_date'].max())

If that still fails (it sometimes does), brute force it:

df['order_date'] = pd.to_datetime(df['order_date'], errors='coerce')
print(f"Failed to parse {df['order_date'].isna().sum()} dates")

The errors='coerce' turns unparseable dates into NaT (Not a Time). You’ll need to decide whether to drop those rows or investigate them manually.

Next, handle missing ratings. Check how bad it is:

print(f"Missing ratings: {df['rating'].isna().sum()} / {len(df)} ({df['rating'].isna().mean()*100:.1f}%)")

If it’s under 5%, you can probably just drop them. Above that, you need to decide: fill with median? Mean? Leave as missing and filter later? There’s no universal answer. For ratings, I’d fill with the median because they’re discrete values:

median_rating = df['rating'].median()
df['rating'] = df['rating'].fillna(median_rating)

But be honest about this choice when you present results. You’re making an assumption that impacts conclusions.

Exploratory Analysis: Looking for Signal

Now the actual analysis starts. What questions can this dataset answer?

• How much revenue per month?
• Which product categories sell best?
• Do higher-priced items get better ratings?
• Are there repeat customers? How much do they spend vs. first-timers?

Let’s tackle revenue by month first:

df['year_month'] = df['order_date'].dt.to_period('M')
monthly_revenue = df.groupby('year_month')['total_price'].sum()
print(monthly_revenue)

year_month
2024-01    15483.50
2024-02    18392.75
2024-03    21058.30
2024-04    19234.80
Name: total_price, dtype: float64

Revenue is growing, but is it statistically significant or just noise? A quick linear fit:

import numpy as np

x = np.arange(len(monthly_revenue))
y = monthly_revenue.values
slope, intercept = np.polyfit(x, y, 1)
print(f"Monthly growth: ${slope:.2f}")

If slope is positive and substantial (say, >$500/month), you have a growth trend. If it’s ~$50, it’s probably flat with seasonal noise.

GroupBy: The Most Powerful Tool You’ll Use Daily

The real workhorse of pandas analysis is groupby(). It follows a split-apply-combine pattern: split data into groups, apply a function to each group, combine results.

Which categories drive revenue?

category_stats = df.groupby('category').agg({
    'total_price': ['sum', 'mean', 'count'],
    'rating': 'mean'
}).round(2)
print(category_stats)

              total_price                    rating
                      sum    mean count   mean
category                                        
Books              8392.50  24.15   348  4.12
Electronics       42130.75 128.94   327  4.45
Home              12483.30  38.72   322  3.89
Sports             11238.60  35.12   320  4.21

Electronics dominates revenue (42k vs 8-12k for others) but also has higher average order value ($129 vs $24-39). If you’re optimizing for revenue, focus there.

But notice the rating column: Electronics has the highest satisfaction (4.45) despite being pricier. That’s interesting. Let’s dig deeper.

Correlation vs Causation (And Why Scatter Plots Matter)

Does price correlate with rating? Basic correlation:

corr = df['total_price'].corr(df['rating'])
print(f"Correlation: {corr:.3f}")

Let’s say you get 0.23. That’s a weak positive correlation. But correlation coefficients hide structure. You need a visual check:

import matplotlib.pyplot as plt

plt.figure(figsize=(8, 5))
plt.scatter(df['total_price'], df['rating'], alpha=0.3)
plt.xlabel('Order Price ($)')
plt.ylabel('Rating')
plt.title('Price vs Rating: Weak Correlation, High Variance')
plt.tight_layout()
plt.savefig('price_vs_rating.png', dpi=100)

If the scatter plot shows a wide cloud with no clear trend, the correlation is real but not predictive. A 0.23 correlation means price explains about $r^2 = 0.23^2 = 0.053$ (5.3%) of rating variance. The other 94.7% is noise or confounders (product quality, shipping speed, customer mood).

This is where beginners get burned. They see p < 0.05 and think “significant relationship!” But effect size matters more than p-values. A correlation of 0.23 is statistically significant with 1000+ rows but practically useless for prediction. (If you want a deeper dive into statistical pitfalls, I covered spurious correlations in a previous post on statistical rigor.)

Repeat Customers: Cohort Analysis Lite

Are customers coming back? Count orders per customer:

customer_counts = df.groupby('customer_id').size().reset_index(name='num_orders')
print(customer_counts['num_orders'].describe())

count    842.000000
mean       1.372208
std        0.721543
min        1.000000
50%        1.000000
max        7.000000

Median is 1.0, meaning most customers ordered exactly once. But some ordered up to 7 times. Let’s compare spending:

df_with_counts = df.merge(customer_counts, on='customer_id')

spend_by_frequency = df_with_counts.groupby('num_orders')['total_price'].agg(['mean', 'sum', 'count'])
print(spend_by_frequency)

If repeat customers (num_orders > 1) have higher average order values, you have a retention signal. If not, they’re just buying more frequently at the same price point.

Let’s say repeat customers spend 20% more per order on average. That’s actionable: build a loyalty program or email campaign targeting one-time buyers.

From DataFrame to Decisions

You’ve now answered 4 business questions:

1. Revenue trend: Growing ~$1800/month (if that’s what the data showed)
2. Top category: Electronics drives 55% of revenue with highest satisfaction
3. Price-rating relationship: Weak (r=0.23), not a lever to pull
4. Retention issue: 60% of customers never return (if that’s the median)

These are insights. But insights without recommendations are just trivia.

If I were presenting this to stakeholders:

• “Focus marketing spend on Electronics — it’s our highest-revenue, highest-satisfaction category.”
• “Launch a win-back campaign for one-time buyers. We’re losing 60% after first purchase.”
• “Don’t obsess over pricing strategy to boost ratings — the correlation is too weak to matter.”

That’s the difference between a data analyst and someone who just runs pandas functions.

The Workflow I Actually Use

Here’s my standard template for any new dataset:

import pandas as pd
import numpy as np
import matplotlib.pyplot as plt

# 1. Load with encoding fallback
try:
    df = pd.read_csv('data.csv')
except UnicodeDecodeError:
    df = pd.read_csv('data.csv', encoding='latin-1')

print(f"Shape: {df.shape}")
print(df.head())
print(df.dtypes)
print(df.describe())

# 2. Fix dates
date_cols = [col for col in df.columns if 'date' in col.lower()]
for col in date_cols:
    df[col] = pd.to_datetime(df[col], errors='coerce')

# 3. Check missing values
missing = df.isna().sum()
if missing.any():
    print("Missing values:")
    print(missing[missing > 0])

# 4. Univariate analysis
for col in df.select_dtypes(include='number').columns:
    print(f"\n{col}:")
    print(df[col].describe())

# 5. Bivariate relationships (depends on question)
# groupby, correlations, plots

This covers 80% of first-pass exploration. The other 20% is domain-specific.

Common Pitfalls That Will Bite You

1. Forgetting to reset index after groupby

grouped = df.groupby('category')['price'].mean()
print(grouped['Electronics'])  # Works
print(grouped[0])  # KeyError — index is now category names, not integers

Always use .reset_index() if you need integer indexing later:

grouped = df.groupby('category')['price'].mean().reset_index()

2. Chained indexing warnings

Pandas yells at you for this:

df[df['price'] > 100]['category'] = 'Expensive'  # SettingWithCopyWarning

Use .loc[] instead:

df.loc[df['price'] > 100, 'category'] = 'Expensive'

3. Assuming sorted data

Groupby doesn’t guarantee order. If you need chronological analysis, sort first:

df = df.sort_values('order_date')

4. Ignoring memory

Pandas loads the entire CSV into RAM. A 2GB CSV file needs ~10GB RAM to process (roughly 5x overhead for operations). If you hit MemoryError, either:

• Use chunking: pd.read_csv(..., chunksize=10000)
• Switch to Polars or Dask (but that’s overkill for a first project)
• Filter columns early: pd.read_csv(..., usecols=['col1', 'col2'])

FAQ

Q: Should I learn SQL before Pandas?

No, but SQL helps. Pandas is easier to learn first because you see results immediately (print the DataFrame). SQL requires understanding databases, joins, and query optimization. That said, if you’re working with databases in production, learn SQL — pandas is great for exploration but slow on large data. I compared the two in this benchmark post.

Q: How do I know if my analysis is “good enough”?

Can you answer a specific question with confidence? If yes, it’s good enough. Don’t overthink it. The goal isn’t a perfect statistical model — it’s actionable insight. If your boss/client/team can make a decision based on your findings, you succeeded.

Q: What’s the fastest way to get better at this?

Repetition on varied datasets. Download 10 different CSVs from Kaggle, government data portals, or APIs. Do the same cleaning workflow on each. You’ll internalize the patterns. Reading tutorials helps, but you don’t really learn until you hit an error you’ve never seen before and Google your way out. (And keeping a snack stash nearby helps — Dark Chocolate Espresso Beans kept me sane during late-night debugging sessions.)

When Pandas Isn’t Enough

For this project, pandas is overkill. If you have under 100k rows and basic aggregations, Excel pivot tables would work fine. But pandas scales better: once you write the script, you can rerun it on updated data instantly. Excel requires manual clicking every time.

Pandas starts struggling around 10M+ rows or when you need parallel processing. At that point, look into Polars (faster, better memory management) or PySpark (distributed computing). But for your first project? Stick with pandas. The syntax you learn here transfers directly to those tools.

What I’d Do Differently Next Time

If I were doing this analysis again, I’d add:

• Time series decomposition: Use statsmodels to split revenue into trend + seasonal + residual components. That 5% month-over-month growth might just be Q1 seasonality.
• Customer lifetime value (CLV): Calculate expected revenue per customer: $CLV = \text{avg order value} \times \text{avg orders per year} \times \text{avg lifespan}$. Much more useful than raw revenue totals.
• Anomaly detection: Flag unusually large orders or suspiciously perfect 5.0 ratings — could be fraud or data entry errors.

But I’m not doing those here because this is a first project. You can’t optimize what you haven’t built yet.

The Real Skill: Knowing What to Ignore

Beginner analysts try to explore everything. They make 47 plots and test every possible correlation. This is exhausting and produces no clarity.

The skill is asking: What decision does this data need to inform? If a metric doesn’t change behavior, don’t calculate it. If a plot doesn’t reveal new structure, don’t make it.

For this e-commerce dataset, I ignored:

• Correlation between customer_id and rating (meaningless)
• Distribution of order_id (sequential IDs, no insight)
• Exact revenue on March 17th vs March 18th (noise, not signal)

Data analysis is 20% pandas syntax and 80% judgment about what matters. You build that judgment by doing it badly a few times and learning what questions actually lead somewhere.

Start with a simple question. Load the data. Clean it until it cooperates. Answer the question. Repeat.

That’s the entire game.