Query.jl - create a new column and use it immediately

user_3474279 0
Viewed 76

I have a DataFrame and I want to compute a bunch of group-level summary statistics. Some of those statistics are derived from other statistics I want to compute first.

df = DataFrame(a=[1,1,2,3], b=[4,5,6,8])

df2 = df |>
    @groupby(_.a) |>
    @map({a = key(_), 
          bm = mean(_.b), 
          cs = sum(_.b), 
          d = _.bm + _.cs}) |>
    DataFrame

ERROR: type NamedTuple has no field bm

The closest I can get is this, which works, but gets very repetitive as the number of initial statistics I want to carry forward into the computation of derived statistics grows:

df2 = df |>
    @groupby(_.a) |>
    @map({a=key(_), bm=mean(_.b), cs=sum(_.b)}) |>
    @map({a=_.a, bm=_.bm, cs=_.cs, d=_.bm + _.cs}) |>
    DataFrame

3×4 DataFrame
│ Row │ a     │ bm      │ cs    │ d       │
│     │ Int64 │ Float64 │ Int64 │ Float64 │
├─────┼───────┼─────────┼───────┼─────────┤
│ 1   │ 1     │ 4.5     │ 9     │ 13.5    │
│ 2   │ 2     │ 6.0     │ 6     │ 12.0    │
│ 3   │ 3     │ 8.0     │ 8     │ 16.0    │

Another option is to create a new DataFrame of first-order results, run a new @map on that to compute the second-order results, and then join the two afterward. Is there any way in Query, DataFramesMeta, or even bare DataFrames to do it in one relatively concise step?

julia dataframe
1 Answers

Just for reference, the "create multiple DataFrames" approach:

df = DataFrame(a=[1,1,2,3], b=[4,5,6,8])

df2 = df |>
    @groupby(_.a) |>
    @map({a=key(_), bm=mean(_.b), cs=sum(_.b)}) |>
    DataFrame

df3 = df2 |>
    @map({a=_.a, d=_.bm + _.cs}) |>
    DataFrame

df4 = innerjoin(df2, df3, on = :a)

3×4 DataFrame
│ Row │ a     │ bm      │ cs    │ d       │
│     │ Int64 │ Float64 │ Int64 │ Float64 │
├─────┼───────┼─────────┼───────┼─────────┤
│ 1   │ 1     │ 4.5     │ 9     │ 13.5    │
│ 2   │ 2     │ 6.0     │ 6     │ 12.0    │
│ 3   │ 3     │ 8.0     │ 8     │ 16.0    │
user_34742790
Related