# DataFrames.jl: keep group by result in every row

**URL:** <https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110>\
**Category:** General Usage\
**Tags:** dataframes\
**Created:** [February 3, 2020, 10:10am UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110 "2020-02-03T10:10:19Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![altre](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/altre/32/17036_2.png) [@altre](https://discourse.julialang.org/u/altre)\
**Post date:** [February 3, 2020, 10:10am UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110/1 "2020-02-03T10:10:19Z")

</div>

Using R data.table I’m used to saving the result of a group by aggregation in each row of each group, e.g.:

```julia
> d = data.table(a=c(1,2,3,4), b=c(1,1,2,2))
> d
   a b
1: 1 1
2: 2 1
3: 3 2
4: 4 2
> d[,s:=sum(a), b]
> d
   a b s
1: 1 1 3
2: 2 1 3
3: 3 2 7
4: 4 2 7

```

The last command groups by the column b, sums the values in a and writes the result in each row of the groups from b.

Using DataFrames.jl, I’ve currently always been doing this:

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

julia> join(by(d, :b, g -> sum(g[:, :a])), d, on=:b)
4×3 DataFrame
│ Row │ b │ x1 │ a │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┤
│ 1 │ 1 │ 3 │ 1 │
│ 2 │ 1 │ 3 │ 2 │
│ 3 │ 2 │ 7 │ 3 │
│ 4 │ 2 │ 7 │ 4 │

```

This seems to me to be a bit complicated to write, hard to read and probably inefficient, due to the unnecessary join.

Is there a better way?

---

<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:** [February 3, 2020, 11:52am UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110/2 "2020-02-03T11:52:32Z")

</div>

DataFramesMeta’s `@transform` macro on a grouped DataFrame will do what you want.

This kind of transformation is something that will hopefully be added to DataFrames soon.

---

<div class="post-metadata">

**Author:** ![altre](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/altre/32/17036_2.png) [@altre](https://discourse.julialang.org/u/altre)\
**Post date:** [February 3, 2020, 4:44pm UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110/3 "2020-02-03T16:44:32Z")

</div>

Thanks, in case anybody is interested, here it is:

```julia
> using DataFramesMeta
> @transform(groupby(d, :b), s=sum(:a))
4×3 DataFrame
│ Row │ a │ b │ s │
│ │ Int64 │ Int64 │ Int64 │
├─────┼───────┼───────┼───────┤
│ 1 │ 1 │ 1 │ 3 │
│ 2 │ 2 │ 1 │ 3 │
│ 3 │ 3 │ 2 │ 7 │
│ 4 │ 4 │ 2 │ 7 │

```

---

<div class="post-metadata">

**Author:** ![Mattriks](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/mattriks/32/351_2.png) [@Mattriks](https://discourse.julialang.org/u/Mattriks)\
**Post date:** [February 3, 2020, 7:38pm UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110/4 "2020-02-03T19:38:46Z")

</div>

Here’s another solution:

```julia
sumfill(x) = (a=x, s=fill(sum(x), length(x))) 
by(d, :b, :a=>sumfill)

```

---

<div class="post-metadata">

**Author:** ![jules](https://avatars.discourse-cdn.com/v4/letter/j/41988e/32.png) [@jules](https://discourse.julialang.org/u/jules)\
**Post date:** [February 9, 2020, 12:24pm UTC](https://discourse.julialang.org/t/dataframes-jl-keep-group-by-result-in-every-row/34110/5 "2020-02-09T12:24:05Z")

</div>

I’ve just made a small macro package that approximates data.table syntax: [[ANN] FilteredGroupbyMacro.jl](https://discourse.julialang.org/t/ann-filteredgroupbymacro-jl/34373)

It has the assignment syntax you want as well, although it uses a join in the background. At least you don’t have to write that out.

Your example would be: (the grouping variable comes second)

```julia
@by d[!, :b, s := sum(:a)]

```