Project: a CSV statistics tool in D

csvstats reads a CSV file and prints, for every column with numbers, its count, mean, median, minimum and maximum with two decimals, and how many cells it skipped. Two rules decide more than the code does: which cells are numbers, and how a cell becomes a double. Read the cell 0.045 with D’s to!double and the report prints 0.05; read it with C’s strtod, as csvstats does, and it prints 0.04, as Python does.

This project builds the tool with dub and tests its modules, compares three readers on fourteen awkward cells, measures reading on 10,000 cells that sit on a rounding tie, and times a million rows against a budget of 2 seconds of processor time. The practice builds the same report again in Python, one step at a time.

The specification fixes every line of the report

Here is the sample file of this project. It has the cells that real exports contain: an empty one, n/a, a quoted note with a comma, and a temperature written with a space in front of it:

station,hour,celsius,rain_mm,note
Pune,6,21.5,0.0,
Pune,12,32.5,0.0,clear
Nagpur,6,24.0,1.25,
Nagpur,12,38.5,0.0,hot
Nashik,6,19.5,n/a,sensor down
Nashik,12,29.0,4.75,
Solapur,6, 23.0,0.5,"rain, light"
Solapur,12,36.0,,

What csvstats prints for it is fixed before any code is written, so that two programs written from these rules, in any language, print the same bytes:

1. The first line is the header. Each column gets one line of the report, in the header’s order.
2. A cell counts when, taken exactly as it is, it is a number by rule 3 and its value fits a double. Every other cell of the column, an empty one too, is skipped and counted.
3. A number is an optional + or -, then digits with an optional point and more digits, or a point and digits, then an optional exponent: e or E, an optional sign and digits. Only the ASCII digits 0 to 9 count.
4. A column with numbers prints count, mean, median, min, max and skipped. The four values have two decimals, from the nearest double of each cell; the mean adds the values in file order, and the median of an even count is the two middle values added and halved.
5. A column without numbers prints no numbers and the cells it skipped.
6. No file name gives a usage line and exit statusexit status: The whole number a program returns to whatever started it: 0 for success by convention, anything else for a failure. Shells and build pipelines read it to decide what happens next. More in “Project: a marks report in Object Pascal” 2, a file that cannot be read exit status 1, and a record with another number of cells than the header exit status 2, each with a message on standard errorstandard error: The second output stream every program has, meant for error messages. Kept apart from standard output, it lets a report be saved to a file while its errors still reach the terminal or a log. More in “Project: a marks report in Object Pascal”.

Step through one run on weather.csv, to the celsius line:

Each module has one job and its own tests

csvstats is a dub project of four files. app.d holds main, which only checks the arguments, reads the file and turns each kind of error into a message and an exit status. The work is in three modules under source/csvstats/, and each of them has a unittest block:

report.d is the heart: it reads the records with csvReader, sorts the cells into one list of numbers and one count of skipped cells per column, and builds the lines:

module csvstats.report;

import std.csv : csvReader;
import std.format : format;
import csvstats.cells : isNumber, toNumber;
import csvstats.stats : summarise, twoDecimals;

/// The report of a CSV text whose first line is the header: one line per column, in the header's order.
string[] report(string text)
{
    auto records = csvReader!string(text, null);
    auto header = records.header;
    auto numbers = new double[][](header.length);
    auto skipped = new size_t[](header.length);
    foreach (record; records)
    {
        size_t column = 0;
        foreach (cell; record)
        {
            immutable value = isNumber(cell) ? toNumber(cell) : double.nan;
            if (value == value)          // false only for NaN: not a number, or too large
                numbers[column] ~= value;
            else
                skipped[column]++;
            column++;
        }
    }
    string[] lines;
    foreach (i, name; header)
    {
        if (numbers[i].length == 0)
        {
            lines ~= format("%s: no numbers, %s cells skipped", name, skipped[i]);
            continue;
        }
        immutable s = summarise(numbers[i]);
        lines ~= format("%s: count %s, mean %s, median %s, min %s, max %s, skipped %s", name, s.count,
            twoDecimals(s.mean), twoDecimals(s.median), twoDecimals(s.min), twoDecimals(s.max), skipped[i]);
    }
    return lines;
}

unittest
{
    immutable text = "city,marks\nPune,72\nNagpur,n/a\nNashik,61\n";
    assert(report(text) == [
        "city: no numbers, 3 cells skipped",
        "marks: count 2, mean 66.50, median 66.50, min 61.00, max 72.00, skipped 1",
    ]);
}

from session import run

# csvstats as a dub project: its files and recipe, its unit tests, a release build, the reports of two sample files
# and the three ways it can refuse to run.
run("""
cd csvstats
find source -name '*.d' | sort
cat dub.json
dub test
dub build --build=release
./csvstats ../data/weather.csv
./csvstats ../data/halves.csv
!./csvstats ../data/broken.csv
!./csvstats missing.csv
!./csvstats
""")

$ cd csvstats
$ find source -name '*.d' | sort
source/app.d
source/csvstats/cells.d
source/csvstats/report.d
source/csvstats/stats.d
$ cat dub.json
{
	"name": "csvstats",
	"description": "Statistics for each numeric column of a CSV file.",
	"license": "BSL-1.0",
	"targetType": "executable"
}
$ dub test
             Generating test runner configuration 'csvstats-test-application' for 'application' (executable).
     Warning Excluding main source file source/app.d from test.
    Starting Performing "unittest" build using …/bin/ldc2 for aarch64, arm_hardfloat.
    Building csvstats ~master: building configuration [csvstats-test-application]
     Linking csvstats-test-application
     Running csvstats-test-application
3 modules passed unittests
$ dub build --build=release
    Starting Performing "release" build using …/bin/ldc2 for aarch64, arm_hardfloat.
    Building csvstats ~master: building configuration [application]
     Linking csvstats
$ ./csvstats ../data/weather.csv
station: no numbers, 8 cells skipped
hour: count 8, mean 9.00, median 9.00, min 6.00, max 12.00, skipped 0
celsius: count 7, mean 28.71, median 29.00, min 19.50, max 38.50, skipped 1
rain_mm: count 6, mean 1.08, median 0.25, min 0.00, max 4.75, skipped 2
note: no numbers, 8 cells skipped
$ ./csvstats ../data/halves.csv
name: no numbers, 2 cells skipped
maths: count 2, mean 0.12, median 0.12, min 0.12, max 0.12, skipped 0
physics: count 2, mean 1.84, median 1.84, min 1.00, max 2.67, skipped 0
$ ./csvstats ../data/broken.csv
csvstats: ../data/broken.csv: Row 2's length 2 does not match previous length of 3.
[exit status 2]
$ ./csvstats missing.csv
csvstats: missing.csv: No such file or directory
[exit status 1]
$ ./csvstats
usage: csvstats FILE.csv
[exit status 2]

dub test reported 3 modules passed unittests, one for each module with tests. This is the layout the first lesson of this mini-course arrived at: dub test leaves the file with main out, so nothing that needs a test lives there. The two reports match the specification line by line: celsius counted 7 of its 8 cells and skipped " 23.0", rain_mm skipped n/a and the empty cell, and station and note printed no numbers. The three refusals ended with the exit statuses of rule 6, so a script that runs csvstats can tell a missing file, status 1, from a bad file or a wrong call, status 2.

A written rule decides what a number is

Rule 3 is code of its own, in cells.d, and it does not ask any parser:

module csvstats.cells;

import core.stdc.stdlib : strtod;
import std.ascii : isDigit;
import std.math : isFinite;
import std.string : toStringz;

/// True when cell, taken exactly as it is, is a number by the specification: an optional + or -, digits with an
/// optional point and more digits (or a point and digits), then an optional exponent; ASCII digits only, nothing else.
bool isNumber(const(char)[] cell) @safe pure nothrow @nogc
{
    size_t i = 0;
    if (i < cell.length && (cell[i] == '+' || cell[i] == '-'))
        i++;
    size_t digits = 0;
    while (i < cell.length && isDigit(cell[i]))
    {
        i++;
        digits++;
    }
    if (i < cell.length && cell[i] == '.')
    {
        i++;
        while (i < cell.length && isDigit(cell[i]))
        {
            i++;
            digits++;
        }
    }
    if (digits == 0)
        return false;
    if (i < cell.length && (cell[i] == 'e' || cell[i] == 'E'))
    {
        i++;
        if (i < cell.length && (cell[i] == '+' || cell[i] == '-'))
            i++;
        immutable start = i;
        while (i < cell.length && isDigit(cell[i]))
            i++;
        if (i == start)
            return false;
    }
    return i == cell.length;
}

/// The nearest double to a cell that isNumber accepts, read by C's strtod; NaN when it does not fit a double.
double toNumber(const(char)[] cell) @trusted
{
    char[64] buffer = void;
    const(char)* text;
    if (cell.length < buffer.length)
    {
        buffer[0 .. cell.length] = cell[];
        buffer[cell.length] = '\0';
        text = buffer.ptr;
    }
    else
        text = cell.toStringz;
    immutable value = strtod(text, null);
    return isFinite(value) ? value : double.nan;
}

unittest
{
    import std.format : format;

    foreach (cell; ["12", "-0.25", "+7", "1e3", "2.5E-3", ".5", "5."])
        assert(isNumber(cell), cell);
    foreach (cell; ["", " 12", "12 ", "1,000", "1_000", "nan", "inf", "0x1A", "1e", "-", ".", "١٢"])
        assert(!isNumber(cell), cell);
    assert(format("%.2f", toNumber("1.005")) == "1.00");    // the nearest double is just below 1.005
    assert(toNumber("1e400") != toNumber("1e400"));         // too large for a double: NaN
}

Why not let to!double decide, and count a cell as skipped when it throws? Because each reader has its own idea of a number, and none of them is the specification. The session gives fourteen cells to to!double, to csvstats and to Python’s float():

from session import run

# What three readers make of the same fourteen cells: D's to!double, the rule csvstats uses, and Python's float().
run("""
cd cells
ldc2 -I../csvstats/source cellrules.d ../csvstats/source/csvstats/cells.d
./cellrules
python3 cellrules.py
""")

$ cd cells
$ ldc2 -I../csvstats/source cellrules.d ../csvstats/source/csvstats/cells.d
$ ./cellrules
[12]       to!double: 12        csvstats: number
[-0.25]    to!double: -0.25     csvstats: number
[1e3]      to!double: 1000      csvstats: number
[.5]       to!double: 0.5       csvstats: number
[ 12]      to!double: refused   csvstats: skipped
[12 ]      to!double: refused   csvstats: skipped
[1,000]    to!double: refused   csvstats: skipped
[1_000]    to!double: 1000      csvstats: skipped
[nan]      to!double: nan       csvstats: skipped
[Infinity] to!double: refused   csvstats: skipped
[0x1Ap0]   to!double: 26        csvstats: skipped
[१२]       to!double: refused   csvstats: skipped
[]         to!double: refused   csvstats: skipped
[1e400]    to!double: refused   csvstats: skipped
$ python3 cellrules.py
[12]       float(): 12.0
[-0.25]    float(): -0.25
[1e3]      float(): 1000.0
[.5]       float(): 0.5
[ 12]      float(): 12.0
[12 ]      float(): 12.0
[1,000]    float(): refused
[1_000]    float(): 1000.0
[nan]      float(): nan
[Infinity] float(): inf
[0x1Ap0]   float(): refused
[१२]       float(): 12.0
[]         float(): refused
[1e400]    float(): inf

Each reader follows rules of its own, and every row is a way for two programs to disagree about one file: a version of csvstats in Python built on float() would count a padded " 23.0" and a Devanagari १२ that the D version skips. A rule that is written down, and coded the same way in both, removes the question. It also makes cheap work of the common case: a cell such as Pune fails the rule at its first letter, without an exception.

Read each cell into the nearest double

A cell that passes the rule still has to become a double, and the report prints two decimals of that double, so the exact double matters. The next session reads the 10,000 cells 0.005, 0.015, … 99.995, each exactly halfway between two values of two decimals, once with to!double and once with C’s strtod, which D calls directly from core.stdc.stdlib:

// The 10,000 cells 0.005, 0.015, … 99.995, each exactly halfway between two values of two decimals. Each is read
// with to!double and with C's strtod and printed with two decimals, as csvstats prints; the SHA-256 of strtod's
// printed values is compared with Python's in the next command.
import core.stdc.stdlib : strtod;
import std.conv : to;
import std.digest : LetterCase, toHexString;
import std.digest.sha : sha256Of;
import std.format : format;
import std.stdio;
import std.string : toStringz;

void main()
{
    size_t differ;
    string printed;
    foreach (i; 0 .. 10_000)
    {
        immutable thousandths = 5 + 10 * i;
        immutable cell = format("%d.%03d", thousandths / 1000, thousandths % 1000);
        immutable byTo = format("%.2f", cell.to!double);
        immutable byStrtod = format("%.2f", strtod(cell.toStringz, null));
        if (byTo != byStrtod)
            differ++;
        printed ~= byStrtod ~ "\n";
    }
    writeln("real.sizeof = ", real.sizeof, ", double.sizeof = ", double.sizeof);
    writeln("cells from 0.005 to 99.995: 10000");
    writeln("printed differently by to!double and strtod: ", differ);
    writeln("SHA-256 of strtod's printed values: ", toHexString!(LetterCase.lower)(sha256Of(printed))[0 .. 16]);
    writefln("0.045 by to!double: %.20f, printed %.2f", "0.045".to!double, "0.045".to!double);
    writefln("0.045 by strtod:    %.20f, printed %.2f", strtod("0.045", null), strtod("0.045", null));
}

from session import run

# to!double against C's strtod on 10,000 cells that sit exactly halfway between two values of two decimals, and
# Python's float() on the same cells, compared through the SHA-256 of what each prints. ties.d also prints the sizes
# of D's real and double on this target.
run("""
cd digits
ldc2 -O ties.d
./ties
python3 ties.py
""")

$ cd digits
$ ldc2 -O ties.d
$ ./ties
real.sizeof = 8, double.sizeof = 8
cells from 0.005 to 99.995: 10000
printed differently by to!double and strtod: 3937
SHA-256 of strtod's printed values: 4adb942f88333594
0.045 by to!double: 0.04500000000000000527, printed 0.05
0.045 by strtod:    0.04499999999999999833, printed 0.04
$ python3 ties.py
SHA-256 of Python's printed values: 4adb942f88333594
0.045 by float():   0.04499999999999999833, printed 0.04

to!double and strtod gave different two-decimal results for 3,937 of the 10,000 cells. 0.045 shows why: no double is exactly 0.045, and the nearest one, 0.04499999999999999833, lies just below the tie, so it prints as 0.04. strtod returned that double, while to!double returned the next one up, 0.04500000000000000527, which prints as 0.05. Python’s float() read every cell as strtod did: the SHA-256 of its 10,000 printed values begins 4adb942f88333594, exactly as strtod’s does. So csvstats checks a cell with the rule and then reads it with strtod, and its Python twin, reading with float(), prints the same digits.

The consequences show in halves.csv: 1.005 and 2.675 printed as 1.00 and 2.67, because their nearest doubles lie just below the tie, and 0.125, which a double holds exactly, printed as 0.12, the even digit. That is a property of binary doubles, not of D: a spreadsheet that rounds the decimal text would print 1.01 and 2.68.

A million rows in under two seconds of processor time

The budget is 1,000,000 rows within 2 seconds of processor timeprocessor time: The time a processor spent running a program: user, in the program's own code, and sys, in the system's code on its behalf, as /usr/bin/time -p reports them. Unlike real, the time on the clock, it hardly grows when other programs keep the machine busy. on the machine that records the session. The session generates two files, of 100,000 and 1,000,000 rows, builds csvstats and the first reading loop of the tool, throwing.d, which sent every cell through to!double and caught the exception, and times each run with /usr/bin/time -p:

// The first reading loop of csvstats: every cell goes through to!double, and a cell that is not a number costs
// an exception, which the loop catches to count the cell as skipped.
import std.conv : to;
import std.csv : csvReader;
import std.file : readText;
import std.stdio;

void main(string[] args)
{
    auto records = csvReader!string(readText(args[1]), null);
    auto header = records.header;
    auto numbers = new double[][](header.length);
    auto skipped = new size_t[](header.length);
    foreach (record; records)
    {
        size_t column = 0;
        foreach (cell; record)
        {
            try
                numbers[column] ~= cell.to!double;
            catch (Exception)
                skipped[column]++;
            column++;
        }
    }
    foreach (i, name; header)
        writefln("%s: %s numbers, %s skipped", name, numbers[i].length, skipped[i]);
}

from session import run

# A release build of csvstats and of the first, throwing reading loop; generated files of 100,000 and 1,000,000 rows;
# then /usr/bin/time -p for each run: real is the time on the clock, user and sys the processor time of the program.
run("""
cd csvstats
dub build --build=release --quiet
cd ../speed
ldc2 -O gen.d
ldc2 -O --release throwing.d
./gen 100000 > small.csv
./gen 1000000 > big.csv
wc -l < big.csv
/usr/bin/time -p ./throwing small.csv
/usr/bin/time -p ../csvstats/csvstats small.csv
/usr/bin/time -p ../csvstats/csvstats big.csv
""")

$ cd csvstats
$ dub build --build=release --quiet
$ cd ../speed
$ ldc2 -O gen.d
$ ldc2 -O --release throwing.d
$ ./gen 100000 > small.csv
$ ./gen 1000000 > big.csv
$ wc -l < big.csv
 1000001
$ /usr/bin/time -p ./throwing small.csv
station: 0 numbers, 100000 skipped
hour: 100000 numbers, 0 skipped
celsius: 100000 numbers, 0 skipped
rain_mm: 100000 numbers, 0 skipped
note: 0 numbers, 100000 skipped
real 4.97
user 2.17
sys 0.03
$ /usr/bin/time -p ../csvstats/csvstats small.csv
station: no numbers, 100000 cells skipped
hour: count 100000, mean 11.50, median 11.00, min 0.00, max 23.00, skipped 0
celsius: count 100000, mean 27.45, median 27.40, min 15.00, max 39.90, skipped 0
rain_mm: count 100000, mean 19.71, median 19.53, min 0.00, max 39.99, skipped 0
note: no numbers, 100000 cells skipped
real 0.34
user 0.08
sys 0.00
$ /usr/bin/time -p ../csvstats/csvstats big.csv
station: no numbers, 1000000 cells skipped
hour: count 1000000, mean 11.50, median 11.00, min 0.00, max 23.00, skipped 0
celsius: count 1000000, mean 27.45, median 27.40, min 15.00, max 39.90, skipped 0
rain_mm: count 1000000, mean 19.71, median 19.52, min 0.00, max 39.99, skipped 0
note: no numbers, 1000000 cells skipped
real 2.21
user 0.83
sys 0.03

The first loop threw 200,000 exceptions for 100,000 rows, one for each station cell and one for each note cell. csvstats did all of that loop’s other work, and more, in 0.08 s of processor time for the same rows, so the exceptions took nearly all of the 2.20 s: about (2.20 − 0.08) ÷ 200,000 ≈ 10.6 µs each. For the million rows csvstats needed 0.86 s, inside the budget. The clock said more, 2.21 s, because the machine that recorded the session was busy with other work; processor time counts only the program’s own work, which is why the budget uses it.

Practise: csvstats again, in Python

A second implementation written from the specification is the best test oracletest oracle: A second, trusted way to work out the expected results of tests, such as a short program written straight from the specification, used to check an implementation on many inputs. for the first: wherever the two disagree, one of them is wrong, or the specification is unclear. The lab builds csvstats’ report in Python in three steps, the number rule, the statistics and the report, and every sample test is a line that the D tool printed in this lesson’s sessions:

def is_number(cell):
    """True when csvstats counts the cell as a number."""
    try:
        float(cell)
        return True
    except ValueError:
        return False


def to_number(cell):
    """The cell's value, or None when csvstats skips it."""
    return float(cell) if is_number(cell) else None

from cells import is_number, to_number

COUNTED = ["12", "-0.25", "1e3", ".5"]
SKIPPED = [" 12", "12 ", "1,000", "1_000", "nan", "Infinity", "0x1Ap0", "१२", "", "1e400"]


def test_the_cells_csvstats_counts():
    """the four cells that cellrules printed as number"""
    assert [is_number(c) for c in COUNTED] == [True] * 4
    assert [to_number(c) for c in COUNTED] == [12.0, -0.25, 1000.0, 0.5]


def test_the_cells_csvstats_skips():
    """the ten cells that cellrules printed as skipped"""
    assert [to_number(c) for c in SKIPPED] == [None] * 10


def test_spaces_and_underscores_are_not_numbers():
    """float() accepts them; the rule does not"""
    assert not is_number(" 12") and not is_number("12 ") and not is_number("1_000")


def test_digits_of_other_scripts():
    """१२ is Devanagari twelve: float() reads it, csvstats skips it"""
    assert not is_number("१२")
    assert not is_number("١٢")


def test_the_rest_of_the_rule():
    """signs, a point on either side, and exponents with their digits"""
    assert all(is_number(c) for c in ["+7", "5.", "2.5E-3", "1e+3", "007"])
    assert not any(is_number(c) for c in ["-", ".", "+.", "1e", "e3", "1.2.3", "1e3.5", "--1"])


def test_too_large_for_a_float():
    """1e400 follows the rule, but its value does not fit"""
    assert is_number("1e400")
    assert to_number("1e400") is None
    assert to_number("1e308") == 1e308

import statistics


def summarise(values):
    """(count, mean, median, smallest, largest) of a column's numbers, as stats.d computes them."""
    values.sort()
    n = len(values)
    median = values[n // 2]
    return (n, statistics.fmean(values), median, values[0], values[-1])


def two(value):
    """The value as csvstats prints it."""
    return str(round(value, 2))

from summary import summarise, two

CELSIUS = [21.5, 32.5, 24.0, 38.5, 19.5, 29.0, 36.0]     # weather.csv, the 7 cells csvstats counted
RAIN = [0.0, 0.0, 1.25, 0.0, 4.75, 0.5]                  # weather.csv, the 6 cells it counted


def printed(values):
    count, mean, median, smallest, largest = summarise(values)
    return (count, two(mean), two(median), two(smallest), two(largest))


def test_celsius():
    """csvstats: celsius: count 7, mean 28.71, median 29.00, min 19.50, max 38.50"""
    assert printed(CELSIUS) == (7, "28.71", "29.00", "19.50", "38.50")


def test_rain_has_an_even_count():
    """csvstats: rain_mm: count 6, mean 1.08, median 0.25, min 0.00, max 4.75"""
    assert printed(RAIN) == (6, "1.08", "0.25", "0.00", "4.75")


def test_halves_print_from_the_nearest_double():
    """csvstats: maths 0.12 everywhere; physics mean 1.84, min 1.00, max 2.67"""
    assert printed([0.125, 0.125]) == (2, "0.12", "0.12", "0.12", "0.12")
    assert printed([1.005, 2.675]) == (2, "1.84", "1.84", "1.00", "2.67")


def test_the_callers_list_is_left_alone():
    """summarise sorts a copy, as stats.d does with .dup"""
    values = list(CELSIUS)
    summarise(values)
    assert values == CELSIUS


def test_the_mean_adds_in_file_order():
    """0.1 + 0.2 + 0.3 in this order, divided by 3"""
    count, mean, median, smallest, largest = summarise([0.1, 0.2, 0.3])
    assert mean == (0.1 + 0.2 + 0.3) / 3
    assert (count, median, smallest, largest) == (3, 0.2, 0.1, 0.3)


def test_one_value():
    """a column with a single number"""
    assert printed([7.5]) == (1, "7.50", "7.50", "7.50", "7.50")

import statistics


def to_number(cell):
    """Step 1: the cell's value, or None when csvstats skips it."""
    try:
        return float(cell)
    except ValueError:
        return None


def summarise(values):
    """Step 2: (count, mean, median, smallest, largest)."""
    values.sort()
    n = len(values)
    return (n, statistics.fmean(values), values[n // 2], values[0], values[-1])


def report(text):
    """The lines csvstats prints for a CSV text with a header line."""
    lines = text.splitlines()
    header = lines[0].split(",")
    numbers = [[] for _ in header]
    skipped = [0 for _ in header]
    for line in lines[1:]:
        for i, cell in enumerate(line.split(",")):
            value = to_number(cell)
            if value is None:
                skipped[i] += 1
            else:
                numbers[i].append(value)
    out = []
    for i, name in enumerate(header):
        if not numbers[i]:
            out.append(f"{name}: no numbers, {skipped[i]} cells skipped")
        else:
            n, mean, median, lo, hi = summarise(numbers[i])
            out.append(f"{name}: count {n}, mean {mean:.2f}, median {median:.2f}, min {lo:.2f}, max {hi:.2f}, skipped {skipped[i]}")
    return out

from report import report

WEATHER = """station,hour,celsius,rain_mm,note
Pune,6,21.5,0.0,
Pune,12,32.5,0.0,clear
Nagpur,6,24.0,1.25,
Nagpur,12,38.5,0.0,hot
Nashik,6,19.5,n/a,sensor down
Nashik,12,29.0,4.75,
Solapur,6, 23.0,0.5,"rain, light"
Solapur,12,36.0,,
"""

HALVES = """name,maths,physics
Asha,0.125,1.005
Ravi,0.125,2.675
"""


def test_weather():
    """the five lines csvstats printed for weather.csv"""
    assert report(WEATHER) == [
        "station: no numbers, 8 cells skipped",
        "hour: count 8, mean 9.00, median 9.00, min 6.00, max 12.00, skipped 0",
        "celsius: count 7, mean 28.71, median 29.00, min 19.50, max 38.50, skipped 1",
        "rain_mm: count 6, mean 1.08, median 0.25, min 0.00, max 4.75, skipped 2",
        "note: no numbers, 8 cells skipped",
    ]


def test_halves():
    """the three lines csvstats printed for halves.csv"""
    assert report(HALVES) == [
        "name: no numbers, 2 cells skipped",
        "maths: count 2, mean 0.12, median 0.12, min 0.12, max 0.12, skipped 0",
        "physics: count 2, mean 1.84, median 1.84, min 1.00, max 2.67, skipped 0",
    ]


def test_a_header_without_records():
    """every column has no numbers and nothing skipped"""
    assert report("a,b\n") == ["a: no numbers, 0 cells skipped", "b: no numbers, 0 cells skipped"]


def test_the_rule_decides():
    """1_000, Infinity and a padded 7 are skipped; 1e3 counts"""
    assert report("x\n1_000\nInfinity\n 7\n1e3\n") == ["x: count 1, mean 1000.00, median 1000.00, min 1000.00, max 1000.00, skipped 3"]

Then the self-check, on the five ideas of the project. The idea your answers show least is asked first, and every answer comes with its reason:

Interview questions

Warm-up (fresher to mid-level): Why does csvstats test a cell with its own rule before converting it, instead of calling to!double and catching the exception?

For two reasons, correctness and cost. to!double has its own idea of a number: it accepted 1_000, 0x1Ap0 and nan and refused " 12", and Python’s float() would accept other cells again, so letting a parser decide makes the report depend on the language. A rule written in the specification and coded the same way in every version decides the same cells everywhere. The cost is the second reason: an exception for every word is expensive, and in the lesson the throwing loop spent 2.20 s of processor time on 100,000 rows, where csvstats needed 0.08 s, because a word fails the rule at its first letter. Exceptions stay for what should not happen, such as a missing file.

Two longer questions, on a report that a spreadsheet seems to contradict and on a tool that is slow on a big export:

Key takeaways

• A specification written before the code fixes the header order, the number rule, the five statistics, the two decimals and the exit statuses, so two programs written from it print the same bytes.
• csvstats keeps main small and puts the rule, the statistics and the report in modules with unittest blocks, which dub test ran: 3 modules passed unittests.
• A written rule decides what a number is: to!double and Python’s float() disagree on padded cells, Infinity, 0x1Ap0, Devanagari digits and 1e400, both accept 1_000 and nan, and the rule skips them all.
• The report prints two decimals of the nearest double: to!double differed from strtod on 3,937 of 10,000 tie cells, while strtod and Python’s float() agreed on all of them.
• An exception per text cell dominated the first loop’s time; with the rule, a million rows took 0.86 s of processor time, inside the budget of 2 s.