title: Given two files (IP's and Subnet Info), create file that associates each IP to a subnet
body: <p>I've been struggling for a couple of days with the proper way to address this solution, and I am seeking some assistance.</p>
  
  <p>I have two files and need to create a third that shows the relationship.</p>
  
  <ol>
  <li>IP Address file - ip.csv</li>
  <li>Subnet file - subnet.csv</li>
  </ol>
  
  <p>I need to specify what subnet that each IP is in, and create a third file</p>
  
  <p>The ip.csv file will contain about 1.5 million IP's and the subnet.csv file will contain around 140,000 subnets.</p>
  
  <p>ip.csv file sample:</p>
  
  <pre class="lang-none prettyprint-override"><code>IP,Type
  10.78.175.167,IPv4
  10.20.3.56,IPv4
  </code></pre>
  
  <p>subnet.csv file sample:</p>
  
  <pre class="lang-none prettyprint-override"><code>Subnet,Netmask
  10.176.122.136/30,255.255.255.252
  10.20.3.0/24,255.255.254.0
  </code></pre>
  
  <p>Format of file I need to create:</p>
  
  <pre class="lang-none prettyprint-override"><code>Subnet,IP
  10.20.3.0/24,10.20.3.56
  </code></pre>
  
  <p>I've tried to make use of things from these pages:</p>
  
  <ul>
  <li><a href="https://pypi.python.org/pypi/pysubnettree/0.23" rel="nofollow noreferrer">subnettree module</a></li>
  <li><a href="https://docs.python.org/3/library/ipaddress.html#module-ipaddress" rel="nofollow noreferrer">ipaddress module</a></li>
  <li><a href="http://terminalmage.net/2012/06/10/how-to-find-out-the-cidr-notation-for-a-subnet-given-an-ip-and-netmask.html" rel="nofollow noreferrer">random page with cidr help</a></li>
  <li><a href="http://cmikavac.net/2011/09/11/how-to-generate-an-ip-range-list-in-python/" rel="nofollow noreferrer">IP range help</a></li>
  </ul>
  
  <p>This is the code that I have tried.  It works on small sets, but I'm having problems running it with the full set of files.</p>
  
  <pre><code>#!/usr/local/bin/python2.7
  import csv
  import ipaddress
  import iptools
  import re
  import SubnetTree
  import sys
  from socket import inet_aton
  
  testdir = '/home/test/testdir/'
  iprelfile = testdir + 'relationship.csv'
  testipsub = testdir + 'subnet.csv'
  testipaddr = testdir + 'ip.csv'
  
  o1 = open (iprelfile, "a")
  
  # Subnet file
  IPR = set()
  o1.write('Subnet,IP\n')
  with open(testipsub, 'rb') as master:
      reader = csv.reader(master)
      for row in reader:
          if 'Subnet' not in row[0]:
              # Convert string to unicode to be parsed with ipaddress module
              b = unicode(row[1])
              # Using ipaddress module to create list containing every IP in subnet
              n2 = ipaddress.ip_network(b)
              b1 = (list(n2.hosts()))
              # IP address file
              with open(testipaddr, 'rb') as ipaddy:
                  readera = csv.reader(ipaddy)
                  for rowa in readera:
                      if 'IP' not in rowa[0]:
                          bb = rowa[0]
                          for ij in b1:
                              # Convert to string for comparison
                              f = str(ij)
                              # If the IP address is in subnet range
                              if f == bb:
                                  IPR.update([row[0] + ',' + bb + '\n'])
  
  
  for ip in IPR:
      o1.write(ip + '\n')
  
  # Closing the file
  o1.close()
  </code></pre>