Diane posed me a problem. She had a large spreadsheet filled with data items in three columns, like so:
But what she wanted was a table like this:
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.