Selecting values based on matched columns in R

Question

I'm new to R (about 1 week) and have a question that I could not find an answer to. I have a data frame with about 100 columns that take on the following form:

x_1 x_2 x_3...x_50 y_1 y_2 y_3...y_50.

I need to check each x column for a value (say, "01") and if it exists in a row, extract the value from the corresponding y column. I can easily do this is SAS with the following code:

data want;
     set have;

     array x[50] x_1 - x_50;
     array y[50] y_1 - y_50;
     do i = 1 to 50;
         if x[i] = "01" then value = y[i];
         output;
     end;
run;

Any suggestions?

Hello, welcome to SO. Please have a look at how to make a [reproducible example](https://stackoverflow.com/questions/5963269/how-to-make-a-great-r-reproducible-example) — Sotos, May 24 '17 at 13:24

Matt Jewett · Accepted Answer · 2017-05-24T14:27:51.993

Probably not the most efficient method, but assuming you need to process through a loop as with your SAS example, this may yield the result you are looking for.

for(i in 1:length(colnames(df))){
  col <- colnames(df)[i]
  if(startsWith(col,"x")){
    for(r in 1:nrow(df))
    {
      if(df[r,col] == 1)
      {
        ycol <- sub("x","y",col)
        yval <- df[r,ycol]
        print(paste(col,"=",df[r,col],":",ycol,"=",yval))
      }
    }
  }
}

score 0 · Answer 2 · answered May 24 '17 at 13:26

0

not tested

when you have a dataset y and x you can go like this: (they of course need to have the same dimensions)

y[x=="01"]

answered May 24 '17 at 13:26

Andre Elrico

10,956
6
50
69

score 0 · Answer 3 · answered May 24 '17 at 13:50

With data.table, this solution will work:

library(data.table); library(rebus);

#generate your table
data <- data.table(a = 1:50, rowid = rep(1:50, each=50), x =round(runif(500)), y =round(runif(500)))
data
data <- dcast(data, rowid~a, value.var = c("x", "y"))

### solution

# meltdata
meltdata <- melt(data, id.vars="rowid")
# extract column names
meltdata$part <- str_match(meltdata$variable, pattern = capture(ANY_CHAR) %R% "_" %R% capture(one_or_more(DGT)))[, 2]
meltdata$number <- str_match(meltdata$variable, pattern = capture(ANY_CHAR) %R% "_" %R% capture(one_or_more(DGT)))[, 3]
# seperate x and y tables
xvalue <- meltdata[part == "x", .(rowid, number, xvalue = value)]
yvalue <- meltdata[part == "y", .(rowid, number, yvalue = value)]
#merge x and y tables
mergeddata <- merge(xvalue, yvalue, by=c("rowid", "number"))

There are extra work since you didn't share your data, but I think it works well.

Selecting values based on matched columns in R

3 Answers3