Converting Nested XML to Pandas Data Frame

user_16230943 0
Viewed 85

I have below XML string and trying to convert to DF using below code. In the XML string for each value tag I need them in nested list. Posted the output that I am getting and also sample output of what I am expecting. I am using the below code and might be missing a small logic which I am trying to find out. Can Someone Help me out on what I am missing?

XML String:

xml_str = """
<bookstore>
<book category="COOKING">
    <title lang="en">Everyday Italian</title>
    <author>Giada De Laurentiis</author>
    <year>2005</year>
    <price>30.00</price>
    <values>
      <value>1</value>
      <value>5</value>
      <value>2</value>
    </values>
    <values>
      <value>3</value>
      <value>4</value>
      <value>5</value>
    </values>
    <values>
      <value>6</value>
      <value>7</value>
      <value>8</value>
    </values>
  </book>
</bookstore>
"""

**Code:**

import xml.etree.ElementTree as ET
import pandas as pd


def attribute_add(el, temp2, tag):
    attrib_l = list(el.attrib)
    for att in attrib_l:
        cur_tag_att = tag + '_' + att
        if cur_tag_att in temp2.keys():
            if isinstance(temp2[cur_tag_att], list):
                temp2[cur_tag_att].append(el.attrib[att])
            else:
                el_att_list = []
                el_att_list.extend([temp2[cur_tag_att], el.attrib[att]])
                temp2[cur_tag_att] = el_att_list
        else:
            temp2[cur_tag_att] = el.attrib[att]
    return


def element_loop(element_l, temp1, tag):
    """Input:
            element_list
            sample_dictionary_variable to write the output to
            current xml element tag
        Output:
            dictionary"""
    for el in element_l:
        el_tag = []
        nested_l = list(el)
        new_tag = tag + '_' + el.tag
        attribute_add(el, temp1, new_tag)
        if len(nested_l) == 0:
            if el.text is not None:
                if new_tag in temp1.keys():
                    if isinstance(temp1[new_tag], list):
                        temp1[new_tag].append(el.text)
                    else:
                        el_tag_list = []
                        el_tag_list.extend([temp1[new_tag], el.text])
                        temp1[new_tag] = el_tag_list
                else:
                    temp1[new_tag] = el.text
        elif len(nested_l) > 0:
            element_loop(el, temp1, new_tag)
    return temp1


def convert_xml_to_df(xml_str):
    """Input: XML file
        Output:
        A pandas dataframe with nested xml elements flattened out to individual columns"""
    root = ET.fromstring(xml_str)
    full_data = []
    for ri in root:
        temp = {}
        attribute_add(ri, temp, ri.tag)
        element_l = list(ri)
        full_data.append(element_loop(element_l, temp, ri.tag))
    df = pd.DataFrame(full_data)
    return df

convert_xml_to_df(xml_str)


**Output:**

 book_category book_title_lang        book_title          book_author  
0   COOKING          en            Everyday Italian    Giada De Laurentiis

  book_year book_price            book_values_value
0   2005      30.00         [1, 5, 2, 3, 4, 5, 6, 7, 8]




**Expected Output:**

 book_category book_title_lang        book_title          book_author  
0   COOKING          en            Everyday Italian    Giada De Laurentiis

  book_year book_price            book_values_value
0   2005      30.00         [[1, 5, 2],[ 3, 4, 5], [6, 7, 8]]
hdfs dataframe xml
1 Answers

It seems to me you're making it a bit too complicated. Try something like the following (note: that can be simplified further, but I didn't do in order to make the code is easier to read):

columns = ['category','lang','title','author','year','price','values']
rows = []
for book in books:
    row = []
    row.append(book.get('category'))
    row.append(book.find('./title').get('lang'))
    row.append(book.find('./title').text)
    row.append(book.find('./author').text)
    row.append(book.find('./year').text)
    row.append(book.find('./price').text)
    values = []
    for val in (book.findall('.//values')):
        values.append([v.text for v in val.findall('.//value')])
    row.append(values)
    rows.append(row)
df = pd.DataFrame(rows,columns=columns)
df

The output should be similar to your expected output.

user_94480900
Related