# Combining elements from multiple rows by conditionals into columns with DataFramesMeta

**URL:** <https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097>\
**Category:** General Usage\
**Tags:** dataframes, dataframesmeta\
**Created:** [July 2, 2023, 10:59pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097 "2023-07-02T22:59:16Z")\
**Posts on this page:** 11\
**Page:** 1

<div class="post-metadata">

**Author:** ![alequa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alequa/32/12338_2.png) [@alequa](https://discourse.julialang.org/u/alequa)\
**Post date:** [July 2, 2023, 10:59pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/1 "2023-07-02T22:59:16Z")

</div>

Hello,  
I want to create a new dataframe with a column computed from one or more columns of the initial dataframe.

```julia
using DataFramesMeta
df1 = DataFrame(category = [1,0,1,0], group=[1,1,0,0], measure = [401, 402, 200, 300]);
df2 = @somemagic :group == 1 :result = :measure_false - :measure_true 
## I want this output:
# df2 = DataFrame(category = [1,0], difference = [201, 102]);

```

In the example I want to compute a function of :measure conditional to the value in :group, this has to happen separately for all the elements that belong to category.

The warranty is only that there are 2 elements for category, or as many elements that can be passed to the func.

I can achieve the same by creating separated dataframes:

```julia
func(x,y) = x-y

sort!(df, :category)
aa = @rsubset df :group == 1
bb = @rsubset df :group !== 1
zz = func.(aa.measure, bb.measure)

```

But I would like something in the macro style

Thanks for your help

---

<div class="post-metadata">

**Author:** ![Dan](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/dan/32/42581_2.png) [@Dan](https://discourse.julialang.org/u/Dan)\
**Post date:** [July 2, 2023, 11:29pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/2 "2023-07-02T23:29:56Z")

</div>

```julia
func(category0, category1) = category1 - category0

select(
  innerjoin(groupby(df1, :group, sort=true)..., on = :category, makeunique=true), 
  :category, r"measure" => func => [:difference]
)

```

gives:

```julia
2×2 DataFrame
 Row │ category difference 
     │ Int64 Int64      
─────┼──────────────────────
   1 │ 1 201
   2 │ 0 102

```

This also generalizes to more than two categories with appropriate `func`.

This option doesn’t use macros (others more familiar with DataFrameMeta might help with this).

Another option with `unstack`:

```julia
select(
  unstack(df1, :group, :measure, renamecols=x->Symbol(:_, x)), 
  :category, [:_0, :_1] => func => [:difference]
)

```

(with the same result)

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [July 3, 2023, 5:07am UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/3 "2023-07-03T05:07:43Z")

</div>

I didn’t understand the role of the :group variable and if it comes into play.  
As far as I understand, I would do this.

```julia

gdf=groupby(df1,:category)
combine(gdf,:measure=>x->-(x...))

#or

combine(gdf,:measure=>Base.splat(-)=>:res)

```

Quickly sifting through the help of the DataframesMeta pkg I find this macro that seems provide the same result.

```julia

func(x,y) = x-y
@by df1 :category begin
    :res=func(:measure...)
end

```

Perhaps, I found a role for :group to play 😁

```julia

df1[df1.group.==0,:group].=-1
using LinearAlgebra
@by df1 :category begin
    :res=dot(:measure, :group)
end

```

---

<div class="post-metadata">

**Author:** ![alequa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alequa/32/12338_2.png) [@alequa](https://discourse.julialang.org/u/alequa)\
**Post date:** [July 3, 2023, 8:39am UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/4 "2023-07-03T08:39:01Z")

</div>

Hello!  
Thanks for your advise. In the specific case here, it works!

But in general, I would like to do operations (as in, choose the arguments in `func`) based on the value in `group`. In this case, it is only the order (`group==0` is subtracted to `group==1`) , but I could have several values for `group` field.

The solution proposed by @Dan accounts for this. But it does not use the DataFramesMacro.

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [July 3, 2023, 9:13am UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/5 "2023-07-03T09:13:44Z")

</div>

If you provide a minimal but more complete and general example, someone can try to provide you with the solution in the form you ask.  
What specifically does it mean that group can have many values?  
How would these values then be combined (the ‘-’ function is neither associative nor commutative)?

---

<div class="post-metadata">

**Author:** ![alequa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alequa/32/12338_2.png) [@alequa](https://discourse.julialang.org/u/alequa)\
**Post date:** [July 3, 2023, 9:36am UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/6 "2023-07-03T09:36:38Z")

</div>

Hello,

Thanks! This is the type of operation I was looking for.

It took me some time to understand what the two nested functions do, I will explain it here so to facilitate the future readers.

If someone knows how to do the same operations with DataFramesMeta it d be great.

Alessio

#### 1. groupby + innerjoin

```julia-auto
func(category0, category1) = category1 - category0

select(
  innerjoin(groupby(df1, :group, sort=true)..., on = :category, makeunique=true), 
  :category, r"measure" => func => [:difference]
)

```

The `groupby` function creates a list of dataframes, each for a value of `:group`.  
`innerjoin` stack the newly created dataframes using `:category` as unique id. The `makeunique` argument is pivotal, and it works like this:

- `makeunique` : if `false` (the default), an error will be raised if duplicate names are found in columns not joined on; if `true`, duplicate names will be suffixed with `_i` (`i` starting at 1 for the first duplicate).

The resulting dataframe has columns:  
[:category, :group, :group\_1, :measure, :measure\_1]

The `select` function uses a regular expression to parse the columns that start with “measure”.

The order of the stacked data frames is used to pass the columns in order. In this case, it is easy because it is just two `:group`, but how to have more control on it?

#### 2. unstack

```julia-auto
select(
  unstack(df1, :group, :measure, renamecols=x->Symbol(:_, x)), 
  :category, [:_0, :_1] => func => [:difference]
)

```

The problem of arranging columns based on their name is easier solved with `unstack`.

`unstack` creates new columns based on the values in `:group` in the initial dataframe. I suggest reading the documentation because the positional arguments are not trivial.

The resulting dataframe has columns:  
[:category, :\_0, :\_1]

Where `_0` and `_1` are derived by the values in `:group`. This is convenient because the following `select` function can leverage the named columns:

`select(unstacked_df, :category, [:_0, :_1] => func => [:difference]`

---

<div class="post-metadata">

**Author:** ![alequa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alequa/32/12338_2.png) [@alequa](https://discourse.julialang.org/u/alequa)\
**Post date:** [July 3, 2023, 9:42am UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/7 "2023-07-03T09:42:32Z")

</div>

Mate, the minimal example was clear enough. The solution proposed by @Dan was accurate in this regard.

In this case :group can have `0` or `1`. If :group was `1,2,3,...10`. Then I would like control on the columns used in the select function.

Surely I can order them and be consistent, but it is not a robust solution.  
The solution with `unstack` (see my answer) works well and allows to run an arbitrary complex function based on `:group`, as in

`select(:category, [:_1, :_3, :_1, :_2] => func => [:result])`

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [July 3, 2023, 1:31pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/8 "2023-07-03T13:31:27Z")

</div>

Maybe now I understand better.  
Not quite what you’re looking for, but something that goes a little way in that direction.

I don’t know if it is possible to reproduce with the dataframesmeta macros (I don’t know the package) the expansion into many columns of a namedtuple made by the AsTable function.

```julia
julia> func(x,y)=x-y
func (generic function with 1 method)

julia> sdf=@chain df begin
           @by :category begin
               :res=[(;zip(Symbol.("g_",:group),:measure)...)]
           end
           select(:category,:res=>AsTable)
           @transform :diff= func(:g_1, :g_0)
       end
2×4 DataFrame
 Row │ category g_1 g_0 diff  
     │ Int64 Int64 Int64 Int64
─────┼───────────────────────────────
   1 │ 0 402 300 102
   2 │ 1 401 200 201

```

---

<div class="post-metadata">

**Author:** ![rocco\_sprmnt21](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rocco_sprmnt21/32/20127_2.png) [@rocco\_sprmnt21](https://discourse.julialang.org/u/rocco_sprmnt21)\
**Post date:** [July 3, 2023, 4:19pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/9 "2023-07-03T16:19:41Z")

</div>

the use of the `indexin()` function allows you to associate the measures in an orderly manner with the names of the selected groups, on which to apply a generic function.

```julia
julia> df = DataFrame(category = [1,0,1,0,1,0], group=[1,1,0,0,-9,-9], measure = [401, 402, 200, 300,-100,-200])
6×3 DataFrame
 Row │ category group measure 
     │ Int64 Int64 Int64
─────┼──────────────────────────
   1 │ 1 1 401
   2 │ 0 1 402
   3 │ 1 0 200
   4 │ 0 0 300
   5 │ 1 -9 -100
   6 │ 0 -9 -200

julia> @chain df begin
       @by :category begin
           :g= [:group]
           :m= [:measure]
           @kwarg ungroup = false
       end
       @transform :diff_1_9= func(:m[1][[indexin([1,-9],:g[1])]...]...)
       end
2×4 DataFrame
 Row │ category g m diff_1_9 
     │ Int64 SubArray… SubArray… Int64
─────┼──────────────────────────────────────────────────
   1 │ 0 [1, 0, -9] [402, 300, -200] 602
   2 │ 1 [1, 0, -9] [401, 200, -100] 501

```

```julia
julia> @chain df begin
       @by :category begin
           :g= [:group]
           :m= [:measure]
           @kwarg ungroup = false
       end
       @select begin
           :category
            :diff_1_9= func(:m[1][[indexin([1,-9],:g[1])]...]...)
       end
       end
2×2 DataFrame
 Row │ category diff_1_9 
     │ Int64 Int64
─────┼────────────────────
   1 │ 0 602
   2 │ 1 501

```

---

<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:** [July 3, 2023, 5:31pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/10 "2023-07-03T17:31:17Z")

</div>

Maybe this works?

```julia
julia> @chain df begin
           groupby(:category)
           @combine begin
               :x = first(:measure[:group .== 1]) - first(:measure[:group .== 0])
           end
       end
2×2 DataFrame
 Row │ category x     
     │ Int64 Int64 
─────┼─────────────────
   1 │ 0 102
   2 │ 1 201

```

---

<div class="post-metadata">

**Author:** ![alequa](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alequa/32/12338_2.png) [@alequa](https://discourse.julialang.org/u/alequa)\
**Post date:** [July 5, 2023, 3:41pm UTC](https://discourse.julialang.org/t/combining-elements-from-multiple-rows-by-conditionals-into-columns-with-dataframesmeta/101097/11 "2023-07-05T15:41:49Z")

</div>

This is precisely what I was looking for.

Thanks.