Como posso contar o número de ocorrências de uma sequência específica de números dentro de uma coluna?

Question

Isso vai ser um pouco estranho, tenho uma coluna de 750 linhas preenchidas com números inteiros variando de 1 a 10. Estou tentando olhar para esses dados como umsérie de sequências de 3 linhas, econtaro número de ocorrências para cada sequência, conforme mostrado na captura de tela a seguir:

Tentando encontrar e contar 3 sequências de células no Excel. A coluna A é a coluna de observação, com valores inteiros de 1-3. A coluna I é uma lista de todas as sequências de 3 valores observadas e a coluna J é o número de ocorrências em que cada uma dessas sequências é observada

A coluna A é a coluna de observação, com valores inteiros de 1 a 3 para este exemplo. A coluna I é uma lista de todas as sequências de 3 valores observadas e a coluna J é o número de ocorrências em que cada uma dessas sequências é observada. A coluna I é exibida como um valor de texto, mas seria melhor transformar essa coluna em 3 colunas separadas; um para cada valor na sequência.

Estou tentando fazer isso como uma etapa para criar a matriz de observação de uma cadeia de Markov de 2ª ordem. Numa versão anterior, eu precisava apenas de uma matriz de primeira ordem, composta por 2 sequências de valores. Consegui isso criando 100 colunas; um para cada combinação possível. Então, em cada linha de cada uma dessas colunas, fiz com que a célula olhasse para o valor observado (coluna A) dessa linha e da linha acima dela, e se a sequência correspondesse à sequência dessa coluna, a saída seria 1. No final, somei cada coluna e usei essas informações para gerar as contagens para a matriz de observação.

Tentei escrever isso como uma grade enorme de todas as combinações possíveis usando funções celulares, mas rapidamente ficou óbvio que essa abordagem não funcionaria; 1.000 colunas de 750 linhas representam um problema computacional. Parece-me que pode haver uma maneira simples de fazer isso, vba, mas não tenho certeza se isso é possível. Comecei a aprender sozinho, mas ainda há muita coisa que não sei. É mesmo possível ou estou perdendo meu tempo?

Preciso de duas saídas: preciso da lista de todas as sequências observadas. Os números inteiros podem ser de 1 a 10, mas nem todos os 10 ou todas as combinações de 10 podem estar presentes. Não preciso das combinações que não ocorrem. Também preciso saber o número de vezes que cada sequência é observada.

Estou executando isso em um PC com Windows 7 usando o Microsoft Excel 1010. Estou usando o Microsoft Excel porque é o único programa de matemática que tenho e com o qual me sinto mais confortável.

Answer 1

Você não precisa do Excel. Para começar, tenteesta ferramenta online de análise de ngram.

No campo de texto, tente inserir 8 3 4 3 1 7 8 3 8 3 8. Escolha Using Frequencye mostre trigramsque isso ocorre pelo menos onevezes.

Envie-o e você obterá uma lista de trigramas junto com suas frequências. Apenas ignore as linhas com apenas um ou dois números.

---

Se você precisar desse comportamento de forma dinâmica e programática, eu poderia ajudá-lo a criar um script que faça exatamente esse cálculo com base na entrada do usuário.

Answer 2

Não pude deixar de encontrar uma solução para isso. Em vez disso, usei R, porque faz sentido. O código está abaixo e também disponível nesteR-violino

Observe que o código abaixo possui uma seção para gerar dados simulados. Na prática, você terá que substituir isso pelos dados reais, que seriam armazenados em um vetor chamado xconforme explicado no código.

Se você não se importa com as observações que não ocorrem, o código é muito simples:

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)

A saída então será algo como:

    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

Se você se preocupa em mostrar todas as possibilidades, o código é um pouco mais confuso:

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)

A saída será algo como:

         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

Use uma coluna auxiliar que concatene os dados em grupos de 3 e, em seguida, a) use countif para contar as sequências. ou b) usar uma tabela dinâmica.

Na célula B2 coloque =CONCATENATE(A2,",",A3,",",A4)e arraste para baixo (clique duas vezes no canto inferior direito)

método contagem se

então, colocando =COUNTIF(B:B,I2)J2, você obtém os totais, conforme abaixo.

se você não gosta dos 0s, basta filtrar automaticamente. Embora eu imagine que você usará um conjunto de dados maior do que este e provavelmente não terá nenhum.

articulável

Uma solução mais avançada e mais elegante seria usar uma plataforma giratória. Usando a mesma fórmula na coluna B.

Insira uma tabela dinâmica com base na tabela nas colunas A e B. Com "ROW LABELS" como coluna B e valores como COUNT (não soma) da coluna B.

Você não precisa digitar as sequências a serem contadas, o Excel apenas encontra tudo na coluna B automaticamente.

Além disso, é uma solução generalizada para qualquer comprimento de sequências e qualquer número de dígitos usados ​​(basta adicionar mais células à sua concatenação na coluna B). Além disso, por exemplo, procurando sequências de 5 dígitos nos dados:

1
2
3
4
5
5
4
3
2
1

repetido por 100 linhas dá:

Pedaco de bolo.