pandas and NumPy data handling

The goal is to calculate a weighted mean without silently changing precise source values or counting the weight of a missing observation.

Source and numerical contract

The complete dataframes.py example, together with common.py, creates these rows:

id

v: DECIMAL(18,8)

w: DOUBLE

1

0.125

1

2

0.5

3

3

NULL

2

SQLAlchemy connects through mysql+pymysql. The example builds the URL with URL.create, so special characters in the password are handled by the URL library. pandas.read_sql uses coerce_float=False to preserve DECIMAL objects while auditing source values.

It checks the raw Decimal and NULL values before explicitly constructing float64 numerical inputs. Use pd.isna/notna for mixed None/NaN inputs; np.isnan(None) is not a general missing-value check. Keep identifiers and timestamps out of lossy floating conversions when exact identity matters.

Pairwise-valid weights

Use the same valid (v,w) pairs for both numerator and denominator:

-- Expected-Value: 0.40625
SELECT SUM(v*w) / NULLIF(
    SUM(CASE WHEN v IS NOT NULL AND w IS NOT NULL THEN w END),0
) weighted_mean
FROM (
    SELECT CAST(0.125 AS DOUBLE) v,CAST(1 AS DOUBLE) w
    UNION ALL SELECT CAST(0.5 AS DOUBLE),CAST(3 AS DOUBLE)
    UNION ALL SELECT CAST(NULL AS DOUBLE),CAST(2 AS DOUBLE)
) observations;

The expected mean is (0.125 + 0.5*3) / 4 = 0.40625. Including the third row’s weight in the denominator would incorrectly produce 1.625 / 6.

The Python script independently calculates np.dot(x,w)/w.sum(), checks finite/nonnegative weights and compares the SQL result. A zero total weight returns NULL/None. Define the application’s policy for negative or nonfinite weights.

Precision choices

Choose the type according to the task. Monetary accounting may require DECIMAL arithmetic throughout; scientific calculations may require an explicit DOUBLE input and a stated numerical tolerance.

In the source validation, AVG(integer) on the tested MO build returned a scale-four DECIMAL while floating reference engines returned floating averages. If a feature requires floating calculation, cast the input, for example AVG(CAST(v AS DOUBLE)). Casting an already rounded average cannot recover its lost precision. Do not merely widen a comparison tolerance to hide this difference.

Transactional result publication

Run the complete example after the connection setup:

python dataframes.py

Expected output:

weighted_mean=0.40625; DECIMAL/NULL and commit/rollback readback verified

The script writes a deterministic result key, commits, then reads the full result through a separate connection. It also changes the result inside a transaction, rolls back and verifies the original value through that second connection.

A failed write with an unknown commit outcome needs a result key and a reconciliation strategy. Blindly retrying append-style publication can duplicate results; a successful transaction does not by itself define application-level replay semantics.