Q:

How do I import excel file to python and check column for duplicates?

I have collected some data from a system that includes just over 7000 different selections and I am hoping to import this data into python and treat column A (all of the data) as a list and then check for duplicates.
The desired outcome would to be writing all selections that are duplicates to a txt file.
I have stumbled across separate pieces of code that may do the trick but don't know how I'd compile them to create a solution to my problem.
Code for finding duplicates below - I would of course change the print results to write to file...
def FindDuplicates(in_list):  
    unique = set(in_list)  
    for each in unique:  
        count = in_list.count(each)  
        if count > 1:  
            print 'There are duplicates in this list'  
            return True  
    print 'There are no duplicates in this list'  
    return False

My struggle now is essentially replacing 'in_list' with column A of the imported excel file.
Any help or suggestions would be much appreciated.
Cheers,
Jack

A:

You can use Python's Counter to help determine if there are duplicates:
import xlrd
from collections import Counter

def FindDuplicates(in_list):
    counts = Counter(in_list)
    two_or_more = [item for item, count in counts.items() if count >= 2]
    print two_or_more
    return len(two_or_more) > 0

workbook = xlrd.open_workbook(r"input.xls")
sheet = workbook.sheet_by_index(0)
col_a = [sheet.row(row)[0].value for row in range(sheet.nrows)] # Read in all rows

print FindDuplicates(col_a)

Python by default cannot read in an .xls file so you will need to install a package to help. I have used xlrd to read in the older format .xls file. This could be installed by using:
pip install xlrd

If you are able to save your Excel file in .csv format, then the following code could be used:
import csv
from collections import Counter

def FindDuplicates(in_list):
    counts = Counter(in_list)
    two_or_more = [item for item, count in counts.items() if count >= 2]
    print two_or_more
    return len(two_or_more) > 0

with open('input.csv', 'rb') as f_input:
    col_a = [row[0] for row in csv.reader(f_input)]

print FindDuplicates(col_a)

