Transform data from one column to multiple columns in Ruby

Question

I have two columns of data in csv format as shown below from a prediction server.The first column is an index position for each variable for each prediction. Therefore new data starts at index 1.

1,2.0
2,1.5
3,1.4
1,1.1
2,2.0
3,1.5
4,2.0
5,1.6
1,2.0
2,4.0

.
.
.

I would like to have the data in this format instead,

2.0,1.1,2.0
1.5,2.0,4.0
1.4,1.5
    2.0
    1.6

For ease of work, The empty 'cells' can be filled with zeros or # e.g

2.0,1.1,2.0
1.5,2.0,4.0
1.4,1.5,0
0,  2.0,0
0,  1.6,0

Someone with an elegant way to do this in Ruby?

Answer 1

Let's try to transpose it with Array#transpose:

# first get a 2d representation of the data
rows = CSV.read(fn).slice_before{|row| "1" == row[0]}.map{|x| x.map{|y| y[1]}}

# we want to transpose the array but first we have to fill empty cells
max_length = rows.max_by{|x| x.length}.length
rows.each{|row| row.fill '#', row.length..max_length}

# now we can transpose the array
pp rows.transpose

["2.0", "1.1", "2.0", "5.0"],
["1.5", "2.0", "4.0", "#"],
["1.4",  "1.5", "#", "#"],
["#", "2.0", "#", "#"],
["#", "1.6", "#", "#"], 
["#", "#", "#", "#"]

Answer 2

This should work for you:

require 'csv'

# read csv contents from file to array
rows = CSV.read("path/to/in_file.csv")

res = Hash.new {|h,k| h[k] = []}
rows.each do |(key, val)|
  res[key] << val
end

# write to output csv file
CSV.open("path/to/out_file.csv", "wb") do |csv|
  # sort res hash by keys, map to have array of values and add to csv
  res.sort_by{|k, v| k}.map{|k, v| v}.each do |r|
    csv << r
  end
end