Mapping a dictionary to NAN rows of a column in Panda

user_5782481 0
Viewed 174

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

enter image description here

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 .

pandas python-2.7 dataframe dictionary python
2 Answers

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))
user_94907690

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
user_158639790
Related