# 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](https://docs.finngen.fi/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database). 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](https://docs.finngen.fi/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database) 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 ](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/detailed-longitudinal-data)contains medical codes from different [finnish health registries](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/registers-in-the-detailed-longitudinal-data) representing diagnosis, procedures, measurements and observations. These medical codes comes from [international and finnish code sets](https://docs.finngen.fi/finngen-data-specifics/finnish-health-registers-and-medical-coding/international-and-finnish-health-code-sets) 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](https://docs.finngen.fi/working-in-the-sandbox/which-tools-are-available/bigquery-relational-database).

```
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](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/registers-in-the-detailed-longitudinal-data). 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](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data) 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](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data) 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](https://docs.finngen.fi/finngen-data-specifics/red-library-data-individual-level-data/what-phenotype-files-are-available-in-sandbox-1/service-sector-data) 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).