Эффективное присвоение общего идентификатора связанным строкам в большом кадре данных в R
Я работаю с большим набором данных в R, состоящим примерно из 19 миллионов строк и более 81 столбца, и мне нужны рекомендации по его эффективной обработке.
Мой набор данных отслеживает повторяющиеся записи по имени (на самом деле это другой идентификатор, но для простоты давайте назовем здесь имя), а также их значение, даты начала и окончания, при этом некоторые записи были отменены через несколько лет. Вот упрощенный пример моей структуры данных:
Моя цель — присвоить уникальный идентификатор каждой последовательности связанных записей, где последовательность определяется последовательной записью с одинаковым именем. Насколько я понимаю, если строка не имеет даты отмены, то сопоставьте строку 1 с именем ABC со следующей строкой 2 из ABC, где дата окончания строки 1 соответствует дате начала строки 2. Если запись отменена , оно не должно быть связано с последующими появлениями того же имени. Например, желаемый результат будет выглядеть так (для ассоциации создан новый уникальный идентификатор, который позволяет мне сначала сортировать по имени, затем по уникальному идентификатору, а затем по дате начала):
Учитывая масштаб моего набора данных (3 ГБ FST, 17 миллионов и 81 столбец), использование традиционного цикла в R оказывается неэффективным. Я ищу способ использовать векторизованные операции или функции dplyr (или любые другие, которые вы только можете придумать) для более эффективного выполнения этой задачи.
Будем очень признательны за любые идеи или предложения о том, как решить эту проблему!
редактировать: добавление таблиц r для таблица 1 (ранее):
table_before <- data.frame(
name = c("ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "BAA", "BAA", "BAA", "BAA", "BAA", "BAA", "BAA"),
value = c(77, 66, 55, 44, 33, 22, 11, 44, 33, 22, 45, 23, 54, 15, 42),
cancelled = c(NA, NA, NA, NA, 2012, NA, NA, NA, NA, NA, NA, 2011, NA, NA, NA),
start = c(2010, 2010, 2010, 2011, 2011, 2011, 2012, 2012, 2009, 2009, 2009, 2009, 2010, 2012, 2010),
end = c(2011, 2011, 2011, 2012, 2012, 2012, 2013, 2013, 2012, 2010, 2010, 2011, 2011, 2013, 2011)
)
# The following is a desired state that I wish to achieve, as you can see the rows are sorted first by "Name", then by a unique id that should be generated and groups the rows together (associates them)
table_after <- data.frame(
name = c("ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "ABC", "BAA", "BAA", "BAA", "BAA", "BAA", "BAA", "BAA"),
value = c(77, 44, 11, 66, 33, 55, 22, 44, 33, 15, 22, 54, 45, 42, 23),
cancelled = c(NA, NA, NA, NA, 2012, NA, NA, NA, NA, NA, NA, NA, NA, NA, 2011),
start = c(2010, 2011, 2012, 2010, 2011, 2010, 2011, 2012, 2009, 2012, 2009, 2010, 2009, 2010, 2009),
end = c(2011, 2012, 2013, 2011, 2012, 2011, 2012, 2013, 2012, 2013, 2010, 2011, 2010, 2011, 2011),
new_unique_id = c(1, 1, 1, 2, 2, 3, 3, 3, 4, 4, 5, 5, 6, 6, 7)
)
редактировать: я упростил фрейм данных, сначала отсортировав его по имени, затем по году и удалив сокращенные на две строки, что может сделать его излишне сложным.
изменить: вот изображение логики в порядке очереди. Проще говоря: для каждой строки проверьте, существует ли уникальный идентификатор, а если нет, назначьте новый идентификатор и найдите последующие совпадения на основе первого поиска.
изменить: другое изображение проблемы
В таблице начальный столбец — 2010, 2011... но в таблице table_before начальный столбец — 2010, 2010... Данные не в том же порядке. Для table_before неясно, как определить новый идентификатор.
Как вы определяете последовательную запись. Если start[i] <= end[i-1], то последовательно?
Я упустил тот факт, что ваши значения start/end повторяются в table_before. Насколько точно мы можем с уверенностью предположить, что, если они одинаковы, нам следует увеличить значения данных? Это кажется произвольным и/или созданием данных для заполнения того, что мы хотим.
Я думаю, что некоторая путаница заключается в том, что table_before упорядочено name и start, а table_after переставлено new_unique_id.
@zephryl, спасибо, это то, чего мне не хватало. Узнав это, я думаю, что это проблема оптимизации (упаковка контейнеров), когда вы пытаетесь найти самую длинную последовательность (перед отменой) возможно последовательных строк, а затем найти вторую по длине и т. д. Почему это так? , например, что строке 77 строки 2010-2011 присваивается new_unique_id, хотя это также могло быть 66 или 55?
@r2evans, спасибо! Строке со значением 77 был присвоен идентификатор «1», поскольку это было первое упоминание комбинации имени, начального года и конечного года. Как только эта комбинация повторяется, см. строку со значением 66, она получает идентификатор «2», поскольку это второе упоминание одного и того же. комбинация имени, года начала и года окончания. После этого первое упоминание name&previousendyear¤tendyear используется для поиска следующей строки.
@Botan, поскольку это немного сложнее, тот факт, что у вас 19 миллионов строк, усложняет процесс. Его нельзя векторизовать, если вы не можете дать больше информации о том, как узнать, какой start=2011 должен идти после какого end=1011 (поскольку их несколько). Даже если это соотношение 1 к 1, если не существует детерминированного способа заранее их упорядочить, кажется, что любое решение будет медленным.
@ r2evans Я понимаю, и ты абсолютно прав. Последующее начало всегда является предыдущим концом, сейчас как вы говорите таких комбинаций много. Это может помочь. Например, первая строка имеет end=2011, и теперь мне нужно найти следующую строку, где start=2011 (поскольку их много, я выбираю самую первую, которую нахожу, и присваиваю ей тот же unique_id). Теперь, когда эта строка также имеет уникальный идентификатор, ее нельзя назначить для unique_id=2, который может иметь все те же самые комбинации имени, начала и конца при первом упоминании. Я создам визуализацию, чтобы помочь.
@r2evans Я добавил изображение, изображающее логику. Чтобы уточнить, вторая таблица отсортирована по имени, идентификатору и, наконец, году. Я подумал, что это может улучшить понимание.
Ответы 2
Вы можете использовать это
df$semi_id <- c(FALSE,
df$start[2:(nrow(df))] < df$start[1:(nrow(df)-1)] |
df$name[2:(nrow(df))] != df$name[1:(nrow(df)-1)])
df$new_unique_id <- 1+ cumsum(df$semi_id)
> df
name cancelled start semi_id new_unique_id
1 ABC NA 2010 FALSE 1
2 ABC NA 2011 FALSE 1
3 ABC NA 2012 FALSE 1
4 ABC NA 2013 FALSE 1
5 ABC NA 2010 TRUE 2
6 ABC 2012 2011 FALSE 2
7 ABC NA 2010 TRUE 3
8 ABC NA 2011 FALSE 3
9 ABC NA 2012 FALSE 3
10 ABC NA 2013 FALSE 3
11 BAA NA 2009 TRUE 4
12 BAA NA 2012 FALSE 4
13 BAA NA 2009 TRUE 5
14 BAA NA 2010 FALSE 5
15 BAA NA 2009 TRUE 6
16 BAA NA 2010 FALSE 6
17 BAA 2011 2009 TRUE 7
Что это делает: df$start[2:(nrow(df))] < df$start[1:(nrow(df)-1)] проверяет, была ли дата раньше, чем дата в предыдущей строке, и df$name[2:(nrow(df))] != df$name[1:(nrow(df)-1)] проверяет, не совпадает ли имя с именем в предыдущей строке. Если имеет место одно из обоих, возвращается TRUE. Позже вы просто добавляете количество TRUE к cumsum(), что дает вам новый столбец идентификатора.
Убедитесь, что данные расположены в правильном порядке, как в вашем вопросе. Данные, которые вы предоставили позже с table_before, находятся в другом порядке строк. В таблице вопроса в столбце start стоит 2010, 2011, ..., а в столбце table_beforestart стоит 2010, 2010, .... Данные не в том порядке. Мой ответ относится к предоставленной вами видимой таблице. Для table_before неясно, как определить новый идентификатор.
Спасибо большое, но это не работает. Я также перепроверял порядок снова и снова, но вставленный мной код R оказался правильным. В результате печати таблицы «до» будет получена та же таблица, что и визуальная таблица вопросов. Я использовал ваш код в этой таблице, но не добился вашего результата: -/ есть только 1,...,4 уникальных идентификатора, а начало/конец также не сортируется.
@Botan Еще раз проверьте еще раз, потому что table_before неверно.
@Botan Вторая запись в столбце start таблицы — 2011, а в table_before вторая запись — 2010, например.
@LulY, чтобы уточнить, вторая таблица - это желаемая таблица, я хочу, чтобы мой код генерировал уникальный_ид, который я могу использовать для группировки/сортировки. В настоящее время у меня есть только table_before и я создал table_after только для желаемого состояния вывода. Код table_before относится к первой визуальной таблице, код table_after — ко второй визуальной таблице. Надеюсь, это поможет прояснить проблему. У меня нет второй таблицы, я хочу иметь такую структуру, которая позволит мне иметь уникальный идентификатор для целей сортировки. Пожалуйста, дайте мне знать, если я неправильно понял, очень жаль!
Не уверен, что это будет хорошо масштабироваться, но мы можем использовать эту функцию для выравнивания/упорядочения строк.
fun <- function(start, end, cnx) {
group <- replace(rep(NA, length(start)), start == min(start),
seq_len(sum(start == min(start))))
start0 <- start
for (rn in seq_along(start0)) {
ind <- which(start0 %in% end[rn] & !cnx[rn])
if (length(ind)) {
group[ind[1]] <- group[rn]
start0[ind[1]] <- NA
}
}
group
}
Единственное предостережение: поскольку при этом находит первое (возможно, из многих) совпадение, это не совсем те группировки, которые есть в ваших данных.
дплир
Используя dplyr_1.1.0; если у вас более старая версия, замените все .by=c(..) на соответствующие перед group_by(..) перед соответствующим глаголом dplyr.
library(dplyr)
tmp <- table_before |>
mutate(.by = name, unique_id = fun(start, end, !is.na(cancelled))) |>
arrange(name, unique_id, start)
tmp |>
summarize(.by = name, prevmax = max(unique_id)) |>
mutate(prevmax = c(0, cumsum(prevmax)[-n()])) |>
right_join(tmp, by = "name") |>
mutate(unique_id = unique_id + prevmax) |>
select(-prevmax)
# name value cancelled start end unique_id
# 1 ABC 77 NA 2010 2011 1
# 2 ABC 44 NA 2011 2012 1
# 3 ABC 11 NA 2012 2013 1
# 4 ABC 16 NA 2013 2014 1
# 5 ABC 66 NA 2010 2011 2
# 6 ABC 33 2012 2011 2012 2
# 7 ABC 55 NA 2010 2011 3
# 8 ABC 22 NA 2011 2012 3
# 9 ABC 44 NA 2012 2013 3
# 10 ABC 10 NA 2013 2014 3
# 11 BAA 33 NA 2009 2012 4
# 12 BAA 15 NA 2012 2013 4
# 13 BAA 22 NA 2009 2010 5
# 14 BAA 54 NA 2010 2011 5
# 15 BAA 45 NA 2009 2010 6
# 16 BAA 42 NA 2010 2011 6
# 17 BAA 23 2011 2009 2011 7
Таблица данных
library(data.table)
DT <- as.data.table(table_before)[, unique_id := fun(start, end, !is.na(cancelled)), by = "name"] |>
setorder(name, unique_id, start)
DT[, .(prevmax = max(unique_id)), by = "name"
][, prevmax := c(0, cumsum(prevmax)[-.N])
][DT, on = "name"
][, unique_id := unique_id + prevmax]
# name prevmax value cancelled start end unique_id
# <char> <num> <num> <num> <num> <num> <num>
# 1: ABC 0 77 NA 2010 2011 1
# 2: ABC 0 44 NA 2011 2012 1
# 3: ABC 0 11 NA 2012 2013 1
# 4: ABC 0 16 NA 2013 2014 1
# 5: ABC 0 66 NA 2010 2011 2
# 6: ABC 0 33 2012 2011 2012 2
# 7: ABC 0 55 NA 2010 2011 3
# 8: ABC 0 22 NA 2011 2012 3
# 9: ABC 0 44 NA 2012 2013 3
# 10: ABC 0 10 NA 2013 2014 3
# 11: BAA 3 33 NA 2009 2012 4
# 12: BAA 3 15 NA 2012 2013 4
# 13: BAA 3 22 NA 2009 2010 5
# 14: BAA 3 54 NA 2010 2011 5
# 15: BAA 3 45 NA 2009 2010 6
# 16: BAA 3 42 NA 2010 2011 6
# 17: BAA 3 23 2011 2009 2011 7
Большое спасибо! Но table_after — это пример изображения желаемой выходной таблицы, в настоящее время у меня нет table_after. Цель состоит в том, чтобы перейти от table_before к table_after с помощью R. Есть ли еще способ решить эту проблему?
Посмотрите мое редактирование @Botan, я думаю, что оно может работать достаточно хорошо с 19-ми строками (хотя это определенно не круто)
Спасибо @LulY, я перепроверил таблицы r, какой порядок здесь неправильный? Ваша помощь очень ценна! Спасибо!