title: User Defined Function to create and sum a subset in R
body: <p>I need help defining a function that creates a vector in a database where, for each row, the function looks at another column in that database, searches for that value in a designated column of a separate database, creates a subset of that second database consisting of all matching rows, sums a separate column of that new subset, and returns that value to the corresponding row of the new column in the original database.</p>
  
  <p>In other words, I have a data frame that looks something like this:</p>
  
  <pre><code>ID &lt;- c('a', 'b', 'c', 'd', 'e')
  M &lt;- 20:39
  df &lt;- data.frame(cbind(ID, M))
  df$M &lt;- as.numeric(df$M)
  &gt; df
     ID  M
  1   a  1
  2   b  2
  3   c  3
  4   d  4
  5   e  5
  6   a  6
  7   b  7
  8   c  8
  9   d  9
  10  e 10
  11  a 11
  12  b 12
  13  c 13
  14  d 14
  15  e 15
  16  a 16
  17  b 17
  18  c 18
  19  d 19
  20  e 20
  &gt; str(df)
  'data.frame':   20 obs. of  2 variables:
   $ ID: Factor w/ 5 levels "a","b","c","d",..: 1 2 3 4 5 1 2 3 4 5 ...
   $ M : num  1 2 3 4 5 6 7 8 9 10 ...
  </code></pre>
  
  <p>I would like to create a new data frame, <code>Z</code>, such that <code>Z &lt;- data.frame(cbind(X, Y))</code> where:</p>
  
  <pre><code>X &lt;- as.character(unique(df$ID))
  &gt; X
  [1] "a" "b" "c" "d" "e"
  </code></pre>
  
  <p>and <code>Y</code> is a vector of the sum of all a's, sum of all b's, sum of all c's, etc...</p>
  
  <p>So, <code>Y</code> should be equal to <code>c(34, 38, 42, 46, 50)</code> and my final result should be:</p>
  
  <pre><code>&gt; Z
    X  Y
  1 a 34
  2 b 38
  3 c 42
  4 d 46
  5 e 50
  &gt; str(Z)
  'data.frame':   5 obs. of  2 variables:
   $ X: chr  "a" "b" "c" "d" ...
   $ Y: num  34 38 42 46 50
  </code></pre>
  
  <p>To do this, I've tried first turning <code>X</code> into a data frame (is it easier to work with as a data table?):</p>
  
  <pre><code>&gt; Z &lt;- data.frame(X)
  &gt; Z
    X
  1 a
  2 b
  3 c
  4 d
  5 e
  &gt; str(Z)
  'data.frame':   5 obs. of  1 variable:
   $ X: Factor w/ 5 levels "a","b","c","d",..: 1 2 3 4 5
  </code></pre>
  
  <p>and then defining <code>Y</code> as <code>Z$Y &lt;- sum(df[df$ID == Z$X, 2])</code> but I don't get unique values:</p>
  
  <pre><code>&gt; Z
    X   Y
  1 a 210
  2 b 210
  3 c 210
  4 d 210
  5 e 210
  </code></pre>
  
  <p>I've also tried defining the function <code>f1()</code> like so:</p>
  
  <pre><code>f1 &lt;- function(v, w, x, y, z){sum(v[v$w == x$y, z])}
  </code></pre>
  
  <p>but that gets me:</p>
  
  <pre><code>&gt; f1(df, 'ID', Z, 'X', 'M')
  [1] 0
  </code></pre>
  
  <p>I have found a function from another post on this forum that does something similar:</p>
  
  <pre><code>f1 &lt;- function(df, cols, match_with, to_x = 50){
    df[cols] &lt;- lapply(df[cols], function(i) 
      ifelse(grepl(to_x, match_with, fixed = TRUE), 'MID', 
             i))
    return(df)
  }
  </code></pre>
  
  <p>This looks for the value "50" in the <code>match_with</code> column and returns the value "MID" to that row of the column designated by <code>cols</code>, provided both columns in the same designated data base <code>df</code>. So, I would need to replace <code>to_x = 50</code> with something that, instead of looking for the fixed value "50," looks for whatever value is in the column <code>Z$X</code> and, instead of returning the fixed value "MID," returns the sum of the values <code>df[df$ID == Z$X, df$M]</code>. I've attempted these changes myself by writing variations of the following:</p>
  
  <pre><code>f1 &lt;- function(df, cols, match_with, to_x = df[ , 1], x){
    df[cols] &lt;- lapply(df[cols], function(i) 
      ifelse(grepl(to_x, match_with, fixed = TRUE), sum(x), 
             i))
    return(df)
  }
  </code></pre>
  
  <p>but, so far, none of my variations have produced the desired results. This one gave me:</p>
  
  <pre><code>&gt; f1(Z, df, cols = c('Y'), match_with = df$ID, x = df$M)
    X   Y
  1 a 210
  2 b 210
  3 c 210
  4 d 210
  5 e 210
  Warning messages:
  1: In grepl(to_x, match_with, fixed = TRUE) :
    argument 'pattern' has length &gt; 1 and only the first element will be used
  2: In `[&lt;-.data.frame`(`*tmp*`, cols, value = list(Y = c(210, 210,  :
    replacement element 1 has 20 rows to replace 5 rows
  </code></pre>
  
  <p>It seems to be summing the entirety of <code>df$M</code> instead of the subsets where <code>df$ID == Z$X</code>. In other variations it seemed to have problems referencing a column in a second data frame.</p>
  
  <p>I am somewhat new to R and have almost no experience writing user-defined functions (as you probably could tell by this question). Any help would be very much appreciated!</p>