Merging two index-based data frames, replacing matching values ​​in another column

Question

I have the following wide df1:

Area geotype  type    ...
1      a        2      ...
1      a        1      ... 
2      b        4      ...
4      b        8      ...

And the following two-column df2:

Area   geotype
1      London
4      Cambridge

And I want the following:

Area  geotype  type    ...
1     London     2      ...
1     London     1      ... 
2       b        4      ...
4     Cambridge  8      ...

Therefore, I need to match based on the non-historical column Area, and then, only if there is a match, replace the set values ​​in the geotype column.

Sorry if this is a duplicate, I was really looking for a solution for this.

Answer 1

use update+map

df1.geotype.update(df1.Area.map(df2.set_index('Area').geotype))

   Area    geotype  type
0     1     London     2
1     1     London     1
2     2          b     4
3     4  Cambridge     8

Answer 2

, map Series, set_index, NaN combine_first fillna:

df1.geotype = df1.ID.map(df2.set_index('ID')['geotype']).combine_first(df1.geotype)
#df1.geotype = df1.ID.map(df2.set_index('ID')['geotype']).fillna(df1.geotype)
print (df1)
   ID    geotype type
0   1     London    2
1   2          a    1
2   3          b    4
3   4  Cambridge   8e

mask numpy.in1d:

df1.geotype = df1.geotype.mask(np.in1d(df1.ID, df2.ID),
                               df1.ID.map(df2.set_index('ID')['geotype']))
print (df1)
   ID    geotype type
0   1     London    2
1   2          a    1
2   3          b    4
3   4  Cambridge   8e

:

ID df2 :

df2 = pd.DataFrame({'ID': [1, 1, 4], 'geotype': ['London', 'Paris', 'Cambridge']})
print (df2)
   ID    geotype
0   1     London
1   1      Paris
2   4  Cambridge

, map .

drop_duplicates, :

df2 = df2.drop_duplicates('ID')
print (df2)
   ID    geotype
0   1     London
2   4  Cambridge

, :

df2 = df2.drop_duplicates('ID', keep='last')
print (df2)
   ID    geotype
1   1      Paris
2   4  Cambridge

---

, merge, , ID df2:

df1 = pd.merge(df1, df2, on='ID', how='outer', suffixes=('_',''))
df1.geotype = df1.geotype.combine_first(df1.geotype_)
df1 = df1.drop('geotype_', axis=1)
print (df1)
   ID type    geotype
0   1    2     London
1   1    2      Paris
2   2    1          a
3   3    4          b
4   4   8e  Cambridge

Answer 3

:

In [78]: df1.loc[df1.ID.isin(df2.ID), 'geotype'] = df1.ID.map(df2.set_index('ID').geotype)

In [79]: df1
Out[79]:
   ID    geotype  type
0   1     London     2
1   2          a     1
2   3          b     4
3   4  Cambridge     8

UPDATE: answers an updated question - if you have duplicates in a column Areain df2DF:

In [152]: df1.loc[df1.Area.isin(df2.Area), 'geotype'] = df1.Area.map(df2.set_index('Area').geotype)
...
skipped
...
InvalidIndexError: Reindexing only valid with uniquely valued Index objects

get rid of duplicates:

In [153]: df1.loc[df1.Area.isin(df2.Area), 'geotype'] = df1.Area.map(df2.drop_duplicates(subset='Area').set_index('Area').geotype)

In [154]: df1
Out[154]:
   Area    geotype  type
0     1     London     2
1     1     London     1
2     2          b     4
3     4  Cambridge     8