I was revisiting some code from a few years ago when I was first coming to
grips with J.
It basically reformats a table formatted with one column per variable for each
record (spreadsheet format) to having one record for each variable (database
format).
]tst=: (;:'tag yr weight dob') , <"0 i. 5 4
+---+--+------+---+
|tag|yr|weight|dob|
+---+--+------+---+
|0 |1 |2 |3 |
+---+--+------+---+
|4 |5 |6 |7 |
+---+--+------+---+
|8 |9 |10 |11 |
+---+--+------+---+
|12 |13|14 |15 |
+---+--+------+---+
|16 |17|18 |19 |
+---+--+------+---+
2 melt tst
+---+--+--------+-----+
|tag|yr|variable|value|
+---+--+--------+-----+
|0 |1 |weight |2 |
+---+--+--------+-----+
|0 |1 |dob |3 |
+---+--+--------+-----+
|4 |5 |weight |6 |
+---+--+--------+-----+
|4 |5 |dob |7 |
+---+--+--------+-----+
|8 |9 |weight |10 |
+---+--+--------+-----+
|8 |9 |dob |11 |
+---+--+--------+-----+
|12 |13|weight |14 |
+---+--+--------+-----+
|12 |13|dob |15 |
+---+--+--------+-----+
|16 |17|weight |18 |
+---+--+--------+-----+
|16 |17|dob |19 |
+---+--+--------+-----+
NB.*melt v Reshape table to database style
NB. returns: table in database style
NB. y is: boxed table with column labels in first row
NB. x is: optional left arg for number of "label" columns
NB. eg: 2 melt (;:'tag yr weight dob') , <"0 i. 5 4
NB. semi-inverts cast
melt=: 3 : 0
1 melt y
:
lbls=. (x{.{.y),;:'variable value'
rows=. }. x {."1 y
cols=. x }. {. y
dat=. , }. x }."1 y
lbls,((#cols)#rows),.((#dat)$cols),.dat
)
Looking at this code again, now with a better understanding of the concept of
Rank, it seemed that the final line could be simplified and sure enough:
meltb=: 3 : 0
1 meltb y
:
lbls=. (x{.{.y),;:'variable value'
rows=. }. x {."1 y
cols=. x }. {. y
dat=. }. x }."1 y
lbls , ,/ rows ,"1 cols ,."1 dat
)
I find the above following much formulation cleaner & it lends itself better to
"tacitification":
lbls=. (;:'variable value') ,~ ({. {.)
rows=. {."1 }.
cols=. }. {.
dat=. }."1 }.
meltc=: (lbls , [: ,/ rows ,"1 cols ,."1 dat) f.
Interestingly though, the initial algorithm performs better that the other two
(and a number of others that I tried):
ts=: 6!:2 , 7!:2...@]
tstbig=: (;:'tag yr wgt dob var1 var2 var3 var4 var5 var6') , <"0 i. 50000 10
5 ts '1 melt tstbig'
0.300597404 27528128
5 ts '3 melt tstbig'
0.346103714 34606016
5 ts '1 meltb tstbig'
0.434143451 35409408
5 ts '3 meltb tstbig'
0.475605068 35376640
5 ts '1 meltc tstbig'
0.604043666 22307968
5 ts '3 meltc tstbig'
0.651678196 23946368
----------------------------------------------------------------------
For information about J forums see http://www.jsoftware.com/forums.htm
