You can do a pandas.DataFrame.join
if you know how this works.
-- Edit: merge
is apparently a better choice: See the example at the end.
I think you need an outer
join on Keyword
.
This should give a new DataFrame, that contains unique rows for the Keyword
in both tables. Some entries may be NULL/None. This indicates that in the old or new table, the keyword was not present and you should treat is as a new keyword, or a keyword that has dropped from the list.
Rename the columns in the new table appropriately, and then apply a smart value between columns, taking into account that some values are NULL.
You can do a similar thing in Excel: https://superuser.com/questions/1023123/how-to-simulate-a-full-outer-join-in-excel
Edit:
Minimalistic example:
import pandas as pd
old = pd.DataFrame({'keyword': ['football', 'soccer', 'rugby'], 'position': [2, 1, 3]})
new = pd.DataFrame({'keyword': ['hockey', 'rugby', 'soccer'], 'position': [3, 2, 1]})
old.keyword = old.keyword.astype(str)
new.keyword = new.keyword.astype(str)
old.set_index(['keyword'])
new.set_index(['keyword'])
old = old.rename(columns={"position": "position_old"})
new = new.rename(columns={"position": "position_new"})
print(old)
print(new)
merged = pd.merge(old, new, how='outer', on='keyword')
print(merged)
Output:
keyword position_old
0 football 2
1 soccer 1
2 rugby 3
keyword position_new
0 hockey 3
1 rugby 2
2 soccer 1
keyword position_old position_new
0 football 2.0 NaN
1 soccer 1.0 1.0
2 rugby 3.0 2.0
3 hockey NaN 3.0