Andrew B. Collier / @datawookie

I prefer to have my primary key columns first in a table. I recognise that column order is irrelevant to the performance of the table, but I prefer this for personal aesthetic reasons. However, from SQLAlchemy 2.0.0 there’s a change in the way that column order works with inherited base classes.

A country’s Real Effective Exchange Rate (REER) is the exchange rate averaged over a selection of other countries, taking into account trade volume and adjusted for inflation via the Consumer Price Index (CPI). Zsolt Darvas has developed and maintains a REER dataset for 178 countries.

Do you ever contribute to a Git repository from different machines? Yeah, you probably do. Sometimes you’re on your work machine. Other times you’re on your personal laptop. Or your gaming desktop. And you might have a different Git identity on each of those. And this means that your Git log ends up looking a bit messy. Who are all of these people with similar names but different email addresses? A .mailmap file can be used to tidy things up.

In a previous post we looked at using a Docker image to manage a Gatsby site. But perhaps you want to install Gatsby directly onto your machine? No problem. Here we’ll look at how to do that on Ubuntu.

Getting Gatsby (also GatsbyJS) installed and running can be a challenge. With older versions of Ubuntu I have fought extensively with Node package versions. Docker seems to be a natural solution. This post shows how to build and run a simple Docker image for serving a development Gatsby site.

If you use BASH, then you’re probably already using the command history. BASH history allows you to access a list of previous commands executed in the shell. It can make you more productive and efficient: do more and do it quicker.

The default configuration of BASH history will suit most purposes. But, like most things in the Linux universe, it’s possible to tweak that configuration to suit your specific requirements. In this post I’ll present some of those configuration options.

Do you do any web scraping? If so, then you probably spend a lot of time scratching around in your browser’s Developer Tools, figuring out the DOM structure and understanding how various bits of a site are delivered. Wouldn’t it be cool to access the Developer Tools functionality from inside your scraper? Well, you can. The Chrome DevTools Protocol (CDP) provides a low-level interface for interacting with Chrome. And you can tap into that interface via Selenium.

Message IDs (MIDs) and Content IDs (CIDs) are used to identify and refer to email messages and specific pieces of content within those messages. They are formally described in RFC 2392.

The {emayili} package already has adapters which make it simple to send email via a variety of services. I have just added an adapter for the ZeptoMail transactional email service. The adapter is available in {emayili} version 0.7.13.

I recently upgraded to Ubuntu 22.10. Everything went very smoothly, but there are some minor teething problems. One of them is getting the AWS Workspace client to work. This is how I fixed that.

There’s one major problem with ChromeDriver: anti-bot services are able to detect that a browser session is being automated (as opposed to being used by a regular meat sack) and will often impose restrictions or deny connections altogether. The Undetected ChromeDriver (undetected-chromedriver) Python package is a patched version of ChromeDriver which avoids triggering a selection of anti-bot services, allowing it to glide under the anti-bot radar.

At Fathom Data we have been doing a lot of automated documentation and automated reporting. Although many of these documents are rendered to HTML, there’s an increasing demand for PDF documents. We’ve had to raise out game in that department. The {pagedown} package has become invaluable. This is a short note showing how we tweak the page size and margins for PDF documents.

I’m a density plot devotee. And, using geom_density() from {ggplot2} these plots are effortless to produce. However, sometimes the results of geom_density() are not exactly what I’m after. Here’s how I tweak them to give me precisely what I need.

From time to time I find empty paragraph tags (<p></p>) inserted into my HTML when knitting an R Markdown document.

I’m not a web developer. However, I do regularly use HTML and CSS to layout pages which are then transformed into PDF documents. One of the requirements that I encounter fairly often is vertically aligning images and text. Fairly often, but not often enough to remember the solution. I inevitably have to rediscover the solution (thanks StackOverflow!). Jotting this down for posterity.

In the previous post we looked at how to apply a linter and styler to a Python Project. Now we’re going to do the same for an R project. We’ll use the {precommit} R package to make the setup a breeze.

A linter and a styler can help you to write cleaner and more consistent code. In this post we’ll look at how to set up both for a Python project.

While trolling the internet aimlessly this morning, this TikTok video caught my attention.

In a previous post I took a look at some granular weather data that I acquired via the Weather API. One interesting application of these data is calculating the Fire Danger Index (FDI), which measures the degree of fire danger using information on dryness, wind speed, temperature and humidity.

A quick note on how to use the Shiny Server Docker image, rocker/shiny.

I’m a big believer in starting with the simplest possible setup, getting that to work and then adding complexity in layers. We’ll start with a simple Shiny application in app.R.

I’ve just added some more data to the {blimey} package:

• postal codes and
• postbox locations.

I’ve just added data on schools in England to the {blimey} package. The raw data were obtained from gov.uk.

Getting R set up on Linux can be somewhat frustrating. Many of the fundamental packages (like {devtools} or {remotes}) have implicit system dependencies. Installing these packages can involve numerous iterations back and forth between R and the shell while you figure out what those dependencies are and get them all installed.

I’ve been through this process many times now and finally just created a quick script that will get most of it done quickly and easily.

I’m building a model which requires historical weather data from a selection of locations in South Africa. In this post I demonstrate the process of acquiring the data and doing some simple processing.

I occasionally write scripts where I need to persist some information between runs. These scripts are often wrapped in a Docker image and deployed on Amazon ECS. This means that there is no persistent storage. I could use a database, but this would be overkill for the volume of data involved. This post describes a simple approach to storing these data on S3 using a pickle file.

Introducing the nascent R package {blimey} (repository). At this stage it contains only the following data:

• railways — latitude and longitude segments along railway lines (wide format);
• railways_pivot — latitude and longitude segments along railway lines (long format); and
• railway_stations — codes, names and locations of railway stations.

In this post I show how to connect to the Interactive Brokers API from MATLAB. The “obvious” way generates a SSL error. This is an alternative approach using the system curl command. It’s a bit of a hack but it does the job.

The Interactive Brokers Client Portal API Gateway provides access to a REAST API for IBKR accounts. It makes it possible to retrieve account information, manage portfolios, execute trades, and access market data via standard HTTP requests.

In a previous post I documented my local setup for accessing the Interactive Brokers API via their Gateway application. I’m now in a position where I need to deploy my code onto a VM. My local setup will no longer suffice.

A brief tutorial on setting up the Interactive Brokers Gateway on Linux.

In the previous post I ran through the process of setting up a Mailfence account for encrypting emails using asymmetric encryption. In this post I show how Mailfence can be used with the {emayili} package for sending encrypted email from R.

If you’re thinking about using encrypted email, then Mailfence appears to be a pretty good option for getting started in a relatively painless way.

In a previous post I described a recipe for setting up GitLab Runner using a Docker container. With that setup it was possible to register multiple runners on a single container. However, each runner needed to be registered manually. This setup makes complete sense if the container will be around for a while. But what if you’re spinning up a GitLab Runner container for only a short duration? In this case it might be preferable to have the container pre-configured (or at least easily configured) to provide a runner to a specific project or group. Setting that up is the goal of this post.

Materialised views are a great alternative to views if the underlying query takes a long time to run. However, the principle problem with materialised views is that their content gets stale… and if the database is active, then it gets out of date rather quickly. Sure you can manually refresh a materialised view, but who has the discipline or time to do that? Better to automate the process. Then you can safely forget about it, secure in the knowledge that the data in the materialised view will remain current.

I’ve just started collaborating on a new project, Votela, with Luke. We’re going to be using Firestore for stashing our data. I’ve never worked with Firestore before, so one of my first tasks was just figuring out how to get connected and how to shift some data to and from the database.

Drug pricing is complicated. In this post I take a look at reconciling ASP and WAC prices, focusing on normalising the WAC price per billing unit to achieve a price which is comparable to ASP. This post includes a number of case studies with the objective of laying out and testing a methodology for understanding and dealing with these data. There’s a fair amount of repetition, but I wanted to test the approach across a number of drugs.

A recent announcement from Google stated that from 30 May 2022 they will no longer support login via username and password (this is the “less secure” option). The change will have an impact for people using the {emayili} package to send email from R, but will also affect many others who use this form of authentication to access their emails via desktop email clients. In this short post I detail how to work around this by using an application password.

I use Continuous Integration (CI) extensively across almost all of my remote Git repositories. These are the typical jobs which it’s used for:

• running tests
• building documentation and
• acquiring data.

This post addresses the last item, acquiring data.

If you have database tables in which you are frequently adding or updating data, then it can be useful to have columns which indicates precisely when a specific record was created and updated.

I’ve been using the image registry on GitLab for quite a while now and loved the convenience of having my images living in the same place as my code. However, recently GitLab introduced a soft limit on transfers and that’s cramping my style. I’m moving a lot of my images onto Amazon Elastic Container Registry (ECR). In this post I look at how to get this set up.

At Fathom Data we tend to do quite a lot of web scraping. At the moment I’m working on a small project which requires assembling a large selection of RSS feeds. Aggregator sites (like Feedspot and Feedly) have extensive, carefully curated lists of RSS feeds. As we’ll see below, the underlying lists are not entirely trivial to access.

This is the recipe I use to upgrade R on a Linux box. It’s something that I do fairly frequently on fresh EC2 instances.

Services like Mailchimp and MailerLite make it easy to create stylish email campaigns. Their templating tools allow you to create elegant HTML messages which are personalised to the recipient.

Wouldn’t it be cool if you could do something similar when sending emails from R? Well, with the latest version of {emayili}, that’s now possible (although this feature is definitely in its infancy!).

The {emayili} package for sending emails from R works well within a Shiny app. You just need to set it up right.

No two email clients are equal. Nowhere is this more true than in the way that they treat images in HTML messages.

I’m using the source for GPXSee to figure out some details of the QCT format. The build instructions in the README are somewhat terse. Here are more details.

I’ve got a stash of old (2004 vintage) UK Ordnance Survey maps. They are really works of art and the folk at the Ordnance Survey should be commended on the level of detail embedded in these maps. There’s just one small snag: the maps are in a rather obscure format. The proprietary Quick Chart (.qct extension) format is intended for use with Memory Map navigation software. If you want to use these maps for other purposes then you are stuck.

In a previous post I documented what I had learned while trying to understand the structure of encrypted emails. I then took an informal Twitter poll to gauge how many people are using encrypted email messages.

I’m adding encrypted message support to the {emayili} package for sending emails from R.

At Fathom Data we have a number of workflows that require us to share various bits of data for a short time. The data are not sensitive, so we can freely share them. We have been doing this manually via platforms like Google Drive, Box or Dropbox. However we need to remember to go back and delete the file some time later. This is not ideal. What we needed was a simple “fire and forget” solution which would allow us to share the files and they would disappear automatically after some time. Well, this is precisely what Filebin does.