The dev-db test site is taking far to long to response, you can see there's a huge number of slow API requests being made by react:

taking something like 45 seconds to load the ScanReports page

test-db seems much quicker (there are also less scan reports)

A set of rules should be visible on a single page (as opposed to the pagination currently required).

Consideration could also be given to colour coding domains and allowing filtering by domain

Problem

• specimen concept:
  4001225 .. | 119297000 | Blood specimen | Specimen | Standard Blood specimen
  ---> not working

Suspected problem

• Throws an error when trying to grab the "source_concept_id"

 const destination_field = await cachedOmopFunction(fields,domain+"_source_concept_id")
    // if a destination field can't be found for concept domain, return error                                                                                  
    if(destination_field == undefined){
        throw 'Could not find a destination field for this concept'
    }

https://www.ohdsi.org/web/wiki/doku.php?id=documentation:cdm:specimen

• specimen table does not have a source_concept_id

Fix(es)

1. if statement to skip making a rule for the specimen_source_concept_id in the react code
2. why do we still bother saving the <domain>_source_concept_id?
   --> If we force standard concept ids (which we do), then any <domain>_source_concept_id == <domain>_concept_id
   --> ... the purpose of source_concept_id in OMOP is if you want to use a Non-Standard concept ID for the source value and relate it to a Standard concept ID
   --> Does RQuest ever query the source_concept_id? I bet not.
   --> we could remove the rule creation for <domain>_source_concept_id and if we wanted we could get the ETL tool to automatically fill it from the <domain>_concept_id

There is no use case where we need the dates in a scan report? We need to know what column has the dates within it. But if we dont need them, could we just ignore the date values?

I can see you can change the URL for the number of datasets displayed:
https://ccom.azurewebsites.net/datasets/?p=1&page_size=20

But could we have something on the GUI?

Timeout occurs for large scan reports, meaning manual intervention is necessary.

Examples in comments

The upload function times-out for large scan reports.

For example, for the UoD SHARE dataset - more detail will be supplied in comments

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

I have existing Concepts in my Scan Report Table:

And my ID / Date fields are allocated:

But clicking Refresh Rules does not create any rules:

Expected Behavior

I would expect rules to be created based on these existing Concepts.

Steps To Reproduce

1. A new Carrot mapper install
2. Upload a sample scan report
3. Allocate the ID / Date fields, and allocate the Concepts
4. Click the Refresh Rules

Environment

- OS: Macos Sonoma
- Other environment details: Developer environment

I'm part of a Project Team

Yes - DRS

Anything else?

This is very much related to #637

But it's important enough to create a new issue I think, as Mapping Rules is pretty much the heart of Carrot, and definitely does prevent this product being deployed elsewhere.

The code that creates the Mapping Rules first has to get the existing Scan Reports concepts (aside note that the second parameter table_id should be called scan_report_id as that's what is used to filter).

This is also filtering using the GenericRelation between ScanReportConcept and ScanReportValue/ScanReportField, so it's using the content_type as mentioned in #637. .filter(concepts__isnull=False)
This time - as the content_type that is being allocated to ScanReportConcept when they are created (15 or 17) is wrong, the filtering will correctly return an empty object. So no rules are created.

Again the fix is to enforce correct use of the Django content_type system.

This is basically a block to use/develop Carrot locally - we can do everything up to Rule generation but after that we're blind to as what might be working.

Are you willing to contribute to resolve this issue?

✅ Yes, me or my team intend to do the development.

• after entering this concept the name is so long that it squashes the input boxes
  

• I cant then enter another concept, I even tried zooming out but the ratio of the table remains the same
  

Is there an existing issue for this?

• I have searched the existing issues

Current Behavior

On a fresh install of Carrot, the /mapping_rules/ page on a Scan Report may not show the entire mapping in the Term Map column.

Expected Behavior

I would expect the sample data set to return something like this:

Steps To Reproduce

1. Create a new instance of the product in a clean database.
2. Upload a scan report and add rules as in the quickstart.
3. View the rules table.

Environment

- OS: MacOS 14.3.1
- Other environment details: Using the repository developer environment.

I'm part of a Project Team

DRS

Anything else?

The issue is that the content_type is hardcoded throughout the application, and relies on the django_content_type ID being 15 (ScanReportField) or 17 (ScanReportValue), this is reflected in the docs.

But there is no reason to expect those IDs to be attached to those types, and the current code does not create a table that does (they use ID 13 and 14). As it is the order of models.py and migrations that will probably influence what the IDs are, which have presumably changed.

Ironically most of the code works fine with these hardcoded values. For example concepts are created with a content_type of 15, and then have conditions on being 15 in both the backend and frontend, which is assumed to be ScanReportField, even if it actually isn't.
The exception is the /mapping_rules table, which correctly uses get_for_model here. The code is complex to follow, but is used to create the term mapping, and the API endpoint will just return term_mapping: null.

Fix is one of these possibilities I think:

• Quick: Update the documentation to ask a user to manually change the IDs in the database so 15 and 17 are always used for those content_types. This is brittle, and then needs changes in the auth_permission table (this was my fix to see how it should work).
• Quick: Change the get_mapping_rules_list to use the hardcoded values also, this keeps it very brittle.
• Quick: Reorder the models.py so the IDs revert back, but not sure this would work with migrations.
• Preferred: Remove the magic numbers, and use get_for_id to get the ID for the content_type correctly, presumably the ProcessQueue would need an endpoint to get this type ID also.

Are you willing to contribute to resolve this issue?

Yes

Currently CaRROT only supports a limited number of the clinical tables in its data model. Ideal it would support more of the auxiliary tables for OMOP conversion of data similar to usagi.

One table of specific interest is the FACT_RELATIONSHIP table.

The FACT_RELATIONSHIP table contains records about the relationships between facts stored as records in any table of the CDM. Relationships can be defined between facts from the same domain, or different domains.

Examples of Fact Relationships include:

• Person relationships (parent-child)
• Care site relationships (hierarchical organizational structure of facilities within a health system)
• Indication relationship (between drug exposures and associated conditions)
• Usage relationships (of devices during the course of an associated procedure)
• Facts derived from one another (measurements derived from an associated specimen).

All relationships are directional, and each relationship is represented twice symmetrically within the FACT_RELATIONSHIP table. For example, two persons if person_id = 1 is the mother of person_id = 2 two records are in the FACT_RELATIONSHIP table (all strings in fact concept_id records in the Concept table:

Ie. Person, 1, Person, 2, parent of
Person, 2, Person, 1, child of

There are two stages to this.

1. Support for FACT_RELATIONSHIP conversion in CaRROT CDM
2. Support for the generation of FACT_RELATIONSHIP rules in CaRROT ccom.

I am currently mapping a dataset with associated physical devices, it could be beneficial to be able to represent such concepts.

Additionally, the UK OHDSI OMOP group is currently talking about 'device' data.

https://ohdsi.github.io/CommonDataModel/cdm54.html#DEVICE_EXPOSURE

I think there are some small steps to be made to enable this repository to be easier to work with, including:

• - Add a /workers directory to group the Azure functions together, including their dependencies.
• - Add .vscode workspaces to enable Python environments to be separate (functions and Django). Including launch.json files for the functions/webapp.
• - Use Poetry for dependencies, with versioning (currently very few of the dependencies are pinned to a version), and pyproject.toml. This repo is too big to use a simple requirements.txt (we'd get Poetry to output a requirements.txt in CI)
• - Use pre-commit for linting, formatting, and typing - here's a simple config we used on Ai4Green: https://github.com/AI4Green/AI4Green/blob/main/.pre-commit-config.yaml can also include #543
• - Add a tests folder, and begin to use Pytest to support this.

I'd be happy to make a PR to cover all of this, but open for discussion on any of the points, and any additional ideas.

This is linked to issue #628

To improve the learnings that can be gained from mappings, it would be good if there was the option to create a short note when you generate a mapping to document the decision process that was made for that mapping rule.

PROBLEM STATEMENT:

There can be problems with vocabularies that share a specific code, an example being:

ICD10: H05.1 - Chronic inflammatory disorders of orbit (https://athena.ohdsi.org/search-terms/terms/45605601)

OPCS4: H05.1 - Total colectomy and anastomosis of ileum to rectum (https://athena.ohdsi.org/search-terms/terms/44510650)

Alex Waldren-Glenn has done some testing within CARROT on using the H05.1 code for NUH purposes. the approach was:

• Part 1 (Coding overlap test part 1) - Manually map an example overlapping code H05.1 to both OPCS4 and ICD10.

• Part 2 (Coding overlap testing part 2) - Provide a data dictionary with the code as OPCS4 / ICD10 and see if Carrot maps the values correctly.

The outcome of the test was thus - verbatim text from Alex's email:

"So Carrot has auto mapped to two concepts, the R is from my previous mapping reuse and the V is from vocab. It looks like Carrot already has some background info for OPCS4 (I tested this with v8.2 testing with coding type .)

NUH coding for OPCS4 doesn’t include the “.” that is in Athena. This means that Carrot can’t pick up those codes in my v8.2 testing with coding type . or other testing via the V vocab method…

My main question is on the vocab mapping. For H05.1 carrot vocab mapping gives the value 44510650, which is the omop concept ID for the OPCS4 code.

The mapping that I had previously done (via providing the standard concept id in the DD) gives 4262950. This is the value of the standard concept in SNOMED that I would expect to use for OMOP. (OPCS4 value -> omop concept id -> Non-standard to Standard map (OMOP) -> concept id for the snomed standard concept)

From my understanding we need to use standard concept ids for OMOP, so OPCS4 and ICD10 need to be mapped to the snomed standard concept ids. How does this work in carrot? Would the V vocab lookup values be later mapped to standard concept ids?"

N.B.: I (Esmond) can supply the specifically cited email above as it contains screenshots and thus provides a full & better context for the problem.

Ability to map concept_id, value_as_concept_id, value_as_number to a single record

Note that a check should be included to ensure field availability in target OMOP tables.

In services_nlp.pywhen evaluating pass_from_source=True we check for a literal blank string (i.e. ""). For example on Line 117:

field_text = field.description_column if field.description_column is not "" else field.name

Whereas if pass_from_source=False (i.e. running at the value level) then we evaluate on None. For example on Line 159:

field_text = item.scan_report_field.description_column if item.scan_report_field.description_column is not None else item.scan_report_field.name

This is due to how Vas' Azure Function reads in data from the scan report. For fields, there is a field_description column; this could be blank or contain a descriptive string of text. At the moment, Vas' code casts field_description to string (str(field_description)) which causes literal blank strings when processing each row (if there is no text in the field_description cell in the scan report). There are no fields in the scan report for value descriptions and therefore explictly set as None in her code.

We may want to decide at a later date whether we want to evaluate on "" or None (rather than using both as in the current implementation)

@spco, Via Scott

the issue

Hi Scott

I ran into an issue when running coconnect run map:

$ source coconnect-virtualenv/bin/activate

$ coconnect info version

0.5.0

$ coconnect display dataframe hdruk-isaric_demog_hashed.csv

...OK...

$ coconnect display dataframe hdruk-isaric_events_hashed.csv

...OK...

$ coconnect run map --rules University\ of\ Edinburgh_ISARIC4C_20220503_structural_mapping.json isaric_demog_hashed.csv isaric_events_hashed.csv

...

2022-05-31 12:15:45 - CommonDataModel - INFO - working on person

...

2022-05-31 12:15:52 - CommonDataModel - INFO - working on procedure_occurrence

2022-05-31 12:15:52 - CommonDataModel - INFO - starting on Oxygen therapy 4374

2022-05-31 12:15:52 - ProcedureOccurrence - INFO - Called apply_rules

2022-05-31 12:15:52 - LocalDataCollection - INFO - Retrieving initial dataframe for 'isaric_events_hashed.csv' for the first time

2022-05-31 12:15:52 - ProcedureOccurrence - INFO - Mapped person_id

2022-05-31 12:15:53 - ProcedureOccurrence - INFO - Mapped procedure_concept_id

2022-05-31 12:15:53 - ProcedureOccurrence - INFO - Mapped procedure_datetime

2022-05-31 12:15:53 - ProcedureOccurrence - INFO - Mapped procedure_source_concept_id

2022-05-31 12:15:53 - ProcedureOccurrence - INFO - Mapped procedure_source_value

2022-05-31 12:15:53 - ProcedureOccurrence - INFO - Automatically formatting data columns.

2022-05-31 12:15:55 - ProcedureOccurrence - INFO - created df (0x7f72407e7c88)[Oxygen_therapy_4374]

Traceback (most recent call last):

  File "/home/hdruk_data/coconnect-virtualenv/lib64/python3.6/site-packages/pandas/core/indexes/base.py", line 2898, in get_loc

    return self._engine.get_loc(casted_key)

  File "pandas/_libs/index.pyx", line 70, in pandas._libs.index.IndexEngine.get_loc

  File "pandas/_libs/index.pyx", line 101, in pandas._libs.index.IndexEngine.get_loc

  File "pandas/_libs/hashtable_class_helper.pxi", line 1675, in pandas._libs.hashtable.PyObjectHashTable.get_item

  File "pandas/_libs/hashtable_class_helper.pxi", line 1683, in pandas._libs.hashtable.PyObjectHashTable.get_item

KeyError: 'procedure_start_date'

 

The above exception was the direct cause of the following exception:

 

Traceback (most recent call last):

  File "/home/hdruk_data/coconnect-virtualenv/bin/coconnect", line 8, in <module>

    sys.exit(coconnect())

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 1128, in __call__

    return self.main(*args, **kwargs)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 1053, in main

    rv = self.invoke(ctx)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 1659, in invoke

    return _process_result(sub_ctx.command.invoke(sub_ctx))

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 1659, in invoke

    return _process_result(sub_ctx.command.invoke(sub_ctx))

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 1395, in invoke

    return ctx.invoke(self.callback, **ctx.params)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/core.py", line 754, in invoke

    return __callback(*args, **kwargs)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/click/decorators.py", line 26, in new_func

    return f(get_current_context(), *args, **kwargs)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/coconnect/cli/subcommands/run.py", line 569, in map

    cdm.process(conserve_memory=True)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/coconnect/cdm/model.py", line 654, in process

    for j,obj in enumerate(df_generator):

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/coconnect/cdm/model.py", line 820, in process_table

    df = obj.get_df(start_index=start_index)

  File "/home/hdruk_data/coconnect-virtualenv/lib/python3.6/site-packages/coconnect/cdm/objects/versions/v5_3_1/procedure_occurrence.py", line 44, in get_df

    if df['procedure_start_date'].isnull().all():

  File "/home/hdruk_data/coconnect-virtualenv/lib64/python3.6/site-packages/pandas/core/frame.py", line 2906, in __getitem__

    indexer = self.columns.get_loc(key)

  File "/home/hdruk_data/coconnect-virtualenv/lib64/python3.6/site-packages/pandas/core/indexes/base.py", line 2900, in get_loc

    raise KeyError(key) from err

KeyError: 'procedure_start_date'

$ ls output_data/

person_ids.tsv  person.tsv

Any ideas?

the solution

This isn't an issue with the ETL Tool but the generation of the rules json for the procedure table by the web-app that is getting the name wrong. I can see a quick fix, but the procedure table hasnt been smoke tested properly (which I am working on now)

Could it be possible to group datasets by project, it can be hard to find all the connected datasets. It would be great to have a "Alleviate" view for example.

When you land on datasets, I think I am just viewing active datasets? But the active dataset button is still active.

It can be confusing then on what I am actually looking at.

Is there an existing issue for this?

• I have searched the existing issues

Is your proposal related to a problem or functionality gap?

The SDE programme has defined a minimum OMOP data model for Cohort Discovery. Measurement data is included as it is relevant to research in the domain. It would be great if the following field can be included in the capability of CaRROT-Mapper.

Describe your proposal

Please could you add the value_as_concept_id field from the MEASUREMENT table?

Describe alternatives you've considered

No response

I'm part of a Project Team

No response

Anything else?

No response

Are you willing to contribute to developing this feature?

🎨 Yes, I'm keen to be involved in design discussions.

Currently a user cannot add New Data Partner whilst uploading the scan report. I recall that this decision was made to ensure that the Data Partner name is unique and we do not get variations of it by allowing users to add it.

Suggestion:

• Add the feature 'Add new' besides the Data Partner Drop down
• Only allow addition via a pre-populated drop down list of potential Data Partners to ensure that there is no variation in the name
• If the data partner already exists, show an error message, else add

Report uploads ok with no errors

followed by empty tables:

scan report looks ok:

Currently the mapper only supports automated field value mapping but not field mappings

Suggestion :

• Make use of the dictionary to generate mappings
  Current format for dictionary is
  csv_file_name | field_name | code | value
  abc.csv | condition | icd10

Presently we are required to provide csv_file_name , field_name and code for field value mapping. I'd recommend we use this to map field instead of field values.

In order to map field values we could make use of the 'value' column and use a key word such as 'map_all_values' to suggest it's a field value mapping.
csv_file_name | field_name | code | value
abc.csv | condition | icd10 | map_all_values

If a SRField or SRValue is mapped to multiple Concept ID's, the reuse process when uploading a new SR only adds one of those multiple Concept ID's to the given Field/Value.

e.g

These are the Concept ID's added to the same SRValue

Once is reuploaded, it only CaRROT only resuses one

hi all,

Not sure if it's been propagated to who ever is managing this now but as well the specimen table needing fixing the death table is now needed (implemented in CaRROT-CDM here HDRUK/CaRROT-CDM#153 )

Is there an existing issue for this?

• I have searched the existing issues

Is your proposal related to a problem or functionality gap?

The SDE programme has defined a minimum OMOP data model for Cohort Discovery. Observation-related data is included as it is relevant to research in the domain. It would be great if the following fields were included in the capability of CaRROT-Mapper.

Describe your proposal

Please could you add the following fields from the OBSERVATION table?

value_as_number
value_as_concept_id

Describe alternatives you've considered

No response

I'm part of a Project Team

No response

Anything else?

No response

Are you willing to contribute to developing this feature?

🎨 Yes, I'm keen to be involved in design discussions.

Takes ~1 minute!

Occasionally there may be a need to edit scan report content before use, it would be faster to do this post upload.

I get

/api/mapping/services_nlp.py:116: SyntaxWarning: "is not" with a literal. Did you mean "!="?
  field_text = field.description_column if field.description_column is not "" else field.name

@simon-tarr - please fix!

It would be good to have the ability to delete scan reports which are obsolete/ contain information which shouldn't be visible or were uploaded in error.

The vocabulary tables used by CaRROT-Mapper should be updated regularly.

Existing mappings should be left as they are, but should we consider highlighting anomalies? Obsolete = Greyed out, non-standard = Red, for example.

Is there an existing issue for this?

• I have searched the existing issues

Is your proposal related to a problem or functionality gap?

Python 3.8 will be 5 years old this year, and will no longer be supported.

It's also frustrating to work with 3.8 - as MacOS with M chips don't support it.

Describe your proposal

Update the workers and Django app to use 3.12

Describe alternatives you've considered

No response

I'm part of a Project Team

Yes - DRS

Anything else?

There shouldn't be many problems in going from 3.8 - 3.12.
We should update other dependencies also - like Django is now on 5.0, at least a move to the LTS of 4.2

Are you willing to contribute to developing this feature?

✅ Yes, me or my team intend to do the development.

Progress

• Functions Updated 3.11 #657
• Webapp Updated 3.11

CaRROT-Mapper currently requires manual edits in the Scan report "Field Overview" sheet. (Fraction empty | N unique values | Fraction unique) need changes before upload. Details in comments

WhiteRabbit/Excel truncated the sheet names to be 31 characters. In Field Overview, if you have a name with >31 we cannot look up the sheet based on this name

• Quick fix to look up the names based on name[:31]
• Implement a check for duplicate sheet names because of truncation [Not needed, WhiteRabbit names 0_blahh..]
  • could occur if you have blahhhhhhhhhhhhhhhh_0 and blahhhhhhhhhhhhhhhhhhhh_1 both would be blahhhhhhhhhhhhhhhhhhhh in the sheet name
  • could be a bug in WhiteRabbit

Is there any reason to store the raw scan report after upload? Could we delete them to stop holding on to data unnecessarily?

Tasks

• #729
• #730

When assigning Person ID and Event Dates to individual dataset tables, then can be altered, but not deleted, so that they are blank.

Currently I am mapping a dataset with no date events (just a test mapping run through), but have assigned a data attribute to an Event Date in error. I can change the assigned data attribute, but cannot return the Event Date to its original blank state.

https://ccom.azurewebsites.net/scanreports/505/

N.B.: the Dataset is restricted and Viewers / Editors / Admins are set only to Phil & Esmond

Add the ability to select existing scan reports for which mapping re-use is required.

Will need to be able to avoid mapping conflicts in the case of multiple available mappings

"from table 1684" is not useful

Is there an existing issue for this?

• I have searched the existing issues

Is your proposal related to a problem or functionality gap?

The SDE programme has defined a minimum OMOP data model for Cohort Discovery. Death-related data is included as it is relevant to research in the domain. It would be great if the following fields were included in the capability of CaRROT-Mapper.

Describe your proposal

Please could you add the following fields from the DEATH table?
person_id (key)
death_date (Records the date of death for mortality-related cohorts and survival analysis.)
death_type_concept_id (Identifies the type of death (e.g., natural, accidental) for cohort definition and research on specific death causes.)
cause_concept_id (Identifies the underlying cause of death for mortality analysis and research on disease burden and risk factors.

Describe alternatives you've considered

No response

I'm part of a Project Team

No response

Anything else?

No response

Are you willing to contribute to developing this feature?

🎨 Yes, I'm keen to be involved in design discussions.

It would be good to be able to mark versions of a mapping as complete and have them saved. We can then fork existing mapping for maintenance, so we can safely apply updates to OMOP CDM or updated vocabs to existing mappings with less risk.

Being able to do compare different mapping versions would be nice but would likely be classed as a later feature.

As mappings are non-disclosive, we need a way for uses to see the mappings from different data sets that have previously been applied. This will allow them to see a history of the decisions that have been made and chose whether to also apply the same logic to their mappings. It could allow them to also 'import' these rules to their own project for application to their data which is helpful when similar questionnaire's etc are used.

Ability to map concept_id, value_as_concept_id, value_as_number to a single record

Is there an existing issue for this?

• I have searched the existing issues

Is your proposal related to a problem or functionality gap?

Currently the release workflow for dev/test/production is 3 separate Github actions, working with Gitflow. So merging a new fix requires 3 separate PRs, artifacts, and deployments: https://github.com/Health-Informatics-UoN/CaRROT-Mapper/pulls?q=is%3Apr+is%3Aclosed

Describe your proposal

I propose combining this into one workflow, utilising Github environments to deploy a single artifact from the main branch. An example here: https://github.com/Health-Informatics-UoN/rcc-monitor/actions/runs/7931015704

Describe alternatives you've considered

No response

I'm part of a Project Team

Yes - DRS

Anything else?

It might not be done here - but I think it's worth also exploring when database migrations happen as part of CD, not sure if this currently a manual process?

Are you willing to contribute to developing this feature?

✅ Yes, me or my team intend to do the development.

Update vocabulary tables to include all SNOMED codes.

Issue:

Cannot map metadata items to 6 digit concept IDs, e.g.:

607590 | 1153637007 | Body height

CARROT refuses to accept the mapping for a 'Valid & Standard' vocabulary concept ID.

New feature in CaRROT Mapper

It can be very hard to see what page is active on the pagination, especially on certain screens etc. Can it be made more obvious than a different shade of grey.

New feature in CaRROT Mapper

Can we have a carrot mapper that is installed within an organisation infra, but can still benefit from mapping rules from the central system

Rules should be refreshed automatically on Mapping Rules page load.

Alternatively a warning should be displayed to alert the user of the need to refresh