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]
];