Pandas- regroupement du nombre de minutes dans diverses plages horaires de date et heure

Question

Je cherche un moyen efficace de traiter les données suivantes dans pandas.

J'ai un dataframe contenant plusieurs centaines de milliers de timestamps de début et de fin :

data_df
                      start_ts                     end_ts
0    2019-06-10 12:00:00+00:00  2019-06-10 22:30:00+00:00
1    2019-06-11 12:00:00+00:00  2019-06-11 13:30:00+00:00
2    2019-06-11 14:00:00+00:00  2019-06-11 19:00:00+00:00
3    2019-06-14 12:00:00+00:00  2019-06-14 18:30:00+00:00
4    2019-06-10 12:00:00+00:00  2019-06-10 21:30:00+00:00
5    2019-06-11 12:00:00+00:00  2019-06-11 18:30:00+00:00
...

J'ai également un ensemble d'intervalles de temps étiquetés (tp1-tp10). Il y a 10 intervalles par jour, mais les heures de ces intervalles peuvent changer d'un jour à l'autre (par exemple - tp1 peut être de 00:00 à 01:30 un jour, puis de 00:00 à 01:45 un autre jour). Chaque ensemble de données à traiter comporte 7 jours, avec 10 périodes de temps par jour, donc l'ensemble des plages est de taille 70, et ressemble à ceci :

labeled_bins_df
                   start_range                  end_range  label
0    2019-06-10 00:00:00+00:00  2019-06-10 04:30:00+00:00    tp1
1    2019-06-10 04:30:00+00:00  2019-06-10 09:45:00+00:00    tp2
2    2019-06-10 09:45:00+00:00  2019-06-10 12:30:00+00:00    tp3
...

Ce que je voudrais, c'est une table avec les données originales de data_df, mais avec des colonnes supplémentaires, tp1 à tp10, avec le nombre de minutes pour chaque ligne :

timed_bins
                      start_ts                     end_ts    tp1    tp2    tp3    tp4 ...
0    2019-06-10 12:00:00+00:00  2019-06-10 22:30:00+00:00      0      0     30    120 ...
1    2019-06-11 12:00:00+00:00  2019-06-11 13:30:00+00:00      0     45     45      0 ...

Je le fais actuellement de manière naïve, en bouclant sur mes lignes et en cherchant les intervalles dans lesquels se trouve chaque ligne de données, et comme vous pouvez l'imaginer, c'est assez lent. Y a-t-il une manipulation pandas à effectuer pour faire ce genre de regroupement sur les plages de dates ?

EDIT : Une pensée, qui pourrait aider à réfléchir dans une nouvelle direction. Si je convertissais tous mes timestamps (à la fois dans mes données et dans mes intervalles étiquetés) en timestamps Unix (secondes depuis le 1er janvier 1970), il suffirait d'effectuer un regroupement/somme basé sur des plages entières plutôt que des dates. Cela donnerait alors le nombre de secondes dans chaque intervalle, il suffirait de diviser par 60, et j'obtiendrai mes minutes dans chaque intervalle. Cela élimine toutes les préoccupations concernant les limites de dates, etc.

EDIT 2 : Comme demandé, voici un ensemble de données d'exemple simplifié, en utilisant trois intervalles de temps différents. J'ai spécifiquement fait en sorte qu'un des échantillons de données (la deuxième ligne) s'étende sur 2 jours. De plus, il y a un result_df qui montre le résultat attendu.

data_samples = [
    {'start_ts': '2019-06-10T12:00:00+0000', 'end_ts': '2019-06-10T22:30:00+0000'},
    {'start_ts': '2019-06-10T22:00:00+0000', 'end_ts': '2019-06-11T05:30:00+0000'},
    {'start_ts': '2019-06-10T10:00:00+0000', 'end_ts': '2019-06-10T14:15:00+0000'},
    {'start_ts': '2019-06-12T08:07:00+0000', 'end_ts': '2019-06-12T18:22:00+0000'},
    {'start_ts': '2019-06-11T14:03:00+0000', 'end_ts': '2019-06-11T15:30:00+0000'},
    {'start_ts': '2019-06-11T02:33:00+0000', 'end_ts': '2019-06-11T10:31:00+0000'}
]

data_set = [{
    'start_ts': datetime.datetime.strptime(x['start_ts'], '%Y-%m-%dT%H:%M:%S%z'),
    'end_ts': datetime.datetime.strptime(x['end_ts'], '%Y-%m-%dT%H:%M:%S%z')} for x in data_samples]

data_df = pd.DataFrame(data_set)[['start_ts', 'end_ts']]

time_bin_samples = [
    {'start_ts': '2019-06-10T00:00:00+0000', 'end_ts': '2019-06-10T08:15:00+0000', 'label': 't1'},
    {'start_ts': '2019-06-10T08:15:00+0000', 'end_ts': '2019-06-10T18:00:00+0000', 'label': 't2'},
    {'start_ts': '2019-06-10T18:00:00+0000', 'end_ts': '2019-06-11T00:00:00+0000', 'label': 't3'},

    {'start_ts': '2019-06-11T00:00:00+0000', 'end_ts': '2019-06-11T09:00:00+0000', 'label': 't1'},
    {'start_ts': '2019-06-11T09:00:00+0000', 'end_ts': '2019-06-11T19:15:00+0000', 'label': 't2'},
    {'start_ts': '2019-06-11T19:15:00+0000', 'end_ts': '2019-06-12T00:00:00+0000', 'label': 't3'},

    {'start_ts': '2019-06-12T00:00:00+0000', 'end_ts': '2019-06-12T10:30:00+0000', 'label': 't1'},
    {'start_ts': '2019-06-12T10:30:00+0000', 'end_ts': '2019-06-12T12:00:00+0000', 'label': 't2'},
    {'start_ts': '2019-06-12T12:00:00+0000', 'end_ts': '2019-06-13T00:00:00+0000', 'label': 't3'},
]

time_bin_set = [{
    'start_ts': datetime.datetime.strptime(x['start_ts'], '%Y-%m-%dT%H:%M:%S%z'),
    'end_ts': datetime.datetime.strptime(x['end_ts'], '%Y-%m-%dT%H:%M:%S%z'),
    'label': x['label']} for x in time_bin_samples
]

time_bin_df = pd.DataFrame(time_bin_set)[['start_ts', 'end_ts', 'label']]

result_set = [
    {'t1': 0, 't2': 360, 't3': 270},
    {'t1': 330, 't2': 0, 't3': 120},
    {'t1': 0, 't2': 255, 't3': 0},
    {'t1': 143, 't2': 90, 't3': 382},
    {'t1': 0, 't2': 87, 't3': 0},
    {'t1': 387, 't2': 91, 't3': 0}
]

result_df = pd.DataFrame(result_set)

Answer 1

Je sais que itérer sur les lignes d'un dataframe n'est pas efficace.

Ici, j'essaierais d'identifier le premier et le dernier bin par ligne dans data_df en utilisant merge_asof.

Ensuite, je construirais une liste de sous-dataframes en itérant une fois les valeurs du dataframe afin d'ajouter tous les bins correspondant à une ligne et concaténer cette liste.

À partir de là, il suffit de calculer l'intervalle de temps par bin et d'utiliser pivot_table pour obtenir le résultat attendu.

Le code pourrait être :

# stocker l'index en tant que colonne pour s'assurer de le conserver
data_df = data_df.rename_axis('ix').reset_index().sort_values(
    ['end_ts', 'start_ts'])
time_bin_df = time_bin_df.rename_axis('ix').reset_index().sort_values(
    ['end_ts', 'start_ts'])

# identifier le premier et le dernier bin par ligne
first = pd.merge_asof(data_df, time_bin_df, left_on='start_ts',
                      right_on='end_ts', suffixes=('', '_first'),
                      direction='forward').values
last = pd.merge_asof(data_df, time_bin_df, left_on='end_ts', right_on='start_ts',
                     suffixes=('', '_ bin')).values

# construire une liste de dataframes de bin (un par ligne dans data_df)
data = []
for i, val in enumerate(first):
    elt = time_bin_df[(time_bin_df['ix']>=val[3])
                      &(time_bin_df['ix']<=last[i][3])].copy()
    # calculer le début et la fin de l'intersection de la période et du bin
    elt.loc[elt['start_ts']val[2], 'end_ts'] = val[2]
    elt['ix_data'] = val[0]
    data.append(elt)

# concaténer tout
tmp = pd.concat(data)

# calculer les durées en minutes
tmp['duration'] = (tmp['end_ts'] - tmp['start_ts']).dt.total_seconds() / 60

# pivoter pour obtenir le résultat attendu
result_df = tmp.pivot_table('duration', 'ix_data', 'label', 'sum', fill_value=0
                            ).rename_axis(None).rename_axis(None, axis=1)

Cela peut prendre du temps car il y a encore une opération longue pour construire la liste de dataframes, mais les autres opérations devraient être vectorisées.