[Python] Python merging two dataframes with overlapping components and keeping original order...

I have two dataframes like below. Some of the area1_name and area2_name overlap, and I'm trying to combine the two area names into one long list.

df1 = pd.DataFrame({'area1_index': [0,1,2,3,4,5], 'area1_name': ['AL','AK','AZ','AR','CA','CO']})

df2 = pd.DataFrame({'area2_index': [0,1,2,3,4,5,6], 'area2_name': ['MN','AL','CT','TX','AK','AR','CA']})


What I want eventually is this:

final = pd.DataFrame({'area1_index': [nan,0,nan,nan,1,2,3,4,5], 'area1_name': [nan,'AL',nan,nan,'AK','AZ','AR','CA','CO'], 'area2_index': [0,1,2,3,4,nan,5,6,nan], 'area2_name':['MN','AL','CT','TX','AK',nan,'AR','CA',nan]})


My first thought was to identify the overlapping area names, join the overlapping dataframe and the missing dataframe, like below:

df1_df2_overlap = pd.DataFrame({'area1_index': [0,1,3,4], 'area2_index': [1,4,5,6], 'area1_name': ['AL','AK','AR','CA']})
df2_missing = pd.DataFrame({'area2_index': [0,2,3], 'area2_name': ['MN','CT','TX']})

df3 = pd.merge(df1, df2, "outer")
df4 = pd.merge(df3, df2_missing, "outer")


But this sorts everything by area2_index. I tried adding the .sort_values() argument using the by = ['seq2_index', 'seq1_index'] too but had the same result. How can I order this the way I want? Or is there a better way to combine df1 and df2 without having to identify the overlapping/missing components?

Continue reading...