Tabla de frecuencias en Calc o Excel utilizando “CONTAR.SI”

Aunque la mejor forma de trabajo es utilizar calc o excel sólo para guardar los datos y utilizar programas externos para analizarlos de forma reproducible, en ocasiones es más fácil y rápido (aunque menos seguro) realizar algunos cálculos directamente en la propia hoja de cálculo.

Si se realizan cálculos en la propia hoja de cálculo, lo ideal es hacerlo en una hoja diferente a la que contiene los datos y los datos originales no tocarlos nunca (o lo menos posible, en cuyo caso es mejor copiar la hoja, para no  tocar el original).

En este post yo voy a utilizar LibreOffice Calc, pero el mismo procedimiento debería funcionar en Microsoft Excel.

Dicho esto, el objetivo de este post es crear una tabla de contingencia a partir de una tabla de datos con observaciones (filas) x variables (columnas). Para el ejemplo utilizaré una tabla de datos de aves recopilada por mis alumnos.

Primeras lineas de la hoja “Aves” de los datos recogidos por mis alumnos. Observar que la notación de cada celda es una letra y un número. Por ejemplo la zona del Ave_001 está en la celda E2.

Tabla de contingencia simple

El primer ejemplo será una tabla de contingencia simple en la que vamos a contar el número de aves en “Campus de la UPV”, “Viveros”, “Blasco Ibañez”, “El río” y “Otros”. Para ello lo primero que hacemos es crear una hoja nueva (Hoja>Insertar hoja>poner nombre>aceptar) y en ella poner los nombres de las zonas que queremos utilizar.

Aunque podríamos utilizar la función “FRECUENCIA” como proponen aquí y aquí, vamos a utilizar mejor la función CONTAR.SI, que es mucho más versátil y nos servirá para luego complicar la selección: Insertar>Función>CONTAR.SI.

Asistente de funciones. Seleccionar la función CONTAR.SI

En esta función nos pide dos cosas, el intervalo y los criterios.

El Intervalo va a ser la columna donde están los datos de “zona”:  Aves.E$1:Aves.E$1000    Importante que el último número sea mayor que el número de observaciones (líneas de la tabla de datos).

El Criterio va a ser: A2, Es decir, que coincida con el valor de A2 en la tabla de contingencia, que es “Campus de la UPV”. También se podría escribir el texto directamente aquí.

por lo que la celda B2 quedará así: =CONTAR.SI(Aves.E$2:Aves.E$1000;A2)

Los $ sirven para que no se muevan los valores al arrastrar.

Al dar a aceptar, ya tenemos el primer valor:

Arrastrando la fórmula podemos tener ya hechas hasta “El río”, pero como no hay categoría “Otros”, para esta no nos vale:

Para calcular los otros, primero vamos a calcular la suma con la función CONTARA, que sólo nos pide el intervalo:

Y ya los “Otros serán el valor de la suma (B7) menos la suma de los valores anteriores (B2:B5):

Tabla de contingencia algo más complicada

Si queremos hacer una tabla más complicada, como por ejemplo la tabla de contingencia con los diferentes nichos de las aves de, “Campus de la UPV”, sólo tenemos que utilizar la función CONTAR.SI.CONJUNTO, para poner varios intervalos y criterios. También pueden ser útiles dos funciones muy interesantes:

  • Y()
  • O()

La primera de ellas da un valor verdadero si se cumple todo lo que hay dentro, mientras que la segunda da un valor verdadero si se cumple alguno de los criterios que hay dentro.

Así por ejemplo la fórmula para las aves del “Campus de la UPV”, que se encuentran “Sobre el cesped a la sombra”, será:

                                   =CONTAR.SI.CONJUNTO($Aves.E2:E1000; “Campus de la UPV”; $Aves.H2:H1000; “Sobre el cesped a la sombra” )

Espero que os sea útil. Si es así o encontráis algún error, por favor, comentar.

Crear capa vectorial de pendientes personalizada a partir de raster poligonizando máscaras

En este post voy a explicar cómo se puede crear una capa vectorial personalizada, con los rangos que se quiera, a partir de una capa raster. En este ejemplo el objetivo es crear una capa vectorial con tres rangos de pendientes: menor de 10%, entre 10 y 20% y más de 20%. Para ello utilizaré la capa raster de pendientes del municipio de marines, obtenida de Terrasit [ver como] y la capa poligonal del municipio obtenida de la web de Arcgis [ver como]. Los pasos a seguir serán:

  1. Recortar la capa raster con el polígono del municipio
  2. Crear máscaras con los valores seleccionados utilizando la calculadora raster
  3. Poligonizar las máscaras
  4. Unir las máscaras poligonales
  5. Crear la variable de texto con los rangos de la pendiente utilizando la calculadora vectorial

    1. Recortar la capa raster con el polígono del municipio

Cuando intentamos recortar la capa raster utilizando la función Clipper [Raster>Extracción>Clipper..], empezamos con los problemas. Resulta que al abrir la capa raster de pendientes sale un aviso de que el SRC no está definido, por lo que habrá que definir el correcto, para que se ponga en el lugar adecuado.crop  Preproyectar2srcara definir el SRC se pincha con el botón derecho en la capa {sta, en el ejemplo}, y se cambia el SRC con el adecuado (Lo normal es que esté en metadatos o en la web de donde se baja). En nuestro caso es el EPSG:32630, que corresponde con el WGS84/UTM zone 30N. Para comprobar que es el correcto lo ideal es comparar con otras capas o con algún mapa de fondo utilizando Quickmapservices u Openlayer plugins [explicado aquí].

Una vez definido, resulta que hay que cambiarlo (reproyectarlo) en el sistema de coordenadas del resto de capas del proyecto, que resulta que no es la misma, ya que todas las capas vectoriales de Terrasit utilizan EPSG:3042 (ETRS89/ETRS-TM30). Para cambiarlo utilizamos la función combar (reproyectar) [Raster>Proyecciones>Combar (reproyectar]. Observar que a la nueva capa reproyectada la hemos llamado {staPend}reproyectarclip3Unaclip2 vez que tenemos las dos capas en el mismo sistema de coordenadas, ya podemos hacer el clip sin problemas [Raster>Extracción>Clipper]. Como en cada operación cambiamos el nombre de la capa a {Pend_Marines}.

2. Crear máscaras con los valores seleccionados utilizando la calculadora raster

Si la calculadora raster de Qgis funcionara como es debido, se podría hacer todo con una sola ecuación, como está explicado en este post. En mi caso la calculadora no hace las sumas (será la versión de Qgis), por lo que sólo puedo multiplicar (lo que al menos me permite crear las máscaras).

Voy a crear tres máscaras a las que voy a llamar Pm10, PM10m20 y PM20, que contendrán respectivamente los puntos con pendiente menor o igual a 10, mayor de 10 y menor o igual que 20 y mayor que 20. Para ello en la calculadora pondré las ecuaciones:calcr

  • Pm10: Pend_Marines@1 <= 10
  • PM10m20: Pend_Marines@1 > 10 AND Pend_Marines@1 <=20
  • PM20: Pend_Marines@1 > 20

Con lo cual ahora tenemos tres capas raster con valores 0 (si aparece nan, cambiar el mínimo a 0) y máximo 1 en las zonas que cumplen la condición.

pm10

3. Poligonizar las máscaras

El siguiente paso es poligonizar las máscaras [Raster>Conversión>Poligonizar]. Es interesante poner diferentes nombres de campo en cada una de las capas, de forma que cuando las unamos sepamos qué columna corresponde a cada clase de pendiente.

pendientes1020

4. Unir las máscaras poligonales

Una vez que tenemos las capas tenemos que unirlas. Se podría utilizar la función unión [Vectorial>Geoproceso>Unión], pero sólo permite hacerlo de dos en dos, por lo que prefiero utilizar la función merge de MMQGIS plugin [MMQGIS>Combine>Merge layers].

Obtenemos una capa con tres columnas en la tabla de atributos, con ceros y unos según estén en cada una de las clases de pendientes.

5. Crear la variable de texto con los rangos de pendiente utilizando la calculadora vectorial

Para crear la variable de pendientes lo que haremos será utilizar la calculadora vectorial de la forma que está explicada en este post.

Supongamos que los nombres de las tres variables son Pm10, PM10m20 y PM20.

Ejemplos 1:

Si lo que queremos es una columna numérica con 10, 20, 40  por ejemplo como valor de la pendiente, la ecuación sería:

"Pm10" * 10 + "PM10m20" * 20 + "PM20" * 40

Ejemplo 2:

Si lo que queremos es un valor de texto más explicativo, la ecuación sería:

CASE
  WHEN "Pm10" = 1 THEN 'Pendiente <= 10'
  WHEN "PM10m20" = 1 THEN 'Pendiente entre 10 y 20'
  WHEN "Pm10" = 1 THEN 'Pendiente menor de 20'
END

O lo que se quiera poner, lógicamente.

Si te es útil este post, comenta.