# Tricky data pivot problem

**URL:** <https://www.ruby-forum.com/t/tricky-data-pivot-problem/138454>\
**Category:** Rails\
**Created:** [May 23, 2008, 7:28am UTC](https://www.ruby-forum.com/t/tricky-data-pivot-problem/138454 "2008-05-23T07:28:18Z")\
**Posts on this page:** 3\
**Page:** 1

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [May 23, 2008, 7:28am UTC](https://www.ruby-forum.com/t/tricky-data-pivot-problem/138454/1 "2008-05-23T07:28:18Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [May 23, 2008, 7:30am UTC](https://www.ruby-forum.com/t/tricky-data-pivot-problem/138454/2 "2008-05-23T07:30:58Z")

</div>

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

---

<div class="post-metadata">

**Author:** ![steveoco](https://www.ruby-forum.com/letter_avatar_proxy/v4/letter/s/db5fbb/32.png) [@steveoco](https://www.ruby-forum.com/u/steveoco)\
**Post date:** [May 23, 2008, 1:46pm UTC](https://www.ruby-forum.com/t/tricky-data-pivot-problem/138454/3 "2008-05-23T13:46:52Z")

</div>

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