rianoc/unpivot.md

## unpivot.md

      
    Raw
  

              unpivot.md
            
          
    /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
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
 }
q)data
time                          sensor_1  sensor_2 sensor_3
---------------------------------------------------------
2001.11.03D01:33:25.901806336 0.3539384 31.18175 39.16806
2001.11.03D01:33:25.901806336 31.86017  24.91366 11.92248
2001.11.03D01:33:25.901806336 2.683428  3.449276 39.81772
2001.11.03D01:33:25.901806336 25.55626  13.28844 30.64216
2001.11.03D01:33:25.901806336 21.3122   15.5629  37.53362
q)unpivot[data;`time;`sensor_1`sensor_2`sensor_3;`sensorName;`sensorValue]
time                          sensorName sensorValue
----------------------------------------------------
2001.11.03D01:33:25.901806336 sensor_1   0.3539384
2001.11.03D01:33:25.901806336 sensor_1   31.86017
2001.11.03D01:33:25.901806336 sensor_1   2.683428
2001.11.03D01:33:25.901806336 sensor_1   25.55626
2001.11.03D01:33:25.901806336 sensor_1   21.3122
2001.11.03D01:33:25.901806336 sensor_2   31.18175
2001.11.03D01:33:25.901806336 sensor_2   24.91366
2001.11.03D01:33:25.901806336 sensor_2   3.449276
2001.11.03D01:33:25.901806336 sensor_2   13.28844
2001.11.03D01:33:25.901806336 sensor_2   15.5629
2001.11.03D01:33:25.901806336 sensor_3   39.16806
2001.11.03D01:33:25.901806336 sensor_3   11.92248
2001.11.03D01:33:25.901806336 sensor_3   39.81772
2001.11.03D01:33:25.901806336 sensor_3   30.64216
2001.11.03D01:33:25.901806336 sensor_3   37.53362