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 |