2
$\begingroup$

I'm able to do what I need, but it seems like there's always a better way. I have two lists, epiExcel and epiDB. Each has 3 columns and about 2,500 rows. I need to find out if the first 2 columns of each match, and when they don't where the mismatches are. I did the following:

z = Position[epiDB[[All, {1, 2}]], #] & /@ epiExcel[[All, {1, 2}]];
Position[z, {}]
z = Position[epiExcel[[All, {1, 2}]], #] & /@ epiDB[[All, {1, 2}]];
Position[z, {}]

This worked for the specific case I have at the moment. Is there a better way to do this?

$\endgroup$
3
  • 1
    $\begingroup$ Why don't you provide a mini database, e.g. 3 lines in each list, for us to have some idea about the structure. $\endgroup$
    – DavidC
    Nov 4, 2013 at 22:03
  • $\begingroup$ epiDB[[1 ;; 3]] {{"EMC0008", "2012-01-01", "Casualty Treaty XS Clash", 39697.}, {"EMC0009", "2012-01-01", "Casualty Treaty XS Clash", 24432.}, {"TYCS1041", "2012-01-01", "Casualty Treaty PR CM", 2.64465*10^7}} $\endgroup$ Nov 4, 2013 at 22:13
  • $\begingroup$ Please edit any relevant data directly into the question. $\endgroup$
    – Yves Klett
    Nov 5, 2013 at 14:44

4 Answers 4

3
$\begingroup$

How about

Position[Boole@MapThread[(#1==#2)&,{epiDB[[1;;3]],epiExcel[[1;;3]]},2],0]

{{3,1}}
$\endgroup$
2
  • $\begingroup$ Shouldn't there be ;;, 1;;2 instead of 1;;2? $\endgroup$
    – Kuba
    Nov 4, 2013 at 22:42
  • $\begingroup$ @Kuba: I don't think so, based on the example given below. $\endgroup$
    – Cassini
    Nov 4, 2013 at 22:57
2
$\begingroup$

Similar to David Skulsky solution:

Position[#, 1] & /@ ( Transpose[Unitize[epiDB - epiExcel]][[{1, 2}]] )
$\endgroup$
1
  • $\begingroup$ I always forget about Unitize. Very nice. $\endgroup$
    – Cassini
    Nov 4, 2013 at 22:59
1
$\begingroup$

Perhaps this will address your question.

epiDB = {{"EMC0008", "2012-01-01", "Casualty Treaty XS Clash", 39697.}, {"EMC0009", "2012-01-01", "Casualty Treaty XS Clash", 24432.}, {"TYCS1041", "2012-01-01", "Casualty Treaty PR CM", 2.64465*10^7}};
epiExcel = {{"EMC0008", "2012-01-01", "Casualty Treaty XS Clash", 39697.}, {"EMC0009", "2012-01-01", "Casualty Treaty XS Clash", 24432.}, {"TYCS1049", "2012-01-01", "Casualty Treaty PR CM", 2.64465*10^7}};

The following returns the cases in which the first two respective rows do not match in both the first and second columns. The index is the row number from the original databases. Row 3 does not match in the first two columns.

DeleteCases[MapIndexed[List[#2[[1]], #1] &, Thread[List[epiDB, epiExcel]]], {_, {{a_, b_, __}, {a_, b_, __}}}]

{{3, {{"TYCS1041", "2012-01-01", "Casualty Treaty PR CM", 2.64465*10^7}, {"TYCS1049", "2012-01-01", "Casualty Treaty PR CM", 2.64465*10^7}}}}

$\endgroup$
2
  • $\begingroup$ Thank you - apparently I can't count. There are 4 columns, 3 of which need to match. I realized that when your code returned too many mismatches. I need to re-look at it. Am I supposed to fix my question above or just leave it as is? $\endgroup$ Nov 4, 2013 at 22:36
  • $\begingroup$ I noticed afterwards that there were 4 columns. I got around that by using __ in the final column. $\endgroup$
    – DavidC
    Nov 4, 2013 at 22:37
0
$\begingroup$

It seems from the comments that it is the first 3 positions that need to match (i.e. the strings)

If[epiDB[[All, 1 ;; 3]] === epiExcel[[All, 1 ;; 3]],
 Print["Match"],
 (* else find the positions that don't match *)    
 MapIndexed[
  If[#[[1]] =!= #[[2]], {First[#2], #[[1]], #[[2]]}, 
    Unevaluated@Sequence[]] &, 
  Transpose[{epiDB[[All, 1 ;; 3]], epiExcel[[All, 1 ;; 3]]}]]
 ]
$\endgroup$

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

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