'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:
- Get the list of the names in excel.
- Run a verification and the result will be stored in dictionary format.
- Update the comment column by matching if the name from the result(dict) is in the excel list then update the comment section.
- 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 |
|---|
