I have a data.table that looks like this:

Using shiny I can then select which ID's I want to group by, for example ID1 till ID3:

As you can see the first row of this table is a sum of the first two rows of the first table.

I then calculate the percentage, the column will automatically be put at the very end:

However, I would like see this column just after the IDs.

I tried to use setcolorder however the columns can vary depending on which IDs are selected. The IDs that are used are stored in a vector which I tried to use like so:

dt[, .(vector, percentage, subtotal, total)]

and:

dt[, c(vector, "percentage", "subtotal", "total")]

but neither option worked

for reference (keep in mind that it should work for any combination of IDs):

dput(vector)
c("ID1", "ID2", "ID3")

CodePudding user response：

Perhaps the following solution using dplyr will work for you. It will put the percent column immediately following all columns matching the "id" pattern. The actual re-ordering of columns happens in the relocate() call.

df %>%
  group_by(id1, id2, id3) %>%
  summarise(subtotal = sum(subtotal),
            total = sum(total),
            percent = subtotal / total * 100) %>%
  relocate(percent, .after = contains("id"))

  id1   id2   id3   percent subtotal total
  <chr> <chr> <chr>   <dbl>    <dbl> <dbl>
1 001   001   001      12.5       15   120
2 001   002   001       5         10   200

CodePudding user response：

Managed to find the solution after toying around more with setcolorder:

setcolorder(dt, c(vector, "percentage", colnames(dt)[!(colnames(dt) %in% vector) & !(colnames(dt) == "percentage")]))

CodePudding user response：

l just need to tell you just go here https://jangorecki.gitlab.io/data.cube/library/data.table/html/setcolorder.html and that will help you l try to find your answer but l cant find it

thx to @jp88 - Volunteer in this form community

CodePudding user response：

197 Use setcolorder():

library(data.table)

x <- data.table(a = 1:3, b = 3:1, c = runif(3))
x
#a b c
#[1,] 1 3 0.2880365
#[2,] 2 2 0.7785115
#[3,] 3 1 0.3297416

setcolorder(x, c("c", "b", "a"))
x
#c b a
#[1,] 0.2880365 3 1
#[2,] 0.7785115 2 2
#[3,] 0.3297416 1 3

From ?setcolorder: In data.table parlance, all set* functions change their input by reference. That is, no copy is made at all, other than temporary working memory, which is as large as one column.so should be pretty efficient. See ?setcolorder for details.