# Reading a column formatted data

**URL:** <https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085>\
**Category:** Ruby\
**Created:** [November 13, 2007, 4:55pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085 "2007-11-13T16:55:27Z")\
**Posts on this page:** 6\
**Page:** 1

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 13, 2007, 4:55pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/1 "2007-11-13T16:55:27Z")

</div>

Hi

I am quite fresh to ruby. I want to read a column formatted data like  
the one below:

Full name Q01 Q02 Q03  
Anonymous1 Fair Fair Fair  
Anonymous2 Excellent Fair Poor  
Anonymous3 Fair Excellent Fair  
Anonymous1 Poor Poor Fair  
Anonymous2 Poor Fair Fair  
Anonymous1 Fair Fair Fair  
Anonymous3 Excellent Excellent Excellent

Depending on the column number, these strings (Fair, Excellent, etc)  
must be given a different weight, multiplied by the number of these  
strings occurrences in a column and an index is to be calculated.

I was using excel and the builtin function “countif” of excel to this.  
But I want to automate the whole process by using a program

what is the best approach in ruby?

Thank you  
suresh

---

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 13, 2007, 5:21pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/2 "2007-11-13T17:21:22Z")

</div>

-----BEGIN PGP SIGNED MESSAGE-----  
Hash: SHA1

Hi.

> Depending on the column number, these strings (Fair, Excellent, etc)  
> must be given a different weight, multiplied by the number of these  
> strings occurrences in a column and an index is to be calculated.  
> what is the best approach in ruby?

Don’t know if it’s the best but:

weight = {0=\>1,1=\>2,2=\>3}  
scale = {“Poor”=\>1,“Fair”=\>2,“Excellent”=\>3}

File.open(“suresh.txt”).each do |line|  
value, (name, \*values) = 0, line.split  
values.each\_with\_index {|item,index| value +=  
weight[index]\*scale[item]}  
puts “[#{name}] [#{values.join(‘,’)}] [#{value}]”  
end

> suresh

Wow! Dr. Suresh from Heroes??? 🙂

* * *

Eustáquio “TaQ” Rangel  
[http://eustaquiorangel.com](http://eustaquiorangel.com)

“Premature optimisation is the root of all evil.”  
Donald Knuth  
-----BEGIN PGP SIGNATURE-----  
Version: GnuPG v1.4.2.2 (GNU/Linux)

iD8DBQFHOc7Xb6UiZnhJiLsRAmDeAJ9gwMYrgsLzf98j3TIQOl09TpsBTgCgq7OI  
RsFHTwjXRUK2oeTiUVUwbEk=  
=/Vt2  
-----END PGP SIGNATURE-----

---

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 13, 2007, 8:11pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/3 "2007-11-13T20:11:43Z")

</div>

suresh wrote:

> multiplied by the number of these  
> strings occurrences in a column

The previous solution doesn’t account for that requirement, and the  
solution won’t work on a file with column headings. Here is a way to  
tabulate your data, so that you can calculate your index:

require ‘rubygems’  
require ‘fastercsv’

totals = {‘Poor’ =\>[0,0,0], ‘Fair’=\>[0,0,0], ‘Excellent’=\>[0,0,0]}  
cols = totals.length

FasterCSV.foreach(‘data.txt’, :headers =\>true) do |row|  
cols.times do |i|  
totals[row[i+1]][i] += 1  
end  
end

p totals

–output:–  
{“Excellent”=\>[2, 2, 1], “Poor”=\>[2, 1, 1], “Fair”=\>[3, 4, 5]}

---

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 13, 2007, 8:28pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/4 "2007-11-13T20:28:52Z")

</div>

7stud – wrote:

> FasterCSV.foreach(‘data.txt’, :headers =\>true) do |row|

I forgot to add that if your file doesn’t use commas as the separator  
between columns, you have to specify the separator, which in your case  
looks like it’s a tab(\t):

FasterCSV.foreach(‘data.txt’, :headers =\>true, :col\_sep =\>"\t")

---

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 13, 2007, 8:56pm UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/5 "2007-11-13T20:56:29Z")

</div>

7stud – wrote:

> require ‘rubygems’  
> require ‘fastercsv’
> 
> totals = {‘Poor’ =\>[0,0,0], ‘Fair’=\>[0,0,0], ‘Excellent’=\>[0,0,0]}  
> cols = totals.length
> 
> FasterCSV.foreach(‘data.txt’, :headers =\>true) do |row|  
> cols.times do |i|  
> totals[row[i+1]][i] += 1  
> end  
> end
> 
> p totals
> 
> –output:–  
> {“Excellent”=\>[2, 2, 1], “Poor”=\>[2, 1, 1], “Fair”=\>[3, 4, 5]}

This is more readable:

require ‘rubygems’  
require ‘fastercsv’

totals = {‘Poor’ =\>[0,0,0], ‘Fair’=\>[0,0,0], ‘Excellent’=\>[0,0,0]}  
target\_cols = [‘Q01’, ‘Q02’, ‘Q03’]

FasterCSV.foreach(‘data.txt’, :headers =\>true, :col\_sep =\>" ") do |row|  
target\_cols.each\_with\_index do |col\_name, i|  
rating = row[col\_name]  
totals[rating][i] += 1  
end  
end

p totals

–output:–  
{“Excellent”=\>[2, 2, 1], “Poor”=\>[2, 1, 1], “Fair”=\>[3, 4, 5]}

---

<div class="post-metadata">

**Author:** ![suresh](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/aca169/32.png) [@suresh](https://www.ruby-forum.com/u/suresh)\
**Post date:** [November 15, 2007, 5:50am UTC](https://www.ruby-forum.com/t/reading-a-column-formatted-data/120085/6 "2007-11-15T05:50:31Z")

</div>

hi,

thanks for the wonderful ideas,

suresh  
India