identity column by group?

[lmullany]

What is the python datatable equivalent of R data.tables approach to creating row number or sequential identity column by group?

In R, this is trivial and fast: data[, id:=1:.N, by=.(grp)].

In python datatable, I'd like to do something like:

data[:, dt.update(id = list(range(dt.count))), dt.by(f.grp)]

but that doesn't work..

My work around does work, but it seems exceedingly verbose, not to mention that it is slow on 60,000,000 row dataset with 3,000,000 plus groups

data['id'] = np.concatenate([np.arange(x) for x in data[:,dt.count(), dt.by(f.grp)]['count'].to_numpy()])

[oleksiyskononenko]

Unfortunately, as for the moment there is no way to access groupby C++ information from Python, including the number of groups and row indexes. So I don't really see an elegant solution here. As a workaround you can do something like this

import datatable as dt
from datatable import f

DT0 = dt.Frame({"grp" : [1, 5, 2, 2, 1], "val" : [0, 5, 3, 4, 2]})

DT1 = DT0[:, :, dt.by(f.grp)]
DT1["id"] = DT1[:, dt.qcut(f.grp, nquantiles=DT1[:, f.grp].nunique1())]

Though it is, of course, very far from the optimal solution.

[lmullany]

Thanks @oleksiyskononenko , but this doesn't quite do what I'm looking for. Perhaps I should have been clearer. This creates an fixed id per group (which frankly, we already have in grp. i.e. it produces this:

   |   grp    val     id
   | int32  int32  int32
-- + -----  -----  -----
 0 |     1      0      0
 1 |     1      2      0
 2 |     2      3      1
 3 |     2      4      1
 4 |     5      5      2

Also, we can't apply qcut when grp column is a string identifier.

What I'm actually looking for is row number within group as in R's data[, id:=1:.N, by=.(grp)]., producing something like this:

   |   grp    val     id
   | int32  int32  int32
-- + -----  -----  -----
 0 |     1      0      1
 1 |     1      2      2
 2 |     2      3      1
 3 |     2      4      2
 4 |     5      5      1

[lmullany]

Either way, you've answered the more important question re accessing C++ information from python... I notice you indicate "for the moment"... Does this mean that you envision that as possible/probable in the future? Is this something that the team is working towards? In my experience using R data.table, the "specials" (.N, .I, .BY, etc) are extremely valuable.. I find those, the use of any function in j, and melt/dcast capability are core capabilities, that would be great in order to take python datatable to the next level.

Really appreciate all that you and all the others working on datatable are doing!!

[oleksiyskononenko]

Yes, sorry, now I see what you mean. Unfortunately, I don't think there is a way to generate row ids in the groups directly in datatable. Take a look at this issue #2892 Yes, this of course could be implemented, if the issue gets more interest from the community.

[lmullany]

A useful alternative was provided on stackoverflow: See here -- see the selected answer with create_ranges() function.

[samukweku]

Datatable has a cumcount implementation now.

[lmullany]

Datatable has a cumcount implementation now.

thanks @samukweku , excellent.