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

Question

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

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

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

Я пытаюсь сделать это как шаг к созданию матрицы наблюдения цепи Маркова 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-Fiddle

Обратите внимание, что в приведенном ниже коде есть раздел для генерации фиктивных данных. На практике вам придется заменить их фактическими данными, которые будут храниться в векторе с именем 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, а затем a) используйте countif для подсчета последовательностей. или б) использовать сводную таблицу.

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

метод контра

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

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

PivotTable

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

Вставьте сводную таблицу на основе таблицы в столбцы A и B. С "ROW LABELS" в качестве столбца B и значениями как COUNT (не сумма) столбца B.

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

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

1
2
3
4
5
5
4
3
2
1

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

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