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.