# Proper translations of medical, service sector and provider codes Here we provide details on how to translate the medical, service sector and provider codes present in [service sector detailed longitudinal BigQuery table](/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database.md). There are three simple R functions in `FinnGenUtilsR` package within Sandbox that translates the codes using SQL. All these three functions uses a bigquery table called `fg_codes_info_v5` which contains code translation information. Please use the latest version of this table as it contains extra codes from different registries. Users can run these functions on the whole [service sector detailed longitudinal BigQuery table](/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database.md) but it would take quite a long time to translate all the codes. We recommend to use this after filtering for relevant codes. ### **Medical codes** [Detailed longitudinal data ](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/detailed-longitudinal-data.md)contains medical codes from different [finnish health registries](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/registers-in-the-detailed-longitudinal-data.md) representing diagnosis, procedures, measurements and observations. These medical codes comes from [international and finnish code sets](/finngen-data-specifics/finnish-health-registers-and-medical-coding/international-and-finnish-health-code-sets.md) covering different vocabularies. Users can use the function `fg_bq_append_code_info_to_longitudinal_data` adds translations to the medical codes present in [service sector detailed longitudinal BigQuery table](/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database.md). ``` fg_bq_append_code_info_to_longitudinal_data( bq_project_id, bq_table, fg_codes_info_table ) ``` The function takes into account `SOURCE`, `CODE1`, `CODE2` and `CODE3` columns and translates the code. By default the function takes combination of `CODE1` and `CODE2` for ICD10 codes present in [Hilmo and Avohilmo registries](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/registers-in-the-detailed-longitudinal-data.md). User can specify to consider only `CODE1` column for ICD10 codes. ``` fg_bq_append_code_info_to_longitudinal_data( bq_project_id, bq_table, fg_codes_info_table, ICD10fi_map_to = "CODE1" ) ``` Users can also specify the length of the medical code to be translated. By default, ICD10 diagnosis code is of length 5 but user can get parent code translation by specifying the ICD10 diagnosis code length. ``` fg_bq_append_code_info_to_longitudinal_data( bq_project_id, bq_table, fg_codes_info_table, ICD10fi_precision = 4 ) ``` Default settings and other options the function provides can be viewed using function `fg_bq_append_code_info_to_longitudinal_data_sql`. Users can checkout tutorials on how to connect to the BigQuery tables and translate the medical codes using `FinnGenUtilsR` package in the [tutorial page](https://finngen.github.io/FinnGenUtilsR/articles/tutorial_add_info.html). ### **Service Sector codes** [Service sector data](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data.md) contains codes that provide information regarding the type of visit. Users can get proper translation of these service sector codes with help of function `fg_bq_append_visit_type_info_to_service_sector_data`. Service sector data is only available to two [registries HILMO and AVOHILMO](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data.md) and columns `CODE5`, `CODE6`, `CODE8` and `CODE9` contains the service sector codes. ``` fg_bq_append_visit_type_info_to_service_sector_data( bq_project_id, bq_table, fg_codes_info_table ) ``` For HILMO registry, `CODE5` is used to translate service sector code but starting from 2019 service sector codes in `CODE8` and `CODE9` are translated. But there are `CODE8` and `CODE9` service sector codes available for some medical events before 2019 within HILMO registry. In such scenarios, users can prioritize which service sector codes needs to be translated. ``` fg_bq_append_visit_type_info_to_service_sector_data( bq_project_id, bq_table, fg_codes_info_table, prioritise_SRC_Contact_Urgency_over_SRC_Service_Sector # By default TRUE ) ``` Default settings and other options the function provides can be viewed using function `fg_bq_append_visit_type_info_to_service_sector_data_sql`. Users can checkout tutorials on how to connect to the BigQuery tables and translate the service sector codes using `FinnGenUtilsR` package in the [tutorial page](https://finngen.github.io/FinnGenUtilsR/articles/tutorial_add_info.html). ### **Provider codes** [Service sector data](/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data.md) also contains codes for provider specialty and to get proper translation of these codes you can use the function `fg_bq_append_provider_info_to_service_sector_data`. Only HILMO and AVOHILMO registries gives information on the specialty of the provider. For HILMO registry, the provider specialty code is from column `CODE6` and for AVOHILMO registry it is `CODE7`. ``` fg_bq_append_provider_info_to_service_sector_data( bq_project_id, bq_table, fg_codes_info_table ) ``` Default settings and other options the function provides can be viewed using function `fg_bq_append_provider_info_to_service_sector_data_sql`. Users can checkout tutorials on how to connect to the BigQuery tables and translate the provider codes using `FinnGenUtilsR` package in the [tutorial page](https://finngen.github.io/FinnGenUtilsR/articles/tutorial_add_info.html). --- # Agent Instructions: Querying This Documentation If you need additional information that is not directly available in this page, you can query the documentation dynamically by asking a question. Perform an HTTP GET request on the current page URL with the `ask` query parameter: ``` GET https://docs.finngen.fi/working-in-the-sandbox/which-tools-are-available/miscellaneous-helper-scripts-tools/r-functions-to-translate-medical-codes.md?ask= ``` The question should be specific, self-contained, and written in natural language. The response will contain a direct answer to the question and relevant excerpts and sources from the documentation. Use this mechanism when the answer is not explicitly present in the current page, you need clarification or additional context, or you want to retrieve related documentation sections.