在KDB中,我有一个简单的表,我正在尝试透视它。使用https://code.kx.com/q/kb/pivoting-tables/我还是迷路了。我是KDB的新手。我有一张桌子
t:([]sym:`IBM`FB`TESLA`IBM;exchange:`A`A`B`B;OB_p1:4#100.1;OB_p2:100.2 100.2 100.3 100.4;OB_p3:100.5 100.6 100.7 100.8;OB_p4:100.8 100.8 100.8 100.9)
sym exchange OB_p1 OB_p2 OB_p3 OB_p4
IBM A 100.1 100.2 100.5 100.8
FB A 100.1 100.2 100.6 100.8
TESLA B 100.1 100.3 100.7 100.8
IBM B 100.1 100.4 100.8 100.9
我正在尝试创建下表:
exchange OB IBM FB TESLA
A OB_1
A OB_1
B OB_1
我想知道是否有人可以帮助完成这项任务?我已经在上尝试了透视表推荐https://code.kx.com/q/kb/pivoting-tables/运气不佳
示例表:
t:flip `sym`exchange`OB_p1`OB_p2`OB_p3`OB_p4!flip (
(`IBM;`A;100.1;100.2;100.5;100.8);
(`FB;`A;100.1;100.2;100.6;100.8);
(`TESLA;`B;100.1;100.3;100.7;100.8);
(`IBM;`B;100.1;100.4;100.8;100.9))
提供:
sym exchange OB_p1 OB_p2 OB_p3 OB_p4
--------------------------------------
IBM A 100.1 100.2 100.5 100.8
FB A 100.1 100.2 100.6 100.8
TESLA B 100.1 100.3 100.7 100.8
IBM B 100.1 100.4 100.8 100.9
unpivot
函数取自https://gist.github.com/rianoc/a14b832f12908c4785e2297995db1e76
unpivot:{[tab;baseCols;pivotCols;kCol;vCol]
base:?[tab;();0b;{x!x}(),baseCols];
newCols:{[k;v;t;p] flip (k;v)!(count[t]#p;t p)}[kCol;vCol;tab] each pivotCols;
baseCols xasc raze {[b;n] b,'n}[base] each newCols
}
呼叫:
t:unpivot[t;`sym`exchange;`OB_p1`OB_p2`OB_p3`OB_p4;`OB;`val]
提供:
sym exchange OB val
--------------------------
FB A OB_p1 100.1
FB A OB_p2 100.2
FB A OB_p3 100.6
FB A OB_p4 100.8
IBM A OB_p1 100.1
IBM A OB_p2 100.2
IBM A OB_p3 100.5
IBM A OB_p4 100.8
IBM B OB_p1 100.1
IBM B OB_p2 100.4
IBM B OB_p3 100.8
IBM B OB_p4 100.9
TESLA B OB_p1 100.1
TESLA B OB_p2 100.3
TESLA B OB_p3 100.7
TESLA B OB_p4 100.8
piv
函数取自https://code.kx.com/q/kb/pivoting-tables/
链接中的f
在命名数据透视列时对此用例进行了简化
piv:{[t;k;p;v;f;g]
v:(),v;
G:group flip k!(t:.Q.v t)k;
F:group flip p!t p;
count[k]!g[k;P;C]xcols 0!key[G]!flip(C:f[v]P:flip value flip key F)!raze
{[i;j;k;x;y]
a:count[x]#x 0N;
a[y]:x y;
b:count[x]#0b;
b[y]:1b;
c:a i;
c[k]:first'[a[j]@'where'[b j]];
c}[I[;0];I J;J:where 1<>count'[I:value G]]/::[t v;value F]}
f:{[v;P]P[;0]}
g:{[k;P;c]k,(raze/)flip flip each 5 cut'10 cut raze reverse 10 cut asc c}
呼叫:
piv[t;`exchange`OB;(),`sym;(),`val;f;g]
提供:
exchange OB | FB IBM TESLA
--------------| -----------------
A OB_p1| 100.1 100.1
A OB_p2| 100.2 100.2
A OB_p3| 100.6 100.5
A OB_p4| 100.8 100.8
B OB_p1| 100.1 100.1
B OB_p2| 100.4 100.3
B OB_p3| 100.8 100.7
B OB_p4| 100.9 100.8
Rians答案是一种更通用的方法,但这应该有助于您理解操作:
q)ungroup exec((`FB`IBM`TESLA!3#0n),sym!flip(OB_p1;OB_p2;OB_p3;OB_p4))by exchange,OB:count[i]#enlist`OB_p1`OB_p2`OB_p3`OB_p4 from t
exchange OB FB IBM TESLA
--------------------------------
A OB_p1 100.1 100.1
A OB_p2 100.2 100.2
A OB_p3 100.6 100.5
A OB_p4 100.8 100.8
B OB_p1 100.1 100.1
B OB_p2 100.4 100.3
B OB_p3 100.8 100.7
B OB_p4 100.9 100.8
在功能形式中,它将是:
q)c:cols[t]where cols[t]like"*OB*";
q)s:exec distinct sym from t;
q)ungroup ?[t;();`exchange`OB!(`exchange;(#;(count;`i);enlist enlist c));(,;s!count[s]#0n;(!;`sym;(flip;enlist,c)))]
exchange OB IBM FB TESLA
--------------------------------
A OB_p1 100.1 100.1
A OB_p2 100.2 100.2
A OB_p3 100.5 100.6
A OB_p4 100.8 100.8
B OB_p1 100.1 100.1
B OB_p2 100.4 100.3
B OB_p3 100.8 100.7
B OB_p4 100.9 100.8