Send Feedback
Skip to content

How to Pivot and Unpivot a Table

This page shows you how to pivot and unpivot tables in KDB-X.

Pivoting in KDB-X transforms rows into columns, enabling better data analysis. It is commonly used to restructure data for reporting or visualization.

Overview

Simple pivot illustration

This pivot illustration assumes a table t:

q)t:([]k:1 2 3 2 3;p:`xx`yy`zz`xx`yy;v:10 20 30 40 50)
q)t
k p  v
-------
1 xx 10
2 yy 20
3 zz 30
2 xx 40
3 yy 50

We begin by getting the distinct pivot values from p. These will become our column names in addition to the key column k.

Note

p must be a column of symbols for this to work.

To pivot this table with k as the key and p as the pivot values, run the following:

q)P:asc exec distinct p from t      / get distinct pivot column names
q)P
`s#`xx`yy`zz
q)pvt:exec P#(p!v) by k:k from t    / create pivot table
q)pvt
k| xx yy zz
-| --------
1| 10
2| 40 20
3|    50 30

Another variation on creating the pivot table is:

q)pvt:exec P!(p!v)P by k:k from t
q)pvt
k| xx yy zz
-| --------
1| 10
2| 40 20
3|    50 30

General pivot function

Create a general pivot function, as follows:

/ pivot t, keyed by k, on p, exposing v
/ f, a function of v and pivot values, names the columns
/ g, a function of k, pivot values, and the return of f, orders the columns
/ either can be defaulted with (::)
/ conceptually, this is
/ exec f\[v;P\]!raze((flip(p0;p1;.))!/:(v0;v1;..))\[;P\]by k0,k1,.. from t
/ where P~exec distinct flip(p0;p1;..)from t
/ followed by reordering the columns and rekeying

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]
 `$raze each string raze P[;0],'/:v,/:\:P[;1]
 }

g:{[k;P;c]
 k,(raze/)flip flip each 5 cut'10 cut raze reverse 10 cut asc c
 }

Note

This pivot function is derived from a thread on the k4 listbox between Aaron Davies, Attila Vrabecz and Andrey Zholos.

Apply the general pivot function to sample data:

q)data:([]time:(3#09:00:00.000),(3#09:05:00.000),3#09:10:00.000;sensorName:9#`s1`s2`s3;reading:9?100f)
q)data
time         sensorName reading
---------------------------------
09:00:00.000 s1         70.72084
09:00:00.000 s2         90.57996
09:00:00.000 s3         63.21255
09:05:00.000 s1         0.7575313
09:05:00.000 s2         15.12747
09:05:00.000 s3         94.91208
09:10:00.000 s1         80.4568
09:10:00.000 s2         51.47673
09:10:00.000 s3         44.06643

q)show pivoted:piv[`data;(),`time;(),`sensorName;(),`reading;f;g]
time        | s1reading s2reading s3reading
------------| -----------------------------
09:00:00.000| 41.1597   49.31835  57.85203
09:05:00.000| 8.388858  19.59907  37.5638
09:10:00.000| 61.37452  52.94808  69.16099

q)show pivoted:0!`time`s1`s2`s3 xcol pivoted
time         s1       s2       s3
---------------------------------------
09:00:00.000 41.1597  49.31835 57.85203
09:05:00.000 8.388858 19.59907 37.5638
09:10:00.000 61.37452 52.94808 69.16099

General unpivot function

Create a general unpivot function, as follows:

/ tab : the table to operate on
/ baseCols : the columns not to unpivot
/ pivotCols : the columns which you wish to unpivot
/ kCol : the key name for unpivoted data
/ vCol :  the value name for unpivoted data
unpiv:{[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
 }

Apply the general unpivot function to sample data:

q)pivoted
time         s1       s2       s3
---------------------------------------
09:00:00.000 41.1597  49.31835 57.85203
09:05:00.000 8.388858 19.59907 37.5638
09:10:00.000 61.37452 52.94808 69.16099

q)unpivoted:unpiv[pivoted;`time;`s1`s2`s3;`sensorName;`reading]
q)unpivoted
time         sensorName reading
--------------------------------
09:00:00.000 s1         41.1597
09:00:00.000 s2         49.31835
09:00:00.000 s3         57.85203
09:05:00.000 s1         8.388858
09:05:00.000 s2         19.59907
09:05:00.000 s3         37.5638
09:10:00.000 s1         61.37452
09:10:00.000 s2         52.94808
09:10:00.000 s3         69.16099

q)unpivoted~data
1b

Summary

In this guide, you:

  • Learned how to pivot a table in q
  • Learned how to unpivot a table in q

You now have the essential skills to pivot and unpivot tables in KDB-X.