Averaging IC50 values

AVG(standard_value) is the query that quietly falsifies a structure-activity relationship. Public bioactivity data may look like a numeric column but three properties of it mandate a closer look.

Three ways the mean misleads

Different measurements. IC50, Ki, and EC50 answer different questions in different systems. Pooling them into one average produces a number with no experimental meaning. It is not conservative, it is undefined.

Censored values. A record of “above 10,000 nM” is not a measurement of 10,000; it is a statement that the assay stopped looking. Averaging qualifiers as if they were values drags inactive compounds toward activity.

Scale. Potencies live on a log scale, a hypothetical compound at 1 nM and one at 1,000 nM average to “about 500 nM”, which describes neither. Aggregation as pIC50 (negative log molar) is preferable.

ChEMBL’s pChEMBL value solves the first and third issue halfway: it standardizes half-maximal-type endpoints to a comparable negative log scale. It deliberately does not solve what no column can: comparability across assays remains your judgment call.

The rule: aggregate on the log scale, within assay-comparable groups, keeping qualifiers and counts visible. If a downstream chart cannot tell you how many measurements and which assay types stand behind a point, the chart is fiction.

In SARVault this is dbt code, not tribal knowledge: censored and flagged records never enter the fact table, the admissible endpoint types are an explicit list, and the marts expose only the median and the maximum pChEMBL per compound and target, with the number of measurements and assays alongside. No naive mean exists for a dashboard to pick up, and the activity cliffs are computed from these summaries rather than from raw values.