I think this may well have been asked before but I can’t locate thethread or blog entry anymore. Is there an elegant way to turn thefirst table into the second one?q)t1: ([d:10 20 30 10 20 30]s:abcdef;e:1 1 1 2 2 2)q)t1d | s e–| —10| a 120| b 130| c 110| d 220| e 230| f 2q)t2: ([d:10 20 30]e1:abc;e2:def)q)t2d | e1 e2–| -----10| a d20| b e30| c fThanks
The best I could come up with is this brute-force method: t2: select d,s by e from t1 t2: (value flip @) each (key t2; value t2) t2: 1!flip(d,$“e”,'string first t2 0)!(1_(,/)t2 1)I am looking for an expression that transforms the table"in place" if possible.
q)c:exec distinct e from t1
q)exec(`$“e”,/:string c)!(e!s)c by d:d from t1
| d | e1 e2 |
|---|---|
| 10 | a? d |
| 20 | b? e |
| 30 | c? f |
Yes! Actually one can simplify a bit by pre-sorting t1, which shouldbe fast: fs: $"e",/:string exec distinct e from t1 exec fs!s by d:d from e xasc t1
if your data is square, your assumptions hold, if not…
depends on your needs and data what solution you may want to use
q)t1: ([d:10 20 30 10 20 30 40]s:abcdef`g;e:1 1 1 2 2 2 1)
q)t1
| d | s e |
|---|---|
| 10 | a 1 |
| 20 | b 1 |
| 30 | c 1 |
| 10 | d 2 |
| 20 | e 2 |
| 30 | f 2 |
| 40 | g 1 |
| q)c:exec distinct e from t1 | |
| q)exec (`$“e”,/:string c)!(e!s) c by d:d from t1 | |
| d | e1 e2 |
| – | ----- |
| 10 | a? d |
| 20 | b? e |
| 30 | c? f |
| 40 | g |
| q) fs: `$“e”,/:string exec distinct e from t1 | |
| q)exec fs!s by d:d from `e xasc t1 | |
| 'length |
Yes, there are no holes in the actual data: every d value hasthe full complements of e values, although s may be null, sothis should work. However I should have sorted fs as well, i.e.,fs: `$“e”,/:string asc exec distinct e from t1
Well, there *should* be no holes but maybe there will be some inpractice.That is bad because the actual problem is slightly more complicated inthatthere are two columns that need to be merged. If the data has noholes,then it’s a straightforward extension of the previous expression:q)t1: flip defs!flip ((cross/)(10 20 30 40;1 2;5 7)),'$'"c"$("i"$"a")+til 16q)t1d e f s--------10 1 5 a10 1 7 b10 2 5 c10 2 7 d20 1 5 e20 1 7 f20 2 5 g20 2 7 h30 1 5 i30 1 7 j30 2 5 k30 2 7 l40 1 5 m40 1 7 n40 2 5 o40 2 7 pq)fs: {$“e”,(string x),“_”,(string y)}.'asc distinct flip value exece,f from t1q)fse1_5e1_7e2_5e2_7q)exec fs!s by d:d from ef xasc t1d | e1_5 e1_7 e2_5 e2_7–| -------------------10| a b c d20| e f g h30| i j k l40| m n o pBut if t1 has holes (e.g., -2_t1) or is randomized (e.g., t1@16?16)thenthis fails with a length error and I am unable to find the equivalentof themore robust solution in this case…
Sorry, I meant -16?16, not 16?16, and that works fine but theproblem of the holes remains.
I found the answer: t1: flip deks!flip ((cross/)(10 20 30 40;1 2;5 7)),'$'"c"$("i"$"a")+til 16 t1: 1_-2_t1 / introducing holes fs: {$“e”,(string x),“_”,(string y)}.'f: asc distinct flip valueexec e,k from t1 exec fs!((e,'k)!s)f by d:d from ek xasc t1The trick was to remember that e and k in the last expressionare vectors, hence e,'k and not, say, (e;k)
To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1077)
fs:`$“e”,/:"_"sv/:string asc exec distinct(e,'k)from t1
/no need for xasc anymore (actually makes it slower)
exec fs!?[f(s!e,'k)?f by d:d from t1
Regards,
Attila
On 30 Dec 2009, at 12:59, olivier wrote:
> I found the answer:
>
> t1: flip deks!flip ((cross/)(10 20 30 40;1 2;5 7)),'$'"c"$ \> ("i"$"a")+til 16 \> t1: 1\_-2\_t1 / introducing holes \> fs: {$“e”,(string x),“_”,(string y)}.'f: asc distinct flip value
> exec e,k from t1
> exec fs!((e,'k)!s)f by d:d from ek xasc t1
>
> The trick was to remember that e and k in the last expression
> are vectors, hence e,'k and not, say, (e;k)
>
> –
>
> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at =
http://groups.google.com/group/personal-kdbplus?hl=en.
>
>
To: “[kdb+]” X-Mailer: Apple Mail (2.1077)> exec fs!?[f(s!e,'k)?f by d:d from t1exec fs!?[s!e,'k;f]by d:d from t1orexec fs!(s!e,'k)?f by d:d from t1Regards, Attila
Thanks. You’re right: with these last two forms sorting t1 issuperfluous. But so is /: in the definition of fs: ’ is enough.Now I’ve got one last problem. let us go back to the firstexpression, to keep things simples; no xasc since t1 isalready sorted in this example. I can’t get the functionalform to work:q)t1: ([d:10 20 30 10 20 30]s:abcdef;e:1 1 1 2 2 2)q)fs: $"e",/:string exec distinct e from t1q)exec fs!s by d:d from t1d | e1 e2--| -----10| a d20| b e30| c fq)parse "exec fs!s by d:d from t1"?t1()(,d)!,d,(!;fs;s)q)?[t1;();((),d)!(),d;enlist(!;fs;s)]d |--| -----------10| ! e1e2 s20| ! e1e2 s30| ! e1e2 s
To: personal-kdbplus@googlegroups.comX-Mailer: Apple Mail (2.1077)> Thanks. You’re right: with these last two forms sorting t1 is> superfluous. But so is /: in the definition of fs: ’ is enough.Sure, but that does not have a performance impact.> Now I’ve got one last problem. let us go back to the first> expression, to keep things simples; no xasc since t1 is> already sorted in this example. I can’t get the functional> form to work:> > q)t1: ([d:10 20 30 10 20 30]s:abcdef;e:1 1 1 2 2 2)> q)fs: $"e",/:string exec distinct e from t1\> q)exec fs!s by d:d from t1\> d | e1 e2\> --| -----\> 10| a d\> 20| b e\> 30| c f\> q)parse "exec fs!s by d:d from t1"\> ?\> t1> ()> (,d)!,d> ,(!;fs;s)> q)?[t1;();((),`d)!(),`d;enlist(!;fs;`s)]> d |> --| -----------> 10| ! `e1`e2 `s> 20| ! `e1`e2 `s> 30| ! `e1`e2 `sq)?[t1;();(1#`d)!1#`d;(!;`fs;`s)]d | e1 e2–| -----10| a d 20| b e 30| c f also?[t1;();(1#`d)!1#`d;(!;enlist fs;`s)]Regards, Attila
OK, I had confused fs and `fs but it looks like parseis misleading us nonetheless:?xyzwis the K display form of ?(x;y;z;w), so the last elementis supposedly enlisted.
I meant of ?[x;y;z;w]
To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1077)
not quite
the thing is that parse parses the string
ready to be eval-d not value-d (as .z.pi)
it is easy to get confused
but consider this:
q)parse"exec s from t1"
?
t1 () () ,s
q)eval parse"exec s from t1"
abcdef
q)value parse"exec s from t1"
s q)?[t1;();();enlists]
s q)?[t1;();();s]
abcdef
I hope this helps.
Regards,
Attila
On 30 Dec 2009, at 15:50, olivier wrote:
> OK, I had confused fs and `fs but it looks like parse
> is misleading us nonetheless:
> ?
> x
> y
> z
> w
> is the K display form of ?[x;y;z;w], so the last element
> is supposedly enlisted.
>
> –
>
> You received this message because you are subscribed to the Google =
Groups “Kdb+ Personal Developers” group.
> To post to this group, send email to =
personal-kdbplus@googlegroups.com.
> To unsubscribe from this group, send email to =
personal-kdbplus+unsubscribe@googlegroups.com.
> For more options, visit this group at =
http://groups.google.com/group/personal-kdbplus?hl=en.
>
>
value ~ get? 1b
eval is the dual to parse
Regards,
Xi
Actually ‘value parse code_string’ throws a `type error.Perhaps my question can be rephrased as: what would be anexample of an “? etc” parse form that does *not* have a commain front of the last element? If there is one then perhaps thecomparison will finally shed some light (for me); if not then thatwould make the comma a pure convention.
To: personal-kdbplus@googlegroups.com
X-Mailer: Apple Mail (2.1077)
> Actually ‘value parse code_string’ throws a `type error.
I did not mean to suggest you should use value - just on my simplistic =
example.
> Perhaps my question can be rephrased as: what would be an
> example of an “? etc” parse form that does *not* have a comma
> in front of the last element? If there is one then perhaps the
> comparison will finally shed some light (for me); if not then that
> would make the comma a pure convention.
enlist-ing is like quote in Lisp
not-enlisted symbols evaluate to their content
not-enlisted general lists : let’s say (x;y;z) is evaluated as x[y;z]
in the ? you have to pass in a list as the last parameter
so parse will put an enlist before it
but you don’t need the enlist because you won’t use eval…
i hope it is clear now
and reply to your direct question
q)parse"exec 1 from t1"
?
`t1
()
()
1
but it is not a too useful one tough ;)
Regards,
Attila