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

*To*: mathgroup at smc.vnet.net*Subject*: [mg111282] Re: Need to align data from sublists to Union of dates*From*: Raffy <adraffy at gmail.com>*Date*: Mon, 26 Jul 2010 06:37:25 -0400 (EDT)*References*: <i2gl8s$11b$1@smc.vnet.net>

On Jul 24, 11:23 pm, Robert McHugh <r62... at gmail.com> wrote: > 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 ClearAll[mush]; mush[m_, fill_: 1] := With[ {vDates = Union @@ m[[All, All, 2]]}, Transpose@ Prepend[Replace[vDates, Append[Rule[#2, #1] & @@@ #, _ -> fill], {1}] & /@ m, vDates] ];