Replace all values in a column of one dataframe using values in a row of another dataframe (matching by row name and column name)

Question

I have a dataframe which is a presence absence matrix of species found in different locations (1 indicates presence, 0 absence) like so:

df1<-data.frame(replicate(5,sample(0:1,5,rep=TRUE)))
row.names(df1)<-c("location.1","location.2","location.3","location.4","location.5")
names(df1)<-c("species.1","species.2","species.3","species.4","species.5") 

           species.1 species.2 species.3 species.4 species.5
location.1         0         1         0         1         1
location.2         1         0         0         1         0
location.3         0         0         1         0         0
location.4         1         0         1         0         1
location.5         0         0         1         0         0

I have a second dataframe which has values for each species, like so:

df2<-c(2,4,6,8,10)
df2<-as.matrix(df2)
row.names(df2)<-c("species.1","species.2","species.3","species.4","species.5")

          [,1]
species.1    2
species.2    4
species.3    6
species.4    8
species.5   10

I would like to replace all 1s in the first dataframe, on a column by column basis, with the matching value in the second dataframe, based on the species names. Producing this:

           species.1 species.2 species.3 species.4 species.5
location.1         0         4         0         8        10 
location.2         2         0         0         8         0
location.3         0         0         6         0         0
location.4         2         0         6         0        10
location.5         0         0         6         0         0

I have no idea how I'd go about doing this though, and can't find any similar examples online.

Does anyone have any advice?

Try df1*df2[,1][col(df1)] or sweep(df1, 2, df2[,1], "*") or mapply("*", df1, df2[,1]) — akrun
– akrun, Commented Apr 20, 2017 at 15:01
Thanks a lot, they all work perfectly (can't believe it was so simple, you should have seen the code I tried to write for this!) — Daniel Swindlehurst
– Daniel Swindlehurst, Commented Apr 20, 2017 at 15:19

akrun · Accepted Answer · 2017-04-20 15:24:00Z

0

We can do this by one of the options below

 df1*df2[,1][col(df1)]

Here, we multiply the first dataset with the first column of 'df2' replicated to make the lengths same

Or another option is sweep which has the MARGIN, STATS and FUN argument to apply the function on corresponding elements of column (MARGIN = 1 does on rows)

 sweep(df1, 2, df2[,1], "*")

Or we can use mapply to loop over the columns and multiply by the corresponding elements of the vector 'df2[,1]`

 mapply("*", df1, df2[,1])

answered Apr 20, 2017 at 15:24

akrun

891k38 gold badges590 silver badges700 bronze badges

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

Collectives™ on Stack Overflow

Replace all values in a column of one dataframe using values in a row of another dataframe (matching by row name and column name)

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related