¿Cómo cuento el número de apariciones de una secuencia específica de números dentro de una columna?

Question

Esto va a ser un poco extraño, tengo una columna de 750 filas llenas de números enteros que van del 1 al 10. Estoy tratando de ver esos datos como unaserie de secuencias de 3 filas, ycontarel número de apariciones de cada secuencia como se muestra en la siguiente captura de pantalla:

Intentando encontrar y contar secuencias de 3 celdas en Excel. La columna A es la columna de observación, con valores enteros del 1 al 3. La columna I es una lista de todas las secuencias de 3 valores observadas y la columna J es el número de apariciones de cada una de esas secuencias.

La columna A es la columna de observación, con valores enteros del 1 al 3 para este ejemplo. La columna I es una lista de todas las secuencias de 3 valores observadas y la columna J es el número de apariciones de cada una de esas secuencias. La columna I se muestra como un valor de texto, pero sería mejor convertir esa columna en 3 columnas separadas; uno para cada valor de la secuencia.

Estoy intentando esto como un paso para crear la matriz de observación de una cadena de Markov de segundo orden. En una versión anterior, solo necesitaba una matriz de primer orden, que estaba compuesta por 2 secuencias de valores. Logré esto creando 100 columnas; uno para cada combinación posible. Luego, en cada fila de cada una de esas columnas, hice que la celda observara el valor observado (columna A) para esa fila y la fila encima de ella, y si la secuencia coincidía con la secuencia de esa columna, generaría un 1. Al final, resumí cada columna y usé esa información para generar los recuentos de la matriz de observación.

Intenté escribir esto como una cuadrícula masiva de todas las combinaciones posibles usando funciones de celda, pero rápidamente se hizo obvio que este enfoque no funcionaría; 1000 columnas de 750 filas plantean un problema computacional. Se me ocurre que podría haber una manera sencilla de hacer esto: vba, pero no estoy seguro de que sea posible. He empezado a aprender por mi cuenta, pero todavía hay muchas cosas que no sé. ¿Es posible o estoy perdiendo el tiempo?

Necesito dos resultados: necesito la lista de todas las secuencias observadas. Los números enteros pueden ser del 1 al 10, pero no todos los 10 o todas las combinaciones de 10 pueden estar presentes. No necesito las combinaciones que no ocurren. También necesito saber la cantidad de veces que se observa cada secuencia.

Estoy ejecutando esto en una PC con Windows 7 usando Microsoft Excel 1010. Estoy usando Microsoft Excel porque es el único programa matemático que tengo y con el que me siento más cómodo.

Answer 1

No necesitas Excel. Para empezar, pruebaesta herramienta de análisis de ngram en línea.

En el campo de texto, intenta poner 8 3 4 3 1 7 8 3 8 3 8. Elija Using Frequencyy muestre trigramsque ocurren al menos oneveces.

Envíelo y obtendrá una lista de trigramas junto con sus frecuencias. Simplemente ignore las líneas con uno o dos números únicamente.

---

Si necesita este comportamiento de forma dinámica y programática, podría ayudarlo a crear un script que haga exactamente este cálculo según la entrada del usuario.

Answer 2

No pude evitar encontrar una solución para esto. Usé R en su lugar, porque tiene sentido. El código está a continuación y también está disponible en esteR-violín

Tenga en cuenta que el código siguiente tiene una sección para generar datos simulados. En la práctica, tendrás que reemplazarlos con tus datos reales que se almacenarían en un vector llamado xcomo se explica en el código.

Si no le importan las observaciones que no ocurren, entonces el código es muy simple:

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)

La salida entonces 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

Si te importa mostrar todas las posibilidades, el código es un poco más complicado:

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)

La salida 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

Utilice una columna auxiliar que concatene los datos en grupos de 3 y luego a) utilice countif para contar las secuencias. o b) utilizar una tabla dinámica.

En la celda B2, coloque =CONCATENATE(A2,",",A3,",",A4)y arrastre hacia abajo (haga doble clic en la esquina inferior derecha)

método de conteo

luego, al =COUNTIF(B:B,I2)ingresar J2, se obtienen los totales, como se muestra a continuación.

Si no te gustan los 0, simplemente filtra automáticamente. Aunque imagino que utilizarás un conjunto de datos más grande que este y probablemente no tendrás ninguno.

tabla dinámica

Una solución más avanzada y, en mi humilde opinión, más elegante sería utilizar una tabla dinámica. Usando la misma fórmula en la columna B.

Inserte una tabla dinámica basada en la tabla en las columnas A y B. Con "ETIQUETAS DE FILA" como columna B y valores como RECUENTO (no suma) de la columna B.

No es necesario escribir las secuencias que se van a contar, Excel simplemente encuentra todo en la columna B automáticamente.

Además, es una solución generalizada para cualquier longitud de secuencias y cualquier número de dígitos utilizados (simplemente agregue más celdas a su concatenación en la columna B). Además, por ejemplo, buscar secuencias de 5 dígitos en los datos:

1
2
3
4
5
5
4
3
2
1

repetido durante 100 filas da:

Pedazo de pastel.