Counting repeated (duplicated) in a list using dictionary
<p>I'm writing a program to identify repeated values and their count in a particular column (named 'StrId') in an Excel spreadsheet. Besides finding repetitions, I need to know how many times each value is repeated.</p>

<p>The Excel data was processed as a list of dictionaries (one dictionary per row) with headers as keys and data as values, like [{'StrId' : 1, 'ProjId' : 358}][{'StrId' : 2, 'ProjId' : 984...}] etc.</p>

<p>My plan was to first identify the 'StrId' keys in each dictionary, put them in a list, and then create another dictionary within that list to pass values and separate when there is more than 1 value, counting those that show up more than once.</p>

<p>Here is my code. Right now, it shows a 'KeyError' message with the fist value, and stops.</p>

<p>I'd appreciate any help. Thanks in advance</p>

<pre><code>from openpyxl import load_workbook
workbook = load_workbook('./fullallreadyconversionxmlclean4.xlsx')
sheet = workbook['Full-All']
headers = ["StrId", "ProjectId", "TweetText", "Label"]

excel_data = []
for row_num, row in enumerate(sheet):
    if row_num is 0:
        continue
    row_data = {}
    for col_num, cell in enumerate(row):
        if col_num &gt; len(headers) - 1:
            continue
        key = headers[col_num]
        value = cell.value
        row_data[key] = value
    excel_data.append(row_data)    


for row in excel_data:
    for key in row:    
        if key is 'StrId':
            value = row[key]
            list_ids = []
            list_ids.append(value)

            dup_dic = {}           
            for  value in list_ids:
                if value in list_ids:
                    dup_dic[value] +=1
                else:
                    dup_dic[value] =1                

                print dup_dic
</code></pre>

<p>You can use Python's <a href="https://docs.python.org/2/library/collections.html#collections.Counter" rel="nofollow"><code>Counter</code></a> for this. I'm assuming your <code>excel_data</code> is structured as a list of lists with one dictionary per list, but let me know if it's not the case.</p>

<pre><code>from collections import Counter

excel_data = [
    [{'StrId': 1, 'ProjId': 358}],
    [{'StrId': 2, 'ProjId': 984}],
    [{'StrId': 2, 'ProjId': 984}],
    [{'StrId': 2, 'ProjId': 984}],
]

# create a list of all values
flattened_values = [list_dict[0]['StrId'] for list_dict in excel_data]

# pass them to counter to get a dict of value to count
counter = Counter(flattened_values)  # Counter({2: 3, 1: 1})

# use dictionary comprehension to create a dict from this counter with only
# values with count &gt; 1 to find duplicates
repetitions = {
    val: count for val, count in counter.iteritems() if count &gt; 1
}  # {2: 3}
</code></pre>

python excel dictionary duplicates