Самый быстрый способ объединения 2 таблиц данных при суммировании данных в одной из них
Проблема
У меня есть 2 таблицы данных,
Движение:
library(data.table)
consEx = data.table(
begin = as.POSIXct(c("2019-04-01 00:00:10"," 2019-04-07 10:00:00","2019-04-10 23:00:00","2019-04-12 20:00:00","2019-04-15 10:00:00",
"2019-04-20 10:00:00","2019-04-22 13:30:00","2019-04-10 15:30:00","2019-04-12 21:30:00","2019-04-15 20:00:00")),
end = as.POSIXct(c("2019-04-01 20:00:00","2019-04-07 15:00:00","2019-04-11 10:00:00", "2019-04-12 23:30:00","2019-04-15 15:00:00",
"2019-04-21 12:00:00","2019-04-22 17:30:00","2019-04-10 20:00:00","2019-04-13 05:00:00", "2019-04-15 12:30:00")),
carId = c(1,1,1,2,2,3,3,4,4,5),
tripId = c(1:10)
)
И оповещения:
alertsEx = data.table(
timestamp = as.POSIXct(c("2019-04-01 10:00:00","2019-04-01 10:30:00","2019-04-01 15:00:00","2019-04-15 13:00:00","2019-04-22 14:00:00",
"2019-04-22 15:10:00","2019-04-22 15:40:00","2019-04-10 16:00:00","2019-04-10 17:00:00","2019-04-13 04:00:00")),
type = c("T1","T2","T1",'T3',"T1","T1","T3","T2","T2","T1"),
carId = c(1,1,1,2,3,3,3,4,4,4),
additionalInfo1 = rnorm(10,mean=10,sd=4)
)
В таблице перемещений записывается период begin - end, в который двигался автомобиль. В таблице предупреждений показано, когда с автомобилем произошло предупреждение, содержащее type, timestamp и carId.
Мне нужно объединить эти 2 таблицы, суммируя данные оповещений по type. Когда есть несколько предупреждений одного и того же типа за один и тот же период, мне нужно агрегировать additionalInfo1, взяв среднее значение.
Текущий подход
В настоящее время я делаю это, перебирая consEx и применяя функцию к каждой строке, которая возвращает список с нужными мне вычислениями.
findAlerts = function(begin,end,carId_2){
saida = alertsEx[timestamp >= begin & timestamp <= end & carId == carId_2,]
totals = nrow(saida)
saida = split(saida,by = "type")
resultsList = list(
"totals" = 0,
"t1Count" = 0,
"t1Mean" = 0,
"t2Count" = 0,
"t2Mean" = 0,
"t3Count" = 0,
"t3Mean" = 0)
resultsList[["totals"]] = totals
types = names(saida)
if ("T1" %in% types){
resultsList[["t1Count"]] = nrow(saida[["T1"]])
resultsList[["t1Mean"]] = mean(saida[["T1"]]$additionalInfo1)
}
if ("T2" %in% types){
resultsList[["t2Count"]] = nrow(saida[["T2"]])
resultsList[["t2Mean"]] = mean(saida[["T2"]]$additionalInfo1)
}
if ("T3" %in% types){
resultsList[["t3Count"]] = nrow(saida[["T3"]])
resultsList[["t3Mean"]] = mean(saida[["T3"]]$additionalInfo1)
}
return(resultsList)
}
for(i in 1:nrow(consEx)){
aux = findAlerts(consEx$begin[i],consEx$end[i],consEx$carId[i])
consEx[i,names(aux) := aux]
}
Ожидаемый результат
Что дает ожидаемый результат:
begin end carId tripId totals t1Count t1Mean t2Count t2Mean t3Count t3Mean
1: 2019-04-01 00:00:10 2019-04-01 20:00:00 1 1 3 2 10.123463 1 14.479288 0 0.000000
2: 2019-04-07 10:00:00 2019-04-07 15:00:00 1 2 0 0 0.000000 0 0.000000 0 0.000000
3: 2019-04-10 23:00:00 2019-04-11 10:00:00 1 3 0 0 0.000000 0 0.000000 0 0.000000
4: 2019-04-12 20:00:00 2019-04-12 23:30:00 2 4 0 0 0.000000 0 0.000000 0 0.000000
5: 2019-04-15 10:00:00 2019-04-15 15:00:00 2 5 1 0 0.000000 0 0.000000 1 6.598062
6: 2019-04-20 10:00:00 2019-04-21 12:00:00 3 6 0 0 0.000000 0 0.000000 0 0.000000
7: 2019-04-22 13:30:00 2019-04-22 17:30:00 3 7 3 2 7.610410 0 0.000000 1 10.218593
8: 2019-04-10 15:30:00 2019-04-10 20:00:00 4 8 2 0 0.000000 2 9.703278 0 0.000000
9: 2019-04-12 21:30:00 2019-04-13 05:00:00 4 9 1 1 7.095564 0 0.000000 0 0.000000
10: 2019-04-15 20:00:00 2019-04-15 12:30:00 5 10 0 0 0.000000 0 0.000000 0 0.000000
Но это слишком медленно для моих исходных данных, в которых 13 миллионов строк в consEx и 2 миллиона в предупреждениях. Есть также несколько разных типов, где мне нужно вычислить среднее, минимум и максимум для двух показателей.
Есть ли более быстрый способ написать функцию или цикл?
Кроме того, даст ли написание практически того же кода на python лучший результат? Я подумываю переписать его, но это займет много времени, и я не уверен, что это решит проблему.
Спасибо
PS: Другой подход, который я пробовал, заключался в циклическом просмотре предупреждений и назначении каждому из них tripId из consEx, а затем агрегировании результирующей таблицы, но это, кажется, еще медленнее.
Ответы 5
Одной из причин задержки является зацикливание большого кадра данных. Если вы отмените поиск и пройдетесь по массиву предупреждений, это должно несколько повысить производительность.
Я попытался использовать метод «разделяй и властвуй», разбивая фрейм данных consEx на список с помощью carid, чтобы ускорить поиск. После того, как идентификатор поездки был назначен фрейму данных alertsEx, это способ суммирования таблицы и помещения ее в правильный формат.
Вот решение с использованием data.frames и пакетов dplyr и tidyr.
library(dplyr)
library(tidyr)
#split the consEx down to separate carId
splitcons<-split(consEx, consEx$carId)
alertsEx$tripid<-NA
#loop and only search the cons that match the alerts
alertsEx$tripid<-apply(alertsEx, 1, function(a){
#retrive the list assicoated to the correct car
cons<-splitcons[[a[["carId"]] ]]
alerttime<-as.POSIXct(a[["timestamp"]])
#find the trip which contains the alerttime
tripid<- which((alerttime>= cons$begin) & (alerttime<=cons$end))
#identify and return the tripid
cons$tripId[tripid]
})
#Referenced:
#https://stackoverflow.com/questions/30592094/r-spreading-multiple-columns-with-tidyr
#https://stackoverflow.com/questions/35321497/spread-multiple-columns-tidyr
alertsEx2<-alertsEx %>% group_by(carId, tripid, type) %>% summarize( mean=mean(additionalInfo1), count=n())
alerttable<-gather(alertsEx2, variable, val, -(carId:type), na.rm=TRUE) %>%
unite(temp, type, variable) %>%
spread(temp, val, fill=0)
# A tibble: 5 x 8
# Groups: carId, trip [5]
carId tripid T1_count T1_mean T2_count T2_mean T3_count T3_mean
<dbl> <int> <dbl> <dbl> <dbl> <dbl> <dbl> <dbl>
1 1 1 2 9.50 1 14.6 0 0
2 2 5 0 0 0 0 1 11.2
3 3 7 2 6.86 0 0 1 17.7
4 4 8 0 0 2 7.95 0 0
5 4 9 1 11.4 0 0 0 0
#last step to complete is to left_join "alertstable" to "consEx" using tripid columns
Я уверен, что есть еще более эффективный метод dplyr/purrr или data.table для поиска идентификатора поездки.
Я бы предложил вам один из способов сделать это в Питон с помощью пакета pandas. Это почти то же самое, что и в R, как предложил @Dave2e.
Я использую функцию apply для строк, чтобы найти совпадающие строки из фрейма данных alertsEx. Затем эти совпадающие строки группируются по type для вычисления mean и count.
Здесь подробности:
# Import module
import pandas as pd
import numpy as np
# Define dataframe
consEx = pd.DataFrame({
"begin":["2019-04-01 00:00:10"," 2019-04-07 10:00:00","2019-04-10 23:00:00","2019-04-12 20:00:00","2019-04-15 10:00:00",
"2019-04-20 10:00:00","2019-04-22 13:30:00","2019-04-10 15:30:00","2019-04-12 21:30:00","2019-04-15 20:00:00"],
"end": ["2019-04-01 20:00:00","2019-04-07 15:00:00","2019-04-11 10:00:00", "2019-04-12 23:30:00","2019-04-15 15:00:00",
"2019-04-21 12:00:00","2019-04-22 17:30:00","2019-04-10 20:00:00","2019-04-13 05:00:00", "2019-04-15 12:30:00"],
"carId": [1,1,1,2,2,3,3,4,4,5],
"tripId": [i for i in range (1,11)]
})
alertsEx = pd.DataFrame({
"timestamp": ["2019-04-01 10:00:00","2019-04-01 10:30:00","2019-04-01 15:00:00","2019-04-15 13:00:00","2019-04-22 14:00:00",
"2019-04-22 15:10:00","2019-04-22 15:40:00","2019-04-10 16:00:00","2019-04-10 17:00:00","2019-04-13 04:00:00"],
"type": ["T1","T2","T1",'T3',"T1","T1","T3","T2","T2","T1"],
"carId": [1,1,1,2,3,3,3,4,4,4],
"additionalInfo1": np.random.normal(10, 4, 10)
})
# Optional : convert date as date object
# consEx["begin"] = pd.to_datetime(consEx["begin"])
# consEx["end"] = pd.to_datetime(consEx["end"])
# alertsEx.timestamp = pd.to_datetime(alertsEx.timestamp)
# Get all the type possible
list_of_type = np.unique(alertsEx.type)
print(list_of_type)
# ['T1' 'T2' 'T3']
# Function apply at each row
def getExtraColums(row):
# Select date matching row from alertsEx dataframe
matching_rows = alertsEx[(alertsEx.timestamp >= row.begin) & (alertsEx.timestamp < row.end)]
# Define total as the number of rows
row["total"] = matching_rows.shape[0]
# Iterate over the matching rows group by type
for name, sub_df in matching_rows.groupby(by = "type"):
# Define count value
row[name+"_count"] = sub_df.shape[0]
# Define mean value
row[name+"_mean"] = sub_df.additionalInfo1.mean()
return row
# Apply the function : axis=1 for rows
consEx = consEx.apply(getExtraColums, axis=1)
#Re order the columns names according your output expected
new_order = [6, 8, 7, 10, 9, 0,1,2,3,4,5 ]
consEx = consEx[consEx.columns[new_order]]
print(consEx)
# begin end carId tripId total T1_count T1_mean T2_count T2_mean T3_count T3_mean
# 0 2019-04-01 00:00:10 2019-04-01 20:00:00 1 1 3 2.0 7.028186 1.0 9.895152 NaN NaN
# 1 2019-04-07 10:00:00 2019-04-07 15:00:00 1 2 0 NaN NaN NaN NaN NaN NaN
# 2 2019-04-10 23:00:00 2019-04-11 10:00:00 1 3 0 NaN NaN NaN NaN NaN NaN
# 3 2019-04-12 20:00:00 2019-04-12 23:30:00 2 4 0 NaN NaN NaN NaN NaN NaN
# 4 2019-04-15 10:00:00 2019-04-15 15:00:00 2 5 1 NaN NaN NaN NaN 1.0 8.629333
# 5 2019-04-20 10:00:00 2019-04-21 12:00:00 3 6 0 NaN NaN NaN NaN NaN NaN
# 6 2019-04-22 13:30:00 2019-04-22 17:30:00 3 7 3 2.0 10.084251 NaN NaN 1.0 3.845562
# 7 2019-04-10 15:30:00 2019-04-10 20:00:00 4 8 2 NaN NaN 2.0 12.523441 NaN NaN
# 8 2019-04-12 21:30:00 2019-04-13 05:00:00 4 9 1 1.0 7.363178 NaN NaN NaN NaN
# 9 2019-04-15 20:00:00 2019-04-15 12:30:00 5 10 0 NaN NaN NaN NaN NaN NaN
consEx = consEx.fillna(0)
print(consEx)
# begin end carId tripId total T1_count T1_mean T2_count T2_mean T3_count T3_mean
# 0 2019-04-01 00:00:10 2019-04-01 20:00:00 1 1 3 2.0 7.028186 1.0 9.895152 0.0 0.000000
# 1 2019-04-07 10:00:00 2019-04-07 15:00:00 1 2 0 0.0 0.000000 0.0 0.000000 0.0 0.000000
# 2 2019-04-10 23:00:00 2019-04-11 10:00:00 1 3 0 0.0 0.000000 0.0 0.000000 0.0 0.000000
# 3 2019-04-12 20:00:00 2019-04-12 23:30:00 2 4 0 0.0 0.000000 0.0 0.000000 0.0 0.000000
# 4 2019-04-15 10:00:00 2019-04-15 15:00:00 2 5 1 0.0 0.000000 0.0 0.000000 1.0 8.629333
# 5 2019-04-20 10:00:00 2019-04-21 12:00:00 3 6 0 0.0 0.000000 0.0 0.000000 0.0 0.000000
# 6 2019-04-22 13:30:00 2019-04-22 17:30:00 3 7 3 2.0 10.084251 0.0 0.000000 1.0 3.845562
# 7 2019-04-10 15:30:00 2019-04-10 20:00:00 4 8 2 0.0 0.000000 2.0 12.523441 0.0 0.000000
# 8 2019-04-12 21:30:00 2019-04-13 05:00:00 4 9 1 1.0 7.363178 0.0 0.000000 0.0 0.000000
# 9 2019-04-15 20:00:00 2019-04-15 12:30:00 5 10 0 0.0 0.000000 0.0 0.000000 0.0 0.000000
Это может дать вам некоторые идеи.
Я игнорирую, если это более эффективно, чем в R, дайте нам знать!
Вот моя попытка. Дайте мне знать, если вы думаете, что это может быть правильно, или если это достаточно быстро.
Обновлено: теперь, когда вышла версия table.express v0.2.0,
можно выразить расчет следующим образом:
types <- alertsEx[, unique(type)]
aggregated <- consEx %>%
start_expr %>%
right_join(alertsEx, carId, begin <= timestamp, end >= timestamp) %>%
select(carId, tripId, type, additionalInfo1) %>%
chain %>%
group_by(carId, tripId, type) %>%
transmute(typeCount = .N, typeMean = mean(additionalInfo1)) %>%
group_by(carId, tripId) %>%
mutate(totals = sum(typeCount)) %>%
end_expr
wide <- data.table::dcast(
aggregated,
... ~ type,
value.var = c("typeCount", "typeMean"),
sep = "",
fill = 0
)
# as.character removes dimensions
sd_cols <- as.character(outer(types, c("typeCount", "typeMean"), function(a, b) { paste0(b, a) }))
names(sd_cols) <- as.character(outer(types, c("Count", "Mean"), paste0))
sd_cols <- c(sd_cols, totals = "totals")
consEx %>%
start_expr %>%
mutate_join(wide, carId, tripId, .SDcols = sd_cols) %>%
end_expr %>%
setnafill(fill = 0) %>%
setcolorder(c("carId", "tripId", "begin", "end"))
Оригинальный ответ:
library(data.table)
set.seed(322902L)
consEx = data.table(
begin = as.POSIXct(c("2019-04-01 00:00:10"," 2019-04-07 10:00:00","2019-04-10 23:00:00","2019-04-12 20:00:00","2019-04-15 10:00:00",
"2019-04-20 10:00:00","2019-04-22 13:30:00","2019-04-10 15:30:00","2019-04-12 21:30:00","2019-04-15 20:00:00")),
end = as.POSIXct(c("2019-04-01 20:00:00","2019-04-07 15:00:00","2019-04-11 10:00:00", "2019-04-12 23:30:00","2019-04-15 15:00:00",
"2019-04-21 12:00:00","2019-04-22 17:30:00","2019-04-10 20:00:00","2019-04-13 05:00:00", "2019-04-15 12:30:00")),
carId = c(1,1,1,2,2,3,3,4,4,5),
tripId = c(1:10)
)
alertsEx = data.table(
timestamp = as.POSIXct(c("2019-04-01 10:00:00","2019-04-01 10:30:00","2019-04-01 15:00:00","2019-04-15 13:00:00","2019-04-22 14:00:00",
"2019-04-22 15:10:00","2019-04-22 15:40:00","2019-04-10 16:00:00","2019-04-10 17:00:00","2019-04-13 04:00:00")),
type = c("T1","T2","T1",'T3',"T1","T1","T3","T2","T2","T1"),
carId = c(1,1,1,2,3,3,3,4,4,4),
additionalInfo1 = rnorm(10,mean=10,sd=4)
)
types <- unique(alertsEx$type)
joined <- consEx[alertsEx,
.(carId, tripId, type, additionalInfo1),
on = .(carId, begin <= timestamp, end >= timestamp)]
aggregated <- joined[, .(typeCount = .N, typeMean = mean(additionalInfo1)), by = .(carId, tripId, type)]
totals <- aggregated[, .(totals = sum(typeCount)), by = .(carId, tripId)]
wide <- dcast(aggregated, carId + tripId ~ type, value.var = c("typeCount", "typeMean"), sep = "", fill = 0)
replaceNA <- function(x) { replace(x, is.na(x), 0) }
consEx[, `:=`(as.character(outer(types, c("Count", "Mean"), paste0)),
lapply(wide[consEx,
as.character(outer(types, c("typeCount", "typeMean"),
function(a, b) { paste0(b, a) })),
with = FALSE,
on = .(carId, tripId)],
replaceNA))]
consEx[, totals := lapply(totals[consEx, x.totals, on = .(carId, tripId)], replaceNA)]
setcolorder(consEx, c("carId", "tripId", "begin", "end"))
print(consEx)
carId tripId begin end T1Count T2Count T3Count T1Mean T2Mean T3Mean totals
1: 1 1 2019-04-01 00:00:10 2019-04-01 20:00:00 2 1 0 10.458406 9.170923 0.000000 3
2: 1 2 2019-04-07 10:00:00 2019-04-07 15:00:00 0 0 0 0.000000 0.000000 0.000000 0
3: 1 3 2019-04-10 23:00:00 2019-04-11 10:00:00 0 0 0 0.000000 0.000000 0.000000 0
4: 2 4 2019-04-12 20:00:00 2019-04-12 23:30:00 0 0 0 0.000000 0.000000 0.000000 0
5: 2 5 2019-04-15 10:00:00 2019-04-15 15:00:00 0 0 1 0.000000 0.000000 12.694035 1
6: 3 6 2019-04-20 10:00:00 2019-04-21 12:00:00 0 0 0 0.000000 0.000000 0.000000 0
7: 3 7 2019-04-22 13:30:00 2019-04-22 17:30:00 2 0 1 10.998666 0.000000 6.812388 3
8: 4 8 2019-04-10 15:30:00 2019-04-10 20:00:00 0 2 0 0.000000 11.600738 0.000000 2
9: 4 9 2019-04-12 21:30:00 2019-04-13 05:00:00 1 0 0 6.369317 0.000000 0.000000 1
10: 5 10 2019-04-15 20:00:00 2019-04-15 12:30:00 0 0 0 0.000000 0.000000 0.000000 0
Этот ответ предлагает два подхода data.table, которые используют неравные соединения для сопоставления предупреждений с поездками и dcast() для изменения формы, но различаются.
в том, как агрегаты сочетаются с consEx.
Первый вариант создает новый набор данных result, оставляя constEx без изменений.
Второй вариант изменяет constEx на месте. Это похоже на решение Алексис, но более лаконично и с использованием новой функции setnafill() (доступно в разрабатываемой версии 1.12.3 data.table).
Вариант 1: создать новый результирующий набор данных
agg <- consEx[alertsEx, on = .(carId, begin <= timestamp, end >= timestamp),
.(tripId, type, additionalInfo1)][
, .(Count = .N, Mean = mean(additionalInfo1)),
by = .(tripId, type)][
, totals := sum(Count), by = tripId][]
result <- dcast(agg[consEx, on = "tripId"], ... ~ type,
value.var = c("Count", "Mean"), fill = 0)[
, c("Count_NA", "Mean_NA") := NULL][
is.na(totals), totals := 0]
setcolorder(result, names(consEx))
result
begin end carId tripId totals Count_T1 Count_T2 Count_T3 Mean_T1 Mean_T2 Mean_T3 1: 2019-04-01 00:00:10 2019-04-01 20:00:00 1 1 3 2 1 0 12.654609 12.375862 0.000000 2: 2019-04-07 10:00:00 2019-04-07 15:00:00 1 2 0 0 0 0 0.000000 0.000000 0.000000 3: 2019-04-10 23:00:00 2019-04-11 10:00:00 1 3 0 0 0 0 0.000000 0.000000 0.000000 4: 2019-04-12 20:00:00 2019-04-12 23:30:00 2 4 0 0 0 0 0.000000 0.000000 0.000000 5: 2019-04-15 10:00:00 2019-04-15 15:00:00 2 5 1 0 0 1 0.000000 0.000000 9.316815 6: 2019-04-20 10:00:00 2019-04-21 12:00:00 3 6 0 0 0 0 0.000000 0.000000 0.000000 7: 2019-04-22 13:30:00 2019-04-22 17:30:00 3 7 3 2 0 1 8.586061 0.000000 11.498512 8: 2019-04-10 15:30:00 2019-04-10 20:00:00 4 8 2 0 2 0 0.000000 8.696356 0.000000 9: 2019-04-12 21:30:00 2019-04-13 05:00:00 4 9 1 1 0 0 9.343681 0.000000 0.000000 10: 2019-04-15 20:00:00 2019-04-15 12:30:00 5 10 0 0 0 0 0.000000 0.000000 0.000000
Обратите внимание, что средства, вероятно, будут различаться в разных опубликованных ответах, поскольку additionalInfo1 создается rnorm(10, mean = 10, sd = 4) без предварительного вызова set.seed(). Вызов set.seed() создает воспроизводимые случайные числа.
Пояснение к варианту 1
Первая часть находит совпадающие поездки для каждого оповещения по неравное соединение и вычисляет агрегаты по tripId и type, а также общее количество для каждой поездки:
agg
tripId type Count Mean totals 1: 1 T1 2 12.654609 3 2: 1 T2 1 12.375862 3 3: 5 T3 1 9.316815 1 4: 7 T1 2 8.586061 3 5: 7 T3 1 11.498512 3 6: 8 T2 2 8.696356 2 7: 9 T1 1 9.343681 1
Обратите внимание, что tripId считается уникальным ключом в consEx.
На следующем шаге агрегаты присоединяются справа к consEx
agg[consEx, on = "tripId"]
tripId type Count Mean totals begin end carId 1: 1 T1 2 12.654609 3 2019-04-01 00:00:10 2019-04-01 20:00:00 1 2: 1 T2 1 12.375862 3 2019-04-01 00:00:10 2019-04-01 20:00:00 1 3: 2 <NA> NA NA NA 2019-04-07 10:00:00 2019-04-07 15:00:00 1 4: 3 <NA> NA NA NA 2019-04-10 23:00:00 2019-04-11 10:00:00 1 5: 4 <NA> NA NA NA 2019-04-12 20:00:00 2019-04-12 23:30:00 2 6: 5 T3 1 9.316815 1 2019-04-15 10:00:00 2019-04-15 15:00:00 2 7: 6 <NA> NA NA NA 2019-04-20 10:00:00 2019-04-21 12:00:00 3 8: 7 T1 2 8.586061 3 2019-04-22 13:30:00 2019-04-22 17:30:00 3 9: 7 T3 1 11.498512 3 2019-04-22 13:30:00 2019-04-22 17:30:00 3 10: 8 T2 2 8.696356 2 2019-04-10 15:30:00 2019-04-10 20:00:00 4 11: 9 T1 1 9.343681 1 2019-04-12 21:30:00 2019-04-13 05:00:00 4 12: 10 <NA> NA NA NA 2019-04-15 20:00:00 2019-04-15 12:30:00 5
Выходные данные немедленно преобразуются из длинного в широкий формат с помощью dcast().
Наконец, результат очищается путем удаления лишних столбцов, замены NA на 0 и изменения порядка столбцов.
Вариант 2: Обновление на месте
agg <- consEx[alertsEx, on = .(carId, begin <= timestamp, end >= timestamp),
.(tripId, type, additionalInfo1)][
, .(Count = .N, Mean = mean(additionalInfo1)),
by = .(tripId, type)][
, totals := sum(Count), by = tripId][]
wide <- dcast(agg, ... ~ type, value.var = c("Count", "Mean"), fill = 0)
consEx[wide, on = "tripId", (names(wide)) := mget(paste0("i.", names(wide)))]
setnafill(consEx, fill = 0) # data.table version 1.12.3+
consEx
begin end carId tripId totals Count_T1 Count_T2 Count_T3 Mean_T1 Mean_T2 Mean_T3 1: 2019-04-01 00:00:10 2019-04-01 20:00:00 1 1 3 2 1 0 12.654609 12.375862 0.000000 2: 2019-04-07 10:00:00 2019-04-07 15:00:00 1 2 0 0 0 0 0.000000 0.000000 0.000000 3: 2019-04-10 23:00:00 2019-04-11 10:00:00 1 3 0 0 0 0 0.000000 0.000000 0.000000 4: 2019-04-12 20:00:00 2019-04-12 23:30:00 2 4 0 0 0 0 0.000000 0.000000 0.000000 5: 2019-04-15 10:00:00 2019-04-15 15:00:00 2 5 1 0 0 1 0.000000 0.000000 9.316815 6: 2019-04-20 10:00:00 2019-04-21 12:00:00 3 6 0 0 0 0 0.000000 0.000000 0.000000 7: 2019-04-22 13:30:00 2019-04-22 17:30:00 3 7 3 2 0 1 8.586061 0.000000 11.498512 8: 2019-04-10 15:30:00 2019-04-10 20:00:00 4 8 2 0 2 0 0.000000 8.696356 0.000000 9: 2019-04-12 21:30:00 2019-04-13 05:00:00 4 9 1 1 0 0 9.343681 0.000000 0.000000 10: 2019-04-15 20:00:00 2019-04-15 12:30:00 5 10 0 0 0 0 0.000000 0.000000 0.000000
Пояснение к варианту 2
Первая часть такая же, как и в варианте 1.
На следующем этапе агрегаты из длинного формата изменяются в широкий.
Затем выполняется обновить присоединиться, где consEx обновляется на месте соответствующими строками wide. Выражение
(names(wide)) := mget(paste0("i.", names(wide)))
это короткий путь для
`:=`(totals = i.totals, Count_T1 = i.Count_T1, ..., Mean_T3 = i.Mean_T3)
где префикс i. относится к столбцам wide и используется во избежание двусмысленности.
В строках consEx, где нет соответствия, добавленные столбцы содержат NA. Итак, setnafill() используется для замены всех значений NA на 0, наконец.
Ориентир
На момент написания статьи было опубликовано 6 различных решений.
- Фино ОП в базе
R - Дэйв2е с использованием
dplyrиtidyr - Александр Б в
python - Алексис с помощью
data.table - и 2
data.tableварианта от себя.
5 R решений сравниваются с использованием press() и mark() из пакета bench.
Выборочные наборы данных параметризованы для изменения количества строк. Поскольку некоторые решения изменяют consEx, каждый запуск теста начинается с новой копии.
Потребовались небольшие изменения, чтобы коды работали с bench. Наиболее примечательно то, что сокращенная формула ... ~ type в dcast() вызывала ошибку при вызове в bench::mark(), и ее пришлось заменить версией, в которой все переменные LHS имеют явные имена.
Поскольку ОП указал, что его версия довольно медленная, первый запуск теста использует только 1000 строк consEx и 50, 200 и 1000 строк alertsEx.
bm <- bench::press(
n_trips = 1E3,
n_alerts = 1E3/c(20, 5, 1),
{
n_cars <- 5
n_periods <- round(n_trips / n_cars)
n_types = 3
types = sprintf("T%02i", 1:n_types)
consEx0 <- data.table(
tripId = 1:n_trips,
carId = rep(1:n_cars, each = n_periods),
begin = seq.POSIXt(as.POSIXct("2000-01-01"), length.out = n_periods, by = "14 days") %>%
rep(n_cars) %>%
`mode<-`("double")
)[, end := begin + 7*24*60*60]
set.seed(1)
idx <- sample(n_trips, n_alerts, TRUE)
alertsEx <- consEx0[
idx,
.(timestamp = begin + runif (n_alerts, 1, 7*24*60*60 - 1),
type = sample(types, n_alerts, TRUE),
carId,
additionalInfo1 = rnorm(n_alerts, mean = 10, sd = 4))]
bench::mark(
fino = {
consEx <- copy(consEx0)
findAlerts = function(begin,end,carId_2){
saida = alertsEx[timestamp >= begin & timestamp <= end & carId == carId_2,]
totals = nrow(saida)
saida = split(saida,by = "type")
resultsList = list(
"totals" = 0,
"t1Count" = 0,
"t1Mean" = 0,
"t2Count" = 0,
"t2Mean" = 0,
"t3Count" = 0,
"t3Mean" = 0)
resultsList[["totals"]] = totals
types = names(saida)
if ("T1" %in% types){
resultsList[["t1Count"]] = nrow(saida[["T1"]])
resultsList[["t1Mean"]] = mean(saida[["T1"]]$additionalInfo1)
}
if ("T2" %in% types){
resultsList[["t2Count"]] = nrow(saida[["T2"]])
resultsList[["t2Mean"]] = mean(saida[["T2"]]$additionalInfo1)
}
if ("T3" %in% types){
resultsList[["t3Count"]] = nrow(saida[["T3"]])
resultsList[["t3Mean"]] = mean(saida[["T3"]]$additionalInfo1)
}
return(resultsList)
}
for(i in 1:nrow(consEx)){
aux = findAlerts(consEx$begin[i],consEx$end[i],consEx$carId[i])
consEx[i,names(aux) := aux]
}
consEx[]
},
dave = {
# library(dplyr)
# library(tidyr)
consEx <- copy(consEx0)
#split the consEx down to separate carId
splitcons <- split(consEx, consEx$carId)
alertsEx$tripId <- NA
#loop and only search the cons that match the alerts
alertsEx$tripId <- apply(alertsEx, 1, function(a) {
#retrive the list assicoated to the correct car
cons <- splitcons[[a[["carId"]]]]
alerttime <- as.POSIXct(a[["timestamp"]])
#find the trip which contains the alerttime
tripId <-
which((alerttime >= cons$begin) & (alerttime <= cons$end))
#identify and return the tripId
cons$tripId[tripId]
})
#Referenced:
#https://stackoverflow.com/questions/30592094/r-spreading-multiple-columns-with-tidyr
#https://stackoverflow.com/questions/35321497/spread-multiple-columns-tidyr
alertsEx2 <-
alertsEx %>%
dplyr::group_by(carId, tripId, type) %>%
dplyr::summarize(mean = mean(additionalInfo1), count = dplyr::n())
alerttable <-
tidyr::gather(alertsEx2, variable, val, -(carId:type), na.rm = TRUE) %>%
tidyr::unite(temp, type, variable) %>%
tidyr::spread(temp, val, fill = 0)
consEx %>%
dplyr::left_join(alerttable, by = c("tripId", "carId"))
},
alexis = {
consEx <- copy(consEx0)
joined <- consEx[alertsEx,
.(carId, tripId, type, additionalInfo1),
on = .(carId, begin <= timestamp, end >= timestamp)]
aggregated <- joined[, .(typeCount = .N, typeMean = mean(additionalInfo1)), by = .(carId, tripId, type)]
totals <- aggregated[, .(totals = sum(typeCount)), by = .(carId, tripId)]
long <- dcast(aggregated, carId + tripId ~ type, value.var = c("typeCount", "typeMean"), sep = "", fill = 0)
replaceNA <- function(x) { replace(x, is.na(x), 0) }
consEx[, `:=`(as.character(outer(types, c("Count", "Mean"), paste0)),
lapply(long[consEx,
as.character(outer(types, c("typeCount", "typeMean"),
function(a, b) { paste0(b, a) })),
with = FALSE,
on = .(carId, tripId)],
replaceNA))]
consEx[, totals := lapply(totals[consEx, x.totals, on = .(carId, tripId)], replaceNA)]
setcolorder(consEx, c("carId", "tripId", "begin", "end"))
consEx
},
uwe1 = {
consEx <- copy(consEx0)
agg <- consEx[alertsEx, on = .(carId, begin <= timestamp, end >= timestamp),
.(tripId, type, additionalInfo1)][
, .(Count = .N, Mean = mean(additionalInfo1)),
by = .(tripId, type)][
, totals := sum(Count), by = tripId][]
result <- dcast(agg[consEx, on = "tripId"],
tripId + carId + begin+ end + totals ~ type,
value.var = c("Count", "Mean"), fill = 0)[
, c("Count_NA", "Mean_NA") := NULL][
is.na(totals), totals := 0][]
# setcolorder(result, names(consEx))
result
},
uwe2 = {
consEx <- copy(consEx0)
agg <- consEx[alertsEx, on = .(carId, begin <= timestamp, end >= timestamp),
.(tripId, type, additionalInfo1)][
, .(Count = .N, Mean = mean(additionalInfo1)),
by = .(tripId, type)][
, totals := sum(Count), by = tripId][]
wide <- dcast(agg, tripId + totals ~ type, value.var = c("Count", "Mean"), fill = 0)
consEx[wide, on = "tripId", (names(wide)) := mget(paste0("i.", names(wide)))]
setnafill(consEx, fill = 0) # data.table version 1.12.3+
consEx
},
check = FALSE
)
}
)
library(ggplot2)
autoplot(bm)
Обратите внимание на логарифмическую шкалу времени.
Диаграмма подтверждает предположение ОП о том, что его подход на порядок медленнее, чем любые другие решения. Для 1000 строк потребовалось 3 секунды, в то время как производственный набор данных OP содержит 12 миллионов строк. Время выполнения подхода Dave2e увеличивается с количеством предупреждений.
Кроме того, подход OP наиболее требователен к выделенной памяти. Он выделяет до 190 МБ, в то время как версии data.table требуют только 1 МБ.
bm
# A tibble: 15 x 15 expression n_trips n_alerts min median `itr/sec` mem_alloc `gc/sec` n_itr n_gc total_time result memory time <bch:expr> <dbl> <dbl> <bch:tm> <bch:tm> <dbl> <bch:byt> <dbl> <int> <dbl> <bch:tm> <list> <list> <lis> 1 fino 1000 50 2.95s 2.95s 0.339 149.25MB 3.39 1 10 2.95s <data~ <Rpro~ <bch~ 2 dave 1000 50 21.52ms 22.61ms 41.6 779.46KB 5.94 21 3 505.01ms <data~ <Rpro~ <bch~ 3 alexis 1000 50 21.98ms 23.09ms 41.2 1007.84KB 3.93 21 2 509.5ms <data~ <Rpro~ <bch~ 4 uwe1 1000 50 14.47ms 15.45ms 62.3 919.12KB 1.95 32 1 513.94ms <data~ <Rpro~ <bch~ 5 uwe2 1000 50 14.14ms 14.73ms 64.2 633.2KB 1.95 33 1 513.91ms <data~ <Rpro~ <bch~ 6 fino 1000 200 3.09s 3.09s 0.323 155.12MB 4.53 1 14 3.09s <data~ <Rpro~ <bch~ 7 dave 1000 200 53.4ms 62.11ms 11.0 1.7MB 5.49 8 4 728.02ms <data~ <Rpro~ <bch~ 8 alexis 1000 200 22.42ms 23.34ms 41.5 1.03MB 3.77 22 2 530.25ms <data~ <Rpro~ <bch~ 9 uwe1 1000 200 14.72ms 15.76ms 62.2 953.07KB 0 32 0 514.46ms <data~ <Rpro~ <bch~ 10 uwe2 1000 200 14.49ms 15.17ms 63.6 695.63KB 1.99 32 1 503.4ms <data~ <Rpro~ <bch~ 11 fino 1000 1000 3.6s 3.6s 0.278 187.32MB 3.61 1 13 3.6s <data~ <Rpro~ <bch~ 12 dave 1000 1000 242.46ms 243.7ms 4.07 6.5MB 6.78 3 5 737.06ms <data~ <Rpro~ <bch~ 13 alexis 1000 1000 24.32ms 25.78ms 37.6 1.21MB 3.95 19 2 505.84ms <data~ <Rpro~ <bch~ 14 uwe1 1000 1000 16.04ms 16.8ms 56.8 1.05MB 1.96 29 1 510.23ms <data~ <Rpro~ <bch~ 15 uwe2 1000 1000 15.69ms 16.41ms 54.8 938.74KB 3.92 28 2 510.63ms <data~ <Rpro~ <bch~
Таким образом, подход Фино исключен из второго запуска теста с большими размерами задач: 10 000 и 100 000 строк для consEx и 2 000 и 20 000 строк для alertsEx, соответственно.
Поскольку подход Dave2e примерно в 100 раз медленнее для 20 тыс. строк, чем самый быстрый подход, он исключается из третьего запуска. Это будет имитировать производственный набор данных OP из 12 миллионов строк consEx и 2 миллионов строк alertsEx:
print(bm, n = Inf)
# A tibble: 27 x 15 expression n_trips n_alerts min median `itr/sec` mem_alloc `gc/sec` n_itr n_gc total_time result memory time <bch:expr> <dbl> <dbl> <bch:tm> <bch:tm> <dbl> <bch:byt> <dbl> <int> <dbl> <bch:tm> <list> <list> <lis> 1 alexis 1.00e5 20000 747.87ms 747.87ms 1.34 38.18MB 6.69 1 5 747.87ms <data~ <Rpro~ <bch~ 2 uwe1 1.00e5 20000 124.52ms 138.89ms 7.32 37.88MB 3.66 4 2 546.17ms <data~ <Rpro~ <bch~ 3 uwe2 1.00e5 20000 72.29ms 73.76ms 11.3 16.63MB 1.88 6 1 533.26ms <data~ <Rpro~ <bch~ 4 alexis 1.00e6 20000 7.51s 7.51s 0.133 335.44MB 3.33 1 25 7.51s <data~ <Rpro~ <bch~ 5 uwe1 1.00e6 20000 995.31ms 995.31ms 1.00 346.94MB 2.01 1 2 995.31ms <data~ <Rpro~ <bch~ 6 uwe2 1.00e6 20000 714.19ms 714.19ms 1.40 89.27MB 1.40 1 1 714.19ms <data~ <Rpro~ <bch~ 7 alexis 1.00e7 20000 1.67m 1.67m 0.00999 3.21GB 0.340 1 34 1.67m <data~ <Rpro~ <bch~ 8 uwe1 1.00e7 20000 3.44m 3.44m 0.00485 3.41GB 0.00969 1 2 3.44m <data~ <Rpro~ <bch~ 9 uwe2 1.00e7 20000 42.51s 42.51s 0.0235 810.3MB 0 1 0 42.51s <data~ <Rpro~ <bch~ 10 alexis 1.00e5 200000 15.38s 15.38s 0.0650 73.22MB 0.0650 1 1 15.38s <data~ <Rpro~ <bch~ 11 uwe1 1.00e5 200000 1.34s 1.34s 0.747 63.81MB 0 1 0 1.34s <data~ <Rpro~ <bch~ 12 uwe2 1.00e5 200000 1.47s 1.47s 0.681 58.98MB 0 1 0 1.47s <data~ <Rpro~ <bch~ 13 alexis 1.00e6 200000 2.91m 2.91m 0.00573 375.93MB 0.0115 1 2 2.91m <data~ <Rpro~ <bch~ 14 uwe1 1.00e6 200000 9.72s 9.72s 0.103 371.69MB 0 1 0 9.72s <data~ <Rpro~ <bch~ 15 uwe2 1.00e6 200000 888.67ms 888.67ms 1.13 161.82MB 0 1 0 888.67ms <data~ <Rpro~ <bch~ 16 alexis 1.00e7 200000 6.29m 6.29m 0.00265 3.15GB 0.0928 1 35 6.29m <data~ <Rpro~ <bch~ 17 uwe1 1.00e7 200000 2.45m 2.45m 0.00681 3.43GB 0.0136 1 2 2.45m <data~ <Rpro~ <bch~ 18 uwe2 1.00e7 200000 12.48m 12.48m 0.00134 887.99MB 0.00134 1 1 12.48m <data~ <Rpro~ <bch~ 19 alexis 1.00e5 2000000 3.04s 3.04s 0.329 207MB 0 1 0 3.04s <data~ <Rpro~ <bch~ 20 uwe1 1.00e5 2000000 2.96s 2.96s 0.338 196.42MB 0 1 0 2.96s <data~ <Rpro~ <bch~ 21 uwe2 1.00e5 2000000 2.81s 2.81s 0.355 187.79MB 0 1 0 2.81s <data~ <Rpro~ <bch~ 22 alexis 1.00e6 2000000 6.96m 6.96m 0.00239 726.14MB 0.00479 1 2 6.96m <data~ <Rpro~ <bch~ 23 uwe1 1.00e6 2000000 2.01m 2.01m 0.00827 631.1MB 0 1 0 2.01m <data~ <Rpro~ <bch~ 24 uwe2 1.00e6 2000000 30.54s 30.54s 0.0327 584.81MB 0 1 0 30.54s <data~ <Rpro~ <bch~ 25 alexis 1.00e7 2000000 31.54m 31.54m 0.000528 3.66GB 0.0127 1 24 31.54m <data~ <Rpro~ <bch~ 26 uwe1 1.00e7 2000000 8.72m 8.72m 0.00191 3.67GB 0 1 0 8.72m <data~ <Rpro~ <bch~ 27 uwe2 1.00e7 2000000 12.35m 12.35m 0.00135 1.58GB 0 1 0 12.35m <data~ <Rpro~ <bch~
Предостережение: Обратите внимание, что время работы для большинства вариантов параметров подтверждается только одним измерением. Кроме того, сборка мусора (gc) может иметь влияние из-за ограниченного объема оперативной памяти на моем компьютере (8 ГБ). Таким образом, ваш пробег может варьироваться.
Тайминги соответствуют нет. В общем, uwe2 (обновление на месте) значительно быстрее, чем uwe1 (новый набор результатов), который подтверждает предположение Янгорецкого. Алексис всегда самый медленный почти. (Обратите внимание на логарифмическую шкалу времени на диаграмме или сравните временные рамки в таблице.)
Тем не менее, некоторые изменения параметров показывают отклонения от вышеуказанного шаблона. Для 10-миллионных поездок uwe1 быстрее, чем uwe2 в двух случаях, и Алексис также догоняет. Эти эффекты могут быть связаны со сборкой мусора.
Выделение памяти намного меньше для подхода обновления на месте (даже если это не всегда окупается с точки зрения скорости).
Опять же, интерпретируйте результаты с осторожностью. Третий запуск теста следует повторить на более крупной машине и с большим терпением, чтобы можно было проводить повторные измерения.
Вариант 2 следует предпочесть, поскольку OP упоминает большие наборы данных, поэтому следует избегать копий. TLDR был бы полезен в вашем ответе, сейчас трудно найти лучшее решение среди всех опубликованных, и ваш вариант 2, я считаю, будет работать лучше всего.
Спасибо за удивительный ответ. Я планировал сделать тест сам, но не нашел времени. Я пошел с вашим вторым вариантом с некоторыми корректировками, и он работал около 5 минут. Большое улучшение по сравнению с 9 днями, которые потребовались бы при моем первоначальном подходе к завершению.
Уве, очень хороший и подробный ответ. Я знал, что мой ответ был улучшением, а также далек от оптимального, я рад, что вы смогли его предоставить.
Очень хорошо! Если краткие тесты, которые я делал локально, меня не обманывают, основное ускорение происходит от setnafill, что неудивительно, так как R-версия, которую я использовал, должна пару раз копировать данные.
Один из подходов заключается в использовании левого соединения и маркировке записей, в которых метка времени находится между началом и концом. Если размер объединения будет проблемой, объединение можно выполнить индивидуально для каждого carId.
df = consEx %>%
left_join(alertsEx, by = "carId") %>%
mutate(AlertEntry = ifelse(timestamp>= begin & timestamp<=end, "Yes", "No")) %>%
filter(AlertEntry == "Yes") %>%
group_by(carId, tripId, type) %>%
summarise(Total = n(),
Mean = mean(additionalInfo1))
# Create Summary
df_summary = df %>%
group_by(carId, tripId) %>%
gather(key, value, -c(carId, tripId, type), na.rm=T) %>%
unite(newType, type, key) %>%
spread(newType, value, fill=0)
Возможно, вы можете сравнить это, поскольку вы это предлагаете.
.applyв Python Pandas, как известно, очень медленный, я думал. Вот некоторые тесты, которые я сделал, и я обнаружил, что панды медленнее и требуют больше памяти, что приводит к сбою раньше, чем data.table в R. h2oai.github.io/db-бенчмарк