Replace all values in column of one dataframe using values in row of another dataframe (matching by row name&column name), replacement is characters

Question

This is very similar to the post "Replace all values in a column of one dataframe using values in a row of another dataframe (matching by row name and column name)"

Except my replacement requirement is the ones ("1") are replaced by the name of the column the one is in.

The example setup

df1<-data.frame(replicate(5,sample(0:1,5,rep=TRUE)))
row.names(df1)<-c("hootsuite","foodtank","FarmsNews","agchat","TysonFoods")
names(df1)<-c("food","agvocate","editor","gmo","ag")

           food agvocate editor gmo ag
hootsuite     1        1      0   0  1
foodtank      1        1      0   0  1
FarmsNews     1        0      1   0  1
agchat        0        0      0   0  1
TysonFoods    1        0      1   1  0

would become

                food    agvocate    editor gmo      ag
hootsuite       food    agvocate    0       0       ag
foodtank        food    agvocate    0       0       ag
FarmsNews       food    0           editor  0       ag
agchat          0       0           0       0       ag
TysonFoods      food    0           editor  gmo     0

The solutions in the similar post

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

(using df2 as defined below)

df2<-c("food","agvocate","editor","gmo","ag")
df2<-as.matrix(df2)
row.names(df2)<-c("food","agvocate","editor","gmo","ag")

give an error "Error in FUN(left, right) : non-numeric argument to binary operator", meaning the matrix multiplication really is not for characters ;)

So the approach I should take is what?

akrun · Accepted Answer · 2017-12-14 02:35:43Z

3

We can use Map to replace the corresponding columns with values that are 1 with the column names

df1[] <- Map(function(x, y) replace(x, x==1, y), df1, names(df1))
df1
#           food agvocate editor gmo ag
#hootsuite  food agvocate      0   0 ag
#foodtank   food agvocate      0   0 ag
#FarmsNews  food        0 editor   0 ag
#agchat        0        0      0   0 ag
#TysonFoods food        0 editor gmo  0

Or use a logical matrix to subset and assign

df1[df1==1] <- names(df1)[col(df1)][df1==1]

answered Dec 14, 2017 at 2:35

akrun

891k38 gold badges590 silver badges700 bronze badges

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

Comments

www · Accepted Answer · 2017-12-14 02:43:27Z

A solution using the purrr package. map2_dfc can loop through two lists or vectors (in this case, df1 and names(df1)) with a functions. It can also bind the output to be a data frame.

set.seed(1)

df1<-data.frame(replicate(5,sample(0:1,5,rep=TRUE)))
row.names(df1)<-c("hootsuite","foodtank","FarmsNews","agchat","TysonFoods")
names(df1)<-c("food","agvocate","editor","gmo","ag")

library(purrr)

df2 <- map2_dfc(df1, names(df1), function(x, y){
  x[x %in% 1] <- y
  return(x)
})
df2
# # A tibble: 5 x 5
#    food agvocate editor   gmo    ag
#   <chr>    <chr>  <chr> <chr> <chr>
# 1     0 agvocate      0     0    ag
# 2     0 agvocate      0   gmo     0
# 3  food agvocate editor   gmo    ag
# 4  food agvocate      0     0     0
# 5     0        0 editor   gmo     0

Collectives™ on Stack Overflow

Replace all values in column of one dataframe using values in row of another dataframe (matching by row name&column name), replacement is characters

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