Bar Chart (Stacked, Normalized, Sorted)

Stacked, normalized bar chart, with bar order in decreasing order of the under-5 years population

Compare with D3.js original

(SELECT state,
        yunder5/total AS yunder5,
        y5to13/total AS y5to13,
        y14to17/total AS y14to17,
        y18to24/total AS y18to24,
        y25to44/total AS y25to44,
        y45to64/total AS y45to64,
        y65over/total AS y65over
 FROM (
   SELECT *, (yunder5 + y5to13 + y14to17 + y18to24 +
              y25to44 + y45to64 + y65over)::float AS total
   FROM EXTERNAL CSV("http://scleraviz.herokuapp.com/assets/data/population-alt.csv") TYPEINFER(LIMIT 1)
 )
 ORDER BY yunder5 DESC)
UNPIVOT population FOR age IN (
  yunder5 AS "Under 5 Years",
  y5to13 AS "5 to 13 Years",
  y14to17 AS "14 to 17 Years",
  y18to24 AS "18 to 24 Years",
  y25to44 AS "25 to 44 Years",
  y45to64 AS "45 to 64 Years",
  y65over AS "65 Years and Over"
)
ORDERED BY state
PLOT(
  GEOM=BAR(x=state, y=population),
  POSITION=STACK,
  FILL=age SCALE=COLOR(
    "#98abc5",
    "#8a89a6",
    "#7b6888",
    "#6b486b",
    "#a05d56",
    "#d0743c",
    "#ff8c00"
  ) LEGEND(REVERSED),
  TOOLTIP=(population*100)::varchar || "%"
)
AXIS population(TICKFORMAT="%")

Description

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 sorted to get the states in decreasing order of the under-5 years population, and then transformed to get one row per bar using SQL's `UNPIVOT`. The ggplot2-inspired `POSITION=STACK` modifier then groups the rows with the same `x` (i.e. `state`) and plots each group in sequence. Note that the legend is reversed using the `REVERSED` modifier, needed to match the order of the age groups in the bars.

Data Preparation

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 normalized, then sorted to get the states in decreasing order of the normalized under-5 years population, and 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 a re-sorting of the data, needed because `POSITION=STACK` 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.

population-alt.csv

STATE,YUNDER5,Y5TO13,Y14TO17,Y18TO24,Y25TO44,Y45TO64,Y65OVER
AL,310504,552339,259034,450818,1231572,1215966,641667
AK,52083,85640,42153,74257,198724,183159,50277
AZ,515910,828669,362642,601943,1804762,1523681,862573
AR,202070,343207,157204,264160,754420,727124,407205
CA,2704659,4499890,2159981,3853788,10604510,8819342,4114496
CO,358280,587154,261701,466194,1464939,1290094,511094
CT,211637,403658,196918,325110,916955,968967,478007
DE,59319,99496,47414,84464,230183,230528,121688
DC,36352,50439,25225,75569,193557,140043,70648
FL,1140516,1938695,925060,1607297,4782119,4746856,3187797
GA,740521,1250460,557860,919876,2846985,2389018,981024
HI,87207,134025,64011,124834,356237,331817,190067
ID,121746,201192,89702,147606,406247,375173,182150
IL,894368,1558919,725973,1311479,3596343,3239173,1575308
IN,443089,780199,361393,605863,1724528,1647881,813839
IA,201321,345409,165883,306398,750505,788485,444554
KS,202529,342134,155822,293114,728166,713663,366706
KY,284601,493536,229927,381394,1179637,1134283,565867
LA,310716,542341,254916,471275,1162463,1128771,540314
ME,71459,133656,69752,112682,331809,397911,199187
MD,371787,651923,316873,543470,1556225,1513754,679565
MA,383568,701752,341713,665879,1782449,1751508,871098
MI,625526,1179503,585169,974480,2628322,2706100,1304322
MN,358471,606802,289371,507289,1416063,1391878,650519
MS,220813,371502,174405,305964,764203,730133,371598
MO,399450,690476,331543,560463,1569626,1554812,805235
MT,61114,106088,53156,95232,236297,278241,137312
NE,132092,215265,99638,186657,457177,451756,240847
NV,199175,325650,142976,212379,769913,653357,296717
NH,75297,144235,73826,119114,345109,388250,169978
NJ,557421,1011656,478505,769321,2379649,2335168,1150941
NM,148323,241326,112801,203097,517154,501604,260051
NY,1208495,2141490,1058031,1999120,5355235,5120254,2607672
NC,652823,1097890,492964,883397,2575603,2380685,1139052
ND,41896,67358,33794,82629,154913,166615,94276
OH,743750,1340492,646135,1081734,3019147,3083815,1570837
OK,266547,438926,200562,369916,957085,918688,490637
OR,243483,424167,199925,338162,1044056,1036269,503998
PA,737462,1345341,679201,1203944,3157759,3414001,1910571
RI,60934,111408,56198,114502,277779,282321,147646
SC,303024,517803,245400,438147,1193112,1186019,596295
SD,58566,94438,45305,82869,196738,210178,116100
TN,416334,725948,336312,550612,1719433,1646623,819626
TX,2027307,3277946,1420518,2454721,7017731,5656528,2472223
UT,268916,413034,167685,329585,772024,538978,246202
VT,32635,62538,33757,61679,155419,188593,86649
VA,522672,887525,413004,768475,2203286,2033550,940577
WA,433119,750274,357782,610378,1850983,1762811,783877
WV,105435,189649,91074,157989,470749,514505,285067
WI,362277,640286,311849,553914,1487457,1522038,750146
WY,38253,60890,29314,53980,137338,147279,65614

Built with D3 and D3-Legend

Powered by Sclera