replaceColumn!
Syntax
replaceColumn!(table, colName, newCol)
Arguments
table is a non-shared in-memory table or a DFS table.
colName is a string indicating the name of the column to replace. When table is an in-memory table, colName can also be a vector of strings indicating multiple column names.
newCol is the column values to replace with. When colName is a scalar, newCol is a vector with the same number of elements as the rows of table; when colName is a vector, newCol is a tuple containing the same number of elements as colName. Each tuple element is a vector with the same number of elements as the rows of table.
Details
Replace table column(s) with the specified vector(s). The data type of the new column is the same as the data type of the specified vector. The original column name is not changed. When table is an in-memory table, replacing multiple columns at once is supported. Note that the multi-column replacement operation is not atomic, which means in cases of some system errors, some specified column replacements may succeed while others fail partway through.
To update column's value without changing its data type, we can use both
replaceColumn!
and SQL statement update. Only
replaceColumn!
, however, can change a column's data type.
Examples
sym = `C`MS`MS`MS`IBM`IBM`C`C`C
price= 49.6 29.46 29.52 30.02 174.97 175.23 50.76 50.32 51.29
qty = 2200 1900 2100 3200 6800 5400 1300 2500 8800
timestamp = [09:34:07,09:36:42,09:36:51,09:36:59,09:32:47,09:35:26,09:34:16,09:34:26,09:38:12]
t = table(timestamp, sym, qty, price)
schema(t).colDefs;
name | typeString | typeInt | comment |
---|---|---|---|
timestamp | SECOND | 10 | |
sym | STRING | 18 | |
qty | INT | 4 | |
price | DOUBLE | 16 |
To change the data type of column "sym" to SYMBOL:
syms=symbol(exec sym from t)
replaceColumn!(t,`sym,syms);
schema(t).colDefs;
name | typeString | typeInt | comment |
---|---|---|---|
timestamp | SECOND | 10 | |
sym | SYMBOL | 17 | |
qty | INT | 4 | |
price | DOUBLE | 16 |
To replace columns "price" and "timestamp":
newPrice =round(t.qty)
newTimestamp = minute(t.timestamp)
replaceColumn!(t, `price`timestamp, (newPrice,newTimestamp))
schema(t).colDefs;
name |
typeString |
typeInt |
extra |
comment |
---|---|---|---|---|
timestamp | MINUTE | 9 | ||
sym | SYMBOL | 17 | ||
qty | INT | 4 | ||
price | INT | 4 |
login("admin","123456")
if(existsDatabase("dfs://replaceColumn")){
dropDatabase("dfs://replaceColumn")
}
n=10
month=take(2012.06.13..2012.06.13, n);
x=rand(1.0, n);
t=table(month, x);
db=database("dfs://replaceColumn", VALUE, 2012.06.13..2012.06.23)
pt = db.createPartitionedTable(t, `pt, `month);
pt.append!(t);
schema(pt).colDefs
name | typeString | typeInt | extra | comment |
---|---|---|---|---|
month | DATE | 6 | ||
x | DOUBLE | 16 |
newCols=int(exec x from loadTable("dfs://replaceColumn",`pt))
replaceColumn!(loadTable("dfs://replaceColumn",`pt), `x, newCols)
schema(pt).colDefs
name | typeString | typeInt | extra | comment |
---|---|---|---|---|
month | DATE | 6 | ||
x | INT | 4 |