Suppose I have the following data frame df:
date symbol_a symbol_b ratio
0 2017/01/01 AAAA AA 10
1 2017/01/02 AAAA AA 20
2 2017/01/03 AAAA AA 30
3 2017/01/04 AAAA AA 10
4 2017/01/05 AAAA AA 10
5 2017/01/06 AAAA AA 10
6 2017/01/01 BBBB BB 10
7 2017/01/02 BBBB BB 20
8 2017/01/03 BBBB BB 30
9 2017/01/04 BBBB BB 10
10 2017/01/01 CCCC CC 10
11 2017/01/02 CCCC CC 20
12 2017/01/03 CCCC CC 30
13 2017/01/04 CCCC CC 10
14 2017/01/05 CCCC CC 10
15 2017/01/06 CCCC CC 5
I am interested in mean values of the ratio column (this comes from the previous data frame that had two additional columns value_a value_b and ratio = value_a/value_b, more or less). What I would like to do is the following:
to take the mean values by symbol_a (or _b actually is the same) every n elements. Let's say n=3.
Ordinarely I would do something like:
df.groupby(['symbol_a','symbol_b']).mean()
However, I would like to get sub-means every 3 days (the actual time span obviously is much longer and I would need it every 5).
Initially I thought I would have always the same number of symbols, divisible by n, and so I tried something like:
df.groupby([df.index/n, 'symbol_a', 'symbol_b']).mean().reset_index()
This was quite working but strongly relied on the assumption of divisibility by n. Unfortunately not only that is an assumption I'd like to drop, but I also noticed that not all the symbols come out n times: notice for example that symbol_a "BBBB" only appear four times (days). This of course makes the above attempt completely unreliable as it will mix ratio values of different symbols.
To recap, I would need something that let me have the mean of the ratio column every n elements, and if the number of symbols is not divisible by n, it gets the mean every n and in the end the mean of the reminder (if number_of_symbols < n just the mean on such number).
For example the result would look something like (supposing n=3):
symbol_a symbol_b 3_mean_ratio
0 AAAA AA 20
1 AAAA AA 10
2 BBBB BB 20
4 BBBB BB 10
5 CCCC CC 20
6 CCCC CC 8.33
Is there any way to do something like this? Any help is very appreciated, thank you.
EDIT: Thanks for the answers so far. One last thing that would be ideal is an easy way to put the new n-days-ratio column next to the original one. Of course the length of this new one is shorter but I would not mind if the elements are repeated. Basically I need a way to select "good rows" based on this average. So far I'm creating a dictionary as a middle step but I'm sure there is a better way. If I could obtain something like below would be great:
date symbol_a symbol_b ratio n-days-ratio
0 2017/01/01 AAAA AA 10 20
1 2017/01/02 AAAA AA 20 20
2 2017/01/03 AAAA AA 30 20
3 2017/01/04 AAAA AA 10 10
4 2017/01/05 AAAA AA 10 10
5 2017/01/06 AAAA AA 10 10
6 2017/01/01 BBBB BB 10 20
7 2017/01/02 BBBB BB 20 20
8 2017/01/03 BBBB BB 30 20
9 2017/01/04 BBBB BB 10 10
10 2017/01/01 CCCC CC 10 20
11 2017/01/02 CCCC CC 20 20
12 2017/01/03 CCCC CC 30 20
13 2017/01/04 CCCC CC 10 8.3
14 2017/01/05 CCCC CC 10 8.3
15 2017/01/06 CCCC CC 5 8.3