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 stackoverflow.com/q/12391950/680068 stackoverflow.com/q/12603890/680068 — zx8754
– zx8754, Commented Apr 30, 2021 at 9:31

Ronak Shah · Accepted Answer · 2021-04-30 09:09:29Z

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, 2021 at 9:09

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jantje Houten Over a year ago

Hi Ronak, thank you. I have updated the question to add that I would need to perform operations on this same column.

Ronak Shah Over a year ago

@JantjeHouten You can use table[table[[2]]=="a", (2) := "c"]

zx8754 · Accepted Answer · 2021-04-30 09:36:00Z

2

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" ]

answered Apr 30, 2021 at 9:36

zx8754

56.7k12 gold badges131 silver badges229 bronze badges

Comments

akrun · Accepted Answer · 2021-04-30 17:03:04Z

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, 2021 at 17:03

akrun

891k38 gold badges590 silver badges700 bronze badges

Comments

lovestacksflow · Accepted Answer · 2021-05-04 12:31:22Z

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 4, 2021 at 12:31

lovestacksflow

5834 silver badges16 bronze badges

Comments

Jantje Houten · Accepted Answer · 2021-04-30 09:31:22Z

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, 2021 at 9:31

Jantje Houten

1517 bronze badges

Collectives™ on Stack Overflow

Filter by column index number data.table R

5 Answers 5

2 Comments

Comments

data

Comments

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

2 Comments

Comments

data

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related