'Matching and updating a specific column in excel using Openpyxl?

I'm trying to build a tool which matches/searches a specific value in excel, Example if Name: Jay is in the list then I will run a verification and the result of it will be stored in a dictionary based on that will be also used to update the data frame.

Flow:

  1. Get the list of the names in excel. 
  2. Run a verification and the result will be stored in dictionary format. 
  3. Update the comment column by matching if the name from the result(dict) is in the excel list then update the comment section. 
  4. Update and save the excel file. 
Current:
Name, Lastname, Address, Comment
Jay, Till, 123 Partway, NA
Bob, Rossel, 555 North, NA
Dave, Cede, 982 South, NA

My Target(Update the Comment Column):
Name, Lastname, Address, Comment
Jay, Till, 123 Partway, Active
Bob, Rossel, 555 North, Need to check
Dave, Cede, 982 South, Inactive

My Code:

## OPEN THE REPORT FILE
excelnew = "Report.xlsx" #NEW FILE
dnew = pd.read_excel("Report.xlsx", sheet_name='Device Detail',  skiprows = 3)
filt_dnew = dnew[(dnew.State == 'FAILED') & (dnew.Type.isin(['Router','Switch','WLAN Controller']))]
empDfObj2 = pd.DataFrame(filt_dnew, columns=['State', 'Name','Type','Model','Last','Reason','Comments'])

## To explain.
# bures = this is the result .. a dictionary based. 
# new_devlist = just list of devices.

test = []
new_devlist = filt_dnew['Name'].tolist()

## the condition is, If device in the result then need to get the result details and put it in  excel under "Comment" Column. 

for key in bures.values():
    for x in new_devlist:
        if x in key.values():
            print("matched:",x,key["Result"])
            column_name = x
            value = empDfObj2.loc[empDfObj2.Name == x, "Comments"] = key["Result"]
            print ("Value: ",value,"\n")
        else:
            test.append("test")

How can I update the excel file using the updated DF named as empDfObj2 without creating duplicate entries? or what would be the best approach here?



Sources

This article follows the attribution requirements of Stack Overflow and is licensed under CC BY-SA 3.0.

Source: Stack Overflow

Solution Source