Как подсчитать количество появлений определенной последовательности чисел в столбце?

Question

Это будет немного странно, у меня есть столбец из 750 строк, заполненных целыми числами от 1 до 10. Я пытаюсь рассматривать эти данные каксерия из 3-х рядных последовательностей, исчитатьколичество появлений для каждой последовательности, как показано на следующем снимке экрана:

Попытка найти и подсчитать 3 последовательности ячеек в Excel. Столбец A — это столбец наблюдения с целыми значениями от 1 до 3. Столбец I — это список всех наблюдаемых последовательностей из 3 значений, а столбец J — это количество вхождений каждой из этих последовательностей.

Столбец A — это столбец наблюдения с целыми значениями от 1 до 3 для этого примера. Столбец I — это список всех наблюдаемых последовательностей из 3 значений, а столбец J — это количество вхождений каждой из этих последовательностей. Столбец I отображается как текстовое значение, но было бы лучше, если бы один столбец был преобразован в 3 отдельных столбца: по одному для каждого значения в последовательности.

Я пытаюсь сделать это в качестве шага к созданию матрицы наблюдения цепи Маркова 2-го порядка. В предыдущей версии мне нужна была только матрица первого порядка, которая состояла из 2 последовательностей значений. Я добился этого, создав 100 столбцов: по одному для каждой возможной комбинации. Затем в каждой строке каждого из этих столбцов я заставил ячейку смотреть на наблюдаемое значение (столбец A) для этой строки и строки над ней, и если последовательность совпадала с последовательностью для этого столбца, она выводила 1. В конце я суммировал каждый столбец и использовал эту информацию для генерации счетчиков для матрицы наблюдения.

Я попытался записать это как огромную сетку всех возможных комбинаций, используя функции в ячейках, но быстро стало очевидно, что этот подход не сработает; 1000 столбцов из 750 строк представляют собой вычислительную проблему. Мне приходит в голову, что может быть простой способ сделать это с помощью vba, но я не уверен, возможно ли это вообще. Я начал учиться сам, но все еще многого не знаю. Возможно ли это вообще, или я зря трачу время?

Мне нужны два выхода: мне нужен список всех наблюдаемых последовательностей. Целые числа могут быть от 1 до 10, но не все 10, или могут присутствовать все комбинации из 10. Мне не нужны комбинации, которые не встречаются. Мне также нужно знать, сколько раз наблюдается каждая последовательность.

Я запускаю это на ПК с Windows 7, используя Microsoft Excel 1010. Я использую Microsoft Excel, потому что это единственная математическая программа, которая у меня есть, и с ней мне удобнее всего работать.

Answer 1

Вам не нужен Excel. Для начала попробуйтеэтот онлайн-анализатор ngram.

В текстовом поле попробуйте ввести 8 3 4 3 1 7 8 3 8 3 8. Выберите Using Frequencyи покажите trigrams, что встречается не реже одного oneраза.

Отправьте его, и вы получите список триграмм вместе с их частотами. Просто игнорируйте строки с одним или двумя числами.

---

Если вам необходимо реализовать такое поведение динамически и программно, я могу помочь вам создать скрипт, который будет выполнять именно такие вычисления на основе пользовательского ввода.

Answer 2

Я не мог не найти решение для этого. Я использовал R вместо этого, потому что это просто имеет смысл. Код ниже, а также доступен на этомR-скрипка

Обратите внимание, что в коде ниже есть раздел для генерации фиктивных данных. На практике вам придется заменить его вашими реальными данными, которые будут храниться в векторе, который называется, xкак описано в коде.

Если вас не волнуют наблюдения, которые не происходят, то код очень и очень прост:

x <- c("01", "02", "03", "01", "02", "03", "01", "02 ", "03") # your Column A
n <- 3 # number of elements in each combination. configurable.

# create a vector with n-sized sequences of characters. (e.g. n = 3 -> "XX-YY-ZZ")
mydata <- x
for (i in 2:n) {
    y <- c(x[-i], x[i])
    mydata <- paste(mydata, y, sep="-")
}

# calculate the frequency of each observation and save into data table
frequencies <- data.frame(table(mydata))
head(frequencies)

Тогда вывод будет примерно таким:

    mydata Freq
1 01-02-02    2
2 01-04-04    2
3 01-05-05    1
4 01-07-07    1
5 01-10-10    1
6 02-02-02    1

Если вы хотите показать все возможности, код будет немного запутаннее:

n <- 3 # number of elements in each combination. configurable.

# -----------------------------------------------------------------------------------#
#            THIS PART SIMPLY GENERATES MOCK DATA. REPLACE WITH ACTUAL DATA          #
# -----------------------------------------------------------------------------------#
universe <- 1:10 # your range of numbers
m <- 100 # number of rows in the mock data

# generate some mock data with a simple m-sized vector of numbers within 'universe'
set.seed(1337) # hardcode random seed so mock data can be reproduced
x <- sample(universe, m, replace=TRUE)
x <- formatC(x, width=nchar(max(universe)), flag=0) # pad our data with 0s as needed
# -----------------------------------------------------------------------------------#
#                               END OF MOCK DATA PART                                #
# -----------------------------------------------------------------------------------#
# At this point, you should have a variable x which contains a sequence of
# numbers stored as characters (text) e.g. "01" "04" "10" "04" "06"

# create a vector with n-sized sequences of characters. (e.g. n = 3 -> "XX-YY-ZZ")
mydata <- x
for (i in 2:n) {
    y <- c(x[-i], x[i])
    mydata <- paste(mydata, y, sep="-")
}

# calculate the frequency of each observation and save into data table
frequencies <- data.frame(table(mydata))

# generate all possible permutations and save them to a data table called
p <- as.matrix(expand.grid(replicate(n, universe, simplify=FALSE)))
p <- formatC(p, width=nchar(max(universe)), flag=0)
q <- apply(p, 1, paste, collapse="-")
permutations <- data.frame(q, stringsAsFactors=FALSE) # broken into separate   step for nicer variable name in df
permutations$Freq <- 0 # fill with zeroes 
permutations$Freq[match(frequencies$mydata, permutations$q)] <- frequencies$Freq

head(permutations)

Вывод будет примерно таким:

         q Freq
1 01-01-01    0
2 02-01-01    0
3 03-01-01    2
4 04-01-01    0
5 05-01-01    1
6 06-01-01    0

Answer 3

Используйте вспомогательный столбец, который объединяет данные в группы по 3, а затем а) ​​используйте countif для подсчета последовательностей. или б) используйте сводную таблицу.

В ячейку B2 поместите =CONCATENATE(A2,",",A3,",",A4)и перетащите вниз (дважды щелкните в правом нижнем углу)

метод countif

затем, подставив =COUNTIF(B:B,I2)J2, вы получите итоги, как показано ниже.

если вам не нравятся нули, то просто используйте автофильтр. Хотя я предполагаю, что вы будете использовать больший набор данных, чем этот, и, вероятно, у вас их не будет.

поворотный стол

Более продвинутым и, по-моему, более элегантным решением было бы использование сводной таблицы. Используя ту же формулу в столбце B.

Вставьте сводную таблицу на основе таблицы в столбцах A и B. С «МЕТКАМИ СТРОК» в качестве столбца B и значениями в виде COUNT (не суммы) столбца B.

Вам не нужно вводить последовательности для подсчета, Excel просто автоматически найдет все в столбце B.

Кроме того, это обобщенное решение для любой длины последовательностей и любого количества используемых цифр (просто добавьте больше ячеек к вашей конкатенации в столбце B). Также, например, поиск 5-значных последовательностей в данных:

1
2
3
4
5
5
4
3
2
1

повторение для 100 строк дает:

Кусок пирога.