How to fill nan values in a column if the value from another column matches

user_14073111 0
Viewed 129

Lets say i have a dataframe like this one:

      col1         col2     col3
0     data1         0        NaN
1     data1         0        NaN
2     data1         1        Done
3     data2         0        NaN
4     data2         1      To be done
5     data3         0        NaN
6     data3         1        Fail

How can i replace nan values in col3 for example: data1 in col1 hasa a row in col3 that is 'Done', how can i pass this value to all NaN rows in col3 which contains data1 in col1?

Desirable df would look like this:

      col1         col2      col3
0     data1         0        Done
1     data1         0        Done
2     data1         1        Done
3     data2         0      To be done
4     data2         1      To be done
5     data3         0        Fail
6     data3         1        Fail
pandas dataframe python-3.x python
2 Answers

Use groupby + bfill:

df['col3'] = df.groupby('col1')['col3'].bfill()
print(df)

# Output:
    col1  col2        col3
0  data1     0        Done
1  data1     0        Done
2  data1     1        Done
3  data2     0  To be done
4  data2     1  To be done
5  data3     0        Fail
6  data3     1        Fail
user_152399510

one way is to create a dict and map it over col1:

d = dict(df[pd.notna(df['col3'])][['col1', 'col3']].values)
df['col3'] = df['col1'].map(d)
col1 col2 col3
0 data1 0 Done
1 data1 0 Done
2 data1 1 Done
3 data2 0 To be done
4 data2 1 To be done
5 data3 0 Fail
6 data3 1 Fail
user_49089000
Related