"flattened spreadsheet"

amnonkhen commented 2 years ago

UCSC Wranglers have asked for a csv that contains "all the data". No concrete use cases were mentioned.

I (@amnonkhen) understand this requirement as follows:

As a data consumer I would like to query, filter and aggregate project data according to any attribute

Tasks

[x] 1st draft - sequence file -> donor
[x] gather query examples from EBI Wranglers
[ ] gather requirements from Sanger data consumers. (names needed)
[ ] decide on column names: human (SEQUENCING PROTOCOL ID (Required)) or variables (sequencing_protocol.protocol_core.protocol_id)

Acceptance Criteria

[ ] ability to answer questions like "file count by donor"
[ ] ability to use "non programmer" tools for data interrogation, e.g. Excel Pivot Tables.

ipediez commented 2 years ago

As we have discussed, this is a great starting point and we can keep working from here. There are some things to take into account for the next drafts:

The current implementation works for a single experimental design, but there's a big variety of them. There are a lot of tabs from the spreadsheet that are optional (including the sequence file tab, in the case of managed access datasets where only the analysis files are available). We should think of a way to automatically model the graph depending on each case. For example, we could generate the graph based on the tabs that are available (by trying to load them) and then perform the joining based on a pre-defined order.
About the design again, we have to take also into account that elements of a tab can have different inputs, including input from the same tab. An example: We have "specimen from organism" taken from two donors, so we have specimen_1 and specimen_2, where their "Input Donor Organism ID" would be donor_1 and donor_2. Then, those specimens are enriched in two different subregions of the tissue (epithelium and lamina propria), so we have 4 new specimens: specimen_1_epithelium,...,...,specimen_2_laminaPropria. For these 4 specimens, there would not be any "Input Donor Organism ID" but a column "Input Specimen from organism ID" would be added, with the values being specimen_1 or specimen_2, for the corresponding epithelium/laminaPropia specimens.
We are currently including every single column in the spreadsheet. After joining, we could drop those columns that are completely empty

ipediez commented 2 years ago

@amnonkhen to ask for feedback about the draft, and to create a second draft with other spreadsheets as an example.