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)