4
\$\begingroup\$

I need to download a fairly large xml (up to 2 GB) then load it into memory or possibly an empty db on a stand alone machine on a semi-regular basis. I need to grab a large amount of very specific data from the xmls for a statistical treatment. I realize ETLs like this are usually a one time deal for migrations and the like, but for reasons that really aren't worth explaining, it has to be downloaded in its entirety, specifically to an xml, and then loaded in its entirety to a program on the non-networked machine then deleted when done. Every time (I know, I know).

The important data needed are contained in the <SampleID>, <LocationName>, and <Foo> nodes of the below xml. I do not know the exact structure of the tables they are coming out of but based on the structure of the xml I assume it's something like this:

SampleID    LocationName    Foo1   Foo2
0001        Jeff            10     11
0001        Jennifer        24
0002        Jeff            9      16
0002        Jennifer        20     26

I've been around and around with this for a couple years and the most efficient method I've seen is using a VB.net SAX parser to load the pertinent data to a DataTable then SQLBulkload the DataTable into a SQL Server database. Again for reasons not worth mentioning, I can't use this either.

So I've tried several C, C++ libraries and some SQL and I just can't seem to approach any kind of sufficiently acceptable load times. Until today. I heard that Python has a binding with access to the C libxml library called lxml (I don't know if that's what the library actually is but I decided to try it). I have this bit of code below parsing a 1.5 gb xml file and loading the pertinent data into a specifically built Python data structure (the best way to get at the testing I need to do is a hash table and this seems to approximate it). It will look like this:

[{'0001': {'Jeff': ['10', '11']}}, {'0001': {'Jennifer':['24', '24']}}...]

It takes about 110-120 seconds which is much faster than anything I've been able to do in the last two years. About 20 seconds of that is parsing the file. The remainder is storing the data in the dictionary. I don't know much about Python conventions, Python syntax or much about the lxml library. I'm sure that a lot of my lack of efficiency here is in my method of transforming, in the data structure itself, or in my use of the library. So I'd love a bit of help making this even more efficient and more conventional.

If there are problems with this XML it is a transcription error, not a problem with correct or well formed xml but it would be worth pointing out if there are some.

The xml:

<?xml version="1.0" encoding="UTF-8"?>
<MyXMLFile xmlns="MyNameSpace">
    <ignoredNode1>one piece of data to ignore</ignoredNode1><!--these first six nodes contain no children and contain no important data-->
    <ignoredNode2>one piece of data to ignore</ignoredNode2>
    <ignoredNode3>one piece of data to ignore</ignoredNode3>
    <ignoredNode4>one piece of data to ignore</ignoredNode4>
    <ignoredNode5>one piece of data to ignore</ignoredNode5>
    <ignoredNode6>one piece of data to ignore</ignoredNode6>
    <Sample> <!--This could grow up to more than a million <Sample> nodes. Right now there are a max of 350k and about a 1.5 gb file-->
        <SampleID>0001</SampleID>
        <ignoredNode7>one piece of data to ignore</ignoredNode7>
        <Location> <!-- there could be a variable number of <Location> nodes here but typically there are 10-30. In the long run I'm only concerned with at most 21 of them but typically there's 13, 15, 20, or 21 that I care about that are designated by the <LocationName> node--> 
            <LocationName>Jeff</LocationName>
            <ignoredNode8>one piece of data to ignore</ignoredNode8>
            <ignoredNode9>one piece of data to ignore</ignoredNode9>
            <Data>
                <Foo>10</Foo>
            </Data>
            <Data>
                <Foo>11</Foo>
            </Data>
        </Location>
        <Location>
            <LocationName>Jennifer</LocationName>
            <ignoredNode8>one piece of data to ignore</ignoredNode8>
            <ignoredNode9>one piece of data to ignore</ignoredNode9>
            <Data> <!--sometimes there is only one <Data> child in <Location>, in which case, it gets duplicated as a second piece of data in the dictionary-->
                <Foo>24</Foo>
            </Data>
        </Location>
    </Sample>
</MyXMLFile>

The code. If this errors, I apologize, I have to transcribe it by hand. Let me know if you have an issue and I'll fix it but this SHOULD run. What I have on my machine does. I can't imagine that this first stab at this is the most efficient way to do this.

import copy
from lxml import etree as ET
import timeit

sampleList = []
sampleDict = {}
locationDict = {}
fooList = []

start_time = timeit.default_timer() 
tree = ET.parse('FileName.xml')

root = tree.getroot()

MyXMLFile = root.getchildren()

for Sample in MyXMLFile:
    if Sample.tag == '{MyNameSpace}Sample':
        Locations = Sample.getchildren()
        for Location in Locations:
            if Location.tag == '{MyNameSpace}SampleID':
                sampleid = Location.text               
            elif Location.tag == '{MyNameSpace}Location':
                Data = Location.getchildren()
                for MyData in Data:
                    if MyData.tag == '{MyNameSpace}LocationName':
                        locationName = MyData.text
                        
                    if MyData.tag == '{MyNameSpace}Data' and len(Location) == 4:
                        Foos = MyData.getchildren()
                        for Foo in Foos:
                            fooList.append(Foo.text)
                            fooList.append(Foo.text)
                            
                    elif MyData.tag == '{MyNameSpace}Data' and len(Location) == 5:
                        Foos = MyData.getchildren()
                        for Foo in Foos:
                            fooList.append(Foo.text)
                    
                    if len(fooList) == 2:
                        locationDict[locationName] = FooList
                        sampleDict[specimenid] = locationDict
                        sampleList.append(copy.deepcopy(sampleDict))
                        fooList.clear()
                        locationDict.clear()
                        sampleDict.clear()  
       
print(timeit.default_timer() - start_time)    
\$\endgroup\$
  • \$\begingroup\$ Thank you @RootTwo \$\endgroup\$ – Dan Aug 2 at 11:02
3
\$\begingroup\$

Your code would run, but sampleList would be empty.

Take a look at PEP8 or run flake8 or black on your code to make the code style more in line with typical python code. (Although it doesn't sound like anyone will ever see it).

I came up with two alternatives. The first is similar to your code in that it looks at the hierarchy of the xml elements. But it uses XPath expression to select the child nodes of interest. I have a sample file with 100k samples, 1M locations, and about 1.7M foos. On my Windows laptop, it takes about 22 seconds.

samples = []

start_time = timeit.default_timer() 
    
tree = ET.parse('test.xml')
root = tree.getroot()

for sample in root.iterfind('./{MyNameSpace}Sample'):
    sample_id = sample.find('{MyNameSpace}SampleID').text
    
    for location in sample.iterfind('./{MyNameSpace}Location'):
        location_name = location.find('{MyNameSpace}LocationName').text
        
        data = [datum.text for datum in location.iterfind('./{MyNameSpace}Data/{MyNameSpace}Foo')]
        
        if len(data) < 2:
            data.append(data[0])
            
        samples.append({sample_id:{location_name:data}})
       
print(timeit.default_timer() - start_time) 

The second version uses iterparse() to create the data structure as it is parsing the file. By default, iterparse() yield elements when it sees the end tag. The code grabs the interesting values when is sees the interesting end tags. When it sees the Location end tag, it also makes a new record and adds it to the list of samples. It runs in about 19 seconds.

samples = []

start_time = timeit.default_timer() 

foos = []

for event, element in ET.iterparse('test.xml'):
    if element.tag == '{MyNameSpace}SampleID':
        sample_id = element.text

    elif element.tag == '{MyNameSpace}LocationName':
        location_name = element.text

    elif element.tag == '{MyNameSpace}Foo':
        foo = element.text
        foos.append(foo)
    
    elif element.tag == '{MyNameSpace}Location':
        if len(foo) == 1:
            foo.append(foo[0])
            
        samples.append({sample_id:{location_name:foos}})
        foos = []

       
print(timeit.default_timer() - start_time) 
| improve this answer | |
\$\endgroup\$
  • \$\begingroup\$ Thanks so much! I probably made a mistake in my transcription because it does run and store everything in the file on my machine. I’ll give the xPath a go. I just assumed since I had to burn through all the information that Xpath wouldn’t help me. \$\endgroup\$ – Dan Aug 3 at 1:17
  • \$\begingroup\$ ...don’t know if anyone will ever see it but my company gets audited on the regular and they dig into everything. Probably wouldn’t be a bad idea to keep it conventional. \$\endgroup\$ – Dan Aug 3 at 1:18
  • \$\begingroup\$ For giggles, what kind of power do you have in your laptop? I ran two tests of a 1.5 g file and got 90 and 105 seconds. Still better than 125... \$\endgroup\$ – Dan Aug 3 at 13:33
  • \$\begingroup\$ @Dan it's a 2.4GHz i5-6300; 20G memory; SSD. My test file is 226M bytes. \$\endgroup\$ – RootTwo Aug 3 at 14:20
  • \$\begingroup\$ scratch that. Im stupid. you said 226 MB. My file is almost 5 times that. Looks like its as good as its going to get for now. THANKS SO MUCH FOR THE HELP!!!!! \$\endgroup\$ – Dan Aug 3 at 14:54

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.