2

I have a dataframe that has duplicated time indices and I would like to get the mean across all for the previous 2 days (I do not want to drop any observations; they are all information that I need). I've checked pandas documentation and read previous posts on Stackoverflow (such as Apply rolling mean function on data frames with duplicated indices in pandas), but could not find a solution. Here's an example of how my data frame look like and the output I'm looking for. Thank you in advance.

data:

import pandas as pd
df = pd.DataFrame({'id': [1,1,1,2,3,3,4,4,4],'t': [1, 2, 3, 2, 1, 2, 2, 3, 4],'v1':[1, 2, 3, 4, 5, 6, 7, 8, 9]})

output:

t v2
1 -
2 -
3 4.167
4 5
5 6.667
wz1055
  • 21
  • 4

2 Answers2

0

A rough proposal to concatenate 2 copies of the input frame in which values in 't' are replaced respectively by values of 't+1' and 't+2'. This way, the meaning of the column 't' becomes "the target day".

Setup:

import pandas as pd
df = pd.DataFrame({'id': [1,1,1,2,3,3,4,4,4],
                   't': [1, 2, 3, 2, 1, 2, 2, 3, 4],
                   'v1':[1, 2, 3, 4, 5, 6, 7, 8, 9]})

Implementation:

len = df.shape[0]
incr = pd.DataFrame({'id': [0]*len, 't': [1]*len, 'v1':[0]*len}) # +1 in 't'
df2 = pd.concat([df + incr, df + incr + incr]).groupby('t').mean()
df2 = df2[1:-1] # Drop the days that have no full values for the 2 previous days 
df2 = df2.rename(columns={'v1': 'v2'}).drop('id', axis=1)

Output:

         v2 
t          
3  4.166667 
4  5.000000 
5  6.666667
Eric Marchand
  • 619
  • 3
  • 10
0

Thank you for all the help. I ended up using groupby + rolling (2 Day), and then drop duplicates (keep the last observation).

Dharman
  • 30,962
  • 25
  • 85
  • 135
wz1055
  • 21
  • 4
  • Please don't add "thank you" as an answer. Instead, **[accept the answer](https://stackoverflow.com/help/accepted-answer)** that you found most helpful. - [From Review](/review/late-answers/31113756) – Simas Joneliunas Feb 24 '22 at 02:29