Python/Pandas: dataframe merge and fillna

Question

I try to merge two Pandas dataframes based on date and then ffill NaN values until specific date. I have the following data example:

df_1:

date	value1
01/12	10
02/12	20
03/12	30
04/12	40
05/12	60
06/12	70
07/12	80

df_2:

date	value2
01/12	100
03/12	300
05/12	500

I use the following line: df = pd.merge((df_1,df_2, how='left', on=['date']

I get this:

date	value1	value2
01/12	10	100
02/12	20	NaN
03/12	30	300
04/12	40	Nan
05/12	50	500
06/12	60	NaN
07/12	70	NaN

What I want to achieve then is to forwardfill the NaN values in df['value2'] until 05/12 and not until 07/12.

Answer 1

First, convert date to datetime format to use conditional operand. It will return YYYY-MM-DD by default.

Next, create a mask for your condition ffill to 05/12. and use loc for fillna.

Lastly, convert back date from datetime back to string

df['date'] = pd.to_datetime(df["date"], format="%d/%m")
mask = (df["date"].lt(pd.to_datetime('05/12', format="%d/%m")))
df.loc[mask, "val2"] = df.loc[mask, "val2"].fillna(method="ffill")
df['date'] = df['date'].dt.strftime('%d/%m')