7
$\begingroup$

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

$\endgroup$
8
$\begingroup$
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[#]++} & @@@ #]&;

addCounter @ data

{{"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}}

$\endgroup$
  • $\begingroup$ @ 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. $\endgroup$ – ramesh Jan 15 '18 at 22:56
  • $\begingroup$ @ramesh, good point. I will post an update if i find a better solution. $\endgroup$ – kglr Jan 15 '18 at 23:06
  • $\begingroup$ @rames, please see the update. $\endgroup$ – kglr Jan 15 '18 at 23:13
  • $\begingroup$ 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! $\endgroup$ – ramesh Jan 15 '18 at 23:28
  • $\begingroup$ @ramesh, glad you found them useful. $\endgroup$ – kglr Jan 15 '18 at 23:29
8
$\begingroup$

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 *)
 res4 = addCounter@data;
 ]
AbsoluteTiming[
 (* @JasonB. *)
 keys = AssociationThread[Union[data[[2 ;;, 1]]] -> 0]; 
 tally = Join[{"Count"}, keys[#] += 1 & /@ data[[2 ;;, 1]]]; 
 res5 = MapThread[Append, {data, tally}];
 ]

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

(* {0.416703, Null}
   {0.739995, Null}
   {1.16564, Null}
   {1.81539, Null}
   {1.93773, Null}
   True  *)
$\endgroup$
  • $\begingroup$ @ aardvark2012, thank you for answer. I really appreciate it ! $\endgroup$ – ramesh Jan 16 '18 at 5:51
6
$\begingroup$

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]]];
data2 = MapThread[Append, {data, tally}]

(* {{"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}} *)
$\endgroup$
  • $\begingroup$ Thank you Jason. I really appreciate it for its simplicity. $\endgroup$ – ramesh Jan 15 '18 at 23:39

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.