The avatar for @jzavala-gonzalez
José M. Zavala González
Public
Edited
Jun 9, 2024
Fork of Resumen de apagones (old)
12
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
{
const num_days = regionsWithoutService_daily_peak_total.length;
const num_days_above_threshold = regionsWithoutService_daily_peak_total.filter(d => d.totalClientsWithoutService >= service_threshold).length;
const pct_days_above_threshold = num_days_above_threshold / num_days;
return htl.html`
Hay <strong>${num_days_above_threshold} de ${num_days} dias (${pct_days_above_threshold.toLocaleString(undefined,{style: 'percent', minimumFractionDigits:0})})</strong> con al menos <strong>${service_threshold.toLocaleString("en-US")} clientes</strong> tolerando apagones
`
}
Insert cell
{
const dataWidth = width;
const marginLeft = 50;
const marginRight = 50;
return Plot.plot({
width: dataWidth + marginLeft + marginRight,
marginLeft,
marginRight,
marks: [
Plot.rectY(clientes_afectados_por_dia,
{
x: 'marca_fecha_presentada',
y: "total_clientes_afectados",
fill: d => (d.total_clientes_afectados >= service_threshold) ? 'red' : 'black',
fillOpacity: d => (d.total_clientes_afectados >= service_threshold) ? .5 : 1,
tip: true,
interval: d3.utcDay}),
// Plot.rectY(regionsWithoutService_daily_peak,
// { filter: d => (d.type === 'totals') & (d.totalClientsWithoutService >= service_threshold),
// x: d => new Date(d.timestamp_date_string),
// y: "totalClientsWithoutService",
// fill: 'red', fillOpacity: 0.5,
// // stroke: 'red', strokeOpacity: 1.0,
// tip: true,
// interval: d3.utcDay}),
Plot.ruleY([0])
]
})
}
Insert cell
Insert cell
Insert cell
Insert cell
{
let thedata = clientes_afectados_por_dia
.map(d => Object({
fecha: d3.utcFormat('%Y-%m-%d')(d.marca_fecha_presentada),
total_clientes_afectados: d.total_clientes_afectados,
}))

return aq.from(thedata).view(1000)
}
Insert cell
Plot.plot({
width,
marks: [
Plot.ruleY([0]),
Plot.lineY(saidi_minutos, {
x: "marca_dia_presentada", y: "saidi_minutos", marker: true, tip: true})
]
})
Insert cell
Insert cell
Insert cell
{
let thedata = clientes_afectados_por_region_por_dia
.filter(d => d.region === region_ver)
.map(d => Object({
fecha: d3.utcFormat('%Y-%m-%d')(d.marca_fecha_presentada),
total_clientes_afectados: d.total_clientes_sin_servicio,
}))

return aq.from(thedata).view(1000)
}
Insert cell
clientes_afectados_por_region_por_dia
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
d3.timeFormat('%Y-%m-%d')(new Date())
Insert cell
new Date().toDateString()//.slice(0,10)
Insert cell
Insert cell
width
Insert cell
Insert cell
Insert cell
Insert cell
Insert cell
daily_observations_chart = {

let thedata = clientes_energizados_daychosen;
let thedata_max = thedata[d3.maxIndex(thedata, d => d.total_clientes_sin_servicio)];

let themargins = {
marginLeft: 10,
marginRight: 0,
// marginTop: 200,
marginTop: 20,
marginBottom: 40,
}

let the_width = chosen_width

// Input params for dot_symbol_packing_X
let chart_width = the_width - themargins.marginLeft - themargins.marginRight;
// let num_points = thedata.length;
let num_points = 288;
const min_radius = 1;
const max_radius = 3;
// output
const chosen_radius = dot_symbol_packing_X('circle', chart_width, num_points, min_radius, max_radius);

// Input for rule width scaling
const rule_padding = .95;
const min_rule_width = 0;
// output
const fit_radius = chart_width / (num_points - 1) / 2;
const fit_rule_width = fit_radius*2;
const padded_rule_width = fit_rule_width * (1 - rule_padding);
const chosen_rule_width = Math.max(
min_rule_width,
Math.min(padded_rule_width, 1)
);


let theplot = Plot.plot({
caption: `Radius: ${(chosen_radius)} Rule width: ${chosen_rule_width} Chart width: ${chart_width}`,
style: "overflow: visible;",
...themargins,
// marginLeft: 80,
width: the_width,
// width: 700,
height: 600,
// marginTop: 200,
x: {
domain: [fecha_picker, new Date(fecha_picker.getTime() + 1439*60000)],
},
y: {
domain: [0,100e3],
// ticks: 5,
// tickFormat: '%'
},
r: {type: 'linear'},
marks: [
Plot.axisX({
fontFamily: 'Inter, sans-serif',
}),
Plot.axisY({
rotate: -90,
textAnchor: 'middle',
tickFormat: d => (d/1e3).toString() + ' mil',
label: null,
fontSize: 12,
tickSize: 0,
fontFamily: 'Inter, sans-serif',
// ticks: 5,
ticks: [25e3, 50e3, 75e3, 100e3],
}),
Plot.gridY({
strokeDasharray: "2",
interval: 25e3, stroke: "black", strokeOpacity: 1}),
// Plot.areaY(
// thedata
// , {
// tip: false,
// x: "marca_hora_presentada",
// y1: d => d["total_clientes_sin_servicio"] >= service_threshold ? 0 : null,
// y2: d => d["total_clientes_sin_servicio"] >= service_threshold ? d["total_clientes_sin_servicio"] : null,
// fill: 'red', fillOpacity: 0.5,
// curve: 'step-after'
// //stroke: 'red', strokeOpacity: 1,
// }),
Plot.ruleX(thedata, {
x: 'marca_hora_presentada', y: d => d.total_clientes_sin_servicio !== 0 ? d.total_clientes_sin_servicio : null,
// strokeWidth: d => .2,
strokeWidth: chosen_rule_width,
}),
Plot.ruleX(thedata, {
x: 'marca_hora_presentada',
y1: d => d.total_clientes_sin_servicio !== 0 ? Math.max(0, d.total_clientes_sin_servicio - stroke_length) : null,
y2: d => d.total_clientes_sin_servicio !== 0 ? d.total_clientes_sin_servicio : null,
// strokeWidth: d => .2,
strokeWidth: 1,
stroke: 'black',
strokeLinecap: 'square',
}),
show_line ? Plot.line(
thedata
, {
tip: false,
x: "marca_hora_presentada", y:"total_clientes_sin_servicio",
stroke: 'darkblue',
strokeWidth: chosen_radius,
channels: {noService: "porcentaje_clientes_sin_servicio", hora: 'marca_hora_presentada'},
}) : null,
show_dot ? Plot.dot(
thedata
, {
tip: true,
x: "marca_hora_presentada", y:"total_clientes_sin_servicio",
stroke: null,
fill: 'black',
r: chosen_radius,
channels: {noService: "porcentaje_clientes_sin_servicio", hora: 'marca_hora_presentada'},
}) : null,
Plot.text([thedata_max], {
x: "marca_hora_presentada", y:"total_clientes_sin_servicio",
fill: 'black',
text: 'total_clientes_sin_servicio',
lineAnchor: 'bottom', dy: -8,
fontWeight: 'bold',
fontSize: 14,
fontFamily: 'B612 Mono',
}),

Plot.ruleY([0]),
// Plot.Tip()
]
})

return theplot
}
Insert cell
fecha_picker
Insert cell
Insert cell
Insert cell
clientes_energizados_daychosen[d3.maxIndex(clientes_energizados_daychosen, d => d.total_clientes_sin_servicio)]
Insert cell
Insert cell
estilos = htl.html`
<style>
@import url('https://fonts.googleapis.com/css2?family=B612+Mono:wght@400;700&family=Inter:wght@400;700&display=swap');
label {
font-family: 'Inter', sans-serif;
}
</style>
`
Insert cell
<style>
</style>
Insert cell
Insert cell
import lz77 from "lz77"
Insert cell
function dot_symbol_packing_X(symbol, chart_width, num_points, min_radius, max_radius) {

// Calculations
const fit_radius = chart_width / (num_points - 1) / 2;

// Outputs
const chosen_radius = Math.max(min_radius, Math.min(fit_radius, max_radius))
return chosen_radius * symbol_packing_scale_factors[symbol]
}
Insert cell
symbol_packing_scale_factors = Object({
'circle': 1, // default. 'r' maps to circle radius
'square': 1.1275, // 'r' maps to half a side length, after scaling
})
Insert cell
regions_service
Insert cell
Insert cell
regionsWithoutService_daily_peak_total = regionsWithoutService_daily_peak.filter(d => d.type === 'totals')
Insert cell
import {m4} from "@uwdata/m4-scalable-time-series-visualization"
Insert cell
// regions_service = d3.csvParse(datastring, d => {
// const dauto = d3.autoType(d);
// dauto['timestamp_saved'] = new Date(parsePRDate(dauto['timestamp_saved']) ) // idk whats up w this one
// dauto['timestamp'] = new Date(parsePRDate(dauto['timestamp']) ) // read as PR time (GMT-4). All set (?)
// dauto['region_name'] = dauto['region_name'] === null ? 'totals' : dauto['region_name']
// dauto['timestamp_date_string'] = dauto['timestamp'].toLocaleString(undefined, {
// "year": "numeric",
// "month": "2-digit",
// "day": "2-digit",
// timeZone: 'UTC', // so much pain
// })
// return dauto;
// })
Insert cell
// datastring = fetch("https://raw.githubusercontent.com/jzavala-gonzalez/scraping-luma/main/regionsWithoutService_historical.csv").then((response) => response.text())
Insert cell
clientes_energizados_por_region_dayago = clientes_energizados_por_region.filter(d => (d.marca_hora_presentada >= a_day_ago))
Insert cell
clientes_energizados_dayago = clientes_energizados.filter(d => (d.marca_hora_presentada >= a_day_ago))
Insert cell
clientes_energizados_daychosen = clientes_energizados.filter(d => d.marca_fecha_presentada.getTime() === fecha_picker.getTime())
Insert cell
clientes_energizados_daychosen_filled = clientes_energizados_filled.filter(d => d.marca_fecha_presentada.getTime() === fecha_picker.getTime())
Insert cell
clientes_energizados_filled.filter(d => d.marca_fecha_presentada === null)
Insert cell
clientes_energizados_daychosen
with listado_marca_hora_presentada as (
select
unnest(generate_series(
(marca_fecha_presentada::VARCHAR || ' 00:00:00')::DATETIME,
(marca_fecha_presentada::VARCHAR || ' 23:55:00')::DATETIME,
interval 5 minute
)) as marca_hora_presentada
from (from clientes_energizados_daychosen limit 1)
)


-- from listado_marca_hora_presentada
-- left join
-- clientes_energizados_daychosen
-- using (marca_hora_presentada)
-- order by marca_hora_presentada

from clientes_energizados_daychosen
Insert cell
clientes_energizados_daychosen
Type SQL, then Shift-Enter. Ctrl-space for more options.

Insert cell
latest_timestamp = d3.max(clientes_afectados_por_dia, d => d.marca_fecha_presentada)
Insert cell
latest_timestamp_date_string = latest_timestamp.toLocaleString(undefined, {
"year": "numeric",
"month": "2-digit",
"day": "2-digit",
timeZone: 'UTC', // so much pain
})
Insert cell
a_day_ago = new Date(getUTCTime(new Date(Date.now() - ms_per_hour*24)) - ms_per_hour*4)
Insert cell
ms_per_hour = 3_600_000
Insert cell
function range(n) { return Array.from(Array(n).keys()) }
Insert cell
function getUTCTime(date) {
let now_utc = Date.UTC(date.getUTCFullYear(), date.getUTCMonth(),
date.getUTCDate(), date.getUTCHours(),
date.getUTCMinutes(), date.getUTCSeconds());
return now_utc
}
Insert cell
function parseUTCDate(datestring) {
let date = new Date(datestring);
return getUTCTime(date);
}
Insert cell
function parsePRDate(datestring) {
let now_utc = parseUTCDate(datestring);
return now_utc - 3_600_000*4;
}
Insert cell
(parseUTCDate("07/21/2023 04:23 AM"))
Insert cell
new Date(parseUTCDate("07/21/2023 04:23 AM"))
Insert cell
new Date(parsePRDate("07/21/2023 04:23 AM"))
Insert cell
uniform_date(sample_date)
Insert cell
function uniform_date(date) {
return new Date(Date.UTC(0, 0,
1, date.getUTCHours(),
date.getUTCMinutes(), date.getUTCSeconds()) + 14_400_000*0)
}
Insert cell
sample_date = regions_service[0].timestamp
Insert cell
import {aq, op} from "@uwdata/arquero"
Insert cell
dbref
select *,
date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
from clientes_energizados
-- order by marca_hora_presentada
Insert cell
dbref
with clientes_energizados_copy as (
select *,
date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
from clientes_energizados
),
listado_marca_hora_presentada as (
select
unnest(generate_series(
(min(marca_fecha_presentada)::VARCHAR || ' 00:00:00')::DATETIME,
(max(marca_fecha_presentada)::VARCHAR || ' 23:55:00')::DATETIME,
interval 5 minute
)) as marca_hora_presentada
from clientes_energizados_copy
order by marca_hora_presentada
),

listado_marca_hora_y_fecha as (
select *, date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
from listado_marca_hora_presentada
)

select *,
marca_hora_presentada <= max(marca_hora_presentada) filter (marca_hora_accedida is not null) over () as dato_publicado,
case when not dato_publicado then false
when marca_hora_accedida is not null then true
else false end as dato_accedido,
from listado_marca_hora_y_fecha
left join
clientes_energizados_copy
using (marca_hora_presentada)
order by marca_hora_presentada
Insert cell
dbref
from clientes_energizados_por_region
Insert cell
// dbref = DuckDBClient.of({
// 'clientes_energizados': FileAttachment("clientes_energizados@4.parquet"),
// 'clientes_energizados_por_region': FileAttachment("clientes_energizados_por_region@4.parquet"),
// })
Insert cell
// dbempty = new DuckDBClient(db_instance)
dbempty = DuckDBClient.of({})
Insert cell
dbref = dbempty, (
await dbempty.query(`
create or replace table clientes_energizados_por_region as (
from read_parquet('${bucket_url}/datasets/clientes_energizados_por_region.parquet')
)
`)
),
(
await dbempty.query(`
create or replace table clientes_energizados as (
from read_parquet('${bucket_url}/datasets/clientes_energizados.parquet')
)
`)
),
dbempty
Insert cell
// loadquery = await dbref.query(`
// CREATE OR REPLACE TABLE regionsWithoutService AS (

// with initial_table as (
// SELECT
// strptime(timestamp_saved, '%m/%d/%Y %I:%M %p') as timestamp_saved,
// strptime(timestamp, '%m/%d/%Y %I:%M %p') as timestamp,
// type,
// region_name,
// totalClients,
// totalClientsWithoutService,
// totalClientsWithService,
// percentageClientsWithoutService,
// percentageClientsWithService
// FROM read_parquet('https://raw.githubusercontent.com/jzavala-gonzalez/scraping-luma/main/regionsWithoutService_historical.parquet')
// )

// select *,
// strftime(timestamp, '%m/%d/%Y') as timestamp_date_string,
// date_part('hour', timestamp) as timestamp_hour,
// from initial_table
// )
// `)
Insert cell
datadb
from clientes_afectados_por_dia
Insert cell
datadb
select
date_trunc('day', marca_hora_presentada) as marca_dia_presentada,
sum("total_clientes_sin_servicio")::FLOAT as cliente_observaciones_sin_servicio,
max(total_clientes) as "total_clientes_suplidos",
(cliente_observaciones_sin_servicio / total_clientes_suplidos) as saidi_observaciones,
round(saidi_observaciones * 5, 2) as saidi_minutos, -- 5 minutos per observacion
from clientes_energizados
-- where
-- date_part('year', marca_hora_presentada) = 2024
-- and date_part('month', marca_hora_presentada) <= 3
group by marca_dia_presentada
order by marca_dia_presentada
Insert cell
dailyquery = //loadquery,
(await dbref.query(`
CREATE OR REPLACE TABLE clientes_afectados_por_dia AS (

with extra_data as (
select *,
date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
( max(total_clientes_sin_servicio) over (partition by "marca_fecha_presentada", "region")) as the_worst
from clientes_energizados_por_region
),

only_the_worst as (

select distinct on (marca_fecha_presentada, region) * exclude the_worst
from extra_data
where total_clientes_sin_servicio = the_worst
order by marca_fecha_presentada, region, marca_hora_presentada -- NOTE: no deberiamos tener duplicados de marca_fecha en caso que mas de una hora presentada tengan el mismo valor
)

select
marca_fecha_presentada,
-- total_clientes_sin_servicio,
sum(total_clientes_sin_servicio)::INTEGER as total_clientes_afectados
from only_the_worst
group by marca_fecha_presentada
)
`))
Insert cell
datadb
from clientes_afectados_por_region_por_dia
Insert cell
query_clientes_afectados_por_region_por_dia = //loadquery,
(await dbref.query(`
CREATE OR REPLACE TABLE clientes_afectados_por_region_por_dia AS (

with extra_data as (
select *,
date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
( max(total_clientes_sin_servicio) over (partition by "marca_fecha_presentada", "region")) as the_worst
from clientes_energizados_por_region
),

only_the_worst as (

select distinct on (marca_fecha_presentada, region) * exclude the_worst
from extra_data
where total_clientes_sin_servicio = the_worst
order by marca_fecha_presentada, region, marca_hora_presentada -- NOTE: no deberiamos tener duplicados de marca_fecha en caso que mas de una hora presentada tengan el mismo valor
)

select
*
from only_the_worst
)
`))
Insert cell
datadb
from xmr_clientes_afectados_por_dia
Insert cell
xmr_clientes_afectados_por_dia_query = dailyquery,
(await dbref.query(`
CREATE OR REPLACE TABLE xmr_clientes_afectados_por_dia AS (

with sorted_data as (
select
marca_fecha_presentada as order_variable,
total_clientes_afectados as X
from clientes_afectados_por_dia
order by order_variable
),

xmr as (
select
order_variable,
X,
abs(X - lag(X) over ()) as mR,
mean(X) over () as X_mean,
from sorted_data
)

select
*,
mean(mR) over () as mR_mean,
case when (X_mean - 2.66*mR_mean) > 0 THEN (X_mean - 2.66*mR_mean) else 0 end as LPNL,
X_mean + 2.66*mR_mean as UPNL,
mR_mean*3.269 as URL,
from xmr
)
`))
Insert cell
datadb
from clientes_afectados_por_dia_crudo
Insert cell
dailyquery_crudo = //loadquery,
(await dbref.query(`
CREATE OR REPLACE TABLE clientes_afectados_por_dia_crudo AS (

with extra_data as (
select *,
date_trunc('day', marca_hora_presentada) as marca_fecha_presentada,
( max(total_clientes_sin_servicio) over (partition by "marca_fecha_presentada")) as the_worst
from clientes_energizados
),

only_the_worst as (

select distinct on (marca_fecha_presentada) * exclude the_worst
from extra_data
where total_clientes_sin_servicio = the_worst
order by marca_fecha_presentada, marca_hora_presentada
)

select
marca_fecha_presentada,
-- total_clientes_sin_servicio,
sum(total_clientes_sin_servicio)::INTEGER as total_clientes_afectados
from only_the_worst
group by marca_fecha_presentada
)
`))
Insert cell
bucket_url = 'https://numeros-luzpr.com' // smh
Insert cell
datadb = dailyquery_crudo, dailyquery, query_clientes_afectados_por_region_por_dia, xmr_clientes_afectados_por_dia_query, dbref
Insert cell
dbref
select 2 as two;
-- select *
-- from clientes_energizados_por_region
Insert cell
clientes_energizados_por_region = dbref.sql`from clientes_energizados_por_region;`
Insert cell
datadb
select *
from regionsWithoutService_daily_peak
Insert cell
regions_service[0]
Insert cell
duckdb = import(
"https://cdn.jsdelivr.net/npm/@duckdb/duckdb-wasm@1.28.1-dev106.0/+esm"
)
Insert cell
bundle = {
const bundles = duckdb.getJsDelivrBundles()
const duckdb_bundle = 'Auto';
if (duckdb_bundle === 'Auto') {
return duckdb.selectBundle(bundles)
} else {
const bun = bundles[duckdb_bundle]
bun['pthreadWorker'] = null;
return bun;
}
// return bundles['mvp']
}
Insert cell
async function makeDB() {
const logger = new duckdb.ConsoleLogger();
const worker = await duckdb.createWorker(bundle.mainWorker);
const db = new duckdb.AsyncDuckDB(logger, worker);
await db.instantiate(bundle.mainModule);
return db
}
Insert cell
db_instance = makeDB()
Insert cell

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.
Learn more