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?

Improve this question
$\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$ Commented 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$ Commented Nov 4, 2013 at 22:13
  • $\begingroup$ Please edit any relevant data directly into the question. $\endgroup$ Commented Nov 5, 2013 at 14:44

4 Answers 4

Reset to default
3
$\begingroup$

How about

Position[Boole@MapThread[(#1==#2)&,{epiDB[[1;;3]],epiExcel[[1;;3]]},2],0] {{3,1}}
Improve this answer
$\endgroup$
2
  • $\begingroup$ Shouldn't there be ;;, 1;;2 instead of 1;;2? $\endgroup$ Commented Nov 4, 2013 at 22:42
  • $\begingroup$ @Kuba: I don't think so, based on the example given below. $\endgroup$ Commented Nov 4, 2013 at 22:57
2
$\begingroup$

Similar to David Skulsky solution:

Position[#, 1] & /@ ( Transpose[Unitize[epiDB - epiExcel]][[{1, 2}]] )
Improve this answer
$\endgroup$
1
  • $\begingroup$ I always forget about Unitize. Very nice. $\endgroup$ Commented 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}}}}

Improve this answer
$\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$ Commented 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$ Commented 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]]}]] ]
Improve this answer
$\endgroup$

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.