# Rownumber() in Dataframe like SQL

**URL:** <https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098>\
**Category:** New to Julia\
**Tags:** dataframes, sorting\
**Created:** [November 26, 2021, 8:41am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098 "2021-11-26T08:41:35Z")\
**Posts on this page:** 10\
**Page:** 1

<div class="post-metadata">

**Author:** ![AlexanderChen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alexanderchen/32/22007_2.png) [@AlexanderChen](https://discourse.julialang.org/u/AlexanderChen)\
**Post date:** [November 26, 2021, 8:41am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/1 "2021-11-26T08:41:35Z")

</div>

Hi,

In SQL you have a function called rownumber() over which you partition a certain column and get a column from 1 …n. Like this:

![image](https://global.discourse-cdn.com/julialang/original/3X/6/a/6a15bf4e87091ecd7a04b38591209e9545df4b95.png)

dataframes has a function called rownumber but it only return an integer in specific cases. How do I do this is Dataframes

---

<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 26, 2021, 9:34am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/2 "2021-11-26T09:34:40Z")

</div>

```julia
df.row_num = 1:nrow(df)

```

---

<div class="post-metadata">

**Author:** ![AlexanderChen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alexanderchen/32/22007_2.png) [@AlexanderChen](https://discourse.julialang.org/u/AlexanderChen)\
**Post date:** [November 26, 2021, 10:32am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/3 "2021-11-26T10:32:46Z")

</div>

Hi,

Can I still partition over this? I need 2 row counts for two different columns.

best,

---

<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 26, 2021, 11:11am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/4 "2021-11-26T11:11:47Z")

</div>

Sorry, I don’t understand the question - what does “partition over this” mean? How would the row count be different for two columns when they’re in the same DataFrame?

---

<div class="post-metadata">

**Author:** ![AlexanderChen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alexanderchen/32/22007_2.png) [@AlexanderChen](https://discourse.julialang.org/u/AlexanderChen)\
**Post date:** [November 26, 2021, 11:34am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/5 "2021-11-26T11:34:16Z")

</div>

Hi @nilshg,

sorry then I did not explain it correctly.

I have the current df ordered on alphabetical order on column ‘NAMES’.  
Once done I order the same df on ‘NAMES\_NUMBER’ and create a separate column that gives me the rownumbers of that order bunch. So 1 name will have 2 different rownumbers. In SQL you can explicitly say one rownumber goes over one column and another rownumber goes over another column. You do that by partitioning it explicitly for both instanstances.

does that make sense?

best,

---

<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 26, 2021, 11:36am UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/6 "2021-11-26T11:36:52Z")

</div>

Okay, if I understand correctly wouldn’t that just be:

```julia
sort!(df, :NAMES)
df.row_num_1= 1:nrow(df)
sort!(df, :NAMES_NUMBER)
df.row_num_2 = 1_nrow(df)

```

?

---

<div class="post-metadata">

**Author:** ![AlexanderChen](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/alexanderchen/32/22007_2.png) [@AlexanderChen](https://discourse.julialang.org/u/AlexanderChen)\
**Post date:** [November 26, 2021, 1:04pm UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/7 "2021-11-26T13:04:50Z")

</div>

yes, sometimes one gets stuck in tunnelvision :). Thanks!

---

<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:** [November 26, 2021, 2:04pm UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/8 "2021-11-26T14:04:26Z")

</div>

~~Instead of sorting the whole dataframe just to record the row numbers of the sorted items, you could record the output of `sortperm` for both columns.~~

```julia
julia> df = DataFrame(a = rand(10), b = rand(10))
10×2 DataFrame
 Row │ a b
     │ Float64 Float64
─────┼──────────────────────
   1 │ 0.710751 0.981133
   2 │ 0.407548 0.820994
   3 │ 0.560146 0.488446
   4 │ 0.851708 0.167793
   5 │ 0.0648273 0.309059
   6 │ 0.618235 0.818621
   7 │ 0.0239858 0.433564
   8 │ 0.741798 0.0706922
   9 │ 0.947348 0.719011
  10 │ 0.492506 0.430335

julia> transform(df, [:a, :b] .=> sortperm .=> [:rownumber_a, :rownumber_b])
10×4 DataFrame
 Row │ a b rownumber_a rownumber_b
     │ Float64 Float64 Int64 Int64
─────┼────────────────────────────────────────────────
   1 │ 0.710751 0.981133 7 8
   2 │ 0.407548 0.820994 5 4
   3 │ 0.560146 0.488446 2 5
   4 │ 0.851708 0.167793 10 10
   5 │ 0.0648273 0.309059 3 7
   6 │ 0.618235 0.818621 6 3
   7 │ 0.0239858 0.433564 1 9
   8 │ 0.741798 0.0706922 8 6
   9 │ 0.947348 0.719011 4 2
  10 │ 0.492506 0.430335 9 1

```

Edit: Wait that’s not entirely correct, but it’s close in terms of the underlying idea. This just has the number you’d have to index at to receive the sorted vector…

---

<div class="post-metadata">

**Author:** ![rafael.guerra](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/rafael.guerra/32/216610_2.png) [@rafael.guerra](https://discourse.julialang.org/u/rafael.guerra)\
**Post date:** [November 26, 2021, 5:17pm UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/9 "2021-11-26T17:17:27Z")

</div>

Trying to follow @jules here.  
`N` is the index for `Names` and `NN` for `NamesNum`:

```julia
using DataFrames
Names = ["Beth", "Carl", "Ana", "Dan"]
NamesNum = [11, 5, 2, 7]
n = nrow(df)
df = DataFrame(Names=Names, NamesNum=NamesNum)

df.N, df.NN = sortperm.([df.Names, df.NamesNum])
df.N[df.N] .= 1:n
df.NN[df.NN] .= 1:n
df

 Row │ Names NamesNum N NN    
     │ String Int64 Int64 Int64 
─────┼────────────────────────────────
   1 │ Beth 11 2 4
   2 │ Carl 5 3 2
   3 │ Ana 2 1 1
   4 │ Dan 7 4 3

```

---

<div class="post-metadata">

**Author:** ![eotero](https://sea2.discourse-cdn.com/julialang/user_avatar/discourse.julialang.org/eotero/32/36642_2.png) [@eotero](https://discourse.julialang.org/u/eotero)\
**Post date:** [August 28, 2023, 4:21pm UTC](https://discourse.julialang.org/t/rownumber-in-dataframe-like-sql/72098/10 "2023-08-28T16:21:36Z")

</div>

Bogumil recommends the built-in function “eachindex” for:  
`combine(groupby(df,field) , eachindex) `

[“RowNumber by Partition” function · Issue #3374 · JuliaData/DataFrames.jl (github.com)](https://github.com/JuliaData/DataFrames.jl/issues/3374)

Hope this helps.