Add new row for each duplicate in word doc using Python and Mailmerge

Question

Good morning All,

I wrote a script that populates a word document letter with multiple fields (ship to#, serial#, etc.) and creates a new word document for each row in my address list which contains the ship to#, serial# and customer name. My code outputs all 2000 letters for each row entry I have in my address list. However, one customer might have multiple serial# and thus might have multiple entries in the address list. The current code prints out multiple letters for the same customer for each different row/serial#.

How can I add a row for each different serial# in the same letter, instead of having multiple letters for the same customer for each different serial#? I am currently trying to create a dictionary with the duplicate customer names and their corresponding serial# and then use this dictionary in mailmerge but still not sure how. Any tips or guidance are greatly appreciated.

EDIT1: when I try to parse for duplicates using this print ([item for item, count in collections.Counter(dict_list).items() if count > 1]), I get an error message: unhashable type: 'dict'.

from __future__ import print_function
from xlrd import open_workbook
from mailmerge import MailMerge
import pandas as pd
import numpy as np
import operator
import itertools



#####################address list template            
book = open_workbook('Template3.xlsx')
sheet = book.sheet_by_index(0)

keys = [sheet.cell(0, col_index).value for col_index in range(sheet.ncols)]

dict_list = []
for row_index in range(1, sheet.nrows):
    d = {keys[col_index]: sheet.cell(row_index, col_index).value 
        for col_index in range(sheet.ncols)}
    dict_list.append(d)

###################serial number template  
book = open_workbook('serialtemp1.xlsx')
sheet = book.sheet_by_index(0)

serialkeys = [sheet.cell(0, col_index).value for col_index in range(sheet.ncols)]

serial_list = []
for row_index in range(1, sheet.nrows):
    d1 = {serialkeys[col_index]: sheet.cell(row_index, col_index).value 
        for col_index in range(sheet.ncols)}
    serial_list.append(d1)          

#serialized = map(tuple, map(sorted, map(dict.items, serial_list)))
#unique = set(serialized)
#result = list(map(dict, unique))

#print(serial_list)

######################new dict with serial as keys
key = operator.itemgetter('Ship To')
b = [{'Ship To': x, 'serial_number': str({d1['serial_number'] for d1 in y})} 
     for x, y in itertools.groupby(sorted(serial_list, key=key), key=key)]



###################create pandas df for serial list
pdframe1 = pd.DataFrame(b)

pdframe1.sort_values(by=['Ship To'])
pdframe1['serial_number']=pdframe1['serial_number'].str.replace(r"\{","")
pdframe1['serial_number']=pdframe1['serial_number'].str.replace(r"\'","")
pdframe1['serial_number']=pdframe1['serial_number'].str.replace(r"\}","")
pdframe1['Ship To'] = pdframe1['Ship To'].astype(np.int64)
pdframe1['serial_number'] = pdframe1['serial_number'].astype(str).replace('\.0', '', regex=True)

##############################33
pdframe = pd.DataFrame(dict_list)
pdframe.sort_values(by=['Ship To'])
pdframe['serial_number'] = pdframe['serial_number'].astype(str).replace('\.0', '', regex=True)
pdframe['Ship To'] = pdframe['Ship To'].astype(np.int64)

############################
pdframe = pdframe.merge(pdframe1, on = 'Ship To', how = 'right')
pdframe.sort_values(by=['Ship To'])
pdframe = pdframe.astype(str)
############3

looprange1 = range(len(pdframe1.index))

looprange = range(int(len(pdframe.index)))

for j in looprange:
    a =[{'serial_number' : pdframe1['serial_number'][k]} for k in looprange1]
    template = 'Template.docx'
    document = MailMerge(template)
    document.merge(
            ship_to1 = pdframe['Ship To'][j],
            mailing_name1 = pdframe['Mailing Name'][j],
            address_line11 = pdframe['Address Line 1'][j],
            address_line21 = pdframe['Address Line 2'][j],
            city1 = pdframe['City'][j],
            state1 = pdframe['State'][j],
            zipcode1 = pdframe['ZipCode'][j],
            ship_to2 = pdframe['Ship To'][j],
            mailing_name2 = pdframe['Mailing Name'][j],
            address_line12 = pdframe['Address Line 1'][j],
            address_line22 = pdframe['Address Line 2'][j],
            city2 = pdframe['City'][j],
            state2 = pdframe['State'][j],
            zipcode2 = pdframe['ZipCode'][j])

    document.merge_rows('serial_number', a)
    document.write(pdframe['Mailing Name'][j]+ ' ' + pdframe['Ship To'][j] +'.docx')

Answer 1

To add new rows to the word doc table, I used the function merge_rows() from docx-mailmerge library.