pass dataframe column as parameter in xpath

user_6176642 0
Viewed 49

I am using xpath in pyspark to extract from xml which is stored as a column in a table.

Below works fine

entity_id="D8"
                               
dfquestionstep=df_source_xml.selectExpr("disclosure_entity_id",       
      f'xpath(**xml**,"*/entities/entity[@type=\'TI\']/entity[@type=\'UNDERWRITING\']/entity[@type=\'DISCLOSURES\']/entity[**@id=\'{entity_id}\'**]/entity[@type=\'DECISION_PATH\']/entity[@type=\'QUESTION_STEP\']/@id") QUESTION_STEP_ID' 
     )

PROBLEM

Now I want to pass disclosure_entity_id which is a column in dataframe having values like D8, D9 etc. in place of entity_id, i.e. entity[@id=disclosure_entity_id]

But all I get is [] as result when I execute like this, i.e. xpath fails to find anything.

Is there a way to pass the DF column directly as argument to XPATH like above?

pyspark xpath
1 Answers

Some testdata:

data = [
  ['a','<x><a>a1</a><b>b1</b><c>c1</c></x>'],
  ['b','<x><a>a2</a><b>b2</b><c>c2</c></x>'],
  ['c','<x><a>a3</a><b>b3</b><c>c3</c></x>'],
    ]
    
df = spark.createDataFrame(data, ['col','data'])

Attempt 1:

Creating a column with an XPath expression can be done:

from pyspark.sql import functions as f

df.withColumn('my_path', f.concat(f.lit('//'), f.col('col'))) \
  .selectExpr('xpath(data, my_path)').show()

But unfortunately the code above only yields the error message

AnalysisException: cannot resolve 'xpath(`data`, `my_path`)' due to data type mismatch: 
path should be a string literal; line 1 pos 0;

The path parameter of the xpath function has to be a constant string. This string is parsed before Spark even looks at the data.

Attempt 2:

Another option is to use an udf and use standard Python functions to process the XPath expression inside of the udf:

import xml.etree.ElementTree as ET
from pyspark.sql import types as T

def find_val(col, data): 
  result= ET.fromstring(data).find(f'.//{col}')
  if not result is None:
    return result.text

find_val_udf=f.udf(find_val, returnType=T.StringType())

df.select('col', 'data',  find_val_udf('col', 'data')).show(truncate=False)

Output:

+---+----------------------------------+-------------------+
|col|data                              |find_val(col, data)|
+---+----------------------------------+-------------------+
|a  |<x><a>a1</a><b>b1</b><c>c1</c></x>|a1                 |
|b  |<x><a>a2</a><b>b2</b><c>c2</c></x>|b2                 |
|c  |<x><a>a3</a><b>b3</b><c>c3</c></x>|c3                 |
+---+----------------------------------+-------------------+
user_21298010
Related