Re: Sorting paired columns of dates and values
- To: mathgroup at smc.vnet.net
- Subject: [mg106702] Re: Sorting paired columns of dates and values
- From: Raffy <raffy at mac.com>
- Date: Thu, 21 Jan 2010 04:55:03 -0500 (EST)
- References: <hj6qj5$8vc$1@smc.vnet.net>
On Jan 20, 3:48 am, Garapata <warsaw95... at mypacks.net> wrote:
> I've imported a large flat file of dates and values in a structure
> like this:
>
> data = {{1/3/1984,997.5,1/5/1970,2402.85,1/2/1961,536.3},
> {1/4/1984,998.6,1/6/1970,2406.22,1/3/1961,527.2},
> {1/5/1984,1015.8,1/7/1970,2394.96,1/4/1961,527.4},
> {1/6/1984,1029,1/8/1970,2283.68,1/5/1961,531.2},
> {1/9/1984,1034.6,1/9/1970,2359,1/6/1961,526.4}...}
>
> The above example has 3 columns of dates. Each date column has a
> matching column of values immediately to its right.
>
> As seen above, each of the date columns starts with a different date.
>
> Not seen, each date and value paired column has a final value on the
> same date: 12/31/2009.
>
> I want to sort each of the date and value paired columns in a
> descending order by its date column, so that my first row of data
> would all have the 12/31/2009 date. This should also line up all the
> subsequent rows by descending dates, which would allow me to drop all
> but one of the columns with dates and consolidate the list.
>
> I've looked at Partition, Sort, and SortBy but can't see a direct way
> to do this.
>
> I'll keep trying through the night. Any help much appreciated.
(Given mOld = the data as you described)
mT = Transpose[mOld];
(* Get all the dates available, sort em *)
vDates = SortBy[Union@Flatten[mT[[Range[1, Length[mT], 2]]]], (1-
ToExpression@StringSplit[#, "/"]).{31, 1, 372} &];
(* For each column pair, replace each date string with the value,
otherwise replace with Indeterminate *)
mNew = Transpose@Developer`PartitionMap[vDates /. Append[MapThread
[Rule, #], _String -> Indeterminate] &, mT, 2];
(* Print Results *)
TableForm[mNew, TableHeadings -> {vDates, Automatic}]