# Row number in DataFrames groupby query

**URL:** <https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319>\
**Category:** Data\
**Created:** [March 16, 2021, 5:40pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319 "2021-03-16T17:40:08Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![bdeonovic](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bdeonovic/32/3928_2.png) [@bdeonovic](https://discourse.julialang.org/u/bdeonovic)\
**Post date:** [March 16, 2021, 5:40pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/1 "2021-03-16T17:40:08Z")

</div>

I’m trying to understand how to do data manipulations in DataFrames (+ Query or + DataFramesMeta or +LINQ), in particular today I am looking to get rownumber in a groupby query

For MWE consider

```julia
df = DataFrame(a=repeat([1,2,3], inner=[2]), b=randn(6))

```

Lets say I want to group by `a`, get mean of `b` within `a`, and return something like:

```julia
df |>
 @groupby(_.a) |> 
 @map({rows=_, m=mean(_.b), t=1:length(_)}) |> 
 @mapmany(_.t, {r=__, getindex(_.rows,__).a, getindex(_.rows,__).b, _.m}) |>
 DataFrame

```

```julia
6×4 DataFrame
 Row │ r a b measure   
     │ Int64 Int64 Float64 Float64   
─────┼────────────────────────────────────
   1 │ 1 1 0.137441 -0.347454
   2 │ 2 1 -0.832349 -0.347454
   3 │ 1 2 0.775431 0.68382
   4 │ 2 2 0.592209 0.68382
   5 │ 1 3 -1.17639 -0.324448
   6 │ 2 3 0.527495 -0.324448

```

Is there a better way to do this? in particular, my real world example I have too many variables for this `getindex` approach to work.

In R I would do something like this

```nohighlight
> data.frame(a=rep(1:3,each=2), b=rnorm(2*3)) %>% group_by(a) %>% mutate(m=mean(b), r=row_number())
# A tibble: 6 x 4
# Groups: a [3]
      a b m r
  <int> <dbl> <dbl> <int>
1 1 0.474 0.391 1
2 1 0.307 0.391 2
3 2 0.500 -0.370 1
4 2 -1.24 -0.370 2
5 3 -1.33 -0.862 1
6 3 -0.398 -0.862 2

```

The use of `_` and `__` is almost ineligible to me.

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [March 16, 2021, 5:52pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/2 "2021-03-16T17:52:00Z")

</div>

Kinda hacky but this works:

```julia
transform(groupby(df,:a),"b"=>mean,"b"=> (x->1:length(x)) =>"r")

```

I just make that column be a `1:n` range where `n` is the number of elements in the group.

---

<div class="post-metadata">

**Author:** ![bdeonovic](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/bdeonovic/32/3928_2.png) [@bdeonovic](https://discourse.julialang.org/u/bdeonovic)\
**Post date:** [March 16, 2021, 5:58pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/3 "2021-03-16T17:58:42Z")

</div>

> [@tbeason](#):
>
> `transform(groupby(df,:a),"b"=>mean,"b"=> (x->1:length(x)) =>"r")`

How do you groupby multiple columns in this fashion?

---

<div class="post-metadata">

**Author:** ![tbeason](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/tbeason/32/15898_2.png) [@tbeason](https://discourse.julialang.org/u/tbeason)\
**Post date:** [March 16, 2021, 5:59pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/4 "2021-03-16T17:59:33Z")

</div>

`groupby(df,[:a1,:a2])`

---

<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:** [March 16, 2021, 8:51pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/5 "2021-03-16T20:51:37Z")

</div>

> [@bdeonovic](#):
>
> The use of `_` and `__` is almost ineligible to me.

Maybe you’d be interested in other piping macro packages then, which try to cut down on repetitive syntax. For example Chain.jl

```julia
using Chain

@chain df begin
    groupby(:a)
    transform(:b => mean, :b => (x -> 1:length(x)) => :r)
end

```

You don’t have to write \_ if your dataframe is the first argument, a bit like in R

---

<div class="post-metadata">

**Author:** ![xiaodai](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/xiaodai/32/15937_2.png) [@xiaodai](https://discourse.julialang.org/u/xiaodai)\
**Post date:** [August 13, 2021, 1:30pm UTC](https://discourse.julialang.org/t/row-number-in-dataframes-groupby-query/57319/6 "2021-08-13T13:30:10Z")

</div>

> [@bdeonovic](#):
>
> In R I would do something like this

this is an intro to my recommended packages [https://www.youtube.com/watch?v=rDvpLFxcL84](https://www.youtube.com/watch?v=rDvpLFxcL84)

For this in R

```julia
data.frame(a=rep(1:3,each=2), b=rnorm(2*3)) %>% 
   group_by(a) %>% 
   mutate(m=mean(b), r=row_number())

```

I would do like this where the `@c` macro means treat column `:b` as a vector and not apply the function by row which is normally what you want with `@transform`

```julia
using Chain, DataFrames, DataFrameMacros
using StatsBase: mean

df = DataFrame(DataFrame(a=repeat([1,2,3], inner=[2]), b=randn(6))
@chain df begin
    @groupby(:a)
    @transform(:m = @c(mean(:b)), :r = @c(1:length(:b)))
end

```

so it’s just as simple as R once you know what packages to use.