title: Row and columns-wise calculation in long format in R
body: <p>I am struggling with cellwise-calculations in a complex data-set (see below for dput() example).</p>
  
  <p>I need to apply the formula for standardized mean difference (M1-M2/sqrt(s1^2+s2^2) to multiple rows and columns (studies and tests). The M1 &amp; M2 (means) values are in the  <code>pr_cognm_</code> columns and s1 and s2 (standard deviations) in the <code>pr_cognsd_</code> columns, and they should be calculated dependent on the factors <code>id</code> &amp; <code>tx...3</code> (treatments). </p>
  
  <p>So e.g. for the <code>pr_cognm_VV2_CRT_error</code> column the <code>id</code> 336 has two rows and, in this case!, the values in the <code>treat1</code> row need to be subtracted from <code>VGT</code>. But sometimes it is the other way around, e.g. within <code>id</code> 162 <code>dGT</code> needs to be subtracted from <code>treat1</code> (luckily, this logic will be the same for each specific comparison though). Then, the same thing needs to happen with the standard deviations (i.e. potentiate and addition). Lastly, M and S need to be divided. There are many tests (columns) to run the formula on (e.g.<code>pr_cognm_BVMT_perc_retention</code>, <code>pr_cognm_VV2_CRT_error</code>, etc.) and often NA, since the specific <code>id</code> did not have this test.  The data is in the long format and, to make it more complicated, some <code>id</code> have three instead of two rows (where two rows need to be subtracted from one other in a specific direction e.g. <code>task1</code>). </p>
  
  <p>My best idea was to </p>
  
  <pre><code>#make a dataset
  a &lt;- readxl::read_excel("C:/.../reprod.xlsx")
  b&lt;- a[!grepl("com", a$id),] #already omitted in example dataset
  pr_cognm &lt;- dplyr::select(b,contains("pr_cognm"))
  pr_cognsd &lt;- dplyr::select(b,contains("pr_cognsd"))
  
  c &lt;- cbind(b$tx...3, b$id ,pr_cognm, pr_cognsd)
  c$`b$id` &lt;- as.factor(c$`b$id`)
  
  #turn var's into numerics and factors
  #potentiate all standard deviations (s1^2 and s2^2)
  c[,3:ncol(c)] &lt;- sapply(c[,3:ncol(c)], as.numeric)
  c[,grepl("pr_cognsd", colnames(c))] &lt;- c[,grepl("pr_cognsd", colnames(c))]^2
  
  #then reshape
  require(reshape2)
  c %&gt;% 
    dcast(b$id ~ b$tx...3, value.var = c("pr_cognm_VV2_CRT_error"), fill = 0)
  
     b$id BF BL BT dGT H-TT  HFL LM-TT treat1  VGT
  1    55  0  0  0   0    0 0.00  0.00   0.00 0.00
  2   162  0  0  0   0    0 0.00  0.00   0.00 0.00
  3   236  0  0  0   0    0 0.00  0.00   0.00 0.00
  4   336  0  0  0   0    0 0.00  0.00   8.75 7.58
  5   377  0  0  0   0    0 0.00  0.00   0.00 0.00
  6   521  0  0  0   0    0 0.00  0.00   0.00 0.00
  7   525  0  0  0   0    0 0.00  0.00   0.00 0.00
  8   527  0  0  0   0    0 0.00  0.00   0.00 0.00
  9   528  0  0  0   0    0 0.00  0.00   0.00 0.00
  10  535  0  0  0   0    0 5.65  6.54   0.00 0.00
  11  548  0  0  0   0    0 0.00  0.00   0.00 0.00
  12  553  0  0  0   0    0 0.00  0.00   0.00 0.00
  </code></pre>
  
  <p>Now I could define the rules which variables should be added like <code>c$sub &lt;- c$treat1-c$VGT</code> and <code>c$sub &lt;- c$HFL-c$LM-TT</code>, do the addition with the SD's in a similar fashion and finally divide the two variables to find the SMD. BUT, this only works for one test. In this case  <code>value.var = c("pr_cognm_VV2_CRT_error")</code>. I would like to get this matrix for every test I have in the dataset via e.g. loop since more than one <code>value.var</code> dont work:</p>
  
  <pre><code>require(reshape2)
  c %&gt;% 
     dcast(b$id ~ b$tx...3, value.var = c("pr_cognm_VV2_CRT_error", "pr_cognm_BNT_perc_retention"), fill = 0)
  
  Error in .subset2(x, i, exact = exact) : subscript out of bounds
  In addition: Warning message:
  In if (!(value.var %in% names(data))) { :
    the condition has length &gt; 1 and only the first element will be used
  </code></pre>
  
  <p>If there were a way to loop via </p>
  
  <pre><code>c %&gt;% 
  +   dcast(b$id ~ b$tx...3, value.var = c(names(c[,3:ncol(c)]), fill = 0)
  </code></pre>
  
  <p>then I could maybe <code>rbind</code> them and do the subtractions into a new variable as described above and after doing the same for the SD's I would finally be able to do the division to get the SMD.</p>
  
  <p>I could not get any solutions to work. </p>
  
  <p>Reprod example (truncated):</p>
  
  <pre><code>a &lt;- structure(list(checked = c("Y", "Y", NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA), id = c("55", "55", "162", "162", "236", "236", "336", "336", 
  "377", "377", "521", "521", "525", "525", "527", "527", "528", 
  "528", "535", "535", "548", "548", "548", "553", "553"), tx...3 = c("task1", 
  "VGT", "dGT", "task1", "BT", "H-TT", "task1", "VGT", "BT", "H-TT", 
  "task1", "VGT", "HFL", "H-TT", "BF", "BT", "HFL", "task1", "HFL", 
  "LM-TT", "HFL", "BL", "task1", "HFL", "task1"), nta = c(2, 2, 
  2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 2, 3, 3, 3, 
  2, 2), id2 = c(1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 1, 2, 
  1, 2, 1, 2, 1, 2, 3, 1, 2), cross = c("N", "N", "N", "N", "N", 
  "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", 
  "N", "N", "N", "N", "N", "N", "N"), pre_post = c("N", "N", "N", 
  "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", 
  "N", "N", "N", "N", "N", "N", "N", "N", "N"), case_control = c("N", 
  "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", 
  "N", "N", "N", "N", "N", "N", "N", "N", "N", "N", "N"), expsy = c("Y", 
  "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", 
  "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y", "Y"), hosp = c("Out", 
  "Out", "Out", "Out", "In", "In", "NA", "NA", "In", "In", "NR", 
  "NR", "NR", "NR", "Mx", "Mx", "NR", "NR", "Mx", "Mx", "Out", 
  "Out", "Out", "Out", "Out"), tx...11 = c("task1", "VGT", "dGT", 
  "task1", "BT", "H-TT", "task1", "VGT", "BT", "H-TT", "task1", 
  "VGT", "HFL", "H-TT", "BF", "BT", "HFL", "task1", "HFL", "LM-TT", 
  "HFL", "BL", "task1", "HFL", "task1"), vt_p = c("17", "17", "24", 
  "24", "21", "21", "NR", "NR", "NR", "NR", "NA", "NA", "17", "17", 
  "24", "24", "17", "17", "17", "17", "17", "17", "17", "17", "17"
  ), n_se = c("12", "12", "20", "20", "6", "6", "10", "10", "NR", 
  "NR", "20", "20", "10", "6", "8", "8", "15", "15", "10", "6", 
  "15", "15", "15", "10", "10"), cogn_name_AMI_K = c(NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA), cogn_cite_AMI_K = c(NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA), cogn_last_stim_AMI_K = c(NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA), n_bcogn_AMI_K = c(NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA), n_pcogn_AMI_K = c(NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA), pr_cognm_AMI_K = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
  NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), 
      pr_cognsd_AMI_K = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
      ), po_cognm_n_AMI_K = c(NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA), po_cognsd_n_AMI_K = c(NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA), cogn_name_BVMT_perc_retention = c(NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, "brief_visual_memory_test", "brief_visual_memory_test", 
      "brief_visual_memory_test", NA, NA), cogn_cite_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "benedict_1997", "benedict_1997", "benedict_1997", 
      NA, NA), cogn_last_stim_BVMT_perc_retention = c(NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, "NR", "NR", "NR", NA, NA), n_bcogn_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, 19, 24, 18, NA, NA), n_pcogn_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, 19, 24, 18, NA, NA), pr_cognm_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "91.6", "86.6", "90", NA, NA), pr_cognsd_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "17", "36", "13.3", NA, NA), po_cognm_n_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "86.7", "82.1", "71.7", NA, NA), po_cognsd_n_BVMT_perc_retention = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "20", "39.3", "24.2", NA, NA), cogn_name_BNT_naming = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, "boston_naming_task_naming", 
      "boston_naming_task_naming", NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA), cogn_cite_BNT_naming = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, "kaplan_1983", "kaplan_1983", 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA
      ), cogn_last_stim_BNT_naming = c(NA, NA, NA, NA, NA, NA, 
      NA, NA, 30, 30, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA), n_bcogn_BNT_naming = c(NA, NA, NA, NA, NA, 
      NA, NA, NA, 14, 14, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA), n_pcogn_BNT_naming = c(NA, NA, NA, NA, 
      NA, NA, NA, NA, 14, 14, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA), pr_cognm_BNT_naming = c(NA, NA, 
      NA, NA, NA, NA, NA, NA, "19.64", "18.14", NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), pr_cognsd_BNT_naming = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, "9.15", "5.3", NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), po_cognm_n_BNT_naming = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, "20.21", "20.71", NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), po_cognsd_n_BNT_naming = c(NA, 
      NA, NA, NA, NA, NA, NA, NA, "9.38", "6.34", NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), cogn_name_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "VV2_crt_error", "VV2_crt_error", NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, "VV2_crt_error", "VV2_crt_error", 
      NA, NA, NA, NA, NA), cogn_cite_VV2_CRT_error = c(NA, NA, 
      NA, NA, NA, NA, "robbins_1994", "robbins_1994", NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, "robbins_1994", "robbins_1994", 
      NA, NA, NA, NA, NA), cogn_last_stim_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "NR", "NR", NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, "NR", "NR", NA, NA, NA, NA, NA), n_bcogn_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, 12, 12, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, 12, 12, NA, NA, NA, NA, NA), n_pcogn_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, 12, 12, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, 12, 12, NA, NA, NA, NA, NA), pr_cognm_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "8.75", "7.58", NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "5.65", "6.54", NA, NA, NA, NA, NA), pr_cognsd_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "1.13", "2.84", NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "1.10", "1.89", NA, NA, NA, NA, NA), po_cognm_n_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "7.50", "5.33", NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "7.50", "2.34", NA, NA, NA, NA, NA), po_cognsd_n_VV2_CRT_error = c(NA, 
      NA, NA, NA, NA, NA, "2.06", "2.42", NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, "2.06", "2", NA, NA, NA, NA, NA), cogn_name_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "VV2_crt_latency", "VV2_crt_latency", 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA), cogn_cite_VV2_CRT_latency = c(NA, NA, NA, NA, NA, 
      NA, "robbins_1994", "robbins_1994", NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), cogn_last_stim_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "NR", "NR", NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), n_bcogn_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, 12, 12, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA), n_pcogn_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, 12, 12, NA, NA, NA, NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA), pr_cognm_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "476.05", "465.65", NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), pr_cognsd_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "35.86", "37.54", NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), po_cognm_n_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "460.66", "433.13", NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA), po_cognsd_n_VV2_CRT_latency = c(NA, 
      NA, NA, NA, NA, NA, "34.75", "46.70", NA, NA, NA, NA, NA, 
      NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA, NA)), row.names = c(NA, 
  -25L), class = c("tbl_df", "tbl", "data.frame"))
  </code></pre>