Changing the month format: a fairly general solution

For a full list of BASHing data blog posts, see the index page.

I sometimes need to change the month format in a dataset, for instance from “Jan” to “01”, or “3” to “March”. There are various clever ways to do this on the command line, but I’m not good at remembering clever. To save time I wrote a table with the 6 different month formats I see most often. It’s the table you see below, and if you highlight and copy it, you should be able to paste it into a text editor as a tab-separated table. Save the file as “months”.

1	01	Jan	January	i	I
2	02	Feb	February	ii	II
3	03	Mar	March	iii	III
4	04	Apr	April	iv	IV
5	05	May	May	v	V
6	06	Jun	June	vi	VI
7	07	Jul	July	vii	VII
8	08	Aug	August	viii	VIII
9	09	Sep	September	ix	IX
10	10	Oct	October	x	X
11	11	Nov	November	xi	XI
12	12	Dec	December	xii	XII

My general strategy is to use AWK to change formats, and to load “months” into an appropriate array in the AWK command. Below are a few examples.

Expand abbreviated month

awk ‘FNR==NR /-/ ‘ months file

Split “full” date into ISO 8601 components

awk -F”t” ‘FNR==NR FNR==1 FNR>1 ‘ months file

Convert “full American” date into Roman month-numeral date

awk -F”t” ‘FNR==NR FNR==1 FNR>1 ‘ months file

Convert date range into ISO 8601 interval date

awk ‘FNR==NR /[0-9]-[0-9]/ / – / && length($0)<21 / – / && length($0)>21 ‘ months file

To remind myself which fields are which in “months”, I just cat the file before building the AWK command:

Last update: 2018-12-30

Source