Converting XML data to a single dataframe

HDJ · September 24, 2020, 11:17am

Dear RStudio community,

I recently started to try and learn R programming to help with manipulating xml data files for my work.
At my work we have a testsystem which creates data that looks like this example:

<?xml version="1.0" encoding="Windows-1252"?>
<?xml-stylesheet type="text/xsl" href="DataLog.xsl"?>
<R>
 <Info Version="1.0" />
 <ST NMP="test" OP="Administrator" TS="Capacitive" SD="16-06-2020 08:42:45" ED="16-06-2020 08:44:29">
<BI BCP="Reference-1" ID="0" TR="1" AK="0" SD="16-06-2020 08:43:48" TT="10" NT="84" NF="21">
      <TEST F="BEGIN" C="" SG1="" SG2="" PD1="" PD2="" NM="BEGIN_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEBEGIN" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_BEGIN_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEREAD" C="26" SG1="B1P1" SG2="" PD1="B1P1" PD2="" NM="B1P1" TR="1" MU="pF" ML="18.8936" MM="20.9" MH="22.9064" MR="44.5022" MP="112.93%" />
      <TEST F="CAPACITIVEREAD" C="54" SG1="B5P4" SG2="" PD1="B5P4" PD2="" NM="B5P4" TR="0" MU="pF" ML="11.502" MM="13.5" MH="15.498" MR="13.8026" MP="2.24%" />
      <TEST F="CAPACITIVEREAD" C="41" SG1="B1P6" SG2="" PD1="B1P6" PD2="" NM="B1P6" TR="0" MU="pF" ML="80.01" MM="90" MH="99.99" MR="90.6007" MP="0.67%" />
      <TEST F="CAPACITIVEREAD" C="14" SG1="B5P9" SG2="" PD1="B5P9" PD2="" NM="B5P9" TR="0" MU="pF" ML="19.7944" MM="21.8" MH="23.8056" MR="22.1846" MP="1.76%" />
      <TEST F="CAPACITIVEREAD" C="51" SG1="B1P2" SG2="" PD1="B1P2" PD2="" NM="B1P2" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="26.1571" MP="5.05%" />
      <TEST F="CAPACITIVEREAD" C="24" SG1="B5P5" SG2="" PD1="B5P5" PD2="" NM="B5P5" TR="0" MU="pF" ML="6.7077" MM="8.7" MH="10.6923" MR="8.99205" MP="3.36%" />
      <TEST F="CAPACITIVEREAD" C="11" SG1="B1P7" SG2="" PD1="B1P7" PD2="" NM="B1P7" TR="0" MU="pF" ML="3.1161" MM="5.1" MH="7.0839" MR="5.22272" MP="2.41%" />
      <TEST F="CAPACITIVEREAD" C="39" SG1="B5P10" SG2="" PD1="B5P10" PD2="" NM="B5P10" TR="0" MU="pF" ML="18.09" MM="20.1" MH="22.11" MR="20.4858" MP="1.92%" />
      <TEST F="CAPACITIVEREAD" C="53" SG1="B2P2" SG2="" PD1="B2P2" PD2="" NM="B2P2" TR="0" MU="pF" ML="22.7912" MM="24.8" MH="26.8088" MR="25.9174" MP="4.51%" />
      <TEST F="CAPACITIVEREAD" C="49" SG1="B5P6" SG2="" PD1="B5P6" PD2="" NM="B5P6" TR="0" MU="pF" ML="80.545" MM="90.5" MH="100.455" MR="91.4817" MP="1.08%" />
      <TEST F="CAPACITIVEREAD" C="38" SG1="B2P8" SG2="" PD1="B2P8" PD2="" NM="B2P8" TR="0" MU="pF" ML="8.802" MM="10.8" MH="12.798" MR="10.8546" MP="0.51%" />
      <TEST F="CAPACITIVEREAD" C="9" SG1="B5P11" SG2="" PD1="B5P11" PD2="" NM="B5P11" TR="0" MU="pF" ML="22.7912" MM="24.8" MH="26.8088" MR="25.1793" MP="1.53%" />
      <TEST F="CAPACITIVEREAD" C="28" SG1="B2P1" SG2="" PD1="B2P1" PD2="" NM="B2P1" TR="1" MU="pF" ML="19.005" MM="21" MH="22.995" MR="41.4924" MP="97.58%" />
      <TEST F="CAPACITIVEREAD" C="52" SG1="B4P4" SG2="" PD1="B4P4" PD2="" NM="B4P4" TR="0" MU="pF" ML="11.4034" MM="13.4" MH="15.3966" MR="13.7575" MP="2.67%" />
      <TEST F="CAPACITIVEREAD" C="13" SG1="B2P7" SG2="" PD1="B2P7" PD2="" NM="B2P7" TR="0" MU="pF" ML="3.0957" MM="5.1" MH="7.1043" MR="5.1916" MP="1.80%" />
      <TEST F="CAPACITIVEREAD" C="12" SG1="B4P9" SG2="" PD1="B4P9" PD2="" NM="B4P9" TR="0" MU="pF" ML="19.7036" MM="21.7" MH="23.6964" MR="22.4055" MP="3.25%" />
      <TEST F="CAPACITIVEREAD" C="30" SG1="B3P1" SG2="" PD1="B3P1" PD2="" NM="B3P1" TR="1" MU="pF" ML="18.8032" MM="20.8" MH="22.7968" MR="63.625" MP="205.89%" />
      <TEST F="CAPACITIVEREAD" C="22" SG1="B4P5" SG2="" PD1="B4P5" PD2="" NM="B4P5" TR="0" MU="pF" ML="6.699" MM="8.7" MH="10.701" MR="8.92121" MP="2.54%" />
      <TEST F="CAPACITIVEREAD" C="43" SG1="B2P6" SG2="" PD1="B2P6" PD2="" NM="B2P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="90.6285" MP="0.03%" />
      <TEST F="CAPACITIVEREAD" C="37" SG1="B4P10" SG2="" PD1="B4P10" PD2="" NM="B4P10" TR="0" MU="pF" ML="18" MM="20" MH="22" MR="20.5939" MP="2.97%" />
      <TEST F="CAPACITIVEREAD" C="55" SG1="B3P2" SG2="" PD1="B3P2" PD2="" NM="B3P2" TR="1" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="62.9871" MP="152.96%" />
      <TEST F="CAPACITIVEREAD" C="20" SG1="B3P5" SG2="" PD1="B3P5" PD2="" NM="B3P5" TR="0" MU="pF" ML="7.011" MM="9" MH="10.989" MR="9.24221" MP="2.69%" />
      <TEST F="CAPACITIVEREAD" C="45" SG1="B3P6" SG2="" PD1="B3P6" PD2="" NM="B3P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="90.9269" MP="0.36%" />
      <TEST F="CAPACITIVEREAD" C="7" SG1="B4P11" SG2="" PD1="B4P11" PD2="" NM="B4P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.221" MP="1.29%" />
      <TEST F="CAPACITIVEREAD" C="25" SG1="B3P3" SG2="" PD1="B3P3" PD2="" NM="B3P3" TR="1" MU="pF" ML="21.2048" MM="23.2" MH="25.1952" MR="53.1915" MP="129.27%" />
      <TEST F="CAPACITIVEREAD" C="50" SG1="B3P4" SG2="" PD1="B3P4" PD2="" NM="B3P4" TR="0" MU="pF" ML="11.6008" MM="13.6" MH="15.5992" MR="14.1046" MP="3.71%" />
      <TEST F="CAPACITIVEREAD" C="15" SG1="B3P7" SG2="" PD1="B3P7" PD2="" NM="B3P7" TR="0" MU="pF" ML="3.3858" MM="5.4" MH="7.4142" MR="5.45848" MP="1.08%" />
      <TEST F="CAPACITIVEREAD" C="5" SG1="B3P11" SG2="" PD1="B3P11" PD2="" NM="B3P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.406" MP="2.03%" />
      <TEST F="CAPACITIVEREAD" C="27" SG1="B4P3" SG2="" PD1="B4P3" PD2="" NM="B4P3" TR="1" MU="pF" ML="20.999" MM="23" MH="25.001" MR="64.468" MP="180.30%" />
      <TEST F="CAPACITIVEREAD" C="23" SG1="B2P3" SG2="" PD1="B2P3" PD2="" NM="B2P3" TR="1" MU="pF" ML="21.1134" MM="23.1" MH="25.0866" MR="42.2409" MP="82.86%" />
      <TEST F="CAPACITIVEREAD" C="40" SG1="B3P8" SG2="" PD1="B3P8" PD2="" NM="B3P8" TR="0" MU="pF" ML="9.009" MM="11" MH="12.991" MR="10.9911" MP="-0.08%" />
      <TEST F="CAPACITIVEREAD" C="35" SG1="B3P10" SG2="" PD1="B3P10" PD2="" NM="B3P10" TR="1" MU="pF" ML="18.2002" MM="20.2" MH="22.1998" MR="35.572" MP="76.10%" />
      <TEST F="CAPACITIVEREAD" C="1" SG1="B4P2" SG2="" PD1="B4P2" PD2="" NM="B4P2" TR="1" MU="pF" ML="22.6993" MM="24.7" MH="26.7007" MR="64.8456" MP="162.53%" />
      <TEST F="CAPACITIVEREAD" C="48" SG1="B2P4" SG2="" PD1="B2P4" PD2="" NM="B2P4" TR="0" MU="pF" ML="11.6008" MM="13.6" MH="15.5992" MR="13.6961" MP="0.71%" />
      <TEST F="CAPACITIVEREAD" C="42" SG1="B4P8" SG2="" PD1="B4P8" PD2="" NM="B4P8" TR="0" MU="pF" ML="8.7098" MM="10.7" MH="12.6902" MR="10.9078" MP="1.94%" />
      <TEST F="CAPACITIVEREAD" C="10" SG1="B3P9" SG2="" PD1="B3P9" PD2="" NM="B3P9" TR="1" MU="pF" ML="19.7944" MM="21.8" MH="23.8056" MR="36.3264" MP="66.63%" />
      <TEST F="CAPACITIVEREAD" C="31" SG1="B4P1" SG2="" PD1="B4P1" PD2="" NM="B4P1" TR="1" MU="pF" ML="18.8936" MM="20.9" MH="22.9064" MR="65.1385" MP="211.67%" />
      <TEST F="CAPACITIVEREAD" C="18" SG1="B2P5" SG2="" PD1="B2P5" PD2="" NM="B2P5" TR="0" MU="pF" ML="6.7077" MM="8.7" MH="10.6923" MR="8.93025" MP="2.65%" />
      <TEST F="CAPACITIVEREAD" C="17" SG1="B4P7" SG2="" PD1="B4P7" PD2="" NM="B4P7" TR="0" MU="pF" ML="3.02" MM="5" MH="6.98" MR="5.19226" MP="3.85%" />
      <TEST F="CAPACITIVEREAD" C="8" SG1="B2P9" SG2="" PD1="B2P9" PD2="" NM="B2P9" TR="0" MU="pF" ML="19.998" MM="22" MH="24.002" MR="22.2283" MP="1.04%" />
      <TEST F="CAPACITIVEREAD" C="33" SG1="B5P1" SG2="" PD1="B5P1" PD2="" NM="B5P1" TR="0" MU="pF" ML="19.0955" MM="21.1" MH="23.1045" MR="21.4756" MP="1.78%" />
      <TEST F="CAPACITIVEREAD" C="16" SG1="B1P5" SG2="" PD1="B1P5" PD2="" NM="B1P5" TR="0" MU="pF" ML="6.8024" MM="8.8" MH="10.7976" MR="8.9329" MP="1.51%" />
      <TEST F="CAPACITIVEREAD" C="47" SG1="B4P6" SG2="" PD1="B4P6" PD2="" NM="B4P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="90.3059" MP="-0.32%" />
      <TEST F="CAPACITIVEREAD" C="34" SG1="B2P10" SG2="" PD1="B2P10" PD2="" NM="B2P10" TR="0" MU="pF" ML="18.2002" MM="20.2" MH="22.1998" MR="20.4294" MP="1.14%" />
      <TEST F="CAPACITIVEREAD" C="3" SG1="B5P2" SG2="" PD1="B5P2" PD2="" NM="B5P2" TR="0" MU="pF" ML="23" MM="25" MH="27" MR="25.5223" MP="2.09%" />
      <TEST F="CAPACITIVEREAD" C="46" SG1="B1P4" SG2="" PD1="B1P4" PD2="" NM="B1P4" TR="0" MU="pF" ML="11.6998" MM="13.7" MH="15.7002" MR="13.8816" MP="1.33%" />
      <TEST F="CAPACITIVEREAD" C="19" SG1="B5P7" SG2="" PD1="B5P7" PD2="" NM="B5P7" TR="0" MU="pF" ML="3.0906" MM="5.1" MH="7.1094" MR="5.18879" MP="1.74%" />
      <TEST F="CAPACITIVEREAD" C="4" SG1="B2P11" SG2="" PD1="B2P11" PD2="" NM="B2P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.3644" MP="1.87%" />
      <TEST F="CAPACITIVEREAD" C="29" SG1="B5P3" SG2="" PD1="B5P3" PD2="" NM="B5P3" TR="0" MU="pF" ML="21.2962" MM="23.3" MH="25.3038" MR="23.9127" MP="2.63%" />
      <TEST F="CAPACITIVEREAD" C="21" SG1="B1P3" SG2="" PD1="B1P3" PD2="" NM="B1P3" TR="1" MU="pF" ML="21.2962" MM="23.3" MH="25.3038" MR="44.4744" MP="90.88%" />
      <TEST F="CAPACITIVEREAD" C="44" SG1="B5P8" SG2="" PD1="B5P8" PD2="" NM="B5P8" TR="0" MU="pF" ML="8.7098" MM="10.7" MH="12.6902" MR="11.0848" MP="3.60%" />
      <TEST F="CAPACITIVEREAD" C="2" SG1="B1P11" SG2="" PD1="B1P11" PD2="" NM="B1P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.5021" MP="2.42%" />
      <TEST F="CAPACITIVEREAD" C="32" SG1="B1P10" SG2="" PD1="B1P10" PD2="" NM="B1P10" TR="0" MU="pF" ML="18.09" MM="20.1" MH="22.11" MR="20.648" MP="2.73%" />
      <TEST F="CAPACITIVEREAD" C="6" SG1="B1P9" SG2="" PD1="B1P9" PD2="" NM="B1P9" TR="0" MU="pF" ML="19.9071" MM="21.9" MH="23.8929" MR="22.6727" MP="3.53%" />
      <TEST F="CAPACITIVEREAD" C="36" SG1="B1P8" SG2="" PD1="B1P8" PD2="" NM="B1P8" TR="0" MU="pF" ML="8.8944" MM="10.9" MH="12.9056" MR="11.0974" MP="1.81%" />
      <TEST F="CAPACITIVEPARSE" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_PARSE_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEISO" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_ISO_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="ISOLATION" C="5-35" SG1="B3P11" SG2="B3P10" PD1="B3P11" PD2="B3P10" NM="ISO CHN 5 - 35" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="5-10" SG1="B3P11" SG2="B3P9" PD1="B3P11" PD2="B3P9" NM="ISO CHN 5 - 10" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="10-35" SG1="B3P9" SG2="B3P10" PD1="B3P9" PD2="B3P10" NM="ISO CHN 10 - 35" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908493" MP="-99.09%" />
      <TEST F="ISOLATION" C="35-5" SG1="B3P10" SG2="B3P11" PD1="B3P10" PD2="B3P11" NM="ISO CHN 35 - 5" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="534.712" MP="434.71%" />
      <TEST F="ISOLATION" C="40-10" SG1="B3P8" SG2="B3P9" PD1="B3P8" PD2="B3P9" NM="ISO CHN 40 - 10" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="599.53" MP="499.53%" />
      <TEST F="ISOLATION" C="25-50" SG1="B3P3" SG2="B3P4" PD1="B3P3" PD2="B3P4" NM="ISO CHN 25 - 50" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="55-25" SG1="B3P2" SG2="B3P3" PD1="B3P2" PD2="B3P3" NM="ISO CHN 55 - 25" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908296" MP="-99.09%" />
      <TEST F="ISOLATION" C="30-25" SG1="B3P1" SG2="B3P3" PD1="B3P1" PD2="B3P3" NM="ISO CHN 30 - 25" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.907814" MP="-99.09%" />
      <TEST F="ISOLATION" C="30-55" SG1="B3P1" SG2="B3P2" PD1="B3P1" PD2="B3P2" NM="ISO CHN 30 - 55" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908625" MP="-99.09%" />
      <TEST F="ISOLATION" C="55-45" SG1="B3P2" SG2="B3P6" PD1="B3P2" PD2="B3P6" NM="ISO CHN 55 - 45" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="1-47" SG1="B4P2" SG2="B4P6" PD1="B4P2" PD2="B4P6" NM="ISO CHN 1 - 47" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="27-52" SG1="B4P3" SG2="B4P4" PD1="B4P3" PD2="B4P4" NM="ISO CHN 27 - 52" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="393.325" MP="293.32%" />
      <TEST F="ISOLATION" C="1-27" SG1="B4P2" SG2="B4P3" PD1="B4P2" PD2="B4P3" NM="ISO CHN 1 - 27" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908882" MP="-99.09%" />
      <TEST F="ISOLATION" C="31-27" SG1="B4P1" SG2="B4P3" PD1="B4P1" PD2="B4P3" NM="ISO CHN 31 - 27" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908519" MP="-99.09%" />
      <TEST F="ISOLATION" C="31-1" SG1="B4P1" SG2="B4P2" PD1="B4P1" PD2="B4P2" NM="ISO CHN 31 - 1" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908387" MP="-99.09%" />
      <TEST F="ISOLATION" C="28-23" SG1="B2P1" SG2="B2P3" PD1="B2P1" PD2="B2P3" NM="ISO CHN 28 - 23" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908078" MP="-99.09%" />
      <TEST F="ISOLATION" C="28-53" SG1="B2P1" SG2="B2P2" PD1="B2P1" PD2="B2P2" NM="ISO CHN 28 - 53" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="53-23" SG1="B2P2" SG2="B2P3" PD1="B2P2" PD2="B2P3" NM="ISO CHN 53 - 23" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="23-48" SG1="B2P3" SG2="B2P4" PD1="B2P3" PD2="B2P4" NM="ISO CHN 23 - 48" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="1193.35" MP="1093.35%" />
      <TEST F="ISOLATION" C="21-46" SG1="B1P3" SG2="B1P4" PD1="B1P3" PD2="B1P4" NM="ISO CHN 21 - 46" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="51-21" SG1="B1P2" SG2="B1P3" PD1="B1P2" PD2="B1P3" NM="ISO CHN 51 - 21" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="26-51" SG1="B1P1" SG2="B1P2" PD1="B1P1" PD2="B1P2" NM="ISO CHN 26 - 51" TR="0" MU="MOhm" ML="100" MM="100" MH="120" MR="100000" MP="99900.00%" />
      <TEST F="ISOLATION" C="26-21" SG1="B1P1" SG2="B1P3" PD1="B1P1" PD2="B1P3" NM="ISO CHN 26 - 21" TR="1" MU="MOhm" ML="100" MM="100" MH="120" MR="0.908577" MP="-99.09%" />
      <TEST F="CAPACITIVEEND" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_END_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="END" C="" SG1="" SG2="" PD1="" PD2="" NM="END_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
</BI>
<BI BCP="Reference-1" ID="1" TR="0" AK="0" SD="16-06-2020 08:43:58" TT="4" NT="61" NF="0">
      <TEST F="BEGIN" C="" SG1="" SG2="" PD1="" PD2="" NM="BEGIN_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEBEGIN" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_BEGIN_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEREAD" C="26" SG1="B1P1" SG2="" PD1="B1P1" PD2="" NM="B1P1" TR="0" MU="pF" ML="18.8936" MM="20.9" MH="22.9064" MR="21.4625" MP="2.69%" />
      <TEST F="CAPACITIVEREAD" C="54" SG1="B5P4" SG2="" PD1="B5P4" PD2="" NM="B5P4" TR="0" MU="pF" ML="11.502" MM="13.5" MH="15.498" MR="13.848" MP="2.58%" />
      <TEST F="CAPACITIVEREAD" C="41" SG1="B1P6" SG2="" PD1="B1P6" PD2="" NM="B1P6" TR="0" MU="pF" ML="80.01" MM="90" MH="99.99" MR="91.2566" MP="1.40%" />
      <TEST F="CAPACITIVEREAD" C="14" SG1="B5P9" SG2="" PD1="B5P9" PD2="" NM="B5P9" TR="0" MU="pF" ML="19.7944" MM="21.8" MH="23.8056" MR="22.3499" MP="2.52%" />
      <TEST F="CAPACITIVEREAD" C="51" SG1="B1P2" SG2="" PD1="B1P2" PD2="" NM="B1P2" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.2664" MP="1.47%" />
      <TEST F="CAPACITIVEREAD" C="24" SG1="B5P5" SG2="" PD1="B5P5" PD2="" NM="B5P5" TR="0" MU="pF" ML="6.7077" MM="8.7" MH="10.6923" MR="8.98765" MP="3.31%" />
      <TEST F="CAPACITIVEREAD" C="11" SG1="B1P7" SG2="" PD1="B1P7" PD2="" NM="B1P7" TR="0" MU="pF" ML="3.1161" MM="5.1" MH="7.0839" MR="5.21725" MP="2.30%" />
      <TEST F="CAPACITIVEREAD" C="39" SG1="B5P10" SG2="" PD1="B5P10" PD2="" NM="B5P10" TR="0" MU="pF" ML="18.09" MM="20.1" MH="22.11" MR="20.5001" MP="1.99%" />
      <TEST F="CAPACITIVEREAD" C="53" SG1="B2P2" SG2="" PD1="B2P2" PD2="" NM="B2P2" TR="0" MU="pF" ML="22.7912" MM="24.8" MH="26.8088" MR="25.119" MP="1.29%" />
      <TEST F="CAPACITIVEREAD" C="49" SG1="B5P6" SG2="" PD1="B5P6" PD2="" NM="B5P6" TR="0" MU="pF" ML="80.545" MM="90.5" MH="100.455" MR="92.9085" MP="2.66%" />
      <TEST F="CAPACITIVEREAD" C="38" SG1="B2P8" SG2="" PD1="B2P8" PD2="" NM="B2P8" TR="0" MU="pF" ML="8.802" MM="10.8" MH="12.798" MR="10.9513" MP="1.40%" />
      <TEST F="CAPACITIVEREAD" C="9" SG1="B5P11" SG2="" PD1="B5P11" PD2="" NM="B5P11" TR="0" MU="pF" ML="22.7912" MM="24.8" MH="26.8088" MR="25.0304" MP="0.93%" />
      <TEST F="CAPACITIVEREAD" C="28" SG1="B2P1" SG2="" PD1="B2P1" PD2="" NM="B2P1" TR="0" MU="pF" ML="19.005" MM="21" MH="22.995" MR="21.0794" MP="0.38%" />
      <TEST F="CAPACITIVEREAD" C="52" SG1="B4P4" SG2="" PD1="B4P4" PD2="" NM="B4P4" TR="0" MU="pF" ML="11.4034" MM="13.4" MH="15.3966" MR="13.8374" MP="3.26%" />
      <TEST F="CAPACITIVEREAD" C="13" SG1="B2P7" SG2="" PD1="B2P7" PD2="" NM="B2P7" TR="0" MU="pF" ML="3.0957" MM="5.1" MH="7.1043" MR="5.18898" MP="1.74%" />
      <TEST F="CAPACITIVEREAD" C="12" SG1="B4P9" SG2="" PD1="B4P9" PD2="" NM="B4P9" TR="0" MU="pF" ML="19.7036" MM="21.7" MH="23.6964" MR="22.307" MP="2.80%" />
      <TEST F="CAPACITIVEREAD" C="30" SG1="B3P1" SG2="" PD1="B3P1" PD2="" NM="B3P1" TR="0" MU="pF" ML="18.8032" MM="20.8" MH="22.7968" MR="21.1284" MP="1.58%" />
      <TEST F="CAPACITIVEREAD" C="22" SG1="B4P5" SG2="" PD1="B4P5" PD2="" NM="B4P5" TR="0" MU="pF" ML="6.699" MM="8.7" MH="10.701" MR="8.95891" MP="2.98%" />
      <TEST F="CAPACITIVEREAD" C="43" SG1="B2P6" SG2="" PD1="B2P6" PD2="" NM="B2P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="91.3539" MP="0.83%" />
      <TEST F="CAPACITIVEREAD" C="37" SG1="B4P10" SG2="" PD1="B4P10" PD2="" NM="B4P10" TR="0" MU="pF" ML="18" MM="20" MH="22" MR="20.3095" MP="1.55%" />
      <TEST F="CAPACITIVEREAD" C="55" SG1="B3P2" SG2="" PD1="B3P2" PD2="" NM="B3P2" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.4207" MP="2.09%" />
      <TEST F="CAPACITIVEREAD" C="20" SG1="B3P5" SG2="" PD1="B3P5" PD2="" NM="B3P5" TR="0" MU="pF" ML="7.011" MM="9" MH="10.989" MR="9.24636" MP="2.74%" />
      <TEST F="CAPACITIVEREAD" C="45" SG1="B3P6" SG2="" PD1="B3P6" PD2="" NM="B3P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="91.6414" MP="1.15%" />
      <TEST F="CAPACITIVEREAD" C="7" SG1="B4P11" SG2="" PD1="B4P11" PD2="" NM="B4P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.3916" MP="1.97%" />
      <TEST F="CAPACITIVEREAD" C="25" SG1="B3P3" SG2="" PD1="B3P3" PD2="" NM="B3P3" TR="0" MU="pF" ML="21.2048" MM="23.2" MH="25.1952" MR="23.7232" MP="2.26%" />
      <TEST F="CAPACITIVEREAD" C="50" SG1="B3P4" SG2="" PD1="B3P4" PD2="" NM="B3P4" TR="0" MU="pF" ML="11.6008" MM="13.6" MH="15.5992" MR="14.1484" MP="4.03%" />
      <TEST F="CAPACITIVEREAD" C="15" SG1="B3P7" SG2="" PD1="B3P7" PD2="" NM="B3P7" TR="0" MU="pF" ML="3.3858" MM="5.4" MH="7.4142" MR="5.47973" MP="1.48%" />
      <TEST F="CAPACITIVEREAD" C="5" SG1="B3P11" SG2="" PD1="B3P11" PD2="" NM="B3P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.1742" MP="1.10%" />
      <TEST F="CAPACITIVEREAD" C="27" SG1="B4P3" SG2="" PD1="B4P3" PD2="" NM="B4P3" TR="0" MU="pF" ML="20.999" MM="23" MH="25.001" MR="23.4688" MP="2.04%" />
      <TEST F="CAPACITIVEREAD" C="23" SG1="B2P3" SG2="" PD1="B2P3" PD2="" NM="B2P3" TR="0" MU="pF" ML="21.1134" MM="23.1" MH="25.0866" MR="23.4083" MP="1.33%" />
      <TEST F="CAPACITIVEREAD" C="40" SG1="B3P8" SG2="" PD1="B3P8" PD2="" NM="B3P8" TR="0" MU="pF" ML="9.009" MM="11" MH="12.991" MR="11.1903" MP="1.73%" />
      <TEST F="CAPACITIVEREAD" C="35" SG1="B3P10" SG2="" PD1="B3P10" PD2="" NM="B3P10" TR="0" MU="pF" ML="18.2002" MM="20.2" MH="22.1998" MR="20.441" MP="1.19%" />
      <TEST F="CAPACITIVEREAD" C="1" SG1="B4P2" SG2="" PD1="B4P2" PD2="" NM="B4P2" TR="0" MU="pF" ML="22.6993" MM="24.7" MH="26.7007" MR="25.1958" MP="2.01%" />
      <TEST F="CAPACITIVEREAD" C="48" SG1="B2P4" SG2="" PD1="B2P4" PD2="" NM="B2P4" TR="0" MU="pF" ML="11.6008" MM="13.6" MH="15.5992" MR="13.7358" MP="1.00%" />
      <TEST F="CAPACITIVEREAD" C="42" SG1="B4P8" SG2="" PD1="B4P8" PD2="" NM="B4P8" TR="0" MU="pF" ML="8.7098" MM="10.7" MH="12.6902" MR="11.146" MP="4.17%" />
      <TEST F="CAPACITIVEREAD" C="10" SG1="B3P9" SG2="" PD1="B3P9" PD2="" NM="B3P9" TR="0" MU="pF" ML="19.7944" MM="21.8" MH="23.8056" MR="22.0955" MP="1.36%" />
      <TEST F="CAPACITIVEREAD" C="31" SG1="B4P1" SG2="" PD1="B4P1" PD2="" NM="B4P1" TR="0" MU="pF" ML="18.8936" MM="20.9" MH="22.9064" MR="21.2994" MP="1.91%" />
      <TEST F="CAPACITIVEREAD" C="18" SG1="B2P5" SG2="" PD1="B2P5" PD2="" NM="B2P5" TR="0" MU="pF" ML="6.7077" MM="8.7" MH="10.6923" MR="8.93579" MP="2.71%" />
      <TEST F="CAPACITIVEREAD" C="17" SG1="B4P7" SG2="" PD1="B4P7" PD2="" NM="B4P7" TR="0" MU="pF" ML="3.02" MM="5" MH="6.98" MR="5.22961" MP="4.59%" />
      <TEST F="CAPACITIVEREAD" C="8" SG1="B2P9" SG2="" PD1="B2P9" PD2="" NM="B2P9" TR="0" MU="pF" ML="19.998" MM="22" MH="24.002" MR="22.0717" MP="0.33%" />
      <TEST F="CAPACITIVEREAD" C="33" SG1="B5P1" SG2="" PD1="B5P1" PD2="" NM="B5P1" TR="0" MU="pF" ML="19.0955" MM="21.1" MH="23.1045" MR="21.2925" MP="0.91%" />
      <TEST F="CAPACITIVEREAD" C="16" SG1="B1P5" SG2="" PD1="B1P5" PD2="" NM="B1P5" TR="0" MU="pF" ML="6.8024" MM="8.8" MH="10.7976" MR="8.96169" MP="1.84%" />
      <TEST F="CAPACITIVEREAD" C="47" SG1="B4P6" SG2="" PD1="B4P6" PD2="" NM="B4P6" TR="0" MU="pF" ML="80.634" MM="90.6" MH="100.566" MR="92.2402" MP="1.81%" />
      <TEST F="CAPACITIVEREAD" C="34" SG1="B2P10" SG2="" PD1="B2P10" PD2="" NM="B2P10" TR="0" MU="pF" ML="18.2002" MM="20.2" MH="22.1998" MR="20.1582" MP="-0.21%" />
      <TEST F="CAPACITIVEREAD" C="3" SG1="B5P2" SG2="" PD1="B5P2" PD2="" NM="B5P2" TR="0" MU="pF" ML="23" MM="25" MH="27" MR="25.0649" MP="0.26%" />
      <TEST F="CAPACITIVEREAD" C="46" SG1="B1P4" SG2="" PD1="B1P4" PD2="" NM="B1P4" TR="0" MU="pF" ML="11.6998" MM="13.7" MH="15.7002" MR="13.8843" MP="1.35%" />
      <TEST F="CAPACITIVEREAD" C="19" SG1="B5P7" SG2="" PD1="B5P7" PD2="" NM="B5P7" TR="0" MU="pF" ML="3.0906" MM="5.1" MH="7.1094" MR="5.15806" MP="1.14%" />
      <TEST F="CAPACITIVEREAD" C="4" SG1="B2P11" SG2="" PD1="B2P11" PD2="" NM="B2P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.0532" MP="0.62%" />
      <TEST F="CAPACITIVEREAD" C="29" SG1="B5P3" SG2="" PD1="B5P3" PD2="" NM="B5P3" TR="0" MU="pF" ML="21.2962" MM="23.3" MH="25.3038" MR="23.3452" MP="0.19%" />
      <TEST F="CAPACITIVEREAD" C="21" SG1="B1P3" SG2="" PD1="B1P3" PD2="" NM="B1P3" TR="0" MU="pF" ML="21.2962" MM="23.3" MH="25.3038" MR="23.6901" MP="1.67%" />
      <TEST F="CAPACITIVEREAD" C="44" SG1="B5P8" SG2="" PD1="B5P8" PD2="" NM="B5P8" TR="0" MU="pF" ML="8.7098" MM="10.7" MH="12.6902" MR="11.0876" MP="3.62%" />
      <TEST F="CAPACITIVEREAD" C="2" SG1="B1P11" SG2="" PD1="B1P11" PD2="" NM="B1P11" TR="0" MU="pF" ML="22.908" MM="24.9" MH="26.892" MR="25.2768" MP="1.51%" />
      <TEST F="CAPACITIVEREAD" C="32" SG1="B1P10" SG2="" PD1="B1P10" PD2="" NM="B1P10" TR="0" MU="pF" ML="18.09" MM="20.1" MH="22.11" MR="20.6072" MP="2.52%" />
      <TEST F="CAPACITIVEREAD" C="6" SG1="B1P9" SG2="" PD1="B1P9" PD2="" NM="B1P9" TR="0" MU="pF" ML="19.9071" MM="21.9" MH="23.8929" MR="22.5652" MP="3.04%" />
      <TEST F="CAPACITIVEREAD" C="36" SG1="B1P8" SG2="" PD1="B1P8" PD2="" NM="B1P8" TR="0" MU="pF" ML="8.8944" MM="10.9" MH="12.9056" MR="11.1325" MP="2.13%" />
      <TEST F="CAPACITIVEPARSE" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_PARSE_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEISO" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_ISO_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="CAPACITIVEEND" C="" SG1="" SG2="" PD1="" PD2="" NM="CAP_END_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
      <TEST F="END" C="" SG1="" SG2="" PD1="" PD2="" NM="END_COMP" TR="0" MU="V" ML="-10" MM="0" MH="10" MR="0" MP="0.00%" />
    </BI>
</ST>
</R>

I would like to have the data in a single dataframe so that I can manipulate it from there.
So far my code looks like this:

# Clear environment
rm(list = ls()) 

# Clear console
cat("\014")  # ctrl+L

library("XML")
library("methods")

xmlfile=xmlParse("Reference.xml")

xml.df.ST <- XML:::xmlAttrsToDataFrame(xmlfile["//ST"])
xml.df.BI <- XML:::xmlAttrsToDataFrame(xmlfile["//BI"])
xml.df.TEST <- XML:::xmlAttrsToDataFrame(xmlfile["//TEST"])

View(xml.df.ST)
View(xml.df.BI)
View(xml.df.TEST)

^{Created on 2020-09-24 by the reprex package (v0.3.0)}

This gives me three dataframes but I do not know how to combine them.

If I import the xml file into excel and save it as an excel file which I then import into a dataframe in R i have no problem with getting the data as a single dataframe.
My issue with that solution is that the amount of data can become quite large (>200Mb) which excel isn't too happy to handle.

Your help with this topic is much appreciated.

francisbarton · September 25, 2020, 9:11pm

Assuming the data frames are compatible, you can use rbind/cbind or dplyr's bind_rows or bind_cols to combine your three outputs back into a single df.

pieterjanvc · September 29, 2020, 11:38am

Hi,

Welcome to the RStudio community!

I have made the assumptions, based-off the XLM structure, that there is only one ST level in your data, and one BI per TEST. With that in mind, here is my approach:

library(XML)
library(dplyr)

xmlfile = xmlParse("myData.xml")

xml.df.ST <- XML:::xmlAttrsToDataFrame(xmlfile["//ST"])
xml.df.BI <- XML:::xmlAttrsToDataFrame(xmlfile["//BI"])

#Get the TEST data frames in separate lists
xml.df.TEST <- lapply(xmlfile["//BI"], XML:::xmlAttrsToDataFrame)

#Add the IDs of xml.df.BI to each data frame
xml.df.TEST = mapply(function(df, id) {
  df$ID = id
  return(df)
}, df = xml.df.TEST, id = xml.df.BI$ID, SIMPLIFY = F)
#Now merge them
xml.df.TEST = do.call(rbind, xml.df.TEST)
  
#Join TEST with BI by ID
result = left_join(xml.df.BI, xml.df.TEST, by = "ID")
#Add all ST data to every row
result = cbind(xml.df.ST, result)

This should scale well for many BI (TEST) datasets in your data and should look similar to the output excel generates. I have been looking for a long time trying to find a known package or function that could do this, as it seems so basic (if Excel can do it...) but no luck hence this

Hope this helps,
PJ

system · October 20, 2020, 11:38am

This topic was automatically closed 21 days after the last reply. New replies are no longer allowed.

If you have a query related to it or one of the replies, start a new topic and refer back with a link.