Zark Newsletter Extracts
Vector 19:4, page 32
Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 32
Here is another Zark newsletter extract. As always, don’t hesitate to send in any solutions you may have that you may be inspired to investigate. There may be new more elegant ways of solving an old problem, which may also turn out to be faster. Please don’t be afraid to try and shed "new" light on the "old" problems. Once again, thanks to Jonathan Barman for helping typeset this column.
Utility Corner: Exporting Numbers¶
(The purpose of this column is to make you more productive by introducing you to utility functions. Think of utility functions as APL functions that have names instead of symbols. By expanding your function vocabulary, you’ll be able to write APL code that’s more concise, more efficient, and more readable.)
In last issue’s Limbering Up column, you were asked to define the utility functions TOCOMMA and FROMCOMMA. These functions facilitate your ability to communicate with the outside (non-APL) world.
Suppose you have a matrix of number that you want to export from APL to a non-APL application. The function TOCOMMA translates the numeric matrix to a "comma-delimited text file" (a character vector) that can be read by the non-APL application.
For example, if this is the numeric matrix to convert,
The TOCOMMA function returns the following comma-delimited character vector:
(The lines appear double-spaced because of the inclusion of both carriage return and line feed characters at the end of each line. ASCII files typically contain both of these characters at the end of each line.)
FROMCOMMA does the inverse of TOCOMMA. Given a comma and carriage return/line feed delimited character vector as its argument, it returns the numeric matrix of the numbers within the character vector. For example,
Obviously, TOCOMMA and FROMCOMMA do not solve the problem entirely. The character vector must still be written to or read from a file. In APL*PLUS PC or APL*PLUS II, this can be accomplished as follows:
Exporting a numeric matrix:
⍝ Convert the numeric matrix to a
⍝ comma-delimited character vector:
CVEC←TOCOMMA NMAT
⍝ Create a "native" (DOS) file:
'C:\FILENAME.TXT'⎕NCREATE ¯1
⍝ Write the character vector to the
⍝ file and deactivite the file
CVEC ⎕NAPPEND ¯1
⎕NUNTIE ¯1
Importing from a comma-delimited file:
⍝ Activate the "native" (DOS) file:
'C:\FILENAME.TXT'⎕NTIE ¯1
⍝ Read its contents as a character
⍝ vector, and deactivate the file:
CVEC←⎕NREAD ¯1,82,⎕NSIZE ¯1
⎕NUNTIE ¯1
⍝ Convert the comma-delimited charac-
⍝ ter vector to a numeric matrix:
NMAT←FROMCOMMA CVEC
Let’s look at the FROMCOMMA function first, since its logic is somewhat simpler:
∇ NMAT←FROMCOMMA CVEC;D;N;⎕IO
[1] ⍝ Converts comma-delimited char vec
[2] ⍝ CVEC, whose records are delimited
[3] ⍝ (followed) by carriage return/line
[4] ⍝ feed paris, into a numeric matrix
[5] ⍝ with one row per record.
[6] ⎕IO←1
[7] ⍝ Grab the first record (⎕TC[2]=
[8] ⍝ ⎕TCNL=carriage return):
[9] N←(¯1+CVEC⍳⎕TC[2])↑CVEC
[10] N[(N=',')/⍳⍴N]←' ' ⍝ Repl ',' by ' '
[11] N←⍎N ⍝ ...and cvt to num (or ⎕FI)
[12] N←⍴N ⍝ This many columns in result
[13] ⍝ Replace all delimiters by blanks
[14] ⍝ (⎕TC[3]=⎕TCLF=line feed), and also
[15] ⍝ blank out the EOF char (⎕AV[27]):
[16] D←',',⎕TC[2 3],⎕AV[27]
[17] CVEC[(CVEC∊D)/⍳⍴CVEC]←' '
[18] NMAT←,⍎CVEC ⍝ Convert to numeric
[19] ⍝ Reshape to a matrix:
[20] NMAT←(((⍴NMAT)÷N),N)⍴NMAT
∇
On lines [9] to [12], FROMCOMMA determines how many numbers there are per record. It grabs the first record (up to the first carriage return character), replaces the commas by blanks, converts the characters to numbers by executing them (thought the APL*PLUS system function ⎕FI would do the job just as well, and probably more efficiently, since it’s simpler), and checks the shape of the result. FROMCOMMA makes the assumption that all records contain the same number of numbers. The result of ⍎ is ravelled to handle the case in which the record contains just one number. The logic on line [12] assumes that N is a vector.
On lines [16] to [18], the entire character vector is converted to a numeric vector in one fell swoop. To do this, the non-digit characters (commas, carriage returns, line feeds, and EOF [end-of-file] markers) are replaced by blanks, and the result is executed. Again, ⎕FI could be used here in place of ,⍎. The EOF marker (⎕AV[27]) is included at the end of DOS ASCII/text files by most non-APL applications.
On line [20], the numbers are reshaped into a matrix, with one row per delimited records. The number of records is determined by dividing the total number of numbers by the number of numbers per record.
To TOCOMMA function is a bit trickier:
∇ CVEC←TOCOMMA NMAT;I;N;R;⎕IO;⎕PP
[1] ⍝ Converts numeric matrix NMAT into
[2] ⍝ a comma-delimited character vector.
[3] ⍝ Each row of NMAT becomes one record
[4] ⍝ in the result, where the records
[5] ⍝ are delimited (followed) by a
[6] ⍝ carriage return/line feed pair.
[7] ⎕IO←1
[8] N←¯1↑⍴NMAT ⍝ No. of columns in NMAT
[9] ⎕PP←17 ⍝ Set print precision to max
[10] ⍝ Num mat → char vec, with one blank
[11] ⍝ after each number:
[12] CVEC←(⍕,NMAT),' '
[13] I←(CVEC=' ')/⍳⍴CVEC ⍝ Inds of blanks
[14] CVEC[I]←',' ⍝ Insert comma delims
[15] ⍝ Inds of trlng comma for each rec:
[16] I←((⍴I)⍴(-N)↑1)/I
[17] ⍝ I←I[N×⍳(⍴I)÷N] ⍝ Alternative
[18] R←(⍴CVEC)⍴1 ⍝ Replication vector
[19] R[I]←2 ⍝ Double trailing commas
[20] CVEC←R/CVEC ⍝ ... for CR/LF
[21] ⍝ Adjust indices for expanded vec:
[22] I←I+⍳⍴I ⍝ Indices of LF's
[23] ⍝ Replace comma-paris by CR/LF pairs
[24] ⍝ (⎕TC[2 3]=⎕TCNL,⎕TCLF=CR/LF):
[25] CVEC[I∘.-1 0]←((⍴I),2)⍴⎕TC[2 3]
∇
On line [12], the numeric matrix is ravelled and converted to a character vector all at once. Before doing so, though, the value of ⎕PP is set to its largest setting (16 on some APL systems) so numbers with decimal places will be formatted to the maximum possible precision. For example, the number from 22÷7 will be formatted as 3.1428571428571430, instead of the default 3.142857143.
On lines [13] and [14], the blank delimiters are replaced by commas. Since monadic ⍕ returns one blank between each number, and since a blank is catenated to the end of the vector on line [12], there is now one comma after each number.
On line [16] (or [17] if that algorithm is more efficient on your machine), the vector of indices of the comma delimiters is compressed to retain just the indices of those commas that mark the end of each record. Each of these commas must be replaced by a carriage return(CR) and a line feed (LF).
To replace single commas by CR/LF pairs, the character vector must be expanded. On lines [18] to [20], replicate (/) is used to perform this expansion by doubling up each of the end-of-record commas.
Once the character vector is expanded, the indices that point to the end-of-record commas are no longer valid. On line [22], the indices are adjusted (adding 1 to the first, 2 to the second, and so on) so they point to the second of the two commas that now exist at the end of each record.
On line [25], outer product is used to build a two-column matrix of the indices of both end-of-record commas. A corresponding two-column matrix of CR/LF pairs is constructed and inserted. This direct insertion of a matrix into a vector is possible because indexed assignment (A[B]←C) permits its index argument and its assigned argument to be of any rank, as long as they are scalar compatible (i.e. as long as the expression B=C would not cause an error).
ToComma and FromComma¶
from Jon Longley and Mahesh Navani - Jennison Associates
∇ R←FROMCOMMA X;COLS;ROWS
[1] COLS←(ROWS++/','=X)÷ROWS←+/⎕TC[2]=X
[2] ((,X∊',',(⎕TC[2 3]))/,X)←' '
[3] R←(ROWS,COLS)⍴⍎,X
∇
∇ K←TOCOMMA K
[1] K←⊃,/(¯1↓¨,/(⍕¨K),¨','),¨⊂⎕TC[2 3]
∇
ToComma and FromComma¶
from Adrian Smith – Causeway
This one bothers me a little, because it seems to understate (quite substantially) the problem. The real challenge is in dealing with the mess that Excel throws out when you ask to save a CSV file and choose comma rather than tab as the separator. Things get a lot worse when there is a chance that someone may mess with the file by hand, but even in the ‘simple case’ you must deal with garbage like this:
This is a nasty file!
When,How much,Who
15-Sep-2000,$4.00,Fat cat
17-Sep-2000,$3.00,"7, Castlegate"
19-Sep-2000,$4.50,"Who put that '""' there?"
21-Sep-2000,"$1,500.00"
23-Sep-2000,$6.00
25-Sep-2000,$7.00
27-Sep-2000,"$23,016.45"
29-Sep-2000,$5.00
01-Oct-2000,"$4,321,000.00"
My ‘bitter experience’ code from GraPL seems to deal with most of what users have thrown at it over the last 2 years, and reads files up to around 10,000 rows by 20 columns in a reasonable time (faster than Excel can, anyway). It looks awfully over-engineered by comparison, but I think I can claim that in the real world (see Stephen Taylor on XP) it may be "the simplest thing that possibly works" …
∇ r←ParseTxt crv;tb;nl;lf;mat;dd;qq;cols;flds;rows;pad;xle
[1] ⍝ Handle tab and comma-separated data, returning a matrix of text cells
[2] ⍝ Space-separated takes any run of spaces as the delimiter - June 2002
[3] ⍝ Returns the number of separators in each record for later filtering
[4] (tb nl lf)←⎕AV[10 4 3]
[5]
[6] :If nl∊crv ⍝ Not a Unix file
[7] crv~←lf
[8] :End
[9]
[10] :While nl=¯1↑crv
[11] crv←¯1↓crv
[12] :End
[13]
[14] :If ~tb∊crv ⍝ Turn CSV into TSV
[15] dd←crv∊','
[16] :If ~∨/dd ⍝ No commas either!
[17] ⍝ GNU_Plot accepts blank-separated lists!
[18] ⍝ Also handy for APL-formatted matrices!
[19] crv←tb WhiteSpaceToDelim crv
[20] dd←crv∊tb
[21] :End
[22] qq←≠\crv∊'"'
[23] ((dd∧~qq)/crv)←tb
[24] (('""'⍷crv)/crv)←'∘'
[25] (('∘∘'⍷crv)/crv)←'"'
[26] crv~←'"'
[27] ((crv='∘')/crv)←'"'
[28] :Else ⍝ Just clear out quotes (Excel uses these in CSV)
[29] (('""'⍷crv)/crv)←'∘'
[30] (('∘∘'⍷crv)/crv)←'"'
[31] crv~←'"'
[32] ((crv='∘')/crv)←'"'
[33] :End
[34]
[35] ⍝ Sort out dates which arrive in 'Jan-01' format (Excel throws these)
[36] crv←(tb,nl,'.-/ ')FixDates crv
[37] ⍝ Sort out numbers which arrive in '$23.56' format
[38] crv←(tb,nl,'-')FixNums crv
[39] ⍝ Now split it on the newlines and tabs
[40] ⍝ First count the tabs in each record (classic partitioned sum)
[41] dd←¯2-/0,(1,⍨crv=nl)/+\(0,⍨crv=tb) ⋄ cols←1+⌈/dd
[42] ⍝ Now split on either delimiter
[43] mat←tb nl csvton crv
[44] flds←1+dd
[45]
[46] ⍝ Special case for Excel nulls - treat as zero for now
[47] ⍝ The '@' version comes from old 123 sheets which Excel still supports
[48] xle←'#N/A' '#VALUE!' '#REF!' '#DIV/0!' '#NUM!' '#NAME?' '#NULL!' '@NA'
[49] ((mat∊xle)/mat)←⊂,'0'
[50]
[51] :If flds∨.≠cols ⍝ Expand mask time (but beware overtake) ....
[52] pad←⊂''
[53] mat←1↓(1,∊cols↑¨flds⍴¨1)\pad,mat
[54] :End
[55]
[56] rows←⌊(⍴mat)÷cols
[57] mat←(rows,cols)⍴mat
[58] r←(mat dd)
∇
I won’t bore readers with the subfunctions to fix up Excel-formatted dates, or numbers with currency decorators. The workspace is free to anyone who could use it – email me at Causeway for the latest (probably even more defensive) copy.
Limbering Up: Numbers to Words¶
(The purpose of this column is to work some flab off your APL midsection. Like muscles, your APL skills can atrophy if not exercised with adequate frequency and variety. This column presents a task for you to perform. Set aside a few minutes from your busy schedule and work the task. Mail in your solution and stay tuned for the results.)
When your pay check comes in, the amount is generally expressed both in terms of digits and in terms of words. Like so:
$1,234.56
One thousand two hundred and thirty four and 56/100 dollars.
As APL programmers, we don’t have much trouble formatting the amount as a bunch of digits. For example:
The words, however, present more of a programming challenge.
Your task is to define a utility function named NUMBERWORDS, which takes a single positive integer as its right argument and returns a character that describes the number. For example:
NUMBERWORDS 1234
one thousand two hundred thirty four
NUMBERWORDS 1007
one thousand seven
NUMBERWORDS 0
zero
NUMBERWORDS 1003011
one million three thousand eleven
Given such a function, the task of formatting a cheque amount into words is straightforward:
∇ R←CHECKAMOUNT N;C;D
[1] ⍝ Format a $ amount (e.g. 1234.56)
[2] ⍝ into words (e.g. 'One thousand two
[3] ⍝ hundred thirty four and 56/100
[4] ⍝ dollars'). Needs NUMBERWORDS.
[5] ⍝
[6] D←⌊N ⍝ Dollars
[7] C←100×N-D ⍝ Cents
[8] D←NUMBERWORDS D ⍝ Dollars to words
[9] ⍝ Capitalize first letter:
[10] D[⎕IO]←'EFNOSTZ'['efnostz'⍳D[⎕IO]]
[11] ⍝ PUt it all together:
[12] R←D,' and',(0⍕C),'/100 dollars'
∇
CHECKAMOUNT 1234.56
One thousand two hundred thirty four and 56/100 dollars
The NUMBERWORDS function can be fast or slow, elegant or inelegant, short or long. All that matters is that it produces the correct result.