title: Create a dataframe from nested xml and generate a csv
body: <p>I have an XML file like this:</p>
  
  <pre><code>&lt;?xml version="1.0"?&gt;
  &lt;PropertySet&gt;
      &lt;PropertySet NumOutputObjects="1" &gt;
          &lt;Message IntObjectName="Class Def" MessageType="Integration Object"&gt;
              &lt;ListOf_Class_Def&gt;
                  &lt;ImpExp Type="CLASS_DEF" Name="lp_pkg_cla" Object_Num="1001p"&gt;
                      &lt;ListOfObject_Def&gt;
                          &lt;Object_Def Ancestor_Num="" Ancestor_Name=""&gt;
                          &lt;/Object_Def&gt;
                      &lt;/ListOfObject_Def&gt;
                      &lt;ListOfObject_Arrt&gt;
                          &lt;Object_Arrt Orig_Id="6666p" Attr_Name="LP_Portable"&gt;
                          &lt;/Object_Arrt&gt;
                      &lt;/ListOfObject_Arrt&gt;
                  &lt;/ImpExp&gt;
              &lt;/ListOf_Class_Def&gt;
          &lt;/Message&gt;
      &lt;/PropertySet&gt;
      &lt;PropertySet NumOutputObjects="1" &gt;
          &lt;Message IntObjectName="Class Def" MessageType="Integration Object"&gt;
              &lt;ListOf_Class_Def&gt;
                  &lt;ImpExp Type="CLASS_DEF" Name="M_pkg_cla" Object_Num="1023i"&gt;
                      &lt;ListOfObject_Def&gt;
                          &lt;Object_Def Ancestor_Num="" Ancestor_Name=""&gt;
                          &lt;/Object_Def&gt;
                      &lt;/ListOfObject_Def&gt;
                      &lt;ListOfObject_Arrt&gt;
                          &lt;Object_Arrt Orig_Id="7010p" Attr_Name="O_Portable"&gt;
                          &lt;/Object_Arrt&gt;
                          &lt;Object_Arrt Orig_Id="7012j" Attr_Name="O_wireless"&gt;
                          &lt;/Object_Arrt&gt;
                      &lt;/ListOfObject_Arrt&gt;
                  &lt;/ImpExp&gt;
              &lt;/ListOf_Class_Def&gt;
          &lt;/Message&gt;
      &lt;/PropertySet&gt;
      &lt;PropertySet NumOutputObjects="1" &gt;
          &lt;Message IntObjectName="Prod Def" MessageType="Integration Object"&gt;
              &lt;ListOf_Prod_Def&gt;
                  &lt;ImpExp Type="PROD_DEF" Name="Laptop" Object_Num="2008a"&gt;
                      &lt;ListOfObject_Def&gt;
                          &lt;Object_Def Ancestor_Num="1001p" Ancestor_Name="lp_pkg_cla"&gt;
                          &lt;/Object_Def&gt;
                      &lt;/ListOfObject_Def&gt;
                      &lt;ListOfObject_Arrt&gt;
                      &lt;/ListOfObject_Arrt&gt;
                  &lt;/ImpExp&gt;
              &lt;/ListOf_Prod_Def&gt;
          &lt;/Message&gt;
      &lt;/PropertySet&gt;
      &lt;PropertySet NumOutputObjects="1" &gt;
          &lt;Message IntObjectName="Prod Def" MessageType="Integration Object"&gt;
              &lt;ListOf_Prod_Def&gt;
                  &lt;ImpExp Type="PROD_DEF" Name="Mouse" Object_Num="2987d"&gt;
                      &lt;ListOfObject_Def&gt;
                          &lt;Object_Def Ancestor_Num="1023i" Ancestor_Name="M_pkg_cla"&gt;
                          &lt;/Object_Def&gt;
                      &lt;/ListOfObject_Def&gt;
                      &lt;ListOfObject_Arrt&gt;
                      &lt;/ListOfObject_Arrt&gt;
                  &lt;/ImpExp&gt;
              &lt;/ListOf_Prod_Def&gt;
          &lt;/Message&gt;
      &lt;/PropertySet&gt;
      &lt;PropertySet NumOutputObjects="1" &gt;
          &lt;Message IntObjectName="Prod Def" MessageType="Integration Object"&gt;
              &lt;ListOf_Prod_Def&gt;
                  &lt;ImpExp Type="PROD_DEF" Name="Speaker" Object_Num="5463g"&gt;
                      &lt;ListOfObject_Def&gt;
                          &lt;Object_Def Ancestor_Num="" Ancestor_Name=""&gt;
                          &lt;/Object_Def&gt;
                      &lt;/ListOfObject_Def&gt;
                      &lt;ListOfObject_Arrt&gt;
                      &lt;/ListOfObject_Arrt&gt;
                  &lt;/ImpExp&gt;
              &lt;/ListOf_Prod_Def&gt;
          &lt;/Message&gt;
      &lt;/PropertySet&gt;
  &lt;/PropertySet&gt;
  </code></pre>
  
  <p>I am hoping to extract the <code>Name</code>, <code>Object_Num</code>, <code>Orig_Id</code> and <code>Attr_Name</code> tags from it using Python and convert them into a .csv format.</p>
  
  <p>The .csv format I'd like to see it in is simply:</p>
  
  <pre class="lang-none prettyprint-override"><code>ProductId   Product AttributeId Attribute
  2008a   Laptop  6666p           LP_Portable
  2987d   Mouse   7010p           O_Portable
  2987d   Mouse   7012p           O_Wireless
  5463g   Speaker ""          ""
  </code></pre>
  
  <p>Actually there is a relationship like this in xml tags:</p>
  
  <ol>
  <li>All products are in the tags, "ImpExp Type="PROD_DEF".. "  </li>
  <li>All attributes are in the tags, "ImpExp Type="CLASS_DEF".. "</li>
  <li><p>If a product has attributes, then there is a tag<br>
  <code>&lt;Object_Def Ancestor_Num="1023i".. &gt;</code></p></li>
  <li><p>The <code>Ancestor_Num</code> is equal to <code>Object_Num</code> in tags, 
  <code>Type="CLASS_DEF"..</code></p></li>
  </ol>
  
  <p>I have tried this:</p>
  
  <pre><code>from lxml import etree
  import pandas
  import HTMLParser 
  
  inFile = "./newm.xml"
  outFile = "./new.csv"
  
  ctx1 = etree.iterparse(inFile, tag=("ImpExp", "ListOfObject_Def", "ListOfObject_Arrt",))
  
  
  hp = HTMLParser.HTMLParser()
  csvData = []
  csvData1 = []
  csvData2 = []
  csvData3 = []
  csvData4 = []
  csvData5 = []
  
  for event, elem in ctx1:
      value1 = elem.get("Type")
      value2 = elem.get("Name")
      value3 = elem.get("Object_Num")
      value4 = elem.get("Ancestor_Num")
      value5 = elem.get("Orig_Id")
      value6 = elem.get("Attr_Name")
      if value1 == "PROD_DEF":
          csvData.append(value2)
          csvData1.append(value3)
          for event, elem in ctx1:
              if value4 is not None:
                  csvData2.append(value4)
                  elem.clear()
  
  df = pandas.DataFrame({'Product':csvData, 'ProductId':csvData1, 'AncestorId':csvData2})
  
  for event, elem in ctx1: 
      if value1 == "Class Def":
          csvData3.append(value3)
          csvData4.append(value5)
          csvData5.append(value6)
          elem.clear()
  
  df1 = pandas.DataFrame({'AncestorId':csvData3, 'AttribId':csvData4, 'AttribName':csvData5})
  
  dff = pandas.merge(df, df1, on="AncestorId")
  dff.to_csv(outFile, index = False)
  </code></pre>