Data Tools: Spreadsheets, SQL and Python

Introduction

Data analysis is not performed by one tool alone. Spreadsheets are effective for rapid exploration, calculation and pivot analysis; SQL is designed to retrieve and aggregate structured data in databases; and Python provides programmable, reproducible workflows for importing, cleaning, reshaping, analysing and exporting data. Professional analysts select tools according to the data structure, scale, repeatability and decision context.

A useful analytical workflow is:

1. Define the question and required output.
2. Identify the data sources and data types.
3. Import or connect to the data.
4. Inspect structure, quality and completeness.
5. Clean and transform the data.
6. Join or reshape datasets when required.
7. Calculate summaries or analytical measures.
8. Validate results.
9. Communicate findings and preserve a reproducible record.

The same conceptual operation may appear in all three environments. For example, filtering rows can be performed with spreadsheet filters, a SQL WHERE clause, or a pandas Boolean condition. Understanding the underlying operation is more important than memorising a particular syntax.

Learning Objectives

By the end of this chapter, students should be able to:

• use spreadsheets for formulas, structured tables and pivot analysis;
• explain the difference between rows, columns, records, fields, keys and measures;
• write SQL queries using selection, filtering, grouping, aggregation and joins;
• use NumPy arrays and pandas DataFrames for reproducible analysis;
• import, inspect, clean, join, reshape and export datasets;
• validate analytical results and identify common sources of error;
• choose an appropriate tool for a practical data-analysis task.

1. Data Structures and Tool Selection

A dataset is commonly represented as a rectangular relation: each row represents an observation and each column represents an attribute. In a sales table, one row may represent one order, while columns may contain order ID, date, product, region, quantity and revenue.

Important terms include:

• Record or observation: one row describing an entity or event.
• Field or variable: one column containing a particular attribute.
• Data type: the interpretation of a value, such as integer, decimal, text, date or Boolean.
• Primary key: a field, or combination of fields, that uniquely identifies a record.
• Foreign key: a field that refers to a key in another table.
• Measure: a numerical quantity that can often be aggregated, such as revenue.
• Dimension: a descriptive category used to group measures, such as region or month.
• Missing value: an unavailable, unknown or inapplicable value.

Tool choice should reflect the task.

No tool automatically guarantees correctness. A spreadsheet can contain hidden formula errors, SQL can produce duplicated rows after an incorrect join, and Python code can repeatedly execute an incorrect assumption. Validation is therefore part of analysis, not an optional final step.

2. Spreadsheet Analysis

2.1 Organising a worksheet

A reliable analytical worksheet separates raw data, calculations and presentation. Raw data should normally be stored in a rectangular table with one header row, no merged cells, consistent data types and one observation per row. Blank rows, decorative subtotals and manually typed totals make filtering and importing more difficult.

Use meaningful column names such as OrderDate, Region, Quantity and UnitPrice. Dates should be stored as dates rather than text, and numerical values should not contain currency symbols embedded in the value. A separate data dictionary can document definitions, units and permitted values.

2.2 Formula-based calculations

Suppose quantity is in cell D2 and unit price is in E2. Revenue can be calculated as:

=D2*E2

If discount is represented as a decimal in F2, net revenue may be calculated as:

=D2E2(1-F2)

The general arithmetic relationship is:

where R is net revenue in currency units, Q is quantity in units, P is unit price in currency units per item, and d is the discount proportion. The formula assumes that discount is applied to the full gross value and that d lies between 0 and 1. If tax, shipping or multiple discount rules apply, the calculation must be extended and documented.

Common spreadsheet functions include SUM, AVERAGE, COUNT, MIN, MAX, IF, SUMIF, SUMIFS, COUNTIF and COUNTIFS. For a set of n numerical observations x₁, x₂, …, xₙ, the arithmetic mean is:

Here x̄ is the sample mean, xᵢ is observation i, and n is the number of included observations. The mean is appropriate only when the included values are comparable and missing or invalid entries have been handled deliberately.

Absolute and relative references are important when copying formulas. In a formula such as =D2*$H$1, D2 changes when copied down, while $H$1 remains fixed. This is useful when a common tax rate or exchange rate is stored in one cell.

2.3 Pivot analysis

A pivot table summarises data by dimensions. For example, Region may be placed in Rows, Product in Columns, and Revenue in Values. The resulting table can show total revenue by region and product without manually writing many formulas.

A pivot operation usually involves:

1. selecting the complete source table;
2. assigning categorical fields to rows or columns;
3. assigning a measure to values;
4. selecting an aggregation such as sum, count or average;
5. applying filters and checking the number format;
6. interpreting the result in relation to the original records.

A frequent error is using “average of averages.” If regional averages are averaged without considering regional sample sizes, the result may not equal the overall mean. The correctly weighted overall mean is:

where x̄ⱼ is the mean for group j and nⱼ is the number of observations in that group. This formula assumes every observation should receive equal weight.

2.4 Spreadsheet controls and limitations

Professional spreadsheet practice includes data validation, protected formula cells, consistent units, named ranges, visible assumptions and reconciliation checks. A useful check is to compare a pivot-table total with the total calculated directly from the source data.

Spreadsheets become risky when many users edit them, formulas are copied inconsistently, data exceeds practical limits, or transformations cannot be audited. They are excellent for exploration and communication, but large or recurring processes should generally be moved to SQL or Python.

3. SQL for Querying and Aggregation

SQL, or Structured Query Language, is a declarative language for working with relational databases. A query states what data is required rather than specifying every procedural step used by the database engine.

3.1 Selecting and filtering

A basic query is:

SELECT OrderID, Region, Revenue
FROM Sales
WHERE Region = 'North';

SELECT identifies columns, FROM identifies the table, and WHERE restricts rows. Conditions can use =, <>, <, >, <=, >=, AND, OR, IN, BETWEEN and LIKE. Missing values require IS NULL or IS NOT NULL; the expression = NULL does not correctly test for missingness in SQL.

For example:

SELECT *
FROM Sales
WHERE OrderDate >= '2025-01-01'
  AND OrderDate <  '2025-02-01'
  AND Revenue IS NOT NULL;

Using an inclusive lower bound and exclusive upper bound is often safer for date-time fields because it includes every time on the first date and excludes the first instant of the next period.

3.2 Aggregation and grouping

Aggregate functions include SUM, AVG, COUNT, MIN and MAX. A grouped query is:

SELECT Region,
       SUM(Revenue) AS TotalRevenue,
       COUNT(*) AS OrderCount,
       AVG(Revenue) AS AverageOrderValue
FROM Sales
GROUP BY Region;

WHERE filters individual rows before grouping. HAVING filters groups after aggregation:

SELECT Region, SUM(Revenue) AS TotalRevenue
FROM Sales
GROUP BY Region
HAVING SUM(Revenue) > 100000;

COUNT(*) counts rows, whereas COUNT(Revenue) generally counts only non-null Revenue values. This distinction must be understood before interpreting results.

3.3 Joins

A join combines rows from tables using related keys. Suppose Orders contains CustomerID and Customers contains one row per customer:

SELECT o.OrderID, c.CustomerName, o.Revenue
FROM Orders AS o
INNER JOIN Customers AS c
  ON o.CustomerID = c.CustomerID;

An INNER JOIN retains only matching rows. A LEFT JOIN retains every row from the left table and adds matching values from the right table:

SELECT c.CustomerID, c.CustomerName, o.OrderID
FROM Customers AS c
LEFT JOIN Orders AS o
  ON c.CustomerID = o.CustomerID;

The cardinality of a join matters. If one customer has five orders, joining customers to orders produces five rows for that customer. Summing a customer-level measure after this join may duplicate it. Before joining, determine whether the relationship is one-to-one, one-to-many or many-to-many.

3.4 Query evaluation and validation

Although SQL is written beginning with SELECT, a simplified conceptual order is FROM and JOIN, then WHERE, then GROUP BY, then HAVING, then SELECT, and finally ORDER BY. Database engines may optimise execution, but the logical order helps explain why aliases or aggregates cannot always be used in every clause.

Validation techniques include checking row counts before and after joins, testing unique keys, comparing totals with an independent calculation, and inspecting unmatched records. SQL views can provide reusable, controlled definitions of business metrics.

4. NumPy and pandas in Python

Python supports reproducible analysis because data-import, transformation, calculation and export steps can be stored in scripts or notebooks. Reproducibility requires more than saving code: analysts should record data sources, assumptions, package versions, filtering rules and output locations.

4.1 NumPy arrays

NumPy provides efficient multidimensional arrays and numerical operations. An array typically contains values of a compatible data type and supports vectorised calculation.

import numpy as np

quantity = np.array([2, 5, 3])
price = np.array([10.0, 8.0, 12.0])
revenue = quantity * price
print(revenue)

The output is [20. 40. 36.]. Vectorisation applies the operation element by element without writing a conventional loop. NumPy functions such as np.sum, np.mean, np.min and np.max operate on arrays. For a two-dimensional array, the axis argument controls whether calculations are performed down rows or across columns; its interpretation should always be checked rather than assumed.

4.2 Importing and inspecting pandas data

A pandas DataFrame is a labelled two-dimensional data structure. A typical workflow is:

import pandas as pd

sales = pd.read_csv("sales.csv")
print(sales.head())
print(sales.shape)
print(sales.dtypes)
print(sales.isna().sum())
print(sales.describe(include="all"))

head() displays initial records, shape returns rows and columns, dtypes reports inferred types, isna().sum() counts missing values by column, and describe() provides descriptive summaries. Inspection should occur before transformation.

Dates should be converted explicitly when inference is uncertain:

sales["OrderDate"] = pd.to_datetime(sales["OrderDate"], errors="coerce")

errors="coerce" converts unrecognised dates to missing values, which must then be investigated. Numeric conversion can similarly be performed with pd.to_numeric.

4.3 Filtering, creating variables and aggregation

recent = sales.loc[
    (sales["OrderDate"] >= "2025-01-01") &
    (sales["OrderDate"] < "2025-02-01")
].copy()

recent["Revenue"] = recent["Quantity"] * recent["UnitPrice"]
summary = (recent.groupby("Region", as_index=False)
                 .agg(TotalRevenue=("Revenue", "sum"),
                      Orders=("OrderID", "nunique")))

Parentheses are required around individual conditions when combining pandas Boolean expressions. The operator & is used for element-wise AND; Python’s scalar and is not the appropriate operator for Series conditions.

The use of nunique counts distinct orders. This may differ from row count if one order occupies multiple product rows. The correct measure depends on the business question.

4.4 Missing values and duplicates

Missing values may be removed, replaced, retained as a category or investigated as a separate signal. For example:

sales["Quantity"] = sales["Quantity"].fillna(0)
sales = sales.drop_duplicates()

Replacing missing quantity with zero is valid only if missing means “no quantity,” not “quantity was not recorded.” A careless imputation changes the data-generating assumption. Duplicate removal should use a documented definition of duplication, such as repeated OrderID and ProductID, rather than automatically deleting every identical row.

4.5 Joining and reshaping

A pandas merge resembles a relational join:

customer_sales = sales.merge(
    customers,
    on="CustomerID",
    how="left",
    validate="many_to_one"
)

validate="many_to_one" checks the intended relationship: many sales rows may match one customer row. This is an important safeguard against accidental row multiplication.

Long and wide data formats serve different purposes. Long format stores one observation per row and is usually easier to filter and group. Wide format places categories into separate columns and may be convenient for reporting.

wide = (sales.pivot_table(
    index="Region",
    columns="Product",
    values="Revenue",
    aggfunc="sum",
    fill_value=0
).reset_index())

The reverse operation can be performed with melt:

long = wide.melt(
    id_vars="Region",
    var_name="Product",
    value_name="Revenue"
)

Reshaping does not create new evidence; it changes the representation of existing values. Analysts should check totals before and after reshaping.

4.6 Exporting results

summary.to_csv("regional_summary.csv", index=False)
summary.to_excel("regional_summary.xlsx", index=False)

Exported files should have meaningful names, documented definitions and an appropriate level of precision. Avoid overwriting raw data. A useful pipeline separates raw, processed and output directories.

5. A Unified Workflow Across Tools

The same analytical task can be expressed in three environments:

Tool translation is useful because it clarifies whether a result comes from selection, transformation, aggregation or presentation. It also allows an analyst to prototype in a spreadsheet and later implement a repeatable SQL or Python pipeline.

6. Illustrative Case: Regional Retail Performance

Background

A fictional retailer, Northstar Home, stores transaction-level sales in sales.csv and customer information in customers.csv. Management wants a January revenue summary by region and wishes to identify whether all customers are represented in the transaction data.

Problem

The sales file contains one row per product line, while management informally refers to each row as an order. This could overstate order counts. Some dates are text, several prices are missing, and the customer table may contain duplicate customer identifiers.

Analysis and approach

The analyst first inspects row counts, data types, missingness and key uniqueness in Python. Dates are parsed explicitly. Revenue is calculated as Quantity multiplied by UnitPrice, but rows with missing price are reported rather than silently assigned zero. Customer IDs are checked for uniqueness before the merge.

A left merge from customers to sales is used when the question is whether every customer has transactions. A separate left merge from sales to customers is used to identify sales records without a matching customer. These are different questions and require different left-hand tables.

The analyst then produces a regional summary using groupby, counting distinct OrderID rather than transaction rows. The same summary is independently checked with a SQL query using COUNT(DISTINCT OrderID) and a spreadsheet pivot table. Totals are reconciled to the source data, and the output is exported as a dated CSV file.

Outcome

The comparison reveals that one region appeared to have unusually high order volume because each order contained several product lines. Distinct-order counting corrects the interpretation. A small number of unmatched customer IDs are reported to the data steward rather than removed. The final report contains revenue, distinct orders, missing-price count and unmatched-customer count.

Learning points

• The unit of analysis must be defined before aggregation.
• Join direction reflects the business question.
• Key validation prevents silent duplication or loss.
• Independent implementation in another tool can expose errors.
• Missing values should be interpreted, not merely replaced.

7. Quality, Ethics and Governance

Data tools can reveal personal, financial or commercially sensitive information. Analysts should apply data minimisation, access control and appropriate retention practices. Customer names, addresses and identifiers should not be exported unnecessarily. Depending on jurisdiction and context, privacy and data-protection laws may require a lawful purpose, transparency, security and rights for data subjects.

Reproducibility and privacy must be balanced. A script should document transformations without placing confidential raw data in a public repository. Use parameterised SQL rather than constructing queries by concatenating untrusted user input; this reduces the risk of SQL injection. Maintain audit trails for important reports, including source version, processing date, assumptions and reviewer.

Common technical threats include:

• incorrect data types, especially dates stored as text;
• duplicated keys and many-to-many joins;
• accidental inclusion or exclusion of boundary dates;
• inconsistent units, currencies or tax treatment;
• treating missing as zero without justification;
• hidden spreadsheet formulas or stale pivot caches;
• rounding intermediate values rather than final reported values;
• exporting a summary without its definition or denominator.

AI-assisted coding can accelerate analysis, but generated code must be inspected, tested and validated. An AI system may invent column names, misunderstand missingness or produce a syntactically valid but conceptually incorrect query. Human responsibility for data governance and conclusions remains essential.

8. Best Practices and Contemporary Developments

Modern analytical teams increasingly use notebooks, version control, automated tests, data warehouses and scheduled pipelines. SQL is often executed in cloud data platforms, while Python is used for orchestration, modelling and quality checks. Spreadsheet outputs remain important because decision-makers value accessible summaries.

A practical minimum standard is:

1. Preserve an immutable copy of raw data.
2. Record the data source, extraction date and definitions.
3. Inspect shape, types, missingness and uniqueness.
4. Use explicit transformations rather than manual edits.
5. Validate row counts, totals and join cardinality.
6. Separate exploratory code from production outputs.
7. Keep sensitive information protected.
8. Export documented results without overwriting raw files.
9. Make assumptions visible to users of the analysis.

The strongest workflow is not necessarily the most sophisticated. It is the one that produces a correct, understandable, maintainable and appropriately governed result.

Chapter Summary

Spreadsheets provide accessible calculation, formula and pivot-table capabilities, but require disciplined structure and control. SQL supports precise retrieval, filtering, grouping, aggregation and relational joins; understanding nulls and join cardinality is essential. NumPy supplies efficient numerical arrays, while pandas supports DataFrame-based importing, inspection, cleaning, joining, reshaping, aggregation and export. Across all tools, the analyst must define the unit of analysis, validate data types and keys, distinguish missing values from zero, reconcile results and document assumptions. Tool selection should reflect scale, repeatability, collaboration and governance requirements. Privacy, security and responsible use remain part of professional data analysis.

Key Terms

DataFrame: A labelled two-dimensional data structure used by pandas.

Data type: The representation and interpretation of a value, such as integer, date or text.

Dimension: A descriptive category used to group or segment measures.

Foreign key: A field that references a key in another table.

Group-by aggregation: Summarising records according to one or more grouping fields.

Join: Combining records from two datasets using related fields.

Long format: A structure in which observations are stored in rows and variables in columns.

Measure: A numerical quantity that can be calculated or aggregated.

Missing value: A value that is unavailable, unknown or inapplicable.

NumPy: A Python library for numerical arrays and vectorised computation.

Pandas: A Python library for tabular data manipulation and analysis.

Pivot table: An interactive summary that groups and aggregates tabular data.

Primary key: A field or field combination that uniquely identifies a record.

Query: An instruction requesting or transforming data.

Record: A row representing an observation or event.

Relational database: A database that organises data into related tables.

SQL: Structured Query Language for working with relational data.

Vectorisation: Applying an operation to an array or column without an explicit element-by-element loop.

Wide format: A structure in which categories or measurements may appear as separate columns.

Review Questions

Short Answer Questions

1. What is the difference between a primary key and a foreign key?
2. Why should missing values not automatically be replaced with zero?
3. What is the purpose of a pivot table?
4. Distinguish between WHERE and HAVING in SQL.
5. Why is join cardinality important in data analysis?

Long Answer Questions

1. Explain a complete workflow for importing, inspecting, cleaning and exporting a dataset using pandas.
2. Compare spreadsheets, SQL and Python with respect to accessibility, scalability and reproducibility.
3. Explain how an incorrect one-to-many join can distort an aggregate result, using a suitable example.
4. Describe best practices for designing a spreadsheet used for professional analysis.
5. Discuss the ethical, privacy and governance responsibilities associated with customer data analysis.

Analytical / Application Questions

1. A sales table contains five rows for one order because the order has five product lines. Which measures would you use to calculate revenue and distinct order volume, and why?
2. A monthly report includes dates from 1 January through 31 January, but some fields contain timestamps. Write a SQL filtering strategy that includes all January records without excluding records on 31 January.
3. A pandas merge unexpectedly increases a dataset from 20,000 to 75,000 rows. Identify likely causes, describe checks to perform, and propose a corrective approach.

Multiple Choice Questions with Answers

Question 1

Which spreadsheet feature is most appropriate for summarising revenue by region and product?

A. Pivot table

B. Cell comment

C. Spell checker

D. Page-break preview

Correct Answer: A. Pivot table

Question 2

Which SQL clause filters individual rows before grouping?

A. ORDER BY

B. WHERE

C. HAVING

D. SELECT

Correct Answer: B. WHERE

Question 3

What does COUNT(DISTINCT OrderID) measure in a transaction table?

A. The number of product lines

B. The number of non-null prices

C. The number of unique orders

D. The total revenue

Correct Answer: C. The number of unique orders

Question 4

Which pandas method is primarily used to combine tables using a related key?

A. merge

B. describe

C. melt

D. fillna

Correct Answer: A. merge

Question 5

Which expression correctly calculates revenue when quantity is in column Quantity and price is in UnitPrice?

A. Quantity + UnitPrice

B. Quantity / UnitPrice

C. Quantity * UnitPrice

D. Quantity - UnitPrice

Correct Answer: C. Quantity * UnitPrice

Question 6

What is the main risk of replacing every missing quantity with zero without investigation?

A. It changes the column name

B. It may impose an unjustified interpretation on missing data

C. It prevents all aggregation

D. It converts dates into text

Correct Answer: B. It may impose an unjustified interpretation on missing data

Question 7

Which join retains every row from the left table, whether or not a match exists in the right table?

A. INNER JOIN

B. CROSS JOIN

C. LEFT JOIN

D. SELF JOIN

Correct Answer: C. LEFT JOIN

Question 8

Which pandas operation changes data from wide representation to long representation?

A. melt

B. head

C. drop_duplicates

D. astype

Correct Answer: A. melt

Question 9

Why should an analyst inspect key uniqueness before joining tables?

A. To select a chart colour

B. To prevent or detect unintended row multiplication

C. To reduce the number of column labels

D. To guarantee that values are normally distributed

Correct Answer: B. To prevent or detect unintended row multiplication

Question 10

In a pandas Boolean filter, which operator is normally used for element-wise AND between conditions?

A. and

B. &&

C. &

D. AND()

Correct Answer: C. &