Jan Benetka janbenetka

janbenetka / random_sample_of_crtain_size.sql

Created July 21, 2022 10:22

[Random sample in BQ] #bigquery #random

	SELECT * FROM subset
	WHERE RAND() < 1e6 / (SELECT COUNT(*) FROM subset)

janbenetka / jupyter_notebook_width.py

Created July 18, 2022 09:23

[Jupyter notebook width] #jupyter

	from IPython.display import HTML, IFrame, display
	display(HTML("<style>.container { width:1400px !important; }</style>"))

janbenetka / dbpedia_county_info.sparql

Created November 25, 2021 08:08

[DBPedia County Info] #sparql #rdf @dbpedia

	PREFIX dbpedia2: <http://dbpedia.org/property/>
	PREFIX dbo: <http://dbpedia.org/ontology/>
	PREFIX geo: <http://www.w3.org/2003/01/geo/wgs84_pos#>

	SELECT *
	WHERE {
	?county rdf:type yago:County108546183 .
	?county dbo:country dbr:United_States.
	?county dbo:state ?state.
	?county rdfs:label ?county_label.

janbenetka / find_adjacent_tracts.sql

Created November 16, 2021 01:35

[Adjacent tracts SQL] #bigquery #sql #geo

	CREATE OR REPLACE TABLE `uc-prox-core-dev.z_jan_retained.adjacent_tracts` AS

	WITH adjacent_tracts AS (
	SELECT a.fips, b.fips as fips_adjacent, a.state_name, b.state_name as state_name_adjacent, a.county_name, b.county_name as county_name_adjacent, a.geog, b.geog as geog_adjacent,
	FROM `uc-atlas.maps_us.census_tracts` a, `uc-atlas.maps_us.census_tracts` b
	WHERE ST_INTERSECTS(ST_BUFFER(a.geog, 25), b.geog)
	)

	SELECT * FROM adjacent_tracts

janbenetka / cell_height_colab.py

Created October 29, 2021 22:11

[Restrict height of cell in Colab (in px)] #colab

	from IPython.display import Javascript # Restrict height of output cell.
	display(Javascript('''google.colab.output.setIframeHeight(0, true, {maxHeight: 300})'''))

janbenetka / osm_pois_from_bq.sql

Created October 25, 2021 07:49

OSM POI w/ details from BigQuery

	WITH osm AS (
	SELECT
	layer_code,
	layer_class,
	layer_name,
	gdal_type as geography_type,
	osm_id,
	CASE
	WHEN (tags.key = 'name') THEN tags.value
	ELSE ''

janbenetka / norm_counts.sql

Created October 15, 2021 09:10

[Normalized Counts per Group] #bigquery #over

	WITH daily AS (
	SELECT
	local_event_date,
	venue_type,
	SUM(device_count) device_count,
	SUM(person_count) person_count,
	FROM `uc-prox-core-dev.international_metrics.traffic_trends`
	GROUP BY local_event_date, venue_type
	)

janbenetka / dict_filter.py

Created October 13, 2021 15:27

[Filter dictionary by keys] #python #dictionary #lambda

	dict_filter = lambda x, y: dict([ (i,x[i]) for i in x if i in set(y) ])
	large_dict = {"a":1,"b":2,"c":3,"d":4}
	keys_to_remove = ("c","d")
	updated_dict = dict_filter(large_dict, keys_to_remove)
	print(updated_dict)

janbenetka / reload_python_module.py

Created September 30, 2021 02:13

[Reload python module] #python

	import importlib
	importlib.reload(scale_by_scale_optim)

	from utils import scale_by_scale_optim

janbenetka / python_function_header.py

Created September 27, 2021 10:33

[Function header] #python #bestpractices

	def get_accuracy(series, recovered_series):
	"""Compare a simulated and recovered series in terms of overall label accuracy.

	Input
	-----
	series : list of lists
	recovered_series : list of lists

	Output
	------