Need to subract from Second Row as baseline value for each ID

user_14036964 0
Viewed 27

I need to subtract each conc from the baseline value of each ID. I can’t subtract from the first row of each ID since it has a dosing information. I need the value as in the req_conc column. The baseline value is found in row[2] of each ID.

DataFrame

│ Row │ id    │ time  │ conc    │ req_conc │
│     │ Int64 │ Int64 │ Int64?  │ Int64?   │
├─────┼───────┼───────┼─────────┼──────────┤
│ 1   │ 1     │ 0     │ missing │ missing  │
│ 2   │ 1     │ 0     │ 32      │ 0        │
│ 3   │ 1     │ 1     │ 45      │ 13       │
│ 4   │ 1     │ 2     │ 36      │ 4        │
│ 5   │ 1     │ 3     │ 32      │ 0        │
│ 6   │ 2     │ 0     │ missing │ missing  │
│ 7   │ 2     │ 0     │ 40      │ 0        │
│ 8   │ 2     │ 1     │ 62      │ 22       │
│ 9   │ 2     │ 2     │ 53      │ 13       │
│ 10  │ 2     │ 3     │ 48      │ 8        │

Data

id = [1,1,1,1,1,2,2,2,2,2]
time = [0,0,1,2,3,0,0,1,2,3]
conc = [missing, 32, 45, 36, 32, missing, 40, 62, 53, 48]
req_conc = [missing, 0, 13, 4, 0, missing, 0, 22, 13, 8]

df = DataFrame(id=id, time=time, conc=conc, req_conc=req_conc)
julia dataframe
1 Answers

The simplest is to do the following:

julia> transform(groupby(df, :id), :conc => (x -> x .- x[2]) => :req_conc2)
10×5 DataFrame
 Row │ id     time   conc     req_conc  req_conc2
     │ Int64  Int64  Int64?   Int64?    Int64?
─────┼────────────────────────────────────────────
   1 │     1      0  missing   missing    missing
   2 │     1      0       32         0          0
   3 │     1      1       45        13         13
   4 │     1      2       36         4          4
   5 │     1      3       32         0          0
   6 │     2      0  missing   missing    missing
   7 │     2      0       40         0          0
   8 │     2      1       62        22         22
   9 │     2      2       53        13         13
  10 │     2      3       48         8          8

where I take advantage of the fact that in the first row we have missing and subtracting anything from missing produces a missing value.

This assumes that your data frame is sorted by :id and by :time, but I understand this is guaranteed - right?

user_12695670
Related