0
$\begingroup$

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.

$\endgroup$
3
  • 1
    $\begingroup$ stackoverflow.com/questions/40814201/pandas-rolling-gives-nan you are looking for the rolling function and the min_period keyword $\endgroup$ Commented Apr 5, 2023 at 12:45
  • $\begingroup$ @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. $\endgroup$
    – Kareit
    Commented Apr 5, 2023 at 15:40
  • $\begingroup$ I think you can build custom functions... but not sure how (agg key ?) $\endgroup$ Commented Apr 5, 2023 at 15:52

3 Answers 3

0
$\begingroup$

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

df['Rain6Hour'] = df['Rain1Hour'].rolling(min_periods=1, window=6).sum()
$\endgroup$
0
$\begingroup$

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)
$\endgroup$
-1
$\begingroup$

You can do the following:

# fill missing values in Rain6Hour with shifted values from Rain1Hour
df['Rain6Hour'] = df['Rain6Hour'].fillna(df['Rain1Hour'].shift(5))
$\endgroup$
1
  • $\begingroup$ 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'. $\endgroup$
    – Kareit
    Commented Apr 5, 2023 at 15:11

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.