Convert pandas dataframe column with xml data to normalised columns?
<p>I have a <code>DataFrame</code> in <code>pandas</code>, one of whose columns is a XML string. What I want to do is create one column for each of the xml nodes with column names in a normalised form. For example,</p>

<pre><code>    id    xmlcolumn
    1     &lt;main attr1='abc' attr2='xyz'&gt;&lt;item&gt;&lt;prop1&gt;text1&lt;/prop1&gt;&lt;prop2&gt;text2&lt;/prop2&gt;&lt;/item&gt;&lt;/main&gt;
    2     &lt;main ........&lt;/main&gt;
</code></pre>

<p>I want to convert this to a data frame like so:</p>

<pre><code>id   main.attr1  main.attr2 main.item.prop1  main.item.prop2
1       abc        xyz          text1           text2
2      .....
</code></pre>

<p>How would I do that, while still keeping the existing columns in the <code>DataFrame</code>?</p>

<p>The first step that needs to be done is to convert the XML string to a pandas <code>Series</code> (under the assumption, that there will always be the same amount of columns in the end). So you need a function like:</p>

<pre><code>def convert_xml(raw):
    # some etree xml mangling
</code></pre>

<p>This can be achieved e.g. using the <a href="https://docs.python.org/2/library/xml.etree.elementtree.htm" rel="nofollow">etree</a> package in python. The returned series must have an index, where each entry in the index is the new column name to appear, e.g. for your example:</p>

<pre><code>pd.Series(['abc', 'xyz'], index=['main.attr1', 'main.attr2'])
</code></pre>

<p>Given this function, you can do the following with pandas (mocking away the XML mangling):</p>

<pre><code>frame = pd.DataFrame({'keep': [42], 'xml': '&lt;foo&gt;&lt;/foo&gt;'})
temp = frame['xml'].apply(convert_xml)
frame = frame.drop('xml', axis=1)
frame = pd.concat([frame, temp], axis=1)
</code></pre>

python xml pandas data-analysis