Extract rows from a data frame under certain conditions

user_7821879 0
Viewed 530

I have to filter my dataframe in relation to particular condition. Better if the solution contemplates the use of dplyr.

i have a dataframe structure like this

  sentId.   B.   label.   partner. code
    1.      2.     3.       4.      123
    1.      2.     2.       4.      124
    4.      2.     3.       8.      125
    7.      3.     2.       7.      126

If the column label contains a particular value (for example, 3.), collect not only that Row but also all the Rows that have the same sentID and partner value of the previous one.

The expected results is this:

  sentId.   B.   label.   partner. code
    1.      2.     3.       4.      123
    1.      2.     2.       4.      124
    4.      2.     3.       8.      125
dplyr dataframe r
4 Answers

We can use %in% to filter the rows after grouping by 'sentId.` and 'partner.'

library(dplyr)
df1 %>%
  group_by(sentId., partner.) %>%
  filter(3 %in% label.)
# A tibble: 3 x 5
# Groups:   sentId. [2]
#  sentId.    B. label. partner.  code
#    <dbl> <dbl>  <dbl>    <dbl> <int>
#1       1     2      3        4   123
#2       1     2      2        4   124
#3       4     2      3        8   125

Or in a compact way with data.table

library(data.table)
setDT(df1)[, .SD[3 %in% label.], .(sentId., partner.)]

Or with base R 

df1[with(df1, ave(label.==3, sentId., partner., FUN = any)),]

data

df1 <- structure(list(sentId. = c(1, 1, 4, 7), B. = c(2, 2, 2, 3), label. = c(3, 
 2, 3, 2), partner. = c(4, 4, 8, 7), code = 123:126),
 class = "data.frame", row.names = c(NA, 
 -4L))
user_37322710

We can first find out the row indices where we have our interested label value and then use those indices to subset sentId and partner values from the entire dataframe.

label_value <- 3
inds <- df$label == label_value
df[with(df, sentId %in% sentId[inds] & partner %in% partner[inds]), ]

#  sentId B label partner code
#1      1 2     3       4  123
#2      1 2     2       4  124
#3      4 2     3       8  125

The same logic in dplyr would be

library(dplyr)

df %>%
  filter(sentId %in% sentId[label == label_value] & 
         partner %in% partner[label == label_value])
user_39629140

This problem can easily be formulated using SQL, so one option would be to use the sqldf library:

library(sqldf)

# your data frame df
sql <- "SELECT t1.\"sentId.\", t1.\"B.\", t1.\"label.\", t1.\"partner.\", t1.code
        FROM yourTable t1
        WHERE t1.\"label.\" = '3.' OR
            EXISTS (SELECT 1 FROM yourTable t2
                    WHERE t1.\"sentId.\" = t2.\"sentId.\" AND
                          t1.\"partner.\" = t2.\"partner.\" AND
                          t2.\"label.\" = '3.')"

result <- sqldf(sql)

enter image description here

Demo

Note: The above demo actually uses MariaDB, because SQLite was not working with the demo tool. But it still shows that the query logic be correct.

user_18632290

Using sqldf: It extracts sentID and partner with label 3 as two inner queries and fetches the result from it. 

names(df) <- gsub("\\.", "", names(df)) # to remove . from column name
sqldf("select * from df where (sentID IN (select sentID from df where label IS 3) OR 
      partner IN (select partner from df where label IS 3))")

Output:

  sentId B label partner code
1      1 2     3       4  123
2      1 2     2       4  124
3      4 2     3       8  125
user_58357630
Related