I want to assign a value in "Value" column into a new column "new_value" and up to all next empty rows until next value comes. My expected value is given in a new column 'Expected_Value' for referance.
import pandas as pd
import numpy as np
df2 = pd.DataFrame({ 'Value':['Second', np.nan, np.nan, 'Fourth', np.nan],
'Name':['John', 'Tom', 'Tom', 'Tom','One'],
'Expected_Value':['Second', 'Second', 'Second', 'Fourth', 'Fourth']
})
I tried this using two functions (apply lambda & assign lambda), however, there were error messages as follows.
Using apply lambda method
df2['new_value'] = np.nan
df2['new_value'] = df2.apply(lambda row: row['Value'] if (~pd.isna(row['Value'])) else row['new_value'].shift(-1))
# KeyError: 'Value'
Using assign Lambda method
del df2['new_value']
df2 = df2.assign(new_value=lambda x: (x['Value']) if (~(x['Value'].isna())) else x['new_value'].shift(-1))
# ValueError: The truth value of a Series is ambiguous. Use a.empty, a.bool(), a.item(), a.any() or a.all()
Appreciate it if someone can help.