SPB Git

Repository Public

TableProfile.swift 11.2 KB 233 lines swift

1import Foundation2import SheetCore34public enum InferredColumnType: String, Sendable, Codable, CaseIterable {5    /// No values at all.6    case empty7    /// Numbers, at least one with a fractional part.8    case number9    /// Numbers that are all whole.10    case integer11    /// Numbers that all carry a date or time number format.12    case date13    case text14    case boolean15    /// More than one kind of value.16    case mixed17}1819/// Value-kind counts of a column and the type inferred from them.20public struct ColumnProfile: Sendable, Hashable, Codable {21    public var type: InferredColumnType22    public var numbers = 023    /// Numbers with a date/time number format (also counted in `numbers`).24    public var dates = 025    /// Whole numbers (also counted in `numbers`).26    public var integers = 027    public var texts = 028    /// Text cells that would parse as a number ("12", "3.5%"): numbers stored as text.29    public var numericTexts = 030    public var booleans = 031    public var errors = 032    public var blanks = 033    /// Share of the most common kind (number / text / boolean) among the typed cells; 1 for a clean column.34    public var dominantShare = 0.035    /// The most common kind when `type` is `mixed` (otherwise equal to `type`).36    public var dominantType: InferredColumnType37}3839/// Missing or unusable entries of one column.40public struct MissingValues: Sendable, Hashable, Codable {41    /// Zero-based offset of the column inside the analysed range.42    public var column: Int43    /// Sheet rows that are empty (or hold empty / whitespace-only text).44    public var blankRows: [Int]45    /// Sheet rows holding a placeholder such as "N/A", "-" or "null".46    public var placeholderRows: [Int]47    /// Sheet rows holding an error value.48    public var errorRows: [Int]49    public var total: Int { blankRows.count + placeholderRows.count + errorRows.count }50}5152/// Descriptive summary of a column. Numeric fields are nil when the column has no numbers.53public struct ColumnSummary: Sendable, Hashable, Codable {54    public var name: String55    public var type: InferredColumnType56    /// Non-blank cells.57    public var count: Int58    public var blanks: Int59    public var errors: Int60    /// Distinct non-blank values (text case-insensitive).61    public var distinct: Int62    public var numericCount: Int63    public var sum: Double?64    public var mean: Double?65    public var min: Double?66    public var max: Double?67    /// Median of the numeric cells (mean of the two middle values for an even count).68    public var median: Double?69    /// Sample standard deviation (n − 1) of the numeric cells; nil for fewer than two.70    public var standardDeviation: Double?71    /// Shortest and longest text length among text cells.72    public var minLength: Int?73    public var maxLength: Int?74    /// Most frequent non-blank value as text (ties: the one met first) and its count.75    public var mostFrequent: String?76    public var mostFrequentCount: Int77}7879extension TableOps {80    /// Placeholders treated as missing by default (compared case-insensitively after trimming).81    public static let defaultMissingPlaceholders: Set<String> = ["n/a", "na", "#n/a", "null", "none", "nan", "-", "--", "?", "."]8283    /// True for number formats that display dates or times ("yyyy-mm-dd", "d-mmm", "h:mm"…).84    /// Quoted text, bracketed sections and escaped characters are ignored.85    public static func isDateFormat(_ format: String) -> Bool {86        var stripped = ""87        var inQuote = false, inBracket = false, skip = false88        for ch in format {89            if skip { skip = false; continue }90            if inQuote { if ch == "\"" { inQuote = false }; continue }91            if inBracket {92                if ch == "]" { inBracket = false }93                continue94            }95            switch ch {96            case "\"": inQuote = true97            case "[": inBracket = true98            case "\\", "_", "*": skip = true99            default: stripped.append(ch)100            }101        }102        let s = stripped.lowercased()103        if s.contains("general") || s.isEmpty { return false }104        if s.contains(where: { "0#?".contains($0) }) && !s.contains(where: { "ydhs".contains($0) }) { return false }105        // elapsed-time formats such as [h]:mm lose their bracket above but keep the minutes106        if format.lowercased().contains("[h]") || format.lowercased().contains("[m]") || format.lowercased().contains("[s]") { return true }107        return s.contains(where: { "ydhsm".contains($0) })108    }109110    /// Counts value kinds in one column of `range` (data rows only) and infers its type:111    /// all numbers → `date` if every one has a date format, else `integer` if all whole, else `number`;112    /// all text → `text`; all booleans → `boolean`; nothing → `empty`; otherwise `mixed`.113    /// Blanks and errors do not affect the type.114    public static func inferType(workbook: Workbook, sheet si: Int, range: RangeRef, column: Int) -> ColumnProfile {115        var p = ColumnProfile(type: .empty, dominantType: .empty)116        guard si >= 0, si < workbook.sheets.count, column >= 0, column < range.cols else { return p }117        let sheet = workbook.sheets[si]118        let col = range.c0 + column119        var formatIsDate: [Int32: Bool] = [:]120        for r in range.r0...range.r1 {121            switch sheet.value(r, col) {122            case .number(let n):123                p.numbers += 1124                if n == n.rounded() { p.integers += 1 }125                let sid = sheet.styleID(r, col)126                let isDate = formatIsDate[sid] ?? isDateFormat(workbook.styles[sid].numberFormat)127                formatIsDate[sid] = isDate128                if isDate { p.dates += 1 }129            case .text(let s):130                if s.trimmingCharacters(in: .whitespaces).isEmpty { p.blanks += 1; break }131                p.texts += 1132                if Value.parseNumber(s) != nil { p.numericTexts += 1 }133            case .bool: p.booleans += 1134            case .error: p.errors += 1135            default: p.blanks += 1136            }137        }138        let typed = p.numbers + p.texts + p.booleans139        guard typed > 0 else { return p }140        let numericType: InferredColumnType = p.dates == p.numbers ? .date : (p.integers == p.numbers ? .integer : .number)141        let ranked: [(InferredColumnType, Int)] = [(numericType, p.numbers), (.text, p.texts), (.boolean, p.booleans)]142        let top = ranked.max { $0.1 < $1.1 }!143        p.dominantType = top.0144        p.dominantShare = Double(top.1) / Double(typed)145        p.type = top.1 == typed ? top.0 : .mixed146        return p147    }148149    /// Missing-value report for every column of `range` (data rows only).150    public static func missingValues(sheet: Sheet, range: RangeRef, placeholders: Set<String> = defaultMissingPlaceholders) -> [MissingValues] {151        (0..<range.cols).map { k in152            var m = MissingValues(column: k, blankRows: [], placeholderRows: [], errorRows: [])153            for r in range.r0...range.r1 {154                switch sheet.value(r, range.c0 + k) {155                case .blank: m.blankRows.append(r)156                case .error: m.errorRows.append(r)157                case .text(let s):158                    let t = s.trimmingCharacters(in: .whitespaces).lowercased()159                    if t.isEmpty { m.blankRows.append(r) } else if placeholders.contains(t) { m.placeholderRows.append(r) }160                default: break161                }162            }163            return m164        }165    }166167    /// Summary of one column of `range` (data rows only).168    public static func summary(workbook: Workbook, sheet si: Int, range: RangeRef, column: Int, name: String) -> ColumnSummary {169        let profile = inferType(workbook: workbook, sheet: si, range: range, column: column)170        var s = ColumnSummary(name: name, type: profile.type, count: 0, blanks: profile.blanks, errors: profile.errors, distinct: 0,171                              numericCount: 0, mostFrequentCount: 0)172        guard si >= 0, si < workbook.sheets.count, column >= 0, column < range.cols else { return s }173        let sheet = workbook.sheets[si]174        var nums = [Double]()175        var counts: [String: (text: String, count: Int, first: Int)] = [:]176        for r in range.r0...range.r1 {177            let v = sheet.value(r, range.c0 + column)178            if case .text(let t) = v, t.trimmingCharacters(in: .whitespaces).isEmpty { continue }179            if v.isBlank { continue }180            s.count += 1181            if case .number(let n) = v { nums.append(n) }182            if case .text(let t) = v {183                s.minLength = Swift.min(s.minLength ?? t.count, t.count)184                s.maxLength = Swift.max(s.maxLength ?? t.count, t.count)185            }186            let key = AuditRuleSupport.key(v) ?? ("e" + AuditRuleSupport.display(v))187            counts[key, default: (AuditRuleSupport.display(v), 0, r)].count += 1188        }189        s.distinct = counts.count190        if let top = counts.values.max(by: { ($0.count, -$0.first) < ($1.count, -$1.first) }) {191            s.mostFrequent = top.text192            s.mostFrequentCount = top.count193        }194        s.numericCount = nums.count195        if !nums.isEmpty {196            nums.sort()197            let n = nums.count198            let total = nums.reduce(0, +)199            let mean = total / Double(n)200            s.sum = total; s.mean = mean; s.min = nums[0]; s.max = nums[n - 1]201            s.median = n % 2 == 1 ? nums[n / 2] : (nums[n / 2 - 1] + nums[n / 2]) / 2202            if n > 1 { s.standardDeviation = (nums.reduce(0) { $0 + ($1 - mean) * ($1 - mean) } / Double(n - 1)).squareRoot() }203        }204        return s205    }206207    /// Summaries of every column of a table (data rows only; empty when the table has none).208    public static func summaries(workbook: Workbook, sheet si: Int, table: TableDef) -> [ColumnSummary] {209        guard let data = table.dataRange else { return [] }210        return (0..<data.cols).map { k in211            summary(workbook: workbook, sheet: si, range: data, column: k,212                    name: k < table.columns.count ? table.columns[k].name : "Column\(k + 1)")213        }214    }215216    /// Lays summaries out as a report grid: one row per statistic, one column per table column.217    public static func summaryTable(_ summaries: [ColumnSummary]) -> [[Value]] {218        func num(_ d: Double?) -> Value { d.map { Value.num($0) } ?? .blank }219        func int(_ i: Int?) -> Value { i.map { .number(Double($0)) } ?? .blank }220        let rows: [(String, (ColumnSummary) -> Value)] = [221            ("Type", { .text($0.type.rawValue) }), ("Count", { int($0.count) }), ("Blanks", { int($0.blanks) }),222            ("Errors", { int($0.errors) }), ("Distinct", { int($0.distinct) }), ("Numeric count", { int($0.numericCount) }),223            ("Sum", { num($0.sum) }), ("Mean", { num($0.mean) }), ("Min", { num($0.min) }), ("Max", { num($0.max) }),224            ("Median", { num($0.median) }), ("Std. deviation (sample)", { num($0.standardDeviation) }),225            ("Min length", { int($0.minLength) }), ("Max length", { int($0.maxLength) }),226            ("Most frequent", { $0.mostFrequent.map { .text($0) } ?? .blank }), ("Most frequent count", { int($0.mostFrequentCount) }),227        ]228        var out: [[Value]] = [[.text("Statistic")] + summaries.map { .text($0.name) }]229        for (label, f) in rows { out.append([.text(label)] + summaries.map(f)) }230        return out231    }232}233
Files main
Menu

Theme