0

I have two lists.

  1. The list times is a list of datetimes from 2018-04-10 00:00 to 2018-04-10 23:59.

  2. For each item in times I have a corresponding label of 0 or 1 recorded in the list labels.

My goal is to get the mean label value (between 0 and 1) for every minute interval.

times = [Timestamp('2018-04-10 00:00:00.118000'),
 Timestamp('2018-04-10 00:00:00.547000'),
 Timestamp('2018-04-10 00:00:00.569000'),
 Timestamp('2018-04-10 00:00:00.690000'),
.
.
.
Timestamp('2018-04-10 23:59:59.999000') ]

labels = [0,1,1,0,1,0,....1]

where len(times) == len(labels)

For every minute interval between 2018-04-10 00:00 and 2018-04-10 23:59, the min and max times in the list respectively, I am trying to get two lists:

1) The start time of the minute interval.

2) The mean average label value of all the datetimes in that interval.

In particular I am having trouble with (2).

Note: the times list is not necessarily chronologically ordered

piccolo
  • 2,093
  • 3
  • 24
  • 56

2 Answers2

1

Firstly, I begin with how I generated the data as above format

from datetime import datetime
size = int(1e6)

timestamp_a_day = np.linspace(datetime.now().timestamp(), datetime.now().timestamp()+24*60*60, size)
dummy_sec = np.random.rand(size)

timestamp_series = pd.Series(timestamp_a_day + dummy_sec)\
.sort_values().reset_index(drop=True)\
.apply(lambda x: datetime.fromtimestamp(x))

data = pd.DataFrame(timestamp_series, columns=['timestamp'])
data['label'] = np.random.randint(0, 2, size)

Let's solve this problem !!! (I hope I understand your question precisely hahaha)

1) data['start_interval'] = data['timestamp'].dt.floor('s')
2) data.groupby('start_interval')['label'].mean()
Noppu
  • 59
  • 4
  • Related: [Python Pandas: Group datetime column into hour and minute aggregations](https://stackoverflow.com/questions/16266019/python-pandas-group-datetime-column-into-hour-and-minute-aggregations), [How to group DataFrame by a period of time?](https://stackoverflow.com/questions/11073609/how-to-group-dataframe-by-a-period-of-time), – wwii Sep 01 '19 at 18:11
1
  • zip times and labels then sort;
  • Write a function that returns the date, hour, minute of a Timestamp;
  • groupby that function;
  • sum and average the labels for each group
wwii
  • 23,232
  • 7
  • 37
  • 77