Re: Sorting paired columns of dates and values
- To: mathgroup at smc.vnet.net
- Subject: [mg106696] Re: Sorting paired columns of dates and values
- From: "Norbert P." <bertapozar at gmail.com>
- Date: Thu, 21 Jan 2010 04:53:54 -0500 (EST)
- References: <hj6qj5$8vc$1@smc.vnet.net>
Hi,
This will do what you need:
Your data:
In[1]:= 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}};
Now you can use:
In[2]:= tonumbers[s_String]:=ToExpression/@StringSplit[s,"/"]
[[{3,1,2}]];
Reverse[#[[Ordering[tonumbers/@#]]]]&/@(Transpose@Partition[data,
{Length[data],2}])[[All,1]]
Join@@@Transpose[%]
Out[3]= {{{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}}}
Out[4]= {{1/9/1984,1034.6,1/9/1970,2359,1/6/1961,526.4},
{1/6/1984,1029,1/8/1970,2283.68,1/5/1961,531.2},
{1/5/1984,1015.8,1/7/1970,2394.96,1/4/1961,527.4},
{1/4/1984,998.6,1/6/1970,2406.22,1/3/1961,527.2},
{1/3/1984,997.5,1/5/1970,2402.85,1/2/1961,536.3}}
It first splits the data into columns, sort each column in the
descending order and join the columns back together.
Of course, if you just need to consolidate the data, i.e. collect all
values for one date together with the date, no sorting (you can sort
afterwards, it's simple), this is much more straightforward:
In[5]:= Reap[Sow@@@Reverse[Flatten[Partition[data,{Length[data],2}],2],
2],_,{#1,Sequence@@#2}&][[2]]
Out[5]= {{1/3/1984,997.5},{1/4/1984,998.6},{1/5/1984,1015.8},
{1/6/1984,1029},{1/9/1984,1034.6},{1/5/1970,2402.85},
{1/6/1970,2406.22},{1/7/1970,2394.96},{1/8/1970,2283.68},
{1/9/1970,2359},{1/2/1961,536.3},{1/3/1961,527.2},{1/4/1961,527.4},
{1/5/1961,531.2},{1/6/1961,526.4}}
Best,
Norbert
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.