Rearranging Tables in J
This blog entry was inspired by a need that I had to rearrange a comma-delimited text (csv) file for an administrative task, using the J programming language. I thought it was interesting enough (and difficult enough) to warrant describing here as a way to achieve these sorts of things in J. Being an array-based programming language, J has some features that make the algorithm different than what I would use in another language.
In this study, I had a very large table containing student names and the names of primary, joint and secondary supervisors for their projects. A primary supervisor is the person responsible for the supervision, a joint supervisor is an active supervisor who is not the primary supervisor and a secondary supervisor is an advisor who does not actively supervise the student on a regular basis.
For the purpose of workload balancing in our department, we want to make sure that individuals are not supervising too many students, so we need to monitor how many students are being supervised by any one person. As the data is keyed to students, this is a difficult thing to see from the csv file. For this reason, and because the students with projects change on a regular basis, I wanted a script that would take the csv data and extract the number of students supervised in each of the three categories for each academic. We then use a scoring system (1 point for primary, 0.5 points for joint and 0 points for secondary supervisions) to give a score to each academic involved in supervision. This is not as easy as it sounds.
Below I have put a (much simpler) sample data set like the actual one I had to use. Supervisors names have been changed and students are generic in this example.
Student ,Primary Supervisor,Joint Supervisor,Secondary Supervisor Student Name1,Tony Stark,,"Jay Garrick, Steve Rogers" Student Name2,Diana Prince ,"Bruce Banner, Bruce Wayne", Student Name3,Oliver Queen,"Eric Twinge,Peter Parker, Alan Scott ", Student Name4,Alan Scott,Tony Stark,Peter Parker Student Name5,Bruce Wayne,Dick Grayson, Student Name6, Alan Scott,Diana Prince , Student Name7,Diana Prince ," Diana PRINCE, Tony Stark", Student Name8, Diana Prince,,Bruce Banner Student Name9,Bruce Wayne,Dick Grayson,Peter Parker Student Name10,Bruce banner,,James Howlett
A few things to note:
- The first row describes the columns and the data starts at the second row
- For any given student, there is not always a joint or a secondary supervisor
- Although there is only one primary supervisor, there is often more than one secondary and/or joint supervisor for a given student, and there is always at least two supervisors on a supervisory panel.
- People are not very consistent in data entry (see the capital surname, comma-delimited lists and different numbers of spaces before and after names. All these quirks have to be dealt with, otherwise there would be multiple names assigned to the same person.
- Our data is a mixture of text and numbers, meaning that it can’t be represented by a simple array. In J, such data is dealt with using boxed data.
Because I like doing numerical work in J, but did not have much experience working with mixed data in boxes, I decided to try to implement this in J. What we want to have at the end of this code is a list of all the names of supervisors in the first column, and then a vector containing the numbers of primary-supervised students, joint-supervised students and secondary-supervised students for that academic, with the last column being the total score for that supervisors. If the score exceeds a certain value, we know not to assign a new student to that supervisor until they are finished supervising one of the others.
Before we begin in earnest, a caveat: I’m not a very experienced J programmer, so what I have done should not be considered in any way optimal, and an experienced J programmer would find much more efficient ways of doing this. But it was an interesting lesson for me in how to manipulate tabular mixed string and number data. I’ll go through each part of the code and describe it as we go along.
J uses terminology borrowed from english grammar: verbs are functions that operate on data, nouns are the data, and adverbs are modifiers that change the way their verb arguments operate. A conjunction is like an adverb but takes arguments on both sides to produce a new verb with some kind of modified behaviour. So when I talk about these parts of speech below in a J context, that’s what I mean. The vocabulary part of the J documentation goes into detail about the differences between these ways of operating.
clear ''
1!:44 '/home/xx/j902-user/projects/Supervision/'
load 'csv'
datafile =: readcsv 'SuperVision.csv'
headings =: 0{datafile
data =: 1}. datafile
nstudents =: 0{$data NB. number of students
Primaries =: 1}"1 data
Joints =: 2}"1 data
Secondaries =: 3}"1 data
The clear '' verb clears all the variables that might have been defined elsewhere. This is very important for debugging, as it’s easy to get fooled that a change you made interactively is part of the code. By executing the script each time a change is made, this command guarantees you are starting with a clean slate.
1!:44 '...' is an example of a J ‘foreign’ verb, which executes an operating system call. They are all of the form a!:b with different values of a and b for different OS-specific applications. This one sets the working directory.
load 'csv' loads some libraries for dealing with comma-delimited files, allowing us to use the readcsv verb to open the file Supervision.csv, containing the data above. The remaining words read the data from the file 1}. datafile removes the first line, although in this case, the monadic ‘behead’ form (without the 1) would also work, as there is only one line of header data. The variables Primaries, Joints and Secondaries contain the unformatted list of names for the primary, joint and secondary supervisors.
The variable datafile looks like this…
┌──────────────┬──────────────────┬─────────────────────────────────────┬─────────────────────────┐ │Student │Primary Supervisor│Joint Supervisor │Secondary Supervisor │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name1 │Tony Stark │ │Jay Garrick, Steve Rogers│ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name2 │Diana Prince │Bruce Banner, Bruce Wayne │ │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name3 │Oliver Queen │Eric Twinge,Peter Parker, Alan Scott │ │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name4 │Alan Scott │Tony Stark │Peter Parker │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name5 │Bruce Wayne │Dick Grayson │ │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name6 │ Alan Scott │Diana Prince │ │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name7 │Diana Prince │ Diana PRINCE, Tony Stark │ │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name8 │ Diana Prince │ │Bruce Banner │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name9 │Bruce Wayne │Dick Grayson │Peter Parker │ ├──────────────┼──────────────────┼─────────────────────────────────────┼─────────────────────────┤ │Student Name10│Bruce banner │ │James Howlett │ └──────────────┴──────────────────┴─────────────────────────────────────┴─────────────────────────┘
The next section of code provides the verbs and nouns used to rearrange the data and clean it up.
NB. General utility verbs
NB. The last two are taken from the j phrases collection
nub =: ~: # ] NB. unique names
RotateLeading =: ] |.~ (+/)@(*./\ )@(' '&=) NB. Move leading spaces to end
RemoveTrailing =: #~ ([: +./\. ' '&~:) NB. Remove trailing spaces
NB. Arranging data verbs
unify =: 3 : 0
tolower RotateLeading"1 > ,>(< ;._1) each ',', each y
)
Primaries =: unify 1}"1 data
Joints =: unify 2}"1 data
Secondaries =: unify 3}"1 data
nub produces a list of the unique items in an array. This is an example of tacit, or functional programming. It is equivalent to ~: y # y where y is the argument to the right of the list of verbs. ] selects the argument on the right of nub (i.e. y), while ~: finds the locations in the array y of its values as an array of 1s and 0s that is the same length as y. The ones indicate unique values, while the zeros indicate repeated values. This string of 1s and 0s, when passed to #, will provide a new array consisting only of the unique items. Such a collection of 3 verbs is known as a train in J. Tacit programming is very powerful, but I find it quite hard to implement. This verb nub is one that comes up very commonly in manipulation of arrays, and so is good to have in your library.
RotateLeading and RemoveTrailing are collections of verbs contained in the J Phrases book, and which I have lifted directly for this purpose. RotateLeading takes any leading spaces in each name and rotates them to the end of the character array. J can display the order in which verbs are executed in tree mode, as follows:
┌─ ]
├─ ~ ─── |.
│ ┌─ / ─── +
──┤ ┌─ @ ─┴─ \ ─── / ─── *.
│ │
└─ @ ─┤ ┌─ ' '
└─ & ─┴─ =
Working our way from right to left along the tree, we first find all the characters in the name that are spaces, represent them as a list of 1s and 0s, then use *./\ to or each of the elements of that array progressively. +/ sums the remaining values to give the number of spaces at the start of the string. Then the |.~ rotates the string so that the leading spaces are sent to the end of the array. As a concrete example, take the string '___Hello__' where the underscores represent spaces. ' '&= produces 1 1 1 0 0 0 0 0 1 1. *./\ produces the progressive and-ing of each of these values in the array producing 1 1 1 0 0 0 0 0 0 0, i.e. a listing representing the leading spaces in the array as 1s. +/ then sums these to 3 and ] |. rotates the array by 3, placing the leading spaces at the end of the array. So we would end up, in this example, with 'Hello_____' as our final product. This is a really good example of the tacit and loopless way that J operates on arguments. There is no assumption made about the type of data that this verb operates on, and it uses the properties of the data itself to determine what needs to be done.
I’ll leave RemoveTrailing as an exercise for the reader, but it removes the trailing spaces from a string. So sequential application of RotateLeading and RemoveTrailing will remove all the spaces from the start and end of a string.
unify removes leading spaces from each name in the boxed array of names and transforms the text to lowercase. This allows names like Prince and PRINCE in the data to be treated the same and counted together. Otherwise it will look like they are two different supervisors. < and > are the box and unbox verbs respectively, which pack and unpack items stored in boxes. Boxes fulfil the task taken by structs in some other languages. unify also uses the ;. cut conjunction, which is a very powerful way of operating on patterns of data separated by some kind of delimiter (in this case, the commas separating lists of joint or secondary supervisors). So unify removes leading spaces, changes names to lowercase and separates out the names in the comma-delimited lists to produce a boxed list of names. If there are no names in an entry, then it produces an empty item.
NB. Now work on the data.
NB. Collections of unique supervisor lists and supervision numbers
NB. for each category of Primary, Joint and Secondary supervisors
GradeNub =: ([: /: nub) { nub
PrimNub =: GradeNub Primaries
PrimNum =: (+/"1) -. (<"1 PrimNub) (i."0 _) <"1 Primaries
JointNub =: GradeNub Joints
JointNum =: (+/"1) -. (<"1 JointNub) (i."0 _) <"1 Joints
SecNub =: GradeNub Secondaries
SecNum =: (+/"1) -. (<"1 SecNub) (i."0 _) <"1 Secondaries
In this part of the code GradeNub produces the unique supervisors in a given list of primary, joint or secondary supervisors and arranges them in alphabetical order starting with the first name. The sorting is done using the grade up /: verb. These are stored in nouns PrimNub, JointNub and SecNub. The PrimNum etc. nouns generate sums of the number of times each supervisor is named in this list, in the same position in the array as the name. +/ sums up and i. generates the indices of the instances of each name so it can be summed with +/. This idea of generating matrices of 1s and 0s for occurrences and summing over the array is common in J. I probably should have factored out the verb part of the code for the numbers as I did for the Nub words, but didn’t bother in this case.
If you have not seen J before you might wonder what the "0 _" and "1 things are. These are examples of the rank conjunctions (the underscore means infinity in J). Rank conjunctions tell the verbs before them (e.g. the +/ in +/"1) to operate on different subsections of a matrix. So while +/ would, by default, sums down columns of a 2-d matrix, +/"1 makes it sum down rows. Here’s a simple example:
3 4 $ i. 12 NB. Make a 3 x 4 array of the integers between 0 and 11 0 1 2 3 4 5 6 7 8 9 10 11 +/ 3 4 $ i. 12 NB. Sum down columns 12 15 18 21 (+/"1) 3 4 $ i. 12 NB. Sum along rows 6 22 38
Using infinity as one of the ranks (in "0 _", the verb is applied to the left noun at rank 0 and to the right noun at infinite rank) implies the highest possible rank that the data structure has. Rank is something that I find difficult to understand in J, as different verbs and conjunctions operate by default on different ranks. Mostly I just do lots of experimentation to ensure that I have the data being operated on in the right way. This, for me, is the most time-consuming part of programming in this language, although rank is one of the concepts that makes J a flexible and elegant way of expressing algorithms. One of the nice things about this language, as you can see from the vocabulary, is that there is a relatively small number of verbs and conjunctions in the language, but they can be combined using the rank concept in an amazing variety of ways. This means you don’t have so much to remember, but it also means that you have to understand those complex ways of interacting.
So by now our script has generated 6 arrays: 3 arrays of supervisors and 3 arrays containing the number of supervised students in each of the 3 supervision categories for each supervisor.
NB. Now make a list of the unique names with NB. numbers of primary, joint and secondary supervisions for each NB. First, generate the unique list and sort in alphabetical order TotalNames =: RemoveTrailing each <"1 GradeNub PrimNub,JointNub,SecNub
TotalNames accumulates the three lists of supervisors using , to string them together, and then finds the unique names in the accumulated list and removes the trailing space from each item in the list. This produces a single alphabetically ordered list of all the supervisors who showed up in any of the three supervision categories.
TotalNames ┌┬──────────┬────────────┬───────────┬────────────┬────────────┬───────────┬─────────────┬───────────┬────────────┬─... ││alan scott│bruce banner│bruce wayne│diana prince│dick grayson│eric twinge│james howlett│jay garrick│oliver queen│pe... └┴──────────┴────────────┴───────────┴────────────┴────────────┴───────────┴─────────────┴───────────┴────────────┴─...
Note that the first item is blank. This represents the cases where there is an empty item in one of the supervision lists.
NB. Now generate the numbers of each category of supervisions
NB. Note we add a 0 to the list of numbers because
NB. if a name does not show up, the index 1 beyond the end
NB. is generated, which corresponds to the name not being
NB. on the list of primaries.
NumPrimNub =: ((RemoveTrailing each <"1 PrimNub) i. TotalNames){PrimNum,0
NumJointNub =: ((RemoveTrailing each <"1 JointNub) i. TotalNames){JointNum,0
NumSecNub =: ((RemoveTrailing each <"1 SecNub) i. TotalNames){SecNum,0
Having got the list of supervisors, we need to read the number of supervisions in each supervision category for each of the supervisors in the master list. This is done by the code generating the nouns NumPrimNub, NumJointNub and NumSecNub. The 0 is appended to the array because if a name does not occur in the array, i. references one past the end of the array. So we want to make sure that if a name does not occur in the array of names, we say that person is supervising 0 students.
NB. Now produce the Summary Tables Score =: +/"1 (|: NumPrimNub,NumJointNub,:NumSecNub) (*"1) 1, 0.5,0 SupervisionMatrix =: (|: NumPrimNub,NumJointNub,:NumSecNub),"1 0 Score Labels =: 'Name'; 'Primary'; 'Joint'; 'Secondary'; 'Score' AlphaTable =: }. (TotalNames),"0 1 (<"0 SupervisionMatrix) LeagueTable =: AlphaTable \: }. Score AlphaSummary =: Labels,AlphaTable LeagueSummary =: Labels,LeagueTable
Score produces an array of the of the number or primary, joint and secondary supervisions for each supervisor (|: NumPrimNub,NumJointNub,:NumSecNub) and multiplies this by the score for each type (1 0.5 0) and sums to produce an array containing the total supervision score for each supervisor in TotalNames.
SupervisionMatrix generates a matrix made up of the number of supervisions in each category, followed by a column for the total score. AlphaTable and LeagueTable then arrange the list of names with the matrix generated as SupervisionMatrix to make summaries in alphabetical and score order respectively. }. is used to remove the blank from the list of supervisors. As the matrix for supervisions is sparse, and there is no chance that there will be no blank fields in the table, it is safe to remove this from the matrix.
The final result of all this is the LeagueSummary, with all the supervisors and their supervisions and scores in decreasing order from highest score to lowest, with titles as the first row. Within each category of scores supervisors are ordered alphabetically.
┌─────────────┬───────┬─────┬─────────┬─────┐ │Name │Primary│Joint│Secondary│Score│ ├─────────────┼───────┼─────┼─────────┼─────┤ │diana prince │3 │2 │0 │4 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │alan scott │2 │1 │0 │2.5 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │bruce wayne │2 │1 │0 │2.5 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │tony stark │1 │2 │0 │2 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │bruce banner │1 │1 │1 │1.5 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │dick grayson │0 │2 │0 │1 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │oliver queen │1 │0 │0 │1 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │eric twinge │0 │1 │0 │0.5 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │peter parker │0 │1 │2 │0.5 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │james howlett│0 │0 │1 │0 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │jay garrick │0 │0 │1 │0 │ ├─────────────┼───────┼─────┼─────────┼─────┤ │steve rogers │0 │0 │1 │0 │ └─────────────┴───────┴─────┴─────────┴─────┘
It’s clear from this table that Wonder Woman is doing too much supervision and Wolverine, Flash and Captain America are not pulling their weight.
WriteSummary =: 3 : 0 FileName =. y LeagueSummary writecsv FileName )
This final verb allows the LeagueSummary to be saved to a different .csv file for later use, using the writecsv verb in the csv library.
So hopefully if you have not used J much, this might give a flavour of what can be done with it. As I said before, I am sure that someone with better skills than me could do this more efficiently, and I don’t think my code is a good advertisement for the terseness of J, but I was happy with being able to do this in an automated way, as it’s a lot of work to do such rearrangement manually and it has to be done on a reasonably regular basis. I welcome any comments on how this might be done better in J, or in another language for that matter.
NB. Here is the full J source code, and here is the csv data it operates on.
Leave a Reply