_source_of_shame.sql

CREATE VIEW trigger_ps
AS
SELECT
  loctype,
  sampleloc,
  sampledate,
  watershed,
  paving,
  CASE
    WHEN TCd > 0 AND DCd > 0 AND TSS > 0 THEN '='
    WHEN TCd > 0 AND DCd > 0 AND TSS < 0 THEN '>'
    WHEN TCd > 0 AND DCd < 0 AND TSS < 0 AND - DCd / TCd <= 0.30 THEN '>'
    WHEN TCd > 0 AND DCd < 0 AND TSS < 0 AND - DCd / TCd > 0.30 THEN '>'
    WHEN TCd < 0 AND DCd < 0 AND TSS < 0 THEN '<'
    WHEN TCd < 0 AND DCd < 0 AND TSS > 0 THEN '<'
    WHEN TCd < 0 AND DCd > 0 AND TSS > 0 THEN '<'
    WHEN TCd > 0 AND DCd < 0 AND TSS > 0 AND - DCd / TCd <= 0.30 THEN '>'
    WHEN TCd > 0 AND DCd < 0 AND TSS > 0 AND - DCd / TCd > 0.30 THEN '>'
    WHEN TCd < 0 AND DCd IS NULL AND TSS < 0 THEN '<'
    WHEN TCd < 0 AND DCd IS NULL AND TSS > 0 THEN '<'
    WHEN TCd > 0 AND DCd IS NULL AND TSS > 0 THEN NULL
    WHEN TCd > 0 AND DCd IS NULL AND TSS < 0 THEN NULL
  END AS Qual_Cd,
  CASE
    WHEN TCd > 0 AND DCd > 0 AND TSS > 0 THEN 1e3 * (TCd - DCd) / TSS
    WHEN TCd > 0 AND DCd > 0 AND TSS < 0 THEN 1e3 * (TCd - DCd) / (- 1 * TSS)
    WHEN TCd > 0 AND DCd < 0 AND TSS < 0 AND - DCd / TCd <= 0.30 THEN 1e3 * (TCd + DCd) / (- 1 * TSS)
    WHEN TCd > 0 AND DCd < 0 AND TSS < 0 AND - DCd / TCd > 0.30 THEN 1e3 * (1 - dfcd) * TCd / (- 1 * TSS)
    WHEN TCd < 0 AND DCd < 0 AND TSS < 0 THEN - 999
    WHEN TCd < 0 AND DCd < 0 AND TSS > 0 THEN - 999
    WHEN TCd < 0 AND DCd > 0 AND TSS > 0 THEN - 999
    WHEN TCd > 0 AND DCd < 0 AND TSS > 0 AND - DCd / TCd <= 0.30 THEN 1e3 * (TCd + DCd) / TSS
    WHEN TCd > 0 AND DCd < 0 AND TSS > 0 AND - DCd / TCd > 0.30 THEN 1e3 * (1 - dfcd) * TCd / TSS
    WHEN TCd < 0 AND DCd IS NULL AND TSS < 0 THEN - 999
    WHEN TCd < 0 AND DCd IS NULL AND TSS > 0 THEN - 999
    WHEN TCd > 0 AND DCd IS NULL AND TSS > 0 THEN NULL
    WHEN TCd > 0 AND DCd IS NULL AND TSS < 0 THEN NULL
  END AS PS_Cd,
  TCd,
  DCd,
  CASE
    WHEN TCu > 0 AND DCu > 0 AND TSS > 0 THEN '='
    WHEN TCu > 0 AND DCu > 0 AND TSS < 0 THEN '>'
    WHEN TCu > 0 AND DCu < 0 AND TSS < 0 AND - DCu / TCu <= 0.30 THEN '>'
    WHEN TCu > 0 AND DCu < 0 AND TSS < 0 AND - DCu / TCu > 0.30 THEN '>'
    WHEN TCu < 0 AND DCu < 0 AND TSS < 0 THEN '<'
    WHEN TCu < 0 AND DCu < 0 AND TSS > 0 THEN '<'
    WHEN TCu < 0 AND DCu > 0 AND TSS > 0 THEN '<'
    WHEN TCu > 0 AND DCu < 0 AND TSS > 0 AND - DCu / TCu <= 0.30 THEN '>'
    WHEN TCu > 0 AND DCu < 0 AND TSS > 0 AND - DCu / TCu > 0.30 THEN '>'
    WHEN TCu < 0 AND DCu IS NULL AND TSS < 0 THEN '<'
    WHEN TCu < 0 AND DCu IS NULL AND TSS > 0 THEN '<'
    WHEN TCu > 0 AND DCu IS NULL AND TSS > 0 THEN '='
    WHEN TCu > 0 AND DCu IS NULL AND TSS < 0 THEN '>'
  END AS Qual_Cu,
  CASE
    WHEN TCu > 0 AND DCu > 0 AND TSS > 0 THEN 1e3 * (TCu - DCu) / TSS
    WHEN TCu > 0 AND DCu > 0 AND TSS < 0 THEN 1e3 * (TCu - DCu) / (- 1 * TSS)
    WHEN TCu > 0 AND DCu < 0 AND TSS < 0 AND - DCu / TCu <= 0.30 THEN 1e3 * (TCu + DCu) / (- 1 * TSS)
    WHEN TCu > 0 AND DCu < 0 AND TSS < 0 AND - DCu / TCu > 0.30 THEN 1e3 * (1 - dfcu) * TCu / (- 1 * TSS)
    WHEN TCu < 0 AND DCu < 0 AND TSS < 0 THEN - 999
    WHEN TCu < 0 AND DCu < 0 AND TSS > 0 THEN - 999
    WHEN TCu < 0 AND DCu > 0 AND TSS > 0 THEN - 999
    WHEN TCu > 0 AND DCu < 0 AND TSS > 0 AND - DCu / TCu <= 0.30 THEN 1e3 * (TCu + DCu) / TSS
    WHEN TCu > 0 AND DCu < 0 AND TSS > 0 AND - DCu / TCu > 0.30 THEN 1e3 * (1 - dfcu) * TCu / TSS
    WHEN TCu < 0 AND DCu IS NULL AND TSS < 0 THEN - 999
    WHEN TCu < 0 AND DCu IS NULL AND TSS > 0 THEN - 999
    WHEN TCu > 0 AND DCu IS NULL AND TSS > 0 THEN 1e3 * (1 - dfcu) * TCu / TSS
    WHEN TCu > 0 AND DCu IS NULL AND TSS < 0 THEN 1e3 * (1 - dfcu) * TCu / (- 1 * TSS)
  END AS PS_Cu,
  TCu,
  DCu,
  CASE
    WHEN TPb > 0 AND DPb > 0 AND TSS > 0 THEN '='
    WHEN TPb > 0 AND DPb > 0 AND TSS < 0 THEN '>'
    WHEN TPb > 0 AND DPb < 0 AND TSS < 0 AND - DPb / TPb <= 0.30 THEN '>'
    WHEN TPb > 0 AND DPb < 0 AND TSS < 0 AND - DPb / TPb > 0.30 THEN '>'
    WHEN TPb < 0 AND DPb < 0 AND TSS < 0 THEN '<'
    WHEN TPb < 0 AND DPb < 0 AND TSS > 0 THEN '<'
    WHEN TPb < 0 AND DPb > 0 AND TSS > 0 THEN '<'
    WHEN TPb > 0 AND DPb < 0 AND TSS > 0 AND - DPb / TPb <= 0.30 THEN '>'
    WHEN TPb > 0 AND DPb < 0 AND TSS > 0 AND - DPb / TPb > 0.30 THEN '>'
    WHEN TPb < 0 AND DPb IS NULL AND TSS < 0 THEN '<'
    WHEN TPb < 0 AND DPb IS NULL AND TSS > 0 THEN '<'
    WHEN TPb > 0 AND DPb IS NULL AND TSS > 0 THEN '='
    WHEN TPb > 0 AND DPb IS NULL AND TSS < 0 THEN '>'
  END AS Qual_Pb,
  CASE
    WHEN TPb > 0 AND DPb > 0 AND TSS > 0 THEN 1e3 * (TPb - DPb) / TSS
    WHEN TPb > 0 AND DPb > 0 AND TSS < 0 THEN 1e3 * (TPb - DPb) / (- 1 * TSS)
    WHEN TPb > 0 AND DPb < 0 AND TSS < 0 AND DPb / TPb <= 0.30 THEN 1e3 * (TPb + DPb) / (- 1 * TSS)
    WHEN TPb > 0 AND DPb < 0 AND TSS < 0 AND DPb / TPb > 0.30 THEN 1e3 * (1 - dfpb) * TPb / (- 1 * TSS)
    WHEN TPb < 0 AND DPb < 0 AND TSS < 0 THEN - 999
    WHEN TPb < 0 AND DPb < 0 AND TSS > 0 THEN - 999
    WHEN TPb < 0 AND DPb > 0 AND TSS > 0 THEN - 999
    WHEN TPb > 0 AND DPb < 0 AND TSS > 0 AND - DPb / TPb <= 0.30 THEN 1e3 * (TPb + DPb) / TSS
    WHEN TPb > 0 AND DPb < 0 AND TSS > 0 AND - DPb / TPb > 0.30 THEN 1e3 * (1 - dfpb) * TPb / TSS
    WHEN TPb < 0 AND DPb IS NULL AND TSS < 0 THEN - 999
    WHEN TPb < 0 AND DPb IS NULL AND TSS > 0 THEN - 999
    WHEN TPb > 0 AND DPb IS NULL AND TSS > 0 THEN 1e3 * (1 - dfpb) * TPb / TSS
    WHEN TPb > 0 AND DPb IS NULL AND TSS < 0 THEN 1e3 * (1 - dfpb) * TPb / (- 1 * TSS)
  END AS PS_Pb,
  TPb,
  DPb,
  CASE
    WHEN THg > 0 AND DHg > 0 AND TSS > 0 THEN '='
    WHEN THg > 0 AND DHg > 0 AND TSS < 0 THEN '>'
    WHEN THg > 0 AND DHg < 0 AND TSS < 0 AND - DHg / THg <= 0.30 THEN '>'
    WHEN THg > 0 AND DHg < 0 AND TSS < 0 AND - DHg / THg > 0.30 THEN '>'
    WHEN THg < 0 AND DHg < 0 AND TSS < 0 THEN '<'
    WHEN THg < 0 AND DHg < 0 AND TSS > 0 THEN '<'
    WHEN THg < 0 AND DHg > 0 AND TSS > 0 THEN '<'
    WHEN THg > 0 AND DHg < 0 AND TSS > 0 AND - DHg / THg <= 0.30 THEN '>'
    WHEN THg > 0 AND DHg < 0 AND TSS > 0 AND - DHg / THg > 0.30 THEN '>'
    WHEN THg < 0 AND DHg IS NULL AND TSS < 0 THEN '<'
    WHEN THg < 0 AND DHg IS NULL AND TSS > 0 THEN '<'
    WHEN THg > 0 AND DHg IS NULL AND TSS > 0 THEN '='
    WHEN THg > 0 AND DHg IS NULL AND TSS < 0 THEN '>'
  END AS Qual_Hg,
  CASE
    WHEN THg > 0 AND DHg > 0 AND TSS > 0 THEN 1e3 * (THg - DHg) / TSS
    WHEN THg > 0 AND DHg > 0 AND TSS < 0 THEN 1e3 * (THg - DHg) / (- 1 * TSS)
    WHEN THg > 0 AND DHg < 0 AND TSS < 0 AND DHg / THg <= 0.30 THEN 1e3 * (THg + DHg) / (- 1 * TSS)
    WHEN THg > 0 AND DHg < 0 AND TSS < 0 AND DHg / THg > 0.30 THEN 1e3 * (1 - dfHg) * THg / (- 1 * TSS)
    WHEN THg < 0 AND DHg < 0 AND TSS < 0 THEN - 999
    WHEN THg < 0 AND DHg < 0 AND TSS > 0 THEN - 999
    WHEN THg < 0 AND DHg > 0 AND TSS > 0 THEN - 999
    WHEN THg > 0 AND DHg < 0 AND TSS > 0 AND - DHg / THg <= 0.30 THEN 1e3 * (THg + DHg) / TSS
    WHEN THg > 0 AND DHg < 0 AND TSS > 0 AND - DHg / THg > 0.30 THEN 1e3 * (1 - dfHg) * THg / TSS
    WHEN THg < 0 AND DHg IS NULL AND TSS < 0 THEN - 999
    WHEN THg < 0 AND DHg IS NULL AND TSS > 0 THEN - 999
    WHEN THg > 0 AND DHg IS NULL AND TSS > 0 THEN 1e3 * (1 - dfHg) * THg / TSS
    WHEN THg > 0 AND DHg IS NULL AND TSS < 0 THEN 1e3 * (1 - dfHg) * THg / (- 1 * TSS)
  END AS PS_Hg,
  THg,
  DHg,
  CASE
    WHEN TEQ > 0 AND TSS > 0 THEN '='
    WHEN TEQ > 0 AND TSS < 0 THEN '>'
    WHEN TEQ < 0 AND TSS > 0 THEN '<'
    WHEN TEQ < 0 AND TSS < 0 THEN '<'
  END AS Qual_TEQ,
  CASE
    WHEN TEQ > 0 AND TSS > 0 THEN 1e3 * (TEQ) / TSS
    WHEN TEQ > 0 AND TSS < 0 THEN 1e3 * (TEQ) / (- 1 * TSS)
    WHEN TEQ < 0 AND TSS > 0 THEN - 999
    WHEN TEQ < 0 AND TSS < 0 THEN - 999
  END AS PS_TEQ,
  TEQ,
  CASE
    WHEN TEQ_NoDNQ > 0 AND TSS > 0 THEN '='
    WHEN TEQ_NoDNQ > 0 AND TSS < 0 THEN '>'
    WHEN TEQ_NoDNQ < 0 AND TSS > 0 THEN '<'
    WHEN TEQ_NoDNQ < 0 AND TSS < 0 THEN '<'
  END AS Qual_TEQ_NoDNQ,
  CASE
    WHEN TEQ_NoDNQ > 0 AND TSS > 0 THEN 1e3 * (TEQ_NoDNQ) / TSS
    WHEN TEQ_NoDNQ > 0 AND TSS < 0 THEN 1e3 * (TEQ_NoDNQ) / (- 1 * TSS)
    WHEN TEQ_NoDNQ < 0 AND TSS > 0 THEN - 999
    WHEN TEQ_NoDNQ < 0 AND TSS < 0 THEN - 999 
  END AS PS_TEQ_NoDNQ,
  TEQ_NoDNQ,
  CASE
    WHEN TCDD > 0 AND TSS > 0 THEN '='
    WHEN TCDD > 0 AND TSS < 0 THEN '>'
    WHEN TCDD < 0 AND TSS > 0 THEN '<'
    WHEN TCDD < 0 AND TSS < 0 THEN '<'
  END AS Qual_TCDD,
  CASE
    WHEN TCDD > 0 AND TSS > 0 THEN 1e3 * (TCDD) / TSS
    WHEN TCDD > 0 AND TSS < 0 THEN 1e3 * (TCDD) / (- 1 * TSS)
    WHEN TCDD < 0 AND TSS > 0 THEN - 999
    WHEN TCDD < 0 AND TSS < 0 THEN - 999
  END AS PS_TCDD,
  TCDD,
  TSS
FROM (
    SELECT
      tps.loctype,
      tps.watershed,
      tps.paving,
      tps.sampleloc,
      tps.sampledate,
      tps.TCd,
      tps.DCd,
      tps.TCu,
      tps.DCu,
      tps.TPb,
      tps.DPb,
      tps.THg,
      tps.DHg,
      tps.TCDD,
      tps.TEQ,
      tps.TEQ_NoDNQ,
      tps.TSS,
      DFCd.df AS dfcd,
      DFCu.df AS dfcu,
      DFPb.df AS dfpb,
      DFHg.df AS dfhg
    FROM
      trigger_PS_setup AS tps
    INNER JOIN
      dissolved_fractions AS DFCd ON DFCd.watershed = tps.watershed AND DFCd.analyte = 'Cadmium'
    INNER JOIN
      dissolved_fractions AS DFCu ON DFCu.watershed = tps.watershed AND DFCu.analyte = 'Copper'
    INNER JOIN
      dissolved_fractions AS DFPb ON DFPb.watershed = tps.watershed AND DFPb.analyte = 'Lead'
    INNER JOIN
      dissolved_fractions AS DFHg ON DFHg.watershed = tps.watershed AND DFHg.analyte = 'Mercury'
  WHERE
      tps.TSS IS NOT NULL
) AS src

redemption.py

def computeParticulateStrength(xtab, diss_frac, oddballs, nodissolved):
    for poc in xtab.columns.get_level_values('analyte').unique():
        if poc != 'Total Suspended Solids':
            xtab[(poc, 'PS', 'res')] = xtab.apply(
                lambda row: ps_res(row, poc, diss_frac,
                                   nodissolved=nodissolved,
                                   oddballs=oddballs),
                axis=1
            )

            xtab[(poc, 'PS', 'qual')] = xtab.apply(
                lambda row: ps_qual(row, poc),
                axis=1
            )

            xtab = xtab.sort(axis=1)

    data = (
        xtab.stack(level='analyte')
            .stack(level='fraction')
            .reset_index()
            .assign(units='mg/kg')
    )

    return data


def ps_res(row, poc, dissolved_fractions, oddballs=None, nodissolved=None):
    if oddballs is None:
        oddballs = []

    if nodissolved is None:
        nodissolved = []

    if poc in oddballs:
        ps = _ps_res_oddball(row, poc, dissolved_fractions)
    elif poc in nodissolved:
        ps = _ps_res_no_dissolved(row, poc)
    else:
        ps = _ps_res_standard(row, poc, dissolved_fractions)

    # convert from ug/mg to mg/kg
    if ps is not None:
        ps *= 1000.

    return ps


def ps_qual(row, poc):
    if 'TCDD' in poc:
        qual = _ps_qual_no_dissolved(row, poc)
    else:
        qual = _ps_qual_std(row, poc)

    return qual


def _ps_qual_std(row, poc):
    total = row[(poc, 'Total', 'qual')]
    dissolved = row[(poc, 'Dissolved', 'qual')]
    tss = row[('Total Suspended Solids', 'Total', 'qual')]

    if total == "<":
        qual = "<"
    elif total == '=' and tss == '=' and dissolved != '<':
        qual = "="
    else:
        qual = ">"

    return qual


def _ps_qual_no_dissolved(row, poc):
    total = row[(poc, 'Total', 'qual')]
    tss = row[('Total Suspended Solids', 'Total', 'qual')]

    if total == '<':
        qual = '<'
    elif tss == '<':
        qual = '>'
    else:
        qual = '='

    return qual


def _ps_res_standard(row, poc, dissolved_fractions, ndval=1e-15):
     # pull out the result values
    total = row[(poc, 'Total')]
    dissolved = row[(poc, 'Dissolved')]
    tss = row[('Total Suspended Solids', 'Total')]

    # watershed and dissolved fraction
    outfall = row.name[1]
    avg_dfrac = dissolved_fractions.loc[(outfall, poc)]
    dfrac = dissolved['res'] / total['res']

    # compute PS
    if total['qual'] == '<':
        ps = ndval
    else:
        if pandas.isnull(dissolved['qual']) or (dissolved['qual'] == "<" and dfrac < 0.3):
            ps = (1 - avg_dfrac) * total['res'] / tss['res']
        else:
            ps = (total['res'] - dissolved['res']) / tss['res']

    return ps


def _ps_res_no_dissolved(row, poc, ndval=1e-15):
    total = row[(poc, 'Total')]
    tss = row[('Total Suspended Solids', 'Total')]

    if total['qual'] == '<':
        ps = ndval
    else:
        ps = (total['res']) / tss['res']

    return ps


def _ps_res_oddball(row, analyte, dissolved_fractions, ndval=1e-15):
    # pull out the quantities
    total = row[(analyte, 'Total')]
    dissolved = row[(analyte, 'Dissolved')]
    tss = row[('Total Suspended Solids', 'Total')]

    # get the watershed name, look up the avg. dissolved fraction
    watershed = row.name[1]
    avg_dfrac = dissolved_fractions.loc[(watershed, analyte)]

    # compute the sample's dissolved fration (w/ DLs if non-dteect)
    dfrac = dissolved['res'] / total['res']

    # compute PS
    if total['qual'] == '<':
        ps = ndval
    elif pandas.isnull(dissolved['res']):
        ps = None
    else:
        if dfrac > 0.3:
            ps = (1 - avg_dfrac) * total['res'] / tss['res']
        else:
            ps = (total['res'] - dissolved['res']) / tss['res']

    return ps