Files, Data Processing, and Basic Analytics

Introduction

Most useful programs do more than calculate values temporarily held in memory. They read information from files, transform it into a consistent form, identify errors, and produce evidence that supports decisions. A sales report, attendance register, sensor log, survey response file, or inventory list is valuable only when a program can handle it reliably.

This chapter introduces practical techniques for working with text files, CSV files, and structured data in Python and Java. It also develops a small analytics workflow: inspect, clean, transform, summarize, validate, and communicate results. The emphasis is on certificate-level programming: students should understand the concepts and be able to write small, dependable programs without relying entirely on specialized analytics libraries.

A data-processing program can be viewed as a pipeline:

Input file → Reading → Parsing → Cleaning → Validation → Transformation
                                                     ↓
                                         Summary and decision support

The order matters. Averages calculated from incorrect or inconsistent values may be precise but misleading.

Learning Objectives

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

• explain files, records, fields, delimiters, encoding, and file paths;
• read and write text files safely in Python and Java;
• process CSV files while recognizing common data-quality problems;
• represent simple structured data using dictionaries, lists, classes, and JSON;
• clean, transform, and validate data;
• calculate and interpret basic summaries;
• create a small evidence-based data-processing application;
• identify security, privacy, reliability, and ethical issues in file-based programs.

1. Files as Persistent Data

A variable normally exists only while a program is running. A file provides persistent storage: its contents remain after the program terminates. Files may be stored locally, on a network drive, or in a cloud service, but the basic programming ideas are similar.

A file path identifies the location of a file. An absolute path gives the complete location, such as C:\\Data\\sales.csv on Windows or /home/user/data/sales.csv on Linux. A relative path is interpreted from the program's current working directory, such as data/sales.csv. Relative paths make programs more portable, but the working directory must be understood.

A text file is a sequence of characters. Common encodings include UTF-8 and UTF-16. UTF-8 is widely used because it represents ordinary English text efficiently while also supporting many international characters. When reading or writing text, explicitly choosing an encoding reduces platform-dependent errors.

A file operation normally follows this lifecycle:

1. open the file;
2. read from or write to it;
3. process the contents;
4. close the file.

A failure at any stage can affect the program. The preferred practice is to use a construct that closes the file automatically, even if an error occurs.

Text modes

A program should avoid using w when it intends to preserve earlier results. For sensitive or important data, create a backup or write to a temporary output file before replacing the original.

2. Reading and Writing Plain Text

Plain text is appropriate for notes, logs, configuration files, and simple line-oriented data. A line usually ends with a newline character, which should often be removed before processing.

Python example

from pathlib import Path

path = Path("data/temperatures.txt")

try:
    with path.open("r", encoding="utf-8") as file:
        temperatures = []
        for line in file:
            text = line.strip()
            if text:
                temperatures.append(float(text))
except FileNotFoundError:
    print("The input file was not found.")
except ValueError:
    print("A line did not contain a valid number.")

with Path("data/summary.txt").open("w", encoding="utf-8") as file:
    file.write("Temperature summary\n")
    file.write(f"Readings: {len(temperatures)}\n")

The with statement closes the file automatically. strip() removes leading and trailing whitespace. Converting text to float is an example of parsing: the program changes a character sequence into a numeric value.

Java example

import java.io.BufferedReader;
import java.io.BufferedWriter;
import java.io.IOException;
import java.nio.charset.StandardCharsets;
import java.nio.file.Files;
import java.nio.file.Path;

Path input = Path.of("data", "temperatures.txt");

try (BufferedReader reader = Files.newBufferedReader(input,
        StandardCharsets.UTF_8)) {
    String line;
    while ((line = reader.readLine()) != null) {
        String text = line.trim();
        if (!text.isEmpty()) {
            double temperature = Double.parseDouble(text);
            System.out.println(temperature);
        }
    }
} catch (IOException ex) {
    System.out.println("File error: " + ex.getMessage());
} catch (NumberFormatException ex) {
    System.out.println("Invalid numeric value.");
}

Path output = Path.of("data", "summary.txt");
try (BufferedWriter writer = Files.newBufferedWriter(output,
        StandardCharsets.UTF_8)) {
    writer.write("Temperature summary");
    writer.newLine();
}

Java's try-with-resources statement performs automatic closing. BufferedReader and BufferedWriter are efficient for line-based text processing. Exceptions should be handled at a level where the program can respond meaningfully.

3. CSV Data

CSV means comma-separated values. A CSV file commonly stores one record per line and one field per column:

product,quantity,unit_price
Notebook,12,3.50
Pen,25,1.20

CSV is simple but not merely “split every line at commas.” A field may contain a comma inside quotation marks:

product,description,quantity
Notebook,"A4, ruled",12

A proper CSV parser understands quoting, escaped quotation marks, empty fields, and line endings. Consequently, use a library rather than manually calling split(',') for general CSV input.

Python CSV processing

import csv
from pathlib import Path

records = []
with Path("sales.csv").open("r", newline="", encoding="utf-8") as file:
    reader = csv.DictReader(file)
    required = {"product", "quantity", "unit_price"}
    if not required.issubset(reader.fieldnames or []):
        raise ValueError("Required columns are missing")

    for row_number, row in enumerate(reader, start=2):
        try:
            product = row["product"].strip()
            quantity = int(row["quantity"])
            unit_price = float(row["unit_price"])
            records.append({
                "product": product,
                "quantity": quantity,
                "unit_price": unit_price,
                "revenue": quantity * unit_price
            })
        except (TypeError, ValueError) as error:
            print(f"Skipping row {row_number}: {error}")

DictReader uses the header row as field names. The program performs parsing and creates a derived field, revenue. In financial applications, binary floating-point numbers can create small rounding differences. For currency that must be exact, Python's decimal.Decimal is preferable.

Java CSV considerations

The Java standard library provides file and string utilities but no universally convenient built-in CSV parser with all CSV rules. For simple, controlled files, Files.readAllLines and a carefully designed parser may be adequate. For production data, use a reputable CSV library approved by the organization, and configure its delimiter, quote character, encoding, and error policy. Never assume that a comma split is correct when fields can contain quoted commas.

4. Structured Data and JSON

Structured data has an explicit organization. A record may contain named fields, nested objects, and lists. JSON (JavaScript Object Notation) is a widely used text format:

{
  "student_id": "S104",
  "name": "Amina Khan",
  "scores": [72, 81, 77],
  "active": true
}

JSON values can be objects, arrays, strings, numbers, Boolean values, or null. JSON is useful for configuration files and communication between programs, but it should be validated before its values are trusted.

Python's standard json module can load and save JSON:

import json

with open("student.json", encoding="utf-8") as file:
    student = json.load(file)

average = sum(student["scores"]) / len(student["scores"])
student["average"] = average

with open("student_updated.json", "w", encoding="utf-8") as file:
    json.dump(student, file, indent=2)

Java programs commonly use a JSON library such as Jackson or Gson. A library maps JSON to classes or generic maps and handles escaping and nested structures more reliably than handwritten string manipulation. Dependencies should be selected, updated, and governed according to institutional policy.

5. A Data-Quality Workflow

A reliable workflow separates six related activities.

5.1 Inspect

Before changing data, inspect its structure. Determine:

• which columns exist and whether names are consistent;
• how many records are present;
• which fields are numeric, categorical, dates, or identifiers;
• whether values are missing, duplicated, or unexpected;
• which units and conventions are being used.

Do not infer that a column is numeric merely because most values look numeric. An identifier such as 00125 may need to remain text because removing leading zeros changes its meaning.

5.2 Clean

Cleaning makes representations consistent and addresses known quality problems. Common operations include:

• trimming surrounding whitespace;
• standardizing capitalization where appropriate;
• converting dates to a consistent format;
• converting numeric text into numbers;
• mapping equivalent categories, such as M, Male, and male;
• removing or correcting impossible values;
• deciding how missing values will be represented.

Cleaning must preserve meaning. Automatically replacing every missing value with zero is dangerous: “unknown,” “not applicable,” and “zero” are different concepts.

5.3 Transform

Transformation derives useful variables. For a transaction record, total value may be calculated as:

Here, quantity is measured in units, unit_price in currency per unit, and revenue in currency. The formula assumes that both fields refer to the same product, transaction, currency, and time basis.

A date can be transformed into year or month. A text category can be mapped to a standard code. Transformation rules should be documented so that another person can reproduce the result.

5.4 Validate

Validation checks whether data satisfies defined rules. Examples include:

• required fields are present;
• values have the correct type;
• quantities are non-negative integers;
• prices are not negative;
• dates are valid and within an expected period;
• category values belong to an approved set;
• identifiers are unique where uniqueness is required.

Validation can be performed before processing, during parsing, and after transformation.

5.5 Summarize

A summary reduces many records to interpretable measures. For values x₁, x₂, …, x_n, the arithmetic mean is:

Here, n is the number of observed values and xᵢ is the value of observation i. The mean is applicable when values are numeric and a meaningful arithmetic average is desired. It is sensitive to extreme values.

The minimum and maximum identify the range's endpoints. The median is the middle ordered value; when n is even, it is the average of the two middle values. The median is often more resistant to outliers than the mean.

For a category, frequency is the number of records in that category. A proportion is:

If expressed as a percentage, multiply the proportion by 100. The denominator must be clearly defined; for example, it may be all valid records rather than all received records.

Worked example

Suppose valid daily sales are 80, 100, 120, and 100 currency units. The mean is:

The ordered values are 80, 100, 100, 120, so the median is 100. The range is 120 − 80 = 40 currency units. These summaries describe this small dataset, but they do not prove why sales changed or predict future sales.

6. Missing, Duplicate, and Invalid Data

A missing value can arise because a respondent skipped a question, a sensor failed, or a field was not applicable. A program should distinguish at least:

• missing but expected;
• not applicable;
• unknown;
• deliberately suppressed for privacy;
• zero, which is an actual measured value.

Possible treatments are deletion, imputation, explicit “unknown” categories, or reporting missingness separately. The correct choice depends on the purpose and domain. For a certificate-level application, documenting the rule is more important than applying a sophisticated method blindly.

A missingness rate can be reported as:

The expected count must be defined. If a file should contain 500 records and 12 have no email address, the email missingness rate is 12 ÷ 500 × 100% = 2.4%, assuming every record is eligible for that field.

Duplicate records may result from repeated uploads or legitimate repeated events. A duplicate should be defined using an appropriate key. For example, a customer identifier alone may not identify a unique purchase; customer identifier plus transaction identifier may.

7. Validation in Programs

Validation should produce useful feedback rather than silently discarding data. A validation result may contain the row number, field, value, and reason:

def validate_sale(row):
    errors = []
    if not row.get("product", "").strip():
        errors.append("product is required")
    try:
        quantity = int(row["quantity"])
        if quantity < 0:
            errors.append("quantity cannot be negative")
    except (KeyError, TypeError, ValueError):
        errors.append("quantity must be an integer")
    try:
        price = float(row["unit_price"])
        if price < 0:
            errors.append("unit_price cannot be negative")
    except (KeyError, TypeError, ValueError):
        errors.append("unit_price must be numeric")
    return errors

A robust application can write invalid rows to an error file while continuing with valid rows. Whether processing should stop depends on the context. A payroll file with one invalid employee record may require complete stoppage, whereas a large survey import may reasonably quarantine invalid records and report them.

Validation rules are not merely technical. A rule such as “age must be at least 18” is a domain or policy decision and should be confirmed by the responsible organization.

8. Grouping and Basic Analytics

Grouping calculates summaries for categories, such as revenue by product or attendance by class. A dictionary is sufficient for many small programs:

from collections import defaultdict

revenue_by_product = defaultdict(float)
quantity_by_product = defaultdict(int)

for sale in records:
    product = sale["product"]
    revenue_by_product[product] += sale["revenue"]
    quantity_by_product[product] += sale["quantity"]

for product in sorted(revenue_by_product):
    print(product, quantity_by_product[product], revenue_by_product[product])

The program uses additive aggregation. If each record is valid and revenue is measured in the same currency, the total revenue is the sum of individual revenues:

This calculation assumes that records do not overlap and that returns, discounts, and taxes have been treated consistently. A total is not automatically a measure of profitability; costs would also be needed.

When reporting a result, include context: the period, population, units, number of valid records, excluded records, and relevant cleaning rules. A graph or table may communicate the result, but visual presentation does not remove the need for validation.

9. Applied Case: Community Library Loan Analysis

The following is an illustrative case, not a report from a real organization.

Background

A community library records book loans in a CSV file with the columns loan_id, member_type, category, loan_date, and returned. The library wants to know which categories are most frequently borrowed and how complete its data is.

Problem

The file contains extra spaces in category names, Boolean values written as Yes, yes, and Y, and several blank return indicators. One row has a missing loan identifier, and two rows repeat the same loan identifier.

Analysis and approach

The analyst first preserves the original file and creates a working copy. The program then:

1. checks that required columns exist;
2. trims text fields;
3. converts category names to a standard case;
4. maps yes, y, and true to True, and no, n, and false to False;
5. rejects rows with missing loan identifiers;
6. identifies duplicate loan identifiers for review;
7. counts valid loans by category;
8. reports the number of missing return indicators.

A category count is computed only from valid records. If 240 valid loans are available and 72 are in the fiction category, fiction represents 72 ÷ 240 × 100% = 30%. The report must state whether duplicate rows were removed, retained, or sent for investigation.

Outcome

The library receives a table containing category, valid loan count, and percentage of valid loans, together with a data-quality section listing rejected rows, duplicates, and missing return values. Staff can use the category results to guide display space and purchasing discussions, while recognizing that borrowing frequency alone does not measure community value or demand that was unavailable because a book was absent.

Learning points

The case demonstrates that analytics begins with definitions and quality checks. A percentage without a denominator is incomplete, and a ranking without a description of excluded records can mislead. The program supports a decision; it does not replace professional judgment.

10. Reliability, Security, and Ethics

File-processing programs face practical risks.

Path and file risks: Validate input paths, avoid unexpectedly processing arbitrary files, and do not overwrite original data without confirmation. Use controlled directories where possible.

Injection and unsafe content: Data may contain text that looks like a command, formula, or markup. Treat input as data. If exporting to a spreadsheet, consider spreadsheet-formula injection, in which a cell beginning with characters such as = is interpreted as a formula.

Privacy: Names, addresses, student results, health information, and identifiers may be personal data. Collect only what is necessary, restrict access, avoid exposing data in error messages, and follow applicable institutional and legal requirements.

Confidentiality and retention: Encrypt sensitive files when appropriate, control permissions, define retention periods, and securely delete data when it is no longer needed.

Auditability: Keep the original file, record processing time and program version, document transformations, and retain validation summaries. Reproducibility is especially important when results influence funding, employment, education, or access to services.

Bias and interpretation: A dataset may underrepresent some groups or reflect historical decisions. A program can process biased data accurately while producing an unfair conclusion. Check coverage, definitions, and consequences before using an output for a high-impact decision.

11. Best Practices and Contemporary Practice

Good file-processing programs are small, testable, and explicit. Recommended practices include:

• use UTF-8 unless a documented alternative is required;
• use standard parsers for CSV and JSON;
• separate reading, validation, transformation, and reporting functions;
• avoid loading very large files into memory when line-by-line processing is sufficient;
• use clear variable names and comments for business rules;
• test empty files, missing columns, malformed rows, duplicate identifiers, and unusual characters;
• write automated tests for cleaning and validation functions;
• report both successful and rejected records;
• preserve input data and use versioned outputs;
• use decimal arithmetic for exact currency requirements;
• never treat an AI-generated summary as reliable without checking the source data and calculations.

Modern data tools can automate classification, anomaly detection, and natural-language reporting. Such tools may accelerate work, but they can also conceal assumptions, reproduce bias, or expose confidential data. Human review, access controls, and reproducible validation remain necessary.

Chapter Summary

Files provide persistent storage for information used by programs. Safe file processing involves correct paths, explicit encoding, suitable modes, automatic closing, and meaningful exception handling. Plain text is useful for line-oriented data, while CSV and JSON require parsers that respect their structures.

A dependable data workflow inspects, cleans, transforms, validates, and summarizes data in that order. Cleaning includes consistent text, dates, numbers, and missing-value treatment. Validation applies type, range, completeness, uniqueness, and domain rules. Basic analytics includes totals, counts, proportions, means, medians, ranges, and grouped summaries. Every result should state its units, denominator, period, exclusions, and assumptions.

File-based analytics also involves privacy, security, auditability, bias, and responsible interpretation. A technically correct program can still support a poor decision if the data or definitions are unsuitable. Evidence-based programming therefore combines code, documentation, quality controls, and professional judgment.

Key Terms

File: A named collection of data stored persistently.

File path: A location used to identify a file.

Encoding: A rule for representing characters as bytes.

Text file: A file containing character data intended to be read as text.

CSV: A tabular text format commonly containing comma-separated fields.

Delimiter: A character separating fields in a record.

Record: One logical item or row in a dataset.

Field: One named attribute or column of a record.

Parser: Software that interprets the structure of input data.

Structured data: Data organized according to explicit fields, objects, or arrays.

JSON: A text format for objects, arrays, and primitive values.

Cleaning: Correcting or standardizing data representations.

Transformation: Converting data or deriving new values for analysis.

Validation: Checking data against type, range, completeness, or domain rules.

Missing value: An absent, unknown, or inapplicable value.

Duplicate: A repeated record or identifier according to a stated definition.

Mean: The arithmetic sum of numeric observations divided by their count.

Median: The middle value after observations are ordered.

Aggregation: Combining records using operations such as sum or count.

Reproducibility: The ability to obtain results again using documented inputs and procedures.

Review Questions

Short Answer Questions

1. Why is automatic file closing important in a file-processing program?
2. What is the difference between a relative path and an absolute path?
3. Why should a CSV library generally be preferred to split(',')?
4. Give two examples of validation rules for a sales dataset.
5. Why is replacing every missing value with zero potentially misleading?

Long Answer Questions

1. Explain a complete workflow for converting a raw CSV file into a validated summary report.
2. Compare plain text, CSV, and JSON in terms of structure, typical use, and processing requirements.
3. Explain the mean, median, range, and proportion, including one limitation or interpretation issue for each.
4. Describe how Python or Java can handle file errors and malformed records without losing control of the program.
5. Discuss privacy, security, auditability, and bias in a file-based analytics application.

Analytical / Application Questions

1. A sales file contains quantities of 10, 8, and -2, prices of 5.00, 7.50, and 4.00, and one missing product name. Design validation rules and explain which records should be accepted, rejected, or quarantined.
2. A school receives 1,000 attendance records, of which 40 have missing student identifiers and 20 are duplicates. Explain how you would report data completeness and decide what to do before calculating attendance rates.
3. Design a small Python or Java program that reads transactions, calculates revenue, groups results by product, writes a summary file, and records invalid rows. Identify at least three assumptions that must be documented.

Multiple Choice Questions with Answers

Question 1

Which Python construct automatically closes a file after its block finishes?

A. The with statement

B. The print function

C. The import statement

D. The continue statement

Correct Answer: A. The with statement

Question 2

Why is a standard CSV parser preferable to splitting every line at commas?

A. It automatically encrypts the file

B. It handles quoting and commas inside fields

C. It converts every field to an integer

D. It removes all missing records

Correct Answer: B. It handles quoting and commas inside fields

Question 3

What does validation primarily determine?

A. Whether data satisfies defined rules

B. Whether a computer has enough battery power

C. Whether a file has the largest possible size

D. Whether every category has the same frequency

Correct Answer: A. Whether data satisfies defined rules

Question 4

The values 4, 6, and 8 have what arithmetic mean?

A. 4

B. 5

C. 6

D. 8

Correct Answer: C. 6

Question 5

Which treatment best distinguishes an actual zero from an unknown value?

A. Represent both as zero

B. Delete every record containing either value

C. Use separate, documented representations

D. Replace both with the mean

Correct Answer: C. Use separate, documented representations

Question 6

A transaction has quantity 6 and unit price 12 currency units. Under the stated revenue formula, what is its revenue?

A. 18 currency units

B. 36 currency units

C. 72 currency units

D. 144 currency units

Correct Answer: C. 72 currency units

Question 7

Which JSON value represents a collection of ordered values?

A. An array

B. A comment

C. A file path

D. An exception

Correct Answer: A. An array

Question 8

If 25 of 200 eligible records are missing an email address, what is the missingness rate?

A. 5%

B. 12.5%

C. 20%

D. 25%

Correct Answer: B. 12.5%

Question 9

Which file mode replaces an existing file's contents in typical Python usage?

A. r

B. a

C. w

D. x

Correct Answer: C. w

Question 10

Why should an analytics report state its denominator when presenting a percentage?

A. To show what population the percentage describes

B. To make all percentages equal

C. To remove the need for validation

D. To guarantee that the data is unbiased

Correct Answer: A. To show what population the percentage describes