# Problem exporting cohort to csv/tsv/json, missing headers and invalid format

**URL:** <https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503>\
**Category:** Support\
**Tags:** bug\
**Created:** [September 22, 2023, 4:09pm UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503 "2023-09-22T16:09:13Z")\
**Posts on this page:** 5\
**Page:** 1

<div class="post-metadata">

**Author:** ![vanossj](https://sea1.discourse-cdn.com/flex015/user_avatar/discourse.canceridc.dev/vanossj/32/364_2.png) [@vanossj](https://discourse.canceridc.dev/u/vanossj)\
**Post date:** [September 22, 2023, 4:09pm UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503/1 "2023-09-22T16:09:13Z")

</div>

When I export a cohort to CSV or TSV, there are no manifest headers, or column headers in the downloaded file. Which doesn’t match what the [documentation page](https://learn.canceridc.dev/portal/cohort-manifests#csv-tsv-json-cohort-manifest) shows the file to look like.

```auto
UPENN-GBM-00001,upenn_gbm,10.7937/TCIA.709X_DN49,1.3.6.1.4.1.14519.5.2.1.193084931700557478622366870650889741468,1.3.6.1.4.1.14519.5.2.1.40067253395349591373441931744246881031,cc8b6843-b357-4e5a-8f03-dcd3c3842060,5fd52284-6973-44da-b882-473b97e3c82d,16.0
UPENN-GBM-00001,upenn_gbm,10.7937/TCIA.709X_DN49,1.3.6.1.4.1.14519.5.2.1.27626308777696377724750002938243956383,1.3.6.1.4.1.14519.5.2.1.202685500194820733902722321770171989719,34676c1b-0ead-4ab6-93ca-7da191cef968,01918fce-3589-47a7-883e-f82cc9f730bf,16.0
UPENN-GBM-00001,upenn_gbm,10.7937/TCIA.709X_DN49,1.3.6.1.4.1.14519.5.2.1.303922662365557240161187739636975756435,1.3.6.1.4.1.14519.5.2.1.337491152254065288399657726162931889194,7a2afc1c-9686-41b9-ad79-97f988c563f7,951a4b1e-1ed3-4c59-b7d0-0e877b370b03,16.0

```

If I download it as json, the file is not valid json, its not an array of objects, just one object per line.

```auto
{"collection_id": "upenn_gbm", "SeriesInstanceUID": "1.3.6.1.4.1.14519.5.2.1.40067253395349591373441931744246881031", "source_DOI": "10.7937/TCIA.709X_DN49", "idc_version": "", "PatientID": "UPENN-GBM-00001", "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.193084931700557478622366870650889741468", "crdc_study_uuid": "cc8b6843-b357-4e5a-8f03-dcd3c3842060", "crdc_series_uuid": "5fd52284-6973-44da-b882-473b97e3c82d"}
{"collection_id": "upenn_gbm", "SeriesInstanceUID": "1.3.6.1.4.1.14519.5.2.1.202685500194820733902722321770171989719", "source_DOI": "10.7937/TCIA.709X_DN49", "idc_version": "", "PatientID": "UPENN-GBM-00001", "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.27626308777696377724750002938243956383", "crdc_study_uuid": "34676c1b-0ead-4ab6-93ca-7da191cef968", "crdc_series_uuid": "01918fce-3589-47a7-883e-f82cc9f730bf"}
{"collection_id": "upenn_gbm", "SeriesInstanceUID": "1.3.6.1.4.1.14519.5.2.1.337491152254065288399657726162931889194", "source_DOI": "10.7937/TCIA.709X_DN49", "idc_version": "", "PatientID": "UPENN-GBM-00001", "StudyInstanceUID": "1.3.6.1.4.1.14519.5.2.1.303922662365557240161187739636975756435", "crdc_study_uuid": "7a2afc1c-9686-41b9-ad79-97f988c563f7", "crdc_series_uuid": "951a4b1e-1ed3-4c59-b7d0-0e877b370b03"}

```

I tried two cohorts with the same results. The cohort for the examples above is [MR modality from UPENN-GBM collection](https://gist.github.com/vanossj/f65ff7c55c9ba5eb86c1d1a767f852b4).

Also I just noticed a spelling error of `Manfiest Headers` on the Export Manifest dialog  
 ![image](https://us1.discourse-cdn.com/flex015/uploads/canceridc/original/1X/4b964ae3bf22af40a1130e1f5e12e2c112505093.png)

---

<div class="post-metadata">

**Author:** ![fedorov](https://sea1.discourse-cdn.com/flex015/user_avatar/discourse.canceridc.dev/fedorov/32/3_2.png) [@fedorov](https://discourse.canceridc.dev/u/fedorov)\
**Post date:** [September 22, 2023, 4:55pm UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503/2 "2023-09-22T16:55:40Z")

</div>

@vanossj thank you for reaching out!

TL;DR: what are you trying to accomplish with exporting the cohort?

IDC Portal provides several means for exporting cohort manifest, and those different means aim to support different purposes. We discuss those in this documentation page: [https://learn.canceridc.dev/portal/cohort-manifests](https://learn.canceridc.dev/portal/cohort-manifests) (which of course can be improved).

If you want to download the cohort, you should use the s5cmd manifest, which is the default option.

CSV/TSV manifest has rather limited utility, and I asked the opening question in order to make sure it is actually the right manifest kind to support your needs.

> [@vanossj](#):
>
> When I export a cohort to CSV or TSV, there are no manifest headers, or column headers in the downloaded file. Which doesn’t match what the [documentation page](https://learn.canceridc.dev/portal/cohort-manifests#csv-tsv-json-cohort-manifest) shows the file to look like.

Indeed, there is a known regression in the portal, as documented in this issue: [Manifest headers are not showing up for CSV/TSV/JSON Downloads in Test and Prod tiers · Issue #1255 · ImagingDataCommons/IDC-WebApp · GitHub](https://github.com/ImagingDataCommons/IDC-WebApp/issues/1255). We have not yet resolved this issue.

> [@vanossj](#):
>
> If I download it as json, the file is not valid json, its not an array of objects, just one object per line.

What you see is called “newline JSON” (see [https://jsonlines.org/](https://jsonlines.org/) as one resource explaining what this means), which is a “flavor” of JSON that is commonly used for representing data streams and row-structured data. I agree that our documentation is not precise, and we should clarify this in the documentation.

> [@vanossj](#):
>
> Also I just noticed a spelling error of `Manfiest Headers` on the Export Manifest dialog

Thank you for pointing this out! I’ve just [submitted a fix](https://github.com/ImagingDataCommons/IDC-WebApp/pull/1291) that should propagate to the portal shortly, I hope.

I hope this response helps better understand IDC manifests. But please let me know what is your goal so we can make sure we address your specific needs!

---

<div class="post-metadata">

**Author:** ![vanossj](https://sea1.discourse-cdn.com/flex015/user_avatar/discourse.canceridc.dev/vanossj/32/364_2.png) [@vanossj](https://discourse.canceridc.dev/u/vanossj)\
**Post date:** [September 22, 2023, 5:34pm UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503/3 "2023-09-22T17:34:03Z")

</div>

I do use s5cmd to download the files, but then I like to sort them into collection\_id/PatientID/StudyInstanceUID/SeriesInstanceUID.

I use the csv file to get the collection\_id information, although the csv doesn’t list the filenames, so i still need to read the SeriesInstanceUID from each downloaded file to sort them

Thanks for the link on newline JSON, I hadn’t run into that format before.

---

<div class="post-metadata">

**Author:** ![fedorov](https://sea1.discourse-cdn.com/flex015/user_avatar/discourse.canceridc.dev/fedorov/32/3_2.png) [@fedorov](https://discourse.canceridc.dev/u/fedorov)\
**Post date:** [September 23, 2023, 2:48am UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503/4 "2023-09-23T02:48:37Z")

</div>

> [@vanossj](#):
>
> I do use s5cmd to download the files, but then I like to sort them into collection\_id/PatientID/StudyInstanceUID/SeriesInstanceUID.

Ah, thanks for letting me know about this!

You can sort the files into any hierarchy of DICOM tag values using [dicomsort](https://github.com/pieper/dicomsort). But it is a valid point that `collection_id` is not in DICOM.

If you are not intimidated by SQL, it is best to build an `s5cmd` manifest that downloads the files into the proper folder structure on the fly. SQL interface gives you complete flexibility, both in terms of how to filter the selection and how to organize the files upon download.

The code below will generate a manifest that, after being passed to `s5cmd`, will download the files into the `collection_id/StudyInstanceUID/SeriesInstanceUID` hierarchy. But you can modify the query easily to sort into any hierarchy (i.e., by `SeriesDescription`, or `SeriesNumber`).

In this case, for the sake of an example, I select the cohort based on the value of `SeriesInstanceUID`, but you can as easily select based on any criteria, such as value of `collection_id`, or `Modality`.

I also updated the [cookbook notebook](https://github.com/ImagingDataCommons/IDC-Tutorials/blob/master/notebooks/cookbook.ipynb) to include this recipe.

```python
from google.cloud import bigquery
bq_client = bigquery.Client(my_ProjectID)

selection_query ="""
SELECT
  # Organize the files in-place on the fly
  ANY_VALUE(CONCAT("cp s3",
      REGEXP_SUBSTR(aws_url, "(://.*)/"),
      "/* ",collection_id,"/",PatientID,"/",
      StudyInstanceUID,"/",SeriesInstanceUID)) AS s5cmd_command
FROM
  `bigquery-public-data.idc_current.dicom_all`
WHERE
  # Use any filtering criteria here
  StudyInstanceUID = "1.3.6.1.4.1.14519.5.2.1.6279.6001.224985459390356936417021464571"
GROUP BY
  SeriesInstanceUID
"""

selection_result = bq_client.query(selection_query)
selection_df = selection_result.result().to_dataframe()

selection_df.to_csv("/content/s5cmd_aws_manifest.txt", header=False, index=False)

```

Implementing features like this in the IDC Portal is a heavyweight operation, but, fortunately, you can do this easily and with full flexibility without having to open the portal.

Would this address your need?

---

<div class="post-metadata">

**Author:** ![vanossj](https://sea1.discourse-cdn.com/flex015/user_avatar/discourse.canceridc.dev/vanossj/32/364_2.png) [@vanossj](https://discourse.canceridc.dev/u/vanossj)\
**Post date:** [September 26, 2023, 6:02pm UTC](https://discourse.canceridc.dev/t/problem-exporting-cohort-to-csv-tsv-json-missing-headers-and-invalid-format/503/5 "2023-09-26T18:02:48Z")

</div>

That works! thanks for the example.