# How can I create an enumeration variable by groups?

I copied the title and concept for this question from here. I have

data = {{"ID", "Value"}, {1, 48}, {1, 45}, {1, 52}, {1, 43}, {1,
41}, {2, 50}, {2, 42}, {2, 51}, {2, 52}, {bb, 52}, {bb, 54}, {dd,
20}, {dd, 25}, {dd, 27}, {cc, 30}};


I want to create a new column called "Count" so that:

result = {{"ID", "Value", "Count"}, {1, 48, 1}, {1, 45, 2}, {1, 52,
3}, {1, 43, 4}, {1, 41, 5}, {2, 50, 1}, {2, 42, 2}, {2, 51,
3}, {2, 52, 4}, {bb, 52, 1}, {bb, 54, 2}, {dd, 20, 1}, {dd, 25,
2}, {dd, 27, 3}, {cc, 30, 1}};


I have a huge data with almost 2 billion observations. Therefore, efficient coding would save a lot of time and energy. As always, help is much appreciated. Thanks

r1 = Join[{Append[First[data], "Count"]}, Join @@ Values @
GroupBy[Rest @data, First, MapIndexed[Join, #]&]]


{{ID,Value,Count},{1,48,1},{1,45,2},{1,52,3},{1,43,4},{1,41,5},{2,50,1},{2,42,2},{2,51,3},{2,52,4},{bb,52,1},{bb,54,2},{dd,20,1},{dd,25,2},{dd,27,3},{cc,30,1}}

Update 1:

Prepend[Join @@ (MapIndexed[Join, #]&/@ SplitBy[Rest @ data, First]),
Append[First[data], "Count"]]


{{ID,Value,Count},{1,48,1},{1,45,2},{1,52,3},{1,43,4},{1,41,5},{2,50,1},{2,42,2},{2,51,3},{2,52,4},{bb,52,1},{bb,54,2},{dd,20,1},{dd,25,2},{dd,27,3},{cc,30,1}}

Update 2:

addCounter = Module[{cnt}, cnt[_String] := "Count"; cnt[_] := 1; {##, cnt[#]++} & @@@ #]&;



{{"ID", "Value", "Count"}, {1, 48, 1}, {1, 45, 2}, {1, 52, 3}, {1, 43, 4}, {1, 41, 5}, {2, 50, 1}, {2, 42, 2}, {2, 51, 3}, {2, 52, 4}, {bb, 52, 1}, {bb, 54, 2}, {dd, 20, 1}, {dd, 25, 2}, {dd, 27, 3}, {cc, 30, 1}}

• @ kglr, thank you for your answer. One of the problem of using gatherby (at least for me) is that it rearranges data based on id variable. Is there any way to keep the order of id variable the same. Thank you. Commented Jan 15, 2018 at 22:56
• @ramesh, good point. I will post an update if i find a better solution.
– kglr
Commented Jan 15, 2018 at 23:06
• @rames, please see the update.
– kglr
Commented Jan 15, 2018 at 23:13
• I think it should work. I will apply it to my data and update you. Thank you so much kglr. You have helped me by answering most of my questions. I owe you more than I can pay! Commented Jan 15, 2018 at 23:28
• @ramesh, glad you found them useful.
– kglr
Commented Jan 15, 2018 at 23:29

Here's one possibility using Split (which assumes that the IDs being counted always appear in runs)

splitcount =
Transpose[
Flatten[{Transpose@#,
{Flatten[{
"Count", Range /@ Length /@ Split[#[[2 ;;, 1]]]
}]}
}, 1]
] &;


To do some time trials on longer data lists (though not as long as the one you're looking at), first build some data:

SeedRandom[123]
idlist = Flatten[
ConstantArray[#, RandomInteger[{1, 100}]] & /@ Range[10000]];
vallist = RandomInteger[{1, 60}, Length@idlist];
data = Join[{{"ID", "Value"}}, Transpose[{idlist, vallist}]];
Length@data

(* 507939 *)


Then

AbsoluteTiming[
res1 = splitcount[data];
]
AbsoluteTiming[
(* @kglr *)
res2 = Join[{Append[First[data], "Count"]},
Join @@ Values@GroupBy[Rest@data, First, MapIndexed[Join, #] &]];
]
AbsoluteTiming[
(* @kglr *)
res3 = Prepend[
Join @@ (MapIndexed[Join, #] & /@ SplitBy[Rest@data, First]),
Append[First[data], "Count"]];
]
AbsoluteTiming[
(* @kglr *)
]
AbsoluteTiming[
(* @JasonB. *)
keys = AssociationThread[Union[data[[2 ;;, 1]]] -> 0];
tally = Join[{"Count"}, keys[#] += 1 & /@ data[[2 ;;, 1]]];
]

res1 == res2 == res3 == res4 == res5

(* {0.416703, Null}
{0.739995, Null}
{1.16564, Null}
{1.81539, Null}
{1.93773, Null}
True  *)

• @ aardvark2012, thank you for answer. I really appreciate it ! Commented Jan 16, 2018 at 5:51

I can't speak to the efficiency of this, you'll have to try it on your dataset,

keys = AssociationThread[Union[data[[2 ;;, 1]]] -> 0];
tally = Join[{"Count"}, keys[#] += 1 & /@ data[[2 ;;, 1]]];