Data analysis basics: 50 cards on cleaning, pandas, SQL and A/B tests

All 50 cards

1. 1
   Mean or median for typical household income?
   The median: income is right-skewed, and a few very high values pull the mean far above what a typical household earns

   In right-skewed data the mean is greater than the median. The same goes for house prices and response times.
2. 2
   Why report p95 or p99 latency instead of the average?
   Averages hide the slow tail. A p95 of 800 ms means 95% of requests were faster than 800 ms and 1 in 20 users waited longer
3. 3
   Five-number summary
   Minimum, first quartile, median, third quartile and maximum: exactly what a box plot draws

   Box plots usually draw points beyond 1.5 × IQR from the box as individual outliers.
4. 4
   Get a quick statistical summary of a DataFrame
   python

   df.describe()   # count, mean, std, min, quartiles, max
   df.info()       # dtypes, non-null counts, memory

   describe() covers numeric columns by default; describe(include="all") adds the others.
5. 5
   An outlier shows up in your data. Remove it or keep it?
   Investigate first. Fix or drop it if it is an error (a typo, a test record, a unit mix-up); keep it if it is real, because extreme values are often the most important data

   Report results with and without it if it changes the conclusion.
6. 6
   First checks on a new dataset in pandas
   python

   df = pd.read_csv("sales.csv", parse_dates=["order_date"])
   df.shape
   df.head()
   df.dtypes
   df.isna().sum()
   df.duplicated().sum()

   Look at size, types, missing values, duplicates and value ranges before any analysis.
7. 7
   Count missing values per column
   python

   df.isna().sum()

   df.isna().mean() gives the share of missing values instead.
8. 8
   Options for handling missing values
   Drop the rows or columns, fill them (median, mode, a constant, or forward-fill for time series), or keep them and add an “is missing” flag. The right choice depends on why they are missing

   If missingness is related to the value itself, such as high earners skipping an income question, dropping or mean-filling biases the results.
9. 9
   Remove duplicate orders, keeping the latest version
   python

   df = df.drop_duplicates(subset=["order_id"], keep="last")

   Without subset, only rows that are identical in every column count as duplicates.
10. 10
    Convert a text column with stray values to numbers
    python

    df["price"] = pd.to_numeric(df["price"], errors="coerce")

    errors="coerce" turns values it can’t parse, such as "N/A" or "12,50", into NaN, so count the new NaNs afterwards.
11. 11
    Parse a date column and extract the month
    python

    df["created_at"] = pd.to_datetime(df["created_at"])
    df["month"] = df["created_at"].dt.month

    The .dt accessor also offers year, day_name(), to_period("M") and more.
12. 12
    Clean inconsistent category labels like "USA", " usa" and "U.S."
    python

    df["country"] = (
        df["country"].str.strip().str.upper()
        .replace({"U.S.": "USA", "UNITED STATES": "USA"})
    )

    Check the result with value_counts().
13. 13
    Filter rows on two conditions in pandas
    python

    df[(df["country"] == "DE") & (df["amount"] > 100)]

    Use &, | and ~ with parentheses around each condition; Python’s and and or raise an error on Series.
14. 14
    loc vs iloc
    loc selects by labels and boolean masks; iloc selects by integer position

    Label slices with loc include the end label; iloc slices exclude the end, like Python lists.
15. 15
    Add a revenue column from price and quantity
    python

    df["revenue"] = df["price"] * df["qty"]

    Vectorized column arithmetic is much faster than looping over rows or calling apply row by row.
16. 16
    Top 10 rows by revenue
    python

    df.sort_values("revenue", ascending=False).head(10)

    df.nlargest(10, "revenue") does the same.
17. 17
    Count how often each value occurs in a column
    python

    df["status"].value_counts()

    Add normalize=True for proportions and dropna=False to count missing values too.
18. 18
    Why write df.loc[mask, "col"] = value instead of df[mask]["col"] = value?
    The chained version may assign to a temporary copy and leave df unchanged; a single loc call always updates the original

    With Copy-on-Write, the default from pandas 3.0, chained assignment never updates the original.
19. 19
    Total revenue per week from daily orders
    python

    df.set_index("order_date").resample("W")["amount"].sum()

    Use "MS" for calendar months starting on the 1st or "ME" for month end; the old "M" alias is deprecated in recent pandas.
20. 20
    Total revenue per region in pandas
    python

    df.groupby("region")["revenue"].sum()

    Group-by follows split-apply-combine: split rows into groups, apply a function to each, combine the results.
21. 21
    Several named aggregations per group in pandas
    python

    df.groupby("region").agg(
        orders=("order_id", "nunique"),
        revenue=("amount", "sum"),
        avg_order=("amount", "mean"),
    )

22. 22
    Join customer details onto orders in pandas
    python

    orders.merge(customers, on="customer_id", how="left")

    Use left_on and right_on when the key columns have different names.
23. 23
    Inner vs left vs outer join
    Inner keeps only rows whose key appears in both tables; left keeps every row of the left table, with missing values where there is no match; outer keeps every row from both
24. 24
    After a left join you have more rows than before. Why?
    The key isn’t unique in the right table, so each left row is repeated once per match, which also inflates any sums

    Catch it early with merge(..., validate="many_to_one"), which raises an error if the right-hand keys repeat.
25. 25
    Find orders whose customer is missing from the customers table
    python

    m = orders.merge(customers, on="customer_id", how="left", indicator=True)
    m[m["_merge"] == "left_only"]

    In SQL: LEFT JOIN and keep rows where the right table’s key IS NULL.
26. 26
    What does a pivot table do?
    Summarizes data by turning the values of one column into rows and another into columns, with an aggregate such as a sum at each intersection

    Example: revenue by region (rows) and month (columns).
27. 27
    Revenue by region and month as a pivot table in pandas
    python

    df.pivot_table(index="region", columns="month",
                   values="revenue", aggfunc="sum", fill_value=0)

    The default aggfunc is the mean, so set it explicitly. margins=True adds totals.
28. 28
    pivot vs pivot_table in pandas
    pivot only reshapes and fails if an index/column pair appears more than once; pivot_table aggregates the duplicates
29. 29
    Reshape monthly columns (wide) into rows (long)
    python

    df.melt(id_vars=["region"], var_name="month", value_name="revenue")

    Long, or tidy, data has one row per observation and one column per variable, which most plotting and group-by tools expect.
30. 30
    Orders and revenue per region in SQL, biggest first
    sql

    SELECT region,
           COUNT(*)    AS orders,
           SUM(amount) AS revenue
    FROM orders
    GROUP BY region
    ORDER BY revenue DESC;

31. 31
    Pivot statuses into columns in SQL
    sql

    SELECT region,
           SUM(CASE WHEN status = 'paid'     THEN amount ELSE 0 END) AS paid,
           SUM(CASE WHEN status = 'refunded' THEN amount ELSE 0 END) AS refunded
    FROM orders
    GROUP BY region;

    PostgreSQL also supports SUM(amount) FILTER (WHERE status = 'paid').
32. 32
    Monthly revenue in PostgreSQL
    sql

    SELECT DATE_TRUNC('month', created_at) AS month,
           SUM(amount) AS revenue
    FROM orders
    GROUP BY 1
    ORDER BY 1;

    BigQuery puts the unit second, as in DATE_TRUNC(order_date, MONTH); MySQL has no DATE_TRUNC, so use DATE_FORMAT(created_at, '%Y-%m-01').
33. 33
    Each region’s share of total revenue in SQL
    sql

    SELECT region,
           100.0 * SUM(amount) / SUM(SUM(amount)) OVER () AS pct_of_total
    FROM orders
    GROUP BY region;

    The window function runs after GROUP BY, so it can sum the per-region sums.
34. 34
    Why might SELECT converted / visitors return 0?
    Both columns are integers, and in PostgreSQL, SQL Server and SQLite integer division truncates. Multiply by 1.0 or cast to a decimal first

    sql

    SELECT 1.0 * converted / visitors AS conversion_rate FROM funnel;

35. 35
    Daily active users from an events table
    sql

    SELECT event_date, COUNT(DISTINCT user_id) AS dau
    FROM events
    GROUP BY event_date;

    COUNT(*) would count events, not people.
36. 36
    Best chart to compare a value across categories
    A bar chart, sorted by value, with the axis starting at zero

    Use horizontal bars when the category names are long.
37. 37
    Best chart to show a trend over time
    A line chart, with time on the x-axis
38. 38
    Best chart to show the distribution of one numeric variable
    A histogram, or a box plot when comparing distributions across groups

    Try several bin widths: too few bins hide the shape, too many add noise.
39. 39
    Best chart to show the relationship between two numeric variables
    A scatter plot, optionally with a trend line

    With many points, use transparency or a 2D histogram so dense areas stay readable.
40. 40
    Why avoid a pie chart with many slices?
    People compare angles and areas poorly; beyond a few categories, a sorted bar chart shows the differences far more clearly
41. 41
    Why should a bar chart’s value axis start at zero?
    Bar length encodes the value, so a truncated axis makes small differences look huge

    Line charts may use a non-zero baseline, because they show change, not size.
42. 42
    A/B test
    A randomized experiment: users are randomly assigned to a control (A) or a variant (B), and a predefined metric is compared between the groups

    Random assignment makes the groups alike on average in everything except the change, so a difference in the metric can be attributed to it.
43. 43
    What should you decide before starting an A/B test?
    The primary metric, the minimum effect worth detecting, the sample size (from a power analysis), the duration and the rule for stopping
44. 44
    The peeking problem
    Checking results repeatedly and stopping as soon as p < 0.05 greatly inflates the false-positive rate. Fix the sample size in advance or use a sequential testing method
45. 45
    Statistical vs practical significance
    With a huge sample, a tiny effect can be statistically significant yet too small to matter; look at the effect size and its confidence interval, not just the p-value
46. 46
    Sample ratio mismatch (SRM)
    The groups’ sizes differ from the planned split, such as 50.0/50.0 planned but 51.5/48.5 observed with 100,000 users. It signals an assignment or logging bug, so don’t trust the results until it’s explained

    A chi-square test on the group counts detects it.
47. 47
    Ice cream sales and drownings rise together. Does ice cream cause drowning?
    No. Hot weather drives both: it is a confounding variable

    A confounder influences both the supposed cause and the effect, creating an association without causation.
48. 48
    Simpson’s paradox
    A trend that holds within every group reverses or disappears when the groups are combined, because the groups differ in size or mix

    A classic study found treatment A had higher success rates for both small and large kidney stones, yet B looked better overall, because A was used far more on the harder, large stones.
49. 49
    Survivorship bias
    Drawing conclusions only from cases that made it through a filter, such as studying successful startups or customers who stayed, while ignoring those that failed or left

    WWII analysts were advised to armor the areas where returning bombers had no bullet holes: planes hit there never made it back.
50. 50
    Pearson r ≈ 0. Does that mean no relationship?
    No, only no linear relationship. A U-shaped pattern can have r near 0, so always plot the data

    Spearman’s rank correlation captures any monotonic relationship and is less sensitive to outliers.