From: Adam Prescott <adam@...>
Date: 2013-03-04T23:42:41+09:00
Subject: Re: Efficient Ruby equivalent to Excel's "SUMIF"?

--20cf3079b5ea56f76404d71a5e9f
Content-Type: text/plain; charset=UTF-8

I think part of the problem is you're using arrays quite heavily. You can
do it in one pass if you pick a Hash that you later convert to a more
appropriate structure, or simply define your data as a class and
instantiate relevant objects, etc.

Anyway, here's an example:


data = [
  [ 'Ref', 'Count', 'Locn' ],
  [ 'Part1', '3', 'Here' ],
  [ 'Part2', '1', 'Here' ],
  [ 'Part1', '2', 'There' ],
]

desired_output = [
  ["Ref", "Total", "Here", "There"],
  ["Part1", 5, 3, 2],
  ["Part2", 1, 1, 0]
]

# bump off the first element
headers = data.shift

references = {}

data.each do |reference, count, location|
  # if the references hash hasn't "seen" this reference yet,
  # create a hash to keep track of totals and locations.
  references[reference] ||= { :total => 0, :locations => {} }
  r = references[reference]

  # contribute to the total for this reference.
  r[:total] += count.to_i

  # if this location hasn't been seen for this reference yet,
  # start it off at zero. could also use Hash.new(0) in the
  # above definition of references[reference]
  r[:locations][location] ||= 0

  # now contribute to the location's count
  r[:locations][location] += count.to_i
end

# references now looks something like:
#
# {
#  "Part1" => { :total      => 5,
#               :locations => { "Here" => 3, "There" => 2 } },
#  "Part2" => { :total      => 1,
#               :locations  => { "Here" => 1 } }
# }

# This corresponds to desired_output, just in a different way.
#
# You can convert this to the original desired array if you wish, by
# iterating over references, or you can pick a more appropriate Hash
# structure and just use that.

--20cf3079b5ea56f76404d71a5e9f
Content-Type: text/html; charset=UTF-8
Content-Transfer-Encoding: quoted-printable

<div dir=3D"ltr">I think part of the problem is you&#39;re using arrays qui=
te heavily. You can do it in one pass if you pick a Hash that you later con=
vert to a more appropriate structure, or simply define your data as a class=
 and instantiate relevant objects, etc.<div>

<br></div><div>Anyway, here&#39;s an example:</div><div><br><div><br></div>=
<div><div>data =3D [</div><div>=C2=A0 [ &#39;Ref&#39;, &#39;Count&#39;, &#3=
9;Locn&#39; ],</div><div>=C2=A0 [ &#39;Part1&#39;, &#39;3&#39;, &#39;Here&#=
39; ],</div>

<div>=C2=A0 [ &#39;Part2&#39;, &#39;1&#39;, &#39;Here&#39; ],</div><div>=C2=
=A0 [ &#39;Part1&#39;, &#39;2&#39;, &#39;There&#39; ],</div><div>]</div><di=
v><br></div><div>desired_output =3D [</div><div>=C2=A0 [&quot;Ref&quot;, &q=
uot;Total&quot;, &quot;Here&quot;, &quot;There&quot;],</div>

<div>=C2=A0 [&quot;Part1&quot;, 5, 3, 2],</div><div>=C2=A0 [&quot;Part2&quo=
t;, 1, 1, 0]</div><div>]</div><div><br></div><div style># bump off the firs=
t element</div><div>headers =3D data.shift</div><div><br></div><div>referen=
ces =3D {}</div>

<div><br></div><div>data.each do |reference, count, location|</div><div>=C2=
=A0 # if the references hash hasn&#39;t &quot;seen&quot; this reference yet=
,</div><div>=C2=A0 # create a hash to keep track of totals and locations.</=
div><div>

=C2=A0 references[reference] ||=3D { :total =3D&gt; 0, :locations =3D&gt; {=
} }</div><div>=C2=A0 r =3D references[reference]</div><div><br></div><div>=
=C2=A0 # contribute to the total for this reference.</div><div>=C2=A0 r[:to=
tal] +=3D count.to_i</div>
<div>
<br></div><div>=C2=A0 # if this location hasn&#39;t been seen for this refe=
rence yet,</div><div>=C2=A0 # start it off at zero. could also use Hash.new=
(0) in the</div><div>=C2=A0 # above definition of references[reference]</di=
v><div>=C2=A0 r[:locations][location] ||=3D 0</div>

<div><br></div><div>=C2=A0 # now contribute to the location&#39;s count</di=
v><div>=C2=A0 r[:locations][location] +=3D count.to_i</div><div>end</div><d=
iv><br></div><div># references now looks something like:</div><div>#</div><=
div># {</div>

<div># =C2=A0&quot;Part1&quot; =3D&gt; { :total =C2=A0 =C2=A0 =C2=A0=3D&gt;=
 5,</div><div># =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :locations=
 =3D&gt; { &quot;Here&quot; =3D&gt; 3, &quot;There&quot; =3D&gt; 2 } },</di=
v><div># =C2=A0&quot;Part2&quot; =3D&gt; { :total =C2=A0 =C2=A0 =C2=A0=3D&g=
t; 1,</div>

<div># =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 =C2=A0 :locations =C2=A0=
=3D&gt; { &quot;Here&quot; =3D&gt; 1 } }</div><div># }</div><div><br></div>=
<div style># This corresponds to desired_output, just in a different way.</=
div><div>#</div><div># You can convert this to the original desired array i=
f you wish, by</div>

<div># iterating over references, or you can pick a more appropriate Hash</=
div><div># structure and just use that.</div></div></div></div>

--20cf3079b5ea56f76404d71a5e9f--