1package functions23import (4 "strconv"56 "src.rest/cat/012/internal/value"7)89// Functions that compute arrays, which spill from the cell into the10// cells to its right and below. Whole columns cost what they hold: FILTER11// and SORT of A:A work on its rows holding data, the blank rows past12// them being one fill (Array.Fill).1314// arrayFormula is ARRAYFORMULA, which a range that is a cell's whole15// formula is read through.16var arrayFormula = &FuncDef{Name: "ARRAYFORMULA", Args: "array_formula", Desc: "Compute a formula over arrays: ranges read whole, and functions of one value applied to each entry", Min: 1, Max: 1,17 eval: func(args []Node, get lookup) Value { return evalArr(args[0], get) }, arrays: takesArrays, format: inherit}1819func init() {20 define(21 arrayFormula,22 &FuncDef{Name: "FILTER", Args: "range, condition1, [condition2, ...]", Desc: "The rows (or columns) of a range where every condition is true", Min: 2, Max: -1,23 eval: filter, arrays: takesArrays, format: inheritFrom(0)},24 &FuncDef{Name: "SORT", Args: "range, [sort_column], [is_ascending], [sort_column2, is_ascending2, ...]", Desc: "The rows of a range sorted by columns", Min: 1, Max: -1,25 eval: sortRows, arrays: takesArrays, format: inheritFrom(0)},26 &FuncDef{Name: "SORTN", Args: "range, [n], [display_ties_mode], [sort_column1, is_ascending1, ...]", Desc: "The first n rows of a range after sorting", Min: 1, Max: -1,27 eval: sortN, arrays: takesArrays, format: inheritFrom(0)},28 &FuncDef{Name: "UNIQUE", Args: "range, [by_column], [exactly_once]", Desc: "The distinct rows (or columns) of a range, in order", Min: 1, Max: 3,29 eval: unique, arrays: takesArrays, format: inheritFrom(0)},30 &FuncDef{Name: "SEQUENCE", Args: "rows, [columns], [start], [step]", Desc: "An array of numbers counting up from start by step", Min: 1, Max: 4,31 eval: sequence, arrays: takesArrays},32 &FuncDef{Name: "TRANSPOSE", Args: "array_or_range", Desc: "Rows as columns and columns as rows", Min: 1, Max: 1,33 eval: transpose, arrays: takesArrays, format: inheritFrom(0)},34 &FuncDef{Name: "FLATTEN", Args: "range1, [range2, ...]", Desc: "Every entry of ranges in one column, row by row", Min: 1, Max: -1,35 eval: flatten, arrays: takesArrays, format: inheritFrom(0)},36 &FuncDef{Name: "CHOOSECOLS", Args: "array, col_num1, [col_num2, ...]", Desc: "Columns of an array by position, negative from the end", Min: 2, Max: -1,37 eval: func(args []Node, get lookup) Value { return choose2(args, get, false) }, arrays: takesArrays, format: inheritFrom(0)},38 &FuncDef{Name: "CHOOSEROWS", Args: "array, row_num1, [row_num2, ...]", Desc: "Rows of an array by position, negative from the end", Min: 2, Max: -1,39 eval: func(args []Node, get lookup) Value { return choose2(args, get, true) }, arrays: takesArrays, format: inheritFrom(0)},40 )41}4243// Evaluators for the table above, in its order.4445func filter(args []Node, get lookup) Value {46 a, err := arrayArg(args[0], get)47 if err != nil {48 return *err49 }50 conds := make([]*Array, len(args)-1)51 for i, n := range args[1:] {52 if conds[i], err = arrayArg(n, get); err != nil {53 return *err54 }55 }56 byRow := conds[0].Cols == 1 && conds[0].Rows == a.Rows57 if !byRow && (conds[0].Rows != 1 || conds[0].Cols != a.Cols) {58 return value.ErrValue // FILTER has mismatched range sizes59 }60 if !byRow {61 a = a.transposed()62 for i, c := range conds {63 conds[i] = c.transposed()64 }65 }66 for _, c := range conds {67 if c.Rows != a.Rows || c.Cols != 1 {68 return value.ErrValue69 }70 }71 data := a.DRows72 for _, c := range conds {73 data = max(data, c.DRows)74 }75 keep, err := passing(conds, data)76 if err != nil {77 return *err78 }79 out := a.rows(keep)80 if a.Rows > data { // the rows past every data area: one fill81 if ok, err := passes1(conds, -1); err != nil {82 return *err83 } else if ok {84 out.Rows += a.Rows - data85 out.Fill = a.Fill86 }87 }88 if out.Rows == 0 {89 return value.ErrNA // no matches90 }91 if !byRow {92 out = out.transposed()93 }94 return get.arrayValue(out)95}9697// passing is the rows below data where every condition is true.98func passing(conds []*Array, data int) ([]int, *Value) {99 var keep []int100 for r := range data {101 ok, err := passes1(conds, r)102 if err != nil {103 return nil, err104 }105 if ok {106 keep = append(keep, r)107 }108 }109 return keep, nil110}111112// passes1 reports whether every condition is true in row r, or past113// their data with r < 0.114func passes1(conds []*Array, r int) (bool, *Value) {115 for _, c := range conds {116 v := c.Fill117 if r >= 0 {118 v = c.At(r, 0)119 }120 if v.Kind == value.Error {121 return false, errOf(v)122 }123 f, err := toNum(v)124 if err != nil {125 return false, err126 }127 if f == 0 {128 return false, nil129 }130 }131 return true, nil132}133134// rows is a new array of the rows of a at indexes, as wide as a and135// holding as many columns of data.136func (a *Array) rows(indexes []int) *Array {137 out := &Array{Rows: len(indexes), Cols: a.Cols, DRows: len(indexes), DCols: a.DCols, Fill: a.Fill}138 out.V = make([]Value, len(indexes)*a.DCols)139 for i, r := range indexes {140 for c := range a.DCols {141 out.Set(i, c, a.At(r, c))142 }143 }144 return out145}146147// transposed is a with rows and columns swapped.148func (a *Array) transposed() *Array {149 out := &Array{Rows: a.Cols, Cols: a.Rows, DRows: a.DCols, DCols: a.DRows, Fill: a.Fill, V: make([]Value, len(a.V))}150 for r := range a.DRows {151 for c := range a.DCols {152 out.Set(c, r, a.At(r, c))153 }154 }155 return out156}157158func sortRows(args []Node, get lookup) Value {159 a, err := arrayArg(args[0], get)160 if err != nil {161 return *err162 }163 keys, err := sortKeys(a, args, 1, get)164 if err != nil {165 return *err166 }167 return get.arrayValue(sortedData(a, keys))168}169170func sortN(args []Node, get lookup) Value {171 a, err := arrayArg(args[0], get)172 if err != nil {173 return *err174 }175 n, err := intArg(args, 1, 1, get)176 if err != nil {177 return *err178 }179 mode, err := intArg(args, 2, 0, get)180 if err != nil {181 return *err182 }183 if n < 0 || mode < 0 || mode > 3 {184 return value.ErrValue185 }186 keys, err := sortKeys(a, args, 3, get)187 if err != nil {188 return *err189 }190 sorted := sortedData(a, keys)191 sorted.Rows = sorted.DRows // rows past the data are blank: never among the first192 rows := topRows(sorted, keys, n, mode)193 if len(rows) == 0 {194 return value.ErrNA195 }196 return get.arrayValue(sorted.rows(rows))197}198199func unique(args []Node, get lookup) Value {200 a, err := arrayArg(args[0], get)201 if err != nil {202 return *err203 }204 byCol, err := boolArg(args, 1, false, get)205 if err != nil {206 return *err207 }208 once, err := boolArg(args, 2, false, get)209 if err != nil {210 return *err211 }212 if byCol {213 a = a.transposed()214 }215 var keep []int216 for _, g := range groupRows(a, min(a.DRows+1, a.Rows)) { // the data rows, and one blank row past them217 if once && (len(g) > 1 || g[0] == a.DRows && a.Rows > a.DRows+1) {218 continue // repeated, or the blank row, which repeats past the data219 }220 keep = append(keep, g[0])221 }222 if len(keep) == 0 {223 return value.ErrNA224 }225 out := a.rows(keep)226 if byCol {227 out = out.transposed()228 }229 return get.arrayValue(out)230}231232// groupRows groups the first n rows of a that UNIQUE takes to be the233// same, in the order each first comes: the rows of each group.234func groupRows(a *Array, n int) [][]int {235 if a.Cols == 1 { // a column's values are their own keys236 return groupBy(n, func(r int) Value { return uniqueValue(a.At(r, 0)) })237 }238 return groupBy(n, func(r int) string { return rowKey(a, r) })239}240241// groupBy groups rows 0..n-1 by key, in the order each key first comes.242func groupBy[K comparable](n int, key func(int) K) [][]int {243 var groups [][]int244 index := map[K]int{}245 for r := range n {246 k := key(r)247 i, found := index[k]248 if !found {249 i = len(groups)250 index[k] = i251 groups = append(groups, nil)252 }253 groups[i] = append(groups[i], r)254 }255 return groups256}257258// uniqueValue is v as UNIQUE compares it: empty text is a blank.259func uniqueValue(v Value) Value {260 if v.Kind == value.Text && v.Str == "" {261 return Value{}262 }263 return v264}265266// rowKey is a key equal for rows UNIQUE takes to be the same.267func rowKey(a *Array, r int) string {268 var b []byte269 for c := range a.Cols {270 v := uniqueValue(a.At(r, c))271 b = append(b, byte(v.Kind))272 if v.Kind == value.Text || v.Kind == value.Error {273 b = append(b, v.Str...)274 } else {275 b = strconv.AppendFloat(b, v.Num, 'g', -1, 64)276 }277 b = append(b, 0)278 }279 return string(b)280}281282func sequence(args []Node, get lookup) Value {283 rows, err := intArg(args, 0, 1, get)284 if err != nil {285 return *err286 }287 cols, err := intArg(args, 1, 1, get)288 if err != nil {289 return *err290 }291 start, err := optNum(args, 2, 1, get)292 if err != nil {293 return *err294 }295 step, err := optNum(args, 3, 1, get)296 if err != nil {297 return *err298 }299 switch {300 case rows < 1 || cols < 1:301 return value.ErrNum // as Sheets; Excel says #CALC!302 case tooBig(rows, cols):303 return value.ErrValue304 }305 out := NewArray(rows, cols)306 for i := range out.V {307 out.V[i] = num(start + float64(i)*step)308 }309 return get.arrayValue(out)310}311312func transpose(args []Node, get lookup) Value {313 a, err := arrayArg(args[0], get)314 if err != nil {315 return *err316 }317 return get.arrayValue(a.transposed())318}319320func flatten(args []Node, get lookup) Value {321 var vals []Value322 var fill Value323 extra := 0324 for i, n := range args {325 a, err := arrayArg(n, get)326 if err != nil {327 return *err328 }329 rows := a.Rows330 if i == len(args)-1 {331 rows = a.DRows // the rest is one fill332 extra, fill = (a.Rows-a.DRows)*a.Cols, a.Fill333 }334 if tooBig(len(vals)+rows, a.Cols) {335 return value.ErrValue336 }337 for r := range rows {338 for c := range a.Cols {339 vals = append(vals, a.At(r, c))340 }341 }342 }343 out := &Array{Rows: len(vals) + extra, Cols: 1, DRows: len(vals), DCols: 1, V: vals, Fill: fill}344 return get.arrayValue(out)345}346347// choose2 is CHOOSEROWS (rows) or CHOOSECOLS.348func choose2(args []Node, get lookup, rows bool) Value {349 a, err := arrayArg(args[0], get)350 if err != nil {351 return *err352 }353 if !rows {354 a = a.transposed()355 }356 var pick []int357 for i := 1; i < len(args); i++ {358 v, err := arrayArg(args[i], get)359 if err != nil {360 return *err361 }362 for _, x := range v.V {363 f, err := toNum(x)364 if err != nil {365 return *err366 }367 k := int(f)368 if k < 0 {369 k = a.Rows + k + 1370 }371 if k < 1 || k > a.Rows {372 return value.ErrValue373 }374 pick = append(pick, k-1)375 }376 }377 out := a.rows(pick)378 if !rows {379 out = out.transposed()380 }381 return get.arrayValue(out)382}