pandas merge dataframe with NaN (or “unknown”) for missing values

In case you are still looking for an answer for this:

The “strange” things that you described are due to some minor errors in your code. For example, the first (appearance of “bobjames” and “devsys”) is due to the fact that you don’t have a comma between those two values in your source dataframes. And the second is because pandas doesn’t care about the name of your dataframe but cares about the name of your columns when merging (you have a dataframe called “names” but also your columns are called “names”). Otherwise, it seems that the merge is doing exactly what you are looking for:

import pandas as pd
names = pd.DataFrame({'names':['bob','frank','bob','bob','bob', 'james','tim','ricardo','mike','mark','joan','joe'], 
                      'position':['dev','dev','dev','dev','dev','dev', 'sys','sys','sys','sup','sup','sup']})

info = pd.DataFrame({'names':['joe','mark','tim','frank','joe','bill'],
                     'classification':['thief','thief','good','thief','good','thief']})
what = pd.merge(names, info, how="outer")
what.fillna('unknown', inplace=True)

which will result in:

      names position classification
0       bob      dev        unknown
1       bob      dev        unknown
2       bob      dev        unknown
3       bob      dev        unknown
4     frank      dev          thief
5     james      dev        unknown
6       tim      sys           good
7   ricardo      sys        unknown
8      mike      sys        unknown
9      mark      sup          thief
10     joan      sup        unknown
11      joe      sup          thief
12      joe      sup           good
13     bill  unknown          thief