data frame set value based on matching specific row name to column name

Question

I want to set the value based on specific matching of rowname and colname in an R data frame. I have the row names (var1, var2, var3, var4 etc.) and the column names (x-var1-t1, x-var2-t1, x-var1-t4, x-var3-t1, x-var3-t7 etc). The row name needs to match the first "x-varN" part of the column name. For example, var1 row name should match with x-var1-t1 and x-var1-t4.

So this data frame:

      x-var1-t1   x-var2-t1   x-var1-t4   x-var3-t1   x-var3-t7
var1          0           0           0           0           0
var2          0           0           0           0           0
var3          0           0           0           0           0
var4          0           0           0           0           0

would change to this:

      x-var1-t1   x-var2-t1   x-var1-t4   x-var3-t1   x-var3-t7
var1          1           0           1           0           0
var2          0           1           0           0           0
var3          0           0           0           1           1
var4          0           0           0           0           0

What's the best way to perform this function?

Ronak Shah · Accepted Answer · 2018-08-23 03:13:20Z

2

We can use sapply to loop through rownames of df and use grepl to check which column has that row name and convert the value to 1 for those.

df[] <- t(sapply(rownames(df), function(x) as.numeric(grepl(x, colnames(df)))))
df

#     x.var1.t1 x.var2.t1 x.var1.t4 x.var3.t1 x.var3.t7
#var1         1         0         1         0         0
#var2         0         1         0         0         0
#var3         0         0         0         1         1
#var4         0         0         0         0         0

Or as suggested by @Dan Y we can skip the anonymous call and make this more compact by:

df[] <- +t(sapply(rownames(df), grepl, colnames(df)))

edited Aug 23, 2018 at 3:13

answered Aug 23, 2018 at 3:06

Ronak Shah

391k20 gold badges173 silver badges237 bronze badges

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

Comments

Onyambu · Accepted Answer · 2018-08-23 03:16:26Z

2

We can use adist to compare the rownames to columnnames.

 dat[] = +(!do.call(adist, c(partial = TRUE, dimnames(dat))))
 dat
     x.var1.t1 x.var2.t1 x.var1.t4 x.var3.t1 x.var3.t7
var1         1         0         1         0         0
var2         0         1         0         0         0
var3         0         0         0         1         1
var4         0         0         0         0         0

This is equivalent to:

  (adist(rownames(dat),colnames(dat),partial=TRUE)==0)+0

The reason I am adding 0 is to change it from logical to numeric. You can use *1. These are just identities. adist with partial=TRUE is equivalent with agrep.

edited Aug 23, 2018 at 3:16

answered Aug 23, 2018 at 3:06

Onyambu

80.3k3 gold badges29 silver badges65 bronze badges

Collectives™ on Stack Overflow

data frame set value based on matching specific row name to column name

2 Answers 2

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related