0

i am facing a small issue. i have some sample data in this format

04-08-2017 12:00    216 906.23  144.68  429.74  522.73  668.56
04-08-2017 12:01    216 906.23  144.11  429.80  525.06  666.85
04-08-2017 12:02    216 906.23  144.27  430.03  524.44  667.55
04-08-2017 12:03    216 902.65  143.38  430.51  523.81  665.96
04-08-2017 12:04    216 911.95  145.09  430.47  524.72  672.62
04-08-2017 12:05    216 918.40  146.30  430.03  525.00  677.13
04-08-2017 12:06    216 916.96  146.06  430.56  526.71  674.76
04-08-2017 12:07    216 911.24  145.41  430.84  527.70  668.98
04-08-2017 12:08    216 910.52  144.92  431.14  527.61  669.14
04-08-2017 12:09    216 913.39  145.33  431.52  527.68  671.90
04-08-2017 12:10    216 929.85  147.12  432.30  526.80  688.23
04-08-2017 12:11    216 941.30  149.55  433.38  527.87  697.26
04-08-2017 12:12    216 933.43  147.77  433.69  527.63  691.72
04-08-2017 12:13    216 925.55  146.71  433.08  528.04  683.88
04-08-2017 12:14    216 964.21  153.70  433.98  525.96  718.54
04-08-2017 12:15    216 960.63  153.45  435.77  527.64  715.30

now i wish to average it out for a 5min bin. But somehow the code i wrote is generating some problems. Can anyone help me? Thanks

import pandas as pd
import numpy as np

data = pd.read_excel("test_sample.xlsx");
index = pd.date_range(start=data.index[0], end=data.index[-1], freq='1min')
dummy_frame = pd.DataFrame(np.NAN, index=index, columns=data.columns)
data.combine_first(dummy_frame).interpolate('time').resample().asfreq('5min').head()
  • Possible duplicate of [Group DataFrame in 5-minute intervals](https://stackoverflow.com/questions/36681945/group-dataframe-in-5-minute-intervals) – tda Sep 25 '18 at 09:06
  • @tda..I saw this question and tried the same...however my data is in an excel file. And the timestamp is in some custom format ...do you have any other suggestion? –  Sep 25 '18 at 09:12
  • Read the excel file/data into a `pandas` DataFrame, convert the timestamp to a `datetime.datetime` format, then apply. You sometimes have to 'clean' your data before analysing. – tda Sep 25 '18 at 09:14
  • Attempted the same, segregated the two into different column. Yet while processing it's calling for index mode and isn't getting converted to timestamp format. –  Sep 25 '18 at 09:25

0 Answers0