Pandas, create new column based on other columns across multiple rows

Question

Lets say I have the following dataframe representing the dietary habits of my pet frog

date       bugs_eaten_today
2019-01-31 0
2019-01-30 5
2019-01-29 6
2019-01-28 7
2019-01-27 2
...

Now I want to calculate a new column bugs_eaten_past_20_days

date       bugs_eaten_today bugs_eaten_paast_20_days
2019-01-31 0                48
2019-01-30 5                38
2019-01-29 6                57
2019-01-28 7                63
2019-01-27 2                21
...

How would I go about doing this? (Note that we don't have data for last 20 rows, so they will just be NaN)

Andy Hayden · Accepted Answer · 2019-02-06 05:12:08Z

1

You can do a rolling sum (with 20 rather than 3):

In [11]: df.bugs_eaten_today.rolling(3, 1).sum()
Out[11]:
0     0.0
1     5.0
2    11.0
3    18.0
4    15.0
Name: bugs_eaten_today, dtype: float64

You have to do this in reverse, since the index is reversed:

In [12]: df[::-1].bugs_eaten_today.rolling(3, 1).sum()
Out[12]:
4     2.0
3     9.0
2    15.0
1    18.0
0    11.0
Name: bugs_eaten_today, dtype: float64

In [13]: df['bugs_eaten_paast_20_days'] = df[::-1].bugs_eaten_today.rolling(3, 1).sum()

It's probably more robust to use date as the index and roll over 20D(ays):

In [21]: df1 = df.set_index('date').sort_index()

In [22]: df1.bugs_eaten_today.rolling('3D', 1).sum()
Out[22]:
date
2019-01-27     2.0
2019-01-28     9.0
2019-01-29    15.0
2019-01-30    18.0
2019-01-31    11.0
Name: bugs_eaten_today, dtype: float64

edited Feb 6, 2019 at 5:12

answered Feb 6, 2019 at 5:04

Andy Hayden

378k110 gold badges640 silver badges546 bronze badges

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

4 Comments

AlanSTACK Over a year ago

Thanks for the response, but what if the column I want generated depended on a more complex function, as opposed to just a simple sum. Would there be a more general way to iterate through the dataframe with a sliding 20 unit window (looking either ahead or behind)?

Andy Hayden Over a year ago

@AlanSTACK There's rolling.apply which accepts a user's function (similar to DataFrame apply): pandas.pydata.org/pandas-docs/stable/reference/api/…

AlanSTACK Over a year ago

Thank you. Before I accept your answer, do you think you could provide an example of doing what you did in your response with rolling apply - so more novice users can quickly understand what to do?

Andy Hayden Over a year ago

@AlanSTACK something like df1.bugs_eaten_today.rolling('3D', 1).apply(lambda x: x.sum(), raw=False) (though that is a silly example, you can do whatever python function - takes a Series of those (upto) 20 rows.

Collectives™ on Stack Overflow

Pandas, create new column based on other columns across multiple rows

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related