Looping through a dataframe column to replace NaN values with values that aren't null:

Question

The three columns on the left are day month, and year. I am trying to fill in NaN values in the last column which I am calling 'C'. For each week of each month there is one non-empty value in the last column, and I would like to assign the NaN values with the non-empty value.

So far I have tried doing it with the first week with the following code:

for year in range(2013, 2023):
  for month in range(1, 13):
    for day in range(1, 8):
      df.loc[pd.isnull(df['C']), 'C'] = df.loc[(df['year'] == year) & (df['month'] == month) & (df['day'] == 3), 'C']

Answer 1

Build a column week and then use grouping over the columns year, month and week and use .ffill and .bfill:

df['week'] = df['day'].apply(lambda x: (x - 1) // 7 + 1)  # Assign week numbers
df['C'] = df.groupby(['year', 'month', 'week'])['C'].transform(lambda x: x.ffill().bfill())