Replacing empty values in a DataFrame with value of a column

  • A+
Category:Languages

Say I have the following pandas dataframe:

df = pd.DataFrame([[3, 2, np.nan, 0],                     [5, 4, 2, np.nan],                     [7, np.nan, np.nan, 5],                     [9, 3, np.nan, 4]],                     columns=list('ABCD')) 

which returns this:

   A    B    C    D 0  3  2.0  NaN  0.0 1  5  4.0  2.0  NaN 2  7  NaN  NaN  5.0 3  9  3.0  NaN  4.0 

I'd like that if a np.nan is found, that the value is replaced by a value in the A column. So that would mean the result to be this:

   A    B    C    D 0  3  2.0  3.0  0.0 1  5  4.0  2.0  5.0 2  7  7.0  7.0  5.0 3  9  3.0  9.0  4.0 

I've tried multiple things, but I could not get anything to work. Can anyone help?

 


Here is necessary double transpose:

cols = ['B','C', 'D'] df[cols] = df[cols].T.fillna(df['A']).T print(df)    A    B    C    D 0  3  2.0  3.0  0.0 1  5  4.0  2.0  5.0 2  7  7.0  7.0  5.0 3  9  3.0  9.0  4.0 

because:

df[cols] = df[cols].fillna(df['A'], axis=1) print(df) 

NotImplementedError: Currently only can fill with dict/Series column by column

Another solution with numpy.where and broadcasting column A:

df = pd.DataFrame(np.where(df.isnull(), df['A'].values[:, None], df),                    index=df.index,                    columns=df.columns) print (df)      A    B    C    D 0  3.0  2.0  3.0  0.0 1  5.0  4.0  2.0  5.0 2  7.0  7.0  7.0  5.0 3  9.0  3.0  9.0  4.0 

Thank you @pir for another solution:

df = pd.DataFrame(np.where(df.isnull(), df[['A']], df),                    index=df.index,                    columns=df.columns) 

Comment

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: