Diane posed me a problem. She had a large spreadsheet filled with data items in three columns, like so:

LekID Year Number
Well 2001 1
Well 2002 2
Well 2003 3
Mill 2001 4
Mill 2002 5
Mill 2003 6
Plain 2001 7
Plain 2002 8
Plain 2003 9

But what she wanted was a table like this:

Year Mill Plain Well
2001 4 7 1
2002 5 8 2
2003 6 9 3

Now think of having about thirty sites and years going back to the 1960s. This is something tedious and error-prone to rearrange by hand.

Enter a Perl script to help the job along.

Step 1 is to export the data in a comma-delimited file format so that Perl can read it easily. Include the header line.
Step 2 is to figure out which column in the CSV file holds row keys, which hold column keys, and which holds data. For the example, the first column has the column keys, the second column has the row keys, and the third column has the data.
Step 3 is to call the Perl script to transform the input data. Output is on STDOUT, so redirect it to a new CSV file. (Don’t redirect to the same file; the OS will wipe the file out in preparing it for output, and the Perl script will see nothing when it tries to open it.) The first parameter is three letters giving the info on which column does what job, and the second is the input CSV filename.

Here’s the command line I used for this:

xform.pl crd leks.csv > leks.xform.csv

The Perl script itself is here.

I tried three different plugins to try and make it show up verbatim, as code should, here in the display. None of them worked to my satisfaction.