Search code examples
pythonfor-loopcomparison

Iterating through two tables to find common values, and if it exists replace value in one column

I have two tables , TABLE1 AND TABLE2. One with just Project IDs and another with Project ID and Task ID. I want to iterate through TABLE1 and compare it to TABLE2 Projects IDs. If there is a common value, I want to attach the Task ID corresponding to that particular Project ID to TABLE1 Project ID.

The way I did this is as follows.

for index,i in enumerate(TABLE1["ProjectID"]):

  if i in TABLE2["ProjectID"].values:

    TABLE1["TaskId"][index] =TABLE2["TaskId"].loc[TABLE2["TaskId"]==i].values[0]
  else:        
    print("Doesnt exist")

Is there a better way of doing this? It took me ages to do this, even if its just a few line of code! Thankyou!

Solution

  • use merge

    new_table = pd.merge(Table1,Table2,how='left',on='ProjectID')

    go through docs