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[15], nPoints]; valueA = RandomReal[{1, 5}, nPoints]; listA = Partition[Riffle[valueA, timeA], 2]; listA // MatrixForm timeB = RandomSample[Range[15], nPoints]; valueB = RandomReal[{1, 5}, nPoints]; listB = Partition[Riffle[valueB, timeB], 2]; listB // MatrixForm timeC = RandomSample[Range[15], 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. Please advise. Many thanks