CODEWITHSUNDEEP
rloopsdataframematrixreplace
minoo
minoo

Reputation: 575

replace values of a data frame regarding another matrix values

I have a large matrix that a subset of that would be like below:

mat<-read.table(text = "links   X1  X2  X3  X4
a,b 1.2222  1.5147654   1.5147654   1.5147654
a,c 5.444   1.9763846   1.9763846   1.9763846
c,m 8.99999 1.5515179   -2.3816097  1.5515179
d,e 5.44643 2.2518252   2.2518252   -1.3523473
f,g 7.65656 1.8534944   1.8534944   1.8534944
f,k 2.5645  1.7646614   1.7646614   1.7646614
")

and a data frame as follows:

df<-read.table(text="links  values
a,b 2.716741
                    a,c 0
                    f,g 0
                    f,k 1.4534

                ",header=T,stringsAsFactors = F)

I want to replace the values of my df according to my matrix information such that:

if the values in the first column of my data frame exist in the first column of my matrix, the in the front value of that should be replaced with one of the values in the existed line of the matrix. The replacing value in the matrix can be distinguished with the below code:

x<-floor((df_dist_mat[i,2]-0.75)/0.5)+1

x value refers to X1 or ... or X4 column. The final result should be like this:

links   values
a,b 1.5147654
a,c 0
f,g 0
f,k 1.7646614

I write the below code but I don't know how to get the final result:

  for (i in 1:nrow(df)) {

      if (df[i,2]>0&& df[i,2]<=15) {

        x<-floor((df[i,2]-0.75)/0.5)+1

    }

  }

Any idea?

Upvotes: 0

Views: 159

Answers (1)

Stephan
Stephan

Reputation: 2246

That worked for me:

1) you need to add header = TRUE in your mat - dataframe

2) based on your comments I adjusted the loop:

library(tidyverse) # you need dplyr and tidyr

df <- df %>% # prepare the df to splite the link column
  separate(links, c("links1", "links2"))

for (i in 1:nrow(df)) {
  if (df[i,3]>0 && df[i,3]<=15) {
    var1 <- df[i, 1] # get character of link1
    var2 <- df[i, 2] # get character of link2
    x <- floor(((df[i,3]-0.75)/0.5)+1) # check the value and define the X column

    # subset the mat dataset based on link names
    # example "a,b" == "b,a" OR "a,b" == "a,b" OR "b,a" == "b,a"
    foo <- mat %>%  
             select(links, paste0("X", x)) %>% 
             filter(links == paste0(var1, ",", var2) | links == paste0(var2, ",", var1))

    df[i,3] <- foo[1,2]
  }
}

# get back the old form of your df
df <- df %>% 
  unite(links, links1, links2, sep = ",")

output will be:

  links   values
1   a,b 1.514765
2   a,c 0.000000
3   f,g 0.000000
4   f,k 1.764661

Upvotes: 1

Related Questions