computeHeatmap: Compute 2-dimensional multi-layered matrix for heat map visualizations.

Description

Compute aggregate value(s) across two category classes represented by the table columns dimension1 and dimension2. Resulting data frame represents 2-dimensional multi-layered matrix where each layer comprises values from single aggregate. Category columns usually are of character, temporal, or discrete types. Values are aggregates computed across category columns utilizing SQL GROUP BY , . Aggregate formula may use any SQL expressions allowed with the GROUP BY as defined above. Results are usually fed into createHeatmap for heat map visualizations. If defined, parameter by expands grouping columns to be used with heat maps with faceting.

Usage

computeHeatmap(channel, tableName, dimension1,
    dimension2, aggregates = "COUNT(*) cnt",
    aggregateFun = NULL, aggregateAlias = NULL,
    dimAsFactor = TRUE, withMelt = FALSE, where = NULL,
    by = NULL, test = FALSE)

Arguments

channel

connection object as returned by odbcConnect

tableName

table name

dimension1

name of the column for for heatmap x values. This value along with dimension2 are x and y scales of heatmap table.

dimension2

name of the column for for heatmap y values. This value along with dimension1 are x and y scales of heatmap table.

aggregates

vector with SQL aggregates to compute values for heat map. Aggregate may have optional aliases like in "AVG(era) avg_era". Subsequently, use in createHeatmap as color (fill), text, and threshold values for heat map ce

aggregateFun

deprecated. Use aggregates instead.

aggregateAlias

deprecated. Use aggregates instead.

dimAsFactor

logical indicates if dimensions and optional facet columns should be converted to factors. This is almost always necessary for heat maps.

withMelt

logical if TRUE then uses reshape2 melt to transform data frame with aggregate values in designated columns into a molten data frame.

where

specifies criteria to satisfy by the table rows before applying computation. The creteria are expressed in the form of SQL predicates (inside WHERE clause).

for optional grouping by one or more values for faceting or alike

test

logical: if TRUE show what would be done, only (similar to parameter test in RODBC functions: sqlQuery and sqlSave).

Value

Data frame representing 2-dimensional multi-layered matrix to use with createHeatmap. Matrix has as many layers as there are aggregates computed. If by defined, data frame contains multiple matrices for each value(s) from the column(s) in by (to support facets). When withMelt TRUE function melt applies transforming data frame and columns with aggregate values for easy casting: expands number of rows and replaces all aggregate columns with two: variable and value.

Details

Result represents 2-dimensional matrix with as many data layers as there were aggregates computed. Additionally more layers defined with parameter by support facets.

Examples

Run this code

hm = computeHeatmap(conn, "teams_enh", 'franchid', 'decadeid', 'avg(w) w',
                    where="decadeid >= 1950")
hm$decadeid = factor(hm$decadeid)
createHeatmap(hm, 'decadeid', 'franchid', 'w')

# with diverging color gradient
hm = computeHeatmap(conn, "teams_enh", 'franchid', 'decadeid', 'avg(w-l) wl',
                    where="decadeid >= 1950")
hm$decadeid = factor(hm$decadeid)
createHeatmap(hm, 'decadeid', 'franchid', 'wl', divergingColourGradient = TRUE)