3

I am using the following code, which calculates z scores per subgroup. I would like to add one more column at the end, which sums the z scores, so row-wise sums of all the values in all the columns that end with "_zscore". How could I specifically select those columns?

(Note that my real data have many more columns, so I am looking to specifically select "_zscore" in column names.)

 library(dplyr)
 set.seed(12345)
 df1 = data.frame(a=c(rep("a",8), rep("b",5), rep("c",7), rep("d",10)), 
      b=rnorm(30, 6, 2), 
      c=rnorm(30, 12, 3.5), 
      d=rnorm(30, 8, 3)
      )
 df1_z <- df1 %>%
   group_by(a) %>%
   mutate(across(b:d, list(zscore = ~as.numeric(scale(.)))))
Sylvia Rodriguez
  • 1,203
  • 2
  • 11
  • 30

2 Answers2

10

You can use use select to select columns that ends with "zscore" and use rowSums :

library(dplyr)
df1 %>%
  group_by(a) %>%
  mutate(across(b:d, list(zscore = ~as.numeric(scale(.))))) %>%
  ungroup %>%
  mutate(total = rowSums(select(., ends_with('zscore'))))

# A tibble: 30 x 8
#   a         b     c     d b_zscore c_zscore d_zscore  total
#   <chr> <dbl> <dbl> <dbl>    <dbl>    <dbl>    <dbl>  <dbl>
# 1 a      7.17 14.8   8.45    0.697   0.101    0.0179  0.816
# 2 a      7.42 19.7   3.97    0.841   1.17    -1.14    0.865
# 3 a      5.78 19.2   9.66   -0.108   1.05     0.332   1.28 
# 4 a      5.09 17.7  12.8    -0.508   0.732    1.14    1.36 
# 5 a      7.21 12.9   6.24    0.721  -0.329   -0.555  -0.163
# 6 a      2.36 13.7   2.50   -2.09   -0.146   -1.52   -3.76 
# 7 a      7.26 10.9  10.7     0.749  -0.774    0.593   0.567
# 8 a      5.45  6.18 12.8    -0.302  -1.80     1.14   -0.965
# 9 b      5.43 18.2   9.55   -0.445   1.12     1.34    2.02 
#10 b      4.16 12.1   4.11   -1.06    0.0776  -1.02   -2.01 
# … with 20 more rows
Ronak Shah
  • 377,200
  • 20
  • 156
  • 213
  • That's terrific! Thanks so much for your quick reply, Ronak! :) – Sylvia Rodriguez Aug 18 '20 at 05:40
  • Really fast reply :) Ronak, could you, please, explain what is this line about ```mutate(across(b:d, list(zscore = ~as.numeric(scale(.)))))```? Also, I am not a newbie in R, but always wonder what does the dot sign in functions mean? Never met this in practice. – rg4s Aug 18 '20 at 06:20
  • `across` is used to apply same function to multiple columns (here `b` to `d`). Here we are scaling the values column-wise. `~` and `.` is a formula styled syntax used in `tidyverse` which is an alternative to anonymous function. `.` represents the values of the column. – Ronak Shah Aug 18 '20 at 06:29
4

here is a data.table solution

It basically does the same as the code fom Ronak's answer, but then in the data.table syntax.

explanation
setDT(df1_z) is used to set df1_z to a data.table format
total := rowSums(.SD) creates a new column total, which had the value of rowSums of the .SD (a set of selected columns)
.SDcols = patterns("_zscore$") defines the selected columns for .SD. Here, it are the columns who's name match the regex pattern _zscore$ (which means: ending with _zscore)

library( data.table )
setDT(df1_z)[, total := rowSums(.SD), .SDcols = patterns("_zscore$")]
Wimpel
  • 26,031
  • 1
  • 20
  • 37