Notebook

Escape Test: Comparison of Taiwanese and Philippines Netting¶

Abstract Netting from the Philippines retained a significantly higher proportion of beetles that netting from Taiwan. Proportion of beetles remaining in container or entangled in net was 0.62 and 0.21 respectively. Most of the beetles escaping from the Philippines netting were smaller ones (elytra length < 23mm, elytral width < 18). Whereas slightly larger beetles escaped from the Taiwan netting (elytra length < 24mm, elytra width < 19).

In [29]:

%matplotlib inline
import matplotlib.pyplot as plt
import numpy as np
import pandas as pd
import pymysql

In [30]:

connection = pymysql.connect(host='mysql.guaminsects.net', 
                     user='readonlyguest', 
                     passwd='readonlypassword', 
                     database='oryctes')

In [31]:

sql = """
SELECT dateMarked, markNote, releaseNote, COUNT(*) AS count
FROM mark_release_recapture 
WHERE dateMarked IN ('2015-02-16', '2015-03-03', '2015-03-16')
GROUP BY dateMarked, markNote, releaseNote
ORDER BY dateMarked, markNote, releaseNote
"""
df = pd.read_sql(sql, connection)
df

Out[31]:

	dateMarked	markNote	releaseNote	count
0	2015-02-16	A - Taiwan	escaped	9
1	2015-02-16	A - Taiwan	in net	5
2	2015-02-16	A - Taiwan	in peat	1
3	2015-02-16	B - Philippines	escaped	4
4	2015-02-16	B - Philippines	in net	7
5	2015-02-16	B - Philippines	in peat	4
6	2015-02-16	C - no net	escaped	14
7	2015-03-03	A - Philippines	escaped	9
8	2015-03-03	A - Philippines	in net	14
9	2015-03-03	A - Philippines	in peat	3
10	2015-03-03	B - Taiwan	escaped	20
11	2015-03-03	B - Taiwan	in net	5
12	2015-03-03	C - no net	escaped	24
13	2015-03-16	A - Philippines	escaped	13
14	2015-03-16	A - Philippines	in net	10
15	2015-03-16	A - Philippines	in peat	4
16	2015-03-16	B - Taiwan	escaped	24
17	2015-03-16	B - Taiwan	in net	2
18	2015-03-16	B - Taiwan	in peat	1
19	2015-03-16	C - no net	escaped	26
20	2015-03-16	C - no net	in peat	1

# This query was run to fix typos. It is left here for reference. # Running this query will require changing to a user which has UPDATE priveledges sql = """ UPDATE mark_release_recapture SET releaseNote = 'in peat' WHERE releaseNote = 'in pete' """ cursor = connection.cursor() cursor.execute(sql) connection.commit cursor.close() connection.close()

In [32]:

df1 = df[df['markNote'].str.contains('Taiwan')]
total = df1['count'].sum()
escaped = df1['count'][df1['releaseNote'] == 'escaped'].sum()
netting_list = ['Taiwan']
escaped_list = [escaped]
total_list = [total]
df1

Out[32]:

	dateMarked	markNote	releaseNote	count
0	2015-02-16	A - Taiwan	escaped	9
1	2015-02-16	A - Taiwan	in net	5
2	2015-02-16	A - Taiwan	in peat	1
10	2015-03-03	B - Taiwan	escaped	20
11	2015-03-03	B - Taiwan	in net	5
16	2015-03-16	B - Taiwan	escaped	24
17	2015-03-16	B - Taiwan	in net	2
18	2015-03-16	B - Taiwan	in peat	1

In [33]:

df1 = df[df['markNote'].str.contains('Phil')]
total = df1['count'].sum()
escaped = df1['count'][df1['releaseNote'] == 'escaped'].sum()
netting_list.append('Philippines')
escaped_list.append(escaped)
total_list.append(total)
df1

Out[33]:

	dateMarked	markNote	releaseNote	count
3	2015-02-16	B - Philippines	escaped	4
4	2015-02-16	B - Philippines	in net	7
5	2015-02-16	B - Philippines	in peat	4
7	2015-03-03	A - Philippines	escaped	9
8	2015-03-03	A - Philippines	in net	14
9	2015-03-03	A - Philippines	in peat	3
13	2015-03-16	A - Philippines	escaped	13
14	2015-03-16	A - Philippines	in net	10
15	2015-03-16	A - Philippines	in peat	4

In [34]:

df1 = df[df['markNote'].str.contains('no net')]
total = df1['count'].sum()
escaped = df1['count'][df1['releaseNote'] == 'escaped'].sum()
netting_list.append('no net')
escaped_list.append(escaped)
total_list.append(total)
df1

Out[34]:

	dateMarked	markNote	releaseNote	count
6	2015-02-16	C - no net	escaped	14
12	2015-03-03	C - no net	escaped	24
19	2015-03-16	C - no net	escaped	26
20	2015-03-16	C - no net	in peat	1

In [35]:

data = {'netting': netting_list, 'total': total_list, 'escaped': escaped_list}
df2 = pd.DataFrame(data, columns=['netting', 'total', 'escaped'])
df2['contained'] = df2['total'] - df2['escaped']
df2['pcontained'] = df2['contained']/df2['total']
df2

Out[35]:

	netting	total	escaped	contained	pcontained
0	Taiwan	67	53	14	0.208955
1	Philippines	68	26	42	0.617647
2	no net	65	64	1	0.015385

In [36]:

import astropy.stats
ciContained = astropy.stats.binom_conf_interval(df2['contained'], df2['total'])
ciContained

Out[36]:

array([[ 0.16374689,  0.55741416,  0.00589193],
       [ 0.26272371,  0.67446989,  0.03956265]])

In [37]:

df2['lcl'] = ciContained[0]
df2['ucl'] = ciContained[1]
df2['lbar'] = df2['pcontained'] - df2['lcl']
df2['ubar'] = df2['ucl'] - df2['pcontained']
df2.sort('pcontained', inplace=True)
df2.index = range(1,len(df2) + 1)
df2

Out[37]:

	netting	total	escaped	contained	pcontained	lcl	ucl	lbar	ubar
1	no net	65	64	1	0.015385	0.005892	0.039563	0.009493	0.024178
2	Taiwan	67	53	14	0.208955	0.163747	0.262724	0.045208	0.053768
3	Philippines	68	26	42	0.617647	0.557414	0.674470	0.060233	0.056823

In [44]:

yerr = np.array([df2['lbar'],df2['ubar']])
plt.bar(
    left=(1,2,3), 
    height=df2['pcontained'], 
    yerr=yerr,
    color='lightgrey',
    align='center'
)
plt.title('Proportion of beetles remaining in peat or netting')
plt.ylim(-0.1,1)
plt.xticks((1,2,3), ('no net', 'Taiwan netting', 'Philippines netting'))
plt.savefig('barplot.pdf')
plt.show()
# Error bars are 95% binomial confidence intervals

In [39]:

sql = """
SELECT markNote, releaseNote, elytraLength, elytraWidth
FROM mark_release_recapture 
WHERE dateMarked IN ('2015-02-16', '2015-03-03', '2015-03-16')
  AND markNote LIKE '%Taiwan%'
"""
df = pd.read_sql(sql, connection)
df.head()

Out[39]:

	markNote	releaseNote	elytraLength	elytraWidth
0	A - Taiwan	escaped	23.98	18.49
1	A - Taiwan	escaped	22.98	18.09
2	A - Taiwan	escaped	19.25	15.04
3	A - Taiwan	escaped	22.93	17.81
4	A - Taiwan	in net	25.38	19.94

In [40]:

groups = df.groupby('releaseNote')

# Plot
fig, ax = plt.subplots()
ax.margins(0.05) # Optional, just adds 5% padding to the autoscaling
for name, group in groups:
    ax.plot(group.elytraLength, group.elytraWidth, marker='o', linestyle='', ms=12, label=name)
ax.legend(loc='best', numpoints=1)
plt.title('Taiwan netting')
plt.xlabel('elytra length(mm)')
plt.ylabel('elytra width(mm)')
plt.savefig('TaiwanSizes.pdf')
plt.show()

In [41]:

sql = """
SELECT markNote, releaseNote, elytraLength, elytraWidth
FROM mark_release_recapture 
WHERE dateMarked IN ('2015-02-16', '2015-03-03', '2015-03-16')
  AND markNote LIKE '%Philippines%'
"""
df = pd.read_sql(sql, connection)
df.head()

Out[41]:

	markNote	releaseNote	elytraLength	elytraWidth
0	B - Philippines	in net	24.98	19.93
1	B - Philippines	in net	25.03	19.36
2	B - Philippines	in net	26.28	20.67
3	B - Philippines	in net	24.01	19.44
4	B - Philippines	in net	22.15	17.83

In [42]:

groups = df.groupby('releaseNote')

# Plot
fig, ax = plt.subplots()
ax.margins(0.05) # Optional, just adds 5% padding to the autoscaling
for name, group in groups:
    ax.plot(group.elytraLength, group.elytraWidth, marker='o', linestyle='', ms=12, label=name)
ax.legend(loc='best', numpoints=1)
plt.title('Philippines netting')
plt.xlabel('elytra length(mm)')
plt.ylabel('elytra width(mm)')
plt.savefig('PhilippineSizes.pdf')
plt.show()

In [ ]: