CODEWITHSUNDEEP
rlistdataframe
eugenego
eugenego

Reputation: 183

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

I have two dataframes

df1

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

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.

Upvotes: 1

Views: 1302

Answers (1)

Vandenman
Vandenman

Reputation: 3176

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)

Upvotes: 1

Related Questions