Análisis de datos de Naturalista utilizando SQL-Python
Se presenta la página de Naturalista como una opción de fuente de datos para efectuar estudios preliminares sobre abundancia, densidad, riqueza de especies o biodiversidad de algún grupo taxonómico en particular, utilizando MySQL como manejador de los datos y Python como herramienta para el análisis de información.
1. ¿Qué es Naturalista?
Naturalista es una red social de ciencia ciudadana donde sus usuarios pueden aprender sobre plantas, hongos y animales de México y contribuir al conocimiento científico de la naturaleza. La plataforma cuenta con enciclopedia, catálogo fotográfico, mapoteca, y guía de campo. Es considerada por muchos como un pasatiempo educativo, contribuyendo activamente al conocimiento de la naturaleza de México. Presentamos un video de como se usa la apalicación para subir observaciones.
La página de Naturalista se usa como referencia y apoyo en investigaciones científicas como tesis y artículos, principalmente de biología.
Para esta demostración se analizarán datos derivados de observaciones sobre reptiles en México enfocado en serpientes. Estos datos pueden ser localizado en la siguiente liga de Naturalista.
Una vez descargada la información, se presentará cómo subir la datos a través de Python por medio de una red local a MySQL Workbench. Por lo anterior, es requisito para este ejercicio tener preconfigurada una base de datos en MySQL Workbench. Posteriormente, se realizarán consultas a la base de datos utilizando python con el objetivo de realizar un análisis exploratorio de datos.
2. Cargar base de datos en SQL y extraer los datos filtrados para trabajar con ellos
Comenzamos cargando las siguientes librerías en python.
import pandas as pd
import numpy as np
import csv
import pymysql
import matplotlib.pyplot as plt
from sqlalchemy import create_engine
from folium.plugins import MarkerClusterEl primer paso es convertir la base de datos descargada de Naturalista de csv a sql para lograrla cargar al servidor de MySQL Workbench de una manera más sencilla.
Para ello, comenzamos cargando en python localmente la tabla. Utilizamos la función info() para conocer las características del dataframe y de qué tipo son.
df = pd.read_csv('C:/Users/Administrador/Downloads/observations-255685.csv')
df.info()RangeIndex: 157530 entries, 0 to 157529
Data columns (total 53 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 id 157530 non-null int64
1 observed_on_string 157530 non-null object
2 observed_on 157530 non-null object
3 time_observed_at 143235 non-null object
4 time_zone 157492 non-null object
5 user_id 157530 non-null int64
6 user_login 157530 non-null object
7 created_at 157530 non-null object
8 updated_at 157530 non-null object
9 quality_grade 157530 non-null object
10 license 111839 non-null object
11 url 157530 non-null object
12 image_url 157521 non-null object
13 sound_url 13 non-null object
14 tag_list 4366 non-null object
15 description 20621 non-null object
16 num_identification_agreements 157530 non-null int64
17 num_identification_disagreements 157530 non-null int64
18 captive_cultivated 157530 non-null bool
19 oauth_application_id 64826 non-null float64
20 place_guess 156817 non-null object
21 latitude 157530 non-null float64
22 longitude 157530 non-null float64
23 positional_accuracy 124721 non-null float64
24 private_place_guess 14 non-null object
25 private_latitude 14 non-null float64
26 private_longitude 14 non-null float64
27 public_positional_accuracy 127271 non-null float64
28 geoprivacy 5752 non-null object
29 taxon_geoprivacy 133175 non-null object
30 coordinates_obscured 157530 non-null bool
31 positioning_method 32888 non-null object
32 positioning_device 33452 non-null object
33 species_guess 155518 non-null object
34 scientific_name 157530 non-null object
35 common_name 154994 non-null object
36 iconic_taxon_name 157530 non-null object
37 taxon_id 157530 non-null int64
38 taxon_phylum_name 157530 non-null object
39 taxon_subphylum_name 157530 non-null object
40 taxon_superclass_name 0 non-null float64
41 taxon_class_name 157530 non-null object
42 taxon_subclass_name 0 non-null float64
43 taxon_superorder_name 0 non-null float64
44 taxon_order_name 157488 non-null object
45 taxon_suborder_name 140262 non-null object
46 taxon_superfamily_name 3387 non-null object
47 taxon_family_name 156031 non-null object
48 taxon_subfamily_name 106102 non-null object
49 taxon_supertribe_name 0 non-null float64
50 taxon_genus_name 154258 non-null object
51 taxon_genushybrid_name 0 non-null float64
52 taxon_species_name 143137 non-null object
dtypes: bool(2), float64(12), int64(5), object(34)
memory usage: 61.6+ MBAhora, convertimos el dataframe df a formato de sql utilizando pandas. Llamaremos a nuestro archivo ‘serpientes’.
df.to_sql('serpientes', engine, if_exists='replace', index=False)Por medio de la siguiente línea de código, nos conectaremos de manera local al servidor de MySQL Workbench y crearemos la tabla llamada serpientes utilizando el archivo sql que creamos anteriormente.
db_data = 'mysql+mysqldb://' + 'root' + ':' + 'contraseña' + '@' + 'localhost' + ':3306/' \
+ 'serpientes'
engine = create_engine(db_data)Creamos el preámbulo con los datos necesarios para conectarnos al servidor para la consulta de la información que ya hemos subido.
myUser = 'root'
myPass = 'contraseña'
myEndpoint = 'localhost' # Endpoint, para base local es localhost
myPort = 3306
myDb = 'serpientes'
myDataConnect = [myUser, myPass, myEndpoint, myPort, myDb]Definiremos la siguiente función en SQL que permite contectarse al servidor local, filtrar los datos que se deseen obtener y desconectarse del servidor.
def listaxgen(sql, myDataConnect):
connection = pymysql.connect(host=myDataConnect[2], port=myDataConnect[3],
user=myDataConnect[0],
password=myDataConnect[1],
db=myDataConnect[4])
cursor=connection.cursor()
try:
# Execute query
cursor.execute(sql)
# Fetch all the records
result = cursor.fetchall()
field_names = [i[0] for i in cursor.description]
df_RepL = pd.DataFrame(result, columns=field_names)
engine.dispose()
connection.close()
return df_RepL
except:
engine.dispose()
connection.close()Definimos en la variable sql la consulta que deseamos realizar a nuestra tabla. Este query retorna los datos de las observaciones que estén en el suborden de las serpientes, excluyendo las observaciones que no tengan el dato del género de la serpiente, y lo agrupa por género de serpiente con su familia.
Realizamos la consulta con ayuda de la función que definimos antes y guardamos los datos en df_serpientes.
sql="""select taxon_family_name, taxon_genus_name, count(1) Total_de_Observaciones from serpientes where taxon_suborder_name='Serpentes'
and taxon_genus_name is not null
group by taxon_family_name, taxon_genus_name;"""
df_serpientes = listaxgen(sql, myDataConnect)Realizamos un formateo del dataframe y, finalmente, observamos nuestros resultados.
df_serpientes = df_serpientes.set_index('taxon_family_name')
df_serpientes = df_serpientes.sort_index()
df_serpientes = pd.DataFrame(df_serpientes)
df_serpientes
Utilizando el dataframe df_serpiente realizamos una gráfica de las observaciones de los géneros en México, acomodado por familia, resaltando las que son venenosas.
df_serpientes.plot(x="taxon_genus_name", y="Total_de_Observaciones", kind="bar", xlabel='Genero de Serpiente', ylabel='Observaciones', figsize=(16,6))
plt.title('Observaciones de generos de serpientes por familias ')
plt.axvline(x=1.5,color='black', linestyle = ':')
plt.annotate("Anomalepididae",xy=(0,3500))
plt.axvline(x=3.5,color='black', linestyle = ':')
plt.annotate("Boidae",xy=(1,6000))
plt.axvline(x=71.5,color='black', linestyle = ':')
plt.annotate("Colubridae",xy=(45,6000))
plt.axvline(x=78.5,color='black', linestyle = ':')
plt.annotate("Elapidae",xy=(72,6000))
plt.annotate("VENENOSAS",color='red',xy=(71,5800))
plt.axvline(x=74.5,color='black', linestyle = ':')
plt.annotate("Leptotyphlopidae",xy=(74.5,4500))
plt.axvline(x=78.5,color='black', linestyle = ':')
plt.annotate("Loxocemidae",xy=(78.5,3500))
plt.axvline(x=79.5,color='black', linestyle = ':')
plt.annotate("Typhlopidae",xy=(79.5,2500))
plt.axvline(x=81.5,color='black', linestyle = ':')
plt.annotate("Viperidae",xy=(83,6000))
plt.annotate("VENENOSAS",color='red',xy=(82,5800))
Se puede ver que el género más visto es el de las Crotalus, también conocidas como serpientes de cascabel.
De esta base de datos, usando la función que definimos, también podemos georeferenciar observaciones de las serpientes en México, de donde se obtienen 39,205 entradas de todo el país por medio del siguiente bloque de código.
sql="""select taxon_family_name, taxon_genus_name, taxon_species_name, latitude, longitude from serpientes where taxon_suborder_name='Serpentes'
and latitude is not null;"""
df_sgeo=(listaxgen(sql, myDataConnect))
df_sgeo
Adicionalmente, utilizando la libreria folium, estos datos se pueden colocar en un mapa donde, al seleccionar cada punto, indica que especie de serpiente es.
my_map = folium.Map(location = (29.082690, -110.961889), zoom_start =10)
for i in range(0,len(df_sgeo)):
folium.Marker(
location=[df_sgeo.iloc[i]['latitude'], df_sgeo.iloc[i]['longitude']],
popup=df_sgeo.iloc[i]['taxon_species_name'],icon=folium.Icon(color="green",icon="spot")
).add_to(my_map)
my_map
La serpiente que se muestra en la etiqueta de ejemplo es conocida como chicotera y es común en la región de Sonora
Otro ejemplo es, por medio de un query de SQL con la función que definimos, obtener solo la familia Vipiridae que tiene el género más abundante (además de ser venenosas).
sql="""select taxon_family_name, taxon_genus_name, taxon_species_name, latitude, longitude from serpientes where taxon_family_name='Viperidae' and taxon_suborder_name='Serpentes'
and latitude is not null;"""
df_sgeoV=(listaxgen(sql, myDataConnect))
df_sgeoVDe nueva cuenta, se puede generar un mapa de la nueva selección de datos.
my_mapV = folium.Map(location = (29.082690, -110.961889), zoom_start = 10)
for i in range(0,len(df_sgeoV)):
folium.Marker(
location=[df_sgeoV.iloc[i]['latitude'], df_sgeoV.iloc[i]['longitude']],
popup=df_sgeoV.iloc[i]['taxon_species_name'],icon=folium.Icon(color="red",icon="spot")
).add_to(my_mapV)
my_mapV
La serpiente que se muestra en la etiqueta es conocida comúnmente como cascabel de diamantes. Es extremadamente venenosa y de las más distribuidas en la región de Sonora.
Finalmente, se puede repetir esto con cualquier otra familia o género que se desee, por ejemplo, la familia de los Elápidos (que también son venenosas).
sql="""select taxon_family_name, taxon_genus_name, taxon_species_name, latitude, longitude from serpientes where taxon_family_name='Elapidae' and taxon_suborder_name='Serpentes'
and latitude is not null;"""
df_sgeoE=(listaxgen(sql, myDataConnect))
df_sgeoEmy_mapE = folium.Map(location = (29.082690, -110.961889), zoom_start = 12)
for i in range(0,len(df_sgeoE)):
folium.Marker(
location=[df_sgeoE.iloc[i]['latitude'], df_sgeoE.iloc[i]['longitude']],
popup=df_sgeoE.iloc[i]['taxon_species_name'],icon=folium.Icon(color="black",icon="spot")
).add_to(my_mapE)
my_mapE
La serpiente que se muestra en la etiqueta es conocida como coralillo, es muy venenosa, pero tímida y escurridiza. Por lo general huye ante la presencia humana.
Para concluir, por medio de Python podemos realizar un análisis análogo con cualquier fuente de datos disponibles en Naturalista, siendo MySQL Workbench un medio para almacenar de manera ordenada la información obtenida de este portal, facilitando de manera considerable la manipulación de los datos, en especial si deseamos estudiar varias tablas al mismo tiempo.
