notebook.community

Edit and run 





In [1]:



    


# used to make a query on the data



    











In [10]:



    


labels = '''label_label_scores_Passenger
label_label_scores_Tug_Pilot
label_label_scores_Trawlers
label_label_scores_Pole_and_line
label_label_scores_Squid
label_label_scores_Reefer
label_label_scores_Cargo_Tanker
label_label_scores_Seismic_vessel
label_label_scores_Purse_seines
label_label_scores_Fixed_gear
label_label_scores_Drifting_longlines'''.split("\n")



    











In [11]:



    


'''  CASE
    WHEN GREATEST(is_fishing_label_scores_Fishing, is_fishing_label_scores_Non_fishing) = is_fishing_label_scores_Fishing THEN 'Is_fishing'
    WHEN GREATEST(is_fishing_label_scores_Fishing, is_fishing_label_scores_Non_fishing) = is_fishing_label_scores_Non_fishing THEN 'Non_fishing'
  END AS max_label,'''



    


















    






label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines

















In [13]:



    


all_labels = ",".join(labels)
q = '''CASE
'''
for l in labels:
    q += '''WHEN GREATEST({0}) = {1} THEN REGEXP_REPLACE('{1}','label_label_scores_','')
    '''.format(all_labels,l)
    
q += "END as max_label,\n"
q += '''GREATEST({0}) as max_label_score,'''.format(all_labels)
print q



    


















    






CASE
WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Passenger THEN REGEXP_REPLACE('label_label_scores_Passenger','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Tug_Pilot THEN REGEXP_REPLACE('label_label_scores_Tug_Pilot','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Trawlers THEN REGEXP_REPLACE('label_label_scores_Trawlers','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Pole_and_line THEN REGEXP_REPLACE('label_label_scores_Pole_and_line','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Squid THEN REGEXP_REPLACE('label_label_scores_Squid','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Reefer THEN REGEXP_REPLACE('label_label_scores_Reefer','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Cargo_Tanker THEN REGEXP_REPLACE('label_label_scores_Cargo_Tanker','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Seismic_vessel THEN REGEXP_REPLACE('label_label_scores_Seismic_vessel','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Purse_seines THEN REGEXP_REPLACE('label_label_scores_Purse_seines','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Fixed_gear THEN REGEXP_REPLACE('label_label_scores_Fixed_gear','label_label_scores_','')
    WHEN GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) = label_label_scores_Drifting_longlines THEN REGEXP_REPLACE('label_label_scores_Drifting_longlines','label_label_scores_','')
    END as max_label,
GREATEST(label_label_scores_Passenger,label_label_scores_Tug_Pilot,label_label_scores_Trawlers,label_label_scores_Pole_and_line,label_label_scores_Squid,label_label_scores_Reefer,label_label_scores_Cargo_Tanker,label_label_scores_Seismic_vessel,label_label_scores_Purse_seines,label_label_scores_Fixed_gear,label_label_scores_Drifting_longlines) as max_label_score,

















In [ ]:

Content source: GlobalFishingWatch/vessel-maps

Similar notebooks:

notebook.community | gallery | about