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