CODEWITHSUNDEEP
rdataframedplyr
Justin Andrew
Justin Andrew

Reputation: 97

How to calculate ntiles for many columns in R?

I have a dataframe that looks like this:

ID age sex chem1 chem2 chem3 ... chem524
01 64  m   .06   6.8    .3        .2
02 57  f   .7    24.3    NA       .7
03 53  f   .4    2.9    .03       1.6
04 68  m   .7    37.8   .01       .01
05 73  m   1.2   1.4    2.8       3.6
06 49  f   .3    7.6    .3        2.9

I need to make 3 ntiles for each chemical. I know how to do this for one chemical at a time, but I do not want to write it out 524 times.

I would like the ntiles for each chemical to be stored together in a new dataframe so that I can do further analysis on them later.

Here is how I would like the output dataframe to look like: 

ID age sex chem1 chem2 chem3 ... chem524
01 64  m    1     2     2         1
02 57  f    2     3     NA        2
03 53  f    2     1     1         3
04 68  m    2     3     1         1
05 73  m    3     1     3         3
06 49  f    1     2     2         3

Here is what I have tried:

df2 <- mutate_at(df, vars(chem1:chem524), ntile(top_air[4:528], 3))

This didn't work because it appears ntile can only act on one column. I also tried using apply but I couldn't get that to work either.

Thanks for the help!

Upvotes: 0

Views: 934

Answers (2)

user12728748
user12728748

Reputation: 8516

Here is a data.table solution:

library(data.table)
df <- structure(list(ID = c(64L, 57L, 53L, 68L, 73L, 49L), 
                     age = c("m", "f", "f", "m", "m", "f"), 
                     sex = c(0.06, 0.7, 0.4, 0.7, 1.2, 0.3), 
                     chem1 = c(6.8, 24.3, 2.9, 37.8, 1.4, 7.6), 
                     chem2 = c(0.3, NA, 0.03, 0.01, 2.8, 0.3), 
                     chem3 = c(0.2, 0.7, 1.6, 0.01, 3.6, 2.9)), 
                class = "data.frame", 
                row.names = c("01", "02", "03", "04", "05", "06"))
dt <- data.table(df)
cols <- grep("^chem", colnames(dt), value = TRUE)
dt[, (cols) := lapply(.SD, dplyr::ntile, 3), .SDcols=cols][]
#>    ID age  sex chem1 chem2 chem3
#> 1: 64   m 0.06     2     2     1
#> 2: 57   f 0.70     3    NA     2
#> 3: 53   f 0.40     1     1     2
#> 4: 68   m 0.70     3     1     1
#> 5: 73   m 1.20     1     3     3
#> 6: 49   f 0.30     2     2     3

Created on 2020-05-23 by the reprex package (v0.3.0)

Upvotes: 1

Ian Campbell
Ian Campbell

Reputation: 24888

Here's one approach with mutate_at:

library(dplyr)
df %>%
  mutate_at(vars(starts_with("chem")), .funs = list(~ntile(.,3)))
  ID age sex chem1 chem2 chem3 chem524
1  1  64   m     1     2     2       1
2  2  57   f     2     3    NA       2
3  3  53   f     2     1     1       2
4  4  68   m     3     3     1       1
5  5  73   m     3     1     3       3
6  6  49   f     1     2     2       3

The proper parameterization of the .funs = argument is a bit specialized, but for a single function just supply a one element list starting with ~. ~ is shorthand for an anonymous function with one argument which we can access with ..

You can also name the list to get new columns:

df %>%
  mutate_at(vars(starts_with("chem")), list(ntile = ~ntile(.,3)))

Another way is to skip the anonymous function all together and pass the additional arguments to ntile with the ... part of mutate_at:

df %>%
  mutate_at(vars(starts_with("chem")), ntile, 3)
  ID age sex chem1 chem2 chem3 chem524
1  1  64   m     1     2     2       1
2  2  57   f     2     3    NA       2
3  3  53   f     2     1     1       2
4  4  68   m     3     3     1       1
5  5  73   m     3     1     3       3
6  6  49   f     1     2     2       3

Upvotes: 1

Related Questions