Data Table - Select Value of Column by Name from Another Column

Data Table - Select Value of Column by Name From Another Column

Another option:

d[ , value.of.col := diag(as.matrix(.SD)), .SDcols = d[ , name.of.col]]
> d
   value.1 value.2 name.of.col value.of.col
1:     one     two     value.1          one
2:     uno     dos     value.2          dos
3:       1       2     value.1            1

EDIT add a faster solution:

d[ , value.of.col :=
      melt(d,id.vars='name.of.col')[name.of.col==variable, value]]

data.table: Assign a column value where the column is specified by another column

test[, nfeat := .SD[[.BY[[1]]]], by = node_feature_name]
test[nfeat <= node_split, index := node_child_left]

Other ways of creating nfeat:

Data Table - Select Value of Column by Name From Another Column

How to select the columns by the content in another column in data.table of R?

Select values from different columns based on a variable containing column names

I guess this is not a good way to structure your data (with column names referred to in another column), but without knowing more, I can't really say how best to improve it.

Re speed, we don't know whether the full data has a ton more columns like a and b or a ton more rows, so I am not going to make my own example data to test with.

Select column dynamically based on value from another column in R

By looping through the sequence of rows, extract the value with get and assign it to create 'y'

dt[, y := .SD[, get(x), seq_len(.N)]$V1]
dt
#   a b c x y
#1: 2 5 1 a 2
#2: 3 7 2 b 7
#3: 5 7 3 c 3

Select column using the value in other row of a data.table in R

We can use get after looping through sequence of rows

DT[, W :=  get(Z) , 1:nrow(DT)]

Or with eval(as.name

DT[,  W := eval(as.name(Z)) , 1:nrow(DT)]

Select values from different columns based on a variable containing column names

An excuse to use the obscure .BY:

DT[, newval := .SD[[.BY[[1]]]], by=new]

   col1 col2 col3  new newval
1:    1    4   55 col1      1
2:    2    3   44 col2      3
3:    3   34   35 col2     34
4:    4   44   87 col3     87

How it works. This splits the data into groups based on the strings in new. The value of the string for each group is stored in newname = .BY[[1]]. We use this string to select the corresponding column of .SD via .SD[[newname]]. .SD stands for Subset of Data.

Alternatives. get(.BY[[1]]) should work just as well in place of .SD[[.BY[[1]]]]. According to a benchmark run by @David, the two ways are equally fast.

How to retrieve a column name from a table that is stored as a value in another table

i add the [Last Refreshed] column to my tables and write this query and give me the correct answer

DROP TABLE  IF EXISTS #DB_DUMMY

CREATE TABLE  #DB_DUMMY (
[TABLENAME] VARCHAR(512),
[LAST_REFRESHED] VARCHAR(533)
);

DECLARE @COMMAND NVARCHAR(MAX)

SELECT @COMMAND = STRING_AGG(' INSERT INTO #DB_DUMMY SELECT DISTINCT '+CHAR(39)+T.name+CHAR(39)+',['+C.name+'] FROM '+S.name+'.'+T.name + ' GO', CHAR(13)+CHAR(10))
FROM sys.all_columns C
INNER JOIN sys.tables T ON C.object_id = T.object_id
INNER JOIN sys.schemas S ON T.schema_id = S.schema_id
WHERE C.name = 'Last Refreshed'

PRINT(@COMMAND)

EXEC(@COMMAND)

SELECT * FROM #DB_DUMMY

two first line with IF EXISTS is new syntax in sql server 2017

data.table - Selecting by comparing list of columns to list of values

Assuming that the 'values' to be filtered are the ones corresponding to the 'columns' selected, we can do a comparison with Map and Reduce with &

dt[dt[ , Reduce(`&`, Map(`==`, .SD, values)) , .SDcols = columns]]

As a function

filterDT <- function(dt, columns, values) {
       dt[dt[ , Reduce(`&`, Map(`==`, .SD, values)) , .SDcols = columns]]
   }

filterDT(dt, c("V1", "V3"), c(1, 2))
#   V1 V2 V3
#1:  1  4  2

Or another option is setkey

setkeyv(dt, c("V1", "V3"))
dt[.(1, 2)]
#   V1 V2 V3
#1:  1  4  2

Assign certain values to a column in data.table based on another column

An easier option is to convert to factor with levels specified as unique elements of the column and coerce to integer with as.integer

df1$A <- as.integer(factor(df1$stimulus, levels = unique(df1$stimulus)))

Or use match on the unique values

df1$A <- with(df1, match(stimulus, unique(stimulus)))

-Update

If it needs to be custom values, an option is also to specify the labels along with levels

df1$A <- with(df, as.numeric(as.character(factor(stimulus,
       levels = c("S4", "S9", "S5", "S10"), c("1.1", "2.2", "3.2" , "5.8")))))

---

Or a better option may to do a join/merge with a key/value dataset

keydat <- data.frame(stimulus = c("S4", "S9", "S5", "S10"),
            A = c(1.1, 2.2, 3.2, 5.8))
df2 <- merge(df1, keydat, all.x = TRUE)

---

Related Topics