Geek Answers Handbook

Filter rows below a specific row element within a group

I have a framework with a set of dated events on every line associated with a location. Within each location, I have an index event and a series of different consistent events that could occur before and / or after the index event. I need a subset of all the consistent events that occurred before the index event for each location. The data structure is as follows.

locid    match      date          score      iid
1        index      4/11/2013      15        1
1        matched    1/09/2013      23        2
1        matched    14/04/2013      1        3
1        matched    7/1/2014       21        4
2        index      2/4/2013       12        1
2        matched    1/2/2013       10        2
3        index      1/5/2013       23        1
3        matched    2/5/2013       10        2
4        index      3/3/2013        9        1
4        matched    10/2/2013      32        2
4        matched    1/10/2012      15        3
4        matched    4/3/2013       12        4
4        matched    10/3/2013      10        5

And I need to multiply the dataframe so that I finish only rows with a date below the index event date for each location:

locid    match      date          score      iid
1        matched    1/09/2013      23        2
1        matched    14/04/2013      1        3
2        matched    1/2/2013       10        2
4        matched    10/2/2013      32        2
4        matched    1/10/2012      15        3

The first time I ask here, so I hope that I am not mistaken. I tried various permutations of the solutions inside R, but I try my best to find the right one.

+4
2

a data.table ( , df)

library(data.table)
setDT(df)[, date := as.Date(date, format = "%d/%m/%Y")][, 
           .SD[date < date[match == "index"]], by = locid]
#    locid   match       date score iid
# 1:     1 matched 2013-09-01    23   2
# 2:     1 matched 2013-04-14     1   3
# 3:     2 matched 2013-02-01    10   2
# 4:     4 matched 2013-02-10    32   2
# 5:     4 matched 2012-10-01    15   3

R

df <- transform(df, date = as.Date(date, format = "%d/%m/%Y"))
do.call(rbind, by(df, df$locid, FUN = function(x) x[with(x, date < date[match == "index"]), ]))
#      locid   match       date score iid
# 1.2      1 matched 2013-09-01    23   2
# 1.3      1 matched 2013-04-14     1   3
# 2        2 matched 2013-02-01    10   2
# 4.10     4 matched 2013-02-10    32   2
# 4.11     4 matched 2012-10-01    15   3

R

df <- transform(df, date = as.Date(date, format = "%d/%m/%Y"))
do.call(rbind, lapply(split(df, df$locid), function(x) x[with(x, date < date[match == "index"]), ]))
#      locid   match       date score iid
# 1.2      1 matched 2013-09-01    23   2
# 1.3      1 matched 2013-04-14     1   3
# 2        2 matched 2013-02-01    10   2
# 4.10     4 matched 2013-02-10    32   2
# 4.11     4 matched 2012-10-01    15   3

, date date, R . locid , , date, match == index

+5

, dplyr:

require(dplyr)
df %>%
  mutate(date = as.Date(date, format = "%d/%m/%Y")) %>%
  group_by(locid) %>%
  filter(match == "matched" & date < date[match == "index"])

#Source: local data frame [5 x 5]
#Groups: locid
#
#  locid   match       date score iid
#1     1 matched 2013-09-01    23   2
#2     1 matched 2013-04-14     1   3
#3     2 matched 2013-02-01    10   2
#4     4 matched 2013-02-10    32   2
#5     4 matched 2012-10-01    15   3

real Date -format, locid, , matched == "matched" Date - .

. , match == "matched" , , <, (, ), , , , , , <=, match == "matched", .

:

df <- structure(list(locid = c(1L, 1L, 1L, 1L, 2L, 2L, 3L, 3L, 4L, 
4L, 4L, 4L, 4L), match = structure(c(1L, 2L, 2L, 2L, 1L, 2L, 
1L, 2L, 1L, 2L, 2L, 2L, 2L), .Label = c("index", "matched"), class = "factor"), 
    date = structure(c(11L, 1L, 7L, 13L, 8L, 3L, 4L, 9L, 10L, 
    5L, 2L, 12L, 6L), .Label = c("1/09/2013", "1/10/2012", "1/2/2013", 
    "1/5/2013", "10/2/2013", "10/3/2013", "14/04/2013", "2/4/2013", 
    "2/5/2013", "3/3/2013", "4/11/2013", "4/3/2013", "7/1/2014"
    ), class = "factor"), score = c(15L, 23L, 1L, 21L, 12L, 10L, 
    23L, 10L, 9L, 32L, 15L, 12L, 10L), iid = c(1L, 2L, 3L, 4L, 
    1L, 2L, 1L, 2L, 1L, 2L, 3L, 4L, 5L)), .Names = c("locid", 
"match", "date", "score", "iid"), class = "data.frame", row.names = c(NA, 
-13L))
+3

More articles:


All Articles