title: How to convert an XML file to pandas dataframe?
body: <p>I cannot get XML to a python dataframe</p>
  
  <p>Could you please help me to parse XML to python dataframe?
  I can't seem to get it to work
  This is how far I got to:</p>
  
  <pre class="lang-py prettyprint-override"><code>import xmltodict 
  import pandas as pd
  import requests
  from bs4 import BeautifulSoup
   def get_xml():
      url="http://energywatch.natgrid.co.uk/EDP-PublicUI/PublicPI/InstantaneousFlowWebService.asmx"
      headers = {'content-type': 'application/soap+xml; charset=utf-8'}
      body ="""&lt;soap12:Envelope xmlns:xsi="http://www.w3.org/2001/XMLSchema-instance" xmlns:xsd="http://www.w3.org/2001/XMLSchema" xmlns:soap12="http://www.w3.org/2003/05/soap-envelope"&gt;
                  &lt;soap12:Body&gt;
                  &lt;GetInstantaneousFlowData xmlns="http://www.NationalGrid.com/EDP/UI/" /&gt;
                  &lt;/soap12:Body&gt;
                  &lt;/soap12:Envelope&gt;"""
  
      response = requests.post(url,data=body,headers=headers)
      return response.content
  
  response = get_xml()
  soup = BeautifulSoup(response, 'lxml')
  table_columns = []
  for item in soup.find_all(['EDPObjectName'.lower()]):
      table_columns.append(item.text)
  table_columns=pd.DataFrame(table_columns)
  table_rows=[]
  for item in soup.find_all(['applicableat']):
      table_rows.append(item.text) 
  df1=pd.DataFrame(table_rows).drop_duplicates() 
  #df1=pd.to_datetime(df1)
  table=[]
  for item in soup.find_all(['flowrate']):
      table.append(item.text) 
  df=pd.DataFrame(table)
   df_final=pd.DataFrame(df, columns=table_columns, index=df1)
  </code></pre>
  
  <p>This is the result I am looking for:</p>
  
  <pre><code>                    ALDBROUGH   AVONMOUTH   BACTON BBL  …
      2019-08-08T13:00:00 0       1.23    5.1         …
      2019-08-08T13:02:00 0       1.23    5.1         …
      2019-08-08T13:04:00 0       3.23    5.1         …
      2019-08-08T13:06:00 0       3.23    5.1         …
      2019-08-08T13:08:00 0       3.23    5.23            …
      2019-08-08T13:10:00 0       4.23    5.204           …
  </code></pre>