Re: Sorting paired columns of dates and values
- To: mathgroup at smc.vnet.net
- Subject: [mg106710] Re: Sorting paired columns of dates and values
- From: Bill Rowe <readnews at sbcglobal.net>
- Date: Thu, 21 Jan 2010 04:56:33 -0500 (EST)
On 1/20/10 at 6:49 AM, warsaw95826 at mypacks.net (Garapata) 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.
Here is a possible solution. It isn't totally clear to me
precisely how you want the output formated. So, the code below
may well need to be modified.
=46irst, changing your data so that the dates are strings. If this
is not done, the expression 1/3/1984 well get evaluated to
1/5952, something no longer clearly a date.
In[12]:= 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}};
In[13]:= Reverse@
SortBy[Partition[Flatten@data, 2],
AbsoluteTime[{First@#, {"MonthShort", "/", "DayShort", "/",
"Year"}}] &]
Out[13]= {{"1/9/1984", 1034.6}, {"1/6/1984", 1029},
{"1/5/1984", 1015.8}, {"1/4/1984", 998.6},
{"1/3/1984", 997.5}, {"1/9/1970", 2359},
{"1/8/1970", 2283.68}, {"1/7/1970", 2394.96},
{"1/6/1970", 2406.22}, {"1/5/1970", 2402.85},
{"1/6/1961", 526.4}, {"1/5/1961", 531.2},
{"1/4/1961", 527.4}, {"1/3/1961", 527.2},
{"1/2/1961", 536.3}}