Select rows in one dataframe based on column values in another?

Question

I have two dataframes

df1

df2

      p1 p2 p3
site1 0  0  1
site2 4  1  10
site3 15 0  0

Now I want to create a list of dataframes for each site in df2. The dataframes should only consist of rows from df1 that are in df2 and are >0.

For example the new df in my list should be these:

site1:    i1 i2 i3
       p3 0  0  1


site2:     i1 i2 i3
        p1 1  1  1
        p2 0  1  1
        p3 0  0  1  

site3:     i1 i2 i3
        p1 1  1  1

Besides the list issue, I can't get R to select the right rows in df1. What I have done so far is using %in%

 test<-df1[df1[,1] %in% (df2[1,-1]>0),]

which gives me the colnames(df1) with <0 rows> (or 0-length row.names)

Has anyone an idea where I went wrong? I don' know if I can use merge somehow since I need to check for right colname and a value of >0.

Vandenman · Accepted Answer · 2016-02-15 14:30:38Z

1

I think you should you which instead %in%. of Let's define a function using which that does this for any particular row:

foo = function(x, df1, df2) {
  df1[which(df2[x, ] > 0), ]
}

now we use apply to do the function above over all rows in df2.

apply(matrix(1:nrow(df2)), 1, foo, df1 = df1, df2 = df2)

answered Feb 15, 2016 at 14:30

Vandenman

3,2161 gold badge23 silver badges35 bronze badges

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

1 Comment

eugenego Over a year ago

Thank you very much, that worked quite well. However, I first got some strange results and after I looked into it i found that the columns in df2 were not sorted alphabetically. Since only the number not the particular colname of the df2[x,]>0 is passed I got wrong results (which was fixed after ordering them). I just wander if there is a way to bypass this problem? Otherwise I aways have to check whether rownames(df1) and colnames(df2) match (or are identical). That is why I started with the %in% operator becasue I figured it looks at the actual strings.

Collectives™ on Stack Overflow

Select rows in one dataframe based on column values in another?

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related