Columns¶
A column entry says what a column is and which unit it is in. Every key is optional. Columns sit under
datasets.<name>.columns, keyed by the column name as stored.
Together, the column names, each column's type (and physical_type) and unit, the columns' aliases and the
dataset's removed_columns are the dataset's schema. The types come from the data, so a tool can
compare two builds and flag a type change, a likely unit or scale change, or blanks that appear. What to declare for
each change, and what a tool detects on its own, is on Schema changes.
Example¶
dates_asof: # this dataset draws on two vendor sources
vendor_a: release_a
vendor_b: release_b
columns:
rent:
type: number
description: asking rent
currency: USD
per: year
as_of_sources: [vendor_a] # only vendor_a: its citation shows vendor_a's as-of date only
rent_growth: # no as_of_sources: cited with every source's as-of date
type: number
description: quarter-on-quarter rent growth
unit: decimal
area:
type: number
description: floor area
unit: "[sft_i]"
amount:
type: number
description: amount in the currency named in the column amount_unit
unit_from: amount_unit
amount_unit:
type: string
description: "ISO 4217 code of each row's amount, such as USD or EUR"
rent_old:
description: replaced
use_instead: rent
Type, unit and money¶
These are three different questions about a column:
| Question | Field | Applies to | Example |
|---|---|---|---|
| What kind of value is stored? | type (from the data today; see below) | every column | number, integer, string, date |
| What does a number measure? | unit (UCUM) |
numeric measures only | square feet, percent, a count of buildings |
| Which money, per which period? | currency + per |
monetary columns only | USD per year |
- Type says how to read the stored value, using the ODCS logical types. A tool fills it from the data on every
build and writes it into a new file, so you do not type it by hand; the card shows it in the
typecolumn. A declared type that disagrees with the latest build is a warning.physical_type(the storage type, such asint64orlarge_string) is optional: a producer may declare it, a tool keeps the build's value. A column of mixed values (text and numbers) has typeobject: store one type so readers can parse it. A column that is entirely blank has no type yet. - Unit says what a number means.
0.012stored as a number could be 1.2% (decimal) or 0.012% (%); only the unit tells them apart. Names, codes, ids, flags and dates have no unit: leaveunitout (auniton a non-numerictypeis a warning). - Money takes
currency(an ISO 4217 code such asUSD) and, for a flow or rate,per(the period): rent of USD per year iscurrency: USDandper: year. Never put a currency code inunit; that is an error. - Money per something else (USD per square foot per year) has no field yet: write
currency: USD,per: yearand say "per square foot" in the description.
Units (UCUM)¶
unit takes a code from UCUM, the Unified Code for Units of Measure, plus three short
aliases (decimal, pct, bp). A code is checked against the full UCUM table: an unknown symbol is an error, not
only bad syntax. [sqft] is not UCUM; the code is [sft_i].
Where to look a unit up:
- Common UCUM units: the short list most data needs.
- Full list of UCUM symbols: every unit and prefix in the standard, with its name. Customary units such as feet, acres and miles are under Customary Unit Atoms.
- UCUM validator: type a code to check it and see what it means.
How a code is built:
| Part | Means | Example |
|---|---|---|
| a unit symbol | one unit | m2 square metres, a year, mo month, d day |
[...] |
a customary (non-metric) unit | [sft_i] square foot, [acr_us] acre, [mi_i] mile |
10*n. |
times 10 to the power n: use it for thousands, millions | 10*3.[sft_i] thousand square feet |
. and / |
multiply and divide | 1/a per year, [sft_i]/{building} square feet per building |
{...} |
a label for what is counted; it does not change the number | {building} a count of buildings |
1 |
a pure number with no unit: a ratio or a count | 3 |
Common units¶
| Write | Means | Example value |
|---|---|---|
decimal |
A plain fraction (an alias of UCUM 1) |
0.012 is 1.2% |
pct or % |
Percent; both are accepted | 1.2 is 1.2% |
bp |
Basis points (an alias of UCUM 10*-4) |
120 is 1.2% |
1 |
A ratio or index with no unit | 1.04 |
{building} |
A count of buildings (any word in braces names what is counted) | 3 |
[sft_i] |
Square feet | 412.5 |
10*3.[sft_i] |
Thousands of square feet | 412.5 is 412,500 sq ft |
10*6.[sft_i] |
Millions of square feet | 1.2 is 1.2 million sq ft |
m2 |
Square metres | 38.3 |
[acr_us] |
Acres | 12 |
a |
Years (a duration, such as a lease term) | 7.5 |
mo |
Months (a duration) | 18 |
Codes with brackets, braces or * must be quoted in YAML: unit: "[sft_i]", unit: "{building}".
Definitions¶
| Key | Type | Required | Priority | Check | Description |
|---|---|---|---|---|---|
columns.<name> |
map of name to Column | no | Critical | error if the column is not in the data | The key is the column name as stored. |
description |
string | no | Critical | prose lint (warning) | What the column is. Say what it is; put "what not to do" in do_not. |
type |
string, integer, number, boolean, date, timestamp, time, object or array |
no | Useful | error unless one of the ODCS logicalType values; warning if it disagrees with the latest build | How the value is stored (an ODCS logical type). A tool fills it from the data. |
physical_type |
string | no | None | - | The storage type as the engine names it, such as int64, large_string or timestamp[us, tz=UTC]. A tool keeps the build's value; a producer may declare it. |
unit |
string, UCUM | no | Critical | error if not a known UCUM unit (unknown symbol or syntax; a tool may report it as a warning while publishing); warning if the column's type is not numeric; error if it is a three-letter currency code; error if set together with unit_from |
The unit. The aliases pct (%), bp (10*-4) and decimal (1) are accepted and expanded. |
currency |
string | no | Critical | - | ISO 4217 code for a monetary column. Use this, not unit, for currencies. |
per |
day, week, month, quarter, year |
no | Critical | error on any other value | The period a flow or rate is per, for example rent per year. |
unit_from |
column name | no | Critical | error if set together with unit; error if the column is not in the data |
Another column holds the unit of each row: a UCUM code, or an ISO 4217 code for money. Declare that column too. |
use_instead |
column name, or <project>/<Dataset>#<column> |
no | Important | error if a / form is not a valid id; a plain name must be in the data |
The column to use instead of this one. |
as_of_sources |
list of source names | no | Useful | error if a name is not a key of the dataset's dates_asof |
The as-of sources this column derives from, so its citation lists only those. Leave it out when the column draws on every source in the dataset's dates_asof (the default); set it only when a dataset mixes sources and this column comes from some of them. |
aliases |
list of string or object (name, until) |
no | Important | error unless each name uses only letters, digits and _ and does not start with a digit; error if a name is in the dataset's removed_columns; error if a name is a current column of the dataset (a tool may report this one as a warning while publishing) |
Former names of this column. A citation or lookup by a former name reads this column, and the card says "formerly". |