How to convert XML to table?

Question

I'm taking an XML string from a URL. This part works fine.

REQUEST_URL = 'https://URL'
response = requests.get(REQUEST_URL, auth=(login, password))
xml_data = response.text.encode('utf-8', 'ignore') 
tree = ET.parse(xml_data)
root = tree.getroot()

print(response.text) gives me:

<?xml version='1.0' standalone='yes'?><Report Type='SLA Report'
 SiteName='Execute Query'
 SLA_Name='Execute Query'
 SLA_Description='Execute Query'
 From='2018-11-27 00:00'
 Thru='2018-11-27 23:59' 
 obj_device='4500'
 locations='69,31,'
>
<Objective Type='Availability'>
<Goal>99.93</Goal>
<Actual>99.93</Actual>
<Compliant>Yes</Compliant>
<Errors>2</Errors>
<Checks>2878</Checks>
</Objective>
<Objective Type='Uptime'>
<Goal></Goal>
<Actual></Actual>
<Compliant></Compliant>
<Errors>0</Errors>
<Checks>0</Checks>
</Objective>
<Objective Type='Response Time'>
<Goal>300.00</Goal>
<Actual>3.1164</Actual>
<Compliant>Yes</Compliant>
<Errors>0</Errors>
<Checks>2878</Checks>
</Objective>
<MonitoringPeriods>
<Monitor>
<Exclude>No</Exclude><DayFrom>Sunday</DayFrom><TimeFrom>00:00</TimeFrom><DayThru>Sunday</DayThru><TimeThru>23:59</TimeThru>
</Monitor>

I'd like to get the data into a table so it's easier to work with. How can I do this with Python 3.x? When I import it into Excel, it looks great.

It may be something like this:

for sla in root.findall('Objective'):
    goal = sla.find('Goal').text
    actual = sla.find('Actual').text
    compliant = sla.find('Compliant').text
    errors = sla.find('Errors').text
    checks = sla.find('Checks').text
    print('Goal:', goal, 'Actual:', actual, 'Compliant:', compliant, 'Errors:', errors, 'Checks:', checks)

But I want to load each data point into a data frame, not print each data point. How can I do the same thing using Python? TIA.

I think you're looking for the Pandas library. See this answer: [https://stackoverflow.com/a/45815394/10358386](https://stackoverflow.com/a/45815394/10358386) — octopathTraveller, Nov 29 '18 at 15:02
That's somewhat helpful, but I would like to load some items, but not all, into a data frame. I'm really looking for something that gives me a little more control over which elements I'm loading into a data frame. Thanks. — ASH, Nov 29 '18 at 16:33
I know excel works perfect to convert xml to table very well. but there are no on-hands method to do this in pandas/ython. one possible way is to calc the level and then finish the convert. Any geek to code this? — Pengju Zhao, Mar 13 '20 at 11:56

score 1 · Accepted Answer · answered Nov 29 '18 at 16:39

Sould print this:

# importing csv module 
import csv 

# csv file name 
filename = "aapl.csv"

# initializing the titles and rows list 
fields = [] 
rows = [] 

# reading csv file 
with open(filename, 'r') as csvfile: 
    # creating a csv reader object 
    csvreader = csv.reader(csvfile) 

    # extracting field names through first row 
    fields = csvreader.next() 

    # extracting each data row one by one 
    for row in csvreader: 
        rows.append(row) 

    # get total number of rows 
    print("Total no. of rows: %d"%(csvreader.line_num)) 

# printing the field names 
print('Field names are:' + ', '.join(field for field in fields)) 

# printing first 5 rows 
print('\nFirst 5 rows are:\n') 
for row in rows[:5]: 
    # parsing each column of a row 
    for col in row: 
        print("%10s"%col), 
    print('\n')

Source: https://www.geeksforgeeks.org/working-csv-files-python/

How to convert XML to table?

1 Answers1