Prev: Using Mathematica to analyze Firefox startup performance
Next: simple nest
From: Garapata on 20 Jan 2010 06:48
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.



From: Norbert P. on 21 Jan 2010 04:53
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(a)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...(a)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.


From: Garapata on 21 Jan 2010 04:54
I've made a bit of progress on this with the following functions:

uniqueDates[data_, year_] := Module[{step1, step2, step3},=E2=80=A8 step1 =
Flatten[Rest[data][[All, Range[1, Dimensions[data][[2]], 2]]]];
step2 = Delete[step1, Position[step1, ""]];=E2=80=A8 step3 = Union[step2];
DeleteCases[step3, _?(ToExpression[StringTake[#, -4]] < year &)]];

This takes my original data as an input and returns a sorted list of
unique dates. I need this (or some functional equivalent) because I
discovered that each of my paired data and value columns may not have
the same set of dates between any two given dates. Some may not have
values for holidays or weekends others may. Also, as I described in
my first post, some of the paired columns start with earlier dates
than others.

I also discovered in the data set that I have "" (empty strings) in
some positions for both dates and data values.

Next I wrote the following:

cleanData[dataIn_, index_] := Module[{temp1},
temp1 = Rest[dataIn[[All, index ;; index + 1]]];
Delete[temp1, Position[temp1[[All, 1]], ""]]
];

The above, takes my original data and an index corresponding to a date
column of one of the paired columns of date and values from the
original data as input. It returns a matrix with just 2 columns
corresponding to dates and values having delated rows without data.
Note: the Rest[] just takes out a header row.

Next:

deleteDatesBeforeYr[data_, year_] :=
DeleteCases[
data, _?(ToExpression[StringTake[#[[1]], -4]] < year &)];

The above, takes input from the output of cleanData[] and deletes rows
with dates prior to a specified year.

These last two functions only work on one date and value paired column
from the original data set at a time, so they could stand some
improvement to work all at once.

Still, at this point I can produce:

1. A list of sorted unique "master" dates and
2. Several matrices each with a date and data column.

Now I need to combine them into a single matrix with the unique
"master" dates in the first column and the data from each of the
several 2 column matrices aligned in the correct row of the date
column.

(I know this will leave me with some blank entries at some positions
in the data columns of the matrix. I need to give some thought to
what to do with these later.)

After these 3 posts and longwinded explanations, if anyone has stayed
with this and can suggest simpler ways to do the above and a way to do
the next step to get me what I need, I will very much appreciate the
guidance.

Thx
G

(Wouldn't it make things a bit easier if we could edit our posts?
Just wondering)

From: Raffy on 21 Jan 2010 04:54
On Jan 20, 3:48 am, Garapata <warsaw95...(a)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(a)Flatten[mT[[Range[1, Length[mT], 2]]]], (1-
ToExpression(a)StringSplit[#, "/"]).{31, 1, 372} &];

(* For each column pair, replace each date string with the value,
otherwise replace with Indeterminate *)
mNew = Transpose(a)Developer`PartitionMap[vDates /. Append[MapThread
[Rule, #], _String -> Indeterminate] &, mT, 2];

(* Print Results *)
TableForm[mNew, TableHeadings -> {vDates, Automatic}]

From: Bill Rowe on 21 Jan 2010 04:56
On 1/20/10 at 6:49 AM, warsaw95826(a)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(a)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}}


 |  Next  |  Last
Pages: 1 2
Prev: Using Mathematica to analyze Firefox startup performance
Next: simple nest