       Re: Need to align data from sublists to Union of dates

• To: mathgroup at smc.vnet.net
• Subject: [mg111264] Re: Need to align data from sublists to Union of dates
• From: Robert McHugh <r62m10 at gmail.com>
• Date: Sun, 25 Jul 2010 02:23:17 -0400 (EDT)

```Below are some thoughts about how to address this questions.
After finding the union of all times (which you did in the question),
then find the position of those times in the sublists.
then lookup the values corresponding to those positions.
Finally, combine the results.

(*This simulates the data format your described,with 3 data sets,each \
dataset containing a real value and a time value.Times are \
represented as an integer.*)

nPoints = 7;

timeA = RandomSample[Range, nPoints];
valueA = RandomReal[{1, 5}, nPoints];
listA = Partition[Riffle[valueA, timeA], 2];
listA // MatrixForm

timeB = RandomSample[Range, nPoints];
valueB = RandomReal[{1, 5}, nPoints];
listB = Partition[Riffle[valueB, timeB], 2];
listB // MatrixForm

timeC = RandomSample[Range, nPoints];
valueC = RandomReal[{1, 5}, nPoints];
listC = Partition[Riffle[valueC, timeC], 2];
listC // MatrixForm

myList = {listA, listB, listC};

(*============================================================*)
(*Below processes the data as described.*)
(*This is the union of all times found in the above data set*)

allTimes = Union @@ myList[[All, All, 2]];
nTimes = Length[allTimes];

(*Find the times for each list*)

myTimeA = myList[[1, All, 2]];
myTimeB = myList[[2, All, 2]];
myTimeC = myList[[3, All, 2]];

(*Find the position of the times for each data set*)

myPosA = Position[myTimeA, #] & /@ allTimes;
myPosB = Position[myTimeB, #] & /@ allTimes;
myPosC = Position[myTimeC, #] & /@ allTimes;

(*For each data set,get the value corresponding to the postion found \
above *)
(*or report-1 if time is not present in the dataset for each time*)
(*or report-2 if multiple values were found in the dataset for a \
single time*)

myValsA = myList[[1, All, 1]];
partA = If[Length@# == 1, Part[myValsA, #[[1, 1]]],
If[Length@# == 0, -1, -2]] & /@ myPosA;

myValsB = myList[[2, All, 1]];
partB = If[Length@# == 1, Part[myValsB, #[[1, 1]]],
If[Length@# == 0, -1, -2]] & /@ myPosB;

myValsC = myList[[3, All, 1]];
partC = If[Length@# == 1, Part[myValsB, #[[1, 1]]],
If[Length@# == 0, -1, -2]] & /@ myPosC;

(*Combine the dataSets*)
result = Partition[Join[allTimes, partA, partB, partC], nTimes] //
Transpose;
result // TableForm

Best Regards,
Robert McHugh

========================================================

Hi everyone,

A simple example of my problem follows.

I have a nested list, myList, with the following dimensions:

Dimensions[#] & /@ myList

{{93, 2}, {93, 2}, {93, 2}, {88, 2}, {92, 2}, {93, 2}, {93, 2}}

The columns of each sublist contain values and SQLDateTime(s)
respectively, i.e:

myList[[1,1]
=E2=80=A8      {1.01055, SQLDateTime[{2010, 7, 9, 10, 30, 0.}]

I've sorted each sublist by SQLDateTime.

The Union of the date columns across all sub lists give me 120
distinct dates.

myUnion = Union @@ (myList[[All, All, 2]]);
Length[myUnion]

120

That's what I have so far.

Now I need to do 2 things:

First, create a 2 dimensional list with dimensions {120, 7} that would
have myUnion as the first column and the seven columns of values from
the sublists in myList aligned with their respective dates in
myUnion.  This will leave me with some blanks throughout the matrix
which I need to fill in with the value 1.

Just not sure how to do it.