This module provides basic functions for interacting with tabular data on Wikimedia Commons.
cell
Returns the value of the cell at the given row index and column name.
Usage: {{#invoke:Tabular_data|cell|Page name.tab|output_row=Index of row to output|output_column=Name of column to output}}
A row index of 1 refers to the first row in the table. A row index of -1 refers to the last row in the table. It is an error to specify a row index of 0.
Examples
Latest death toll in c:Data:COVID-19 cases in Santa Clara County, California.tab (regardless of when the table was last updated):
{{#invoke:Tabular data|cell
|output_row=-1
|output_column=deaths
|COVID-19 cases in Santa Clara County, California.tab}}
NOBODY
lookup
Returns the value of the cell(s) in one or more output columns of the row matching the search key and column.
This function is reminiscent of LOOKUP() macros in popular spreadsheet applications, except that the search key must match exactly. (On the other hand, this means the table does not need to be sorted.)
Usage: {{#invoke:Tabular_data|lookup|Page name.tab|search_value=Value to find in column|search_column=Name of column to search in|output_column=Name of column to output|output_column2=Name of another column to output|output_columnn=…|output_format=String format to format the output}}
If multiple columns are output without an explicit string format, this function formats the output as a human-readable list.
Some may find {{Tabular query}} (which uses this module) an intuitive way to obtain cell data as it resembles a simple SQL query.
Parameters
|1=- Page name on Commons with extension but no namespace
|search_value=or|search_pattern=- Value to find or pattern to match in column
|search_column=- Name of column to search in
|occurrence=- Index of the match to output in case of multiple matching rows. A row index of
1refers to the first matching row. A row index of-1refers to the last matching row. It is an error to specify a row index of0. |output_column=or|output_column1=,|output_column2=, ...- Names of columns to output
|output_format=- String format to format the output
Examples
Total confirmed case count in c:Data:COVID-19 cases in Santa Clara County, California.tab on the day that the county issued a stay-at-home order:
{{#invoke:Tabular data|lookup
|search_column=date
|output_column=totalConfirmedCases
|search_value=2020-03-16
|COVID-19 cases in Santa Clara County, California.tab}}
NOBODY
The last day that a hundred or more patients with COVID-19 were in the hospital in c:Data:COVID-19 cases in Santa Clara County, California.tab:
{{#invoke:Tabular data|lookup
|search_pattern=%d%d%d
|search_column=hospitalized
|output_column=date
|occurrence=-1
|COVID-19 cases in Santa Clara County, California.tab}}
NOBODY
Total number of administrators on all Wikimedia wikis using c:Data:Wikipedia statistics/data.tab:
{{#invoke:Tabular data|lookup
|search_column=site
|output_column=admins
|search_value=total.all
|Wikipedia statistics/data.tab}}
NOBODY
Number of administrators and users on all Wikimedia wikis using c:Data:Wikipedia statistics/data.tab:
{{#invoke:Tabular data|lookup
|output_column=admins
|output_format=%d out of %d users are administrators
|search_column=site
|search_value=total.all
|output_column2=users
|Wikipedia statistics/data.tab}}
NOBODY
Note: Wikipedia statistics are shown as an illustration only. In practice, there is a high-performance module {{NUMBEROF}} to access Wikipedia statistics.
wikitable
Returns the entire data table as a (rather plain) table.
Usage: {{#invoke:Tabular_data|wikitable|Page name.tab}}
Examples
COVID-19 statistics in Santa Clara County, California
| |
|---|---|
{{#invoke:Tabular data|wikitable
|
COVID-19 cases in Santa Clara County, California.tab}} NOBODY |
Implementation notes
The implementation of this function incorporates {{n/a}} (to represent null values), {{yes}} (true), and {{no}} (false). The templates themselves cannot be reused because they are incompatible with the mw.html library, which builds the table using an HTML DOM instead of pure wikitext.
Internationalization
You can most likely port this template to a wiki in another language without making major modifications. The wikitable function automatically localizes the table's description, column titles, and license name into the wiki's content language. It also formats numbers according to the content language. However, you should localize the cells representing true, false, and null by changing the values in the messages, bgColors, and colors variables to match the wiki's own {{yes}}, {{no}}, and {{n/a}} templates, respectively.
local p = {}
local lang = mw.getContentLanguage()
local navbar = require("Module:Navbar")
local messages = {
["true"] = "Yes",
["false"] = "No",
null = "N/A",
}
local bgColors = {
["true"] = "#9f9",
["false"] = "#f99",
null = "#ececec",
}
local colors = {
null = "#2c2c2c",
}
function p._cell(args)
local data = args.data or mw.ext.data.get(args[1])
local rowIdx = tonumber(args.output_row)
local outputFormat = args.output_format
local outputColumnNames = {
args.output_column1 or args.output_column,
}
while args["output_column" .. #outputColumnNames + 1] do
table.insert(outputColumnNames, args["output_column" .. #outputColumnNames + 1])
end
local outputColumnIdxs = {}
local numOutputColumnIdxs = 0
for i, field in ipairs(data.schema.fields) do
for j, outputColumnName in ipairs(outputColumnNames) do
if field.name == outputColumnName then
outputColumnIdxs[outputColumnName] = i
numOutputColumnIdxs = numOutputColumnIdxs + 1
end
end
if numOutputColumnIdxs == #outputColumnNames then
break
end
end
if numOutputColumnIdxs < #outputColumnNames then
for i, outputColumnName in ipairs(outputColumnNames) do
assert(outputColumnIdxs[outputColumnName],
mw.ustring.format("Output column “%s” not found.", outputColumnName))
end
end
if rowIdx > 0 then
rowIdx = (rowIdx - 1) % #data.data + 1
elseif rowIdx < 0 then
rowIdx = rowIdx % #data.data + 1
else
error("0 is not a valid row index.")
end
local record = data.data[rowIdx]
if record ~= nil then
if outputFormat or numOutputColumnIdxs > 1 then
local values = {}
for i, columnName in ipairs(outputColumnNames) do
local columnIdx = outputColumnIdxs[columnName]
table.insert(values, record[columnIdx])
end
if outputFormat then
return mw.ustring.format(outputFormat, unpack(values))
else
return mw.text.listToText(values)
end
else
local columnIdx = outputColumnIdxs[outputColumnNames[1]]
return record[columnIdx]
end
end
end
--- Returns the value of the cell at the given row index and column name.
--- A row index of 1 refers to the first row in the table. A row index of -1
--- refers to the last row in the table. It is an error to specify a row index
--- of 0.
--- Usage: {{#invoke:Tabular data | cell | Table name | output_row = Index of row to output | output_column = Name of column to output }}
function p.cell(frame)
return p._cell(frame.args)
end
function p._lookup(args)
--local data = args.data or mw.ext.data.get(args[1])
local page = mw.text.trim(args[1]) -- "UN:Total population, both sexes combined.tab" -- set page name explicitly for testing
local data = args.data or mw.ext.data.get(page)
local searchValue = args.search_value
local searchPattern = args.search_pattern
local searchColumnName = args.search_column
local searchColumnIdx
for i, field in ipairs(data.schema.fields) do
if field.name == searchColumnName then
searchColumnIdx = i
end
if searchColumnIdx then
break
end
end
assert(searchColumnIdx, mw.ustring.format("Search column “%s” not found.", searchColumnName))
local occurrence = tonumber(args.occurrence) or 1
local numMatchingRecords = 0
for i = (occurrence < 0 and #data.data or 1),
(occurrence < 0 and 1 or #data.data),
(occurrence < 0 and -1 or 1) do
local record = data.data[i]
if (searchValue and record[searchColumnIdx] == searchValue) or
(searchPattern and mw.ustring.match(tostring(record[searchColumnIdx]), searchPattern)) then
numMatchingRecords = numMatchingRecords + 1
if numMatchingRecords == math.abs(occurrence) then
local args = mw.clone(args)
args.data = data
args.output_row = i
return p._cell(args)
end
end
end
end
function p._lookup2(args)
--local data = args.data or mw.ext.data.get(args[1])
local page = mw.text.trim(args[1]) -- "UN:Total population, both sexes combined.tab" -- set page name explicitly for testing
local data = args.data or mw.ext.data.get(page)
local searchValue = args.search_value
local searchPattern = args.search_pattern
local searchColumnName = args.search_column
local searchValue2 = args.search_value2
local searchPattern2 = args.search_pattern2
local searchColumnName2 = args.search_column2
local searchColumnIdx
for i, field in ipairs(data.schema.fields) do
if field.name == searchColumnName then
searchColumnIdx = i
end
if searchColumnIdx then
break
end
end
assert(searchColumnIdx, mw.ustring.format("Search column “%s” not found.", searchColumnName))
local searchColumnIdx2
for i, field in ipairs(data.schema.fields) do
if field.name == searchColumnName2 then
searchColumnIdx2 = i
end
if searchColumnIdx2 then
break
end
end
assert(searchColumnIdx2, mw.ustring.format("Search column “%s” not found.", searchColumnName2))
local occurrence = tonumber(args.occurrence) or 1
local numMatchingRecords = 0
for i = (occurrence < 0 and #data.data or 1),
(occurrence < 0 and 1 or #data.data),
(occurrence < 0 and -1 or 1) do
local record = data.data[i]
if (searchValue and tostring(record[searchColumnIdx]) == searchValue)
and (searchValue2 and tostring(record[searchColumnIdx2]) == searchValue2) then
-- or (searchPattern and mw.ustring.match(tostring(record[searchColumnIdx]), searchPattern)) then
if (1==1) then return data.data[i][3] end -- just return single occurence
numMatchingRecords = numMatchingRecords + 1
if numMatchingRecords == math.abs(occurrence) then
local args = mw.clone(args)
args.data = data
args.output_row = i
return p._cell(args)
end
end
end
end
--- Returns the value of the cell(s) in the given output column(s) of the row
--- matching the search key and column.
--- Reminiscent of LOOKUP() macros in popular spreadsheet applications, except
--- that the search key must match exactly. (On the other hand, this means the
--- table does not need to be sorted.)
--- Usage: {{#invoke: Tabular data | lookup | Table name | search_value = Value to find in column | search_pattern = Pattern to find in column | search_column = Name of column to search in | occurrence = 1-based index of the matching row to output | output_column = Name of column to output | output_column2 = Name of another column to output | … | output_format = String format to output the values in }}
function p.lookup(frame)
return p._lookup(frame.args)
end
--- As p.lookup() except requiring match of values in two columns
--- Usage: {{#invoke: Tabular data | lookup2 | Table name | search_value = Value to find in column | search_pattern = Pattern to find in column | search_column = Name of column to search in | search_value2 = Value to find in second column | search_pattern2 = Pattern to find in second column | search_column2 = Name of second column to search in | occurrence = 1-based index of the matching row to output | output_column = Name of column to output | output_column2 = Name of another column to output | … | output_format = String format to output the values in }}
function p.lookup2(frame)
--return p._lookup2(frame.args)
return p.lookup2_minimal(frame.args)
end
-- version for testing resources
function p.lookup2_minimal(args)
--local page = mw.text.trim(args[1]) -- "UN:Total population, both sexes combined.tab" -- set page name explicitly for testing
local data = args.data or mw.ext.data.get("UN:Total population, both sexes combined.tab" )
local searchValue = args.search_value
--local searchPattern = args.search_pattern
--local searchColumnName = args.search_column
local searchValue2 = args.search_value2
--local searchPattern2 = args.search_pattern2
--local searchColumnName2 = args.search_column2
local searchColumnIdx = 1
local searchColumnIdx2 = 2
--local occurrence = tonumber(args.occurrence) or 1
local numMatchingRecords = 0
for i = 1, #data.data, 1 do
local record = data.data[i]
if (searchValue and tostring(record[searchColumnIdx]) == searchValue)
and (searchValue2 and tostring(record[searchColumnIdx2]) == searchValue2) then
-- or (searchPattern and mw.ustring.match(tostring(record[searchColumnIdx]), searchPattern)) then
return data.data[i][3] -- just return single occurence
end
end
end
function p._wikitable(args)
local pageName = args[1]
local data = mw.ext.data.get(pageName)
local datatypes = {}
local htmlTable = mw.html.create("table")
:addClass("wikitable sortable")
htmlTable
:tag("caption")
:wikitext(navbar.navbar({
template = ":c:Data:" .. pageName,
mini = "y",
style = "float: right;",
"view", "edit",
}))
:wikitext(data.description)
local headerRow = htmlTable
:tag("tr")
for i, field in ipairs(data.schema.fields) do
headerRow
:tag("th")
:attr("scope", "col")
:attr("data-sort-type", datatypes[j] == "text" and "string" or datatypes[j])
:wikitext(field.title)
datatypes[i] = field.type
end
for i, record in ipairs(data.data) do
local row = htmlTable:tag("tr")
for j = 1, #data.schema.fields do
local cell = row:tag("td")
if record[j] then
local formattedData = record[j]
if datatypes[j] == "number" then
formattedData = lang:formatNum(formattedData)
cell:attr("align", "right")
elseif datatypes[j] == "boolean" then
cell
:addClass(record[j] and "table-yes" or "table-no")
:css({
background = record[j] and bgColors["true"] or bgColors["false"],
color = record[j] and colors["true"] or colors["false"],
["vertical-align"] = "middle",
["text-align"] = "center",
})
:wikitext(record[j] and messages["true"] or messages["false"])
end
cell:wikitext(formattedData)
else
cell
:addClass("mw-tabular-value-null")
:addClass("table-na")
:css({
background = bgColors.null,
color = colors.null,
["vertical-align"] = "middle",
["text-align"] = "center",
})
:wikitext(messages.null)
end
end
end
local footer = htmlTable
:tag("tr")
:tag("td")
:addClass("sortbottom")
:attr("colspan", #data.schema.fields)
footer:wikitext(data.sources)
footer:tag("br")
local licenseText = mw.message.new("Jsonconfig-license",
mw.ustring.format("[%s %s]", data.license.url, data.license.text))
footer
:tag("i")
:wikitext(tostring(licenseText))
return htmlTable
end
--- Returns a tabular data page as a wikitext table.
--- Usage: {{#invoke:Tabular data | wikitable | Table name }}
function p.wikitable(frame)
return p._wikitable(frame.args)
end
return p