Q:

Python Compare and Update CSV File

I have two CSV files.
csv_1 columns:
AP Name       Mac           Switch      Port
AP-2-2    001122334455   switchname1    0/37
AP-2-3    554433221100   switchname2    0/41

csv_2 columns:
    Mac         Switch      Port
001122334455   switchname1  0/37
554433221100   switchname2  0/41

I want to update the switch and port columns in csv_1 with the switch and port in csv_2 based on when a mac address match found (these are not in order).
What is the best and most efficient way to do this properly in python? I know how to read in the CSV files, I'm just not sure how to check the values properly.

A:

You can use pandas to join in the new values:
import pandas as pd

df1 = pd.read_csv("<path to csv1>.csv")
df2 = pd.read_csv("<path to csv2>.csv").rename(columns={"Switch": "new_switch", "Port": "new_port"})

# clean up column names
df1.columns = [i.strip() for i in df1.columns]
df2.columns = [i.strip() for i in df2.columns]

# join in new values
result = df1.join(df2.set_index("Mac"), on="Mac")

# use the new values where they're defined, otherwise fill in with the old values
result["Switch"] = result["new_switch"].fillna(result["Switch"])
result["Port"] = result["new_port"].fillna(result["Port"])

# delete the "new" columns, which are no longer needed
del result["new_switch"], result["new_port"]

# write out
result.to_csv("<path to new csv>.csv", index=False)

This assumes your csv2 has only one row for each Mac value.

