Pandas: как выполнить сложную группировку?

Question

[lloyd67]

Хочу перевести все долгие вычисления с SQL на Pandas в проекте.

Есть таблица сеансов устройств (DEVICE (int), DURATION (timedelta), DATE (datetime))

Необходимо посчитать кол-во DEVICE которые имели продолжительность DURATION суммарно 30 минут в день, не менее 5 дней в месяц. (Для каждого месяца считается отдельно)

То есть на SQL это выглядит примерно так:

SELECT COUNT(1) FROM (
	SELECT COUNT(1) as count
	FROM (
		SELECT device_id
		FROM statistic 
    WHERE YEAR(`date`) = 2019 AND MONTH(`date`) = 1
		GROUP BY device_id, DAY(`date`)
		HAVING SUM(duration)) > 60 * 30
	) as t
	GROUP BY device_id HAVING count <= 5
) as w

Как такое провернуть на Pandas ?

Думаю надо сгруппировать по дням месяца и использовать filter

df[df['date'].between(datetime(2019, 1, 1), datetime(2019, 1, 31))].groupby(pd.Grouper(key='date', freq='D')).filter(lambda x: x['duration'].sum() > timedelta(minutes=30))

Но что делать дальше?

Comments

[Andrey Dugin]

Дайте кусок исходных данных данных, так будет проще.

[lloyd67]

Andrey Dugin,
https://ru.files.fm/u/tnegq3fe - кусок в 10 000 строк (85 килобай) но поскольку реальная выборка идет из сотен миллионов записей, возможно он не даст нужных данных.

Answer 1

[Dimonchik]

на всякий спрошу - Кликхаус не вариант?

Comments

[lloyd67]

Нет, не вариант.

Answer 2

[lloyd67]

Собственно я добился чего хотел, вот только как то длинно получилось, наверняка есть более элегантная запись:

# считаем сумму duration для каждого дня месяца по каждому устройству (двойная группировка):
data = df.groupby([pd.Grouper(key='date_start', freq='D'), 'device']).agg({'duration': 'sum'})

# из этой выборки оставляем только сумму duration более 30 минут в день, и считаем кол-во таких дней по каждому device:
new_data = data[data['duration'] > timedelta(minutes=30)].groupby('device').count().reset_index().rename(columns={'duration': 'days'})

# из результата берем только те device у которых кол-во дней >= 5 и считаем их кол-во:
print new_data[new_data['days'] >= 5]['device'].count()