IVYXSTUDIO · IVY NODE

Table Clean

ivy.node.table-clean · v0.1.0

ivyx✓

Cleans rows for analysis: trims text, drops empty rows, turns number columns into numbers and date columns into ISO dates. Returns the cleaned rows, how many were dropped and every value it could not convert.

#data#clean#table

Inputs

Field	Type	Description
rowsrequired	array	The rows to clean.
number_columns	array	Columns to turn into numbers; currency signs and thousands separators are removed.
date_columns	array	Columns to turn into YYYY-MM-DD dates.

Outputs

Field	Type	Description
rowsrequired	array	The cleaned rows.
droppedrequired	integer	Rows removed because every cell was empty.
problemsrequired	array	Values that could not be converted, as {row, column, value}; the cell is set to null.

Source

python

inp = __ivy_ctx__["nodes"][__ivy_node_id__]["input"]

import re
from datetime import datetime

rows = inp["rows"]
number_columns = inp.get("number_columns", [])
date_columns = inp.get("date_columns", [])
date_formats = ["%Y-%m-%d", "%d.%m.%Y", "%d/%m/%Y", "%m/%d/%Y", "%Y/%m/%d", "%Y-%m-%dT%H:%M:%S"]


def to_number(value):
    if isinstance(value, (int, float)) and not isinstance(value, bool):
        return value
    text = re.sub(r"[^0-9,.\-]", "", str(value))
    # 1.234,56 and 1,234.56 both mean one thousand two hundred.
    if "," in text and "." in text:
        text = text.replace(".", "").replace(",", ".") if text.rfind(",") > text.rfind(".") else text.replace(",", "")
    elif "," in text:
        text = text.replace(",", ".")
    return float(text)


def to_date(value):
    text = str(value).strip()[:19]
    for fmt in date_formats:
        try:
            return datetime.strptime(text[:len(datetime(2000, 1, 1).strftime(fmt))], fmt).strftime("%Y-%m-%d")
        except ValueError:
            continue
    raise ValueError(text)


cleaned, problems, dropped = [], [], 0
for index, row in enumerate(rows):
    row = {k: (v.strip() if isinstance(v, str) else v) for k, v in row.items()}
    if all(v is None or v == "" for v in row.values()):
        dropped += 1
        continue
    for column in number_columns:
        if row.get(column) not in (None, ""):
            try:
                row[column] = to_number(row[column])
            except ValueError:
                problems.append({"row": index, "column": column, "value": row[column]})
                row[column] = None
    for column in date_columns:
        if row.get(column) not in (None, ""):
            try:
                row[column] = to_date(row[column])
            except ValueError:
                problems.append({"row": index, "column": column, "value": row[column]})
                row[column] = None
    cleaned.append(row)

out = __ivy_ctx__["nodes"][__ivy_node_id__]["output"]
out["rows"] = cleaned
out["dropped"] = dropped
out["problems"] = problems

Tests

Requires: python:3.9

cleans
Text is trimmed, money and dates converted, an empty row dropped.
reports-bad-values
A value that is not a date is reported and set to null.