Lume Viz

EDD and Remote Files

Lots of the data we use comes from our Economic Data Dashboard (EDD). EDD stores data in long format, but for lume charts we need to reshape the data depending on the type of visualisation. For example, a line chart requires an x-axis column (usually a date), and a column per series that you want to show in the data. A bar chart requires a column with each catgeory, and a column per series. A hex map requries a column of geography codes, and additional columns for each layer of the data you want to show (which you can move between using sliders). Fundamentally, these are the same format - an x-axis column, and a column per series/category you want to show in the y-axis.

In the case that data is published in a standardised, machine readable format at a remote URL, we can use DuckDB and SQL queries to load and reshape the data in memory, without need for explicit extract, transform, load (ETL) pipelines. This keeps the site size down to a minimum, and vastly speeds up the build time on a data intensive site, such as the housing data explorer.

There is still a place for pipelines in more complex analysis. In this case can still use SQL to load all or some of that data into Lume.

Examples

UK Job Vacancies (000s) by sector

Example: remote.linechart

We access data through our Economic Data Dashboard (EDD). EDD uses long-format for all data files, but for Lume charts we need to reshape the data depending on the type of visualisation. Since the PIVOT statement is not yet supported as a prepared statement by the DuckDB library that we're using, we use a workaround defined in remote.reshape.ts. This has been re-used in remote.linechart.ts.

The query call remote.linechart(), returns the following result:

+---------------+---------------------------------------------+-------------------------------------------------------------------------+----------------------------------------------+---------------------------------------------------+
| date          | UK Job Vacancies (thousands) - Construction | UK Job Vacancies (thousands) - Electricity, Gas, Steam & Air Con Supply | UK Job Vacancies (thousands) - Manufacturing | UK Job Vacancies (thousands) - Mining & Quarrying |
+---------------+---------------------------------------------+-------------------------------------------------------------------------+----------------------------------------------+---------------------------------------------------+
| 1262304000000 | 9                                           | 3                                                                       | 27                                           | 1                                                 |
| 1264982400000 | 9                                           | 3                                                                       | 28                                           | 1                                                 |
| 1267401600000 | 9                                           | 3                                                                       | 26                                           | 1                                                 |
| 1270080000000 | 10                                          | 3                                                                       | 30                                           | 1                                                 |
| 1272672000000 | 10                                          | 3                                                                       | 30                                           | 1                                                 |
| 1275350400000 | 11                                          | 3                                                                       | 34                                           | 1                                                 |
| 1277942400000 | 10                                          | 3                                                                       | 34                                           | 1                                                 |
| 1280620800000 | 10                                          | 3                                                                       | 34                                           | 1                                                 |
| 1283299200000 | 11                                          | 3                                                                       | 33                                           | 1                                                 |
| 1285891200000 | 12                                          | 3                                                                       | 34                                           | 1                                                 |
| 1288569600000 | 13                                          | 3                                                                       | 34                                           | 1                                                 |
| 1291161600000 | 13                                          | 3                                                                       | 35                                           | 1                                                 |
| 1293840000000 | 12                                          | 3                                                                       | 37                                           | 1                                                 |
| 1296518400000 | 13                                          | 3                                                                       | 40                                           | 1                                                 |
| 1298937600000 | 12                                          | 3                                                                       | 41                                           | 2                                                 |
| 1301616000000 | 11                                          | 3                                                                       | 39                                           | 2                                                 |
| 1304208000000 | 10                                          | 3                                                                       | 38                                           | 2                                                 |
| 1306886400000 | 10                                          | 3                                                                       | 36                                           | 2                                                 |
| 1309478400000 | 10                                          | 3                                                                       | 36                                           | 2                                                 |
| 1312156800000 | 11                                          | 3                                                                       | 36                                           | 2                                                 |
| 1314835200000 | 10                                          | 3                                                                       | 37                                           | 2                                                 |
| 1317427200000 | 10                                          | 3                                                                       | 36                                           | 2                                                 |
| 1320105600000 | 9                                           | 3                                                                       | 39                                           | 2                                                 |
| 1322697600000 | 11                                          | 3                                                                       | 42                                           | 2                                                 |
| 1325376000000 | 11                                          | 3                                                                       | 42                                           | 2                                                 |
| 1328054400000 | 12                                          | 3                                                                       | 40                                           | 2                                                 |
| 1330560000000 | 13                                          | 3                                                                       | 40                                           | 2                                                 |
| 1333238400000 | 14                                          | 3                                                                       | 38                                           | 2                                                 |
| 1335830400000 | 14                                          | 3                                                                       | 38                                           | 2                                                 |
| 1338508800000 | 14                                          | 4                                                                       | 36                                           | 2                                                 |
| 1341100800000 | 13                                          | 3                                                                       | 37                                           | 2                                                 |
| 1343779200000 | 14                                          | 3                                                                       | 36                                           | 2                                                 |
| 1346457600000 | 15                                          | 3                                                                       | 38                                           | 2                                                 |
| 1349049600000 | 15                                          | 3                                                                       | 37                                           | 2                                                 |
| 1351728000000 | 13                                          | 3                                                                       | 36                                           | 2                                                 |
| 1354320000000 | 13                                          | 3                                                                       | 37                                           | 3                                                 |
| 1356998400000 | 12                                          | 3                                                                       | 37                                           | 2                                                 |
| 1359676800000 | 12                                          | 3                                                                       | 37                                           | 2                                                 |
| 1362096000000 | 13                                          | 3                                                                       | 37                                           | 3                                                 |
| 1364774400000 | 14                                          | 4                                                                       | 38                                           | 3                                                 |
| 1367366400000 | 15                                          | 4                                                                       | 37                                           | 3                                                 |
| 1370044800000 | 15                                          | 4                                                                       | 37                                           | 2                                                 |
| 1372636800000 | 14                                          | 4                                                                       | 36                                           | 2                                                 |
| 1375315200000 | 14                                          | 4                                                                       | 37                                           | 2                                                 |
| 1377993600000 | 14                                          | 4                                                                       | 39                                           | 2                                                 |
| 1380585600000 | 15                                          | 4                                                                       | 40                                           | 2                                                 |
| 1383264000000 | 16                                          | 4                                                                       | 40                                           | 2                                                 |
| 1385856000000 | 18                                          | 4                                                                       | 40                                           | 2                                                 |
| 1388534400000 | 18                                          | 4                                                                       | 39                                           | 2                                                 |
| 1391212800000 | 19                                          | 4                                                                       | 44                                           | 2                                                 |
| 1393632000000 | 21                                          | 4                                                                       | 46                                           | 2                                                 |
| 1396310400000 | 20                                          | 4                                                                       | 47                                           | 2                                                 |
| 1398902400000 | 19                                          | 4                                                                       | 47                                           | 2                                                 |
| 1401580800000 | 19                                          | 4                                                                       | 50                                           | 2                                                 |
| 1404172800000 | 22                                          | 4                                                                       | 51                                           | 2                                                 |
| 1406851200000 | 23                                          | 4                                                                       | 53                                           | 2                                                 |
| 1409529600000 | 23                                          | 4                                                                       | 48                                           | 2                                                 |
| 1412121600000 | 22                                          | 4                                                                       | 48                                           | 2                                                 |
| 1414800000000 | 22                                          | 4                                                                       | 47                                           | 1                                                 |
| 1417392000000 | 25                                          | 4                                                                       | 47                                           | 1                                                 |
| 1420070400000 | 27                                          | 4                                                                       | 50                                           | 1                                                 |
| 1422748800000 | 27                                          | 4                                                                       | 48                                           | 1                                                 |
| 1425168000000 | 26                                          | 4                                                                       | 47                                           | 1                                                 |
| 1427846400000 | 25                                          | 4                                                                       | 47                                           | 1                                                 |
| 1430438400000 | 26                                          | 4                                                                       | 50                                           | 1                                                 |
| 1433116800000 | 27                                          | 4                                                                       | 51                                           | 1                                                 |
| 1435708800000 | 25                                          | 4                                                                       | 51                                           | 1                                                 |
| 1438387200000 | 24                                          | 4                                                                       | 51                                           | 1                                                 |
| 1441065600000 | 23                                          | 4                                                                       | 49                                           | 1                                                 |
| 1443657600000 | 23                                          | 4                                                                       | 48                                           | 1                                                 |
| 1446336000000 | 22                                          | 4                                                                       | 48                                           | 1                                                 |
| 1448928000000 | 21                                          | 4                                                                       | 49                                           | 1                                                 |
| 1451606400000 | 21                                          | 4                                                                       | 47                                           | 1                                                 |
| 1454284800000 | 21                                          | 4                                                                       | 46                                           | 1                                                 |
| 1456790400000 | 22                                          | 4                                                                       | 45                                           | 1                                                 |
| 1459468800000 | 23                                          | 4                                                                       | 46                                           | 1                                                 |
| 1462060800000 | 22                                          | 4                                                                       | 46                                           | 1                                                 |
| 1464739200000 | 21                                          | 4                                                                       | 49                                           | 1                                                 |
| 1467331200000 | 21                                          | 4                                                                       | 49                                           | 1                                                 |
| 1470009600000 | 21                                          | 5                                                                       | 51                                           | 1                                                 |
| 1472688000000 | 25                                          | 4                                                                       | 52                                           | 1                                                 |
| 1475280000000 | 27                                          | 4                                                                       | 53                                           | 1                                                 |
| 1477958400000 | 29                                          | 4                                                                       | 53                                           | 1                                                 |
| 1480550400000 | 28                                          | 4                                                                       | 52                                           | 1                                                 |
| 1483228800000 | 29                                          | 4                                                                       | 52                                           | 1                                                 |
| 1485907200000 | 28                                          | 4                                                                       | 50                                           | 1                                                 |
| 1488326400000 | 27                                          | 4                                                                       | 55                                           | 1                                                 |
| 1491004800000 | 27                                          | 4                                                                       | 53                                           | 1                                                 |
| 1493596800000 | 27                                          | 4                                                                       | 53                                           | 1                                                 |
| 1496275200000 | 28                                          | 4                                                                       | 51                                           | 1                                                 |
| 1498867200000 | 30                                          | 4                                                                       | 51                                           | 1                                                 |
| 1501545600000 | 30                                          | 4                                                                       | 51                                           | 1                                                 |
| 1504224000000 | 31                                          | 4                                                                       | 54                                           | 1                                                 |
| 1506816000000 | 28                                          | 4                                                                       | 57                                           | 1                                                 |
| 1509494400000 | 26                                          | 4                                                                       | 59                                           | 1                                                 |
| 1512086400000 | 24                                          | 4                                                                       | 57                                           | 1                                                 |
| 1514764800000 | 22                                          | 4                                                                       | 58                                           | 1                                                 |
| 1517443200000 | 22                                          | 4                                                                       | 57                                           | 1                                                 |
| 1519862400000 | 23                                          | 3                                                                       | 60                                           | 1                                                 |
| 1522540800000 | 23                                          | 3                                                                       | 59                                           | 1                                                 |
| 1525132800000 | 26                                          | 4                                                                       | 60                                           | 1                                                 |
| 1527811200000 | 27                                          | 4                                                                       | 60                                           | 1                                                 |
| 1530403200000 | 29                                          | 4                                                                       | 61                                           | 1                                                 |
| 1533081600000 | 26                                          | 3                                                                       | 62                                           | 1                                                 |
| 1535760000000 | 27                                          | 3                                                                       | 63                                           | 1                                                 |
| 1538352000000 | 27                                          | 3                                                                       | 63                                           | 1                                                 |
| 1541030400000 | 27                                          | 3                                                                       | 63                                           | 1                                                 |
| 1543622400000 | 28                                          | 3                                                                       | 64                                           | 1                                                 |
| 1546300800000 | 28                                          | 3                                                                       | 60                                           | 1                                                 |
| 1548979200000 | 28                                          | 3                                                                       | 60                                           | 1                                                 |
| 1551398400000 | 27                                          | 4                                                                       | 57                                           | 2                                                 |
| 1554076800000 | 27                                          | 4                                                                       | 57                                           | 2                                                 |
| 1556668800000 | 26                                          | 4                                                                       | 54                                           | 2                                                 |
| 1559347200000 | 26                                          | 3                                                                       | 54                                           | 2                                                 |
| 1561939200000 | 25                                          | 3                                                                       | 54                                           | 2                                                 |
| 1564617600000 | 25                                          | 3                                                                       | 52                                           | 2                                                 |
| 1567296000000 | 24                                          | 3                                                                       | 51                                           | 2                                                 |
| 1569888000000 | 26                                          | 3                                                                       | 51                                           | 2                                                 |
| 1572566400000 | 26                                          | 3                                                                       | 53                                           | 2                                                 |
| 1575158400000 | 27                                          | 3                                                                       | 57                                           | 2                                                 |
| 1577836800000 | 28                                          | 3                                                                       | 55                                           | 2                                                 |
| 1580515200000 | 27                                          | 3                                                                       | 52                                           | 2                                                 |
| 1583020800000 | 21                                          | 3                                                                       | 41                                           | 1                                                 |
| 1585699200000 | 12                                          | 2                                                                       | 32                                           | 1                                                 |
| 1588291200000 | 7                                           | 2                                                                       | 24                                           | 0                                                 |
| 1590969600000 | 12                                          | 2                                                                       | 27                                           | 0                                                 |
| 1593561600000 | 18                                          | 3                                                                       | 31                                           | 0                                                 |
| 1596240000000 | 21                                          | 3                                                                       | 36                                           | 0                                                 |
| 1598918400000 | 25                                          | 3                                                                       | 42                                           | 1                                                 |
| 1601510400000 | 25                                          | 4                                                                       | 42                                           | 1                                                 |
| 1604188800000 | 28                                          | 3                                                                       | 44                                           | 1                                                 |
| 1606780800000 | 30                                          | 4                                                                       | 47                                           | 1                                                 |
| 1609459200000 | 29                                          | 4                                                                       | 50                                           | 1                                                 |
| 1612137600000 | 30                                          | 5                                                                       | 53                                           | 1                                                 |
| 1614556800000 | 28                                          | 6                                                                       | 57                                           | 1                                                 |
| 1617235200000 | 32                                          | 6                                                                       | 59                                           | 1                                                 |
| 1619827200000 | 30                                          | 6                                                                       | 64                                           | 1                                                 |
| 1622505600000 | 36                                          | 6                                                                       | 68                                           | 1                                                 |
| 1625097600000 | 38                                          | 6                                                                       | 75                                           | 1                                                 |
| 1627776000000 | 43                                          | 6                                                                       | 83                                           | 1                                                 |
| 1630454400000 | 45                                          | 6                                                                       | 90                                           | 2                                                 |
| 1633046400000 | 41                                          | 6                                                                       | 93                                           | 2                                                 |
| 1635724800000 | 40                                          | 6                                                                       | 94                                           | 2                                                 |
| 1638316800000 | 38                                          | 5                                                                       | 95                                           | 2                                                 |
| 1640995200000 | 48                                          | 5                                                                       | 96                                           | 2                                                 |
| 1643673600000 | 47                                          | 5                                                                       | 94                                           | 2                                                 |
| 1646092800000 | 49                                          | 5                                                                       | 95                                           | 2                                                 |
| 1648771200000 | 48                                          | 5                                                                       | 96                                           | 2                                                 |
| 1651363200000 | 46                                          | 6                                                                       | 94                                           | 2                                                 |
| 1654041600000 | 45                                          | 6                                                                       | 93                                           | 2                                                 |
| 1656633600000 | 46                                          | 6                                                                       | 89                                           | 2                                                 |
| 1659312000000 | 47                                          | 6                                                                       | 86                                           | 2                                                 |
| 1661990400000 | 48                                          | 6                                                                       | 84                                           | 2                                                 |
| 1664582400000 | 46                                          | 6                                                                       | 84                                           | 2                                                 |
| 1667260800000 | 43                                          | 5                                                                       | 78                                           | 2                                                 |
| 1669852800000 | 42                                          | 5                                                                       | 79                                           | 2                                                 |
| 1672531200000 | 40                                          | 5                                                                       | 78                                           | 2                                                 |
| 1675209600000 | 41                                          | 6                                                                       | 79                                           | 2                                                 |
| 1677628800000 | 41                                          | 6                                                                       | 75                                           | 2                                                 |
| 1680307200000 | 39                                          | 6                                                                       | 74                                           | 2                                                 |
| 1682899200000 | 39                                          | 6                                                                       | 71                                           | 2                                                 |
| 1685577600000 | 40                                          | 7                                                                       | 71                                           | 2                                                 |
| 1688169600000 | 37                                          | 6                                                                       | 70                                           | 2                                                 |
| 1690848000000 | 34                                          | 6                                                                       | 70                                           | 2                                                 |
| 1693526400000 | 33                                          | 5                                                                       | 69                                           | 2                                                 |
| 1696118400000 | 36                                          | 5                                                                       | 66                                           | 2                                                 |
| 1698796800000 | 37                                          | 5                                                                       | 65                                           | 2                                                 |
| 1701388800000 | 35                                          | 5                                                                       | 68                                           | 2                                                 |
| 1704067200000 | 36                                          | 5                                                                       | 67                                           | 2                                                 |
| 1706745600000 | 37                                          | 5                                                                       | 65                                           | 2                                                 |
| 1709251200000 | 36                                          | 5                                                                       | 61                                           | 2                                                 |
| 1711929600000 | 38                                          | 5                                                                       | 62                                           | 2                                                 |
| 1714521600000 | 36                                          | 4                                                                       | 63                                           | 2                                                 |
| 1717200000000 | 38                                          | 5                                                                       | 62                                           | 2                                                 |
| 1719792000000 | 37                                          | 4                                                                       | 61                                           | 1                                                 |
+---------------+---------------------------------------------+-------------------------------------------------------------------------+----------------------------------------------+---------------------------------------------------+