Tricky data pivot problem

Rails
1

So I’ve come at this a couple of ways, but all of my solutions look
awful, and I’m betting there’s a (more) elegant way to do this. I’m sure
there’s an array jocky who knows how to do this elegantly. Notice that
there are a couple of data points missing in the list of times (john was
out tues and mark monday, slackers…).

I’m using MySQL and Ruby 1.8.6

any help appreciated,

-s

I’m trying to turn this:

Name Day Time
john mon 8
john wed 7
john thu 9
john fri 7
susan mon 8
susan tue 8
susan wed 9
susan thu 7
susan fri 7
mark tue 6
mark wed 5
mark thu 6
mark fri 5

Into this:

Job #6
mon tue wed thu fri sat sun total
john 8 - 7 8.5 7 - - 30.5
susan 8 8 8.5 7 7 - - 38.5
mark - 6 5 6 5 - - 22

2

here’s an excel file with the data (before and after) in it if that’s
useful…

3

On Fri, May 23, 2008 at 07:28:18AM +0200, Steve Orme wrote:

-s

I’m trying to turn this:

Name Day Time
john mon 8
[…]
mark fri 5

I’m going to assume that you have these as ActiveRecord objects with
name,
day, and time fields and you’ve already loaded the relevant ones into an
array called hours.

Into this:

Job #6
mon tue wed thu fri sat sun total
john 8 - 7 8.5 7 - - 30.5
susan 8 8 8.5 7 7 - - 38.5
mark - 6 5 6 5 - - 22

DayMap = {
‘mon’ => 0,
‘tue’ => 1,
‘wed’ => 2,
‘thu’ => 3,
‘fri’ => 4,
‘sat’ => 5,
‘sun’ => 6
}
worker_hours = hours.inject({}) { |h,entry|
(h[entry.name] ||= [0]*7)[DayMap[entry.day]] = entry.time; h
}.map { |name,times|
sum = times.inject { |s,n| s+n }
[ name, times.map { |v| v.zero? ? ‘-’ : v }, sum ].flatten
}

–Greg