Python/Pandas: updating a XML-file from pandas DataFrame

Question

I'm trying to update a xml-file with data from a pandas DataFrame: The xml-file looks like this:

<root>
    <NetworkData>
        <Element loadid="23" type="Load" node1="N23">
            <Name>load1</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>11</x>
            <y>15</y>
        </Element>
        <Element loadid="24" type="Load" node1="N24">
            <Name>load2</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>0.75</x>
            <y>600</y>
        </Element>
        ...

The DataFrame looks like:

ID	x	y
23	17	29
24	123	543
...	...	...

The ID is identical to the loadid in the xml-file. My aim is to update the values x and y in the xml-file with the values from the Dataframe. Is there a easy way do to this because the Dataframe is quite long? By the way all loadids can be found as IDs in the Dataframe. Output xml-file:

  <root>
    <NetworkData>
        <Element loadid="23" type="Load" node1="N23">
            <Name>load1</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>17</x>
            <y>29</y>
        </Element>
        <Element loadid="24" type="Load" node1="N24">
            <Name>load2</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>123</x>
            <y>543</y>
        </Element>
        ...

Thank you!

Alexandra Dudkina · Accepted Answer · 2021-07-29 08:35:35Z

3

import pandas as pd
from lxml import etree

# create test dataframe
df = pd.DataFrame({
    'ID': [23,24,25,26,27],
    'x': [21,22,23,24,25],
    'y': [101,102,103,104,105]
})

# create xml as a string (in your code it could be a file)
text = '''<root>
    <NetworkData>
        <Element loadid="23" type="Load" node1="N23">
            <Name>load1</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>11</x>
            <y>15</y>
        </Element>
        <Element loadid="24" type="Load" node1="N24">
            <Name>load2</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>0.75</x>
            <y>600</y>
        </Element>
        <Element loadid="99" type="Load" node1="N24">
            <Name>load2</Name>
            <ShortName/>
            <InputState>1027</InputState>
            <x>0.75</x>
            <y>600</y>
        </Element>
    </NetworkData>
  </root>
'''

# convert string to xml 
# (in your code it could be read from file instead)
doc = etree.fromstring(text)

# iterate over elements "Element"
for el in doc.xpath(".//NetworkData/Element"):
  # retrieve id from attribute value
  id = el.get('loadid')
  # retrieve appropriate row from dataframe
  row = df[df['ID'] == int(id)]
  # if found, update x and y
  if len(row) == 1:
      # find "x" element
      x = el.find('./x')
      # if found, update
      if x is not None:
        x.text = str(row['x'].item())
      # find "y" element
      y = el.find('./y')
      # if found update
      if y is not None:
        y.text = str(row['y'].item())
  # if there was no match found, update x and y with "0" values
  elif len(row) == 0:
      # find "x" element
      x = el.find('./x')
      # if found, update
      if x is not None:
        x.text = '0'
      # find "y" element
      y = el.find('./y')
      # if found update
      if y is not None:
        y.text = '0'

# save changed XML
# ...

edited Jul 29, 2021 at 8:35

answered Jul 27, 2021 at 9:15

Alexandra Dudkina

4,5123 gold badges18 silver badges29 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

Horst-Jackson Over a year ago

Thank you! This works fine. I have another question: There are several x and y entries in the xml- file which have no corresponding ID in the DataFrame (row = df[df['ID'] == int(id)] is not fulfilled). In this case I would like to set x and y to zero in the xml-file. Have you an idea how i could modify your solution above?

Alexandra Dudkina Over a year ago

Sure. Update answer with the elif branch which updates x and y with default zero values.

Horst-Jackson Over a year ago

Thank you! You have helped me a lot, I'm a absolute beginner.

Collectives™ on Stack Overflow

Python/Pandas: updating a XML-file from pandas DataFrame

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related