<jupyter_start><jupyter_text>Fisheries Employment Visualizing employment in the fishing sector as a proportion of total employmnet in Natural Sectors (agriculture, forestry, and hunting) *Author: <NAME>Created: 24 Aug 2021Environment: jupyterlab*  Style - Vega chart- Chart type: stacked bar- Value: enumber of people employed  Data - Data: [com_037_rw0_fishing_employment_edit](https://resourcewatch.carto.com/u/wri-rw/dataset/com_037_rw0_fishing_employment_edit)- Source: [ILO](https://ilostat.ilo.org/data/)  Preparation  Vega<jupyter_code>import json
from vega import Vega
from IPython.display import display
def Vega(spec):
    bundle = {}
    bundle['application/vnd.vega.v5+json'] = spec
    display(bundle, raw=True)
widget_width = 800
widget_height = 300<jupyter_output><empty_output><jupyter_text>Visualization The ILO provides employment data collected classified according to the International Standard Industrial Classification (ISIC). Here we use two different "levels" of economic activity. ISIC Level 2 is a more detailed version of the groups in Level 1. Each level is provided as a dataset. Each dataset contains data collected by two revisios of the ISIC classification system- ISIC Revisions 3.1 (before 2007) and 4 (after 2007). More info [here](https://archive.unescwa.org/sites/www.unescwa.org/files/events/files/event_detail_id_681_tablesbtwnisicrev.pdf).At classfiication Level 1, in the 3rd revolution of the classifciation system (Rev 3.1) the Natural Sector Economic Activity was split into two classifications:'A. Agriculture, hunting and forestry' [ECO_ISIC3_A] & 'B. Fishing' [ECO_ISIC3_B]. These will need to be combined. In the 4th revolition of the classifcation system (Rev 4) the Natural Sector Economic Activity is aggregated into one classification: A. Agriculture; forestry and fishing [ECO_ISIC4_A]At classification Level 2, in Rev 3.1 Fishing Economic Activity is classified as '05 - Fishing, aquaculture and service activities incidental to fishing' [EC2_ISIC3_B05]. In Rev 4 Fishing Economic Activity is classified as '03 - Fishing and aquaculture' [EC2_ISIC4_A03].For the purpose of this widget, we want to use data collected *only* according to the most recent classification system. This will avoid changes in values that are a reflection of a change in methodolgy/classification as opposed to an actual change in employment. Therefore, we draw Rev 4 data where available. If a country does not have any employment data using the Rev 4 classficiation system, we draw on their records collected using Rev 3.  Queries  Testing ```gadm.geostore_prod ILIKE 'ca5bfde08587530e76c17fe48362a01d'``` used as stand-in for parameterized ```{{{geostore_env}}='{{geostore_id}}'``` in production version ```sql-- entries for countries where data was collected using rev 4SELECT rev,  gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit     FROM (SELECT area, year, rev, obs_value, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit         FROM com_037_rw0_fishing_employment_edit     WHERE obs_value IS NOT NULL AND rev = 4) data     LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.geostore_prod ILIKE 'ca5bfde08587530e76c17fe48362a01d' UNION -- entries for countries where there is no data for rev 4 (in this case we pull data on the most recent classificiation - rev 3)SELECT rev, gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit     FROM (SELECT area, year, obs_value, rev, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit         FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL) dataLEFT JOIN (SELECT DISTINCT(area), obs_value as rev4 FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL AND rev = 4) rev4 ON rev4.area = data.area LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.geostore_prod ILIKE 'ca5bfde08587530e76c17fe48362a01d' AND rev4.rev4 IS NULLORDER BY date ASC, variable ASC```  Parameterized ```sqlSELECT rev,  gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit     FROM (SELECT area, year, rev, obs_value, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit         FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL AND rev = 4) data LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.{{geostore_env}} ILIKE '{{geostore_id}}' ORDER BY date ASC, variable ASC UNIONSELECT rev, gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit     FROM (SELECT area, year, obs_value, rev, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit         FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL) data     LEFT JOIN (SELECT DISTINCT(area), obs_value as rev4 FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL AND rev = 4) rev4 ON rev4.area = data.area     LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.{{geostore_env}} ILIKE '{{geostore_id}}' AND rev4.rev4 IS NULL ```<jupyter_code>spec=json.loads("""{
  "schema": "https://vega.github.io/schema/vega/v5.json",
  "description": "A searchable, stacked area chart of U.S. occupations from 1850 to 2000.",
  "padding": 5,
  "signals": [
    {
      "name": "sex",
      "value": "Total",
      "bind": {"input": "select", "options": ["Total", "Male", "Female"]}
    }
  ],
  "data": [
    {
      "name": "table",
      "url": "https://wri-rw.carto.com/api/v2/sql?q=SELECT rev, gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit FROM (SELECT area, year, obs_value, rev, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL) data LEFT JOIN (SELECT DISTINCT(area), obs_value as rev4 FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL AND rev = 4) rev4 ON rev4.area = data.area LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.{{geostore_env}} ILIKE '{{geostore_id}}' AND rev4.rev4 IS NULL UNION SELECT rev,  gadm.gid_0 AS gid_0, gadm.name_0 AS name_0, CONCAT(data.type, ' ', data.sex) AS variable, data.year AS date, data.obs_value AS value, data.unit AS unit FROM (SELECT area, year, rev, obs_value, concat(type, ' Employment ') AS type, sex, '1000 people' AS unit FROM com_037_rw0_fishing_employment_edit WHERE obs_value IS NOT NULL AND rev = 4) data LEFT JOIN (SELECT DISTINCT(gid_0), name_0, geostore_prod FROM gadm36_0) AS gadm ON data.area = gadm.gid_0 WHERE gadm.{{geostore_env}} ILIKE '{{geostore_id}}' ORDER BY date ASC, variable ASC ",
      "format": {"type": "json", "property": "rows"},
      "transform": [
        {"type": "stack", "field": "value", "groupby": ["gid_0", "date"]},
        {
          "type": "formula",
          "expr": "split(datum.variable, ' Employment')[0]",
          "as": "sector"
        },
        {
          "type": "formula",
          "expr": "split(datum.variable, '_')[1]",
          "as": "sex"
        },
        {"type": "formula", "expr": "datum.value*1000", "as": "value"},
        {
          "type": "formula",
          "expr": "replace(datum.sector, ' ', '_')",
          "as": "sector"
        },
        {
          "type": "formula",
          "expr": "replace(datum.sector, ' ', '_')",
          "as": "sector"
        },
        {
          "type": "pivot",
          "groupby": ["gid_0", "date", "sex"],
          "field": "sector",
          "value": "value"
        },
        {
          "type": "formula",
          "expr": "datum.Fishing > datum.Natural_Sector_Total ? null: datum.Natural_Sector_Total",
          "as": "Natural_Sector_Total"
        },
        {
          "type": "formula",
          "expr": "datum.Fishing ? datum.Natural_Sector_Total - datum.Fishing: datum.Natural_Sector_Total",
          "as": "Other Natural Sectors"
        },
        {
          "type": "fold",
          "fields": ["Fishing", "Other Natural Sectors"],
          "as": ["sector", "value"]
        },
        {"type": "filter", "expr": "isValid(datum.Natural_Sector_Total)"},
        {
          "type": "stack",
          "field": "value",
          "groupby": ["date", "sex"],
          "sort": {
            "field": ["gid_0", "name_0", "date", "sector"],
            "order": ["descending", "descending","ascending", "descending"]
          }
        }
      ]
    },
    {
      "name": "filter",
      "source": "table",
      "transform": [
        {
          "type": "formula",
          "expr": "test(/M/, datum.sex) ? 'Male': test(/F/, datum.sex) ? 'Female' : 'Total'",
          "as": "sex"
        },
        {"type": "filter", "expr": "datum.sex==sex"}
      ]
    }
  ],
  "scales": [
    {
      "name": "x",
      "type": "band",
      "range": "width",
      "domain": {"data": "filter", "field": "date"}
    },
    {
      "name": "y",
      "type": "linear",
      "range": "height",
      "nice": true,
      "zero": true,
      "domain": {"data": "table", "field": "y1"}
    },
    {
      "name": "color",
      "type": "ordinal",
      "range": ["#84c0d0", "#f3b229"],
      "domain": {"data": "filter", "field": "sector"}
    },
      {
      "name": "legend",
      "type": "ordinal",
      "range": ["#84c0d0", "#f3b229"],
      "domain": ["Fishing", "Other Natural Sectors/ (Agriculture, Forestry, /and Hunting)"]
    }
  ],
  "axes": [
    {
      "orient": "bottom",
      "scale": "x",
      "zindex": 1,
      "labelFontSize": 11,
      "labelOverlap": true
    },
    {
      "orient": "left",
      "scale": "y",
      "zindex": 0,
      "title": "Number of people",
      "titlePadding": 8,
      "titleFontSize": 12,
      "titleFont": "Lato",
      "labelBaseline": "middle",
      "labelAlign": "right",
      "labelPadding": 8,
      "ticks": true,
      "tickSize": 5,
      "grid": true,
      "gridOpacity": 0.2,
      "encode": {
        "labels": {"update": {"text": {"signal": "format(datum.value, ',')"}}}
      }
    }
  ],
  "marks": [
    {
      "type": "rect",
      "from": {"data": "filter"},
      "encode": {
        "enter": {
          "x": {"scale": "x", "field": "date"},
          "width": {"scale": "x", "band": 1, "offset": -1},
          "y": {"scale": "y", "field": "y0"},
          "y2": {"scale": "y", "field": "y1"},
          "fill": {"scale": "color", "field": "sector"}},
        "update": {
          "fillOpacity": [
            {
              "test": "test(/Other Natural Sectors/, datum.sector)",
              "value": 0.8
            },
            {"value": 1}
          ]
        },
        "hover": {"fillOpacity": {"value": 0.5}}
      }
    }
  ],
  "legends": [
    {
      "title": ["Sector:"], 
      "fill": "legend", 
      "orient": "right",
      "labelFont": "Lato",
      "titleFont": "Lato",
      "encode": {
      "labels": {
        "update": {
        "lineBreak": {"value": "/"}
        }}
      }
      }
    ],
   "interaction_config": [
    {
      "name": "tooltip",
      "config": {
        "fields": [
          {
            "column": "name_0",
            "property": "Location",
            "type": "string",
            "format": ""
          },
          {
            "column": "sector",
            "property": "Sector",
            "type": "string",
            "format": ""
          },
           {
            "column": "date",
            "property": "Year",
            "type": "string",
            "format": ""
          },
          {
            "column": "value",
            "property": "Number of people employed",
            "type": "number",
            "format": ""
          }
        ]
      }
    }
  ]
}""")

vega_view=dict(spec)

## DEFINE SIZE
vega_view['height'] = widget_height
vega_view['width'] = widget_width

## PARAMETERIZE WIDGET
vega_view['data'][0]['url']= vega_view['data'][0]['url'].replace('{{geostore_env}}','geostore_prod')
vega_view['data'][0]['url'] = vega_view['data'][0]['url'].replace('{{geostore_id}}','ca5bfde08587530e76c17fe48362a01d')

## ADJUST X AXIS
vega_view['axes'][0]['labelBaseline']= "top"

## FONT
vega_view['axes'][1]['titleFont'] = 'Arial'
vega_view['legends'][0]['labelFont'] = 'Arial'
vega_view['legends'][0]['titleFont'] = 'Arial'

Vega(vega_view)<jupyter_output><empty_output>
{'fraction_non_alphanumeric': 0.1513928765422519, 'fraction_numerical': 0.027702335687126562, 'mean_word_length': 2.975323874151758, 'pattern_counts': {'":': 174, '<': 10, '<?xml version=': 0, '>': 11, 'https://': 5, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 0, 'word_list_counts': {'cursed_substrings.json': 0, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '2', 'dir': 'jupyter-structured-clean-dedup', 'id': '351753', 'license': 'MIT', 'n_tokens_mistral': 4594, 'n_tokens_neox': 4237, 'n_words': 1216, 'path': 'widgets/com_037_fishing_employment/fisheries_employment.ipynb', 'repo_name': 'resource-watch/ocean-watch-data', 'size': '12893'}