Skip to content

Instantly share code, notes, and snippets.

View xenatisch's full-sized avatar

Pouria Hadjibagheri xenatisch

62 followers · 1 following
View GitHub Profile
@xenatisch
xenatisch / to_decimal.r
Created January 5, 2023 12:40
Convert non-decimal integer numbers as string to decimal integers in R
# Examples:
# to_decimal("83", 12) : 44
# to_decimal("01101", 2) : 22
to_decimal <- function(num, base) {
digits <- strsplit(num, "")[[1]]
value <- 0
sequence <- seq_along(digits)
@xenatisch
xenatisch / covariance_map.ipynb
Last active January 4, 2022 11:41
Covariance map of case rates at UTLA level in England
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@xenatisch
xenatisch / query-plan.txt
Created November 29, 2021 10:28
Open Data API: Complex query sample
Custom Scan (Citus Adaptive) (cost=0.00..0.00 rows=100000 width=594) (actual time=14.645..14.648 rows=40 loops=1)
Task Count: 32
Tuple data received from nodes: 2240 bytes
Tasks Shown: One of 32
-> Task
Tuple data received from node: 53 bytes
Node: host=private-w6.[REDACTED] port=5432 dbname=citus
-> Nested Loop (cost=0.71..196.77 rows=1 width=94) (actual time=0.120..2.282 rows=1 loops=1)
Buffers: shared hit=190
-> Nested Loop (cost=0.43..196.31 rows=1 width=162) (actual time=0.090..2.251 rows=1 loops=1)
@xenatisch
xenatisch / cache_repopulation-normal.sql
Created November 21, 2021 12:02
Normal and optimised versions of Postgres (Citus) queries and plans thereof
SELECT
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'area_code', area_code,
'area_type', area_type,
'area_name', area_name,
'date', to_char(date::DATE, 'YYYY-MM-DD'),
'metric', metric,
'value', value,
@xenatisch
xenatisch / admission_demographics_row.json
Last active November 18, 2021 19:52
Payload examples
[
{
"age": "0_to_5",
"rate": 175.2,
"value": 868
},
{
"age": "65_to_84",
"rate": 2802.1,
"value": 32250
@xenatisch
xenatisch / cache_repopulation.sql
Last active November 18, 2021 19:36
Query to repopulate cache for postcode pages of the UK Coronavirus Dashboard
SELECT
'area-' || release_date::TEXT || '-' || area_id::TEXT AS key,
JSONB_AGG(
JSONB_BUILD_OBJECT(
'area_code', area_code,
'area_type', area_type,
'area_name', area_name,
'date', to_char(date::DATE, 'YYYY-MM-DD'),
'metric', metric,
'value', value,
@xenatisch
xenatisch / area_reference.csv
Created July 27, 2021 08:22
List of upper and lower tier local authorities in the United Kingdom - 2019 boundaries.
area_type area_code area_name
ltla E06000001 Hartlepool
utla E06000001 Hartlepool
ltla E06000002 Middlesbrough
utla E06000002 Middlesbrough
ltla E06000003 Redcar and Cleveland
utla E06000003 Redcar and Cleveland
ltla E06000004 Stockton-on-Tees
utla E06000004 Stockton-on-Tees
ltla E06000005 Darlington
@xenatisch
xenatisch / cumulative2daily.ipynb
Last active July 26, 2021 10:53
Calculates daily time series using the cumulative `value_metric` columns in data downloaded from the UK Coronavirus Dashboard - APIv2.
Sorry, something went wrong. Reload?
Sorry, we cannot display this file.
Sorry, this file is invalid so it cannot be displayed.
@xenatisch
xenatisch / demographics.r
Created May 27, 2021 15:17
Convert APIv2 response for the age demographics data from the UK Coronavirus Dashboard to unstacked (wide) format
library(dplyr)
library(tidyr)
data <- read.csv("https://api.coronavirus.data.gov.uk/v2/data?areaType=ltla&metric=newCasesBySpecimenDateAgeDemographics&format=csv")
# Add the age bands you want to exclude
excluded_age_bands <- c("unassigned", "60+", "00_59")
# Add area codes you want to include - leave blank for everything.
included_area_codes <- c()
@xenatisch
xenatisch / local_data.sql
Created May 26, 2021 18:53
Retrieves data at the most granular level for a postcode
WITH
location AS (
SELECT id, ref.area_type, area_code, area_name, postcode, priority
FROM covid19.postcode_lookup
JOIN covid19.area_reference AS ref ON ref.id = area_id
JOIN covid19.area_priorities AS ap ON ref.area_type = ap.area_type
WHERE UPPER(REPLACE(postcode, ' ', '')) = $2
),
metrics AS (
SELECT id, metric