# Fill NaN values with values from another column

I have data that look like this:

Time Rain1Hour Rain6Hour
0    0         NaN
1    1         NaN
2    1         NaN
3    1         NaN
4    1         NaN
5    1         NaN
6    1         NaN
7    0         NaN


Where Rain1Hour is the rain in the last hour and Rain6Hour is the accumulated rain in the last 6 hours, which means I want the sum of the rain in the last 6 hours using the data from the Rain1Hour column. How do I fill the column Rain6Hour with the data from Rain1Hour. I want it to be like:

Rain6Hour
0
1
2
3
4
5
6
5


For example, the fourth row is 3 because it has been raining a quantity of 1 each hour the previous 3 hours and a quantity of 0 in the hour 0.

I am using Python and the data is in a Pandas dataframe.

EDIT: After solving the question using the rolling function that lcrmorin mentioned, I have now another one closely related to this. Is it possible to only sum over some specific rows? For example, if I am currently in time 6, I want to sum the value of the rows time=6, time=6-2, and time = 6-4, of the column Rain1Hour and assign it to another column.

• stackoverflow.com/questions/40814201/pandas-rolling-gives-nan you are looking for the rolling function and the min_period keyword Apr 5, 2023 at 12:45
• @lcrmorin Thank you! That what I was looking for. I have now a related question, do you know if it would be possible to only sum over some specific rows? For example, if I am currently in time 6, I want to sum the value of the rows time=6, time=6-2 and time = 6-4, of the column Rain1Hour. Apr 5, 2023 at 15:40
• I think you can build custom functions... but not sure how (agg key ?) Apr 5, 2023 at 15:52

Use the rolling function together with the sum function, as such:

df['Rain6Hour'] = df['Rain1Hour'].rolling(min_periods=1, window=6).sum()


I'm not sure how to use custom index lists with the .rolling() method either, but you try using the .shift() method. The option fill_value in .shift() will help you avoid NaN issue. For instance, to create a column that is sum of (time k) + (time k-2) + (time k-4):

df['0_2_4']=df['1hr']\
+df['1hr'].shift(2,fill_value=0)\
+df['1hr'].shift(4,fill_value=0)


You can do the following:

# fill missing values in Rain6Hour with shifted values from Rain1Hour
df['Rain6Hour'] = df['Rain6Hour'].fillna(df['Rain1Hour'].shift(5))

• I think that function does not do what I want, I think using that I am just putting the values of the column 'Rain1Hour' in 'Rain6Hour' but with a delay of 5, what I want is, for each row, sum every value in the current and the previous 5 rows of the column 'Rain1Hour' and write it in 'Rain6Hour'. Apr 5, 2023 at 15:11