Search
Search

Using data.table to select rows by distance from another row

Anshu says Reinstate Monica Published at Java
16
Anshu says Reinstate Monica

Let's say I have the following data.table.

library(data.table)
DT <- data.table(x=1:6, y=c(0,0,1,0,0,0))

Could I write some command DT[...] that selects all the rows within 2 rows of the one in which y=1? That is, using proximity to row three, I want to select rows 1-5.

akrun

Here is one option to loop over the position index (which(y == 1)) with sapply, create a sequence by adding/subtracting 2 to it, get the unique elements (in case of overlaps) and subset the rows by using that i

library(data.table)
DT[unique(sapply(which(y==1), function(i) (i-2):(i + 2)))]

-output

#   x y
#1: 1 0
#2: 2 0
#3: 3 1
#4: 4 0
#5: 5 0

If there are negative index, we can subset those

i1 <- DT[,unique(sapply(which(y==1), function(i) (i-2):(i + 2)))][,1]
DT[i1[i1 > 0]]

Collected from the Internet

Please contact [email protected] to delete if infringement.

edited at
0

Comments

0 comments
Login to comment

Related

From Dev

select rows from table based on data from another table

From Dev

select rows from table based on data from another table

From Dev

Insert new rows into table but copy data from another row in the table

From Dev

MySQL select row with two matching joined rows from another table

From Dev

Selecting two rows from another table using one row

From Dev

Select rows according row type in another table

From Dev

Select two rows, using data from one row

From Dev

Missing rows when duplicating data from another table using MySQL

From Dev

Select 100 rows from data table using query

From Dev

How to select rows from one data.table to apply in another data.table?

From Dev

how to select one row from one table and multiple rows from other table using joins in mysql,

From Dev

SELECT one row of a table, And all rows in another table

From Dev

MySQL Insert INTO using SELECT from another table with additional data

From Dev

Insert multiple rows from select into another table

From Dev

mysql: select rows from another table as columns

From Dev

Select all rows from a table except where row in another table with same id has a particular value in another column

From Dev

Select rows not in another table

From Dev

How to select data from several rows in a single table in one result row?

From Dev

Insert many rows from a table into one unique row in another table

From Dev

Update a specific row with data from another table

From Dev

delete multiple rows of a table where this data came from another select in postgres

From Dev

Move data from row to another row within a group of specified rows

From Dev

Dynamically add rows into a table using data from another table-jQuery

From Dev

Copy rows of data from one table to another table in same database using pgadmin

From Dev

Select data (join?) from only one table using an id from another table

From Dev

Select rows from a table if one of them matches with another table

From Dev

Select all the rows from the second row from a table?

From Dev

Delete rows from a data table that exists in another data table

From Dev

Delete rows from a data table that exists in another data table

Related Related

  1. 1

    select rows from table based on data from another table

  2. 2

    select rows from table based on data from another table

  3. 3

    Insert new rows into table but copy data from another row in the table

  4. 4

    MySQL select row with two matching joined rows from another table

  5. 5

    Selecting two rows from another table using one row

  6. 6

    Select rows according row type in another table

  7. 7

    Select two rows, using data from one row

  8. 8

    Missing rows when duplicating data from another table using MySQL

  9. 9

    Select 100 rows from data table using query

  10. 10

    How to select rows from one data.table to apply in another data.table?

  11. 11

    how to select one row from one table and multiple rows from other table using joins in mysql,

  12. 12

    SELECT one row of a table, And all rows in another table

  13. 13

    MySQL Insert INTO using SELECT from another table with additional data

  14. 14

    Insert multiple rows from select into another table

  15. 15

    mysql: select rows from another table as columns

  16. 16

    Select all rows from a table except where row in another table with same id has a particular value in another column

  17. 17

    Select rows not in another table

  18. 18

    How to select data from several rows in a single table in one result row?

  19. 19

    Insert many rows from a table into one unique row in another table

  20. 20

    Update a specific row with data from another table

  21. 21

    delete multiple rows of a table where this data came from another select in postgres

  22. 22

    Move data from row to another row within a group of specified rows

  23. 23

    Dynamically add rows into a table using data from another table-jQuery

  24. 24

    Copy rows of data from one table to another table in same database using pgadmin

  25. 25

    Select data (join?) from only one table using an id from another table

  26. 26

    Select rows from a table if one of them matches with another table

  27. 27

    Select all the rows from the second row from a table?

  28. 28

    Delete rows from a data table that exists in another data table

  29. 29

    Delete rows from a data table that exists in another data table

HotTag

Archive