Tables
Table, the rows every OAX and SQL step gives and every method takes: columns, rows, filters, joins and sorts, each giving a new table and never changing the one it started from. Also made-up tables to try a method on.
Table
public sealed class Table : IEnumerable<Row>A table of rows and named columns, kept in memory by column. An OAX or SQL step gives one; C# steps join, filter and add columns, and LightGbm learns from it. Columns are found by name, ignoring case; an OAX result's Customer[CustomerKey] is also found as CustomerKey.
A name gives a column, a number a row: t["Score"] is the column Score (its values by row: t["Score"][0]); t[0] is the first row, the same as t.Rows[0] (its values by column: t[0]["Score"], t[0].Number("Score")). Table.Count is how many rows; Table.Columns the column names (Table.ColumnList the columns themselves).
A table never changes: rows, columns and Table.Rows are views of it, and every method that filters, orders, adds or joins (Where, Head, Take, OrderBy, With, Select, Join…) gives a new table. LINQ over its rows (t.Rows.Where(…), t.OrderBy(r => …)) gives rows; .ToTable() makes them a table again.
Constructors
Its constructors: Table(IEnumerable<Column> columns).
Properties
DaxQuery? Query { get; }- The OAX step that made it (its query), so a model version can keep the query and score with it.
int Count { get; }- How many rows.
ColumnSet Columns { get; }- The column names (text), in order, with
ColumnSet.Withoutfor choosing features. The columns themselves:Table.ColumnList, ort["name"]. IReadOnlyList<Column> ColumnList { get; }- The columns themselves, in order (their names:
Table.Columns). RowList Rows { get; }- The rows, a view (nothing is copied):
t.Rows[0]is the first row (the same ast[0]),t.Rows.Counthow many.
Also: static Table Empty { get; }.
Indexers
Column this[string name] { get; }- A column, by name (ignoring case;
Customer[CustomerKey]also asCustomerKey). A row ist[0], by number. Row this[int row] { get; }- A row, by its position: 0 is the first,
Count - 1the last (the same ast.Rows[row]). A column ist["name"], by name.
Methods
Table With(Column column)- A copy with this column added (or replaced, when the name is there).
Table With(string name, IEnumerable<double> values)Table With(string name, IEnumerable<string?> values)Table With(string name, IEnumerable<DateTime?> values)- A copy with a column of these values (one per row).
Table With(string name, Func<Row, object?> value)- A copy with a column computed for each row.
Table With(string name, object? constant)- A copy with a column of the same value in every row (a number, text, date or true/false).
Table Where(Func<Row, bool> keep)- The rows that keep says to keep, as a new table.
Table Head(int n = 10)- The first n rows, as a new table (all of them when there are fewer).
Table Take(int count)- The first
countrows, as a new table: the same asTable.Head(a table, where LINQ's Take gives rows). Table Skip(int count)- The rows after the first
count, as a new table (a table, where LINQ's Skip gives rows). Table Tail(int n = 10)- The last n rows, as a new table.
Table OrderBy(string column, bool descending = false)- Sorted by a column (missing last; the same order for equal values), as a new table.
Table Sample(int n, int seed = 42)- n rows picked at random (the same ones for the same seed).
Table Take(IReadOnlyList<int> rows)- The rows at these positions (0 is the first), in this order, as a new table; -1 gives a row of missing values.
Table Take(IEnumerable<int> rows)- The rows at these positions (0 is the first), in this order, as a new table.
Table LeftJoin(Table right, params string[] keys)- Every row of this table with the matching rows of the other (by the key columns); missing where none match.
Table Join(Table right, params string[] keys)- The rows of both tables that match on the key columns.
static Table Concat(IEnumerable<Table> tables)static Table Concat(params Table[] tables)- The rows of all the tables, one after the other; a column missing from a table is missing in its rows. A column's kind is that of the parts with a value: a table with no rows (or none but missing values) says nothing of it - With(name, r => …) on no rows cannot know what the function gives -, true/false beside numbers are numbers (1 and 0), and only text beside another kind makes the column text.
static Table FromRecords(IEnumerable<object?> records)- A table from records - anonymous objects (new { name = …, value = … }), records, classes, tuples or dictionaries - a column per property in the first record's order, each numbers, text, dates or true/false as its values are.
static Column FromObjects(string name, IReadOnlyList<object?> values)- A column from values of any kind: numbers, text, dates or true/false (mixed: text).
Also: bool Has(string name), int IndexOf(string name), Table Without(params string[] names), Table Select(params string[] names), Table Rename(string from, string to), IEnumerator<Row> GetEnumerator(), string ToString().
Operators
static implicit operator DaxQuery?(Table t)- An OAX step's table as its query (for Models.Save(features: …)); null for other tables.
Column
public sealed class ColumnA column of a Table (t["Score"]): its values, read-only, by row position (t["Score"][0], or typed: Number(i), Text(i), Date(i), Bool(i)). Numbers, dates and true/false are kept as doubles (dates as OLE automation dates; NaN is missing), text as strings (null is missing). A column converts to double[] (a copy), so it goes straight into Metrics and charts.
Properties
string? Source { get; }- The name it came with from OAX (Customer[CustomerKey]), when that differs from
Column.Name.
Also: string Name { get; }, ColumnKind Kind { get; }, int Count { get; }, bool IsText { get; }, int Missing { get; }.
Indexers
object? this[int row] { get; }- The value at a row position (0 is the first row), as it is: a double, string, DateTime or bool (null when missing). Typed:
Column.Number,Column.Text,Column.Date,Column.Bool.
Methods
double Number(int row)- The value as a number: dates as OLE dates, true as 1; NaN when missing (or text that is not a number).
double[] ToNumbers()- The values as numbers (a copy: changing it leaves the table as it is): dates as OLE dates, true/false as 1/0, missing as NaN.
IReadOnlyList<string> Distinct()- The different values (text as it is, others as text), in order of first appearance.
Also: static Column Numbers(string name, IEnumerable<double> values), static Column Texts(string name, IEnumerable<string?> values), static Column Dates(string name, IEnumerable<DateTime?> values), static Column Booleans(string name, IEnumerable<bool?> values), bool IsMissing(int row), string? Text(int row), DateTime? Date(int row), bool? Bool(int row), Column Rename(string name), double Sum(), double Mean(), double Min(), double Max(), string ToString().
Operators
Its operators: static implicit operator double[](Column c).
ColumnKind
public enum ColumnKindWhat a column holds.
Values
Its values: Number = 0, Text = 1, Date = 2, Boolean = 3.
ColumnSet
public sealed class ColumnSet : IReadOnlyList<string>A table's column names, with ColumnSet.Without for choosing features.
Constructors
ColumnSet(IReadOnlyList<string> names)- A table's column names, with
ColumnSet.Withoutfor choosing features.
Properties
Its properties: int Count { get; }.
Indexers
Its indexers: string this[int index] { get; }.
Methods
ColumnSet Without(params string[] drop)- These names but the given ones (matched like a table matches names).
Also: bool Contains(string name), IEnumerator<string> GetEnumerator(), string ToString().
Row
public readonly struct RowOne row of a table (t[0], t.Rows[0], each row of foreach (var r in t.Rows), Table.Where and Table.With): a view of the table at a row position, nothing copied. Its values by column name: r["Name"] as it is, or typed: r.Number("Score"), r.Text(…), r.Date(…), r.Bool(…).
Properties
int Index { get; }- Its position in the table: 0 for the first row.
Table Table { get; }- The table it is a row of.
Indexers
object? this[string column] { get; }- The value of a column (by name) in this row, as it is: a double, string, DateTime or bool (null when missing).
Methods
double Num(string column)- The value as a number (NaN when missing): the same as
Row.Number. double Number(string column)- The value as a number: dates as OLE dates, true as 1; NaN when missing (or text that is not a number).
DateTime Date(string column)- The value as a date; an error when it is missing (
Row.DateOrNullgives null instead). bool Bool(string column)- The value as true/false: false when missing.
Also: string? Text(string column), DateTime? DateOrNull(string column), bool IsMissing(string column), string ToString().
RowList
public sealed class RowList : IReadOnlyList<Row>A table's rows (Table.Rows), a view: nothing is copied. t.Rows[0] is the first row (as t[0]), t.Rows.Count how many; LINQ over them gives rows, which .ToTable() makes a table again.
Properties
Its properties: int Count { get; }.
Indexers
Row this[int index] { get; }- A row, by its position: 0 is the first.
Methods
Its methods: IEnumerator<Row> GetEnumerator(), string ToString().
RowExtensions
public static class RowExtensionsRows back into a table, after LINQ.
Methods
static Table ToTable(this IEnumerable<Row> rows)- Rows of one table (what LINQ gives:
t.OrderBy(r => r.Text("Name")).Take(5)) as a table again, in their order. Rows of several tables cannot be one table: Table.Concat puts tables one after the other.
Synthetic
public static class SyntheticMade-up tables to try a method on before pointing it at the model (the analytics catalog's recipes run on them): their truth is known, so a recipe shows whether a method finds it. Replace them with an OAX step's rows of the same shape for real work.
Methods
static Table Retention(int customers = 2000, int months = 12, int seed = 1)- A subscription business's monthly snapshots: per customer and month while they stay (as_of, the first of each month from 2024-01-01), what was known then (tenure_months, monthly_spend, days_since_last, support_tickets, plan, region, spend_before: the spend in the month before), a retention offer given at random to half the snapshots (treated: 1/0, an experiment run every month), whether they left in the 30 days after (churned) and, known only because the data is made up, how much the offer changed that chance (true_uplift: below 0, the offer kept them; above 0 it reminded content customers to leave). Spend drifts up in the later months. value: a year's spend.
static Table Contacts(int customers = 2000, int degree = 4, int seed = 1)- Customers who know each other (From, To: a referral or shared household network, about degree neighbours each, in tight groups of about 30): with Retention's customer names (the first customers of it), for network-effect recipes.