Filter by column index number data.table R

Question

Interesting I am unable to find a way to filter using the column number. I do not know the name of the column because it changes name, but I always know the position of the column.

This seems pretty trivial but it seems like I can only reference the i portion using the column name.

table = data.table(one = c(1,2,3), two = c("a","b","c"))

> table
   one two
1:   1   a
2:   2   b
3:   3   c

I do not know that the second column is "two". I just want to filter by second column.

> table[two == "a"]
   one two
1:   1   a

UPDATE:

As Ronak described, I could use

> table[table[[2]]=="a"]

   one two
1:   1   a

However I would next like to update this same column, for example I would like to turn "a" into "c".

what I need:

> table
   one two
1:   1   c
2:   2   b
3:   3   c

I have tried:

> table[table[[2]]=="a", table[[2]]:= "c"]
> table
   one two    a    b    c
1:   1   a    c    c    c
2:   2   b <NA> <NA> <NA>
3:   3   c <NA> <NA> <NA>

So it seems like I am taking all the values in the second column and creating new columns for them instead of just changing the filtered rows to c.

> table[table[[2]]=="a", table[2]:= "c"]

Error in `[.data.table`(table, table[[2]] == "a", `:=`(table[2], "c")) : 
  LHS of := must be a symbol, or an atomic vector (column names or positions).

So I think I need to know the position of the second column.

Related, possible duplicate of https://stackoverflow.com/q/12391950/680068 https://stackoverflow.com/q/12603890/680068 — zx8754, Apr 30 '21 at 09:31

score 5 · Accepted Answer · answered Apr 30 '21 at 09:09

5

Using [[ works :

library(data.table)

dt <- data.table(a = 1:5, b = 2:6)
dt[dt[[1]] == 1]

#   a b
#1: 1 2

This gives the same output as dt[a == 1].

answered Apr 30 '21 at 09:09

Ronak Shah

377,200
20
156
213

Hi Ronak, thank you. I have updated the question to add that I would need to perform operations on this same column. – Jantje Houten Apr 30 '21 at 09:21
3

@JantjeHouten You can use `table[table[[2]]=="a", (2) := "c"]` – Ronak Shah Apr 30 '21 at 09:35

score 2 · Answer 2 · answered Apr 30 '21 at 09:36

As we know we need the 2nd column, get the 2nd column name, and use "variable as column name", see example:

library(data.table)
d <- data.table(one = c(1,2,3), two = c("a","b","c"))

# get the 2nd column name
myCol <- colnames(d)[ 2 ]

# subset
d[ get(myCol) == "a", ]

# subset and update
d[ get(myCol) == "a", (myCol) := "c" ]

score 2 · Answer 3 · answered Apr 30 '21 at 17:03

2

We can use .SD

dt[dt[, .SD[[1]] == 1]]
#   a b
#1: 1 2

data

dt <- data.table(a = 1:5, b = 2:6)

answered Apr 30 '21 at 17:03

akrun

874,273
37
540
662

score 1 · Answer 4 · answered May 04 '21 at 12:31

1

You can also try this:

table[[2]][table[[2]]=="a"] <- "c"
table
> table
   one two
1:   1   c
2:   2   b
3:   3   c

answered May 04 '21 at 12:31

lovestacksflow

521
3
14

score 0 · Answer 5 · answered Apr 30 '21 at 09:31

0

I have figured it out:

> table[table[[2]]=="a", colnames(table)[2]:= "c"]
> table
   one two
1:   1   c
2:   2   b
3:   3   c

Thanks!

answered Apr 30 '21 at 09:31

Jantje Houten

151
7

Filter by column index number data.table R

5 Answers5

data