Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

I'm trying to create a stacked bar chart using raw data coming from a MySQL server. The result coming back from one query is:

{{SQLDateTime[{2011, 7, 23, 13, 0, 0.}], 74}, 
 {SQLDateTime[{2011, 8, 11, 15, 35, 54.}], 1}, 
 {SQLDateTime[{2011, 8, 18, 9, 28, 14.}], 49}, 
 {SQLDateTime[{2011, 8, 24, 21, 45, 29.}], 1}, 
 {SQLDateTime[{2011, 8, 31, 10, 0, 18.}], 1}, 
 {SQLDateTime[{2011, 9, 8, 10, 6, 45.}], 5}, 
 {SQLDateTime[{2011, 9, 14, 9, 35, 40.}], 10}, 
 {SQLDateTime[{2011, 9, 15, 6, 27, 12.}], 1}, 
 {SQLDateTime[{2011, 9, 21, 9, 47, 26.}], 6}, 
 {SQLDateTime[{2011, 9, 29, 10, 5, 2.}], 3}, 
 {SQLDateTime[{2011, 10, 6, 9, 34, 26.}], 3}, 
 {SQLDateTime[{2011, 10, 24, 9, 31, 42.}], 1}, 
 {SQLDateTime[{2011, 10, 27, 9, 52, 7.}], 2}, 
 {SQLDateTime[{2011, 11, 25, 10, 53, 27.}], 2}, 
 {SQLDateTime[{2011, 12, 1, 11, 0, 21.}], 3}, 
 {SQLDateTime[{2011, 12, 6, 10, 49, 42.}], 3}, 
 {SQLDateTime[{2011, 12, 8, 14, 38, 54.}], 1}, 
 {SQLDateTime[{2012, 3, 27, 8, 10, 31.}], 1}}

I'm going to get ten similar sets of results. The difference will be that they don't contain all of the same dates. I need to create a matrix like so:

The first row is a list of dates. Each result set will contain different dates and some will be the same and some not, think of this first row as a list of unique dates pooled together from all result sets. Should be ordered chronologically.

Row number two lists the correponding values in each column. Look at my example result set above. Each date has a value corresponding to it. All of these values make up a row, with each element under its corresponding date. There will be dates in the first row for which there are no corresponding values in this particular result set, these elements should be zero.

There will be one row for each result set plus the top row consisting of dates.

How can I do this?

share|improve this question
    
Are the dates returned in each query guaranteed to be unique? If not, how would you handle multiple values for the same date? –  rm -rf Apr 10 '12 at 19:48
    
I rewrote my question, hopefully it is now more understandable. The dates are unique in each result set. –  Pickett Apr 10 '12 at 19:58

3 Answers 3

up vote 7 down vote accepted

I don't have a free kernel to try your question on, so here's a smaller generalized approach that I could construct in my head. In the end, I'll mention how you can adapt it to yours.

First, consider a non-rectangular list similar to yours:

list = {{{1, a}, {2, b}}, 
        {{3, a}, {2, c}, {4, f}}, 
        {{3, b}, {6, f}, {4, c}, {5, e}}};

The output expected here is a 3x6 matrix with each column corresponding to the unique elements and each row having the corresponding element or zero. Obtaining the unique elements and constructing a larger matrix, as the following, we get:

With[{un = Union@Flatten@list[[All, All, 1]]},
    (un /. Rule@@@#&)/@list /. _Integer -> 0
]

(* Out[1] = {{a, b, 0, 0, 0, 0}, 
             {0, c, a, f, 0, 0}, 
             {0, 0, b, c, e, f}} 
*)

which, I believe, is what you want. If you want the first row to be the list of unique elements, simply join un with the rest as:

With[{un = Union@Flatten@list[[All, All, 1]]},
    {un} ~Join~ ((un /. Rule@@@#&)/@list /. _Integer -> 0)
]

(* Out[2]= {{1, 2, 3, 4, 5, 6}, 
            {a, b, 0, 0, 0, 0}, 
            {0, c, a, f, 0, 0}, 
            {0, 0, b, c, e, f}} 
*)

Now the only change that's required for this to work on your data is to use _SQLDateTime instead of _Integer in the With construct above.

share|improve this answer
    
Please confirm if this works and gives you the result. Also, list above is the equivalent of the collected output from your 10 different queries. –  rm -rf Apr 10 '12 at 20:22
    
This works great. Thank you. –  Pickett Apr 11 '12 at 19:37

This is based on SparseArray, like rcollyer's answer, but it's a one-liner since everything can by done by Applying Rule at the proper level and then using PadRight to turn a ragged array into a rectangular one. Let

list = {{{1, a}, {2, b}}, 
        {{3, a}, {2, c}, {4, f}}, 
        {{3, b}, {6, f}, {4, c}, {5, e}}};

Then:

PadRight[Normal /@ SparseArray /@ (Apply[Rule, list, {2}])]
{{a, b, 0, 0, 0, 0}, 
 {0, c, a, f, 0, 0}, 
 {0, 0, b, c, e, f}}

EDIT to add: Of course, the real trick is getting the first row right, like @rcollyer points out in the comments below. It's possible to extend this solution in a manner similar to the one in R.M.'s answer, but I'm not sure how much that wins you:

Pillsy`Rectangularize[lists : {___List}] := 
 With[{columns = Union@Cases[lists, {n_, _} :> n, {2}]},
  Prepend[
   PadRight[Normal /@ SparseArray /@
      (lists /. 
        MapThread[{#1, v_} :> (#2 -> v) &, {columns, 
           Range@Length@columns}])],
   columns]]

I tested the function with the following:

namedList = list /. Thread[Range[6] -> CharacterRange["A", "F"]]
{{{"A", a}, {"B", b}}, 
 {{"C", a}, {"B", c}, {"D", f}}, 
 {{"C", b}, {"F", f}, {"D", c}, {"E", e}}}
Pillsy`Rectangularize[namedList] 
{{"A", "B", "C", "D", "E", "F"}, 
 {a, b, 0, 0, 0, 0}, 
 {0, c, a, f, 0, 0}, 
 {0, 0, b, c, e, f}}

I did come up with a totally different solution based on Ordering and DeleteDuplicates which I kind of like:

Pillsy`AnotherRectangularize[lists_] :=
 With[{columns = Union @@ lists[[All, All, 1]]},
  Prepend[
   Part[PadRight[#[[All, -1]], Length@columns],
      Ordering@DeleteDuplicates[#[[All, 1]]~Join~columns]] & /@ lists,
   columns]]
share|improve this answer
    
Where's the first row which for the OP's dataset would be the dates? –  rcollyer Apr 11 '12 at 15:41
    
I love how getting the first row correct adds so much additional complexity to the solution. Also, Normal can be applied using postfix notation as it will automatically "normalize" to all depths. +1, btw. –  rcollyer Apr 12 '12 at 0:29
    
Come to think of it, the first row can be extracted and properly ordered using Union@Flatten@list[[All, All, 1]] which then can be prepended or joined to the matrix. –  rcollyer Apr 12 '12 at 0:33

Here is an alternative method based upon Reap and Sow.

Reap[
 MapIndexed[
  Sow[#2[[1]] -> #1[[2]], First@#1] &, 
  list, {2}  (* <-- Note level specifier *)
 ], _, 
 Flatten@{#1, Normal@SparseArray[#2, Length@list]} &][[2]] // 
SortBy[#, First] & // Transpose

If you are not familiar with these two functions, Sow tags each item you indicate and Reap organizes the items by each tag. Among the advantages of Reap and Sow over GatherBy is the ability to simultaneously transform each item while tagging it with one of its original properties. Here I take advantage of this by using MapIndexed to tag each datum with its first element (second parameter in Sow) while associating the second element of the datum with the index of its data set. (This is done by using the {2} as the level specifier.) For example,

MapIndexed[#2[[1]]->#1[[2]], list, {2}]

with

list = {
        {{1, a}, {2, b}}, 
        {{3, a}, {2, c}, {4, f}}, 
        {{3, b}, {6, f}, {4, c}, {5, e}}
       };

gives

{{1 -> a, 1 -> b}, 
 {2 -> a, 2 -> c, 2 -> f}, 
 {3 -> b, 3 -> f, 3 -> c, 3 -> e}}

By creating the association with the data set index, we are marking which row it will end up in after Reap collects them. Reap allows us to specify an additional transformation taking 2 parameters the tag and a list of items with that tag as its third argument. For example,

Reap[MapIndexed[Sow[#2[[1]] -> #1[[-1]], First@#1] &, list, {2}], 
 _, Rule][[2]]

using list from above gives

{1 -> {1 -> a}, 
 2 -> {1 -> b, 2 -> c}, 
 3 -> {2 -> a, 3 -> b}, 
 4 -> {2 -> f, 3 -> c}, 
 6 -> {3 -> f}, 5 -> {3 -> e}}

As you can see, this has collected the data according to the first element of each datum while retaining information about which dataset it belongs to.

Since the tagged data has the form, {_Integer -> value ..}, we can use SparseArray to generate each row while automatically filling in the empty spaces with 0. This is what I do with

Flatten@{#1, Normal@SparseArray[#2, Length@list]} &

as the third parameter.

Then, I just SortBy the First element of each sub-list, and Transpose it into its final form

{{1, 2, 3, 4, 5, 6}, 
 {a, b, 0, 0, 0, 0}, 
 {0, c, a, f, 0, 0}, 
 {0, 0, b, c, e, f}}

As to the specific dataset involving SQLDateTime, it appears that SQLDateTime contains a full date-time stamp. It is unlikely that any two datum will have the same date-time stamp, so if you want to gather within a full day only, change

Sow[#2[[1]] -> #1[[2]], First@#1] &

to

Sow[#2[[1]] -> #1[[2]], { #1[[1, ;;3]] } ] &

where #1[[1, ;;3]] only extracts what appears to be the date part of SQLDateTime. This could be extended to include hours or even minutes by changing ;;3 to ;;4 or ;;5, respectively. Note, I wrapped the tag in an extra set of braces so that the entire date is considered a tag, and not a collection of individual tags.

share|improve this answer
    
Thank you for the thorough explanation. And you were right about only wanting to compare full days :) –  Pickett Apr 11 '12 at 20:02

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.