Repository Public
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