julia dataframe - how to get value in next row by group

user_190791 0
Viewed 158

In Julia DataFrame, how can I do a group by and use the value of the next rows?

For example:

using DataFrames, DataFramesMeta
df = DataFrame(grp=["one", "one", "two", "two", "three"], val=[1, 2, 3, 4, 5])
# Row │ grp     val   
#     │ String  Int64 
#─────┼───────────────
#   1 │ one         1
#   2 │ one         2
#   3 │ two         3
#   4 │ two         4
#   5 │ three       5
   
@combine(groupby(df, :grp),
               count = length(:val),
               first_val = first(:val),
               #next_val = next(:val)
             )
#3×3 DataFrame
# Row │ grp     count  first_val 
#     │ String  Int64  Int64     
#─────┼──────────────────────────
#   1 │ one         2          1
#   2 │ two         2          3
#   3 │ three       1          5

# I would like to obtain:

# Row │ grp     count  first_val next_val
#     │ String  Int64  Int64     
#─────┼──────────────────────────
#   1 │ one         2          1 2
#   2 │ two         2          3 4
#   3 │ three       1          5 NA
julia dataframe
1 Answers

With Julia DataFrames.jl it would be e.g.:

julia> combine(groupby(df, :grp),
               nrow => :count,
               :val => first => :first_val,
               :val => (x -> length(x) > 1 ? x[2] : missing) => :next_val)
3×4 DataFrame
 Row │ grp     count  first_val  next_val
     │ String  Int64  Int64      Int64?
─────┼────────────────────────────────────
   1 │ one         2          1         2
   2 │ two         2          3         4
   3 │ three       1          5   missing

and if you accept additional packages then with ShiftedArrays.jl it would be:

julia> using ShiftedArrays

julia> combine(groupby(df, :grp),
               nrow => :count,
               :val => first => :first_val,
               :val => first∘lead => :next_val)
3×4 DataFrame
 Row │ grp     count  first_val  next_val
     │ String  Int64  Int64      Int64?
─────┼────────────────────────────────────
   1 │ one         2          1         2
   2 │ two         2          3         4
   3 │ three       1          5   missing

And here is the same but with auto-generated column names:

julia> combine(groupby(df, :grp), nrow, :val => first, :val => first∘lead)
3×4 DataFrame
 Row │ grp     nrow   val_first  val_first_lead
     │ String  Int64  Int64      Int64?
─────┼──────────────────────────────────────────
   1 │ one         2          1               2
   2 │ two         2          3               4
   3 │ three       1          5         missing
user_12695670
Related