Pandas, replace NaNs with values from MultiIndex DataFrame

Question

Problem

I have a dataframe with some NaNs that I am trying to fill intelligently based off values from another dataframe. I have not found an efficient way to do this but I suspect there is a way with pandas.

Minimal Example

index1 = [1, 1, 1, 2, 2, 2]
index2 = ['a', 'b', 'a', 'b', 'a', 'b']
# dataframe to fillna
df = pd.DataFrame(
     np.asarray([[np.nan, 90, 90, 100, 100, np.nan], index1, index2]).T, 
     columns=['data', 'index1', 'index2']
)
# dataframe to lookup fill values from
multi_index = pd.MultiIndex.from_product([sorted(list(set(index1))), sorted(list(set(index2)))])
fill_val_lookup = pd.DataFrame([89, 91, 99, 101], index=multi_index, columns= 
['fill_vals'])

Starting data (df):

  data index1 index2
0  nan      1      a
1   90      1      b
2   90      1      a
3  100      2      b
4  100      2      a
5  nan      2      b

Lookup table to find values to fill NaNs:

     fill_vals
1 a         89
  b         91
2 a         99
  b        101

Desired output:

  data index1 index2
0   89      1      a
1   90      1      b
2   90      1      a
3  100      2      b
4  100      2      a
5  101      2      b

Ideas

The closest post I have found is about filling NaNs with values from one level of a multiindex.

I've also tried setting the index of df to be a multiindex using columns index1 and index2 and then using df.fillna, however this does not work.

DYZ · Accepted Answer · 2019-02-07 17:27:03Z

1

combine_first is the function that you need. But first, update the index names of the other dataframe.

fill_val_lookup.index.names = ["index1", "index2"]
fill_val_lookup.columns = ["data"]

df.index1 = df.index1.astype(int)
df.data = df.data.astype(float)

df.set_index(["index1","index2"]).combine_first(fill_val_lookup)\
  .reset_index()
#   index1 index2   data
#0       1      a   89.0
#1       1      a   90.0
#2       1      b   90.0
#3       2      a  100.0
#4       2      b  100.0
#5       2      b  101.0

edited Feb 7, 2019 at 17:27

answered Feb 7, 2019 at 17:15

DYZ

57.3k10 gold badges73 silver badges101 bronze badges

Sign up to request clarification or add additional context in comments.

Collectives™ on Stack Overflow

Pandas, replace NaNs with values from MultiIndex DataFrame

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related