Reorder data.table with variable columns

Question

I have a data.table that looks like this:

ID1	ID2	ID3	ID4	subtotal	total
001	001	001	001	10	100
001	001	001	002	5	20
001	002	001	001	10	200

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

ID1	ID2	ID3	subtotal	total
001	001	001	15	120
001	002	001	10	200

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:

ID1	ID2	ID3	subtotal	total	percentage
001	001	001	15	120	12.5
001	002	001	10	200	5

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")

Does this answer your question? [Overlap join with start and end positions](https://stackoverflow.com/questions/24480031/overlap-join-with-start-and-end-positions) — jp88, Sep 29 '21 at 15:44

score 1 · Answer 1 · answered Sep 29 '21 at 15:47

1

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

answered Sep 29 '21 at 15:47

cazman

1,452
1
4
11

Thank you for your reply. This would generally be what I am looking for except that I would like to keep it in `data.table` format rather than going back to `data.frame` – koolmees Sep 29 '21 at 15:55
Would it be acceptable to re-convert it after `relocate()`? You could add another pipe and use `data.table()`. – cazman Sep 29 '21 at 15:59
I was thinking about doing so however in the same moment I figured out another way of doing it without `dplyr` so I will stick to that – koolmees Sep 29 '21 at 16:00
Fair enough. No need to make large changes if you don't need to. – cazman Sep 29 '21 at 16:01

score 1 · Answer 2 · answered Sep 29 '21 at 22:09

With development version 1.14.3 of data.table, setcolorder() has gained the new arguments before as well as after to indicate the position to insert a column:

setcolorder(dt, "percentage", before = "subtotal")
dt

   ID1 ID2 ID3 percentage subtotal total
1:   1   1   1       12.5       15   120
2:   1   2   1        5.0       10   200

Data

library(data.table)
dt <- fread("
ID1 ID2 ID3 subtotal    total   percentage
001 001 001 15  120 12.5
001 002 001 10  200 5")

score 0 · Accepted Answer · answered Sep 29 '21 at 15:58

0

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")]))

answered Sep 29 '21 at 15:58

koolmees

2,725
9
23

Reorder data.table with variable columns

3 Answers3

Data