Extract data in subfield of multiple XML files

Python Help
1

My XML files are stuctured like this



<?xml version="1.0" encoding="utf-8" standalone="no"?>
<reiXmlPrenos>
  <nazivStavbe>EI RC</nazivStavbe>
  <LokacijaX>123049</LokacijaX>
  <Qfl>1808</Qfl>
  <fOVE>13.7</fOVE>
  <NetoVolumen>613</NetoVolumen>
  <Hv>104.2</Hv>
  <energenti>
    <energent>
      <sifra>energy_zp</sifra>
      <naziv>Zemeljski plin [kWh]</naziv>
      <vrednost>22482</vrednost>
    </energent>
    <energent>
      <sifra>energy_elko</sifra>
      <naziv>Ekstra lahko kurilno olje [l]</naziv>
      <vrednost>2482</vrednost>
    </energent>
    <energent>
      <sifra>energy_lb</sifra>
      <naziv>Lesna biomasa [kg]</naziv>
      <vrednost>20482</vrednost>
    </energent>
  <rei>
    <zavetrovanost>2</zavetrovanost>
    <cone>
      <cona>
        <cona_id>1</cona_id>
        <cc_si_cona>1110000</cc_si_cona>
        <visina_cone>2.7</visina_cone>
        <dolzina_cone>14</dolzina_cone>
      </cona>
      <cona>
        <cona_id>2</cona_id>
        <cc_si_cona>120000</cc_si_cona>
        <visina_cone>2.7</visina_cone>
        <dolzina_cone>14</dolzina_cone>
      </cona>
  </rei>
</reiXmlPrenos>

For several 1,000 XML files I want to read certain values from each and put it in a df. My current code looks like this:

import pandas as pd
import glob
import os

podatki = ['Qfl','Hv']

os.chdir(r'C:\Users\...\XMLs')
dir_path = glob.glob('*.xml')
#print(dir_path)

datoteka = (dir_path)

#print(datoteka)

df = pd.concat((pd.read_xml(file, xpath='//reiXmlPrenos')[podatki] for file in datoteka))
df['datoteka'] = datoteka
df = df.set_index('datoteka')
print(df)

This works just fine. But the problem are data under sub-categories. E.g. <energent> and <cc_si_cona>.

I’d like to have a df that would look like this:

datoteka  Qfl  Hv    energy_zp  energy_elko  energy_lb  cc_si_cona-1  cc_si_cona-2
xml1     1808  104.2  22482        2482         20482      1110000      120000

My try was as following, but it fails.

import pandas as pd
import glob
import os

podatki = ['Qfl','Hv']

podatki2 = ['sifra']

os.chdir(r'C:\Users\...\XMLs')
dir_path = glob.glob('*.xml')
#print(dir_path)

datoteka = (dir_path)

#print(datoteka)

df = pd.concat((pd.read_xml(file, xpath='//reiXmlPrenos')[podatki] for file in datoteka))
df['datoteka'] = datoteka
df = df.set_index('datoteka')
print(df)

df2 = pd.concat((pd.read_xml(file, xpath='//reiXmlPrenos/energenti/energent')[podatki2] for file in datoteka))
df2['datoteka'] = datoteka
df2 = df2.set_index('datoteka2')
print(df2)

So i tried to create a seperate df with a different root and merge the df-s later. But i get a warning:

ValueError: Length of values (6) does not match length of index (16)