Assigning every ID with unique value

user_14036964 0
Viewed 112

I need to replace every id with unique values in an ascending order.

Data

df = DataFrame(id=[1,1,1,3,3,3,4,4,4,7,7,7],
               time=[0,1,2,0,1,2,0,1,2,0,1,2],
               conc=[missing,100,70,missing,60,40, missing,90,80, missing,99,70])

Output

df1 = DataFrame(id=[1,1,1,2,2,2,3,3,3,4,4,4],
                time=[0,1,2,0,1,2,0,1,2,0,1,2],
                conc=[missing,100,70,missing,60,40, missing,90,80, missing,99,70])

I tried using this loop but when I try to weave the document this loop fails.

f = 0
df.id = 0
for b = 1:nrow(df)
    if df.time[b] == 0
       f = f + 1
       df.id[b] = f
    else
       df.id[b] = f
    end
end
julia dataframe
2 Answers

Your loop is generally correct, but in Julia when you want to assign a value to a column of a dataframe or matrix you are doing boardcasting, and a boardcasting operation requires an additional dot before the operator or after the function name. So the correct way to assign df.id to 0 is df.id .= 0.

user_91577870

In addition to the above comment by @hhilrun, here are some compact ways to do what you want (I am creating a new data frame in each case not to overwrite the source data frame).

Option 1. Using StatsBase.jl

julia> using StatsBase

julia> transform(df, :id => denserank => :id)
12×3 DataFrame
 Row │ id     time   conc    
     │ Int64  Int64  Int64?  
─────┼───────────────────────
   1 │     1      0  missing 
   2 │     1      1      100
   3 │     1      2       70
   4 │     2      0  missing 
   5 │     2      1       60
   6 │     2      2       40
   7 │     3      0  missing 
   8 │     3      1       90
   9 │     3      2       80
  10 │     4      0  missing 
  11 │     4      1       99
  12 │     4      2       70

Option 2. Using split-apply-combine:

julia> df2 = copy(df);

julia> df2.id = groupindices(groupby(df2, :id));

julia> df2
12×3 DataFrame
 Row │ id      time   conc    
     │ Int64?  Int64  Int64?  
─────┼────────────────────────
   1 │      1      0  missing 
   2 │      1      1      100
   3 │      1      2       70
   4 │      2      0  missing 
   5 │      2      1       60
   6 │      2      2       40
   7 │      3      0  missing 
   8 │      3      1       90
   9 │      3      2       80
  10 │      4      0  missing 
  11 │      4      1       99
  12 │      4      2       70

Option 3. Using cumsum:

julia> df3 = copy(df);

julia> df3.id = cumsum(df3.time .== 0);

julia> df3
12×3 DataFrame
 Row │ id     time   conc    
     │ Int64  Int64  Int64?  
─────┼───────────────────────
   1 │     1      0  missing 
   2 │     1      1      100
   3 │     1      2       70
   4 │     2      0  missing 
   5 │     2      1       60
   6 │     2      2       40
   7 │     3      0  missing 
   8 │     3      1       90
   9 │     3      2       80
  10 │     4      0  missing 
  11 │     4      1       99
  12 │     4      2       70

Option 4. using accumulate:

julia> df4 = copy(df);

julia> df4.id = accumulate((x, y) -> x + (y == 0), df4.time, init=0);

julia> df4
12×3 DataFrame
 Row │ id     time   conc    
     │ Int64  Int64  Int64?  
─────┼───────────────────────
   1 │     1      0  missing 
   2 │     1      1      100
   3 │     1      2       70
   4 │     2      0  missing 
   5 │     2      1       60
   6 │     2      2       40
   7 │     3      0  missing 
   8 │     3      1       90
   9 │     3      2       80
  10 │     4      0  missing 
  11 │     4      1       99
  12 │     4      2       70

Note that option 3 and option 4 rely on the special structure of :time column only while, option 1 and option 2 rely on the value stored in :id column only. Also note that in option 3 and option 4 you could have used cumsum! and accumumulate! to do an in place assignment into column :id.

user_12695670
Related