# DataFrames: Most efficient way to compute statistics on multiple/nested subgroups

**URL:** <https://discourse.julialang.org/t/dataframes-most-efficient-way-to-compute-statistics-on-multiple-nested-subgroups/66714>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [August 20, 2021, 8:00am UTC](https://discourse.julialang.org/t/dataframes-most-efficient-way-to-compute-statistics-on-multiple-nested-subgroups/66714 "2021-08-20T08:00:10Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![told](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/told/32/17083_2.png) [@told](https://discourse.julialang.org/u/told)\
**Post date:** [August 20, 2021, 8:00am UTC](https://discourse.julialang.org/t/dataframes-most-efficient-way-to-compute-statistics-on-multiple-nested-subgroups/66714/1 "2021-08-20T08:00:10Z")

</div>

I have a dataset with population statistics similar to this:

```julia
using DataFrames
#make a dummy data set
df = DataFrame(date = [1, 1, 1, 1, 1, 1, 1, 2, 2, 2, 2, 2, 2, 2, 2], 
               age_group = [1, 2, 2, 3, 4, 4, 5, 1, 2, 3, 4, 5, 5, 5, 5], 
               count = [10, 10, 20, 20, 10, 10, 30, 10, 20, 30, 10, 20, 30, 40, 50])

```

```julia
15×3 DataFrame
 Row │ date age_group count 
     │ Int64 Int64 Int64 
─────┼─────────────────────────
   1 │ 1 1 10
   2 │ 1 2 10
   3 │ 1 2 20
   4 │ 1 3 20
  ⋮ │ ⋮ ⋮ ⋮
  13 │ 2 5 30
  14 │ 2 5 40

```

So multiple count entries per date and age group.  
My goal is to calculate the total percentage of each age group from the total population for each day.

Currently I’m doing:

```julia
#calculate statistics on the two groups
date_stats = combine(groupby(df,["date"]), df -> DataFrame(date_sum = sum(df.count))) #total count per day
age_group_stats = combine(groupby(df,["date","age_group"]), df -> DataFrame(age_group_sum = sum(df.count))) #agegroup count per day

# join and add/calculate percentage
df = innerjoin(date_stats, age_group_stats, on = :date)
df[:, :age_percentage] = df.age_group_sum./df.date_sum

```

Giving me:

```julia
10×5 DataFrame
 Row │ date date_sum age_group age_group_sum age_percentage 
     │ Int64 Int64 Int64 Int64 Float64        
─────┼───────────────────────────────────────────────────────────
   1 │ 1 110 1 10 0.0909091
   2 │ 1 110 2 30 0.272727
   3 │ 1 110 3 20 0.181818
   4 │ 1 110 4 20 0.181818
  ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮
   8 │ 2 210 3 30 0.142857
   9 │ 2 210 4 10 0.047619
  10 │ 2 210 5 140 0.666667
                                                   3 rows omitted

```

Since I’m not very familiar with DataFrames.jl I’m sure there is a much neater way to do this with less allocations (I don’t need the` age_group_sum` afterwards), less lines (maybe one single` groupby` call?). Any tips are highly appreciated.

---

<div class="post-metadata">

**Author:** ![elbersb](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/elbersb/32/26568_2.png) [@elbersb](https://discourse.julialang.org/u/elbersb)\
**Post date:** [August 20, 2021, 11:41am UTC](https://discourse.julialang.org/t/dataframes-most-efficient-way-to-compute-statistics-on-multiple-nested-subgroups/66714/2 "2021-08-20T11:41:30Z")

</div>

How about:

```julia
agg = combine(groupby(df, [:date, :age_group]), :count => sum => :count) 
transform(groupby(agg, :date), :count => (x -> x / sum(x)) => :age_percentage)

```

---

<div class="post-metadata">

**Author:** ![pdeffebach](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/pdeffebach/32/10320_2.png) [@pdeffebach](https://discourse.julialang.org/u/pdeffebach)\
**Post date:** [August 20, 2021, 1:33pm UTC](https://discourse.julialang.org/t/dataframes-most-efficient-way-to-compute-statistics-on-multiple-nested-subgroups/66714/3 "2021-08-20T13:33:29Z")

</div>

[DataFramesMeta.jl](https://github.com/JuliaData/DataFramesMeta.jl) is also very useful for things.

I’m not following your problem exactly, but the `@by` macro does the grouping and transformation in one step.

You can also use `Chain.jl` (exported by DataFramesMeta) to avoid having to name multiple output data frames.