0

I'm trying to write a function to pivot my tables from wide to long format. so I have something on this lines:

tblwide:([]k1:`a`a`b`b`c`c;xx:1 2 3 4 5 6;yy:11 12 13 14 15 16);
wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;((lhscolnames),varcolname,valuecolname)!(eval lhscolnames;enlist rhscolname;rhscolname)] };
tbllong:raze wide2long[enlist `k1;;`index;`val] each (cols tblwide) except `k1;

and that seems to work.
now when I want to have several columns that will not pivot, adapting the code:

tblwide:([]k1:`a`a`b`b`c`c;k2:`t`u`t`u`t`u;xx:1 2 3 4 5 6;yy:11 12 13 14 15 16)
wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;((lhscolnames),varcolname,valuecolname)!(eval lhscolnames;enlist rhscolname;rhscolname)] };
tbllong:raze wide2long[`k1`k2;;`index;`val] each (cols tblwide) except `k1`k2;

then it no longer works. it seems q doesnt like the eval .

expected result is, if no mistake on my side is:

expected:([] k1:`a`a`b`b`c`c`a`a`b`b`c`c; k2:`t`u`t`u`t`u`t`u`t`u`t`u ; index:`xx`xx`xx`xx`xx`xx`yy`yy`yy`yy`yy`yy;val:1 2 3 4 5 6 11 12 13 14 15 16)

order is not important really I can always reorder later...

I'm totally open to other simpler/faster solutions, but would still be happy to understand how to solve this issue with the eval.

Will
  • 910
  • 7
  • 17

1 Answers1

2

I don't think you need the eval at all. An approach like this should work for both cases:

wide2long:{[lhscolnames;rhscolname;varcolname;valuecolname] ?[tblwide;enlist(~:;(^:;rhscolname));0b;(lhscolnames,varcolname,valuecolname)!lhscolnames,enlist[enlist rhscolname],rhscolname]};

q)raze wide2long[`k1`k2;;`index;`val] each (cols tblwide) except `k1`k2
k1 k2 index val
---------------
a  t  xx    1  
a  u  xx    2  
b  t  xx    3  
b  u  xx    4  
c  t  xx    5  
c  u  xx    6  
a  t  yy    11 
a  u  yy    12 
b  t  yy    13 
b  u  yy    14 
c  t  yy    15 
c  u  yy    16
terrylynch
  • 11,844
  • 13
  • 21
  • Thanks a lot Terry, "it works". Can i please ask you to comment on the !lhscolnames,enlist[enlist rhscolname],rhscolname I'm kind of lost on this (and I don't know how to investigate the type and value of those 3 terms in this context). My understanding is we want a dictionary that maps a key to a list. then lhscolnames contains the name of the column while rhscolname contains the values of the columns, then enlist enlist i dont get it at all... (I half built this thing using parse and trial and error) – Will Jan 20 '21 at 11:02
  • 1
    You're trying to find the functional form of this (specifically the dictionary): ```d:last parse"select k1,k2,index:`xx,val:xx from tblwide where not null xx"```. Generating the keys is straightforward: ```key[d]~lhscolnames,varcolname,valuecolname```. The values are trickier because for the index column you're trying to specify the *word* "xx", not the column "xx". You avoid this ambiguity by enlisting the "xx". The additional enlist is required to stop kdb from appending two symbol lists into one uniform list. Thus ```value[d]~lhscolnames,enlist[enlist rhscolname],rhscolname``` – terrylynch Jan 20 '21 at 11:36
  • I think I got it. thank you for the explanation. – Will Jan 20 '21 at 14:04