CODEWITHSUNDEEP
pythonpandas
Gopal Chitalia
Gopal Chitalia

Reputation: 462

How to divide 60 mins datapoints into 15 mins?

I have a dataset with every 60 mins interval value. Now, I want to divide them into 15mins interval using the averages between those 2 hourly values. How do I do that?

Time                 A                    
2016-01-01 00:00:00  1  
2016-01-01 01:00:00  5  
2016-01-01 02:00:00  13

So, I now want it to be in 15mins interval with average values:

Time                      A      
2016-01-01 00:00:00       1
2016-01-01 00:15:00       2   ### at 2016-01-01 00:00:00 values is 1 and
2016-01-01 00:30:00       3   ### at 2016-01-01 01:00:00 values is 5. 
2016-01-01 00:45:00       4   ### Therefore we have to fill 4 values ( 15 mins interval ) 
2016-01-01 01:00:00       5   ### with the average of the hour values.
2016-01-01 01:15:00       7
2016-01-01 01:30:00       9
2016-01-01 01:45:00       11
2016-01-01 02:00:00       13

I tried resampling it with mean to 15 mins but it won't work ( obviously ) and it given Nan values. Can anyone help me out? on how to do it?

Upvotes: 0

Views: 277

Answers (3)

Erfan
Erfan

Reputation: 42916

We can do this in one line with resample, replace and interpolate:

df.resample('15min').sum().replace(0, np.NaN).interpolate()

Output

                        A
Time                     
2016-01-01 00:00:00   1.0
2016-01-01 00:15:00   2.0
2016-01-01 00:30:00   3.0
2016-01-01 00:45:00   4.0
2016-01-01 01:00:00   5.0
2016-01-01 01:15:00   7.0
2016-01-01 01:30:00   9.0
2016-01-01 01:45:00  11.0
2016-01-01 02:00:00  13.0

Upvotes: 3

ivallesp
ivallesp

Reputation: 2222

I would just resample: df.resample("15min").interpolate("linear")

As you have the column Time set as index already, it should directly work

Upvotes: 3

javidcf
javidcf

Reputation: 59731

You can do that like this:

import pandas as pd

df = pd.DataFrame({
    'Time': ["2016-01-01 00:00:00", "2016-01-01 01:00:00", "2016-01-01 02:00:00"],
    'A': [1 , 5, 13]
})
df['Time'] = pd.to_datetime(df['Time'])
new_idx = pd.DatetimeIndex(start=df['Time'].iloc[0], end=df['Time'].iloc[-1], freq='15min')
df2 = df.set_index('Time').reindex(new_idx).interpolate().reset_index()
df2.rename(columns={'index': 'Time'}, inplace=True)
print(df2)
#                  Time     A
# 0 2016-01-01 00:00:00   1.0
# 1 2016-01-01 00:15:00   2.0
# 2 2016-01-01 00:30:00   3.0
# 3 2016-01-01 00:45:00   4.0
# 4 2016-01-01 01:00:00   5.0
# 5 2016-01-01 01:15:00   7.0
# 6 2016-01-01 01:30:00   9.0
# 7 2016-01-01 01:45:00  11.0
# 8 2016-01-01 02:00:00  13.0

If you want column A in the result to be an integer you can add something like:

df2['A'] = df2['A'].round().astype(int)

Upvotes: 2

Related Questions