Difference between revisions of "Module:Tabular data"

From blackwiki
Jump to navigation Jump to search
blackwiki>Pppery
m (36 revisions imported)
 
(11 intermediate revisions by one other user not shown)
Line 19: Line 19:
 
}
 
}
  
--- Returns the value of the cell at the given row index and column name.
+
function p._cell(args)
--- A row index of 1 refers to the first row in the table. A row index of -1
+
local data = args.data or mw.ext.data.get(args[1])
--- refers to the last row in the table. It is an error to specify a row index
+
local rowIdx = tonumber(args.output_row)
--- of 0.
+
local outputFormat = args.output_format
--- 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)
+
local outputColumnNames = {
local data = mw.ext.data.get(frame.args[1])
+
args.output_column1 or args.output_column,
local rowIdx = tonumber(frame.args.output_row)
+
}
local outputColumnName = frame.args.output_column
+
while args["output_column" .. #outputColumnNames + 1] do
 +
table.insert(outputColumnNames, args["output_column" .. #outputColumnNames + 1])
 +
end
 
 
local outputColumnIdx
+
local outputColumnIdxs = {}
 +
local numOutputColumnIdxs = 0
 
for i, field in ipairs(data.schema.fields) do
 
for i, field in ipairs(data.schema.fields) do
if field.name == outputColumnName then
+
for j, outputColumnName in ipairs(outputColumnNames) do
outputColumnIdx = i
+
if field.name == outputColumnName then
 +
outputColumnIdxs[outputColumnName] = i
 +
numOutputColumnIdxs = numOutputColumnIdxs + 1
 +
end
 +
end
 +
if numOutputColumnIdxs == #outputColumnNames then
 
break
 
break
 
end
 
end
 
end
 
end
assert(outputColumnIdx, mw.ustring.format("Output column “%s” not found.", outputColumnName))
+
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
 
if rowIdx > 0 then
Line 46: Line 59:
 
end
 
end
 
 
return data.data[rowIdx][outputColumnIdx]
+
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
 
end
  
--- Returns the value of the cell in the given output column of the row matching
+
--- Returns the value of the cell at the given row index and column name.
--- the search key and column.
+
--- A row index of 1 refers to the first row in the table. A row index of -1
--- Reminiscent of LOOKUP() macros in popular spreadsheet applications, except
+
--- refers to the last row in the table. It is an error to specify a row index
--- that the search key must match exactly. (On the other hand, this means the
+
--- of 0.
--- table does not need to be sorted.)
+
--- Usage: {{#invoke:Tabular data | cell | Table name | output_row = Index of row to output | output_column = Name of column to output }}
--- Usage: {{#invoke: Tabular data | lookup | Table name | search_value = Value to find in column | search_column = Name of column to search in | output_column = Name of column to output }}
+
function p.cell(frame)
function p.lookup(frame)
+
return p._cell(frame.args)
local data = mw.ext.data.get(frame.args[1])
+
end
local searchValue = frame.args.search_value
+
 
local searchColumnName = frame.args.search_column
+
function p._lookup(args)
local outputColumnName = frame.args.output_column
+
local data = args.data or mw.ext.data.get(args[1])
 +
local searchValue = args.search_value
 +
local searchPattern = args.search_pattern
 +
local searchColumnName = args.search_column
 
 
 
local searchColumnIdx
 
local searchColumnIdx
local outputColumnIdx
 
 
for i, field in ipairs(data.schema.fields) do
 
for i, field in ipairs(data.schema.fields) do
 
if field.name == searchColumnName then
 
if field.name == searchColumnName then
 
searchColumnIdx = i
 
searchColumnIdx = i
 
end
 
end
if field.name == outputColumnName then
+
if searchColumnIdx then
outputColumnIdx = i
 
end
 
if searchColumnIdx and outputColumnIdx then
 
 
break
 
break
 
end
 
end
 
end
 
end
 
assert(searchColumnIdx, mw.ustring.format("Search column “%s” not found.", searchColumnName))
 
assert(searchColumnIdx, mw.ustring.format("Search column “%s” not found.", searchColumnName))
assert(outputColumnIdx, mw.ustring.format("Output column “%s” not found.", outputColumnName))
 
 
 
for i, record in ipairs(data.data) do
+
local occurrence = tonumber(args.occurrence) or 1
if record[searchColumnIdx] == searchValue then
+
return record[outputColumnIdx]
+
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
 
end
 
end
 
end
  
--- Returns a tabular data page as a wikitext table.
+
--- Returns the value of the cell(s) in the given output column(s) of the row
--- Usage: {{#invoke:Tabular data | wikitable | Table name }}
+
--- matching the search key and column.
function p.wikitable(frame)
+
--- Reminiscent of LOOKUP() macros in popular spreadsheet applications, except
local pageName = frame.args[1]
+
--- 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
 +
 
 +
function p._wikitable(args)
 +
local pageName = args[1]
 
local data = mw.ext.data.get(pageName)
 
local data = mw.ext.data.get(pageName)
 
 
Line 166: Line 215:
 
 
 
return htmlTable
 
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
 
end
  
 
return p
 
return p

Latest revision as of 16:47, 29 September 2020

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}}

3226

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 1 refers to the first matching row. A row index of -1 refers to the last matching row. It is an error to specify a row index of 0.
|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:

Lua error at line 48: Output column “totalConfirmedCases” not found..

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}}

2021-04-14

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}}

5435

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}}

5435 out of 243458150 users are administrators

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

Lua error: not enough memory.

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.

See also


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

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

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