In R, how to order a column by specific value in data.table?

Question

Say I have columns in data.table like this:

Sorry that I didn't specific in the first time. Each value belongs to an item code. The code will repeat several time because the items belong to a family. Some items have value a,b,c, some have a,b,c,d, some have a,b, some only have b.

 family item value
1  001    01    a
2  001    01    b
3  001    01    c
4  001    02    b
5  001    05    b
6  001    32    a
7  001    32    b
8  001    32    c
9  001    32    d
10 002    01    a
11 002    01    b
12 002    01    c
13 002    32    b
14 002    55    b

How to order the column by putting b at the front of each item? like b,a,c, b,a,c,d, b,a, b. The ordered column should be like this：

family item value id
1  001    01    b   1
2  001    01    a   1
3  001    01    c   1
4  001    02    b   2
5  001    05    b   3
6  001    32    b   4
7  001    32    a   4
8  001    32    c   4
9  001    32    d   4
10 002    01    b   5
11 002    01    a   5
12 002    01    c   5
13 002    32    b   6
14 002    55    b   7

data.table package has setorder() and setorderv(), but both can only be ordered by column. How about a specific value?

Answer 1

Assuming your data.table is called "DT" and the column names are "V1" and "V2", try the following:

DT[, V2 := factor(V2, c("b", setdiff(unique(V2), "b")))][order(V1, V2)]
#    V1 V2
# 1:  1  b
# 2:  1  a
# 3:  1  c
# 4:  2  b
# 5:  3  b
# 6:  4  b
# 7:  4  a
# 8:  4  c
# 9:  4  d

---

Update

Using your new sample data, try the following:

DT[, id := .GRP, by = list(family, item)][, value := factor(
  value, c("b", setdiff(unique(value), "b")))][order(id, value)]
#     family item value id
#  1:      1    1     b  1
#  2:      1    1     a  1
#  3:      1    1     c  1
#  4:      1    2     b  2
#  5:      1    5     b  3
#  6:      1   32     b  4
#  7:      1   32     a  4
#  8:      1   32     c  4
#  9:      1   32     d  4
# 10:      2    1     b  5
# 11:      2    1     a  5
# 12:      2    1     c  5
# 13:      2   32     b  6
# 14:      2   55     b  7

---

The above answer is ver nice (and more general). In this particular case, we can also do it this way. We simply create an integer column which has a smaller value for b and then use setorder to reorder the rows by reference as follows:

DT[, id := 1L][value == "b", id := 0L] 
setorder(DT, family, item, id)[, id := NULL] # don't need 'id' after ordering
#     family item value
#  1:      1    1     b
#  2:      1    1     a
#  3:      1    1     c
#  4:      1    2     b
#  5:      1    5     b
#  6:      1   32     b
#  7:      1   32     a
#  8:      1   32     c
#  9:      1   32     d
# 10:      2    1     b
# 11:      2    1     a
# 12:      2    1     c
# 13:      2   32     b
# 14:      2   55     b