In Synapse Notebook using pyspark inbuilt functions how to convert country code to country name as description

SaiSekhar, MahasivaRavi (Philadelphia) 80

Hi Team,

Could you please help us that how to change country code to country name (as description) for a column in synapse notebook using pyspark (modules or libraries) could you please share the code.

we have used below but it is not working.
Eg:

import country_converter as coco
from pyspark.sql.types import StringType
from pyspark.sql.functions import udf, lit,col

def country_converter(country):
    if country:
        return coco.convert(country, to='IS02', not_found=None)
    return None

cc_udf = udf(country_converter, StringType())

Smaran Thoomu 19,390 Reputation points Microsoft Vendor

2025-01-17T09:42:08.19+00:00
Hi @SaiSekhar, MahasivaRavi (Philadelphia)
Welcome to Microsoft Q&A platform and thanks for posting your query here.

I understand you're trying to convert country codes to country names in a Synapse Notebook using PySpark. The approach you shared is on the right track, but I recommend trying the following revised implementation.

I have provided an updated code snippet for you to test. It uses the country_converter library and ensures proper handling of country codes. Please try running this code in your Synapse environment:

import country_converter as coco from pyspark.sql.types import StringType from pyspark.sql.functions import udf def country_converter(code): try: return coco.convert(names=code, to='name_short', not_found=None) except: return None cc_udf = udf(country_converter, StringType()) data = [("US",), ("IN",), ("GB",), ("FR",), (None,)] columns = ["country_code"] df = spark.createDataFrame(data, columns) result_df = df.withColumn("country_name", cc_udf(df["country_code"])) result_df.show()

Additionally, there is a related discussion on Stack Overflow that might be helpful: PySpark DataFrame: Convert country names to ISO codes with country_converter. While the example in the post focuses on converting country names to ISO codes, the principles and the library usage are similar.

Please try the solution and let us know if it resolves your issue.

Kindly consider upvoting the comment if the information provided is helpful. This can assist other community members in resolving similar issues.
SaiSekhar, MahasivaRavi (Philadelphia) 80 Reputation points

2025-01-17T10:48:54.67+00:00

HI @Smaran Thoomu ,

Thanks for the update, but when we are writing into delta file it is throughing an error could you please check.
Error:
e "/opt/spark/python/lib/pyspark.zip/pyspark/cloudpickle/cloudpickle.py", line 649, in subimport import(name) ModuleNotFoundError: No module named 'country_converter'
SaiSekhar, MahasivaRavi (Philadelphia) 80 Reputation points

2025-01-17T10:52:21.3233333+00:00

Hi @Smaran Thoomu , we are getting the below error while writing the file as delta could you please check using the above script. Error: "/opt/spark/python/lib/pyspark.zip/pyspark/cloudpickle/cloudpickle.py", line 649, in subimport import(name) ModuleNotFoundError: No module named 'country_converter'
phemanth 13,305 Reputation points Microsoft Vendor

2025-01-20T06:41:31.4133333+00:00

@SaiSekhar, MahasivaRavi (Philadelphia) We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Accepted answer

Smaran Thoomu 19,390 Reputation points Microsoft Vendor

2025-01-17T13:32:59.16+00:00
Hi @SaiSekhar, MahasivaRavi (Philadelphia)

It seems like the error you are encountering, ModuleNotFoundError: No module named 'country_converter', indicates that the country_converter library is not available in the Synapse environment where you're trying to write the data as a Delta file.To resolve this issue, you'll need to ensure that the country_converter library is installed and accessible within your Spark pool or environment.

In Azure Synapse Analytics, you need to install the country_converter library on your Spark pool.

The error you're seeing suggests that the module is missing, so we need to install it in the environment where the notebook is being executed.

If country_converter is not available in the default packages, download the library's .whl file or .tar.gz from PyPI.

In Synapse Studio, go to Manage > Apache Spark Pools > Packages and upload the .whl or .tar.gz file as per below.

Attach the library to your Spark pool and restart the pool.

Use the following test code to verify if the country_converter module is available:

Once the library is installed and the Spark pool is restarted, try executing the notebook again. The error should no longer appear when you're trying to write the data as Delta.

If the issue persists, please share the specifics of your Synapse environment (such as version or custom configurations), and I can assist you further.
Please sign in to rate this answer.
SaiSekhar, MahasivaRavi (Philadelphia) 80 Reputation points

2025-01-21T07:10:01.0466667+00:00

Hi @Smaran Thoomu ,
Many Thanks for the update,
It is working as expected but when we are validating the data few of the countries are not comming as expected is there any way that we can we use other key word (to = 'name_short').
There is a performance issue with this UDF function it taking more than 2 hours to execute for (6 lakh rows), could you please help me on this performance optimization. Please let us know how we can achieve the above two conditions.
Thanks in advance

SaiSekhar, MahasivaRavi (Philadelphia) 80 Reputation points

2025-01-21T07:12:54.6066667+00:00

Hi @Smaran Thoomu ,
Many Thanks for the update, The script is working as expected but there is performance issue, it is taking more than 2 hours for 6 lakh records, is there any way to optimize the code. please share the inputs on this.

Smaran Thoomu 19,390 Reputation points Microsoft Vendor

2025-01-21T09:32:59.79+00:00

Hi SaiSekhar, MahasivaRavi (Philadelphia)

Thank you for the update! I’m glad to hear that the script is now working as expected. I understand that you are experiencing two issues:

Performance concerns with the UDF for 6 lakh records.

Some country codes are not being mapped correctly.

Let me address these one by one.

Performance Optimization:

Using Python UDFs in PySpark can be slower due to serialization and deserialization overhead. For better performance, I recommend the following approaches:

Option1: Use Pandas UDFs

Pandas UDFs (vectorized UDFs) are optimized for large-scale processing. Here's how you can implement the solution with a Pandas UDF:

import country_converter as coco from pyspark.sql.functions import pandas_udf from pyspark.sql.types import StringType import pandas as pd @pandas_udf(StringType()) def country_converter_udf(country_code: pd.Series) -> pd.Series: return country_code.apply(lambda x: coco.convert(names=x, to='name_short', not_found=None) if x else None) # Sample DataFrame data = [("US",), ("IN",), ("GB",), ("FR",), (None,)] columns = ["country_code"] df = spark.createDataFrame(data, columns) # Apply Pandas UDF result_df = df.withColumn("country_name", country_converter_udf(df["country_code"])) result_df.show()

Pandas UDFs process data in batches, reducing overhead and improving performance.

Option2: Use a Broadcast Variable

If the country code-to-name mapping is relatively static, you can create a dictionary and use Spark’s broadcast mechanism to optimize lookups:

from pyspark.sql.functions import udf from pyspark.sql.types import StringType from pyspark.sql import SparkSession # Country mapping dictionary country_dict = { "US": "United States", "IN": "India", "GB": "United Kingdom", "FR": "France" } # Broadcast the dictionary broadcast_dict = spark.sparkContext.broadcast(country_dict) # Define UDF to use the broadcast dictionary def map_country(code): return broadcast_dict.value.get(code, None) country_udf = udf(map_country, StringType()) # Apply the UDF result_df = df.withColumn("country_name", country_udf(df["country_code"])) result_df.show()

This approach avoids repeated calls to the country_converter library and is faster for static mappings.

Handling Missing Mappings

To improve the handling of missing country codes, you can:

Use not_found='None' or an appropriate fallback in the coco.convert function.

Create a fallback mapping dictionary for unmapped country codes and update the DataFrame post-processing.

For example:

# Define fallback mapping for missing codes fallback_dict = { "XX": "Unknown", "ZZ": "Other" } # Update missing mappings after the initial conversion result_df = result_df.withColumn( "country_name", udf(lambda x: fallback_dict.get(x, x), StringType())(result_df["country_name"]) ) result_df.show()

Suggested Actions:

Test Performance Improvements: Try both Pandas UDFs and broadcast variables to see which approach works best for your dataset.

Validate Mappings: Review the mappings and add fallback logic for unmapped country codes to ensure completeness.

Monitor Execution: Use Spark UI and metrics to monitor job execution time and resource utilization.

I hope this helps. Do let us know if you any further queries.
Sign in to comment

Use comments to ask for clarification, additional information, or improvements to the question.

Share via

In Synapse Notebook using pyspark inbuilt functions how to convert country code to country name as description

0 additional answers

Your answer