title: Comparing two csv files by non unique values from multiple columns, output to csv extra data where matched
body: <p>I have run out of search terms and have come full circle. I've read and tried a ton of question and answers on what I thought would be a fairly simple task, but no joy.</p>
  
  <p>I have two csv files.</p>
  
  <pre><code>**file1.csv** (2,000 + lines)&lt;br/&gt;
  product_code, colour, size, sku, more cols..., barcode&lt;br/&gt;
  &lt;span style="color: red"&gt;
  item98, red, XL, adfd56384678, ..., null&lt;br/&gt;
  item99, black, L, adfgk9087467, ..., null&lt;br/&gt;
  item98, red, S, adfgad240568, ..., null&lt;br/&gt;
  &lt;/span&gt;
  
  **file2.csv** (20,000 + lines)&lt;br/&gt;
  ITEM_CODE, COLOUR, SIZE, BAR_CODE&lt;br/&gt;
  &lt;span style="color: red"&gt;
  item98, RED, XL, 090900887&lt;br/&gt;
  item98, RED, S, 43581034&lt;br/&gt;
  item97, BLUE, M, 519685371&lt;br/&gt;
  &lt;/span&gt;
  </code></pre>
  
  <p>I need to output:</p>
  
  <pre><code>**matched-result.csv** (2,000 + lines)&lt;br/&gt;
  product_code, colour, size, sku, more cols..., barcode&lt;br/&gt;
  &lt;span style="color: red"&gt;
  item98, red, XL, adfd56384678, ..., 090900887&lt;br/&gt;
  item99, black, L, adfgk9087467, ..., null&lt;br/&gt;
  item98, red, S, adfgad240568, ..., 519685371&lt;br/&gt;
  &lt;/span&gt;
  </code></pre>
  
  <p>The sku and barcode are unique values which can only be identified by matching product_code, size and colour. I need the barcode in a new column at the end of file1.csv. My first successful-ish attempt was using awk.</p>
  
  <pre><code>&lt;pre&gt;
  awk -F',' -v OFS=, 'NR==FNR{a[$1,tolower($2),$3]=$4;next}{if( b = a[$1,tolower($2),$3]){ print $1,$2,$3,$4,b }}' file1.csv file2.csv &gt; matched-result.csv
  &lt;/pre&gt;
  </code></pre>
  
  <p>I struggled outputting the whole of <strong>file1.csv</strong> with the results, trying <strong>print $0,b</strong> which created a new row with value of b. I would also like all of file1.csv output with empty values for non matches and the head if possible. I had to align the columns to match up for this awk approach to work, but the original fields don't align. This is no big deal to do, but I wonder if it's necessary of if there's a work around?</p>
  
  <p>After that I tried to merge <strong>file1.csv</strong> with the awk output file <strong>matched-result.csv</strong> using a python script.</p>
  
  <pre>
      import csv
      from collections import OrderedDict
  
      with open('file1.csv') as f:
          r = csv.reader(f, delimiter=',')
          dict1 = {row[0]: row[1:] for row in r}
  
      with open('matched-result.csv') as f:
          r = csv.reader(f, delimiter=',')
          dict2 = OrderedDict((row[0], row[1:]) for row in r)
  
      result = OrderedDict()
      for d in (dict1, dict2):
          for key, value in d.iteritems():
              result.setdefault(key, []).extend(value)
  
      with open('desired-result.csv', 'wb') as f:
          w = csv.writer(f)
          for key, value in result.iteritems():
              w.writerow([key] + value)
  </pre>
  
  <p>The output isn't the desired result. The order is wrong the number of records don't exactly match <strong>file1.csv</strong> there's an extra row? Also this two step approach seems disjointed and feels like one of these could do the job if done properly?</p>
  
  <p>I have tried csvkit to join / merge the files but output 20,000 + rows some of which were duplicate. I think it treated the product_code/ ITEM_CODE columns as unique values, which they aren't. I have looked into join and grep but they don't seem to be the answer either.</p>
  
  <p>I have installed panda and powerShell and willing to give them a go but don't know where to start with those, clear instructions needed there. Oh and i'm noob to all these languages and programs but not completely wet behind the ears.</p>
  
  <p>hopefully I have given enough info to go on. I would have posted links to the posts of here I have read. You can take my work for it that it's +90% of them. </p>
  
  <p><strong>Please can replies, offer code examples and preferably not a completely new language or approach.</strong></p>
  
  <p><strong>// UPDATE</strong></p>
  
  <p>I've voted for <a href="https://stackoverflow.com/users/7553525/zwer">@zwer</a>  answer because it more or less worked out the box bar a minor tweak, see the comments. But it's worth pointing out that <a href="https://stackoverflow.com/users/5866580/ravindersingh13">@RavinderSingh13</a> awk method worked as well with the same result. I will pursue <a href="https://stackoverflow.com/users/129600/acushner">@acushner</a> panda method when / if I hear back and update this bit. </p>
  
  <p>In case anyone cares, which I think is the best approach? Tomayto, tomarto, not sure that works in print. Personally, python is the easier to read. Awk is nice and short, I <em>think</em> it's a regex based language which I find harder to understand and was steeper learning curve. </p>
  
  <p><strong>Thanks to everyone who commented. I was wrestling with this for a long time and got it sorted on here pronto.</strong></p>