Pretty new to Python, but as an SEO I'm looking at the benefits of using notebooks in my workflow.
I've got two excel files which I've cleaned and imported into a new notebook using pandas.
I'm trying to compare position changes and create a new dataframe with new columns to show previous, new, and changes in positions.
Have a look at the screengrabs[! of the data below. Thanks in advance.
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 