Numbers - transposing a worksheet


I am new in manipulating a MAC number worksheet in AppleScript. I’ve done a research for “Numbers” on this forum and could not find any examples.

I want to transpose all rows of a worksheet into columns.

The current worksheet contains 4 fields (Obs, Date, Hours and okWh). Each row contains a time period for a given day. The hours are always the same (00:00, 04:00, 08:00, 12:00, 16:00 and 20:00) for up to 2190 rows (365 days). The file is to filled once a month.

Kind regards!

tell application “Numbers”
tell the first table of the active sheet of document 1
set numbofrec to row count

	repeat with currentrow from 1 to row count
		set info to value of cell "D" & (currentrow as string)
		display dialog "Rows: " & numbofrec & " row: " & currentrow & " data: " & info
		set currentrow to currentrow + 1
	end repeat
end tell

end tell
end tell[/AppleScript]

Model: MacBook Pro Monterey version 12.2.1
Browser: Safari 605.1.15
Operating System: macOS 12

If you have a recent version of Numbers (eg 11.2) you can do this much more effectively with a pivot table

You could do it with AppleScript, but it would be painfully slow as it would involve creating a new table and then duplicating the data for at least 2190 individual cells (more in leap years…)

WOW, I just do not understand why I did not think of that.

You are absolutely correct!

I’ve just done it and it was a peace of cake.

Many thanks!

:slight_smile: Glad I could help!

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"
	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"
	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

While there are awesome methods in the replies, looking at the dictionary for numbers now shows a command to transpose a table.

In other words, it appears that numbers can do this for you already.

The command transpose v appears in the Numbers suite of the Numbers dictionary. The description of the command says “transpose the rows and columns of the table.”

That’s true but what the user required isn’t exactly transposition. Numbers’ transpose would take a table whose dimensions are 4 x 186 (4 columns with six rows per day, or 186 for his sample month of december) and turn it into a 186 x 4 table (note that I’m excluding headers). Ultimately, the table needs to be reshaped and many cells must be dropped (i.e. every date cell except one per day, every hour cell). Each day in the source has 24 cells, and each day in the output has 7.

The shell’s rs and cut commands accomplish that. It’s easy enough to replicate the cut command in numbers but I’m unaware of any feature that would substitute for rs. While it took me a while to figure out how (although I actually spent the most time trying to generate source data), you can actually perform the transformation with a single command. Incidentally, that’s also what a pivot table does and why it was a solution for the issue.

1 Like

Ah… I see it now. Nice use of rs- always amazes me how many handy tools are right there in the shell!!

That’s really true. I was unaware of it until about a month ago. Trying to understand how to take advantage of it was what prompted me to work on this problem. Also just learned about lsappinfo in the Second frontmost Application thread.