CODEWITHSUNDEEP
pythonpython-3.xpandas
ardms
ardms

Reputation: 170

Pandas resample based on higher resolution data

I have two time-series one on 30 min resolution and one on 15 minute resolution A and B as shown under. I would like to Upsample A to a 15 minute resolution using B to scale the values for the given interval. So for the first value it would be: 

B['final']['01/11/2017  07:30:00'] =  77.0*29.7/(29.7+12.67)

A['irrad']

2017-11-01 07:30:00     77.0
2017-11-01 08:00:00    214.0
2017-11-01 08:30:00    470.0
2017-11-01 09:00:00    714.0

B['util']

2017-11-01 07:15:00     12.67
2017-11-01 07:30:00     29.70
2017-11-01 07:45:00     46.80
2017-11-01 08:00:00     74.07
2017-11-01 08:15:00    166.27
2017-11-01 08:30:00    256.50
2017-11-01 08:45:00    271.70
2017-11-01 09:00:00    354.33

So the final series would be

B['final']

01/11/2017 07:30    54.0
01/11/2017 07:45    82.9
01/11/2017 08:00    131.1
01/11/2017 08:15    184.8
01/11/2017 08:30    285.2

I was looking into Pandas resample or merge functions but could see this being possible. Any ideas?

Upvotes: 1

Views: 593

Answers (1)

Ami Tavory
Ami Tavory

Reputation: 76316

Say you upsample, then left-merge to util:

A.index = pd.to_datetime(A.index)
B.index = pd.to_datetime(B.index)
merged = pd.merge(B, A.resample('15s').ffill(), left_index=True, right_index=True, how='left')
>>> merged

util    irrad
index       
2017-11-01 07:15:00 12.67   NaN
2017-11-01 07:30:00 29.70   77.0
2017-11-01 07:45:00 46.80   77.0
2017-11-01 08:00:00 74.07   214.0
2017-11-01 08:15:00 166.27  214.0
2017-11-01 08:30:00 256.50  470.0
2017-11-01 08:45:00 271.70  470.0
2017-11-01 09:00:00 354.33  714.0

Now you can use

>>> merged.irrad * merged.util / (merged.util + merged.util.shift(-1))
index
2017-11-01 07:15:00           NaN
2017-11-01 07:30:00     29.894118
2017-11-01 07:45:00     29.813850
2017-11-01 08:00:00     65.952318
2017-11-01 08:15:00     84.163446
2017-11-01 08:30:00    228.237410
2017-11-01 08:45:00    203.982237
2017-11-01 09:00:00           NaN
dtype: float64

Note that this isn't what you specified in your question. Feel free to clarify how you got to your final results.

Upvotes: 1

Related Questions