I love to build dataviz for insight discovery. I also love to put technology to the service of humanity
Public
Edited
Aug 20, 2022
Icicle(dataColumns.headersTree, {
label: (d) => d[0],
title: (d) => d[0],
height: 900
})
Tree(dataColumns.headersTree, {
label: (d) => d[0],
title: (d) => d[0],
// height: 900,
width
})
dataAsNetwork = dataColumns
.filter(
(d) =>
d.Valor &&
industries.includes(d["Título"]) &&
concepts.includes(d.Concepto) &&
conceptDigits.includes(("" + d[codigo]).slice(0, numDigits)) &&
d["Año"] === year
)
.map((d) => {
let source = d["Concepto"];
return d.all_headers.path.split("_").map((p) => {
const prevSource = source;
source = p;
return {
source: prevSource,
target: p,
value: d["Valor"]
};
});
})
.flat()
dataOriginal = FileAttachment(
"oferta-utilizacion-precios-constantes-2020provisional.xlsx"
).xlsx()
d3.group(
dataColumns.filter((d) => !isNaN(+d["Códigos CPC Vers. 2 A.C.\n"])),
(d) => d["Año"]
)
dataOriginal.sheet(`Cuadro 23`)
a = transformWithNewHeaders(dataOriginal.sheet(`Cuadro 23`), 2020)
// Build the headers combining the first rows
function transformWithNewHeaders(rawData, year) {
let data = [];
// Skip to second headers
let i = 9;
// // skip null rows. < 3 because of navio
// while (i < rawData.length && Object.keys(rawData[i]).length < 3) {
// i += 1;
// }
// console.log("skip null", i);
// // Skip first set of headers
// while (i < rawData.length && Object.keys(rawData[i]).length > 0) {
// i += 1;
// }
// console.log("skip first headers", i);
// // skip empty row after first headers
// i++;
// get the headers by appending the three rows together. Null for searching for empty rows
let headersMap = getNestedHeaders(rawData.slice(i), null);
// while (i < rawData.length && Object.keys(rawData[i]).length > 0) {
// i += 1;
// }
i = i + headersMap.__i; // where we found the last headers
console.log("skip second headers", i, headersMap);
while (i < rawData.length && Object.keys(rawData[i]).length < 3) {
i += 1;
}
console.log("First row of data", i);
// flag to bet set to true when we found the Total
let done = false;
// Create a new array replacing the headers
data = rawData
.slice(i)
.map(
(row) => {
const hs = Object.keys(row);
// const hs = Array.from(headersMap.keys());
if (hs.length < 3) return undefined;
// Manual fold with headers and not hs because sometimes there aren't any values
return hs
.map((h, i, hs) => {
// Marks the end of the data
if (!done && row[hs[0]] === "Total") {
console.log("Found Total finishing", i);
done = true;
}
const maxHeaderDepth = headersMap.__i - 1; // The number of rows processed when finding the headers
// debugger;
// Skip first two columns
if (
i >= 2 &&
!done &&
headersMap.get(h) &&
headersMap.get(h)[maxHeaderDepth]
)
return {
Año: year,
[headersMap.get("A")[maxHeaderDepth]]: row["A"],
[headersMap.get("B")[maxHeaderDepth]]: row["B"],
Título: headersMap.get(h)["path"],
all_headers: headersMap.get(h),
Valor: row[h]
};
})
.filter((d) => d !== undefined && d.Valor !== undefined);
}
// Object.fromEntries(Object.keys(row).map((h, i) => [headers[i], row[h]]))
)
.filter((d) => d !== undefined)
.flat();
data.columns = headersMap;
data.headersTree = getHeadersTree(headersMap);
return data;
}
// Transforms the headers into a tree
function getHeadersTree(headersMap) {
let tree = new Map();
for (let [originalHeader, headerObj] of headersMap.entries()) {
let parent = tree;
let parentHeader = null;
for (let [i, header] of Object.entries(headerObj)) {
// If the headers is the same than the parent, we don't create a new node
if (header && parentHeader !== header) {
// Ignore this one
if (i === "path") continue;
// Check if the node already exists, if not create it
let node = parent.get(header) || new Map();
parent.set(header, node);
parent = node;
parentHeader = header;
}
}
}
return tree;
}
// Takes a spreadsheet with nested headers and collapses them into one single row
// If numHeaderRows is falsy it will search for an empty row to mark the end of the headers
function getNestedHeaders(rawData, numHeaderRows = rawData.length) {
let i = 0;
let headersMap = new Map(
Array.from(Object.entries(rawData[i])).map(([k, v]) => [k, { 0: v }])
);
while (
// If we get a numHeaderRows use it to count, otherwise check for empty rows
(numHeaderRows ? i < numHeaderRows : Object.keys(rawData[i]).length) &&
Object.keys(rawData[i]).length
) {
for (let [c, v] of headersMap.entries()) {
v[i] = rawData[i][c];
// Concatenate the headers
let nh = v[i];
let ph = i > 0 ? v[i - 1] : nh;
let path = i > 0 ? v.path : nh;
// if (i > 0 && ph !== nh) debugger;
// If it is the same as previous row, don't concat it
if (i > 0 && nh && ph !== nh) {
path = path.concat(`_${nh}`);
}
v.path = path;
headersMap.set(c, v);
}
i++;
}
console.log("Get nested headers, end of cycle", i, headersMap);
headersMap.__i = i;
return headersMap;
}
oferta = d3
.range(13, 25, 2)
.map((cuadro, i) =>
transformWithNewHeaders(dataOriginal.sheet(`Cuadro ${cuadro}`), 2015 + i)
)
.reduce(flatWithProperties, [])
utilizacion = d3
// .range(2, 14, 2) // 2 digits
.range(14, 26, 2) // 5 digits
.map((cuadro, i) =>
transformWithNewHeaders(dataOriginal.sheet(`Cuadro ${cuadro}`), 2015 + i)
)
.reduce(flatWithProperties, [])
// Equiv to flat() while keeping properties
flatWithProperties = (prev, d) => Object.assign(d, prev.concat(d))
dataColumns = utilizacion
codigo = Object.keys(dataColumns[0])[1]
navio(utilizacion.map((d) => ({ ...d })))
import { navio } from "@john-guerra/navio"
import { multiAutoSelect } from "@john-guerra/multi-auto-select"
import { searchCheckbox } from "@john-guerra/search-checkbox"
// import { chart } with { dataMatrixGrouped as data } from "@d3/zoomable-icicle"
import { Icicle } from "@d3/icicle"
import { Tree } from "@d3/tree"
import { table2Tree } from "@john-guerra/table-2-tree"
import { SankeyChart } from "@d3/sankey"
One platform to build and deploy the best data apps
Experiment and prototype by building visualizations in live JavaScript notebooks. Collaborate with your team and decide which concepts to build out.
Use Observable Framework to build data apps locally. Use data loaders to build in any language or library, including Python, SQL, and R.
Seamlessly deploy to Observable. Test before you ship, use automatic deploy-on-commit, and ensure your projects are always up-to-date.