Pandas merge on variable columns

Question

I have a table of sites with a land cover class and a state. I have another table with values linked to class and state. In the second table, however, some of the rows are linked only to class:

sites = pd.DataFrame({'id': ['a', 'b', 'c'],
                      'class': [1, 2, 23],
                      'state': ['al', 'ar', 'wy']})

values = pd.DataFrame({'class': [1, 1, 2, 2, 23],
                       'state': ['al', 'ar', 'al', 'ar', None],
                       'val': [10, 11, 12, 13, 16]})

I'd like to link the tables by class and state, except for those rows in the value table for which state is None, in which case they would be linked only by class.

A merge has the following result:

combined = sites.merge(values, how='left', on=['class', 'state'])

  id  class state   val
0  a      1    al  10.0
1  b      2    ar  13.0
2  c     23    wy   NaN

But I'd like val in the last row to be 16. Is there an inexpensive way to do this short of breaking up both tables, performing separate merges, and then concatenating the result?

Answer 1

How about merge them separately:

pd.concat([sites.merge(values, on=['class','state']),
           sites.merge(values[values['state'].isna()].drop('state',axis=1),
                       on=['class'])
          ])

Output:

  id  class state  val
0  a      1    al   10
1  b      2    ar   13
0  c     23    wy   16

Answer 2

We can use combine_first here:

(sites.set_index(['class','state'])
  .combine_first(values.set_index(['class','state']))
  .dropna().reset_index())

---

   class state id   val
0      1    al  a  10.0
1      2    ar  b  13.0
2     23    wy  c  16.0