Data manipulation with data.table

data.table paradigm

data.table manipulation

# make some fake data
id <- c(
  rep(1, 3), 
  rep(2, 2), 
  rep(3, 6), 
  rep(4, 1)
)

ab_types <- c(
  'ampicillin', 'gentamicin', 'cefalotin', 'metronidazol'
)
ab <- sample(ab_types, size = length(id), replace = T)

dt <- data.table::data.table(
  id = id, ab = ab
)
dt

       id           ab
    <num>       <char>
 1:     1 metronidazol
 2:     1   gentamicin
 3:     1   gentamicin
 4:     2 metronidazol
 5:     2 metronidazol
 6:     3    cefalotin
 7:     3   gentamicin
 8:     3 metronidazol
 9:     3   gentamicin
10:     3   ampicillin
11:     3    cefalotin
12:     4 metronidazol

Subsetting

Remove a column temporarily

dt[, !c('id')]

              ab
          <char>
 1: metronidazol
 2:   gentamicin
 3:   gentamicin
 4: metronidazol
 5: metronidazol
 6:    cefalotin
 7:   gentamicin
 8: metronidazol
 9:   gentamicin
10:   ampicillin
11:    cefalotin
12: metronidazol

By, .SD

Get number of records per person

dt[, .N, by = id]

      id     N
   <num> <int>
1:     1     3
2:     2     2
3:     3     6
4:     4     1

Number of unique records

# if use lapply, the results will be a list in the column
dt[, .(n_uab = sapply(.SD, function(x){length(unique(x))})), 
   .SDcols = 'ab', 
   by = id]

      id n_uab
   <num> <int>
1:     1     2
2:     2     1
3:     3     4
4:     4     1