Перевести SQL в поляры и панды
Может кто-нибудь помочь мне перевести
SELECT (TRUNC(lat/0.5)*0.5) AS ll_lat, (TRUNC(lon/0.5)*0.5) AS ll_lon, COUNT(lat) AS strikes FROM lightning GROUP BY ll_lat, ll_lon;
в код Pandas и Polars, когда я пытался
# Pandas
df['ll_lat'] = (df['lat'] // 0.1 * 0.1).round(1)
df['ll_lon'] = (df['lon'] // 0.1 * 0.1).round(1)
df['temporalBasket'] = df['eventtime'].astype(str).str[:13]
df = df.groupby(['ll_lat', 'll_lon', 'temporalBasket']).agg(strikes=('lat', 'count'))
# 3763740 rows × 1 columns
# Polars
df_p_out = df_p.groupby(
(pl.col("lat") // 0.1 * 0.1).alias("ll_lat"),
(pl.col("lon") // 0.1 * 0.1).alias("ll_lon"),
pl.col("eventtime").dt.truncate("1h").alias("temporalBasket"),
).agg(strikes=pl.col("lat").count())
# shape: (3763740, 4)
в отличие от формы SQL (10168 строк × 3 столбца). Мой Dataframe выглядит так
Не группируйте по temporalBasket. Попробуйте: df = df.groupby(['ll_lat', 'll_lon']).agg(strikes=('lat', 'count'), event=('temporalBasket', 'first'))
В вашем sql нет элемента времени, тогда как в ваших полярах он есть. Либо поместите темпоральную корзину в sql, либо уберите ее из своей группы полярников.
Кроме того, координаты wgs84 не имеют фиксированных расстояний, поэтому группировка путем округления координат будет иметь большие площади по мере приближения к экватору. wiki.gis.com/wiki/index.php/Геодезическая_система
@DeanMacGregor очень хороший вопрос, сэр, как мне перевести временную корзину в SQL?
Ответы 1
Вы можете использовать:
df['ll_lat'] = (df['lat'] // 0.5 * 0.5).round(1) # 0.5 or 0.1?
df['ll_lon'] = (df['lon'] // 0.5 * 0.5).round(1) # 0.5 or 0.1?
df['temporalBasket'] = df['eventtime'].str[:13] # YYYY-MM-DD HH
out = (df.groupby(['ll_lat', 'll_lon'], as_index=False)
.agg(strikes=('lat', 'count'),
event=('temporalBasket', 'first')))
В то время как в df 1000 записей, out имеет 857 строк после агрегирования:
>>> out
ll_lat ll_lon strikes event
0 -46.0 -72.5 1 2022-09-12 23
1 -38.5 -58.0 1 2022-06-27 07
2 -38.0 -58.0 1 2022-04-20 03
3 -37.0 -72.5 1 2023-03-29 01
4 -35.0 -59.0 1 2023-01-23 12
.. ... ... ... ...
852 63.5 11.0 1 2022-09-08 08
853 63.5 20.0 1 2022-04-24 03
854 64.5 21.0 1 2022-05-02 11
855 65.0 -14.0 1 2022-10-12 14
856 65.5 78.0 1 2023-03-07 11
[857 rows x 4 columns]
>>> df
lat lon eventtime ll_lat ll_lon temporalBasket
0 45.804626 15.874926 2023-03-30 09:48:05 45.5 15.5 2023-03-30 09
1 34.674118 -92.351753 2022-06-13 22:18:29 34.5 -92.5 2022-06-13 22
2 50.614098 3.124432 2023-02-07 22:29:37 50.5 3.0 2023-02-07 22
3 -27.143572 -56.766484 2022-05-14 11:08:50 -27.5 -57.0 2022-05-14 11
4 43.503934 16.523779 2022-10-18 01:31:38 43.5 16.5 2022-10-18 01
.. ... ... ... ... ... ...
995 53.909600 37.736000 2022-12-15 13:45:56 53.5 37.5 2022-12-15 13
996 55.870995 -4.249439 2023-01-06 03:44:50 55.5 -4.5 2023-01-06 03
997 5.196440 -74.234923 2022-08-10 01:51:16 5.0 -74.5 2022-08-10 01
998 -6.509253 106.897496 2022-12-17 17:24:56 -7.0 106.5 2022-12-17 17
999 49.788203 19.705980 2022-08-24 15:03:58 49.5 19.5 2022-08-24 15
[1000 rows x 6 columns]
Как ни странно (даже после запуска с исправленной опечаткой (это 0,5) я не понимаю, что мне дает SQL. Я не уверен, почему аналогичный запрос дал бы мне 620886 строк вместо ожидаемых 10168, если только мой код не хорошо переведено Как мне воссоздать временную корзину в SQL, чтобы они совпадали?
Не группируйте по временной корзине, чтобы проверить, есть ли у вас одинаковый результат.
Я получил тот же результат, сэр. Есть идеи, как перевести temporalBasket в SQL?
eventime хранится как VARCHAR или DATETIME?
В БД PostgreSQL это text, а в файле паркета это datetime[ns] Сэр/Мадам.
Может быть, вы можете использовать SUBSTRING для извлечения первых 13 символов?
@ Любопытно что-то вроде этого stackoverflow.com/questions/17492167/…
Да, но кажется, что ваш столбец текстовый, поэтому я не думаю, что вы можете использовать date_trunc, но идея с substring такая же.
Пожалуйста, предоставьте минимальный воспроизводимый пример, в настоящее время сложно что-либо протестировать. Также убедитесь, что округление работает должным образом (вы используете
0.5в SQL и0.1в python).