Mapping a dictionary to NAN rows of a column in Panda-CodePudding

Here as shown below is a data frame , where in a column col2 many nan's are there , i want to fill that only nan value the col1 as key from dictionary dict_map and map those value in col2.

Reproducible code:

    import pandas as pd
    import numpy as np
    dict_map = {'a':45,'b':23,'c':97,'z': -1}
    df =  pd.DataFrame()
    df['tag'] = [1,2,3,4,5,6,7,8,9,10,11]
    df['col1'] = ['a','b','c','b','a','a','z','c','b','c','b']
    df['col2'] = [np.nan,909,34,56,np.nan,45,np.nan,11,61,np.nan,np.nan]
    df['_'] = df['col1'].map(dict_map)

Expected Output

One of the Method is :

df['col3'] =  np.where(df['col2'].isna(),df['_'],df['col2'])
df

Just wanted to know any other method using function and map function , we can optimize this .

CodePudding user response：

You can map col1 with your dict_map and then use that as input to fillna, as follows

df['col3'] = df['col2'].fillna(df['col1'].map(dict_map))

CodePudding user response：

You can achieve the very same result just using list comprehension, it is a very pythonic solution and I believe it holds better performance.

We are just reading col2 and copying the value to col3 if its not NaN. Then, if it is, we look into Col1, grab the dict key and, instead, use the corresponding value from dict_map.

df['col3'] = [df['col2'][idx] if not np.isnan(df['col2'][idx]) else dict_map[df['col1'][idx]] for idx in df.index.tolist()]

Output:

df

   tag  col1     col2    col3
 0   1     a      NaN    45.0
 1   2     b    909.0   909.0
 2   3     c     34.0    34.0
 3   4     b     56.0    56.0
 4   5     a      NaN    45.0
 5   6     a     45.0    45.0
 6   7     z      NaN    -1.0
 7   8     c     11.0    11.0
 8   9     b     61.0    61.0
 9  10     c      NaN    97.0
10  11     b      NaN    23.0