Databases
StackOverflow2013
Postsdbo
PORemove duplicate rows from a large file in Python
 

Note that there are some explanatory texts on larger screens.

plurals
  1. PORemove duplicate rows from a large file in Python
    primarykey
    data
    text
    <p>I've a csv file that I want to remove duplicate rows from, but it's too large to fit into memory. I found a way to get it done, but my guess is that it's not the best way. </p> <p>Each row contains 15 fields and several hundred characters, and all fields are needed to determine uniqueness. Instead of comparing the entire row to find a duplicate, I'm comparing <code>hash(row-as-a-string)</code> in an attempt to save memory. I set a filter that partitions the data into a roughly equal number of rows (e.g. days of the week), and each partition is small enough that a lookup table of hash values for that partition will fit in memory. I pass through the file once for each partition, checking for unique rows and writing them out to a second file (pseudo code):</p> <pre><code>import csv headers={'DayOfWeek':None, 'a':None, 'b':None} outs=csv.DictWriter(open('c:\dedupedFile.csv','wb') days=['Mon','Tue','Wed','Thu','Fri','Sat','Sun'] outs.writerows(headers) for day in days: htable={} ins=csv.DictReader(open('c:\bigfile.csv','rb'),headers) for line in ins: hvalue=hash(reduce(lambda x,y:x+y,line.itervalues())) if line['DayOfWeek']==day: if hvalue in htable: pass else: htable[hvalue]=None outs.writerow(line) </code></pre> <p>One way I was thinking to speed this up is by finding a better filter to reduce the number of passes necessary. Assuming the length of the rows is uniformly distributed, maybe instead of </p> <pre><code>for day in days: </code></pre> <p>and </p> <pre><code>if line['DayOfWeek']==day: </code></pre> <p>we have </p> <pre><code>for i in range(n): </code></pre> <p>and</p> <pre><code>if len(reduce(lambda x,y:x+y,line.itervalues())%n)==i: </code></pre> <p>where 'n' as small as memory will allow. But this is still using the same method.</p> <p><a href="https://stackoverflow.com/users/344286/wayne-werner" title="Wayne Werner">Wayne Werner</a> provided a good practical solution below; I was curious if there was better/faster/simpler way to do this from an algorithm perspective.</p> <p>P.S. I'm limited to Python 2.5.</p>
    singulars
    1. PO
      singulars
      1. PTAnswer
    1. This table or related slice is empty.
    1. PTQuestion
    1. USCommunity
    1. USJonC
    plurals
    1. This table or related slice is empty.
    1. PL
      singulars
      1. LTLinked
    2. PL
      singulars
      1. LTLinked
    1. This table or related slice is empty.
    1. PO
      singulars
      1. PTAnswer
    2. PO
      singulars
      1. PTAnswer
    3. PO
      singulars
      1. PTAnswer
    1. VO
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. USunutbu
      1. VTFavorite
    2. VO
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. This table or related slice is empty.
      1. VTUpMod
    3. VO
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. This table or related slice is empty.
      1. VTUpMod
    1. CODo your output rows need to be in the same order as they are on the input file? Do you expect many repetitions, or should the output file's size keep more or less the same order of magnitude as that of the input file (or is this not predictable)?
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. USrbp
    2. COThe order of the rows in the output file isn't important. For this specific case there are relatively few duplicates. Do you think the number of duplicates has bearing in the general case?
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. USJonC
    3. COIt might if, for instance, the unique rows could fit into memory (even though the full file, with the duplicated, would not). I have to leave for a while, but I'll make a suggestion later on.
      singulars
      1. PORemove duplicate rows from a large file in Python
      1. USrbp
 

Querying!

 
Guidance
A row detail

Detail views are divided into sections. All the information in the data section comes from columns in the selected row. The other sections display data from other, related rows.

Related data can be related in a to-one or a to-many fashion. Captions of data related in a to-many fashion link to a list view showing a filtered view of the table.

Try moving around until you find a non-empty to-many entry and click on the label to get to one. You can move back to the root by clicking on the database name in the header.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload