2
$\begingroup$

I have a data set in which I need to remove all matched pairs of entries - not just the first entry that is duplicated but both entries. To add to the complexity, the pairs must be between different columns of the data set.

So if I generate an example data set as follows:

SeedRandom[2]
dataSet = 
  Table[{DatePlus[Take[DateList[], 2], i], RandomInteger[{1, 30}], 
    RandomInteger[{1, 30}]}, {i, 50}];
Grid[%]

To yield a data set of:

    {
 {{2014, 6, 2}, 8, 4},
 {{2014, 6, 3}, 5, 4},
 {{2014, 6, 4}, 7, 4},
 {{2014, 6, 5}, 0, 1},
 {{2014, 6, 6}, 0, 4},
 {{2014, 6, 7}, 3, 7},
 {{2014, 6, 8}, 3, 0},
 {{2014, 6, 9}, 2, 7},
 {{2014, 6, 10}, 8, 7},
 {{2014, 6, 11}, 3, 6}
}

I want to remove any rows in which a value in column 2 matches a value in column 3 (please ignore the date column [[1]] as it is not relevant at the moment). If a match is found between values in column 2 and 3 then both entries should be removed. Zero entries should be ignored and left in the data set so the resultant data set would look like:

{
 {{2014, 6, 2}, 8, 4},
 {{2014, 6, 3}, 5, 4},
 {{2014, 6, 5}, 0, 1},
 {{2014, 6, 6}, 0, 4},
 {{2014, 6, 8}, 3, 0},
 {{2014, 6, 9}, 2, 7},
 {{2014, 6, 10}, 8, 7},
 {{2014, 6, 11}, 3, 6}
}

Note that the actual data set only has a non-zero entry in one of the two columns for a row but it was too difficult for me to reproduce this easily for this example data.

I am unable to get DeleteDuplicates to do what I want so am stumped as to what to do.



My apologies, it looks like my earlier edits did not stick. I'll reformat the data to remove the ambiguity (note goal posts are not moving - just additional clarification of the data).

Generate example data:

SeedRandom[2]
dataSet = 
  Table[{Junk, RandomInteger[{0, 8}], RandomInteger[{0, 8}]}, {i, 
    10}];
Grid[%]


{
 {Junk, 8, 4},
 {Junk, 5, 4},
 {Junk, 7, 4},
 {Junk, 0, 1},
 {Junk, 0, 4},
 {Junk, 3, 7},
 {Junk, 3, 0},
 {Junk, 2, 7},
 {Junk, 8, 7},
 {Junk, 3, 6}
}

Output:

{
 {Junk, 8, 4},
 {Junk, 5, 4},
 {Junk, 0, 1},
 {Junk, 0, 4},
 {Junk, 3, 0},
 {Junk, 2, 7},
 {Junk, 8, 7},
 {Junk, 3, 6}
}

Hopefully that removes the ambiguity of the first column and enables us to concentrate on columns 2 and 3.

I do apologise for the confusion.

I will go through all the answers as I'm sure there are already some that demonstrate a good solution.

$\endgroup$
  • 3
    $\begingroup$ I really don't get what the condition is supposed to be. From the data and the desired result the only condition that I see is when the third element of the first column is equal to the last column. $\endgroup$ – Öskå Jun 26 '14 at 14:05
  • $\begingroup$ This is a very ambiguous question. Do you in fact mean "remove a pairwise match (by your match criteria), rinse-n-repeat until fixedpoint..."? $\endgroup$ – ciao Jun 27 '14 at 0:53
  • $\begingroup$ @rasher That would seem to be the case; I believe that interpretation matches both the description of the problem and the example output. $\endgroup$ – Mr.Wizard Jun 27 '14 at 0:56
  • $\begingroup$ Perhaps further clarification if none of the answers fits the bill is warranted. As it stands, perilously close to a close - if that happens, consider editing in whatever is appropriate and getting it re-opened. $\endgroup$ – ciao Jun 28 '14 at 11:18
2
$\begingroup$

By my interpretation of your question this should do what you want. If you confirm that this behaves as desired I shall seek a more efficient solution.

With your input assigned to dat:

dat //. {a___, {_, x_ /; x != 0, _}, b___, {_, _, x_}, c___} :> {a, b, c}
{
 {{2014, 6, 2}, 8, 4},
 {{2014, 6, 3}, 5, 4},
 {{2014, 6, 5}, 0, 1},
 {{2014, 6, 6}, 0, 4},
 {{2014, 6, 8}, 3, 0},
 {{2014, 6, 9}, 2, 7},
 {{2014, 6, 10}, 8, 7},
 {{2014, 6, 11}, 3, 6}
}
$\endgroup$
  • $\begingroup$ This answers my question as posed but on further testing with a larger dataset it doesn't quite do what I was intending. What is the best way to offer up a new dataset without turning people off? This solution will match and remove a pair if the first occurrence is in the 2nd column and a match is found in the 3rd, but if the first occurrence is in the 3rd column it will not find a matching occurrence in the 2nd. (note you did correctly interpret the first column as the date {2014, 6, 2} for example - and not of specific interest to the problem). $\endgroup$ – CrustyNoodle Jul 2 '14 at 13:39
  • $\begingroup$ @CrustyNoodle Since mine is one of only two extant answers I suggest you update the question with the better example and description. I won't mind; hopefully neither will rasher. Thanks for asking. $\endgroup$ – Mr.Wizard Jul 2 '14 at 20:32
1
$\begingroup$

I'll throw this in the ring, pending needed clarification of the "rules" of this problem. My interpretation (see comments under OP) matches that of Mr. Wizard. Assuming that's what you want, this will be vastly faster on larger lists (if your lists are <100 in length, probably irrelevant):

f = Module[{l = #, ctr = 1, res},
   NestWhile[(res = Pick[Range[ctr + 1, Length@#], #[[ctr + 1 ;;, 3]], #[[ctr, 2]]];
      If[res == {} || #[[ctr, 2]] == 0, ctr++; #, Delete[#, {{ctr}, {res[[1]]}}]]) &,
      l, ctr < Length@# &]] &;

f@dataSet

(*

{{{2014,6,2},8,4},{{2014,6,3},5,4},{{2014,6,5},0,1},{{2014,6,6},0,4},{{2014,6,8},3,0},{{2014,6,9},2,7},{{2014,6,10},8,7},{{2014,6,11},3,6}}

*)

On a list of 1000 length using same random domain as your example, over 100X faster than using rules, larger the list, bigger the edge. This also has much more predictable performance - changing the range of possible values in the test data results in little change with this, while the rules-based can get much worse (e.g. tripling the density of duplicates on the same 1000 length list results in a ~500X performance edge.).

Of course, if the interpretation of your problem is incorrect... never mind ;-)

$\endgroup$
  • $\begingroup$ +1 for jumping ahead to a solution with reasonable computational complexity. Let's hope it's what the OP wanted. $\endgroup$ – Mr.Wizard Jun 27 '14 at 3:19

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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