EXTERNAL CSV("http://scleraviz.herokuapp.com/assets/data/population-group.csv") TYPEINFER(LIMIT 1) UNPIVOT population FOR age IN ( `Under 5 Years`, `5 to 13 Years`, `14 to 17 Years`, `18 to 24 Years`, `25 to 44 Years`, `45 to 64 Years`, `65 Years and Over` ) ORDERED BY state PLOT( GEOM=BAR(x=state, y=population), POSITION=DODGE, FILL=age SCALE=COLOR( "#98abc5", "#8a89a6", "#7b6888", "#6b486b", "#a05d56", "#d0743c", "#ff8c00" ) LEGEND, TOOLTIP=population ) AXIS population(TICKFORMAT=".2s")
Plots a grouped bar chart, where each group corresponds to a US state, and shows the population of different age groups in that state. The input data is transformed to get one row per bar using SQL's `UNPIVOT`. The ggplot2-inspired `POSITION=DODGE` modifier then groups the rows with the same `x` (i.e. `state`) and plots each group in sequence.
The input data is fetched from a URL, and the column datatypes are determined using the `TYPEINFER` operator. The data, containing one row per state, is then transformed using SQL `UNPIVOT` to get one row per bar -- with one new column containing the population, and another containing the age group. The `ORDERED BY state` avoids an unnecessary sort, needed because `POSITION=DODGE` requires all the rows with the same `x` value to be grouped together. The `ORDERED BY` asserts that the input is in the required order, and a sort is not needed.
State,Under 5 Years,5 to 13 Years,14 to 17 Years,18 to 24 Years,25 to 44 Years,45 to 64 Years,65 Years and Over CA,2704659,4499890,2159981,3853788,10604510,8819342,4114496 TX,2027307,3277946,1420518,2454721,7017731,5656528,2472223 NY,1208495,2141490,1058031,1999120,5355235,5120254,2607672 FL,1140516,1938695,925060,1607297,4782119,4746856,3187797 IL,894368,1558919,725973,1311479,3596343,3239173,1575308 PA,737462,1345341,679201,1203944,3157759,3414001,1910571