1 votos

¿Cómo contar el número de ocurrencias de una determinada secuencia de números dentro de una columna?

Este va a ser un poco extraño, tengo una columna de 750 filas rellena con números enteros que van desde 1 a 10. Estoy tratando de ver que los datos como una serie de 3 filas de secuencias, y contar el número de ocurrencias para cada secuencia como se muestra en la siguiente captura de pantalla :

Tratando de encontrar y contar 3 de la célula secuencias en excel. La columna a es la observación de la columna, con valores enteros de 1 a 3. La columna I es un listado de todas las observadas 3-el valor de las secuencias, y la columna J es el número de ocurrencias de cada una de esas secuencias se observan

La columna a es la observación de la columna, con valores enteros de 1 a 3 para este ejemplo. La columna I es un listado de todas las observadas 3-el valor de las secuencias, y la columna J es el número de ocurrencias de cada una de esas secuencias que se observan. La columna I se muestra como un valor de texto, pero sería mejor que una columna se convirtió en 3 columnas separadas, una para cada valor de la secuencia.

Estoy tratando esto como un paso para crear la observación de la matriz de 2º orden de la cadena de Markov. En una versión anterior, sólo necesitaba un primer orden de la matriz, la cual estaba compuesta de 2 valor de secuencias. He logrado esto mediante la creación de 100 columnas, una para cada combinación posible. Luego en cada vta, de cada una de esas columnas, tuve la célula mirar el valor observado (columna a) para la fila y la fila de arriba, y si la secuencia coincidía con la secuencia de la columna, que sería la salida de un 1. Al final me suman cada columna, y utiliza esa información para generar la cuenta de la observación de la matriz.

Traté de escribir esto como una enorme red de todas las combinaciones posibles de usar en las funciones de la célula, pero rápidamente se hizo evidente que este enfoque no trabajo; 1000 columnas de 750 filas plantea un problema computacional. Se me ocurre que podría ser una manera simple de hacer esto es vba, pero no estoy seguro de si es que es posible. He empezado a enseñar a mí mismo, pero todavía hay mucho que no sé. Es incluso posible, o estoy perdiendo mi tiempo?

Hay dos salidas necesito: necesito la lista de todas las secuencias observadas. Los enteros pueden ser de 1 a 10, pero no todos los 10, o toda combinación de 10 de mayo de estar presente. No necesito las combinaciones que no se producen. También necesito saber el número de veces que cada secuencia se observa.

Estoy ejecutando esto en un PC con Windows 7 utilizando Microsoft Excel 1010. Estoy utilizando Microsoft Excel, ya que es el único programa de matemáticas que tengo, y es con la que me siento más cómodo con.

1voto

Jerome Indefenzo Puntos 201

Usted no necesita Excel. Para empezar, pruebe esto en línea ngram analizador de la herramienta.

En el campo de texto, prueba a poner en 8 3 4 3 1 7 8 3 8 3 8. Elija Using Frequency, y mostrar trigrams que se producen, al menos, one de veces.

Presentar, a continuación, obtener una lista de los trigramas, junto con sus frecuencias. Simplemente ignore las líneas con uno o dos números solamente.


Si usted necesita este comportamiento de forma dinámica y programación, me podrían ayudar a hacer un script que hace exactamente este cálculo basado en la entrada del usuario.

0voto

Ash Puntos 626

Yo no podía dejar de encontrar una solución para esto. He usado R lugar, ya que sólo tiene sentido. El código que se encuentra debajo, y también está disponible en este R-Violín

Por favor, observe que el código de abajo tiene una sección para la generación de burlarse de datos. En la práctica, que en realidad va a tener que reemplazar a que con los datos reales que se almacenan en un vector llamado x como se explicó en el código.

Si no te importa acerca de las observaciones que no ocurren, a continuación, 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)

El resultado 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 usted se preocupa de mostrarle todas las posibilidades, el código es un poco más complicada:

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)

El resultado 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

0voto

Some_Guy Puntos 238

El uso de un ayudante de columna que concatena los datos en grupos de 3 y, a continuación, a) uso countif para contar las secuencias. o b) el uso de una tabla dinámica.

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

countif método

a continuación, poner =COUNTIF(B:B,I2) en J2, obtener los totales, como a continuación.

countif

si no te gusta la 0s, a continuación, sólo autofiltro. Aunque me imagino que va a utilizar un conjunto de datos más grande que este y que probablemente no tenga ninguna.

enter image description here

tabla dinámica

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

Insertar una tabla dinámica basada en la tabla en las columnas a y B. Con "ETIQUETAS de FILA" como la Columna B y los valores como COUNT (no la suma) de la columna B.

enter image description here

enter image description here

Usted no tiene que escribir las secuencias de cálculo, excel sólo encuentra todo en la columna B de forma automática.

También, es una solución generalizada para cualquier longitud de las secuencias, y cualquier número de dígitos utilizados (sólo tiene que añadir más células para su concatenación en la columna B). También, por ejemplo, en busca de 5 dígitos secuencias en los datos:

1
2
3
4
5
5
4
3
2
1

repite 100 filas, se obtiene:

enter image description here

Pedazo de la torta.

EnMiMaquinaFunciona.com

EnMiMaquinaFunciona es una comunidad de administradores de sistemas en la que puedes resolver tus problemas y dudas.
Puedes consultar las preguntas de otros sysadmin, hacer tus propias preguntas o resolver las de los demás.

Powered by:

X