Selection of rows from 2 dataframes based on multiple conditions

Question

I have 2 data.frames

> abc
    V1     V2     V3          V4  V5 V6       V7       V8            V9    V10    V11       V12          V13
1 chr1 812640 813470 Rank_108039   5  .  2.51728  2.10797  0.59423|chr1 803450 812182 NR_027055       FAM41C
2 chr1 842313 842638 Rank_154173   3  .  2.34097  1.79807  0.35120|chr1 852197 855072 NR_026874 LOC100130417
3 chr1 843404 843769 Rank_154173   3  .  2.34097  1.79807  0.35120|chr1 852197 855072 NR_026874 LOC100130417
4 chr1 849172 849318 Rank_180753   2  .  2.19849  1.65655  0.25215|chr1 852197 855072 NR_026874 LOC100130417
5 chr1 761091 763246  Rank_11761 227  . 10.29544 24.83220 22.77738|chr1 763177 794826 NR_047525    LINC01128

> cde
    V1     V2     V3         V4  V5 V6       V7       V8            V9    V10    V11       V12          V13
1 chr1  28565  28699 Rank_31267   1  .  2.17937  1.99334  0.18208|chr1  14361  29370 NR_024540       WASH7P
2 chr1 712911 714068 Rank_12239 208  .  8.78112 22.93857 20.88265|chr1 700244 714068 NR_033908 LOC100288069
3 chr1 761091 762902 Rank_11761 227  . 10.29544 24.83220 22.77738|chr1 761585 762902 NR_024321    LINC00115
4 chr1 761091 763246 Rank_11761 227  . 10.29544 24.83220 22.77738|chr1 763177 794826 NR_047525    LINC01128

I want to make a new dataframe which contains all those rows for which abc$V12 == cde$V12 AND abc$V13 == cde$V13 I have tried many possible option (subset, dplyr's filter, sqldf's SELECT) but I am unable to do it.

According to these conditions, my final data.frame will only be having the row5 of abc as it fulfills the required condition. So the output will be:

> final.df
5 chr1 761091 763246  Rank_11761 227  . 10.29544 24.83220 22.77738|chr1 763177 794826 NR_047525    LINC01128

Here are the dput of the data.frames:

> dput(abc)
structure(list(V1 = structure(c(1L, 1L, 1L, 1L, 1L), .Label = "chr1", class = "factor"), 
    V2 = c(812640L, 842313L, 843404L, 849172L, 761091L), V3 = c(813470L, 
    842638L, 843769L, 849318L, 763246L), V4 = structure(c(1L, 
    3L, 3L, 4L, 2L), .Label = c("Rank_108039", "Rank_11761", 
    "Rank_154173", "Rank_180753"), class = "factor"), V5 = c(5L, 
    3L, 3L, 2L, 227L), V6 = structure(c(1L, 1L, 1L, 1L, 1L), .Label = ".", class = "factor"), 
    V7 = c(2.51728, 2.34097, 2.34097, 2.19849, 10.29544), V8 = c(2.10797, 
    1.79807, 1.79807, 1.65655, 24.8322), V9 = structure(c(3L, 
    2L, 2L, 1L, 4L), .Label = c("0.25215|chr1", "0.35120|chr1", 
    "0.59423|chr1", "22.77738|chr1"), class = "factor"), V10 = c(803450L, 
    852197L, 852197L, 852197L, 763177L), V11 = c(812182L, 855072L, 
    855072L, 855072L, 794826L), V12 = structure(c(2L, 1L, 1L, 
    1L, 3L), .Label = c("NR_026874", "NR_027055", "NR_047525"
    ), class = "factor"), V13 = structure(c(1L, 3L, 3L, 3L, 2L
    ), .Label = c("FAM41C", "LINC01128", "LOC100130417"), class = "factor")), .Names = c("V1", 
"V2", "V3", "V4", "V5", "V6", "V7", "V8", "V9", "V10", "V11", 
"V12", "V13"), class = "data.frame", row.names = c(NA, -5L))
> dput(cde)
structure(list(V1 = structure(c(1L, 1L, 1L, 1L), .Label = "chr1", class = "factor"), 
    V2 = c(28565L, 712911L, 761091L, 761091L), V3 = c(28699L, 
    714068L, 762902L, 763246L), V4 = structure(c(3L, 2L, 1L, 
    1L), .Label = c("Rank_11761", "Rank_12239", "Rank_31267"), class = "factor"), 
    V5 = c(1L, 208L, 227L, 227L), V6 = structure(c(1L, 1L, 1L, 
    1L), .Label = ".", class = "factor"), V7 = c(2.17937, 8.78112, 
    10.29544, 10.29544), V8 = c(1.99334, 22.93857, 24.8322, 24.8322
    ), V9 = structure(c(1L, 2L, 3L, 3L), .Label = c("0.18208|chr1", 
    "20.88265|chr1", "22.77738|chr1"), class = "factor"), V10 = c(14361L, 
    700244L, 761585L, 763177L), V11 = c(29370L, 714068L, 762902L, 
    794826L), V12 = structure(c(2L, 3L, 1L, 4L), .Label = c("NR_024321", 
    "NR_024540", "NR_033908", "NR_047525"), class = "factor"), 
    V13 = structure(c(4L, 3L, 1L, 2L), .Label = c("LINC00115", 
    "LINC01128", "LOC100288069", "WASH7P"), class = "factor")), .Names = c("V1", 
"V2", "V3", "V4", "V5", "V6", "V7", "V8", "V9", "V10", "V11", 
"V12", "V13"), class = "data.frame", row.names = c(NA, -4L))

akrun · Answer 1 · 2016-07-12T09:54:01.770

2

We can use merge

merge(abc[c("V12", "V13")], cde, by = c("V12", "V13"))
#        V12       V13   V1     V2     V3         V4  V5 V6       V7      V8            V9    V10    V11
#1 NR_047525 LINC01128 chr1 761091 763246 Rank_11761 227  . 10.29544 24.8322 22.77738|chr1 763177 794826

If we need to split the "V9" column into

cbind(abc, read.table(text = as.character(abc$V9), sep="|", header= FALSE))

edited Jul 12 '16 at 09:54

answered Jul 12 '16 at 08:27

akrun

874,273
37
540
662

Can you suggest me how I can split the column `V9` on `|` so that I get 2 new columns one having index 1 (0.59423) and other the 2nd (chr1) (The example given is of row1 from abc) – Newbie Jul 12 '16 at 09:16
@Newbie You can use `strsplit(df1$V9, '[|]')` – akrun Jul 12 '16 at 09:20
it just splits, this one was what I wanted: `ab <- within(abc, foo <- data.frame(do.call('rbind', strsplit(as.character(V9), '|', fixed = TRUE))))` – Newbie Jul 12 '16 at 09:39
@Newbie You can also use `read.table(text = df1$V9, sep="|", header= FALSE)` But anyway, you acccpted another solution and just ask me, then when you get some answer, you are trying to make fun of the answer – akrun Jul 12 '16 at 09:45
This is a misunderstanding, I am not making fun of the answer. The answer you suggested just splitted that column and as I was looking for it myself, I found what I actually wanted, so shared that information, in case if it could be of help to somebody else. I don't see at all the fun aspect in it. – Newbie Jul 12 '16 at 09:51
Can you help me with this: [Link](http://stackoverflow.com/questions/38432826/looking-for-ranges-in-dataframe-values) – Newbie Jul 18 '16 at 09:29
Can you help me with this [Question](http://stackoverflow.com/questions/38501840/dataframe-processing) – Newbie Jul 21 '16 at 12:00
Hello, I am stuck with this [question](http://stackoverflow.com/questions/38501840/dataframe-processing) Can you please help me with it? – Newbie Jul 25 '16 at 07:41

score 1 · Accepted Answer · answered Jul 12 '16 at 08:55

1

Another option using match_df of plyr package

library(plyr)
match_df(abc,cde,on = c("V12","V13"))

answered Jul 12 '16 at 08:55

user2100721

3,557
2
20
29

Can you guide me how can I retrieve those rows (for example, first 4 rows from abc dataframe) which do not fulfill the required condition)? – Newbie Jul 12 '16 at 09:05
@Newbie : Use this `anti_join(abc,final.df)` from `dplyr` package. – user2100721 Jul 12 '16 at 09:18

Selection of rows from 2 dataframes based on multiple conditions

2 Answers2