Reply
New Contributor
Posts: 1
Registered: ‎04-30-2019

Issue while flattening complex nested XML file in pyspark 2.x

I have a complex XML file which is nested. I have read using data bricks API and parsed. So when I see the schema, It is having complex nesting. So I need to flatten that nesting and select some required data after flattening. But after the nested schema is flattened I couldn't see any data. It is blank. For some files, it works and for some other files, it doesn't work.

 

Please find the below steps I have followed to flatten the nested data frame.

 

def flattening_check(unflattened_df):
    print("Inside flattening function")
    dit['main_df'] = unflattened_df
    for col_name, col_type in unflattened_df.dtypes:

        if col_type[:6] == 'struct':
            dit['main_df'] = flatten_df(dit['main_df'])

        if col_type[:5] == 'array':
            dit['main_df'] = explode_array(dit['main_df'], col_name)

    print(dit['main_df'].printSchema())

    struct_types = [c[0] for c in dit['main_df'].dtypes if c[1][:6] == 'struct']
    array_types = [c[0] for c in dit['main_df'].dtypes if c[1][:5] == 'array']
    if len(struct_types) > 0 or len(array_types) > 0:
        dit['main_df'].persist(StorageLevel.MEMORY_AND_DISK)
        flattening_check(dit['main_df'])

    return dit['main_df']

def flatten_df(nested_df):
    print("Visited flatten df")
    flat_cols = [c[0] for c in nested_df.dtypes if c[1][:6] != 'struct']
    nested_cols = [c[0] for c in nested_df.dtypes if c[1][:6] == 'struct']

    flat_df = nested_df.select(flat_cols + [F.col(nc+'.'+c).alias(nc+'__'+c) for nc in nested_cols for c in nested_df.select(nc+'.*').columns])
    return flat_df


def explode_array(nested_df, column):
    print("Visited array explode")
    explode_col_name = nested_df.select(column)
    exploded_df = nested_df.withColumn(column, explode(explode_col_name[0]))
    return exploded_df

if __name__ == '__main__':
  xml_data = spark.read.format("com.databricks.spark.xml").option("rootTag","datasets").option("rowTag", "dataset").load("nasa.xml")

    print(xml_data.printSchema())

    final_df = flattening_check(xml_data)
    print(final_df.printSchema())
    print(final_df.show())

After I run this I get an empty data frame.

 

results:

 

The below schema is before flattening data:

 

root
 |-- _subject: string (nullable = true)
 |-- _xlink: string (nullable = true)
 |-- altname: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- _VALUE: string (nullable = true)
 |    |    |-- _type: string (nullable = true)
 |-- descriptions: struct (nullable = true)
 |    |-- abstract: struct (nullable = true)
 |    |    |-- para: array (nullable = true)
 |    |    |    |-- element: string (containsNull = true)
 |    |-- description: struct (nullable = true)
 |    |    |-- heading: string (nullable = true)
 |    |    |-- para: array (nullable = true)
 |    |    |    |-- element: string (containsNull = true)
 |    |-- details: struct (nullable = true)
 |    |    |-- astroObjects: struct (nullable = true)
 |    |    |    |-- astroObject: array (nullable = true)
 |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |-- name: array (nullable = true)
 |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |    |    |-- position: struct (nullable = true)
 |    |    |    |    |    |    |-- dec: string (nullable = true)
 |    |    |    |    |    |    |-- ra: string (nullable = true)
 |    |    |-- para: array (nullable = true)
 |    |    |    |-- element: string (containsNull = true)
 |-- field: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- definition: string (nullable = true)
 |    |    |-- footnote: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- _footnoteId: string (nullable = true)
 |    |    |    |    |-- para: array (nullable = true)
 |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |-- name: string (nullable = true)
 |    |    |-- units: string (nullable = true)
 |-- fitsFile: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- _href: string (nullable = true)
 |    |    |-- description: struct (nullable = true)
 |    |    |    |-- para: string (nullable = true)
 |    |    |-- name: string (nullable = true)
 |-- ftp: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _href: string (nullable = true)
 |-- history: struct (nullable = true)
 |    |-- ingest: struct (nullable = true)
 |    |    |-- acknowledgement: string (nullable = true)
 |    |    |-- creator: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- affiliation: string (nullable = true)
 |    |    |    |    |-- lastName: string (nullable = true)
 |    |    |    |    |-- staff: string (nullable = true)
 |    |    |-- date: struct (nullable = true)
 |    |    |    |-- day: long (nullable = true)
 |    |    |    |-- month: string (nullable = true)
 |    |    |    |-- year: string (nullable = true)
 |    |-- revisions: struct (nullable = true)
 |    |    |-- revision: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- creator: struct (nullable = true)
 |    |    |    |    |    |-- lastName: string (nullable = true)
 |    |    |    |    |-- date: struct (nullable = true)
 |    |    |    |    |    |-- year: string (nullable = true)
 |    |    |    |    |-- para: string (nullable = true)
 |-- holding: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _role: string (nullable = true)
 |-- identifier: string (nullable = true)
 |-- keywords: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- _parentListURL: string (nullable = true)
 |    |    |-- keyword: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |    |-- _href: string (nullable = true)
 |-- reference: struct (nullable = true)
 |    |-- ftp: struct (nullable = true)
 |    |    |-- _VALUE: string (nullable = true)
 |    |    |-- _href: string (nullable = true)
 |    |-- holding: struct (nullable = true)
 |    |    |-- _VALUE: string (nullable = true)
 |    |    |-- _role: string (nullable = true)
 |    |-- related: struct (nullable = true)
 |    |    |-- ftp: struct (nullable = true)
 |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |-- _href: string (nullable = true)
 |    |    |-- holding: struct (nullable = true)
 |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |-- _role: string (nullable = true)
 |    |    |-- url: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |    |-- _href: string (nullable = true)
 |    |    |-- xlink:simple: struct (nullable = true)
 |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |-- _href: string (nullable = true)
 |    |-- source: struct (nullable = true)
 |    |    |-- journal: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- author: array (nullable = true)
 |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |-- initial: array (nullable = true)
 |    |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |    |    |    |-- lastName: string (nullable = true)
 |    |    |    |    |    |    |-- suffix: string (nullable = true)
 |    |    |    |    |-- bibcode: string (nullable = true)
 |    |    |    |    |-- date: struct (nullable = true)
 |    |    |    |    |    |-- year: string (nullable = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |    |-- pageno: string (nullable = true)
 |    |    |    |    |-- title: string (nullable = true)
 |    |    |    |    |-- volume: string (nullable = true)
 |    |    |-- other: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- author: array (nullable = true)
 |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |-- initial: array (nullable = true)
 |    |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |    |    |    |-- lastName: string (nullable = true)
 |    |    |    |    |    |    |-- suffix: string (nullable = true)
 |    |    |    |    |-- bibcode: string (nullable = true)
 |    |    |    |    |-- city: string (nullable = true)
 |    |    |    |    |-- date: struct (nullable = true)
 |    |    |    |    |    |-- year: string (nullable = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |    |-- publisher: string (nullable = true)
 |    |    |    |    |-- title: string (nullable = true)
 |    |-- telnet: struct (nullable = true)
 |    |    |-- _VALUE: string (nullable = true)
 |    |    |-- _href: string (nullable = true)
 |    |-- url: array (nullable = true)
 |    |    |-- element: struct (containsNull = true)
 |    |    |    |-- _VALUE: string (nullable = true)
 |    |    |    |-- _href: string (nullable = true)
 |    |-- xlink:simple: struct (nullable = true)
 |    |    |-- _VALUE: string (nullable = true)
 |    |    |-- _href: string (nullable = true)
 |-- tableHead: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- field: array (nullable = true)
 |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |-- definition: string (nullable = true)
 |    |    |    |    |-- footnote: array (nullable = true)
 |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |-- _footnoteId: string (nullable = true)
 |    |    |    |    |    |    |-- para: array (nullable = true)
 |    |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |    |-- units: string (nullable = true)
 |    |    |-- fields: struct (nullable = true)
 |    |    |    |-- field: array (nullable = true)
 |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |-- definition: string (nullable = true)
 |    |    |    |    |    |-- footnote: array (nullable = true)
 |    |    |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |    |    |-- _footnoteId: string (nullable = true)
 |    |    |    |    |    |    |    |-- para: array (nullable = true)
 |    |    |    |    |    |    |    |    |-- element: string (containsNull = true)
 |    |    |    |    |    |-- name: string (nullable = true)
 |    |    |    |    |    |-- units: string (nullable = true)
 |    |    |    |-- units: string (nullable = true)
 |    |    |-- tableLinks: struct (nullable = true)
 |    |    |    |-- tableLink: array (nullable = true)
 |    |    |    |    |-- element: struct (containsNull = true)
 |    |    |    |    |    |-- _href: string (nullable = true)
 |    |    |    |    |    |-- title: string (nullable = true)
 |    |    |-- units: string (nullable = true)
 |-- textFile: array (nullable = true)
 |    |-- element: struct (containsNull = true)
 |    |    |-- _href: string (nullable = true)
 |    |    |-- _type: string (nullable = true)
 |    |    |-- description: struct (nullable = true)
 |    |    |    |-- footnote: struct (nullable = true)
 |    |    |    |    |-- para: string (nullable = true)
 |    |    |    |-- para: string (nullable = true)
 |    |    |-- name: string (nullable = true)
 |-- title: string (nullable = true)
 |-- units: string (nullable = true)
 |-- url: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _href: string (nullable = true)
 |-- xlink:simple: struct (nullable = true)
 |    |-- _VALUE: string (nullable = true)
 |    |-- _href: string (nullable = true)

 

After flattening I could see the schema flattened.

 

root
 |-- _subject: string (nullable = true)
 |-- _xlink: string (nullable = true)
 |-- identifier: string (nullable = true)
 |-- title: string (nullable = true)
 |-- units: string (nullable = true)
 |-- altname___VALUE: string (nullable = true)
 |-- altname___type: string (nullable = true)
 |-- ftp___VALUE: string (nullable = true)
 |-- ftp___href: string (nullable = true)
 |-- holding___VALUE: string (nullable = true)
 |-- holding___role: string (nullable = true)
 |-- url___VALUE: string (nullable = true)
 |-- url___href: string (nullable = true)
 |-- xlink:simple___VALUE: string (nullable = true)
 |-- xlink:simple___href: string (nullable = true)
 |-- field__definition: string (nullable = true)
 |-- field__name: string (nullable = true)
 |-- field__units: string (nullable = true)
 |-- fitsFile___href: string (nullable = true)
 |-- fitsFile__name: string (nullable = true)
 |-- descriptions__abstract__para: string (nullable = true)
 |-- descriptions__description__heading: string (nullable = true)
 |-- descriptions__description__para: string (nullable = true)
 |-- descriptions__details__para: string (nullable = true)
 |-- history__ingest__acknowledgement: string (nullable = true)
 |-- reference__ftp___VALUE: string (nullable = true)
 |-- reference__ftp___href: string (nullable = true)
 |-- reference__holding___VALUE: string (nullable = true)
 |-- reference__holding___role: string (nullable = true)
 |-- reference__telnet___VALUE: string (nullable = true)
 |-- reference__telnet___href: string (nullable = true)
 |-- reference__xlink:simple___VALUE: string (nullable = true)
 |-- reference__xlink:simple___href: string (nullable = true)
 |-- fitsFile__description__para: string (nullable = true)

 

But when I try to print the data, it is printing blank. Not even a Null.

 

+--------+------+----------+-----+-----+---------------+--------------+-----------+----------+---------------+--------------+-----------+----------+--------------------+-------------------+-----------------+-----------+------------+---------------+--------------+----------------------------+----------------------------------+-------------------------------+---------------------------+--------------------------------+----------------------+---------------------+--------------------------+-------------------------+-------------------------+------------------------+-------------------------------+------------------------------+---------------------------+--------------------------+----------------------------+---------------------------+-------------------------------+------------------------------+-----------------------------------+----------------------------------+----------------------------------------+---------------------------------------+------------------------+----------------+---------------+---------------+--------------+------------------------+---------------------------+----------------------+---------------------+----------------------------+---------------------+-------------------------------------+----------------------------------+-------------------------------+----------------------------------+-------------------------------+------------------------------+-----------------------------------+--------------------------------+----------------------------------+---------------------------------+----------------------------------+---------------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+------------------------------------------------------+-------------------------+------------------------+----------------------------+----------------------+-----------------------+------------------------------------+------------------------------+-------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+-----------------------------------------------+----------------------------------------+--------------------------------------+------------------------------------+---------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------+--------------------------------------------+------------------------------------------+-----------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+--------------------------------+-----------------------------------------------+----------------------------------------+
|_subject|_xlink|identifier|title|units|altname___VALUE|altname___type|ftp___VALUE|ftp___href|holding___VALUE|holding___role|url___VALUE|url___href|xlink:simple___VALUE|xlink:simple___href|field__definition|field__name|field__units|fitsFile___href|fitsFile__name|descriptions__abstract__para|descriptions__description__heading|descriptions__description__para|descriptions__details__para|history__ingest__acknowledgement|reference__ftp___VALUE|reference__ftp___href|reference__holding___VALUE|reference__holding___role|reference__telnet___VALUE|reference__telnet___href|reference__xlink:simple___VALUE|reference__xlink:simple___href|fitsFile__description__para|history__ingest__date__day|history__ingest__date__month|history__ingest__date__year|reference__related__ftp___VALUE|reference__related__ftp___href|reference__related__holding___VALUE|reference__related__holding___role|reference__related__xlink:simple___VALUE|reference__related__xlink:simple___href|keywords___parentListURL|tableHead__units|textFile___href|textFile___type|textFile__name|tableHead__fields__units|textFile__description__para|reference__url___VALUE|reference__url___href|field__footnote___footnoteId|field__footnote__para|history__ingest__creator__affiliation|history__ingest__creator__lastName|history__ingest__creator__staff|history__revisions__revision__para|reference__related__url___VALUE|reference__related__url___href|reference__source__journal__bibcode|reference__source__journal__name|reference__source__journal__pageno|reference__source__journal__title|reference__source__journal__volume|reference__source__other__bibcode|reference__source__other__city|reference__source__other__name|reference__source__other__publisher|reference__source__other__title|descriptions__details__astroObjects__astroObject__name|keywords__keyword___VALUE|keywords__keyword___href|tableHead__field__definition|tableHead__field__name|tableHead__field__units|tableHead__fields__field__definition|tableHead__fields__field__name|tableHead__fields__field__units|tableHead__tableLinks__tableLink___href|tableHead__tableLinks__tableLink__title|textFile__description__footnote__para|history__revisions__revision__creator__lastName|history__revisions__revision__date__year|reference__source__journal__date__year|reference__source__other__date__year|descriptions__details__astroObjects__astroObject__position__dec|descriptions__details__astroObjects__astroObject__position__ra|reference__source__journal__author__initial|reference__source__journal__author__lastName|reference__source__journal__author__suffix|reference__source__other__author__initial|reference__source__other__author__lastName|reference__source__other__author__suffix|tableHead__field__footnote___footnoteId|tableHead__field__footnote__para|tableHead__fields__field__footnote___footnoteId|tableHead__fields__field__footnote__para|
+--------+------+----------+-----+-----+---------------+--------------+-----------+----------+---------------+--------------+-----------+----------+--------------------+-------------------+-----------------+-----------+------------+---------------+--------------+----------------------------+----------------------------------+-------------------------------+---------------------------+--------------------------------+----------------------+---------------------+--------------------------+-------------------------+-------------------------+------------------------+-------------------------------+------------------------------+---------------------------+--------------------------+----------------------------+---------------------------+-------------------------------+------------------------------+-----------------------------------+----------------------------------+----------------------------------------+---------------------------------------+------------------------+----------------+---------------+---------------+--------------+------------------------+---------------------------+----------------------+---------------------+----------------------------+---------------------+-------------------------------------+----------------------------------+-------------------------------+----------------------------------+-------------------------------+------------------------------+-----------------------------------+--------------------------------+----------------------------------+---------------------------------+----------------------------------+---------------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+------------------------------------------------------+-------------------------+------------------------+----------------------------+----------------------+-----------------------+------------------------------------+------------------------------+-------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+-----------------------------------------------+----------------------------------------+--------------------------------------+------------------------------------+---------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------+--------------------------------------------+------------------------------------------+-----------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+--------------------------------+-----------------------------------------------+----------------------------------------+
+--------+------+----------+-----+-----+---------------+--------------+-----------+----------+---------------+--------------+-----------+----------+--------------------+-------------------+-----------------+-----------+------------+---------------+--------------+----------------------------+----------------------------------+-------------------------------+---------------------------+--------------------------------+----------------------+---------------------+--------------------------+-------------------------+-------------------------+------------------------+-------------------------------+------------------------------+---------------------------+--------------------------+----------------------------+---------------------------+-------------------------------+------------------------------+-----------------------------------+----------------------------------+----------------------------------------+---------------------------------------+------------------------+----------------+---------------+---------------+--------------+------------------------+---------------------------+----------------------+---------------------+----------------------------+---------------------+-------------------------------------+----------------------------------+-------------------------------+----------------------------------+-------------------------------+------------------------------+-----------------------------------+--------------------------------+----------------------------------+---------------------------------+----------------------------------+---------------------------------+------------------------------+------------------------------+-----------------------------------+-------------------------------+------------------------------------------------------+-------------------------+------------------------+----------------------------+----------------------+-----------------------+------------------------------------+------------------------------+-------------------------------+---------------------------------------+---------------------------------------+-------------------------------------+-----------------------------------------------+----------------------------------------+--------------------------------------+------------------------------------+---------------------------------------------------------------+--------------------------------------------------------------+-------------------------------------------+--------------------------------------------+------------------------------------------+-----------------------------------------+------------------------------------------+----------------------------------------+---------------------------------------+--------------------------------+-----------------------------------------------+----------------------------------------+

What could be the possible issue? 

 

It works for a few files and it doesn't work for a few files. Please help me to understand the issue.

 

Thanks in advance.

 

 

 

 

New Contributor
Posts: 5
Registered: ‎08-08-2018

Re: Issue while flattening complex nested XML file in pyspark 2.x

Are all the files same or they have different set of mappings in the xml schema ?