How to pivot array into another array in Ruby

Question

I have a multidimensional array like this one :

myArray = [["Alaska","Rain","3"],["Alaska","Snow","4"],["Alabama","Snow","2"],["Alabama","Hail","1"]]

I would like to end up with CSV output like this.

State,Snow,Rain,Hail
Alaska,4,3,nil
Alabama,2,nil,1

I know that to get this outputted to CSV the way I want it I have to have output array like this:

outputArray =[["State","Snow","Rain","Hail"],["Alaska",4,3,nil],["Alabama",2,nil,1]]

but I don't know how to get to this stage. I've tried using group_by but with no success.

Answer 1

Here is a way using an intermediate hash-of-hash

The h ends up looking like this

{"Alaska"=>{"Rain"=>"3", "Snow"=>"4"}, "Alabama"=>{"Snow"=>"2", "Hail"=>"1"}}

---

myArray = [["Alaska","Rain","3"],["Alaska","Snow","4"],["Alabama","Snow","2"],["Alabama","Hail","1"]]
myFields = ["Snow","Rain","Hail"]

h = Hash.new{|h, k| h[k] = {}} 
myArray.each{|i, j, k| h[i][j] = k }
p [["State"] + myFields] + h.map{|k, v| [k] + v.values_at(*myFields)}

---

output

[["State", "Snow", "Rain", "Hail"], ["Alaska", "4", "3", nil], ["Alabama", "2", nil, "1"]]

Answer 2

I suggest you do that as follows:

my_array = [["Alaska" ,"Rain","3"], ["Alaska", "Snow","4"],
            ["Alabama","Snow","2"], ["Alabama","Hail","1"]]

attributes = my_array.transpose[1].uniq
  #=> ["Rain", "Snow", "Hail"]

h = my_array.each_with_object({}) { |a,h| (h[a.first] ||= {})[a[1]] = a[2].to_i }
  #=> {"Alaska" =>{"Rain"=>3, "Snow"=>4},
  #    "Alabama"=>{"Snow"=>2, "Hail"=>1}} 

[["State", *attributes], *h.map { |k,v| [k, *v.values_at(*attributes)] }]
  #=> [["State", "Rain", "Snow", "Hail"],
  #    ["Alaska",    3, 4, nil],
  #    ["Alabama", nil, 2,   1]] 

You can, of course, substitute out h.

Let's look more carefully at the calculation of:

h.map { |k,v| [k, *v.values_at(*attributes)] }]

We have:

enum = h.map
  #=> #<Enumerator: {"Alaska"=>{"Rain"=>3,  "Snow"=>4},
  #                  "Alabama"=>{"Snow"=>2, "Hail"=>1}}:map> 

The first element of the enumerator is passed into the block by Enumerator#each:

k,v = enum.next
  #=> ["Alaska", {"Rain"=>3, "Snow"=>4}] 
k #=> "Alaska" 
v #=> {"Rain"=>3, "Snow"=>4} 
b = v.values_at(*attributes)
  #=> {"Rain"=>3, "Snow"=>4}.values_at(*["Rain", "Snow", "Hail"])
  #=> {"Rain"=>3, "Snow"=>4}.values_at("Rain", "Snow", "Hail")
  #=> [3, 4, nil] 
[k, *b]
  #=> ["Alaska", *[3, 4, nil]]
  #=> ["Alaska", 3, 4, nil]

The second element of enum is passed into the block:

k,v = enum.next
  #=> ["Alabama", {"Snow"=>2, "Hail"=>1}] 
b = v.values_at(*attributes)
  #=> [nil, 2, 1] 
[k, *b]
  #=> ["Alabama", nil, 2, 1] 

Answer 3

I think you may want to create a custom Class for this behavior so that you can wrap the entire feature into an object.

The Class would accept an instance of the input Array, and will return the transformed output ready for serialization.

What you need is:

1. An Array that contains the list of Headers, dynamically populated while you loop the input items.

2. A Hash where the key is the State, the value is a Hash of header/value.

   { "Alabama" => { "Snow" => 2 }}
   

When you initialize the object, @headers is an empty array and @data is an empty Hash.

You loop all the items in the input array, and for each item in the list you append the header to @headers if not there yet (in fact, you can use a Set rather an Array and it will remove the duplicates for you), and you add the item to the @data.

If the state already exists in the country, add the new key/value. If the sate doesn't exist, create the state and add the new key/value. You can easily achieve this goal in one line.

# assuming `state` is the current state in the loop
(@data[state] ||= {}).merge(header => value)

When the loop ends, @header will contain all the items to display. At this point, loop the @data and for each item extract all the values declared in @header. If the value does not exist, use nil.

At the end of this second loop you'll have the data you need to produce the CSV.