Faster CSV,need help with logic

N

Nick Barba

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...
 
S

snex

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...

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.
 
J

James Gray

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
 
M

Mmcolli00 Mom

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"
 
J

James Edward Gray II

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
 
M

Mmcolli00 Mom

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
 

Ask a Question

Want to reply to this thread or ask your own question?

You'll need to choose a username for the site, which only take a couple of moments. After that, you can post your question and our members will help you out.

Ask a Question

Members online

No members online now.

Forum statistics

Threads
473,744
Messages
2,569,484
Members
44,903
Latest member
orderPeak8CBDGummies

Latest Threads

Top