simonthompson99

#-- function to write out csv file with date appended to filename
writeCSV <- function(df, fn){
    ffn <- paste0(fn, "-", Sys.Date(), ".csv")
    write.csv(df, ffn, row.names = F)
}

simonthompson99

#-- function to export tables per factor in a zip
writeTablesPerCat <- function(df, cat, fn, zip_flags = "-j", na_string = "", replace = TRUE){
	stopifnot(cat %in% colnames(df))
	df_ls <- split(df, df[[cat]])
	names(df_ls) <- tolower(gsub(" ", "-", names(df_ls)))
	files <- sapply(names(df_ls), function(x) paste0(tempdir(), "/", basename(fn), "-", x, ".txt"))
	for(i in names(df_ls)){
		write_tsv(df_ls[[i]][,!colnames(df_ls[[i]]) %in% cat], files[i], na = na_string)
	}
	if(replace == TRUE){

simonthompson99

#-- function to round up or down a number nicely
roundNicely <- function(x, nice=c(1,1.5,2,2.5,3,4,5,6,8,10), down = TRUE) {
    stopifnot(length(x) == 1)
    if(down){
        10^floor(log10(x)) * nice[which(x <= 10^floor(log10(x)) * nice)[1] - 1]
    } else {
        10^floor(log10(x)) * nice[which(x <= 10^floor(log10(x)) * nice)[1]]
    }
}

simonthompson99

# Takes a folder tree of pngs and converts them to pdf per folder. e.g.
# |- 1 
# |  |- 1_1.png
# |  |- 1-2.png
# |- 2
# |  |- 2_1.png
# |  |- 2_2.png
#
# will create two pdfs called 1.pdf and 2.pdf in the directory of script.

simonthompson99

#-- wrapper function to create a worksheet with particular formatting
writeXlsx <- function(d, fn, sheet_name = ''){
	require(openxlsx)
	# creates xlsx workbook containing data
	# header styling
	hs1 <- createStyle(fgFill = "#4F81BD", halign = "LEFT", textDecoration = "Bold", border = "Bottom", fontColour = "white")
	cs1 <- createStyle(wrapText = TRUE, halign = 'LEFT', valign = 'top')
	wb <- createWorkbook() 
	addWorksheet(wb, sheetName=sheet_name)
	setColWidths(wb, 1, 1:ncol(d), 'auto')

simonthompson99

# add up number of columns in file (assumes that first lines has correct number of columns)
for file in *.txt; do awk '{print NF; exit}' $file; done | paste -sd+ - | bc
 
# convert xlsx to pipe-separate text
find . -type f -name "*.xlsx" | while read file; do ssconvert -O 'separator=|' "$file" "${file%.xlsx}.txt"; done

# scp down files doing rename beforehand
# in this case for-download is <participant_id>|<file_num>|<source_filename>
cat for-download.txt | while IFS='|' read part id inf; do
    out="${part}_${id}.png"

simonthompson99

#-- capture folder path, filename, and extension from filepath
<filename> <- sub("(.*\\/)([^.]+)(\\.[[:alnum:]]+$)", "\\2", <path>)

#-- add leading zeros to number
sprintf("%02d", <num>) #2 here pads with zeros so that all characters hace 2 characters (e.g.9 > 09), 4 would make 39 > 0039
 
#-- timestamp for file etc.
format(now(), "%Y%m%d-%H%M%S")

#-- get unique string for row in dataframe

simonthompson99

# initiate alembic in project
alembic init alembic

#####################################
# need to edit alembic/env.py so that gets connection string from settings/local_config python module
# add in below around about line 17
# add parent project to sys path
import sys, os
sys.path.insert(0, os.path.dirname(os.path.dirname(__file__)))

simonthompson99

create table schema2.the_table (like schema1.the_table including all);
alter table schema2.the_table owner to dw_user;
insert into schema2.the_table
select *
from schema1.the_table;

simonthompson99

#-- GR database, removing _version tables
python make_erd.py -d ngis_genomicrecord_beta -i 10.5.66.12 -p 5432 -u ngis_data_quality -x address_version alembic_version attachment_version cdr_author_version cdr_content_version cdr_relates_to_version clinical_ethnicity_version clinician_version condition_version consent_document_reference_version consent_note_version consent_organisation_version consent_questionnaire_response_version consent_questionnaire_version consent_version consent_witness_version consenting_party_version contact_version cq_item_enable_when_version cq_item_option_version cq_item_version cqr_answer_version cqr_author_version cqr_based_on_version cqr_item_version db_version identifier_version observation_component_version observation_version organisation_consent_version patient_version person_version procedure_request_version referral_additional_clinician_version referral_attachment_version referral_panel_version referral_participant_attachment_version referral_participant_version referral_sam