Reindexing And Filling Nan Values In Pandas

Consider this dataset: data_dict = {'ind' : [1, 2, 3, 4], 'location' : [301, 301, 302, 303], 'ind_var' : [4, 8, 10, 15], 'loc_var' : [1, 1, 7, 3]} df = pd.DataFrame(data_dict) df

Solution 1:

This can be done by stack/unstack and groupby very easily:

# unstack to wide, fillna as 0s
df_wide = df_indexed.unstack().fillna(0)
# stack back to long
df_long = df_wide.stack()
# change 0s to max using groupby.
df_long['ind_var'] = df_long['ind_var'].groupby(level = 0).transform(lambda x: x.max())
df_long['loc_var'] = df_long['loc_var'].groupby(level = 1).transform(lambda x: x.max())
print df_long

This gives you the results:

                   ind_var  loc_var
ind location                  
1   301             4        1
    302             4        7
    303             4        3
2   301             8        1
    302             8        7
    303             8        3
3   301            10        1
    302            10        7
    303            10        3
4   301            15        1
    302            15        7
    303            15        3

Solution 2:

Much cleaner solution than my original. Thanks @cd98

In [41]: loc_dict = {301 : 1, 302 : 7, 303 : 3}

In [42]: ind_dict = {1 : 4, 2: 8, 3: 10}

In [198]: df2 = df2.reset_index()

In [199]: df2
Out[199]: 
   index  id  location  ind_var  loc_var
00130141111302      NaN      NaN
221303      NaN      NaN
33230181442302      NaN      NaN
552303      NaN      NaN
663301      NaN      NaN
773302107883303      NaN      NaN

In [200]: df2['ind_var'] = df2.id.map(ind_dict)

In [201]: df2['loc_var'] = df2.location.map(loc_dict)

In [202]: df2
Out[202]: 
   index  id  location  ind_var  loc_var
001301411113024722130343332301814423028755230383663301101773302107883303103

In [203]: df2 = df2.set_index(['id', 'location'])

In [204]: df2
Out[204]: 
             index  ind_var  loc_var
id location                         
130104130214730324323013813024873035833301610130271073038103