Creating rolling average in pandas dataset for multiple columns

C. Cooney 0
Viewed 26

I have a pandas dataset with multiple columns of data as follows:

code    country_region  date        retail  transit work    res
CA      Canada          2020-02-15   4       3      1        0
CA      Canada          2020-02-16   13      4      0       -2
CA      Canada          2020-02-17  -12     -28    -52       11
CA      Canada          2020-02-18  -1      -1     -1        1
CA      Canada          2020-02-19   1       0      0        0
CA      Canada          2020-02-20   6      -1      1        0
CA      Canada          2020-02-21   2      -1     -3        1
CA      Canada          2020-02-22   8       6      6       -1
CA      Canada          2020-02-23   7      -5      5        6
...

US      United States   2020-02-15   6       3      1        0
US      United States   2020-02-16   13      4      0       -2
...

My objective is to calculate a 7 day average for each of the numeric values within each code / country_region value (I have confirmed there are no missing dates in the data). This would be quite easy in Excel using a ranged average, but unfortunately my table has a very high number of records.

I would also first need to convert my date field to a date object since the date is text. I could perform this utilizing a loop, but it seems unwieldy to retain the "last 6 days" for each of the numeric columns. I have seen the df.rolling() function, but there are few examples of this applied with a group_by statement, and few examples with multiple columns. Is there a python (i.e. non-looped) way to perform this summation?

Edit: I've converted the date column to a proper date field using:

df['date_fmt'] = pd.to_datetime(df['date']).apply(lambda t: datetime.datetime.fromisoformat(str(t)))
group-by pandas python
2 Answers
df = df.assign(date=pd.to_datetime(df.date)).set_index('date').groupby(['code','country_region']).rolling('7D').mean().reset_index()
print(df)

   code country_region       date    retail   transit       work       res
0    CA         Canada 2020-02-15  4.000000  3.000000   1.000000  0.000000
1    CA         Canada 2020-02-16  8.500000  3.500000   0.500000 -1.000000
2    CA         Canada 2020-02-17  1.666667 -7.000000 -17.000000  3.000000
3    CA         Canada 2020-02-18  1.000000 -5.500000 -13.000000  2.500000
4    CA         Canada 2020-02-19  1.000000 -4.400000 -10.400000  2.000000
5    CA         Canada 2020-02-20  1.833333 -3.833333  -8.500000  1.666667
6    CA         Canada 2020-02-21  1.857143 -3.428571  -7.714286  1.571429
7    CA         Canada 2020-02-22  2.428571 -3.000000  -7.000000  1.428571
8    CA         Canada 2020-02-23  1.571429 -4.285714  -6.285714  2.571429
9    US  United States 2020-02-15  6.000000  3.000000   1.000000  0.000000
10   US  United States 2020-02-16  9.500000  3.500000   0.500000 -1.000000
Алексей Р0

IIUC, you can use pandas.DataFrame.rolling for this:

df['date'] = pd.to_datetime(df.date)
df.set_index('date').groupby('code').rolling('7D').mean()

Output:

                   retail   transit       work       res
code date                                               
CA   2020-02-15  4.000000  3.000000   1.000000  0.000000
     2020-02-16  8.500000  3.500000   0.500000 -1.000000
     2020-02-17  1.666667 -7.000000 -17.000000  3.000000
     2020-02-18  1.000000 -5.500000 -13.000000  2.500000
     2020-02-19  1.000000 -4.400000 -10.400000  2.000000
     2020-02-20  1.833333 -3.833333  -8.500000  1.666667
     2020-02-21  1.857143 -3.428571  -7.714286  1.571429
     2020-02-22  2.428571 -3.000000  -7.000000  1.428571
     2020-02-23  1.571429 -4.285714  -6.285714  2.571429
US   2020-02-15  6.000000  3.000000   1.000000  0.000000
     2020-02-16  9.500000  3.500000   0.500000 -1.000000
T C Molenaar0
Related