CODEWITHSUNDEEP
pythonpandas
Stephenie Liu
Stephenie Liu

Reputation: 69

pandas how to calculate yesterday's data and use it in today's data calculation?

This is my df: ts is the timestamp, the index. x1 is the value 

                      x1     
ts      
2017-09-01 17:22:42   7.0    
2017-09-01 17:22:53   11.0   
2017-09-01 17:23:04   9.0    

2017-09-02 17:23:15   15.0   

2017-09-03 17:23:26   13.0   
2017-09-03 17:23:38   19.0   
2017-09-03 17:23:49   13.0   

2017-09-04 17:24:00   15.0

I want a column of value that equal to yesterday mean + today's mean:

                      x1     result
ts      
2017-09-01 17:22:42   7.0     (7+11+9) /3
2017-09-01 17:22:53   11.0    (7+11+9) /3
2017-09-01 17:23:04   9.0     (7+11+9) /3

2017-09-02 17:23:15   15.0    (7+11+9) /3 + 15/1

2017-09-03 17:23:26   13.0    15/1 + (13+19+13)/3
2017-09-03 17:23:38   19.0    15/1 + (13+19+13)/3
2017-09-03 17:23:49   13.0    15/1 + (13+19+13)/3

2017-09-04 17:24:00   15.0    15/1 + (13+19+13)/3

If there is no yesterday's data then use 0

Upvotes: 3

Views: 1062

Answers (2)

BENY
BENY

Reputation: 323236

This I consider , the date 2017-09-02 is missing 

df['group']=pd.to_datetime(df.index)
df['group']=df['group'].dt.date
df['meanval']=df.groupby('group').x1.transform('mean')
id1=pd.Series(pd.date_range(df.group.min(),df.group.max(),freq='D')).dt.date.to_frame(name ='group')
idx=pd.concat([df,id1[~id1.group.isin(df.group)]],axis=0).sort_values('group').fillna(0)
idx=idx.drop_duplicates(['group']).rolling(2).sum().fillna(9).set_index('group')
df.meanval=df.group.map(idx.meanval)


df
Out[680]: 
                     x1       group  meanval
ts                                          
2017-09-01 17:22:42   7  2017-09-01      9.0
2017-09-01 17:22:53  11  2017-09-01      9.0
2017-09-01 17:23:04   9  2017-09-01      9.0
2017-09-03 17:23:26  13  2017-09-03     15.0
2017-09-03 17:23:38  19  2017-09-03     15.0
2017-09-03 17:23:49  13  2017-09-03     15.0
2017-09-04 17:24:00  15  2017-09-04     30.0

Data Input :

df
Out[682]: 
                     x1
ts                     
2017-09-01 17:22:42   7
2017-09-01 17:22:53  11
2017-09-01 17:23:04   9
2017-09-03 17:23:26  13
2017-09-03 17:23:38  19
2017-09-03 17:23:49  13
2017-09-04 17:24:00  15

Upvotes: 1

piRSquared
piRSquared

Reputation: 294258

Use pd.merge_asof, pd.DataFrame.resample, and pd.DataFrame.rolling

pd.merge_asof(
    df,
    df.resample('D').mean().rolling(2, 1).sum().rename(columns={'x1': 'result'}),
    left_index=True, right_index=True
)

                       x1  result
ts                               
2017-09-01 17:22:42   7.0     9.0
2017-09-01 17:22:53  11.0     9.0
2017-09-01 17:23:04   9.0     9.0
2017-09-02 17:23:15  15.0    24.0
2017-09-03 17:23:26  13.0    30.0
2017-09-03 17:23:38  19.0    30.0
2017-09-03 17:23:49  13.0    30.0
2017-09-04 17:24:00  15.0    30.0

Upvotes: 6

Related Questions