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.

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?

share|improve this question
1  
Why don't you provide a mini database, e.g. 3 lines in each list, for us to have some idea about the structure. –  David Carraher Nov 4 '13 at 22:03
    
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}} –  Mitchell Kaplan Nov 4 '13 at 22:13
    
Please edit any relevant data directly into the question. –  Yves Klett Nov 5 '13 at 14:44
add comment

4 Answers

How about

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

{{3,1}}
share|improve this answer
    
Shouldn't there be ;;, 1;;2 instead of 1;;2? –  Kuba Nov 4 '13 at 22:42
    
@Kuba: I don't think so, based on the example given below. –  David Skulsky Nov 4 '13 at 22:57
add comment

Similar to David Skulsky solution:

Position[#, 1] & /@ ( Transpose[Unitize[epiDB - epiExcel]][[{1, 2}]] )
share|improve this answer
    
I always forget about Unitize. Very nice. –  David Skulsky Nov 4 '13 at 22:59
add comment

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}}}}

share|improve this answer
    
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? –  Mitchell Kaplan Nov 4 '13 at 22:36
    
I noticed afterwards that there were 4 columns. I got around that by using __ in the final column. –  David Carraher Nov 4 '13 at 22:37
add comment

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]]}]]
 ]
share|improve this answer
add comment

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.