title: Counting the weighted intersection of equivalent rows in two tables
body: <p>The following question is a generalization to the question posted here:</p>
  
  <p><a href="https://stackoverflow.com/questions/25428577/counting-the-intersection-of-equivalent-rows-in-two-tables">Counting the intersection of equivalent rows in two tables</a></p>
  
  <p>I have two FITS files. For example, <strong>the first file has 100 rows and 2 columns. The second file has 1000 rows and 3 columns</strong>. </p>
  
  <p><code>FITS FILE 1          FITS FILE 2</code></p>
  
  <pre><code>A        B        C        D     E 
  
  1        2        1        2    0.1
  1        3        1        2    0.3 
  2        4        1        2    0.9 
  </code></pre>
  
  <p>I need to take the first row of the first file, i.e 1 and 2 and check how many rows in the second file have C = 1 and D = 2 <strong>weighting each pair (C,D) with respect to the corresponding value in column E</strong>.</p>
  
  <p>In the example, I have 3 rows in the second file that have C = 1 and D = 2. They have weights E = 0.1, 0.3, and 0.9, respectively. Weighting with respect to the values in E, I need to associate the value 0.1+0.3+0.9 = 1.3 to the pair (A,B) = (1,2) of the first file. Then, I need to do the same for the second row (first file), i.e 1 and 3 and find out how many rows in the second file have 1 and 3, again weighting with respect to the value in column E, and so on.</p>
  
  <p><strong>The first file does not have duplicates (all the rows have different pairs, none are identical, only file 2 has many identical pairs which I need to find)</strong>.</p>
  
  <p>I finally need the <strong>weighted numbers</strong> of rows in the second file that have the similar values as that of the rows of the first FITS file.</p>
  
  <p>The result should be:</p>
  
  <p><code>A    B    Number 
  1    2      1.3   # 1 and 2 occurs 1.3 times
  1    3      4.5   # 1 and 3 occurs 4.5 times</code></p>
  
  <p>and so on for all pairs in A and B columns.</p>
  
  <p><strong>I know from the post cited above that the solution for weights in column E all equal to 1 involves  <code>Counter</code></strong>, as follows: </p>
  
  <pre><code>from collections import Counter
  # Create frequency table of (C,D) column pairs
  file2freq = Counter(zip(C,D))
  # Look up frequency value for each row of file 1
  for a,b in zip(A,B):
      # and print out the row and frequency data.
      print a,b,file2freq[a,b]
  </code></pre>
  
  <p><strong>To answer the question I need to include the weights in E when I use <code>Counter</code>:</strong></p>
  
  <pre><code>file2freq = Counter(zip(C,D))
  </code></pre>
  
  <p>I was wondering if it is possible to do that.</p>
  
  <p>Thank you very much for your help!</p>