Skip to content

Formulas Examples

These are the bundled formula examples.

Add Left Cells

Adds the two cells to the left of the current cell.

import numpy as np
import moderncsv.formulas as mc

def add_left_cells():
    """
    Name: Add Left Cells
    Description: Adds the two cells to the left of the current cell.
    """

    a = mc.rc(0, -1)
    b = mc.rc(0, -2)
    return a + b

Convolution

Computes y[n] = sum_{i=0}^{M-1} x[i] * h[n - i]. Pads the front to ensure causality.

h is defined in the formula as the impulse response.

import numpy as np
import moderncsv.formulas as mc

def convolution():
    """
    Name: Convolution
    Description: Computes y[n] = sum_{i=0}^{M-1} x[i] * h[n - i]. Pads the front to ensure causality.
    """

    # Impulse response (normalized)
    h = np.array([3.2, 5.6, 1.7, 2.9, 7.5, 3.2])
    h /= mc.sum(h)
    M = len(h)

    # Input and output sizes
    x_num_samples = mc.num_rows()
    y_num_samples = x_num_samples - M + 1

    # Compute y
    y = np.zeros((y_num_samples))
    for i, k in enumerate(h[::-1]):
        x_curr = mc.col_rel(-1, r_start = mc.num_header_rows() + i, num_rows = y_num_samples)
        y += k * x_curr

    # Prepend with nans to make it causal.
    front = np.full(M - 1, None)
    y = np.concatenate((front, y))

    return y

Fill Total Row From First Column

Finds the row whose first column says Total, sums each column above it, and writes the totals into that row.

The local set_cells() helper is defined in the formula and calls mc.set_cell() for each write.

import numpy as np
import moderncsv.formulas as mc

@mc.single_run
def fill_total_row_from_first_column():
    """
    Name: Fill Total Row From First Column
    Description: Finds the row whose first column says Total, sums each column above it, and writes the totals into that Total row.
    """

    # Write a batch of cell updates using Visual indexes.
    def set_cells(cells):
        for row, col, value in cells:
            mc.set_cell(row, col, value, absolute_index_type = "visual")

    if mc.num_cols(include_headers=True) <= 0:
        return

    first_header_row = mc.first_row_index(include_headers = True, index_type = "visual")
    first_data_row = mc.first_row_index(include_headers = False, index_type = "visual")
    first_data_col = mc.first_col_index(include_headers = False, index_type = "visual")
    last_row = mc.last_row_index(index_type = "visual")
    last_col = mc.last_col_index(index_type = "visual")
    if first_data_row > last_row or first_data_col > last_col:
        return

    def is_nonempty(value):
        return str(value).strip() != ""

    def has_any_nonempty(values):
        return any(is_nonempty(value) for value in np.asarray(values).reshape(-1))

    # Sum only the numeric values in a column and ignore labels or blanks.
    def sum_numeric(values):
        total = 0.0
        found_numeric = False
        for value in np.asarray(values).reshape(-1):
            text = str(value).strip()
            if text == "":
                continue

            try:
                numeric = float(text)
            except Exception:
                continue

            if np.isnan(numeric):
                continue

            total += numeric
            found_numeric = True

        if not found_numeric:
            return ""

        return int(total) if float(total).is_integer() else total

    # Find the lowest Total row in the first data column.
    total_row = None
    first_col_values = mc.col(
        c = first_data_col,
        r_start = first_data_row,
        r_end = last_row,
        absolute_index_type = "visual",
    )
    for offset in range(len(first_col_values) - 1, -1, -1):
        value = first_col_values[offset]
        if str(value).strip().lower() == "total":
            total_row = first_data_row + offset
            break

    # If there is no Total row yet, append one after the last non-empty row.
    if total_row is None:
        total_row = first_data_row
        for row in range(last_row, first_data_row - 1, -1):
            if has_any_nonempty(
                mc.row(r = row, include_headers = True, absolute_index_type = "visual")
            ):
                total_row = row + 1
                break

    # Limit writes to columns that are actually used above the Total row.
    last_data_row = total_row - 1
    scan_last_row = max(first_header_row, last_data_row)
    last_used_col = first_data_col
    for col in range(last_col, first_data_col - 1, -1):
        if has_any_nonempty(
            mc.col(
                c = col,
                r_start = first_header_row,
                r_end = scan_last_row,
                absolute_index_type = "visual",
            )
        ):
            last_used_col = col
            break

    writes = [(total_row, first_data_col, "Total")]
    for col in range(first_data_col + 1, last_used_col + 1):
        writes.append(
            (
                total_row,
                col,
                sum_numeric(
                    mc.col(
                        c = col,
                        r_start = first_data_row,
                        r_end = last_data_row,
                        absolute_index_type = "visual",
                    )
                ),
            )
        )

    set_cells(writes)

Column Delta

Computes the delta between every sample in the column to the left.

import numpy as np
import moderncsv.formulas as mc

@mc.single_run
def column_delta():
    """
    Name: Column Delta
    Description: Computes the delta between every sample in the column to the left.
    """

    col = mc.col(dc = -1)
    delta = col[1:] - col[:-1]
    r = mc.first_row_index() + 1
    c = mc.ci()
    mc.set_cell(r, c, delta)