title: Finding hits based on subgroups (A, B, C) in big data set
body: <p>I have a huge amount of data to analyze. </p>
  
  <p>From this example file I need to know all hits ("samples") that were found either </p>
  
  <ul>
  <li>only in B,</li>
  <li>only in C,</li>
  <li>in A and C or</li>
  <li>in A and B,</li>
  </ul>
  
  <p>but not the ones that are found </p>
  
  <ul>
  <li>in B and C or</li>
  <li>in A, B and C.</li>
  </ul>
  
  <p>[edit: to keep it more simple: there should be no co-occurence of B and C]</p>
  
  <p>These letters are found in column $8. </p>
  
  <p>The first two columns together can be used as an identifier for each "sample". </p>
  
  <p>Example: You can see that for “463;88” we find A and C in column $8 which would make “463;88” a hit that I need in a separate output file. "348;64" is found in A, B and C and would therefore be discarded/ignored. </p>
  
  <p>File1.csv</p>
  
  <pre><code>463;88;1;193187729;280062;CDC73;IS;A;0.0
  463;88;1;193188065;280062;CDC73;IS;A;0.0
  463;88;1;193188527;280062;CDC73;IS;A;0.0
  463;88;1;193188542;280062;CDC73;IS;C;0.0
  348;64;1;155219446;384172;GBAP1;IS;B;0.0
  348;64;1;155224629;384172;GBAP1;IS;C;0.0
  348;64;1;155224965;384172;GBAP1;IS;A;0.0
  71;35;2;27400461;145220;PPM1G;IS;A;0.0
  71;35;2;27400930;145220;PPM1G;IS;A;0.0
  71;35;2;27401162;145220;PPM1G;IS;A;0.0
  71;35;2;27403518;145220;PPM1G;IS;B;0.0
  71;35;2;27403545;145220;PPM1G;IS;B;0.0
  71;35;2;27404353;145220;PPM1G;IS;B;0.0
  71;35;2;27419156;145220;NRBP1;IS;B;0.0
  7;14;20;2894103;92099;PTPRA;IS;B;0.0
  7;14;20;2906211;92099;PTPRA;IS;C;0.0
  7;14;20;2907301;92099;PTPRA;IS;C;0.0
  ...
  </code></pre>
  
  <p>Does anyone have a suggestion how to do this, eg with bash, awk, grep...?</p>
  
  <p>It does not need to be very efficient or fast, it just needs to be reliable.</p>
  
  <p>Edit:</p>
  
  <p>I generated a csv table with the columns 1 and 2 of lines that contain &lt;3 different entries in column $8 in several steps.    </p>
  
  <p>awk print $1, $2, $8 | sort –n | uniq > file.tmp1    </p>
  
  <p>awk print $1, $2 from file.tmp1 | sort –n | unic –c | sed for csv format > file.tmp2    </p>
  
  <p>Finally,<br>
  awk to print only the identifier columns from file.tmp2 where the count was &lt;3 (= only one or two different letters in column $8 of original file).   </p>
  
  <p>File2.csv<br>
  6;3;<br>
  12;9;<br>
  348;40;
  463;88;
  ...</p>
  
  <p>Then, I wanted to use 
  fgrep --file=File2.csv File1.csv</p>
  
  <p>but this does not seem to work properly. And it still requires manual analysis as it gives me also false hits. </p>