Take the 2-minute tour ×
Mathematica Stack Exchange is a question and answer site for users of Mathematica. It's 100% free, no registration required.

Find the intersection of dates within the pair and remove the other dates & corresponding prices. Also keep the list in same format as it currently is in.

Let me explain: Each stock has closing dates and prices. There are many groups of 2 stocks in the list.(all unique pairs) Within each stock pair(group) I want to delete the dates and corresponding prices if the dates does not match both stocks(finding the intersection of dates within the pairs.)

Input: Subsets[Partition[tt, 2, 3], {2}]
Output: {{{{"AAD AT Equity", 
"Date", $Failed, {2013, 8, 1, 0, 0, 0.}, {2013, 8, 2, 0, 0, 
     0.}, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}}, {"", 
    "PX_LAST", 0.82, 1.77, 1.75, 1.72, 1.71}}, {{"ABC AT Equity", 
    "Date", $Failed, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}, 
    "", ""}, {"", "PX_LAST", 0.6808, 3.33, 3.35, "", 
    ""}}}, {{{"AAD AT Equity", 
    "Date", $Failed, {2013, 8, 1, 0, 0, 0.}, {2013, 8, 2, 0, 0, 
     0.}, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}}, {"", 
    "PX_LAST", 0.82, 1.77, 1.75, 1.72, 1.71}}, {{"ABP AT Equity", 
    "Date", $Failed, {2013, 8, 2, 0, 0, 0.}, {2013, 8, 5, 0, 0, 
     0.}, {2013, 8, 6, 0, 0, 0.}, ""}, {"", "PX_LAST", 5.3942, 2.22, 
    2.2, 2.2, ""}}}, {{{"AAD AT Equity", 
    "Date", $Failed, {2013, 8, 1, 0, 0, 0.}, {2013, 8, 2, 0, 0, 
     0.}, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}}, {"", 
    "PX_LAST", 0.82, 1.77, 1.75, 1.72, 1.71}}, {{"ACR AT Equity", 
    "Date", $Failed, {2013, 8, 6, 0, 0, 0.}, "", "", ""}, {"", 
    "PX_LAST", 1., 3.32, "", "", ""}}}, {{{"ABC AT Equity", 
    "Date", $Failed, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}, 
    "", ""}, {"", "PX_LAST", 0.6808, 3.33, 3.35, "", 
    ""}}, {{"ABP AT Equity", 
    "Date", $Failed, {2013, 8, 2, 0, 0, 0.}, {2013, 8, 5, 0, 0, 
     0.}, {2013, 8, 6, 0, 0, 0.}, ""}, {"", "PX_LAST", 5.3942, 2.22, 
    2.2, 2.2, ""}}}, {{{"ABC AT Equity", 
    "Date", $Failed, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}, 
    "", ""}, {"", "PX_LAST", 0.6808, 3.33, 3.35, "", 
    ""}}, {{"ACR AT Equity", "Date", $Failed, {2013, 8, 6, 0, 0, 0.}, 
    "", "", ""}, {"", "PX_LAST", 1., 3.32, "", "", 
    ""}}}, {{{"ABP AT Equity", 
    "Date", $Failed, {2013, 8, 2, 0, 0, 0.}, {2013, 8, 5, 0, 0, 
     0.}, {2013, 8, 6, 0, 0, 0.}, ""}, {"", "PX_LAST", 5.3942, 2.22, 
    2.2, 2.2, ""}}, {{"ACR AT Equity", 
    "Date", $Failed, {2013, 8, 6, 0, 0, 0.}, "", "", ""}, {"", 
"PX_LAST", 1., 3.32, "", "", ""}}}}

For the first pair of stocks I want to end up with this correct result:

{{{"AAD AT Equity", 
"Date", $Failed,, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}}, {"", 
    "PX_LAST", 0.82, 1.72, 1.71}}, {{"ABC AT Equity", 
    "Date", $Failed, {2013, 8, 5, 0, 0, 0.}, {2013, 8, 6, 0, 0, 0.}, 
"", ""}, {"", "PX_LAST", 0.6808, 3.33, 3.35, "", 
""}}}

As you can see, what has happened is that AAD equity have lost it two first trading dates in the list and its corresponding close price, and we end up with a pair of stocks with equal number of corresponding dates, and each stock have same amount of closing prices.

Note: I want this to be done for each pair.

share|improve this question
    
I'm sorry but I think you can do a better job at explaining what you want to do. Someone else downvoted this question and it's probably because even if there is a long description, it's not very obvious what you're trying to do. –  Pickett Sep 2 '13 at 6:18
2  
Also, you do ask many related questions (selection objects in lists, deleting others etc). I strongly suggest to take a good look at the answers to your questions and other related ones, identify the functions used there (look them up in Documentation Center) and try to make sense of it. I am sure you can answer most questions yourself after doing so! (and if you identify a new question, ask it in a concise manner, I am sure people will love to help you here) –  Pinguin Dirk Sep 2 '13 at 6:38
    
I have been working all day trying to get some sort of way to do it, however I cant seem to find a solution, I am sorry if you didnt understand the question I tried to explain it in as much detail as possible. I have also tried using Cases and select, but I am not getting the result I want! –  ALEXANDER Sep 2 '13 at 8:04
    
another note: as far as I can tell, you import Bloomberg Data to excel. The wizards at your disposal for doing so offer a vast range of tools to get data based on weekdays, trading days etc (this obviously doesn't solve the problem presented here, but I have the feeling you could (and maybe should?) approach your problem differently, anyway :) ) –  Pinguin Dirk Sep 2 '13 at 8:09
1  
I understand, I really want to understand how to solve this question. I have no problem getting the data in to the computer in a another format. I just really need it to be done in this specific way! Hope that there are someone that would be able to help me here! Promise that I will continue to work hard to understand how to extract and manipulate lists. I have to say I do have a good conscience when it comes to the stackexchange, I always spend hours and hours before I ask questions, sometimes even weeks. A lot of the programming works but I am struggling with data handling. –  ALEXANDER Sep 2 '13 at 8:17

2 Answers 2

up vote 3 down vote accepted

I answered your prior question relating to manipulating this data. I showed there how transforming the data made it easier to work with. I think perhaps you should consider a new format for your data as you apparently have a number of manipulation in mind and you may have to fight the existing format to get them done conveniently.

Also, if your data is large you may more (space) efficiently process it by not constructing all pairs (subsets) in advance, but instead iterating through them. This goes against normal recommendations for Mathematica programming but it can make sense in such cases.

These things notwithstanding I will still show you a way to manipulate this data.

filter[d1_] :=
  Module[{pos, keep},
    keep = DeleteCases[Intersection @@ d1[[All, 1, 2 ;;]], ""];
    pos = Position[#[[1]], Alternatives @@ keep, {1}] &;
    #[[All, Flatten @ {1, pos @ #}]] & /@ d1
  ]

newdat = filter /@ data;

A look at the result:

MatrixForm /@ newdat

enter image description here


Here is a different formulation that may be faster on long columns:

filter2[d1_] :=
  Module[{keep},
    Scan[
      keep[#] = True; &,
      Intersection @@ d1[[All, 1]]
    ];
    keep[""] = False;
    keep[Except[_List]] = True;
    Select[#\[Transpose], keep @ #[[1]] &]\[Transpose] & /@ d1
  ]
share|improve this answer
    
Mr.Wizard do you have time for a chat, I have a couple of questions –  ALEXANDER Sep 2 '13 at 11:27
    
@ALEXANDER Sorry, I do not today. –  Mr.Wizard Sep 2 '13 at 12:08
    
That is okey! Ill try another day! Thank you –  ALEXANDER Sep 2 '13 at 12:10
    
MR. Wizard could you explain this filter I am still stuck with the scan, keep and intersect. Ive tried splitting it up but I dont understand what is going on. –  ALEXANDER Nov 28 '13 at 2:46
    
@ALEXANDER I am not spending a lot of time on the site these days but I will try to give at least a brief description soon. I can't think of a way to say this that doesn't sound rude, but you have been using Mathematica long enough now that I think you should be able to work through this code (with effort). I encourage you to keep trying. Make sure you are familiar with these tools for the (human) parsing of code. –  Mr.Wizard Nov 29 '13 at 12:49

Based on the chat, here's an answer, sort of. I didn't test for stability, but it seems to work. Please confirm if this is what you want:

I define a function func, that works on each pair, identifying dates in common and then dropping out the other dates and respective prices:

func[object_, preFields_: 3] := 
  Module[{goodDates, posGood, posBad, lengthRecord},
    lengthRecord = Length[First[First@object]];
    goodDates = DeleteCases[
                  Intersection[Sequence @@ object[[All, 1, 1 + preFields ;;]]],""];
    posGood = Function[pair, 
       Flatten[Position[object[[pair, 1, 1 + preFields ;;]], #] & /@ goodDates]] 
       /@ Range[Length@object];
    posBad = Complement[Range[preFields + 1, lengthRecord], #] & /@ (posGood + preFields);
    posBad = Map[List, posBad, {2}];
    Transpose[
       Function[obj, MapIndexed[Delete[#, posBad[[First@#2]]] &, obj]] /@ Transpose[object]]
 ]

I tried to make it easy to read, but wasn't too successful in this regard. Please confirm that it's doing the right thing and I'll add some background.

To use it:

func /@ data

... long output

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.