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']

Timus · Accepted Answer · 2025-03-17 10:50:40Z

0

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())

edited Mar 17 at 10:50

Timus

11.4k5 gold badges20 silver badges33 bronze badges

answered Mar 17 at 6:04

Jinit Trivedi

11 bronze badge

Sign up to request clarification or add additional context in comments.

3 Comments

Timus Mar 17 at 10:51

Why are you building the column week with .apply?

Jinit Trivedi Mar 18 at 7:07

Applies the lambda function to each value in the day column. It calculates the week number for each day.

Timus Mar 18 at 8:20

Yes, I know what it does, but you should avoid .apply whenever you can (it is a performance bottle neck). Why don't you just do df['week'] = (df['day'] - 1) // 7 + 1 or df['week'] = df['day'].add(-1).floordiv(7).add(1) instead?

Collectives™ on Stack Overflow

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

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related