# Simple transformation of a dataframe with grouped data

**URL:** <https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660>\
**Category:** New to Julia\
**Created:** [June 14, 2024, 3:57pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660 "2024-06-14T15:57:51Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![sbacelar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sbacelar/32/4359_2.png) [@sbacelar](https://discourse.julialang.org/u/sbacelar)\
**Post date:** [June 14, 2024, 3:57pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660/1 "2024-06-14T15:57:51Z")

</div>

I am trying to transpose this R script to Julia without success:

```julia
library(dplyr)

culture <- tibble(
  ind = c(1, 1, 1, 2, 2, 2),
  year = c(2015, 2015, 2015, 2016, 2016, 2016),
  value = c(10, 20, 30, 26, 34, 67)
)

culture_w <- culture %>%
  group_by(year) %>%
  mutate(total = sum(value),
         w = value / total)

```

I tried the following solution (and many others) but it gives me an error saying that :total is not in the dataframe.

```julia
using DataFrames, DataFramesMeta

culture = DataFrame(
    ind = [1, 1, 1, 2, 2, 2],
    year = [2015, 2015, 2015, 2016, 2016, 2016],
    value = [10, 20, 30, 26, 34, 67]
)
culture_w = @by culture [:year, :ind] begin
    :total = sum(:value)
    :w = :value ./ :total
end

```

---

<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:** [June 14, 2024, 4:13pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660/2 "2024-06-14T16:13:51Z")

</div>

Yes, unlike `dplyr`, DataFramesMeta.jl does not allow using previously-created variables in the same call.

You would _think_ the solution is to use the `@astable` macro-flag, which lets you create multiple intermediate variables and return a `NamedTuple`. But this is not the solution

```julia
julia> @by culture [:year, :ind] begin
           @astable begin
               :total = sum(:value)
               :w = :value ./ :total
           end
       end
ERROR: ArgumentError: mixing single values and vectors in a named tuple is not allowed

```

This is because `:total` is a scalar while `:w` is a `Vector`. When returning a `NamedTuple` you can’t mix scalars and vectors. See some discussion [here](https://github.com/JuliaData/DataFrames.jl/issues/3408).

I think what you want is a `@chain` with multiple `@transform` calls, the first one takes in a `GroupedDataFrame` but preserves the number of rows in the data frame (unlike `dplyr`, the output drops the grouping by default, which I think is very convenient).

```julia
julia> @chain culture begin
           @groupby [:year, :ind]
           @transform :total = sum(:value) # This gets "spread"
           @rtransform :w = :value / :total
       end
6×5 DataFrame
 Row │ ind year value total w        
     │ Int64 Int64 Int64 Int64 Float64  
─────┼──────────────────────────────────────
   1 │ 1 2015 10 60 0.166667
   2 │ 1 2015 20 60 0.333333
   3 │ 1 2015 30 60 0.5
   4 │ 2 2016 26 127 0.204724
   5 │ 2 2016 34 127 0.267717
   6 │ 2 2016 67 127 0.527559

```

---

<div class="post-metadata">

**Author:** ![sbacelar](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/sbacelar/32/4359_2.png) [@sbacelar](https://discourse.julialang.org/u/sbacelar)\
**Post date:** [June 14, 2024, 4:41pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660/3 "2024-06-14T16:41:25Z")

</div>

> [@pdeffebach](#):
>
> ```julia
> @chain culture begin
> @groupby [:year, :ind]
> @transform :total = sum(:value) # This gets "spread"
> @rtransform :w = :value / :total
> end
> 
> ```

It’s interesting to know why this does not work in `Tidier.jl`:

```julia
using DataFrames, Chain, Tidier

culture = DataFrame(
    ind = [1, 1, 1, 2, 2, 2],
    year = [2015, 2015, 2015, 2016, 2016, 2016],
    value = [10, 20, 30, 26, 34, 67]
)

culture_w = @chain culture begin
           @group_by(year)
           @mutate(total = sum(value), w = value / total)
       end

```

---

<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:** [June 14, 2024, 5:22pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660/4 "2024-06-14T17:22:48Z")

</div>

You mean it does not work in `Tidier.jl` either? That’s expected. Both DataFramesMeta.jl and Tidier.jl just call `DataFrames.transform(df, ...)` and the restriction from not re-using columns created previously is in DataFrames.jl

---

<div class="post-metadata">

**Author:** ![kdpsingh](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/kdpsingh/32/215018_2.png) [@kdpsingh](https://discourse.julialang.org/u/kdpsingh)\
**Post date:** [June 17, 2024, 6:09pm UTC](https://discourse.julialang.org/t/simple-transformation-of-a-dataframe-with-grouped-data/115660/5 "2024-06-17T18:09:11Z")

</div>

It’s also relevant to know _why_ this doesn’t work. DataFrames.jl parallelizes code when `transform()` supplies multiple transformations on grouped data frames.

See for details: [Functions · DataFrames.jl](https://dataframes.juliadata.org/stable/lib/functions/)

So while it’s nice syntactic sugar in R to be able to reuse variables in the same `mutate()` call in R, Julia takes the stance that it’s better to have code run faster. It’s easy enough to chain together separate `@mutate()` macros if you really want to add multiple columns sequentially, like this:

```julia
culture_w = @chain culture begin
    @group_by(year)
    @mutate(total = sum(value))
    @mutate(w = value / total)
end

```