# Why are there some inconsistencies in evidence count?

**URL:** <https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191>\
**Category:** Technical Support\
**Created:** [6 September 2023 08:10 UTC](https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191 "2023-09-06T08:10:24Z")\
**Posts on this page:** 4\
**Page:** 1

<div class="post-metadata">

**Author:** ![charlie](https://avatars.discourse-cdn.com/v4/letter/c/d6d6ee/32.png) [@charlie](https://community.opentargets.org/u/charlie)\
**Post date:** [6 September 2023 08:10 UTC](https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191/1 "2023-09-06T08:10:25Z")

</div>

Hello Open Targets,

My main goal is to find all evidence that is used to calculate the association score. For this purpose, I encountered two related problems listed as follows.

1. Inconsistency between Webpage and BigQuery

On the webpage entitled “Evidence for JAK2 in myeloproliferative disorder”

Link: [Open Targets Platform](https://platform.opentargets.org/evidence/ENSG00000096968/EFO_000425)

Figure 1:

 ![webpage](https://europe1.discourse-cdn.com/flex017/uploads/opentargets/original/1X/fb6d00d1886899987415b6fcfe6638e80a9c27e5.jpeg)

There are 11 entries and 8 entries annotated under the Cancer Gene Census and the ClinVar (somatic) respectively. (I box them in red)

However, if I use Google Cloud BigQuery (codes in Figure 2) to see evidenceCount, there is one inconsistent part: there are only 7 pieces of evidence under the “eva\_somatic” (Figure 3) instead of 8 entries shown on the web page. Could you point out there are any mistakes I made in the query or any misunderstanding to this issue?

Figure 2:

 ![code](https://europe1.discourse-cdn.com/flex017/uploads/opentargets/original/1X/7e4958c206c7a3d5047ead27b398dd8db887f6e0.png)

Figure 3:

 ![evidenceCount](https://europe1.discourse-cdn.com/flex017/uploads/opentargets/original/1X/8280f763592339e564da74b687a2d32c0d549378.png)

1. How to obtain indirect evidence via BigQuery?

As you pointed out that indirect association scores are calculated from the indirect evidence in the issue below, I tried to find indirect evidence via BigQuery.

> [@The number of evidences from "open-targets-prod.platform.evidence" does not match "evidenceCount"](https://community.opentargets.org/t/the-number-of-evidences-from-open-targets-prod-platform-evidence-does-not-match-evidencecount/1165):
>
> Hello Open Targets, My main goal is to understand how the association scores shown on the website are calculated by Harmonic Sum from scratch. First, I am interested in the association between myeloproliferative disorder (EFO: EFO\_0004251) and JAK2 (ENSG00000096968). Their association scores can be obtained from websites as Figure1: Specifically, the datatype score, “Somatic mutations” is 0.9099111064831517 which is what I want to calculate by myself from all evidence scores. …

But, I only found “`open-targets-prod.platform.evidence`” stored on the cloud. If I use the following codes in Figure 4, there are only five pieces of evidence in total. I know these pieces of evidence are direct. Thus, my question is where I can find indirect evidence on the Cloud so that I can obtain the same number of evidence listed on the webpage.

Figure 4  
 ![evidence_code](https://europe1.discourse-cdn.com/flex017/uploads/opentargets/original/1X/f8f3d0a906390d5241ea7e28a4bee35e3124242e.png)

Charlie

---

<div class="post-metadata">

**Author:** ![ochoa](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/ochoa/32/189_2.png) [@ochoa](https://community.opentargets.org/u/ochoa)\
**Post date:** [6 September 2023 11:00 UTC](https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191/2 "2023-09-06T11:00:26Z")

</div>

Your assumptions are correct. The indirect evidence is not available in a dataset, but you can easily reproduce it using the `evidence` dataset + the `disease` dataset. You will first need to explode the diseases `ancestors` field to get all the paths from all the EFOs and the root. Once you have a 2 column dataset with the EFO id and the EFO id of the ontology propagated term, you can perform a `JOIN` operation.

It requires a lit bit of extra SQL skills, but you can always ask the community if you get stuck. Good luck!

---

<div class="post-metadata">

**Author:** ![charlie](https://avatars.discourse-cdn.com/v4/letter/c/d6d6ee/32.png) [@charlie](https://community.opentargets.org/u/charlie)\
**Post date:** [7 September 2023 08:51 UTC](https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191/3 "2023-09-07T08:51:48Z")

</div>

Thanks for the reply. After using the following query, I indeed get a list of EFO and GO items. But I have no idea as how to get all the paths from all the EFOs and the root in BigQuery. May you tell me a clear query command? Thanks.

SELECT  
ancestors  
FROM  
open-targets-prod.platform.diseases

Charlie

---

<div class="post-metadata">

**Author:** ![ochoa](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/ochoa/32/189_2.png) [@ochoa](https://community.opentargets.org/u/ochoa)\
**Post date:** [7 September 2023 10:54 UTC](https://community.opentargets.org/t/why-are-there-some-inconsistencies-in-evidence-count/1191/4 "2023-09-07T10:54:37Z")

</div>

You can find more help working with nested structures in BigQuery here:

> **[Work with arrays  |  BigQuery  |  Google Cloud](https://cloud.google.com/bigquery/docs/arrays)**

The next example will give you the ancestors:

```sql
SELECT
  id,
  ancestor
FROM `bigquery-public-data.open_targets_platform.diseases` AS d
CROSS JOIN UNNEST(d.ancestors.list) AS ancestor;

```

When doing the subsequent join beware not to drop the `direct` evidence. You can also explore using a LEFT JOIN instead of the CROSS JOIN depending what you want to achieve.