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