Build your own pivot function using scripting in Google BigQuery

A common requirement when analysing data is the need to take information stored in separate rows and flip it to so that it is displayed in columns. This is often referred to as pivoting. In a spreadsheet, it’s really simple: you just choose Pivot Table from one of the menus, select the fields you want to see and a few other options, and the job is done. Things are much less straightforward when you are working with databases.

This post works through an example to provide a step-by-step guide to using the scripting features in Google’s BigQuery to pivot data.

The source data


If you have heard of word vectors, you will probably know they enable a type of algebra on words. So, for example,

vector(France) — vector(Paris) + vector(Berlin)

gives an answer which is closest to the word Germany.

What is less well known¹ is that you can use the same word vector algebra to fix spelling mistakes. This post describes how to build a lookup table containing the most common spelling mistakes and their corrections using the pre-trained GloVe vectors from Stanford.

To accompany the post there is a jupyter notebook on github which walks through the code. …

Ed Rushton

Data scientist —formerly economist — interested in consciousness

Get the Medium app

A button that says 'Download on the App Store', and if clicked it will lead you to the iOS App store
A button that says 'Get it on, Google Play', and if clicked it will lead you to the Google Play store