notebook.community

Edit and run

Fishing Effort in the Mascarene Plateau





In [51]:



    


import time
import numpy as np
import matplotlib.pyplot as plt
import matplotlib.pyplot as plt
from mpl_toolkits.basemap import Basemap
import matplotlib
from matplotlib import colors,colorbar
import matplotlib
%matplotlib inline
import csv 
import math
from math import radians, cos, sin, asin, sqrt
# from scipy import stats
import matplotlib.dates as mdates



    











In [52]:



    


import argparse
import googleapiclient
from googleapiclient.discovery import build
from googleapiclient.errors import HttpError
from oauth2client.client import GoogleCredentials
credentials = GoogleCredentials.get_application_default()
# Construct the service object for interacting with the BigQuery API.
bigquery_service = build('bigquery', 'v2', credentials=credentials)



    











In [53]:



    


# create a bounding box:
max_lat = 90
min_lat = -90
max_lon = 180
min_lon = -180
cellsize = .25



    











In [54]:



    


def Query(q):
    query_request = bigquery_service.jobs()
    query_data = {
        'query': (q)
    }
    query_response = query_request.query(
            projectId='world-fishing-827',
            body=query_data).execute()
    rows = []
    for row in query_response['rows']:
        s = [item['v'] for item in row['f']]
        rows.append(s)
    return rows



    











In [7]:



    


q = '''
select count(distinct mmsi) 
FROM
  [tilesets.pipeline_2015_08_24_08_19_01]
WHERE
  latitude > '''+str(min_lat)+'''
  AND latitude <'''+str(max_lat)+'''
  AND longitude > '''+str(min_lon)+'''
  AND longitude < '''+str(max_lon)+'''
  AND weight >=.5'''

number_of_mmsi = Query(q)



    











In [9]:



    


print "Number of unique MMSI:",number_of_mmsi[0][0]#int(number_of_mmsi[0]['f'][0]['v'])



    


















    






 Number of unique MMSI: 66245

66,245 MMSI were fishing this region from January 2014 to July 2015





In [34]:



    


q = '''
select a.mmsi mmsi,
a.number number,
if(b.country is null, "invalid mmsi",b.country) country,
b.continent continent
from
(select mmsi, 
integer(if(length(string(mmsi))= 9,LEFT(STRING(mmsi),3), '0')) code,
count(*) number from
(SELECT
  mmsi,
  date(timestamp) date,
  first(latitude) lat,
  first(longitude) lon
FROM
  [tilesets.pipeline_2015_08_24_08_19_01]
WHERE
  latitude > -15
  AND latitude <-5
  AND longitude > 55
  AND longitude <65
  AND weight >=.5
group by mmsi, date) 
group by mmsi, code 
) 
a
left join [scratch_roan.country_code] b
on a.code = b.code 
order by number desc'''

days_by_mmsi = Query(q)

The List of MMSI that were fishing are below





In [11]:



    


for r in days_by_mmsi:
    if "Taiwan" in r[2]:
        r[2] = "Taiwan"
    print "mmsi:", r[0],"  fishing days:", r[1], "  country: ", r[2].split(" (")[0]



    


















    






mmsi: 416000000   fishing days: 257   country:  Taiwan
mmsi: 416237800   fishing days: 115   country:  Taiwan
mmsi: 416002566   fishing days: 105   country:  Taiwan
mmsi: 416002655   fishing days: 97   country:  Taiwan
mmsi: 416214600   fishing days: 96   country:  Taiwan
mmsi: 416241800   fishing days: 96   country:  Taiwan
mmsi: 416001838   fishing days: 96   country:  Taiwan
mmsi: 416239800   fishing days: 91   country:  Taiwan
mmsi: 525000000   fishing days: 79   country:  Indonesia
mmsi: 416004367   fishing days: 77   country:  Taiwan
mmsi: 416236600   fishing days: 74   country:  Taiwan
mmsi: 416002039   fishing days: 70   country:  Taiwan
mmsi: 416229900   fishing days: 69   country:  Taiwan
mmsi: 416002767   fishing days: 69   country:  Taiwan
mmsi: 416004187   fishing days: 66   country:  Taiwan
mmsi: 416002508   fishing days: 65   country:  Taiwan
mmsi: 416004773   fishing days: 59   country:  Taiwan
mmsi: 533333001   fishing days: 55   country:  Malaysia
mmsi: 416244700   fishing days: 55   country:  Taiwan
mmsi: 416002061   fishing days: 54   country:  Taiwan
mmsi: 416000818   fishing days: 52   country:  Taiwan
mmsi: 416002732   fishing days: 48   country:  Taiwan
mmsi: 416243500   fishing days: 47   country:  Taiwan
mmsi: 416011800   fishing days: 43   country:  Taiwan
mmsi: 416004624   fishing days: 42   country:  Taiwan
mmsi: 416003484   fishing days: 41   country:  Taiwan
mmsi: 416000464   fishing days: 39   country:  Taiwan
mmsi: 645378000   fishing days: 38   country:  Mauritius
mmsi: 416004342   fishing days: 38   country:  Taiwan
mmsi: 416223700   fishing days: 38   country:  Taiwan
mmsi: 416004168   fishing days: 38   country:  Taiwan
mmsi: 416002839   fishing days: 38   country:  Taiwan
mmsi: 416002646   fishing days: 37   country:  Taiwan
mmsi: 416803000   fishing days: 35   country:  Taiwan
mmsi: 416002269   fishing days: 35   country:  Taiwan
mmsi: 416004485   fishing days: 34   country:  Taiwan
mmsi: 416002876   fishing days: 33   country:  Taiwan
mmsi: 416004245   fishing days: 33   country:  Taiwan
mmsi: 660002700   fishing days: 33   country:  Reunion
mmsi: 533333009   fishing days: 33   country:  Malaysia
mmsi: 416308000   fishing days: 32   country:  Taiwan
mmsi: 416002151   fishing days: 32   country:  Taiwan
mmsi: 416002235   fishing days: 31   country:  Taiwan
mmsi: 664583000   fishing days: 31   country:  Seychelles
mmsi: 416225500   fishing days: 30   country:  Taiwan
mmsi: 416002407   fishing days: 30   country:  Taiwan
mmsi: 416003800   fishing days: 30   country:  Taiwan
mmsi: 312888000   fishing days: 29   country:  Belize
mmsi: 416002659   fishing days: 29   country:  Taiwan
mmsi: 533333005   fishing days: 29   country:  Malaysia
mmsi: 370599000   fishing days: 27   country:  Panama
mmsi: 664544000   fishing days: 26   country:  Seychelles
mmsi: 416124600   fishing days: 26   country:  Taiwan
mmsi: 416768000   fishing days: 26   country:  Taiwan
mmsi: 416005500   fishing days: 25   country:  Taiwan
mmsi: 416004507   fishing days: 24   country:  Taiwan
mmsi: 416004463   fishing days: 24   country:  Taiwan
mmsi: 416002885   fishing days: 23   country:  Taiwan
mmsi: 664582000   fishing days: 23   country:  Seychelles
mmsi: 416067700   fishing days: 22   country:  Taiwan
mmsi: 533333003   fishing days: 22   country:  Malaysia
mmsi: 664137000   fishing days: 22   country:  Seychelles
mmsi: 416002875   fishing days: 22   country:  Taiwan
mmsi: 533333007   fishing days: 21   country:  Malaysia
mmsi: 416000303   fishing days: 21   country:  Taiwan
mmsi: 461000062   fishing days: 21   country:  Oman
mmsi: 440822000   fishing days: 20   country:  Korea
mmsi: 416000154   fishing days: 19   country:  Taiwan
mmsi: 416004797   fishing days: 19   country:  Taiwan
mmsi: 416004790   fishing days: 19   country:  Taiwan
mmsi: 416002948   fishing days: 19   country:  Taiwan
mmsi: 416004368   fishing days: 19   country:  Taiwan
mmsi: 416002198   fishing days: 18   country:  Taiwan
mmsi: 416004427   fishing days: 18   country:  Taiwan
mmsi: 416002841   fishing days: 18   country:  Taiwan
mmsi: 416002926   fishing days: 17   country:  Taiwan
mmsi: 247354400   fishing days: 17   country:  Italy
mmsi: 416002447   fishing days: 17   country:  Taiwan
mmsi: 660004300   fishing days: 17   country:  Reunion
mmsi: 416002232   fishing days: 16   country:  Taiwan
mmsi: 416702000   fishing days: 16   country:  Taiwan
mmsi: 441734000   fishing days: 16   country:  Korea
mmsi: 416841000   fishing days: 16   country:  Taiwan
mmsi: 416002632   fishing days: 16   country:  Taiwan
mmsi: 441865000   fishing days: 15   country:  Korea
mmsi: 416004378   fishing days: 15   country:  Taiwan
mmsi: 431602690   fishing days: 15   country:  Japan
mmsi: 416094500   fishing days: 14   country:  Taiwan
mmsi: 312729000   fishing days: 14   country:  Belize
mmsi: 412695710   fishing days: 14   country:  China
mmsi: 412695690   fishing days: 13   country:  China
mmsi: 416826000   fishing days: 13   country:  Taiwan
mmsi: 416004772   fishing days: 13   country:  Taiwan
mmsi: 416004675   fishing days: 13   country:  Taiwan
mmsi: 660004900   fishing days: 12   country:  Reunion
mmsi: 416002764   fishing days: 12   country:  Taiwan
mmsi: 224464000   fishing days: 12   country:  Spain
mmsi: 416003953   fishing days: 12   country:  Taiwan
mmsi: 416167900   fishing days: 11   country:  Taiwan
mmsi: 416002325   fishing days: 11   country:  Taiwan
mmsi: 548055100   fishing days: 11   country:  Philippines
mmsi: 660005100   fishing days: 11   country:  Reunion
mmsi: 224069690   fishing days: 11   country:  Spain
mmsi: 416002287   fishing days: 11   country:  Taiwan
mmsi: 416333000   fishing days: 10   country:  Taiwan
mmsi: 416556000   fishing days: 10   country:  Taiwan
mmsi: 577132000   fishing days: 10   country:  Vanuatu
mmsi: 432298000   fishing days: 10   country:  Japan
mmsi: 416002515   fishing days: 10   country:  Taiwan
mmsi: 416002826   fishing days: 9   country:  Taiwan
mmsi: 416002496   fishing days: 9   country:  Taiwan
mmsi: 416004236   fishing days: 9   country:  Taiwan
mmsi: 416001054   fishing days: 9   country:  Taiwan
mmsi: 660001900   fishing days: 8   country:  Reunion
mmsi: 660003800   fishing days: 8   country:  Reunion
mmsi: 416003048   fishing days: 8   country:  Taiwan
mmsi: 416002794   fishing days: 8   country:  Taiwan
mmsi: 416002779   fishing days: 8   country:  Taiwan
mmsi: 312422000   fishing days: 8   country:  Belize
mmsi: 416001900   fishing days: 7   country:  Taiwan
mmsi: 416004095   fishing days: 7   country:  Taiwan
mmsi: 416004718   fishing days: 7   country:  Taiwan
mmsi: 440316000   fishing days: 7   country:  Korea
mmsi: 226312000   fishing days: 7   country:  France
mmsi: 416055600   fishing days: 7   country:  Taiwan
mmsi: 416602000   fishing days: 6   country:  Taiwan
mmsi: 416001769   fishing days: 5   country:  Taiwan
mmsi: 416003724   fishing days: 5   country:  Taiwan
mmsi: 168007   fishing days: 5   country:  invalid mmsi
mmsi: 416808000   fishing days: 5   country:  Taiwan
mmsi: 416003021   fishing days: 5   country:  Taiwan
mmsi: 416771000   fishing days: 4   country:  Taiwan
mmsi: 525010274   fishing days: 4   country:  Indonesia
mmsi: 416002656   fishing days: 4   country:  Taiwan
mmsi: 416000012   fishing days: 4   country:  Taiwan
mmsi: 416000633   fishing days: 4   country:  Taiwan
mmsi: 440099000   fishing days: 4   country:  Korea
mmsi: 416087700   fishing days: 4   country:  Taiwan
mmsi: 416219500   fishing days: 4   country:  Taiwan
mmsi: 416000833   fishing days: 4   country:  Taiwan
mmsi: 200006671   fishing days: 3   country:  invalid mmsi
mmsi: 416088900   fishing days: 3   country:  Taiwan
mmsi: 412989000   fishing days: 3   country:  China
mmsi: 416220900   fishing days: 3   country:  Taiwan
mmsi: 228231700   fishing days: 3   country:  France
mmsi: 525010273   fishing days: 3   country:  Indonesia
mmsi: 416004418   fishing days: 3   country:  Taiwan
mmsi: 416003496   fishing days: 3   country:  Taiwan
mmsi: 416089800   fishing days: 3   country:  Taiwan
mmsi: 416004687   fishing days: 3   country:  Taiwan
mmsi: 412329677   fishing days: 3   country:  China
mmsi: 416002998   fishing days: 3   country:  Taiwan
mmsi: 440329000   fishing days: 2   country:  Korea
mmsi: 416889000   fishing days: 2   country:  Taiwan
mmsi: 416173500   fishing days: 2   country:  Taiwan
mmsi: 416003315   fishing days: 2   country:  Taiwan
mmsi: 412695660   fishing days: 2   country:  China
mmsi: 412695680   fishing days: 2   country:  China
mmsi: 416705000   fishing days: 2   country:  Taiwan
mmsi: 416189600   fishing days: 2   country:  Taiwan
mmsi: 416103900   fishing days: 2   country:  Taiwan
mmsi: 416015700   fishing days: 2   country:  Taiwan
mmsi: 664268000   fishing days: 2   country:  Seychelles
mmsi: 412328742   fishing days: 2   country:  China
mmsi: 416136900   fishing days: 2   country:  Taiwan
mmsi: 225461000   fishing days: 2   country:  Spain
mmsi: 416348000   fishing days: 2   country:  Taiwan
mmsi: 416869000   fishing days: 2   country:  Taiwan
mmsi: 431704490   fishing days: 2   country:  Japan
mmsi: 416002178   fishing days: 2   country:  Taiwan
mmsi: 664567000   fishing days: 2   country:  Seychelles
mmsi: 416002944   fishing days: 1   country:  Taiwan
mmsi: 416704000   fishing days: 1   country:  Taiwan
mmsi: 215482000   fishing days: 1   country:  Malta
mmsi: 888802000   fishing days: 1   country:  invalid mmsi
mmsi: 200006637   fishing days: 1   country:  invalid mmsi
mmsi: 123456789   fishing days: 1   country:  invalid mmsi
mmsi: 416002865   fishing days: 1   country:  Taiwan
mmsi: 416002934   fishing days: 1   country:  Taiwan
mmsi: 664000022   fishing days: 1   country:  Seychelles
mmsi: 440328000   fishing days: 1   country:  Korea
mmsi: 416003015   fishing days: 1   country:  Taiwan
mmsi: 664545000   fishing days: 1   country:  Seychelles
mmsi: 416121600   fishing days: 1   country:  Taiwan
mmsi: 312000125   fishing days: 1   country:  Belize
mmsi: 224922000   fishing days: 1   country:  Spain
mmsi: 412420909   fishing days: 1   country:  China
mmsi: 416238900   fishing days: 1   country:  Taiwan
mmsi: 416002667   fishing days: 1   country:  Taiwan
mmsi: 416002286   fishing days: 1   country:  Taiwan

















In [45]:



    


print days_by_mmsi



    


















    






[{u'f': [{u'v': u'416000000'}, {u'v': u'257'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416237800'}, {u'v': u'115'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002566'}, {u'v': u'105'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002655'}, {u'v': u'97'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416214600'}, {u'v': u'96'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416001838'}, {u'v': u'96'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416241800'}, {u'v': u'96'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416239800'}, {u'v': u'91'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'525000000'}, {u'v': u'79'}, {u'v': u'Indonesia (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004367'}, {u'v': u'77'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416236600'}, {u'v': u'74'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002039'}, {u'v': u'70'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002767'}, {u'v': u'69'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416229900'}, {u'v': u'69'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004187'}, {u'v': u'66'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002508'}, {u'v': u'65'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004773'}, {u'v': u'59'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'533333001'}, {u'v': u'55'}, {u'v': u'Malaysia'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416244700'}, {u'v': u'55'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002061'}, {u'v': u'54'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000818'}, {u'v': u'52'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002732'}, {u'v': u'48'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416243500'}, {u'v': u'47'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416011800'}, {u'v': u'43'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004624'}, {u'v': u'42'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003484'}, {u'v': u'41'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000464'}, {u'v': u'39'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002839'}, {u'v': u'38'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416223700'}, {u'v': u'38'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'645378000'}, {u'v': u'38'}, {u'v': u'Mauritius (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416004168'}, {u'v': u'38'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004342'}, {u'v': u'38'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002646'}, {u'v': u'37'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002269'}, {u'v': u'35'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416803000'}, {u'v': u'35'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004485'}, {u'v': u'34'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'660002700'}, {u'v': u'33'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416004245'}, {u'v': u'33'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'533333009'}, {u'v': u'33'}, {u'v': u'Malaysia'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002876'}, {u'v': u'33'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416308000'}, {u'v': u'32'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002151'}, {u'v': u'32'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664583000'}, {u'v': u'31'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416002235'}, {u'v': u'31'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002407'}, {u'v': u'30'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003800'}, {u'v': u'30'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416225500'}, {u'v': u'30'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'312888000'}, {u'v': u'29'}, {u'v': u'Belize'}, {u'v': u'NA'}]}, {u'f': [{u'v': u'533333005'}, {u'v': u'29'}, {u'v': u'Malaysia'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002659'}, {u'v': u'29'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'370599000'}, {u'v': u'27'}, {u'v': u'Panama (Republic of)'}, {u'v': u'NA'}]}, {u'f': [{u'v': u'664544000'}, {u'v': u'26'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416768000'}, {u'v': u'26'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416124600'}, {u'v': u'26'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416005500'}, {u'v': u'25'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004463'}, {u'v': u'24'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004507'}, {u'v': u'24'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664582000'}, {u'v': u'23'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416002885'}, {u'v': u'23'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416067700'}, {u'v': u'22'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664137000'}, {u'v': u'22'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'533333003'}, {u'v': u'22'}, {u'v': u'Malaysia'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002875'}, {u'v': u'22'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000303'}, {u'v': u'21'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'533333007'}, {u'v': u'21'}, {u'v': u'Malaysia'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'461000062'}, {u'v': u'21'}, {u'v': u'Oman (Sultanate of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'440822000'}, {u'v': u'20'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004797'}, {u'v': u'19'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004790'}, {u'v': u'19'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004368'}, {u'v': u'19'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002948'}, {u'v': u'19'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000154'}, {u'v': u'19'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004427'}, {u'v': u'18'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002198'}, {u'v': u'18'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002841'}, {u'v': u'18'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002447'}, {u'v': u'17'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'660004300'}, {u'v': u'17'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416002926'}, {u'v': u'17'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'247354400'}, {u'v': u'17'}, {u'v': u'Italy'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416702000'}, {u'v': u'16'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'441734000'}, {u'v': u'16'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002232'}, {u'v': u'16'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416841000'}, {u'v': u'16'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002632'}, {u'v': u'16'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'431602690'}, {u'v': u'15'}, {u'v': u'Japan'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004378'}, {u'v': u'15'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'441865000'}, {u'v': u'15'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416094500'}, {u'v': u'14'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'312729000'}, {u'v': u'14'}, {u'v': u'Belize'}, {u'v': u'NA'}]}, {u'f': [{u'v': u'412695710'}, {u'v': u'14'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416826000'}, {u'v': u'13'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'412695690'}, {u'v': u'13'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004675'}, {u'v': u'13'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004772'}, {u'v': u'13'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'660004900'}, {u'v': u'12'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416003953'}, {u'v': u'12'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'224464000'}, {u'v': u'12'}, {u'v': u'Spain'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416002764'}, {u'v': u'12'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'548055100'}, {u'v': u'11'}, {u'v': u'Philippines (Republic of the)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002287'}, {u'v': u'11'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'224069690'}, {u'v': u'11'}, {u'v': u'Spain'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416167900'}, {u'v': u'11'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002325'}, {u'v': u'11'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'660005100'}, {u'v': u'11'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'577132000'}, {u'v': u'10'}, {u'v': u'Vanuatu (Republic of)'}, {u'v': u'Oceania'}]}, {u'f': [{u'v': u'416002515'}, {u'v': u'10'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'432298000'}, {u'v': u'10'}, {u'v': u'Japan'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416333000'}, {u'v': u'10'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416556000'}, {u'v': u'10'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002496'}, {u'v': u'9'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002826'}, {u'v': u'9'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004236'}, {u'v': u'9'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416001054'}, {u'v': u'9'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'660001900'}, {u'v': u'8'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'660003800'}, {u'v': u'8'}, {u'v': u'Reunion (French Department of) - France'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'416002779'}, {u'v': u'8'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002794'}, {u'v': u'8'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003048'}, {u'v': u'8'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'312422000'}, {u'v': u'8'}, {u'v': u'Belize'}, {u'v': u'NA'}]}, {u'f': [{u'v': u'416001900'}, {u'v': u'7'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004718'}, {u'v': u'7'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004095'}, {u'v': u'7'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'226312000'}, {u'v': u'7'}, {u'v': u'France'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416055600'}, {u'v': u'7'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'440316000'}, {u'v': u'7'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416602000'}, {u'v': u'6'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003021'}, {u'v': u'5'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'168007'}, {u'v': u'5'}, {u'v': u'invalid mmsi'}, {u'v': None}]}, {u'f': [{u'v': u'416003724'}, {u'v': u'5'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416001769'}, {u'v': u'5'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416808000'}, {u'v': u'5'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416087700'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'440099000'}, {u'v': u'4'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000833'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416219500'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416771000'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002656'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000633'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416000012'}, {u'v': u'4'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'525010274'}, {u'v': u'4'}, {u'v': u'Indonesia (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416089800'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'412989000'}, {u'v': u'3'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416088900'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416220900'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'200006671'}, {u'v': u'3'}, {u'v': u'invalid mmsi'}, {u'v': None}]}, {u'f': [{u'v': u'228231700'}, {u'v': u'3'}, {u'v': u'France'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'412329677'}, {u'v': u'3'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002998'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004418'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003496'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416004687'}, {u'v': u'3'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'525010273'}, {u'v': u'3'}, {u'v': u'Indonesia (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'431704490'}, {u'v': u'2'}, {u'v': u'Japan'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664268000'}, {u'v': u'2'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'412328742'}, {u'v': u'2'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'412695680'}, {u'v': u'2'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'440329000'}, {u'v': u'2'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416705000'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'225461000'}, {u'v': u'2'}, {u'v': u'Spain'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416103900'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664567000'}, {u'v': u'2'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'412695660'}, {u'v': u'2'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002178'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416189600'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416889000'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416173500'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416869000'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416136900'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416003315'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416348000'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416015700'}, {u'v': u'2'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002286'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002667'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416002865'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416238900'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416121600'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'412420909'}, {u'v': u'1'}, {u'v': u"China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'416704000'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664545000'}, {u'v': u'1'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'440328000'}, {u'v': u'1'}, {u'v': u'Korea (Republic of)'}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'664000022'}, {u'v': u'1'}, {u'v': u'Seychelles (Republic of)'}, {u'v': u'Africa'}]}, {u'f': [{u'v': u'224922000'}, {u'v': u'1'}, {u'v': u'Spain'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'312000125'}, {u'v': u'1'}, {u'v': u'Belize'}, {u'v': u'NA'}]}, {u'f': [{u'v': u'200006637'}, {u'v': u'1'}, {u'v': u'invalid mmsi'}, {u'v': None}]}, {u'f': [{u'v': u'215482000'}, {u'v': u'1'}, {u'v': u'Malta'}, {u'v': u'Europe'}]}, {u'f': [{u'v': u'416002944'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'123456789'}, {u'v': u'1'}, {u'v': u'invalid mmsi'}, {u'v': None}]}, {u'f': [{u'v': u'416003015'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}, {u'f': [{u'v': u'888802000'}, {u'v': u'1'}, {u'v': u'invalid mmsi'}, {u'v': None}]}, {u'f': [{u'v': u'416002934'}, {u'v': u'1'}, {u'v': u"Taiwan (Province of China) - China (People's Republic of)"}, {u'v': u'Asia'}]}]

















In [11]:



    


# Now Group by Country
q = '''
SELECT 
country,
sum(number) number
from
(select a.mmsi mmsi,
a.number number,
if(b.country is null, "invalid mmsi",b.country) country,
b.continent continent
from
(select mmsi, 
integer(if(length(string(mmsi))= 9,LEFT(STRING(mmsi),3), '0')) code,
count(*) number from
(SELECT
  mmsi,
  date(timestamp) date,
  first(latitude) lat,
  first(longitude) lon
FROM
  [tilesets.pipeline_2015_08_24_08_19_01]
WHERE
  latitude > '''+str(min_lat)+'''
  AND latitude <'''+str(max_lat)+'''
  AND longitude > '''+str(min_lon)+'''
  AND longitude < '''+str(max_lon)+'''
  AND weight >=.5
group by mmsi, date) 
group by mmsi, code 
) 
a
left join [scratch_roan.country_code] b
on a.code = b.code)
group by country
order by number desc'''

country_groups = Query(q)



    











In [12]:



    


print "days\tcountry"
for c in country_groups:
    print c[1],'\t', c[0].split(" (")[0]



    


















    






days	country
2530167 	China
675962 	invalid mmsi
371626 	Spain
296913 	Norway
284232 	Italy
239647 	United States of America
232640 	France
222254 	Iceland
174059 	United Kingdom of Great Britain and Northern Ireland
132736 	Russian Federation
122016 	Taiwan
109492 	Korea
99939 	Netherlands
95057 	Japan
90361 	Denmark
80419 	Turkey
78916 	Canada
60300 	Argentine Republic
55400 	Greece
50845 	Ireland
48035 	Germany
45579 	Sweden
33699 	Faroe Islands - Denmark
33447 	Portugal
33329 	Croatia
33003 	Poland
27154 	Australia
24093 	Belgium
20356 	Chile
18604 	South Africa
17274 	Latvia
15458 	New Zealand
15245 	Ghana
14189 	Uruguay
11694 	Finland
10753 	Greenland - Denmark
9863 	Azores - Portugal
9685 	Mexico
8180 	Estonia
8095 	Malta
7846 	Panama
7747 	Vanuatu
6150 	Lithuania
5378 	New Caledonia - France
4989 	Marshall Islands
4922 	Alaska
4868 	Peru
4830 	Bulgaria
4788 	Morocco
4691 	Colombia
4553 	Belize
4221 	Cyprus
4050 	Falkland Islands
3923 	Namibia
3906 	Ukraine
3633 	Israel
3518 	India
3445 	Malaysia
3391 	Ecuador
3365 	Kiribati
3156 	Iran
3146 	Fiji
3136 	Reunion
3022 	Albania
2932 	Papua New Guinea
2584 	Indonesia
2374 	Venezuela
2310 	Madeira - Portugal
2160 	Saint Vincent and the Grenadines
2134 	Nigeria
1896 	Libya
1832 	Qatar
1818 	Seychelles
1662 	Bhutan
1608 	Philippines
1583 	Saint Kitts and Nevis
1548 	Brazil
1478 	Maldives
1420 	Georgia
1347 	Micronesia
1178 	Singapore
1023 	Senegal
965 	Saint Pierre and Miquelon
930 	Azerbaijan
924 	Kerguelen Islands - France
873 	Thailand
844 	Hong Kong
828 	United Arab Emirates
772 	Saint Helena - United Kingdom of Great Britain and Northern Ireland
771 	Comoros
728 	Antigua and Barbuda
719 	Mauritius
656 	Somalia
622 	Anilles, Bonaire, Sint Eustatius and Saba - Netherlands
606 	Cambodia
525 	Iraq
518 	Cook Islands - New Zealand
507 	Barbados
495 	Solomon Islands
490 	Liberia
484 	Sri Lanka
435 	Cabo Verde
423 	Guatemala
415 	Sao Tome and Principe
415 	Mauritania
400 	Dominica
399 	Viet Nam
363 	Mozambique
359 	Kuwait
353 	French Polynesia - France
343 	Crozet Archipelago - France
341 	Macao
338 	Nicaragua
327 	Romania
318 	Oman
279 	Brunei Darussalam
269 	Palau
269 	British Virgin Islands - United Kingdom of Great Britain and Northern Ireland
248 	Rwanda
243 	Sierra Leone
235 	Dominican Republic
220 	Algeria
217 	Cameroon
189 	Lebanon
180 	Slovenia
170 	American Samoa - United States of America
160 	Tajikistan
158 	Anguilla - United Kingdom of Great Britain and Northern Ireland
147 	Suriname
146 	Pitcairn Island - United Kingdom of Great Britain and Northern Ireland
144 	Costa Rica
138 	Martinique
134 	Afghanistan
128 	Sudan
127 	Central African Republic
123 	El Salvador
122 	State of Palestine
120 	Grenada
116 	Syrian Arab Republic
115 	Adelie Land - France
106 	Kyrgyz Republic
106 	Cayman Islands - United Kingdom of Great Britain and Northern Ireland
98 	Moldova
88 	San Marino
88 	Nauru
86 	Cote d'Ivoire
83 	Andorra
82 	Burundi
76 	Slovak Republic
74 	Malawi
71 	Guiana
65 	Zambia
60 	Czech Republic
60 	Swaziland
59 	Democratic People's Republic of Korea
54 	Yemen
51 	Honduras
47 	Saudi Arabia
42 	Gibraltar - United Kingdom of Great Britain and Northern Ireland
42 	Bahrain
38 	Mongolia
37 	Benin
37 	Tanzania
35 	Tonga
28 	Djibouti
21 	Tunisia
20 	Haiti
19 	Trinidad and Tobago
14 	Bangladesh
14 	Switzerland
13 	Burkina Faso
13 	Turkmenistan
12 	Nepal
4 	Lao People's Democratic Republic
2 	Belarus

















In [13]:



    


objects = [c[0].split(" (")[0] for c in country_groups]
y_pos = np.arange(len(objects))
performance = [c[1] for c in country_groups]
 
plt.figure(figsize=(12,4))
plt.bar(y_pos-4, performance, align='center', alpha=0.5,)
plt.xticks(y_pos-4+.2, objects, fontsize=12) # rotation=45
locs, labels = plt.xticks()
plt.setp(labels, rotation=45,ha='right')
plt.ylabel('Usage',fontsize=12)
plt.title('Fishing Days by Country, Globally Jan 2014 to July 2015',fontsize=15)

ax = plt.axes()
ax.xaxis.set_ticks_position('none') 
plt.savefig("fishing_effort_mascarene_by_Country_global.png",bbox_inches='tight',dpi=150,transparent=True,pad_inches=.1)
plt.show()

Map the Fishing Effort in This Region





In [56]:



    


# Now Group by Country
q = '''
SELECT
lat,
lon,
count(*) fishing_days
from
(SELECT
  mmsi,
  date(timestamp) date,
  integer(first(latitude)*4) lat,
  integer(first(longitude)*4) lon
FROM
  [tilesets.pipeline_2015_08_24_08_19_01]
WHERE
  latitude > '''+str(min_lat)+'''
  AND latitude <'''+str(max_lat)+'''
  AND longitude > 0
  AND longitude < '''+str(max_lon)+'''
  AND weight >=.5
  and mmsi != 220364000 //this one is bad
  and mmsi not in(412437961,412437962,412420502,412420503,412420576,412420574,412420789,412420871,
  900025357,900025393,413322650,414203586,412211196,412440255,412440256,412440257,412440258,412440259,
  412440261,150200162,412440077,412440078,412420805,412420421,412440377,412425706,412447093) // these 
  // are mmsi that were spoofing according to Bjorn
group by mmsi, date) 
group by lat, lon
'''

fishing_grid = Query(q)



    











In [57]:



    


q = '''
SELECT
lat,
lon,
count(*) fishing_days
from
(SELECT
  mmsi,
  date(timestamp) date,
  integer(first(latitude)*4) lat,
  integer(first(longitude)*4) lon
FROM
  [tilesets.pipeline_2015_08_24_08_19_01]
WHERE
  latitude > '''+str(min_lat)+'''
  AND latitude <'''+str(max_lat)+'''
  AND longitude > '''+str(min_lon)+'''
  AND longitude < 0
  AND weight >=.5
  and mmsi != 220364000 //this one is bad
  and mmsi not in(412437961,412437962,412420502,412420503,412420576,412420574,412420789,412420871,
  900025357,900025393,413322650,414203586,412211196,412440255,412440256,412440257,412440258,412440259,
  412440261,150200162,412440077,412440078,412420805,412420421,412440377,412425706,412447093) // these 
  // are mmsi that were spoofing according to Bjorn
group by mmsi, date) 
group by lat, lon
'''

fishing_grid += Query(q)



    











In [58]:



    


cellsize = .25
one_over_cellsize = 4

num_lats = (max_lat-min_lat)*one_over_cellsize
num_lons = (max_lon-min_lon)*one_over_cellsize


grid = np.zeros(shape=(num_lats,num_lons))

for row in fishing_grid:
    lat = int(row[0])
    lon = int(row[1])
    if lon!= 180 and lat!=90:
        lat_index = lat-min_lat*one_over_cellsize
        lon_index = lon-min_lon*one_over_cellsize
        grid[lat_index][lon_index] = int(row[2])



    











In [59]:



    


plt.rcParams["figure.figsize"] = [12,7]

cutoff = 0 # 4 degress away from the pole
firstlat = 90-cutoff
lastlat = -90+cutoff
firstlon = -180
lastlon = 180
scale = cellsize
one_over_cellsize = 4

fishing_days_truncated = grid[one_over_cellsize*cutoff:(180/scale)-cutoff*one_over_cellsize][:]

numlats = int((firstlat-lastlat)*one_over_cellsize+.5)
numlons = int((lastlon-firstlon)*one_over_cellsize+.5)
    
lat_boxes = np.linspace(lastlat,firstlat,num=numlats,endpoint=False)
lon_boxes = np.linspace(firstlon,lastlon,num=numlons,endpoint=False)

fig = plt.figure()
m = Basemap(llcrnrlat=lastlat, urcrnrlat=firstlat,
          llcrnrlon=lastlon, urcrnrlon=firstlon, lat_ts=0, projection='robin',resolution="h", lon_0=0)

m.drawmapboundary(fill_color='#111111')
# m.drawcoastlines(linewidth=.2)
m.fillcontinents('#111111',lake_color='#111111')#, lake_color, ax, zorder, alpha)

x = np.linspace(-180, 180, 360*one_over_cellsize)
y = np.linspace(lastlat, firstlat, (firstlat-lastlat)*one_over_cellsize)
x, y = np.meshgrid(x, y)
converted_x, converted_y = m(x, y)
from matplotlib import colors,colorbar

maximum = 100000
minimum = 1

norm = colors.LogNorm(vmin=minimum, vmax=maximum)
# norm = colors.Normalize(vmin=0, vmax=1000)

m.pcolormesh(converted_x, converted_y, fishing_days_truncated, norm=norm, vmin=minimum, vmax=maximum, cmap = plt.get_cmap('viridis'))

t = "Fishing Days, January 2015 to July 2015"
plt.title(t, color = "#ffffff", fontsize=18)

ax = fig.add_axes([0.2, 0.1, 0.4, 0.02]) #x coordinate , 
norm = colors.LogNorm(vmin=minimum, vmax=maximum)
# norm = colors.Normalize(vmin=0, vmax=1000)
lvls = np.logspace(np.log10(minimum),np.log10(maximum),num=8)
cb = colorbar.ColorbarBase(ax,norm = norm, orientation='horizontal', ticks=lvls, cmap = plt.get_cmap('viridis'))

the_labels = []
for l in lvls:
    if l>=1:
        l = int(l)
    the_labels.append(l)

#cb.ax.set_xticklabels(["0" ,round(m3**.5,1), m3, round(m3**1.5,1), m3*m3,round(m3**2.5,1), str(round(m3**3,1))+"+"], fontsize=10)
cb.ax.set_xticklabels(the_labels, fontsize=10, color = "#ffffff")
cb.set_label('Number of Fishing Days, January 2014 to July 2015',labelpad=-40, y=0.45, color = "#ffffff")

ax.text(1.7, -0.5, 'Data Source: Orbcomm\nMap by Global Fishing Watch',
        verticalalignment='bottom', horizontalalignment='right',
        transform=ax.transAxes,
        color='#ffffff', fontsize=6)

plt.savefig("fishing_days_2014-2015_fullrange.png",bbox_inches='tight',dpi=300,transparent=True,pad_inches=.1, facecolor="#000000")
plt.show()



    


















    






/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/ipykernel/__main__.py:11: DeprecationWarning: using a non-integer number instead of an integer will result in an error in the future










    
























In [61]:



    


plt.rcParams["figure.figsize"] = [12,7]

cutoff = 0 # 4 degress away from the pole
firstlat = 90-cutoff
lastlat = -90+cutoff
firstlon = -180
lastlon = 180
scale = cellsize
one_over_cellsize = 4

fishing_days_truncated = grid[one_over_cellsize*cutoff:(180/scale)-cutoff*one_over_cellsize][:]

numlats = int((firstlat-lastlat)*one_over_cellsize+.5)
numlons = int((lastlon-firstlon)*one_over_cellsize+.5)
    
lat_boxes = np.linspace(lastlat,firstlat,num=numlats,endpoint=False)
lon_boxes = np.linspace(firstlon,lastlon,num=numlons,endpoint=False)

fig = plt.figure()
m = Basemap(llcrnrlat=lastlat, urcrnrlat=firstlat,
          llcrnrlon=lastlon, urcrnrlon=firstlon, lat_ts=0, projection='robin',resolution="h", lon_0=0)

m.drawmapboundary(fill_color='#111111')
# m.drawcoastlines(linewidth=.2)
m.fillcontinents('#111111',lake_color='#111111')#, lake_color, ax, zorder, alpha)

x = np.linspace(-180, 180, 360*one_over_cellsize)
y = np.linspace(lastlat, firstlat, (firstlat-lastlat)*one_over_cellsize)
x, y = np.meshgrid(x, y)
converted_x, converted_y = m(x, y)
from matplotlib import colors,colorbar

maximum = 1000
minimum = 1

norm = colors.LogNorm(vmin=minimum, vmax=maximum)
# norm = colors.Normalize(vmin=0, vmax=1000)

m.pcolormesh(converted_x, converted_y, fishing_days_truncated, norm=norm, vmin=minimum, vmax=maximum, cmap = plt.get_cmap('viridis'))

t = "Fishing Days, January 2015 to July 2015"
plt.title(t, color = "#ffffff", fontsize=18)

ax = fig.add_axes([0.2, 0.1, 0.4, 0.02]) #x coordinate , 
norm = colors.LogNorm(vmin=minimum, vmax=maximum)
# norm = colors.Normalize(vmin=0, vmax=1000)
lvls = np.logspace(np.log10(minimum),np.log10(maximum),num=8)
cb = colorbar.ColorbarBase(ax,norm = norm, orientation='horizontal', ticks=lvls, cmap = plt.get_cmap('viridis'))

the_labels = []
for l in lvls:
    if l>=1:
        l = int(l)
    the_labels.append(l)

#cb.ax.set_xticklabels(["0" ,round(m3**.5,1), m3, round(m3**1.5,1), m3*m3,round(m3**2.5,1), str(round(m3**3,1))+"+"], fontsize=10)
cb.ax.set_xticklabels(the_labels, fontsize=10, color = "#ffffff")
cb.set_label('Number of Fishing Days, January 2014 to July 2015',labelpad=-40, y=0.45, color = "#ffffff")

ax.text(1.7, -0.5, 'Data Source: Orbcomm\nMap by Global Fishing Watch',
        verticalalignment='bottom', horizontalalignment='right',
        transform=ax.transAxes,
        color='#ffffff', fontsize=6)

plt.savefig("fishing_days_2014-2015.png",bbox_inches='tight',dpi=300,transparent=True,pad_inches=.1, facecolor="#000000")
plt.show()



    


















    






/Library/Frameworks/Python.framework/Versions/2.7/lib/python2.7/site-packages/ipykernel/__main__.py:11: DeprecationWarning: using a non-integer number instead of an integer will result in an error in the future










    
























In [63]:



    






    


















    
Out[63]:








1440

















In [ ]:

Content source: GlobalFishingWatch/vessel-maps

Similar notebooks:

notebook.community | gallery | about