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 '13 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$ – Mitchell Kaplan Nov 4 '13 at 22:13
  • $\begingroup$ Please edit any relevant data directly into the question. $\endgroup$ – Yves Klett Nov 5 '13 at 14:44
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 '13 at 22:42
  • $\begingroup$ @Kuba: I don't think so, based on the example given below. $\endgroup$ – Cassini Nov 4 '13 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 '13 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$ – Mitchell Kaplan Nov 4 '13 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 '13 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, privacy policy and cookie policy

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