Data Management with NumPy and pandas

Introduction

Business analytics depends on converting raw records into reliable evidence for decision-making. Sales transactions, customer profiles, inventory movements, website events and financial data are usually stored in tabular or numerical form. Python provides two central libraries for this work: NumPy, which supplies efficient numerical arrays and vectorized operations, and pandas, which supplies labelled one-dimensional and two-dimensional data structures for practical data management.

NumPy and pandas complement one another. NumPy is particularly useful when data are homogeneous and numerical, while pandas is designed for labelled, heterogeneous business data. Together they support data inspection, selection, cleaning, transformation, aggregation and preparation for visualisation or modelling.

Learning Objectives

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

• distinguish NumPy arrays, pandas Series and pandas DataFrames;
• create, inspect and select business data structures;
• apply indexing, filtering, sorting and vectorized computation;
• handle missing values and data types responsibly;
• merge and concatenate datasets;
• reshape data between wide and long forms;
• aggregate data using grouping operations;
• interpret the governance, accuracy and performance implications of data-management choices.

1. NumPy Arrays: Numerical Foundations

A NumPy array, represented by numpy.ndarray, is an indexed collection of values. Unlike a standard Python list, an array is designed for efficient numerical computation. An array normally contains values of one data type, such as integers or floating-point numbers.

import numpy as np

sales = np.array([1250, 980, 1430, 1100])
print(sales.shape)   # (4,)
print(sales.dtype)   # commonly int64

The shape describes the size of each dimension. A one-dimensional array containing four values has shape (4,); a two-dimensional array with three rows and two columns has shape (3, 2). The dtype identifies the element type.

Creating arrays

zeros = np.zeros((2, 3))
ones = np.ones(4)
sequence = np.arange(0, 10, 2)
prices = np.array([19.99, 24.50, 15.75])

np.arange(start, stop, step) excludes the stop value. For decimal sequences, np.linspace(start, stop, number) is often safer because it specifies the number of equally spaced values.

Indexing and slicing

Python uses zero-based indexing. For an array a, a[0] is the first element and a[-1] is the last.

monthly = np.array([120, 135, 128, 150, 164, 172])
first_quarter = monthly[:3]
last_two = monthly[-2:]

For a two-dimensional array, the syntax is array[row, column].

matrix = np.array([[10, 20, 30], [40, 50, 60]])
print(matrix[1, 2])  # 60
print(matrix[:, 0])  # first column

A slice may be a view rather than an independent copy. Changing a view can change the original array. Use .copy() when independent data are required.

Boolean filtering

Boolean indexing selects values that satisfy a condition.

sales = np.array([1250, 980, 1430, 1100])
high_sales = sales[sales >= 1200]

For multiple conditions, use element-wise operators: & for AND, | for OR and ~ for NOT. Each condition should be enclosed in parentheses.

selected = sales[(sales >= 1000) & (sales < 1400)]

The Python keywords and and or should not be used for element-wise NumPy arrays.

Vectorized computation

Vectorization applies an operation to an entire array without writing an explicit Python loop.

revenue = np.array([1000, 1500, 2000])
cost = np.array([600, 900, 1250])
profit = revenue - cost
margin = profit / revenue

For a revenue value R and cost value C, profit is:

where P, R and C must use the same currency and period. If revenue is non-zero, the profit margin is:

The result is a proportion; multiplying by 100 expresses it as a percentage. The formula is not defined when R = 0, so zero-revenue records require an explicit business rule.

NumPy also supports broadcasting. For example, adding a scalar to an array adds it to every element, while compatible arrays can be combined across dimensions.

prices = np.array([10.0, 20.0, 30.0])
new_prices = prices * 1.10

This increases each price by 10 percent. Vectorization is generally clearer and faster than a manually written loop, although readability and memory use must still be considered.

2. pandas Series and DataFrames

A Series is a one-dimensional labelled array. A DataFrame is a two-dimensional labelled table whose columns may have different data types.

import pandas as pd

units = pd.Series([12, 8, 15], index=['East', 'West', 'North'])

orders = pd.DataFrame({
    'order_id': [101, 102, 103],
    'region': ['East', 'West', 'East'],
    'units': [12, 8, 15],
    'unit_price': [25.0, 40.0, 18.0]
})

A DataFrame has an index and named columns. The index identifies rows, but it should not automatically be treated as a unique business key. A separate order_id column is usually preferable for identifying transactions.

Inspecting data

Initial inspection should occur before analysis.

orders.head()
orders.shape
orders.info()
orders.describe(include='all')
orders.dtypes
orders.isna().sum()

These commands reveal dimensions, data types, missing values, numerical ranges and possible anomalies. describe() produces different summaries for numerical and non-numerical columns.

Selecting columns and rows

orders['units']                 # Series
orders[['region', 'units']]      # DataFrame
orders.loc[0, 'region']          # label-based selection
orders.iloc[0, 1]                # position-based selection

.loc uses labels, whereas .iloc uses integer positions. A common professional practice is to use .loc when the meaning of a label matters and .iloc for positional operations.

orders.loc[orders['units'] > 10, ['order_id', 'region', 'units']]

This selects rows with more than 10 units and returns only the specified columns.

Filtering with isin, query and missing values

orders[orders['region'].isin(['East', 'North'])]
orders.query('units >= 10 and unit_price < 30')

Missing values are commonly represented by NaN or pd.NA.

orders['unit_price'].isna()
orders.dropna(subset=['unit_price'])
orders['unit_price'].fillna(orders['unit_price'].median())

Dropping rows is appropriate when the missing record cannot support the analysis. Imputation may be appropriate when a defensible estimate exists, but the chosen method should be documented. A median replacement, for example, may reduce the effect of extreme values but can distort variability.

3. Creating Business Measures with Vectorization

Suppose each order contains units and unit price. Revenue can be calculated as a new column.

orders['revenue'] = orders['units'] * orders['unit_price']
orders['discount_rate'] = 0.05
orders['net_revenue'] = orders['revenue'] * (1 - orders['discount_rate'])

For a discount rate d, where 0 ≤ d ≤ 1, net revenue is:

Here N is net revenue, R is gross revenue and d is dimensionless. The formula assumes that the discount applies directly to the gross revenue and excludes taxes, shipping and other adjustments.

Conditional transformations can use np.where or Series.where.

orders['order_size'] = np.where(
    orders['units'] >= 10, 'Large', 'Small'
)

For complex classifications, a mapping table or a dedicated function may be easier to audit than deeply nested conditional expressions.

Avoid row-wise apply() when a vectorized expression can perform the same task. apply() is sometimes useful for genuinely custom logic, but it is usually slower and can obscure data types.

4. Sorting, Ranking and Index Management

orders.sort_values('revenue', ascending=False)
orders.sort_values(['region', 'revenue'], ascending=[True, False])
orders['revenue_rank'] = orders['revenue'].rank(method='dense', ascending=False)

Sorting is not the same as ranking. Sorting changes row order; ranking creates a measure of relative position. Ties require a policy, such as average, minimum or dense ranking.

The index can be reset after filtering or sorting.

filtered = orders[orders['units'] > 8].reset_index(drop=True)

Use drop=True when the old index has no analytical meaning. If it represents a useful identifier, retain it or preserve the identifier as a column.

5. Combining Data: Concatenation and Merging

Concatenation

Concatenation stacks objects along an axis. It is suitable when datasets have the same conceptual columns, such as monthly files.

q1 = pd.DataFrame({'order_id': [1, 2], 'revenue': [100, 150]})
q2 = pd.DataFrame({'order_id': [3, 4], 'revenue': [125, 175]})
all_orders = pd.concat([q1, q2], ignore_index=True)

axis=0 adds rows; axis=1 adds columns by aligning indexes. Misaligned indexes can produce missing values, so the alignment rule must be intentional.

Relational merging

A merge combines tables using key columns, similar to a relational database join.

customers = pd.DataFrame({
    'customer_id': [1, 2, 3],
    'segment': ['Retail', 'Business', 'Retail']
})
transactions = pd.DataFrame({
    'order_id': [101, 102, 103],
    'customer_id': [1, 2, 1],
    'revenue': [250, 900, 175]
})

combined = transactions.merge(customers, on='customer_id', how='left')

Common join types are:

A left join is often appropriate when transactions are the primary population and customer attributes are supplementary. Before merging, check whether the key is unique where expected.

customers['customer_id'].is_unique
transactions.merge(customers, on='customer_id', how='left', validate='many_to_one')

validate='many_to_one' documents the assumption that many transactions may belong to one customer. Unexpected row multiplication is a serious analytical risk. If a customer appears twice in the customer table, a transaction may be duplicated in the result, inflating revenue.

6. Reshaping Data

Business data may be stored in wide form, with separate columns for periods, or long form, with one observation per row.

wide = pd.DataFrame({
    'product': ['A', 'B'],
    'Jan': [100, 120],
    'Feb': [110, 125]
})

long = wide.melt(
    id_vars='product',
    var_name='month',
    value_name='sales'
)

melt() converts wide data to long data. Long form is often convenient for grouping, visualisation and statistical modelling.

The reverse operation is commonly performed with pivot or pivot_table.

summary = long.pivot(index='product', columns='month', values='sales')

pivot() requires one unique value for each index-column combination. If duplicates exist, use pivot_table() with an aggregation function.

long.pivot_table(
    index='product', columns='month', values='sales', aggfunc='sum'
)

7. Grouping and Aggregation

The split–apply–combine model divides data into groups, applies a calculation to each group and combines the results.

region_summary = (
    orders.groupby('region', as_index=False)
          .agg(
              total_revenue=('revenue', 'sum'),
              average_order=('revenue', 'mean'),
              order_count=('order_id', 'nunique')
          )
)

For values x₁, x₂, …, xₙ, the arithmetic mean is:

The mean assumes that the observations are appropriate to combine and that missing values are handled consistently. A revenue-weighted average price is different from the simple mean of prices. If units are weights, weighted average price is:

where pᵢ is price and wᵢ is the corresponding quantity. The denominator must be non-zero.

Other useful operations include:

orders.groupby('region')['revenue'].sum()
orders.groupby('region')['revenue'].transform('sum')
orders.groupby('region')['revenue'].cumsum()

agg() produces a reduced summary. transform() returns a result aligned with the original rows, making it useful for calculating each order’s share of regional revenue.

orders['region_total'] = orders.groupby('region')['revenue'].transform('sum')
orders['share_of_region'] = orders['revenue'] / orders['region_total']

8. Dates, Types and Data Quality

Dates should be converted to a datetime type before time-based analysis.

orders['order_date'] = pd.to_datetime(orders['order_date'], errors='coerce')
orders['month'] = orders['order_date'].dt.to_period('M')

errors='coerce' converts invalid values to missing values, which should then be investigated. Do not silently discard invalid dates.

Data types affect correctness and memory use. Numeric values stored as strings cannot be reliably summed. Category fields with a small set of repeated values may use the category dtype.

orders['region'] = orders['region'].astype('category')

Typical quality checks include:

orders.duplicated().sum()
orders['order_id'].duplicated().sum()
orders['units'].ge(0).all()
orders['revenue'].isna().sum()

A quality rule should reflect the business meaning. Negative units may be valid for returns, whereas negative units in a shipment table may indicate an error.

9. Applied Case: Regional Sales Performance

Background

The fictional retailer Northstar Homeware receives transaction data and a customer master file. Management wants to compare regional revenue, identify high-value customer segments and calculate each order’s contribution to regional sales.

Problem

The transaction file contains order_id, customer_id, order_date, units, unit_price and region. The customer file contains customer_id and segment. Some customer records are missing, and management suspects duplicate customer keys.

Analysis and approach

transactions = pd.read_csv('transactions.csv')
customers = pd.read_csv('customers.csv')

transactions['order_date'] = pd.to_datetime(
    transactions['order_date'], errors='coerce'
)
transactions['revenue'] = transactions['units'] * transactions['unit_price']

if not customers['customer_id'].is_unique:
    raise ValueError('Customer key is not unique')

sales = transactions.merge(
    customers,
    on='customer_id',
    how='left',
    validate='many_to_one',
    indicator=True
)

sales['segment'] = sales['segment'].fillna('Unknown')
sales['region_total'] = sales.groupby('region')['revenue'].transform('sum')
sales['regional_share'] = sales['revenue'] / sales['region_total']

report = (
    sales.groupby(['region', 'segment'], as_index=False)
         .agg(
             revenue=('revenue', 'sum'),
             orders=('order_id', 'nunique'),
             units=('units', 'sum')
         )
         .sort_values('revenue', ascending=False)
)

Outcome

The process produces an auditable segment-by-region report and identifies unmatched customers through the _merge indicator. Management can investigate missing master-data links before using the report for performance evaluation.

Learning points

The case illustrates that data management is not merely a programming task. Correct joins, validation rules, meaningful missing-value treatment and clear metric definitions are necessary before the results can support decisions. A technically successful script can still produce misleading results if business keys or assumptions are wrong.

10. Performance, Governance and Professional Practice

For large datasets, select only required columns when reading files, use appropriate dtypes and avoid unnecessary copies. Chunked reading can process files that do not fit comfortably in memory.

for chunk in pd.read_csv('large_file.csv', usecols=['region', 'revenue'], chunksize=100_000):
    # process each chunk
    pass

Good practice includes:

• preserve raw data and create derived datasets separately;
• document column definitions, units, assumptions and transformations;
• validate keys before joins;
• test row counts before and after major operations;
• avoid overwriting source columns without a clear reason;
• use reproducible scripts rather than undocumented manual edits;
• control access to personal or commercially sensitive data;
• remove or protect personally identifiable information where it is not required;
• maintain an audit trail for published metrics.

pandas and NumPy do not automatically guarantee data quality, privacy or statistical validity. A result may be computationally correct but unsuitable for a decision because of biased sampling, incomplete records, leakage of future information or an inappropriate metric.

Contemporary analytics workflows increasingly combine pandas with database queries, cloud data warehouses, columnar formats such as Parquet and distributed tools for datasets larger than memory. The core principles remain the same: clear structure, explicit assumptions, validation and reproducibility.

Chapter Summary

NumPy arrays provide efficient numerical storage and vectorized computation. pandas Series and DataFrames add labels, mixed data types and tabular operations suited to business datasets. Indexing and Boolean filtering select relevant observations; sorting and ranking organise them; vectorized expressions create measures efficiently. Missing values and data types must be handled deliberately. Concatenation combines compatible datasets, while merges combine related tables through keys and require validation against duplicate-key errors. Reshaping changes data between wide and long forms, and grouping implements the split–apply–combine model for business summaries. Professional data management also requires documented assumptions, quality checks, privacy protection and reproducible workflows.

Key Terms

NumPy array: A multidimensional numerical data structure provided by NumPy.

Vectorization: Applying an operation to many values without an explicit Python loop.

Broadcasting: NumPy’s method for applying compatible arrays or scalars across dimensions.

Series: A one-dimensional pandas object with labelled values.

DataFrame: A two-dimensional pandas table with labelled rows and columns.

Index: The row labels used by pandas for alignment and selection.

Boolean indexing: Selecting observations using True/False conditions.

View: An array representation that may share memory with its source.

Copy: An independent data object that does not share the source’s data memory.

Missing value: A value absent, unknown or invalid in a dataset.

Imputation: Replacing missing values with estimated or rule-based values.

Concatenation: Combining objects along rows or columns.

Merge: Combining tables using matching key values.

Join key: A column or index used to relate records across datasets.

Reshaping: Changing the structural arrangement of rows and columns.

Aggregation: Reducing multiple observations to summary values.

GroupBy: A pandas operation implementing split–apply–combine.

Broadcasting: Applying compatible values across an array operation.

Data validation: Checking whether data satisfy structural and business rules.

Data lineage: The documented history of a dataset and its transformations.

Review Questions

Short Answer Questions

1. What is the main difference between a NumPy array and a pandas DataFrame?
2. Explain the difference between .loc and .iloc.
3. Why can an unvalidated merge inflate reported revenue?
4. What is the purpose of melt()?
5. When might median imputation be preferable to mean imputation?

Long Answer Questions

1. Explain how vectorization and broadcasting support efficient business calculations in NumPy and pandas.
2. Compare inner, left, right and outer joins, giving a business example for each.
3. Describe a robust workflow for inspecting and validating a newly imported DataFrame.
4. Explain the split–apply–combine model and show how it supports regional sales reporting.
5. Discuss the technical, ethical and governance risks of using poorly documented business data.

Analytical / Application Questions

1. A customer table contains duplicate customer IDs and is merged with a transaction table. Explain how you would diagnose the problem, prevent row multiplication and report the impact.
2. Design pandas operations to calculate each product’s monthly revenue, its share of monthly revenue and its rank within each month.
3. A company reports that average unit price increased after a promotion. Explain how you would test whether this reflects a genuine price change, a change in product mix or a data-quality problem.

Multiple Choice Questions with Answers

Question 1

Which pandas object is designed as a two-dimensional labelled table?

A. NumPy scalar

B. Series

C. DataFrame

D. Python tuple

Correct Answer: C. DataFrame

Question 2

Which expression selects rows where units are at least 10?

A. orders.loc[orders['units'] >= 10]

B. orders.loc['units' >= 10]

C. orders.select(units >= 10)

D. orders.filter(units >= 10)

Correct Answer: A. orders.loc[orders['units'] >= 10]

Question 3

What is the primary purpose of validate='many_to_one' in a pandas merge?

A. To sort the merged table

B. To verify an expected key relationship

C. To remove missing values

D. To convert keys to integers

Correct Answer: B. To verify an expected key relationship

Question 4

Which function converts wide data into long form?

A. concat

B. merge

C. melt

D. rank

Correct Answer: C. melt

Question 5

If revenue is 500 and cost is 320, what is profit?

A. 180

B. 820

C. 1.56

D. 62.5

Correct Answer: A. 180

Question 6

Which operator performs element-wise logical AND for NumPy or pandas conditions?

A. and

B. &&

C. &

D. AND()

Correct Answer: C. &

Question 7

What does groupby().transform('sum') generally return?

A. One scalar for the entire DataFrame

B. Group totals aligned with the original rows

C. A sorted copy with no index

D. Only the unique group labels

Correct Answer: B. Group totals aligned with the original rows

Question 8

Which join keeps every row from the left table?

A. inner

B. right

C. outer

D. left

Correct Answer: D. left

Question 9

Why should a business key be checked before a merge?

A. To guarantee alphabetical sorting

B. To prevent unexpected row multiplication

C. To convert every column to a category

D. To remove all zero values

Correct Answer: B. To prevent unexpected row multiplication

Question 10

What does pd.to_datetime(..., errors='coerce') do with invalid date values?

A. It converts them to zero

B. It raises an error immediately in every case

C. It converts them to missing datetime values

D. It sorts them alphabetically

Correct Answer: C. It converts them to missing datetime values