# Faster CSV,need help with logic

**URL:** <https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055>\
**Category:** Ruby\
**Created:** [June 11, 2009, 7:40pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055 "2009-06-11T19:40:08Z")\
**Posts on this page:** 7\
**Page:** 1

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [June 11, 2009, 7:40pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/1 "2009-06-11T19:40:08Z")

</div>

I’m having a tough time figuring out how to go about solving a specific  
problem.

I have a csv file that looks like this:

Name,Estimated Hours,Actual Hours,Date  
Black, 30, 10, 2009-03-31  
Black,30,10,2009-04-30  
Casey,200,200,2009-04-30  
Clothier,80,40,2009-04-30  
Avino,100,100,2009-05-31  
Black,30,5,2009-05-31  
Clothier,80,50,2009-05-31

I need to figure out how to consolidate the rows so that there is just  
one row per name, adding any actual hours together, and leaving the  
latest date.

So that would become:

Name,Estimated Hours,Actual Hours,Date  
Casey,200,200,2009-04-30  
Avino,100,100,2009-05-31  
Black,30,25,2009-05-31  
Clothier,80,90,2009-05-31

Anyone have any ideas? I was thinking I would need to start by looking  
at the first line, then scan the rest of the file for rows that have  
matching names and store all of those. Then write a csv file with just  
that new combined line, and then delete all rows with those names. Then  
move onto the next name and do the same thing. Problem is I can’t seem  
to figure out how to code it…

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [June 11, 2009, 7:46pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/2 "2009-06-11T19:46:06Z")

</div>

On Jun 11, 12:40 pm, Nick B. [removed\_email\_address@domain.invalid](mailto:removed_email_address@domain.invalid) wrote:

> ## Avino,100,100,2009-05-31 Casey,200,200,2009-04-30
> 
> Posted viahttp://www.ruby-forum.com/.

store everything in a hash table with the name as the key. if the key  
exists, add the hours and replace the date if necessary, otherwise  
insert it with the data given.

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [June 11, 2009, 9:17pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/3 "2009-06-11T21:17:48Z")

</div>

On Jun 11, 2009, at 12:40 PM, Nick B. wrote:

> I’m having a tough time figuring out how to go about solving a  
> specific  
> problem.

How about something like this?

#!/usr/bin/env ruby -wKU

require “rubygems”  
require “faster\_csv”

data = FCSV.parse( DATA.read, :headers =\> true,  
:header\_converters =\> :symbol,  
:return\_headers =\> true )  
FCSV { |csv| csv \<\< data[0].fields }  
data[:name].uniq.each do |name|  
next if name == “Name”  
matches = data.select { |row| row[:name] == name }  
FCSV { |csv| csv \<\< [ name,  
matches.first[:estimated\_hours],  
matches.map { |row| row[:actual\_hours] }.  
inject(0) { |sum, n| sum + n.to\_i },  
matches.map { |row|  
row[:date] }.sort.reverse.first ] }  
end

**END**  
Name,Estimated Hours,Actual Hours,Date  
Black, 30, 10, 2009-03-31  
Black,30,10,2009-04-30  
Casey,200,200,2009-04-30  
Clothier,80,40,2009-04-30  
Avino,100,100,2009-05-31  
Black,30,5,2009-05-31  
Clothier,80,50,2009-05-31

Hope that helps.

James Edward G. II

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [June 11, 2009, 9:53pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/4 "2009-06-11T21:53:02Z")

</div>

Wow that was it exactly! Thanks for the FasterCSV gem too James!

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [November 3, 2009, 11:26pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/5 "2009-11-03T23:26:12Z")

</div>

> data = FCSV.parse( DATA.read, :headers =\> true,  
> :header\_converters =\> :symbol,  
> :return\_headers =\> true )

I really like the logic in this code. It makes good but when I try this  
on my CSV file I am getting no results. Do you know where DATA in the  
above code comes from? I have tried to set DATA with the path to where  
my file is located but it says that I have an uninitialized constant  
DATA (Name error).

DATA = FasterCSV.read(“C:/myCSV.CSV”) #when I tried this I removed the  
.read from the FCSV(parse(DATA.read

DATA = “C:/myCSV.CSV”

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [November 3, 2009, 11:38pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/6 "2009-11-03T23:38:53Z")

</div>

On Nov 3, 2009, at 4:26 PM, Mmcolli00 Mom wrote:

> > data = FCSV.parse( DATA.read, :headers =\> true,  
> > :header\_converters =\> :symbol,  
> > :return\_headers =\> true )
> 
> I really like the logic in this code. It makes good but when I try  
> this  
> on my CSV file I am getting no results. Do you know where DATA in the  
> above code comes from?

DATA is a special Ruby shortcut for easy scripting. Inside a Ruby  
source file you can start a line with the magic **END** tag to end the  
code and start the data section. Ruby will open an IO object, point  
it at the data you put below **END** , and stick that object in the  
constant DATA for easy access.

I bet you can go back and read the email where I used it and it will  
make more sense now. See how I just dumped the CSV under my **END**  
tag? That’s what the code was reading.

You could replace DATA.read in your own code with File.read(“path/to/  
file.csv”). Or you could just put the path where I stuck DATA.read,  
but call FCSV.read() instead of FCSV.parse().

I hope that helps.

James Edward G. II

---

<div class="post-metadata">

**Author:** ![jsimone](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/j/2bfe46/32.png) [@jsimone](https://www.ruby-forum.com/u/jsimone)\
**Post date:** [November 3, 2009, 11:49pm UTC](https://www.ruby-forum.com/t/faster-csv-need-help-with-logic/170055/7 "2009-11-03T23:49:48Z")

</div>

> DATA is a special Ruby shortcut for easy scripting. Inside a Ruby  
> source file you can start a line with the magic **END** tag to end the  
> code and start the data section. Ruby will open an IO object, point  
> it at the data you put below **END** , and stick that object in the  
> constant DATA for easy access.
> 
> I bet you can go back and read the email where I used it and it will  
> make more sense now. See how I just dumped the CSV under my **END**  
> tag? That’s what the code was reading.

Very cool!!! I like this! Thanks so much for explaining James!

-MC