Effective Date Searching
Vector 19:3, page 29
Transcribed from the printed issue; not yet reviewed. Read it in the PDF of the issue, page 29
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 problems being discussed in this column.
Utility Corner: Effective Date Searching¶
(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.)
Suppose your have two numeric vectors of the same length, IDS and PRICES. IDS is a vector of distinct product ID codes for all available products; PRICES is a vector of corresponding process for these products. Given a subset list of product ID codes, say LIST you can easily determine their corresponding process:
Suppose prices change over time. Each price, for a given product, has an associated effective date. For a given product, there may be many different prices, each with a different effective date. To determine the price of a given product, you need to specify not only the product’s ID code, but also the date as of when you want its price.
Here’s an illustration:
| Product Id | Price | Effective Date |
|---|---|---|
| 204 | 2.65 2.75 2.89 |
1993/01/01 1993/07/01 1993/09/31 |
| 202 | 15.50 16.75 |
1993/01/01 1993/10/15 |
The price of product 202 on 1993/09/25 is 15.50. The price of product 204 on 1993/10/15 is 2.89.
In last issue’s Limbering Up column, you were asked to design and define a utility function that takes a list of product IDs and corresponding dates, and return the prices for those products on those dates. We were looking for two things: clean design, and program efficiency.
The responses we received consistently suggested storing the database of price and date information in a single APL matrix. If the product ID is known to be numeric, you can construct a three-column matrix that contains the necessary information:
However, if prices are larger than 32767 the matrix will go floating point anyway.
If the product ID may contain non-digits, the database can be constructed as a character matrix:
In this case, no blank columns are required between the fields of information. However, you may need to include a few extra blank columns in the price field to accommodate the largest possible price.
Having designed the database of effective price information as a single APL matrix, you can design an array of the target information along the same lines. For example, if you want the prices for product 202 on 1993/09/25 and 204 on 1993/10/15, the target array can be:
or
or
Given a database matrix and a target matrix, the syntax of a utility function suggests itself:
The most straightforward and efficient solutions came from Jim Weigang and Dan Thompson. Jim assumed numeric matrices; Dan assumed character matrices. The key step in both solutions was to row-wise catenate the matrices and grade the result.
Here is a function that works for three-column numeric matrix databases, and that combines the best of Jim’s and Dan’s functions:
∇ R←DB EFFECTIVE TARGET;D;DI;E;G;M;N;T;TI;⎕IO
[1] ⍝ Searches the 3-column num matrix DB
[2] ⍝ (ID price, eff. date [yyyymmdd])
[3] ⍝ for the rows that correspond to the
[4] ⍝ rows of the 2-column num matrix
[5] ⍝ TARGET(ID, date), and returns the
[6] ⍝ prices in effect on the target
[7] ⍝ date. For example, given DB:
[8] ⍝ 204 265 19930101
[9] ⍝ 204 275 19930701
[10] ⍝ 204 289 19930931
[11] ⍝ 202 1550 19930101
[12] ⍝ 202 1675 19931015
[13] ⍝ and TARGET:
[14] ⍝ 202 19930925
[15] ⍝ 204 19931015
[16] ⍝ the resultis 1550 289. The result
[17] ⍝ contains 0s for rows of TARGET with
[18] ⍝ no matching row in DB. TARGET may
[19] ⍝ be a 2-element vector.
[20] ⍝
[21] ⎕IO←1
[22] ⍝ MakeTARGET a matrix if a vector:
[23] →(M←2=⍴⍴TARGET)⍴L1
[24] TARGET←(1,⍴TARGET)⍴TARGET
[25] ⍝ Squeeze out unneeded DB rows:
[26] L1:DB←(DB[;1]∊TARGET[;1])⌿DB
[27] ⍝ Make sure a DB row winds up first
[28] ⍝ after sorting:
[29] DB←¯1,[1]DB
[30] ⍝ Vatenate and sort them to merge:
[31] G←⍋DB[;1 3],[1]TARGET
[32] N←1↑⍴DB ⍝ No. of recsin DB
[33] T←~D←G≤N ⍝ Those from TARGET and DB
[34] ⍝ Indices into DB for each row of
[35] ⍝ TARGET (as sorted):
[36] DI←(D/G)[T/+\D]
[37] ⍝ Corresponding indices into TARGET:
[38] TI←(T/G)-N
[39] ⍝ Flag those with a match:
[40] E←DB[DI;1]=TARGET[TI;1]
[41] ⍝ Construct result, one elt per row
[42] ⍝ of TARGET
[43] R←(1↑⍴TARGET)⍴0
[44] R[E/TI]←DB[E/DI;2]
[45] ⍝ Make R scalarif TARGET a vector:
[46] →M⍴0
[47] R←R[1]
∇
Let’s show how this function works, given the following arguments:
DB: 204 265 19930101
204 275 19930701
204 289 19930931
202 1550 19930101
202 1675 19931015
TARGET: 202 19930925
204 19931015
This function allows TARGET to be a two-column matrix or a two-element vector. The result is a vector if the former, or a scalar if the latter. On lines [23] and [24], TARGET is reshaped to a one-row matrix if it is given as a two-element vector. By reshaping TARGET, you can treat it as a matrix for the remainder of the function.
On line [26], EFFECTIVE squeezes down the size of DB to retain only those rows that correspond to products with IDs in TARGET. If DB is large and TARGET is relatively small, this step can speed up the function considerably by reducing the number of rows to be sorted. In this example, DB keeps all its rows since both products are represented in TARGET.
On line [29], a row of ¯1s is catenated to the top of DB:
This step is performed to guarantee that there is always a row of DB preceding each row of TARGET, once DB and TARGET have been merged. You’ll see why below.
On line [31], the product numbers and dates from both DB and TARGET are catenated and graded. This is the workhorse line of the function. Though the rows of DB and TARGET are never reordered via the grade vector, let’s show such a reordering to help you see what’s happening:
G D T +\D
¯1 ¯1 ¯1 1 1 0 1
202 1550 19930101 5 1 0 2
202 19930925 7 0 1 2
202 1675 19931015 6 1 0 3
204 265 19930101 2 1 0 4
204 275 19930701 3 1 0 5
204 289 19930931 4 1 0 6
204 19931015 8 0 1 6
Notice the rows are sorted by product code, and within product code by date. The two rows of TARGET have been automatically inserted directly below the corresponding rows of DB that contain the desired prices.
Lines [32] and [33] flag the rows of the sorted result that correspond to rows from DB (in D) and from TARGET (in T).
Lines [36] and [38] use the values from G, D, and T to construct vectors of indices of the target rows into the DB row above them (in DI) and into the original unsorted TARGET matrix (in TI):
For example, these values tell you that the row DB[5;] (202 1550 19930101) contains the corresponding price for row TARGET[1;] (202 19930925). Likewise, row DB[4;] (204 289 19930931) corresponds to row TARGET[2;] (204 19931015).
Line [40] compares the product codes in the matching rows to make sure DB does indeed contain an effective price for the corresponding row of TARGET. In this case, they do, so E is 1 1. However, if the product code in TARGET is not found in DB, or if the date in TARGET is earlier than the earliest effective date for that product in DB, E will contain 0s.
The remaining lines of the function construct the result R inserting the matching prices where they belong, and leaving 0s for rows of TARGET with no match. Finally, the result is converted to a scalar if TARGET was given as a two-element vector instead of a matrix.
As written, this function is general enough to allow for:
- rows of
DBthat are duplicates, or are not in sorted order; - rows of
TARGETthat are duplicates, or have no matching product code inDB,or have no earlier date inDB.
The modifications needed to make EFFECTIVE work on character matrix arguments or on numeric matrices with multiple columns for dates are simple, and are left as an exercise.
Limbering Up: Exporting Numbers¶
(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.)
APLers tend to live in their own little societies, secluded from the rest of the world and beholden to no one. However, reality being what it is, the need does occasionally arise to communicate with others. Sometimes, you want some data that others have, or they want some data that you have.
How do you move a copy of the data from them to you, or vice versa?
Suppose, for example, the data in question is a bunch of numbers, which you would view as a numeric matrix, and which they would view as a range of spreadsheet cells. They might suggest the numbers be transferred as a "comma-delimited text file". You need to construct a text (ASCII) file that contains the numbers in this format:
If you want to send them the numbers, you must be able to convert your numeric matrix to a file in this format. If you want to receive numbers from them, you must be able to convert a file in this format to a numeric matrix.
Your task is to define utility functions that perform these two tasks. Call the functions TOCOMMA and FROMCOMMA. TOCOMMA is a monadic function whose right argument is a numeric matrix to be converted to a comma-delimited file, and whose result is a character vector. The character vector contains the formatted numbers (formatted with monadic ⍕) separated by commas, and the rows separated by a carriage return and a line feed (⎕TC[2 3]). For example,
NMAT←3 4⍴123 5000 60 90793 124 400 60 31593 125 39900 71 4
2293
TOCOMMA NMAT
123,5000,60,90793
124,400,60,31593
125,39900,71,42293
(The lines appear double-spaced because of the inclusion of both carriage return and line feed characters. 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:
Creating and writing a file:
Reading a file
The functions should be as efficient (fast) as possible.
Send your solution to: Vector Production, Brook House, Gilling East, York YO62 4JJ, UK.
The notable functions and their authors’ names will be printed in the next issue of Vector. Good luck and happy limbering.
Reprinted with kind permission from Zark APL Tutor News, a quarterly publication of Zark Incorporated, 23 Ketchbrook Lane, Ellington, CT06029, USA.