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 (optional) indicates 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:

  • The "valueType" column holds the results of func applied on the columns specified by valueColNames if func is specified, otherwise "valueType" holds the column names specified by valueColNames.

  • The "value" column holds the corresponding values of these columns.

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