Publications by John MacKintosh
Making headlines
In my current mammoth work project, I’m generating many plots. The titles are very descriptive (they tell you what the plot is about), but they are not very interesting. What we’d like, is to analyse the data, bring out the salient points, and have our titles update dynamically .. something along the lines of “some place”” is higher/low...
3519 sym R (3444 sym/5 pcs) 2 img
On target
Here are some notes on getting started with {targets}. The project I am working on involves several different reports, each at least 30 pages, and each with about 20 plots and 20 tables per document. As well as a myriad of functions, I had 7 very large R scripts doing the data munging and processing. I thought they were well ordered, but I had to...
3815 sym R (410 sym/2 pcs) 4 img
Word Up – notes on working with Officer, Officedown and flextable to generate Word documents in RMarkdown
Depending on your age, the song title ‘Word Up’ will either remind you of a bloke in a red codpiece, a Scottish rock band with a left field cover version, or possibly, Little Mix, (according to Google). However, I urge you to put down your codpieces (yes, even you at the back) and instead gather round for tales of woe working with Microsoft W...
8719 sym R (1779 sym/13 pcs) 4 img
Cover Up
I needed to add full size (A4, portrait) images to the first page of my Word document ( there are 13, all 30+ pages, which I’m generating in RMarkdown, as per previous post). I didn’t want to do this by hand, especially when I discovered that due to margins and page settings, each image would have to be resized to fit on the page. (I tried a...
2363 sym R (2518 sym/5 pcs)
(h)appy with lapp(l)y
Here’s a quick example of needing to make some updates to an existing column in a data.table. For reasons I don’t want to get into (involving MS Word :/) I had to change a couple of columns from factor to character, and swap out the ampersand symbol and replace with ‘and’. Dplyr afficionados will be excitedly jumping up and down at the th...
1713 sym R (619 sym/3 pcs)
Dynamic schema, table and column names in SQL Server queries as a gateway to functional programming with R
I’m looking into creating some functions to make it easier to carry out quality checks on our database tables. I’m using SQL Server, where tables are referred to in a [database].[schema].[tablename] format, although you can forgo some of this by using the USE statement – USE my_data_base GO Let’s say you want to run a simple query: SELEC...
2151 sym R (808 sym/7 pcs)
Situation
You want to run a query over multiple tables in your database / warehouse and then process and visualise the combined results of those queries. You need to be able to switch between servers, databases, schemas and tables, selecting different columns and applying different conditions in the WHERE clause. You don’t have permsissions to write co...
1115 sym
Exploring your database with shiny
Several of my recent blog posts have been focussed on using {purrr} and {glue} to programatically query a database. This is something I’d been wanting to achieve for some time, but my efforts got put on hold when COVID-19 came along. The general approach is to use a config file to denote the relevant tables and columns, along with the server an...
2561 sym
Dual axis charts – how to make them and why they can be useful
I had to produce a dual axis chart as part of a piece of work transitioning an Excel report to markdown. I thought I’d do a walkthrough of a chart that is similar in style, but different in content than the one I produced at work. I’m using COVID-19 open data from Public Health Scotland. You can find this via the link in the code below, where...
4262 sym R (6064 sym/10 pcs) 14 img
collapse might rock your world
The NHS-R Community site prompted this post, specifically a question to the help-with-r channel on Slack. Someone asked about obtaining unique values in a 60M row / 6 column dataset. Naturally, data.table came up, and a few of us made suggestions for improvement, but we didn’t seem to be hitting on a magic solution. Without being able to get m...
2912 sym R (1687 sym/9 pcs)