# Calculation question

**URL:** <https://discourse.julialang.org/t/calculation-question/72042>\
**Category:** New to Julia\
**Tags:** question, dataframes\
**Created:** [November 25, 2021, 4:41am UTC](https://discourse.julialang.org/t/calculation-question/72042 "2021-11-25T04:41:07Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![SergeantMike67](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sergeantmike67/32/25103_2.png) [@SergeantMike67](https://discourse.julialang.org/u/SergeantMike67)\
**Post date:** [November 25, 2021, 4:41am UTC](https://discourse.julialang.org/t/calculation-question/72042/1 "2021-11-25T04:41:07Z")

</div>

I am trying to make a graph of over 200 emergence curves from a dataset that is structured below.

```julia
 Row │ Farm Variety Container ED Wasp  
     │ String String String Float64 Int16 
─────┼────────────────────────────────────────────
   1 │ Talsma Aurora A1-1 15.375 1
   2 │ Talsma Aurora A1-1 15.5417 1
   3 │ Talsma Aurora A1-1 15.5417 1
   4 │ Talsma Aurora A1-1 16.5417 2
  ⋮ │ ⋮ ⋮ ⋮ ⋮ ⋮
 275 │ Talsma Aurora A9-5 16.5417 1
 276 │ Talsma Aurora A9-5 17.5417 1
 277 │ Talsma Aurora A9-5 17.5417 2
 278 │ Talsma Aurora A9-5 18.5417 1

```

ED is the elapsed days from setting them out.  
So what I need to do is calculate the percentage of the total wasps (small non-stinging) from each Container that has emerged at the time points

I have imported the data into a DataFrame and have used

```julia
varDaySum=combine(varmean,:VarietyTotal=>sum)

```

Which results in a DataFrame that looks like this

```julia
48×2 DataFrame
 Row │ Container Wasp_sum 
     │ String Int64    
─────┼─────────────────────
   1 │ A1-1 55
   2 │ A1-2 21
   3 │ A1-3 8
   4 │ A1-4 4
  ⋮ │ ⋮ ⋮
  45 │ A9-2 11
  46 │ A9-3 1
  47 │ A9-4 11
  48 │ A9-5 5

```

Where varmean is a grouped DataFrame created from

```julia
varmean=groupby(dataset,:ED)

```

What I cannot figure out is how to calculate the cumlative percentages at each time point.

For example for the Container A1-1

```julia
Row │ ED Wasp Container 
     │ Float64 Int16 String    
─────┼───────────────────────────
   1 │ 15.375 1 A1-1
   2 │ 15.5417 1 A1-1
   3 │ 15.5417 1 A1-1
   4 │ 16.5417 2 A1-1
   5 │ 16.5417 38 A1-1
   6 │ 16.875 3 A1-1
   7 │ 16.875 3 A1-1
   8 │ 17.5417 2 A1-1
   9 │ 17.7083 1 A1-1
  10 │ 18.375 1 A1-1
  11 │ 19.5417 1 A1-1
  12 │ 21.875 1 A1-1

```

The series would be as follows

```julia
Time point Cumlative count Percent 
15.375 1 1/55=0.0181818
15.5417 3 3/55=0.0545454
16.5417 43 44/55=0.80000

```

etc

There must be an elegant coding solution and not a long series of for next loops building a DataFrame one column at time.

Mike Sergeant

---

<div class="post-metadata">

**Author:** ![nilshg](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/nilshg/32/2283_2.png) [@nilshg](https://discourse.julialang.org/u/nilshg)\
**Post date:** [November 25, 2021, 8:35am UTC](https://discourse.julialang.org/t/calculation-question/72042/3 "2021-11-25T08:35:20Z")

</div>

You’re basically doing this:

```julia
julia> using DataFrames

julia> test = DataFrame(ED = [15.3, 15.5, 15.5, 16.5, 16.5], Wasp = [1, 1, 1, 2, 38])
5×2 DataFrame
 Row │ ED Wasp  
     │ Float64 Int64 
─────┼────────────────
   1 │ 15.3 1
   2 │ 15.5 1
   3 │ 15.5 1
   4 │ 16.5 2
   5 │ 16.5 38

julia> test_grouped = combine(groupby(test, :ED), :Wasp => sum)
3×2 DataFrame
 Row │ ED Wasp_sum 
     │ Float64 Int64    
─────┼───────────────────
   1 │ 15.3 1
   2 │ 15.5 2
   3 │ 16.5 40

julia> test_grouped[!, :cumulative] = cumsum(test_grouped.Wasp_sum);

julia> test_grouped[!, :Percent] = test_grouped.Wasp_sum ./ maximum(test_grouped.Wasp_sum);

julia> test_grouped
3×4 DataFrame
 Row │ ED Wasp_sum cumulative Percent 
     │ Float64 Int64 Int64 Float64 
─────┼────────────────────────────────────────
   1 │ 15.3 1 1 0.025
   2 │ 15.5 2 3 0.05
   3 │ 16.5 40 43 1.0

```

And then just apply this across `Container` (ie apply these steps to the data grouped by `Container`)

There’s definitely also some sort of `@chain`-type solution where you write out all these steps in one macro call without the intermediate `test_grouped` DataFrame - I generally prefer the simplicity of base DataFrames but I’m sure someone will be around soon to show the macro solution.

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [November 25, 2021, 8:38am UTC](https://discourse.julialang.org/t/calculation-question/72042/4 "2021-11-25T08:38:13Z")

</div>

like this ?

(for some values of “elegant”)

```julia
julia> d = DataFrame([[1,1,2,2,5], [1,2,3,4,5]], ["a", "b"])
5×2 DataFrame
 Row │ a b
     │ Int64 Int64
─────┼──────────────
   1 │ 1 1
   2 │ 1 2
   3 │ 2 3
   4 │ 2 4
   5 │ 5 5

julia> tw = sum(d[!, :b])
15

julia> a1 = combine(groupby(d, :a), :a=>sum, :b=>cumsum)
5×3 DataFrame
 Row │ a a_sum b_cumsum
     │ Int64 Int64 Int64
─────┼────────────────────────
   1 │ 1 2 1
   2 │ 1 2 3
   3 │ 2 4 3
   4 │ 2 4 7
   5 │ 5 5 5

julia> a2 = combine(groupby(a1, :a), [:a_sum=>maximum, :b_cumsum=>maximum])
3×3 DataFrame
 Row │ a a_sum_maximum b_cumsum_maximum
     │ Int64 Int64 Int64
─────┼────────────────────────────────────────
   1 │ 1 2 3
   2 │ 2 4 7
   3 │ 5 5 5

julia> a3 = combine(a2, [:a=>(a)->a, :b_cumsum_maximum=>cumsum, :b_cumsum_maximum=>(b)->cumsum(b)/tw])
3×3 DataFrame
 Row │ a_function b_cumsum_maximum_cumsum b_cumsum_maximum_function
     │ Int64 Int64 Float64
─────┼────────────────────────────────────────────────────────────────
   1 │ 1 3 0.2
   2 │ 2 10 0.666667
   3 │ 5 15 1.0

```

or by using Pipe

```julia
 @pipe combine(groupby(d, :a), :a=>sum, :b=>cumsum) |>
       combine(groupby(_, :a), [:a_sum=>maximum, :b_cumsum=>maximum]) |>
       combine(_, [:a=>(a)->a, :b_cumsum_maximum=>cumsum, :b_cumsum_maximum=>(b)->cumsum(b)/tw])

```

---

<div class="post-metadata">

**Author:** ![SergeantMike67](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sergeantmike67/32/25103_2.png) [@SergeantMike67](https://discourse.julialang.org/u/SergeantMike67)\
**Post date:** [November 28, 2021, 3:32pm UTC](https://discourse.julialang.org/t/calculation-question/72042/5 "2021-11-28T15:32:34Z")

</div>

Thank you. I think I basically had this but kept getting lost in the logic. Need to go through the coding to see where I missed.

Mike

---

<div class="post-metadata">

**Author:** ![lawless-m](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/lawless-m/32/30869_2.png) [@lawless-m](https://discourse.julialang.org/u/lawless-m)\
**Post date:** [November 29, 2021, 7:57am UTC](https://discourse.julialang.org/t/calculation-question/72042/6 "2021-11-29T07:57:06Z")

</div>

It took me a few gos and I do this stuff all day 🙂