Belatedly, it is possible to transpose the source data in applescript, albeit with a heavy dose of the shell. It can be written more efficiently (i.e. without making the intermediary files) but it is probably clearer with them. It should handle a full year of data with minimal effort.
-- set up paths to files
-- `source.txt` is data from input table (on desktop), other files will be created based on data
set pdk to (path to desktop) as text
set dFile to pdk & "source.txt" -- provided
set hFile to pdk & "header.txt" -- generated
set tFile to pdk & "table.txt" -- generated
set cFile to pdk & "complete.txt" -- produced
set dataFile to quoted form of POSIX path of dFile
set headerFile to quoted form of POSIX path of hFile
set tableFile to quoted form of POSIX path of tFile
set completeFile to POSIX path of cFile
-- fed source data, generates header text
do shell script "cat " & dataFile & " | cut -d' ' -f3 | sort -u | rs -C -T | sed -E 's/(.*) /Hour \\1/' > " & headerFile
--> Hour 00:00 04:00 08:00 12:00 16:00 20:00
-- generate table data
-- sed search pattern: find <space>, replace with <tab>
set qsedPatt to quoted form of "s/ / /g" -- quoted sed search pattern
-- NB broken out from command below to make the space and tab clearer
-- fed source data, writes single date and six okWh for each day's data
do shell script "sed " & qsedPatt & space & dataFile & " | cut -f2,4 | rs -eC 0 6 | cut -f1,2,4,6,8,10,12 > " & tableFile
-->
-- 2021-12-09 6.08 5.87 4.09 3.83 2.93 1.62
-- 2021-12-10 6.26 5.71 4.47 3.48 2.63 1.05
-- 2021-12-11 6.38 5.43 4.06 3.04 2.21 1.00
-- merges header text with final data
do shell script "cat " & headerFile & space & tableFile & " > " & completeFile
-->
-- Hour 00:00 04:00 08:00 12:00 16:00 20:00
-- 2021-12-09 6.08 5.87 4.09 3.83 2.93 1.62
-- 2021-12-10 6.26 5.71 4.47 3.48 2.63 1.05
-- 2021-12-11 6.38 5.43 4.06 3.04 2.21 1.00
-- open in numbers as csv
tell application "Numbers"
activate
open completeFile
end tell
Explanation for the shell script components. The rs or reshape command performs the transposition.
-- generate header file
cat datafile : reads in the source data
cut -d' ' -f3 : using <space> as delimiter, excludes all but column 3, i.e. hours
sort -u : sorts result and removes duplicates
rs -C -T : reshapes sorted list, converting spaces to tabs and transposing list
sed -E 's/(.*) /Hour \\1/' : inserts 'Hour' at beginning, strips trailing <tab>
-- generate table data file
sed 's/ / /g' : replaces every <space> with <tab>
cut -f2,4 : using <tab> as delimiter, excludes all columns except 2 and 4, i.e. date and value
rs -eC 0 6 : reshapes table as 6 column array (each 'column' having date and value)
cut -f1,2,4,6,8,10,12 : exclude all dates except first in each row
By the way, looking at the above code, I think the source of its problem was errant parentheses — the “D” and the currentRow need to be together inside them.
tell application "Numbers"
activate
tell the first table of the active sheet of document 1
set recCount to row count
repeat with currentRow from 2 to recCount
set cellInfo to value of cell ("D" & currentRow as string)
display dialog "Rows: " & recCount & " row: " & currentRow & " data: " & cellInfo
end repeat
end tell
end tell
Glad I could help!