How to find duplicates in a csv column, and remove any row that doesn't have a duplicate

user_14088821 0
Viewed 258

I have a csv with data that looks like this:

777 Brockton Avenue,Abington,MA,2351
30 Memorial Drive,Avon,MA,2322
250 Hartford Avenue,Bellingham,MA,2351
700 Oak Street,Brockton,MA,2301
66-4 Parkhurst Rd,Chelmsford,MA,1824
591 Memorial Dr,Chicopee,MA,2351
55 Brooksby Village Way,Danvers,MA,2301

I want to search the last column (the one with the zipcodes), find any duplicates and remove any row that doesn't have a duplicate in that row.

So the data above would turn into:

777 Brockton Avenue,Abington,MA,2351
250 Hartford Avenue,Bellingham,MA,2351
700 Oak Street,Brockton,MA,2301
591 Memorial Dr,Chicopee,MA,2351
55 Brooksby Village Way,Danvers,MA,2301

I have tried using Pandas and a lot of other methods but I am still having a lot of trouble doing this. Does anyone have any ideas on how I could do this?

csv python-3.x python
2 Answers

You can use a dict to index rows by zip code. collections.defaultdict is nice because you don't specifically have to create a list whenever a new zip code is found. The defaultdict will use the initializer you give it (in this case the list class) in that case.

Once you have populated the dict, its values are lists grouped by zip code and any list > 1 meets your criteria.

import csv
import collections

zip_index = collections.defaultdict(list)
with open('test.csv', newline='') as fileobj:
    reader = csv.reader(fileobj)
    for row in reader:
        zip_index[row[-1]].append(row)

with open('output.csv', 'w', newline='') as fileobj:
    writer = csv.writer(fileobj)
    for rows in zip_index.values():
        if len(rows) > 1:
            writer.writerows(rows)
user_6420700

If you just have it as a textfile, as in the original post, you could just split each line and use the last element. Use a dictionary to keep track of the number of repetitions of each zip code and then loop a second time through all lines, only keeping the ones that have a zip code which occurs more than once:

lines = ["777 Brockton Avenue,Abington,MA,2351",
         "30 Memorial Drive,Avon,MA,2322",
         "250 Hartford Avenue,Bellingham,MA,2351",
         "700 Oak Street,Brockton,MA,2301",
         "66-4 Parkhurst Rd,Chelmsford,MA,1824",
         "591 Memorial Dr,Chicopee,MA,2351",
         "55 Brooksby Village Way,Danvers,MA,2301"]

## Get an overview of duplicates
zipdir = {}
for l in lines:
    try:
        zipdir[l.split(",")[-1]] +=1
    except:
        zipdir[l.split(",")[-1]] = 1

## Retain the ones with more than one occurrence
outlines = []
for l in lines:
    if zipdir[l.split(",")[-1]]>1:
        outlines.append(l)

In your case, this would remove two elements from the data. A list of lines may be obtained from a csv by just opening it as a file (infile = open("xy.csv","r")) and reading all lines as a list (lines = infile.readlines()). Similarily, the resulting list of lines may be written by just writing them as a joined string with a line feed delimiter (outfile.write("\n".join(outlines)).

No packages are required for such a task.

user_139696110
Related