title: Parse CSV File to Find Distinct Columns in Python
body: <p>I have a ~3000 line CSV file formatted with the following column headers:</p>
  
  <pre><code>STORE|BRAND ID|ZONE|SHORT DESCRIPTION (ZONE)|REGION|SHORT DESCRIPTION (REGION)|DISTRICT|SHORT DESCRIPTION (DISTRICT)
  </code></pre>
  
  <p>Here is a sample of that data:</p>
  
  <pre><code>0010|Company A|0001|East|123|New England|012|Connecticut
  0010|Company B|0002|West|456|Coast|010|Oregon
  0025|Company A|0001|East|246|South|010|Florida
  0010|Company A|0004|West|456|Coast|011|California
  </code></pre>
  
  <p>There are duplicate zones, regions and districts within the file. What I'm trying to accomplish is aggregate the data by ZONE, REGION and DISTRICT per BRAND like this:</p>
  
  <p><strong>:ZONES:</strong></p>
  
  <pre><code>0001|Company A|East
  0002|Company B|West
  0004|Company A|West
  </code></pre>
  
  <p><strong>:REGIONS:</strong></p>
  
  <pre><code>123|Company A|New England
  456|Company B|Coast
  246|Company A|South
  </code></pre>
  
  <p><strong>:DISTRICTS:</strong></p>
  
  <pre><code>012|Company A|Connecticut
  010|Company A|Oregon
  010|Company B|Florida
  011|Company A|California
  </code></pre>
  
  <p>The code below, leveraging itertools/groupby, works fine and provides me the data as I need it, however it bothers me how iterative it is in the sense that I read the file, find distinct zones, read the file again, find distinct regions, read the file a third time and find distinct districts. I'm thinking there has to be a more streamlined approach to reading this file and aggregating this data. </p>
  
  <pre><code>with file(myFile, 'r') as f:
          content = f.read()
  
  csv.register_dialect('piper', delimiter='|', quoting=csv.QUOTE_NONE)
  
  with open(myFile) as csvfile:
          reader = csv.DictReader(csvfile, dialect='piper')
  
          zones_dict = sorted(list(reader), key=itemgetter('ZONE','SHORT DESCRIPTION (ZONE)','BRAND ID'))     
  
          csvfile.seek(0)
          reader = csv.DictReader(csvfile, dialect='piper')
  
          regions_dict = sorted(list(reader), key=itemgetter('REGION', 'SHORT DESCRIPTION (REGION)', 'BRAND ID', 'ZONE'))
  
          csvfile.seek(0)
          reader = csv.DictReader(csvfile, dialect='piper')
  
          districts_dict = sorted(list(reader), key=itemgetter('DISTRICT', 'SHORT DESCRIPTION (DISTRICT)', 'BRAND ID', 'REGION'))
  
          ###
          #Aggregate Zone#
          ###
  
          for zone_id, zone_group in groupby(zones_dict, itemgetter('ZONE','SHORT DESCRIPTION (ZONE)','BRAND ID')):
              theZone = zone_id
              print theZone[0]
  
          print " "
  
          ###
          #Aggregate Region#
          ###
  
          for region_id, region_group in groupby(regions_dict, itemgetter('REGION', 'SHORT DESCRIPTION (REGION)', 'BRAND ID', 'ZONE')):
              theRegion = region_id
              print theRegion[0]
  
          print " "
  
          ###
          #Aggregate District#
          ###
  
          for district_id, district_group in groupby(districts_dict, itemgetter('DISTRICT', 'SHORT DESCRIPTION (DISTRICT)', 'BRAND ID', 'REGION')):
              theDistrict = district_id
              print theDistrict[0]
  </code></pre>
  
  <p>Any thoughts on a better approach?</p>