Velocity Reviews - Computer Hardware Reviews

Velocity Reviews > Newsgroups > Programming > Ruby > Faster CSV,need help with logic

Reply
Thread Tools

Faster CSV,need help with logic

 
 
Nick Barba
Guest
Posts: n/a
 
      06-11-2009
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...
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
 
 
 
snex
Guest
Posts: n/a
 
      06-11-2009
On Jun 11, 12:40*pm, Nick Barba <(E-Mail Removed)> wrote:
> 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...
> --
> 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.
 
Reply With Quote
 
 
 
 
James Gray
Guest
Posts: n/a
 
      06-11-2009
On Jun 11, 2009, at 12:40 PM, Nick Barba 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 Gray II

 
Reply With Quote
 
Joe Smoe
Guest
Posts: n/a
 
      06-11-2009
Wow that was it exactly! Thanks for the FasterCSV gem too James!
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
Mmcolli00 Mom
Guest
Posts: n/a
 
      11-03-2009
>
> 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"
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
James Edward Gray II
Guest
Posts: n/a
 
      11-03-2009
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 Gray II


 
Reply With Quote
 
Mmcolli00 Mom
Guest
Posts: n/a
 
      11-03-2009

>
> 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
--
Posted via http://www.ruby-forum.com/.

 
Reply With Quote
 
 
 
Reply

Thread Tools

Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts

BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off


Similar Threads
Thread Thread Starter Forum Replies Last Post
Python Logic Map/Logic Flow Chart. (Example Provided) spike Python 8 02-09-2010 12:31 PM
Asynchronous Logic Gates and Analog Logic Gates Jyoti Ballabh Software 3 11-26-2009 06:48 PM
I can not figure this vhdl logic out, help. logia VHDL 6 01-16-2006 03:03 PM
Help validating in business logic layer Steve ASP .Net 0 09-16-2005 11:19 AM
G5 Dual 1.8ghz or Single G5 Help and release of faster G5 Help L.S Digital Photography 16 01-25-2004 02:20 AM



Advertisments