Reading data from excel in R using variable identifiers

Question

I have a big excel file with 6 sheets in it. Each sheet has 3 identifying variables (and many other ones)that each subject has a value for. Is there a way for R (using Rstudio) to spit out the remaining variables by identifying the subject with these 3 variables. Essentially, I want to be able to specify which sheet, and values for each identifier into a code that will spit out the remaining data for that subject. Let me know if an example would be helpful.

My data is formatted like this example, but there are 5 sheets just like this in one excel file. I want to generate a code (after mapping the file) where I can input my identifiers (by I.D and timepoint e.g (1234, Timepoint1)) within a specified sheet of the file and then have an output of the values of the other variables for that subject e.g. SR score. I understand this just may not be possible in R, hence my question. example data

user_8057777 · Answer

I'm assuming you have figured out how to load Excel sheets in R. If not, let me know. Then, the question is how to find which elements match the first three variables. If we are able to identify them, finding the rest of the variables is trivial.

One approach is to write a function that does that for you. It matches the first three variables with what you input and returns the index(or indexes) of elements that match.

which() returns the index of items that satisfy the condition. When I say which(df[,1] == a), it will return me the index of observations in df where the first column matches a. And so on. Then, you can use intersect() to find the common indexes in x1, x2 and x3. I'm using magrittr pipes %>% to simplify the coding.

check_this = function(df, a, b, c) 
{
  x1 = which(df[,1] == a)
  x2 = which(df[,2] == b)
  x3 = which(df[,3] == c)
  
  v = intersect(x1, x2) %>% 
    intersect(x3)
  return(v)
}

Minimum Working Example First, I'll create a dummy data frame. Then, I'll find the index using the function I just created.

df = tibble(var1 = 1:10, 
            var2 = 11:20, 
            var3 = letters[1:10],
            var4 = LETTERS[1:10])

> df
# A tibble: 10 × 4
    var1  var2 var3  var4 
      
 1     1    11 a     A    
 2     2    12 b     B    
 3     3    13 c     C    
 4     4    14 d     D    
 5     5    15 e     E    
 6     6    16 f     F    
 7     7    17 g     G    
 8     8    18 h     H    
 9     9    19 i     I    
10    10    20 j     J

# the function
check_this = function(df, a, b, c) 
{
  x1 = which(df[,1] == a)
  x2 = which(df[,2] == b)
  x3 = which(df[,3] == c)
  
  v = intersect(x1, x2) %>% 
    intersect(x3)
  return(v)
}

Now, let us see it in action. First, I'll pass the data frame and variables I want to match as arguments. The function will return the indices which I'll store in l. Then, I'll ask R to show me the rows which have indices numbers in l.

# checking and storing the index of matched
l = check_this(df, 2, 12, "b")

> df[l,]
# A tibble: 1 × 4
   var1  var2 var3  var4 
     
1     2    12 b     B

Note: You could have skipped the step of storing indices in l by returning the selected rows of the data frame itself. The function would change to the following.

# the function
check_this = function(df, a, b, c) 
{
  x1 = which(df[,1] == a)
  x2 = which(df[,2] == b)
  x3 = which(df[,3] == c)
  
  v = intersect(x1, x2) %>% 
    intersect(x3)
  return(df[v,])
}

Reading data from excel in R using variable identifiers

1 Answers