# Counts of drugs and indicates for human genes

**URL:** <https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601>\
**Category:** Community Feedback\
**Tags:** batch-search, genetics-portal\
**Created:** [15 May 2022 19:54 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601 "2022-05-15T19:54:01Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![Shicheng\_Guo](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/shicheng_guo/32/217_2.png) [@Shicheng\_Guo](https://community.opentargets.org/u/Shicheng_Guo)\
**Post date:** [15 May 2022 19:54 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601/1 "2022-05-15T19:54:01Z")

</div>

Dear Community,

I am trying to collect the counts of drugs and indicates for each human gene, as the below. Anyone can share a R-API or Python-API script to achieve it?

 ![WeChat Photo Editor_20220515155145](https://europe1.discourse-cdn.com/flex017/uploads/opentargets/original/1X/f5eb75c171f859ae066cb953b7c69d94a9808e29.jpeg)

Thanks.

Shicheng

---

<div class="post-metadata">

**Author:** ![JarrodBaker](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/jarrodbaker/32/44_2.png) [@JarrodBaker](https://community.opentargets.org/u/JarrodBaker)\
**Post date:** [16 May 2022 15:45 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601/2 "2022-05-16T15:45:12Z")

</div>

Hi Shicheng

The API does not support mass data queries. You’re best using [Google Cloud’s Big Query (BQ)](https://console.cloud.google.com/marketplace/product/bigquery-public-data/open-targets-platform?project=milltons-28719) to run those kind of queries, or downloading the raw data and using a tool such as Spark. If you want to use BQ, the following query you give you what you need:

```auto
WITH drugs as (
  SELECT targetId, array_length(array_agg(DISTINCT drugId)) as d
  FROM `bigquery-public-data.open_targets_platform.knownDrugsAggregated`
  GROUP BY targetId
), indications AS (
  SELECT targetId, array_length(array_agg(DISTINCT diseaseId)) as i
  FROM `bigquery-public-data.open_targets_platform.knownDrugsAggregated`
  GROUP BY targetId
) 
SELECT drugs.targetId, d, i FROM drugs 
JOIN indications 
ON drugs.targetId = indications.targetId

```

The raw data is available from [EBI’s FTP server](http://ftp.ebi.ac.uk/pub/databases/opentargets/platform/latest/output/etl/) in Parquet and JSON formats.

---

<div class="post-metadata">

**Author:** ![Kirill\_Tsukanov](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/kirill_tsukanov/32/31_2.png) [@Kirill\_Tsukanov](https://community.opentargets.org/u/Kirill_Tsukanov)\
**Post date:** [17 May 2022 08:01 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601/3 "2022-05-17T08:01:57Z")

</div>



---

<div class="post-metadata">

**Author:** ![Kirill\_Tsukanov](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/kirill_tsukanov/32/31_2.png) [@Kirill\_Tsukanov](https://community.opentargets.org/u/Kirill_Tsukanov)\
**Post date:** [17 May 2022 08:02 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601/4 "2022-05-17T08:02:26Z")

</div>



---

<div class="post-metadata">

**Author:** ![Kirill\_Tsukanov](https://dub1.discourse-cdn.com/flex017/user_avatar/community.opentargets.org/kirill_tsukanov/32/31_2.png) [@Kirill\_Tsukanov](https://community.opentargets.org/u/Kirill_Tsukanov)\
**Post date:** [17 May 2022 08:04 UTC](https://community.opentargets.org/t/counts-of-drugs-and-indicates-for-human-genes/601/5 "2022-05-17T08:04:48Z")

</div>

Thank you @JarrodBaker. I’m going to mark this as resolved for now. @Shicheng_Guo please let us know if you have any further questions