XML data to R dataframes

Question

I have a XML file which was returned by a java application to find the optimal route in a transportation problem. Want to use this output file in R. I have used "XML" package in R. IN the XML file there is a node called "VehicleList", if possible just the path of each vehicle is also sufficient.

XML FILE:

<VrpVehicleRoutingSolution id="1">
  <id>0</id>
  <name>A-n33-k6</name>
  <distanceType>AIR_DISTANCE</distanceType>
  <distanceUnitOfMeasurement>distance</distanceUnitOfMeasurement>
  <locationList id="2">
    <VrpAirLocation id="3">
      <id>1</id>
      <latitude>34.0</latitude>
      <longitude>31.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="4">
      <id>2</id>
      <latitude>45.0</latitude>
      <longitude>55.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="5">
      <id>3</id>
      <latitude>70.0</latitude>
      <longitude>80.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="6">
      <id>4</id>
      <latitude>81.0</latitude>
      <longitude>70.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="7">
      <id>5</id>
      <latitude>85.0</latitude>
      <longitude>61.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="8">
      <id>6</id>
      <latitude>59.0</latitude>
      <longitude>55.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="9">
      <id>7</id>
      <latitude>45.0</latitude>
      <longitude>60.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="10">
      <id>8</id>
      <latitude>50.0</latitude>
      <longitude>64.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="11">
      <id>9</id>
      <latitude>80.0</latitude>
      <longitude>64.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="12">
      <id>10</id>
      <latitude>75.0</latitude>
      <longitude>90.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="13">
      <id>11</id>
      <latitude>25.0</latitude>
      <longitude>40.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="14">
      <id>12</id>
      <latitude>9.0</latitude>
      <longitude>66.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="15">
      <id>13</id>
      <latitude>1.0</latitude>
      <longitude>44.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="16">
      <id>14</id>
      <latitude>50.0</latitude>
      <longitude>54.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="17">
      <id>15</id>
      <latitude>35.0</latitude>
      <longitude>45.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="18">
      <id>16</id>
      <latitude>71.0</latitude>
      <longitude>84.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="19">
      <id>17</id>
      <latitude>1.0</latitude>
      <longitude>9.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="20">
      <id>18</id>
      <latitude>25.0</latitude>
      <longitude>54.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="21">
      <id>19</id>
      <latitude>45.0</latitude>
      <longitude>59.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="22">
      <id>20</id>
      <latitude>45.0</latitude>
      <longitude>71.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="23">
      <id>21</id>
      <latitude>66.0</latitude>
      <longitude>84.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="24">
      <id>22</id>
      <latitude>11.0</latitude>
      <longitude>35.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="25">
      <id>23</id>
      <latitude>81.0</latitude>
      <longitude>46.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="26">
      <id>24</id>
      <latitude>85.0</latitude>
      <longitude>10.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="27">
      <id>25</id>
      <latitude>75.0</latitude>
      <longitude>20.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="28">
      <id>26</id>
      <latitude>15.0</latitude>
      <longitude>21.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="29">
      <id>27</id>
      <latitude>90.0</latitude>
      <longitude>45.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="30">
      <id>28</id>
      <latitude>15.0</latitude>
      <longitude>0.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="31">
      <id>29</id>
      <latitude>31.0</latitude>
      <longitude>26.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="32">
      <id>30</id>
      <latitude>10.0</latitude>
      <longitude>95.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="33">
      <id>31</id>
      <latitude>6.0</latitude>
      <longitude>6.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="34">
      <id>32</id>
      <latitude>51.0</latitude>
      <longitude>5.0</longitude>
    </VrpAirLocation>
    <VrpAirLocation id="35">
      <id>33</id>
      <latitude>26.0</latitude>
      <longitude>36.0</longitude>
    </VrpAirLocation>
  </locationList>
  <depotList id="36">
    <VrpDepot id="37">
      <id>1</id>
      <location class="VrpAirLocation" reference="3"/>
    </VrpDepot>
  </depotList>
  <vehicleList id="38">
    <VrpVehicle id="39">
      <id>0</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="40">
        <id>33</id>
        <location class="VrpAirLocation" reference="35"/>
        <demand>22</demand>
        <previousStandstill class="VrpVehicle" reference="39"/>
        <nextCustomer id="41">
          <id>11</id>
          <location class="VrpAirLocation" reference="13"/>
          <demand>1</demand>
          <previousStandstill class="VrpCustomer" reference="40"/>
          <nextCustomer id="42">
            <id>12</id>
            <location class="VrpAirLocation" reference="14"/>
            <demand>21</demand>
            <previousStandstill class="VrpCustomer" reference="41"/>
            <nextCustomer id="43">
              <id>30</id>
              <location class="VrpAirLocation" reference="32"/>
              <demand>22</demand>
              <previousStandstill class="VrpCustomer" reference="42"/>
              <nextCustomer id="44">
                <id>18</id>
                <location class="VrpAirLocation" reference="20"/>
                <demand>17</demand>
                <previousStandstill class="VrpCustomer" reference="43"/>
                <vehicle reference="39"/>
              </nextCustomer>
              <vehicle reference="39"/>
            </nextCustomer>
            <vehicle reference="39"/>
          </nextCustomer>
          <vehicle reference="39"/>
        </nextCustomer>
        <vehicle reference="39"/>
      </nextCustomer>
    </VrpVehicle>
    <VrpVehicle id="45">
      <id>1</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="46">
        <id>14</id>
        <location class="VrpAirLocation" reference="16"/>
        <demand>25</demand>
        <previousStandstill class="VrpVehicle" reference="45"/>
        <nextCustomer id="47">
          <id>8</id>
          <location class="VrpAirLocation" reference="10"/>
          <demand>15</demand>
          <previousStandstill class="VrpCustomer" reference="46"/>
          <nextCustomer id="48">
            <id>19</id>
            <location class="VrpAirLocation" reference="21"/>
            <demand>17</demand>
            <previousStandstill class="VrpCustomer" reference="47"/>
            <nextCustomer id="49">
              <id>2</id>
              <location class="VrpAirLocation" reference="4"/>
              <demand>26</demand>
              <previousStandstill class="VrpCustomer" reference="48"/>
              <nextCustomer id="50">
                <id>15</id>
                <location class="VrpAirLocation" reference="17"/>
                <demand>16</demand>
                <previousStandstill class="VrpCustomer" reference="49"/>
                <vehicle reference="45"/>
              </nextCustomer>
              <vehicle reference="45"/>
            </nextCustomer>
            <vehicle reference="45"/>
          </nextCustomer>
          <vehicle reference="45"/>
        </nextCustomer>
        <vehicle reference="45"/>
      </nextCustomer>
    </VrpVehicle>
    <VrpVehicle id="51">
      <id>2</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="52">
        <id>13</id>
        <location class="VrpAirLocation" reference="15"/>
        <demand>66</demand>
        <previousStandstill class="VrpVehicle" reference="51"/>
        <nextCustomer id="53">
          <id>22</id>
          <location class="VrpAirLocation" reference="24"/>
          <demand>25</demand>
          <previousStandstill class="VrpCustomer" reference="52"/>
          <vehicle reference="51"/>
        </nextCustomer>
        <vehicle reference="51"/>
      </nextCustomer>
    </VrpVehicle>
    <VrpVehicle id="54">
      <id>3</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="55">
        <id>29</id>
        <location class="VrpAirLocation" reference="31"/>
        <demand>21</demand>
        <previousStandstill class="VrpVehicle" reference="54"/>
        <nextCustomer id="56">
          <id>28</id>
          <location class="VrpAirLocation" reference="30"/>
          <demand>11</demand>
          <previousStandstill class="VrpCustomer" reference="55"/>
          <nextCustomer id="57">
            <id>31</id>
            <location class="VrpAirLocation" reference="33"/>
            <demand>25</demand>
            <previousStandstill class="VrpCustomer" reference="56"/>
            <nextCustomer id="58">
              <id>17</id>
              <location class="VrpAirLocation" reference="19"/>
              <demand>7</demand>
              <previousStandstill class="VrpCustomer" reference="57"/>
              <nextCustomer id="59">
                <id>26</id>
                <location class="VrpAirLocation" reference="28"/>
                <demand>11</demand>
                <previousStandstill class="VrpCustomer" reference="58"/>
                <vehicle reference="54"/>
              </nextCustomer>
              <vehicle reference="54"/>
            </nextCustomer>
            <vehicle reference="54"/>
          </nextCustomer>
          <vehicle reference="54"/>
        </nextCustomer>
        <vehicle reference="54"/>
      </nextCustomer>
    </VrpVehicle>
    <VrpVehicle id="60">
      <id>4</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="61">
        <id>32</id>
        <location class="VrpAirLocation" reference="34"/>
        <demand>2</demand>
        <previousStandstill class="VrpVehicle" reference="60"/>
        <nextCustomer id="62">
          <id>24</id>
          <location class="VrpAirLocation" reference="26"/>
          <demand>7</demand>
          <previousStandstill class="VrpCustomer" reference="61"/>
          <nextCustomer id="63">
            <id>25</id>
            <location class="VrpAirLocation" reference="27"/>
            <demand>21</demand>
            <previousStandstill class="VrpCustomer" reference="62"/>
            <nextCustomer id="64">
              <id>23</id>
              <location class="VrpAirLocation" reference="25"/>
              <demand>16</demand>
              <previousStandstill class="VrpCustomer" reference="63"/>
              <nextCustomer id="65">
                <id>27</id>
                <location class="VrpAirLocation" reference="29"/>
                <demand>21</demand>
                <previousStandstill class="VrpCustomer" reference="64"/>
                <nextCustomer id="66">
                  <id>5</id>
                  <location class="VrpAirLocation" reference="7"/>
                  <demand>15</demand>
                  <previousStandstill class="VrpCustomer" reference="65"/>
                  <nextCustomer id="67">
                    <id>9</id>
                    <location class="VrpAirLocation" reference="11"/>
                    <demand>16</demand>
                    <previousStandstill class="VrpCustomer" reference="66"/>
                    <vehicle reference="60"/>
                  </nextCustomer>
                  <vehicle reference="60"/>
                </nextCustomer>
                <vehicle reference="60"/>
              </nextCustomer>
              <vehicle reference="60"/>
            </nextCustomer>
            <vehicle reference="60"/>
          </nextCustomer>
          <vehicle reference="60"/>
        </nextCustomer>
        <vehicle reference="60"/>
      </nextCustomer>
    </VrpVehicle>
    <VrpVehicle id="68">
      <id>5</id>
      <capacity>100</capacity>
      <depot reference="37"/>
      <nextCustomer id="69">
        <id>7</id>
        <location class="VrpAirLocation" reference="9"/>
        <demand>5</demand>
        <previousStandstill class="VrpVehicle" reference="68"/>
        <nextCustomer id="70">
          <id>20</id>
          <location class="VrpAirLocation" reference="22"/>
          <demand>22</demand>
          <previousStandstill class="VrpCustomer" reference="69"/>
          <nextCustomer id="71">
            <id>21</id>
            <location class="VrpAirLocation" reference="23"/>
            <demand>10</demand>
            <previousStandstill class="VrpCustomer" reference="70"/>
            <nextCustomer id="72">
              <id>10</id>
              <location class="VrpAirLocation" reference="12"/>
              <demand>17</demand>
              <previousStandstill class="VrpCustomer" reference="71"/>
              <nextCustomer id="73">
                <id>16</id>
                <location class="VrpAirLocation" reference="18"/>
                <demand>11</demand>
                <previousStandstill class="VrpCustomer" reference="72"/>
                <nextCustomer id="74">
                  <id>3</id>
                  <location class="VrpAirLocation" reference="5"/>
                  <demand>17</demand>
                  <previousStandstill class="VrpCustomer" reference="73"/>
                  <nextCustomer id="75">
                    <id>4</id>
                    <location class="VrpAirLocation" reference="6"/>
                    <demand>6</demand>
                    <previousStandstill class="VrpCustomer" reference="74"/>
                    <nextCustomer id="76">
                      <id>6</id>
                      <location class="VrpAirLocation" reference="8"/>
                      <demand>7</demand>
                      <previousStandstill class="VrpCustomer" reference="75"/>
                      <vehicle reference="68"/>
                    </nextCustomer>
                    <vehicle reference="68"/>
                  </nextCustomer>
                  <vehicle reference="68"/>
                </nextCustomer>
                <vehicle reference="68"/>
              </nextCustomer>
              <vehicle reference="68"/>
            </nextCustomer>
            <vehicle reference="68"/>
          </nextCustomer>
          <vehicle reference="68"/>
        </nextCustomer>
        <vehicle reference="68"/>
      </nextCustomer>
    </VrpVehicle>
  </vehicleList>
  <customerList id="77">
    <VrpCustomer reference="49"/>
    <VrpCustomer reference="74"/>
    <VrpCustomer reference="75"/>
    <VrpCustomer reference="66"/>
    <VrpCustomer reference="76"/>
    <VrpCustomer reference="69"/>
    <VrpCustomer reference="47"/>
    <VrpCustomer reference="67"/>
    <VrpCustomer reference="72"/>
    <VrpCustomer reference="41"/>
    <VrpCustomer reference="42"/>
    <VrpCustomer reference="52"/>
    <VrpCustomer reference="46"/>
    <VrpCustomer reference="50"/>
    <VrpCustomer reference="73"/>
    <VrpCustomer reference="58"/>
    <VrpCustomer reference="44"/>
    <VrpCustomer reference="48"/>
    <VrpCustomer reference="70"/>
    <VrpCustomer reference="71"/>
    <VrpCustomer reference="53"/>
    <VrpCustomer reference="64"/>
    <VrpCustomer reference="62"/>
    <VrpCustomer reference="63"/>
    <VrpCustomer reference="59"/>
    <VrpCustomer reference="65"/>
    <VrpCustomer reference="56"/>
    <VrpCustomer reference="55"/>
    <VrpCustomer reference="43"/>
    <VrpCustomer reference="57"/>
    <VrpCustomer reference="61"/>
    <VrpCustomer reference="40"/>
  </customerList>
  <score id="78">0hard/-743441soft</score>
</VrpVehicleRoutingSolution>

Here is what i tried:

library(XML)
library(plyr)
xmlfile <- xmlTreeParse("test.xml")
class(xmlfile)
topxml <- xmlRoot(xmlfile)
topxml <- xmlSApply(topxml,
                   function(x) xmlSApply(x, xmlValue))
xml_df <- data.frame(t(topxml),
                    row.names=NULL)

Given the plethora of resources just on SO regarding how to target elements via XPath and the non-insignificant # of questions involving converting semi-complex XML to a rectangular structure in R, what else did you look for and found insufficient to help? — hrbrmstr, Dec 15 '16 at 12:17
In the above XML document there is a node - "vehicleList" which doesn't follow a particular pattern and I am new to XML. — Venkatesh Chintha, Dec 16 '16 at 09:43

score 0 · Accepted Answer · edited May 23 '17 at 12:08

Try this and modify it as appropriate depending on what is wanted. (The question asks for the node called VehicleList but there is no such node in the data and sample output was not provided in the question.) Note that this answer regarding translating a data frame to XML is very similar.

library(XML)

doc <- xmlTreeParse("test.xml", useInternalNodes = TRUE)

do.call("rbind", xpathApply(doc, "//locationList/VrpAirLocation", function(node) {
  data.frame(locationListId = xmlAttrs(xmlParent(node))[["id"]],
             vrpLocationId = xmlAttrs(node)[["id"]],
             id = xmlValue(node[["id"]]), 
             latitude = xmlValue(node[["latitude"]]), 
             longitude = xmlValue(node[["longitude"]]))
}))

giving (first few rows only shown here):

  locationListId vrpLocationId id latitude longitude
1              2             3  1     34.0      31.0
2              2             4  2     45.0      55.0
3              2             5  3     70.0      80.0
4              2             6  4     81.0      70.0
5              2             7  5     85.0      61.0
6              2             8  6     59.0      55.0

Thanks for the reply @Grothendieck, it was very helpful. There is node called vehicleList similar to the locationList which you have used in the solution. — Venkatesh Chintha, Dec 16 '16 at 08:58

XML data to R dataframes

1 Answers1