
Passing spreadsheets around as CSVs is pretty common these days, and I'm frankly quite thankful for this. With cloud-based office tools now being the norm, and proprietary formats getting increasingly difficult to operate on with our greasy unix tools, it's such a relief to get hold of a good ol' fashioned text file that I can mangle in the terminal.

There is nothing magical about CSV files, or comma-separated value files. They are text files with specifically formatted contents, nothing more. As text files, you can treat them like any other text file.

## Retrieve one or more rows from a CSV

Use the `tail` and `head` commands to chop off the top and bottom rows:

```sh
tail -n+3 test.csv | head -n 2
````

`tail -n+3` means "start at line 3", `head -n 2` means "take just the first 2 lines".

## Removing the column headers

Many CSVs include column headers on their first line

```csv
name,age,city
jon,32,oxford
safi,42,london
laura,28,manchester
```

This is primarily for human use, so when you're passing data from the CSV into other processes, you can skip this row using the `tail` approach we mentioned just a moment ago.

```sh
tail -n+1 test.csv
```

## Retrieve one or more columns from a CSV

You don't need any special apps; macOS and Linux both come with the tools you need to extract this data without much difficulty - the `cut` command is all you need

```sh
tail -n+1 test.csv | cut -d , -f 1,3

# => jon,oxford
#    safi,london
#    laura,manchester
```

`cut` is used for extracting specific sections of each line of a file. In this case, we tell it to use a comma as a delimeter to mark the start of a new field (`-d ,`) and return fields 1 and 3 (`-f 1,3`) 



How to read CSVs from the command line


CW: discrimination, transphobia, self-harm, assault

After a quick lunch of Prêt sandwiches consumed on the pavement outside *The Langham* — a luxury hotel just beyond Regent’s Street — I gathered our litter and wandered down a side street in search of a bin. I was hoping to get back quickly before the amassing crowd of trans+ people and allies in Langham Place began to march towards Wellington Arch.

The five-star hotel had put up temporary fencing to separate the waiting Bentleys from the mob. As I passed, I heard a departing guest shout: “what do you think of the party, Baba?” An older gentleman paused before getting into the back seat of a car. With a smile and a laugh, he called back: “in my country, we’d stone them to death.”

Just down the road, out of sight but not out of mind, a crowd of fascists had convened in characteristic chaos, and were being feverishly mined for content by a click-desperate media. Even before we set off, there had already been reports that a LTP steward had been assaulted, and we were given ominous warnings to stay away from certain places and never to travel alone.

I got back to my group, electing not to tell them what I’d heard, and we joined the march.

This is how London Trans+ Pride 2024 began for me: with reminders of adversity, of real and ready threats of violence, of people unable to think of trans+ people as anything other than attention-seeking purple-haired perverts or curious cultural aberrations to be legislated against and corrected — by force, if necessary, — if indeed they thought of us at all.

That’s how it began, that’s not how it ended.

We started our stop-start march through some of the busiest streets in Europe, setting off late as always. Confused shoppers walked on or stopped to watch and pointed their phone cameras at us. I imagine many were shared online alongside a full gamut of comments. I made sure my ‘NO MORE DEAD TRANS CHILDREN’ placard faced the pavement, the cameras and the curious public.

Having been to LT+P marches for several years, I’m familiar with the rhythms and routine of the day, but this was the first year I’d brought our seven-month-old puppy Alba. She’s been to Oxford Pride and Pride in London before — I made sure she was socialised early to collective action. Alternating between carrying her and being dragged along by her made this a rather more tiring march than previous years, but the inconvenience is nothing compared to the joy she seems to have brought to others in the crowd.

Little will break the ice more than an adorable puppy, I’ve learned in the last few months, so it was no surprise to me how many strangers hopped over and asked if they could say hello to Alba. What did surprise me is the number of people who became visibly emotional at the unconditional love of my chaotic little dog. The armchair psychologist in me thinks that when a person is subjected to so much conditional tolerance at best and complete rejection at worse, there’s something powerful in the uncritical unconditional love of a small dog.

A young nonbinary person tentatively approached me during the speeches, telling me Alba reminded them of their family dog when they were growing up. Possibly projection, but I sensed something deep and longing in the way in which they spoke to me. Their arms and shoulders were covered in a tight patchwork of mean scars that I recognised far too well. I made sure they got as much time with Alba as they could.

Joy, pride, and connection is how I would characterise London Trans+ Pride. In a march comprising people who are at the sharp edge of state-sanctioned prejudice, biased science dressed up as objective reality, populist scorn, and sneering memes pumped into social media by AI bots paid for by shadowy ultraconservatives aiming to manipulate public discourse, you may be forgiven for expecting more vitriol, more violence. You will, however, find the opposite among the 55,000 marchers. Groups of lesbians chanting ’No LGB without the T’ faster and faster until they dissolve into laughter, androgynous giants carrying pithy placards about trans rights that put my pun game to shame, thousands of strangers (purple-haired or not) linking arm-in-arm, metaphorically and literally, to demand respect and rights with an outstretched hand and a smile that only just conceals the rage.

On the fringes of the march, I approached a member of our Met police chaperone and asked which rally they preferred: us or the racists. A loaded question, of course, but without missing a beat, she replied: “this one, without doubt.” She continued:  “the atmosphere is always so friendly, there’s rarely any trouble, it’s such a good vibe.” For a group of people whose rights and freedoms are pummelled daily for headlines and to appease those radicalised by conservative trolls on the Internet, I would agree. In the dichotomy of laugh or cry, the community seems to have chosen laughter, fun, ‘trans joy’ — a phrase you see a lot in t+ spaces.

The march merited little more than a footnote in the BBC coverage; a sub-clause in an article covering the much smaller but louder fascist march happening at the same time. “55,000 trans+ people and allies stage joyous peaceful annual march in London” isn’t as clickable as dripping headlines about far-right ghouls, as the engagement economy has discovered in the past 10 years, to all our cost. The combined number of fascist protestors and counter-protestors doesn’t even come to half the estimated attendance of London Trans+ Pride.

LT+P may not get the same coverage of the highly corporate Pride in London, or even much smaller marches happening on the same day barely a mile away, and I’ve come to realise that that’s fine. The march isn’t for the impartial spectator, it’s not for the average disinterested person scrolling through their feed. It’s not for them, it’s for us. 

I’ve often heard it said that the proliferation of people identifying as trans/nonbinary is all down to Covid cabin fever, the weeks and months we spent terminally online inside while the plague raged outside the front door four strange years ago. It’s hard to ignore the irony of this myth being perpetuated by unwitting trolls radicalised by their Facebook feeds while trans people physically gather in their masses to socialise and demand their rights. I doubt the atmosphere would be quite so friendly at a march demanding the restriction and erasure of certain people and their freedoms; just look at the far-right, something of which I have first-hand experience.

I fall into the thin sliver of the Venn diagram of people to have attended both marches by progressives demanding rights, and ultraconservatives demanding those rights be destroyed. I attended several EDL rallies — a pre-Brexit organisation now largely consigned to history, but whose foul legacy and leadership lurches on — as part of my undergrad studies, in which I hawkishly observed and asked Theroux-esque questions to other bald, white people with identical backgrounds to me. I can validate that Met officer’s opinion. Better are the marches where people lovingly stroke my dog and find in each other a shared struggle for love and acceptance, than those where the only real bond is hate, and cans of lager are thrown at fellow protestors for supporting the wrong football team.

The mirthful chuckle at the thought of legally bludgeoning queer people to death with rocks is now just a jarring duff-note in my memory of Saturday, as are the vague warnings about avoiding certain Tube stations, or rumours about stewards being assaulted by rabid beer-fuelled fascists on the prowl.

What will remain with me from London Trans+ Pride 2024 is a moment from the speeches under Wellington Arch. It’s a vivid flashbulb memory of Olivia Campbell-Cavendish’s cry of community and camaraderie, family and friendship; a question so powerful and prescient that it is less a question and more of a dare. To a crowd of 55,000 trans, nonbinary and genderqueer people and their allies of all stripes, packed in tight to the swelling grounds around Wellington Arch, with the weight of a scornful society, a hostile media, and an indifferent state bearing down on us, she asks: “look around you — do you feel weak?”

That’s what we have. It is irrevocably ours, and it is where our power lies: our solidarity and our community. That’s why LT+P is barely reported on. Our community is the strength we take with us into our jobs, into unfriendly spaces, into places where we dilute or dismiss our authentic selves for the sake of others, or for the sake of our safety.

“Do you feel weak?” In that moment, no one could have.


“Look around you — do you feel weak?” London Trans+ Pride 2024


DynamoDB's `begins_with` query function is tremendously useful. It can be used to query records with certain formats (imagine querying based on an ISO date string), or in Single Table Design to match entity types by some canonical prefix. If you're using DynamoDB streams, especially in a Single Table Design setting, you might expect to be able to use the same concept to match events coming through the stream.

AWS's documentation is, to no one's surprise, clear as mud on this subject. Other Lambda triggers have examples detailing how this can be done, but not DynamoDB, which seems to imply one has to perform an exact key match for a trigger to fire.

```json
{
  "dynamodb": {
    "NewImage": {
      "sk": {
        "S": ["SOME_WHOLE_KEY"]
      }
    }
  }
}
```

Thankfully this is not the case, and although a solution isn't obvious, one does exist.

To match records with a specific prefix, you can modify the matching string with an object with key `prefix` and value of whatever prefix you wish to match.

For example, let's say we're storing a ledger of customer payment records. Our key structure looks like this: `pk: CUSTOMER#1234 sk: PAYMENT#2023-02-03`.

Looking at the documentation, you might assume it's impossible to match all new items with an `sk` beginning with `PAYMENT` but fortunately, it's simply a case of altering our filter pattern to something like this:

```json
{
  "dynamodb": {
    "NewImage": {
      "sk": {
        "S": [
          {
            "prefix": "PAYMENT#"
          }
        ]
      }
    }
  }
}
```

Here, we match all new images with an `sk` beginning with `PAYMENT#` — just what we want.


Prefix matching in Lambda DynamoDB triggers


On the subject of generative AI, I bounce back and forth between gleeful childish optimism, and something approaching apocalyptic terror.

I may well write a full egotistical opinion piece on that another time, but for now I'll summarise by saying that the practical applications of neural networks and LLMs in fields like science and medicine will quite likely be a revolutionary force for good in the medium and long term. But, as with almost all technology, there are countless and unforeseeable negative applications too. Although I don't think the AIs are going to turn sentient and decide to exterminate us, I'm more concerned that LLMs will continue to be exploited by dark political actors and authoritarian nation states for anti-democratic purposes. There's a real risk that convincing populist misinformation can be produced so cheaply and so prodigiously that it eventually reduces human society to something subservient and pitiful, or drive us all into solipsistic madness. Though it's unlikely to be _quite_ that bad, the prospect of machines that can write natural language in a convincingly coherent fashion is staggering, and until I actually started using an LLM, I struggled to keep the fear in check.

Society's presumably impending collapse aside (this is sarcasm, if you're in an AI anxiety phase), the other worry about advanced LLMs and generative AI is that it will replace software engineers. As all software engineers have had to do in the last six months, I've considered the possibility that an AI could come along and automate me out of a job. For a while I was almost convinced; the seemingly weekly quantum leaps in capability made this seem inevitable. Now that the novelty shock is wearing off, and the prospect of AI causing mass unemployment seems less likely, especially in the case of software engineers; I no more think AI will make software engineers redundant than I think DAWs will make musicians obsolete, or a brick-laying machine will make builders a thing of the past; you still need someone who understands music, who knows what a house needs to be and do for the tools to have value.

As a seemingly increasing number of engineers seem to have done already, I'm arriving at the conclusion that generative AI is an assistive tool, something that can help us build the things we build by at least partly automating some source code writing. I've experimented with GPT-3 for a little while, and far from being scared into assuming I'll be out of a job in the next few years, I've actually been relieved that it is both more useful than I hoped, and less useful than I feared. (Side-note: I'm aware that GPT-3.5 and 4 are both more powerful than GPT-3, but the fundamentals of the tests I want to do are roughly the same. From what I've read about GPT-4, I'm ready to raise my eyebrows by no more than a couple of millimetres.)

Modern software engineering, for the majority of us, is about far more than just writing source code. There's a tremendous amount of reasoning and planning; architecture and building, which require cognition and reasoning. At a basic level, these are things that generative AIs are simply can't do, no matter how hard the blow-hards blow. This has become clear over the last few months, and I remain sceptical of some of the larger demonstrations of GPT-generated software whose source code remains closed.

Assuming that AI will end up automating some monotony in my job, a brick-laying machine, I wanted to know how useful it would be as a coding tool, something to generate source code for me. I've started small, and asked it to do something that I've had to implement a couple of times in my career: given an input array, split that array into several smaller arrays of a given size. Let's see how the robot got on with that.

> Write a function in JavaScript to divide an array of items into smaller arrays of variable length.

```js
function chunkArray(arr, size) {
  const chunkedArr = [];
  for (let i = 0; i < arr.length; i += size) {
    chunkedArr.push(arr.slice(i, i + size));
  }
  return chunkedArr;
}
```

If I had Googled this same question and landed on Stack Overflow, this is precisely the answer I would expect; a simple `for` loop that does the job. The formatting is a bit weird and clunky (or was, until my editor formatted it), and it's a little hard to grok but, importantly, it _does_ work. Let's see if we can get it to generate something similar using slightly different syntax:

> Write a function in JavaScript to divide an array of items into smaller arrays of variable length. You must use the Array.reduce method, and must not use a for loop.

```js
function chunkArrayReduce(arr, size) {
  return arr.reduce((acc, el, i) => {
    const div = Math.floor(i / size);
    if (!acc[div]) {
      acc[div] = [];
    }
    acc[div].push(el);
    return acc;
  }, []);
}
```

I've implemented this function in exactly the same way for years, so this is a good result. I find it interesting how it changed the name of the function and the names of the variables, and how it added a guard against undefined chunks without being told that was a risk. I mostly use TypeScript these days, so let's ask it to add in some types.

> Refactor the chunkArrayReduce function in TypeScript. The array must use a generic type.

```typescript
function chunkArrayReduce<T>(arr: T[], size: number): T[][] {
  return arr.reduce((acc, el, i) => {
    const div = Math.floor(i / size);
    if (!acc[div]) {
      acc[div] = [];
    }
    acc[div].push(el);
    return acc;
  }, []);
}
```

The fact that it can correctly apply generic types with minimal prompting is quite impressive. This is already a useful tool; it would have taken me a couple of minutes to write that, and there's a good chance I'd make a reasoning error in there or, worse, reach for a third-party library like Lodash.

It's written a bit of code for me, but crucially, it has no idea why it needed that function, or what to do with it next. This function on its own is useless, some coordinating entity needs to know that this function is required at all. There are logical leaps to be made regarding the necessity of a piece of code, and the subsequent utilisation of that code. Generative AI can do neither; it doesn't think, it algorithmically predicts based on the input parameters.

That's a basic bit of code I'd expect any junior developer a little way into a bootcamp to be able to do, to some degree of quality. Let's try something more difficult. Handling timezones is the infamous bane of a dev's professional life; it's sometimes baffling to try to work out what 'now' refers to, and all the variables that go into that equation. Applying that logic practically when localising times between timezones can be an absolute nightmare.

In a previous job, I had the invidious assignment to determine how to represent the same time (which may be months in the future) to three or more parties in different timezones, and then persist that time in a way that didn't fall foul of things like DST changes and legislative changes that altered how a specific jurisdiction understood its own temporal relationship to the sun. The solutions we came up with ended up being robust but fiddly and computaitonally expensive. It took us weeks to get it right, and to the best of my knowledge, is still being improved upon years later.

As a starter for 10, let's ask the LLM if it can write me a function to get the number of minutes offset from UTC for a given city.

> Write a function in JavaScript that returns the number of minutes offset from UTC for a given city.

```js
function getTimezoneOffset(city) {
  let offset = Intl.DateTimeFormat().resolvedOptions().timeZone;
  if (city) {
    offset = Intl.DateTimeFormat(undefined, { timeZone: city }).resolvedOptions().timeZone;
  }

  const tz = new Date().getTimezoneOffset();
  return tz + Intl.DateTimeFormat().resolvedOptions().timeZone.offset;
}
```

This took me by surprise at first; it looks pretty convincing, it's using the datetime methods available to us through `Intl` and everything, and I can imagine an engineer just whacking this into their work, not giving it any thought, and merrily merging a PR because 'the AI knows what it's doing.' That dev is in for a rude awakening (possibly literally) when they find out that this doesn't work.

There are a couple of misconceptions, misappropriations, and outright errors here, any one of which would make this useless.

The `offset` property is bizarrely set to the name of a timezone (the IANA name, like 'Europe/London', not the name of a city; if we provide a city name, it will not be able to resolve the TZ name). It is then disregarded and never referenced again. The LLM then tries to use a non-existent property in a calculation with the current timezone offset; `timeZone` is a string, so `offset` will always be undefined. The output of this function will always be `NaN`. The AI failed us there; it's not even remotely salvageable.

Maybe I'm being unfair - I deliberately asked it to only take the name of a city to see how it would handle the ambiguity, but that was clearly a step too far. Let's try again.

> Write a function in JavaScript that takes the name of a timezone as its argument, and returns the number of minutes offset from UTC for that timezone.

```js
function getUTCOffset(timezone) {
  let offset = 0;
  if (timezone) {
    const tz = new Date().getTimezoneOffset();
    const timezoneOffset = Intl.DateTimeFormat(undefined, { timeZone: timezone }).resolvedOptions()
      .timeZone.offset;
    offset = tz + timezoneOffset;
  }
  return offset;
}
```

I really don't know where it's getting this from; this is simply incorrect. The result will always be 0 at best, and `NaN` at worst.

Let's try giving it an example.

> Write a function in JavaScript that takes the name of a timezone as its argument, and returns the number of minutes that timezone is offset from UTC. The function should be compatible with Node.js 16 and up.

> For example:
> Input: 'Europe/London'
> Output: -60
> Input 'America/New_York'
> Output: 300

This produced exactly the same result as above, which I'll assume is the end of this particular journey; GPT-3 simply can't do it. There are plenty of correct-ish solutions, including some particularly hacky ones involving Regex-ing the offset out of a localised date using Intl, which is probably what I'd try to do at first!

How about simply localising a given UTC time?

> Write a function in JavaScript that takes the name of a timezone and an ISO8601 string date in UTC as its arguments, and localises the date to the given timezone.

```ts
function localiseDate(timezone, dateString) {
  let date = new Date(dateString);
  let offset = getTimezoneOffset(timezone);
  date.setMinutes(date.getMinutes() + offset);
  return date;
}
```

It tried to use its own non-functional function. Mutating the time alone is also not ideal for many reasons.

This is the kind of task I would expect any professional engineer to be able to do without much thought; it made several glaring errors which any human engineer would just not make, or at least would not be incapable of debugging and solving with use of some documentation or a more experienced colleague.

In a previous attempt at this (a session now lost, sadly) I presented the LLM with a similar prompt, and its response was to give me a static map of North American cities and static offsets from UTC with a basic lookup function attached. Of course, that's next to useless for several reasons, but at least it would return something.

I'm hoping to make this a series. There are many subjects I want to explore, such as getting an LLM to create an integration for third party data sources, write test suites, write larger and more impactful implementations and so on. As a software engineer, I remain unconvinced that AI will replace our jobs, but will instead eventually come to augment our toolkit and, in a best-case scenario, lead to less time wasted on fiddly little functions that can often by the lynchpin of a business-critical application. I particularly want to see if GPT-4 is any better at some of these challenges.


Can AI Code? Part 1


I often need to generate random IDs for some reason or other, whether it's a unique non-sequential ID for a data store, a way of differentiating otherwise identical items in the DOM, or a cryptographically secure token.

The JS/TS ecosystem provides us with many tools to do this.

## UUIDs

A Unique Universal Identifier is a 32-digit code, recognisable by its 8-4-4-4-12 format. UUID v4, the most common random format, is a fully random ID that makes some bold claims about how unlikely a collision – two IDs being the same – is to occur. You can determine the 'version' of a UUID by looking at the first digit of the third block of the ID; you're most likely to see a 4.

Creating a UUID in Node.js is easy. There's the excellent, lightweight [node-uuid]() library that's been around for years, but (spoiler) there is an even easier way that I'll get to next.

```ts
import { v4 as uuid } from 'uuid';

const id = uuid();
// id => 'ddcace95-1db2-4a05-9249-d42e8c9c19a3'
```

As of Node 14-ish, the Node Crypto API exposes a function for generating v4 UUIDs:

```ts
import { randomUUID } from "node:crypto";

const id = randomUUID();
// id => 'ddcace95-1db2-4a05-9249-d42e8c9c19a3'
```

## Long random strings

UUIDs won't work for everything, especially if you're required to provide a single-use code for some authentication protocols, an initialisation vector, or some other cryptographically useful random code that needs to be a certain length and complexity.

At the root of a lot of these solutions is the `crypto.randomBytes` function, which provides pseudo-random data that can be used for cryptographic purposes.

We can then use the returned `Buffer` instance (essentially an array of 8-bit integers) to yield a useful string. For example:

### 32-digit hexadecimal

We can use the `randomBytes` function, along with some string processing functions as follows:

```ts
import { randomBytes } from "node:crypto";

const id = randomBytes(16).toString("hex");
// id => '4d79a9b797c69ee1a8c6d9fecf3f6a3b'
```

You can use this to generate strings of almost any length. You can also use a variety of encodings with the Buffer.toString() method, though the most useful are generally `hex` and `base64`/`base64url`.

```ts
const id = randomBytes(16).toString("base64url");
// id => 'eh7swNTsSu1euChfLea5Sw'
```

Bear in mind that the length of your final string and the number of bytes you generate are not the same. hexadecimal strings will require 2x the number of bytes (eg. 16 for a 32-digit string).

Base64 is slightly less predictable; although it theoretically encodes 6 bits per character, this leaves some remaindered bits which will require padding. If you require a base64 string of a certain length and don't want to do the maths (or want to avoid trailing padding characters,) you can request more bytes than necessary, then trim the excess:

```ts
const id = randomBytes(32).toString("base64url").slice(0, 32);
// id => 'uAomv50rGywlVmhzyAEpm5TkeWTSeinS'
```

## Hard Mode (JS built-ins only)

Let's say the Node Crypto library is not available, and we have to rely on JS built-in objects only. This method can be particularly useful in front-end contexts where the Node APIs are not available, and you wish to avoid importing additional libraries into your bundle.

Using the `Math.Random()` method, we can create random numbers between 0 and 1, which may not sound helpful at first, but we can coerce that number into a useful ID string by casting it as a hexatridecimal (a fancy word for base36) string and slicing off the leading `0.`:

```ts
const id = Math.random().toString(36).slice(2);
// id => jprznc7hjxg
```


Hello, I'm Jon 👋🏻

🧻 Recent Posts

How to read CSVs from the command line

“Look around you — do you feel weak?” London Trans+ Pride 2024

Prefix matching in Lambda DynamoDB triggers

Can AI Code? Part 1

How to generate random IDs in Node.js