unpivot

Syntax

unpivot(obj, keyColNames, valueColNames, [func])

Arguments

obj is a table.

keyColNames is a string scalar/vector indicating column name(s).

valueColNames is a vector of column names. The specified columns will be converted into a single column. Note the values in these columns must have the same data type.

func is an optional parameter indicating a function that is applied to valueColNames before they're converted into one column.

Details

Convert the columns specified by valueColNames into a single column. Return a table with columns arranged in the following order: the columns specified by keyColNames, the "valueType" column, and the "value" column. "valueType" holds the column names specified by valueColNames and "value" holds the corresponding values of these columns. If func is specified, "value" holds the results of func applied on the columns specified by valueColNames.

Examples

t=table(1..3 as id, 2010.01.01 + 1..3 as time, 4..6 as col1, 7..9 as col2, 10..12 as col3, `aaa`bbb`ccc as col4, `ddd`eee`fff as col5, 'a' 'b' 'c' as col6);
t;
id time col1 col2 col3 col4 col5 col6
1 2010.01.02 4 7 10 aaa ddd 'a'
2 2010.01.03 5 8 11 bbb eee 'b'
3 2010.01.04 6 9 12 ccc fff 'c' 
t.unpivot(`id, `col1`col2);
id valueType value
1 col1 4
2 col1 5
3 col1 6
1 col2 7
2 col2 8
3 col2 9 
f = def(x): x.split("col")[1];
t.unpivot(`id, `col1`col2, f);
id valueType value
1 1 4
2 1 5
3 1 6
2 2 8
3 2 9 
t.unpivot(, `col1`col2);
valueType value
col1 4
col1 5
col1 6
col2 7
col2 8
col2 9 
f = def(x): x.regexReplace("col", "var")
t.unpivot(`id, `col1`col2`col3, f);
id valueType value
1 var1 4
2 var1 5
3 var1 6
1 var2 7
2 var2 8
3 var2 9
1 var3 10
2 var3 11
3 var3 12 
t.unpivot(`time, `col4`col5)
time valueType value
2010.01.02 col4 aaa
2010.01.03 col4 bbb
2010.01.04 col4 ccc
2010.01.02 col5 ddd
2010.01.03 col5 eee
2010.01.04 col5 fff 
t = table(1..3 as id, 2010.01.01 + 1..3 as time, 8.1 9.2 11.3 as bid1, 12.4 11.1 10.5 as bid2, 10.1 10.2 10.3 as bid3, 10.1 10.2 10.3 as bid4, 10.1 11.2 9.3 as bid5, 7.7 8.2 10.5 as ask1, 11.4 10.1 9.5 as ask2, 9.6 9.2 11.3 as ask3, 12.1 7.2 8.3 as ask4, 10.1 12.5 8.9 as ask5);
t;
t1 = t.unpivot(`id`time, `bid1`bid2`bid3`bid4`bid5);
t2 = t.unpivot(, `ask1`ask2`ask3`ask4`ask5);
re = rename!(t1, `valueType`value, `bid_type`bid_value) join rename!(t2, `valueType`value, `ask_type`ask_value)
re;
id time bid_type bid_value ask_type ask_value
1 2010.01.02 bid1 8.1 ask1 8.1
2 2010.01.03 bid1 9.2 ask1 9.2
3 2010.01.04 bid1 11.3 ask1 11.3
1 2010.01.02 bid2 12.4 ask2 12.4
2 2010.01.03 bid2 11.1 ask2 11.1
3 2010.01.04 bid2 10.5 ask2 10.5
1 2010.01.02 bid3 10.1 ask3 10.1
2 2010.01.03 bid3 10.2 ask3 10.2
3 2010.01.04 bid3 10.3 ask3 10.3
1 2010.01.02 bid4 10.1 ask4 10.1
2 2010.01.03 bid4 10.2 ask4 10.2
3 2010.01.04 bid4 10.3 ask4 10.3
1 2010.01.02 bid5 10.1 ask5 10.1
2 2010.01.03 bid5 11.2 ask5 11.2
3 2010.01.04 bid5 9.3 ask5 9.3