Python Pandas : How to use values inside dictionaries as columns?

0
votes

I'm having trouble turning an xml file into a dataframe.

Simplified version of my data looks like this :

data =  {'item': [{'col': [ {'#text': '2', '@name': 'a'},  {'#text': '4', '@name': 'b'} ] # - this list will be the first row
                ,  'col': [ {'#text': '6', '@name': 'a'},  {'#text': '2', '@name': 'b'} ] # - this will be the second row
                ,  'col': [ {'#text': '3' ,'@name': 'a'},  {'#text': '11', '@name': 'b'}] } #- this will be the third row and so on.

For each row, I want to use each list paired with 'col', while using #text:values as values.

For the columns I want to use values for @name ('a' and 'b') as columns.

My desired output would look like this.

desired output

how can I achieve this?

pythonpandasxmldataframe