Combine(groupby())

New to Julia
1

Is there a way to do this:

c = combine(groupby(df, :A),:B => mean => :B_mean)

but in such a way that the output does not only show :A and :B_mean but also the other variables that df had (well, at least the variables that can also be combined due to repetition)?

2

DataFrames can’t know your intent beyond what you program. It can’t intuit which variables are invariant withing an “A” grouping unless you program that.

You could write:

vars_sub = Symbol.(setdiff(names(df), ["B", "A"]) )
c = combine(groupby(df, :A), :B => mean => :B_mean, (vars_sub .=> first )...)
3

You can just add all the variables that are constant within groups as extra grouping variables.

4 
julia> using Statistics, StatsBase

julia> df = DataFrame(A = [1, 1, 2, 2], B = [1, 2, 300, 400], C = [5, 5, 6, 6], D = [10, 30, 60, 80])
4×4 DataFrame
 Row │ A      B      C      D     
     │ Int64  Int64  Int64  Int64 
─────┼────────────────────────────
   1 │     1      1      5     10
   2 │     1      2      5     30
   3 │     2    300      6     60
   4 │     2    400      6     80

julia> function only_constant(x)
           all(==(first(x)), x) || throw(ArgumentError("All elements of the vector must be equal"))
           return first(x)
       end;

julia> @chain df begin
           @by :A begin
               :B = mean(:B)
               $([:C] .=> only_constant)
           end
       end
2×3 DataFrame
 Row │ A      B        C_only_constant 
     │ Int64  Float64  Int64           
─────┼─────────────────────────────────
   1 │     1      1.5                5
   2 │     2    350.0                6
5

Thanks for the great answers. I wonder whether there could be a shortcut for this. It’s probably not so rare that you have not only multiple factors and levels, but also multiple observations per subject and you want to group by ID and e.g. average some result.

6

A variant of @croberts’ code:

gdf = groupby(df, :A)
combine(gdf, :B => mean => :B_mean, Not([:A, :B]) .=> first)
7

@rafael.guerra ’s solution is the easiest to parse for newbies, but still bit hard to remember.

8

In plain English, it reads: For each column in the grouped dataframe (gdf), excluding the grouping variable (:A) and the aggregated column (:B), take the first value within each group.

9

It could help to avoid redundancy in tabular data. In this case, you would have two datasets: one for person-level observations, and one for person-time -level observations. If you want to bring along more variables from the person-level table to the summary table, you join by ID. Example:

julia> using DataFrames, Chain, Statistics

julia> person_df = DataFrame(id = [1,2,3], A = [1,0,0])
3×2 DataFrame
 Row │ id     A     
     │ Int64  Int64 
─────┼──────────────
   1 │     1      1
   2 │     2      0
   3 │     3      0

julia> person_time_df = DataFrame(id = [1,1,2,2,3,3], time = [1,2,1,2,1,2], B = [0,3,0,2,1,4])
6×3 DataFrame
 Row │ id     time   B     
     │ Int64  Int64  Int64 
─────┼─────────────────────
   1 │     1      1      0
   2 │     1      2      3
   3 │     2      1      0
   4 │     2      2      2
   5 │     3      1      1
   6 │     3      2      4

julia> mean_B_by_id = @chain person_time_df begin
           groupby(:id)
           combine(:B => mean => :mean_B)
       end
3×2 DataFrame
 Row │ id     mean_B  
     │ Int64  Float64 
─────┼────────────────
   1 │     1      1.5
   2 │     2      1.0
   3 │     3      2.5

julia> result = leftjoin(mean_B_by_id, person_df, on = :id)
3×3 DataFrame
 Row │ id     mean_B   A      
     │ Int64  Float64  Int64? 
─────┼────────────────────────
   1 │     1      1.5       1
   2 │     2      1.0       0
   3 │     3      2.5       0