simonthompson99’s gists

simonthompson99 / view-pandas-df.py

Last active January 15, 2021 11:46

[View Pandas Dataframe in Browser] View pandas df in a datatableesque browser window #python #html

	from tempfile import NamedTemporaryFile
	import webbrowser

	base_html = """
	<!doctype html>
	<html><head>
	<meta http-equiv="Content-type" content="text/html; charset=utf-8">
	<script type="text/javascript" src="https://ajax.googleapis.com/ajax/libs/jquery/2.2.2/jquery.min.js"></script>
	<link rel="stylesheet" type="text/css" href="https://cdn.datatables.net/1.10.16/css/jquery.dataTables.css">
	<script type="text/javascript" src="https://cdn.datatables.net/1.10.16/js/jquery.dataTables.js"></script>

simonthompson99 / pandas-example-df.py

Last active January 15, 2021 11:48

[Example Pandas Dataframe] Test example dataframe for pandas #python #pandas

	import pandas as pd
	raw_data = {'first_name': ['Jason', 'Molly', 'Tina', 'Jake', 'Amy'],
	'last_name': ['Miller', 'Jacobson', 'Ali', 'Milner', 'Cooze'],
	'age': [42, 52, 36, 24, 73],
	'preTestScore': [4, 24, 31, 2, 3],
	'postTestScore': [25, 94, 57, 62, 70]}
	df = pd.DataFrame(raw_data, columns = ['first_name', 'last_name', 'age', 'preTestScore', 'postTestScore'])

simonthompson99 / psql-connections.sql

Last active January 15, 2021 12:04

[psql Connection strings] Connection strings for psql #sql #database #genomics_england

	-- Index from query
	psql metrics -h 10.1.24.39 -p 5433 -U cdt_user

	-- Index from local
	psql metrics -h localhost -p 5441 -U cdt_user

	-- local postgres
	psql metrics -h localhost -p 5432 -U cdt_user

simonthompson99 / read_in_xlsx.r

Last active January 15, 2021 12:04

[Read in xlsx file] Read in first sheet of excel file into dataframe #excel #r

	read_in_xlsx <- function(f) {
	# read in an Excel file into a dataframe
	require(openxlsx)
	d <- read.xlsx(f)
	cat(paste0(f, " - ", nrow(d), "Rx", ncol(d), "C\n"))
	return(d)
	}

simonthompson99 / common-sqlalchemy-queries.py

Last active January 15, 2021 12:05

[SQLAlchemy Query Cheatsheet] Common SQLAlchemy constructs for querying #python #sqlalchemy #cheatsheet

	# case when
	from sqlalchemy import case
	d = s.query(config_db.ClinicalIndication.clinical_indication_uid,
	case([(config_db.ClinicalIndication.clinical_indication_code.startswith('M'), 'C'),
	(config_db.ClinicalIndication.clinical_indication_code.startswith('R'), 'R')],
	else_ = None).label('programme')).all()

	# convert query to pandas df
	import pandas as pd
	pd.DataFrame(q.all(), columns = ['col1', 'col2'])

simonthompson99 / temp-zip-file.py

Last active December 5, 2024 05:34

[Zip to temporary file] Make a temporary zip file and write to it #python #file_operations

	import tempfile
	import zipfile

	# make archive.zip in temp directory
	tmpdir = tempfile.mkdtemp()
	zip_fn = os.path.join(tmpdir, 'archive.zip')
	zip_obj = zipfile.ZipFile(zip_fn, 'w')

	for f in files:
	zip_obj.write(f, os.path.basename(f)) # add file to archive, second argument is the structure to be represented in zip archive, i.e. this just makes flat strucutre

simonthompson99 / send-file-to-slack.py

Last active January 15, 2021 12:07

[Send file to Slack] Upload a file to slack channel #python #slack

	def send_file_to_slack(fp, channel, fn = None):
	"""
	Send a file to a slack channel
	- fp - path to file
	- channel - channel to send to
	- fn - the filename
	"""
	import requests
	import os

simonthompson99 / update-query-with-join.sql

Last active January 15, 2021 12:09

[Update query with join] Update db table based on joins #sql #database

	-- update a table using a standard query
	with t as (
	select <the primary key of the table> as rowid, <value to use to update> as update_value
	from <table>
	left join ....
	where ....
	)
	update <table to update>
	set <field to update> = t.update_value
	from t

simonthompson99 / run-query.r

Last active June 10, 2021 08:47

[Run query from file] Run query from sql file on specific connection #r #sql #database

	run_query <- function(conn, f) {
	# run the contents of sql file - f- on a given db connection - conn
	require(DBI)
	q <- trimws(readLines(f))
	# remove lines starting with --
	q <- gsub("^--.*$", "", q)
	d <- dbGetQuery(conn, paste(q, collapse = " "))
	cat(paste0(f, ' - ', nrow(d), 'Rx', ncol(d), 'C\n'))
	return(d)
	}

simonthompson99 / md5.py

Last active January 15, 2021 12:11

[Generate md5sum of file] Generate MD5 sum for a given file path #python

Simon Thompson simonthompson99