How to remove new lines in dataframe while writing xml into it?

Admin 1
Viewed 74

I'm trying to write xml response from an api, iterating over tag TrackingEvent and saving into a dataframe:

The xml response looks like this :



<?xml version="1.0" encoding="UTF-8"?>
<TrackingResult>
   <Events>
      <TrackingEvent>
         <DateTimeStamp>202010</DateTimeStamp>
         <Event>Delivered</Event>
         <ExtraInfo>02921</ExtraInfo>
      </TrackingEvent>
      <TrackingEvent>
         <DateTimeStamp>202010</DateTimeStamp>
         <Event>Delivery today</Event>
         <ExtraInfo>31916</ExtraInfo>
      </TrackingEvent>
   </Events>
   <Signatures />
   <Errors />
</TrackingResult>

I am writing this into a dataframe using this code :

response_status = requests.get(url, headers = headers)
print(type(response_status)) #<class 'requests.models.Response'>
print(type(response_status.content)) #<class 'bytes'>

tree = ET.fromstring(response_status.content)
for child in tree.iter('TrackingEvent'):
    for elem in child.iter():
         data = {str(elem.tag):[elem.text]}
         if str(elem.text)=='None' :continue
         response_df = pd.DataFrame(data)
         consolidated_list.append(response_df) 
consolidated_df = pd.concat(consolidated_list,ignore_index=True)
print(consolidated_df)

This is the output dataframe i'm getting :

DateTimeStamp            Event              ExtraInfo
202010
                        Delivered            
                                             02921
202010
                        Delivery today
                                             31916

I want to remove these empty spaces to put one child iteration into a single dataframe row

expected output:

DateTimeStamp            Event              ExtraInfo
202010                   Delivered          02921
202010                   Delivery today     31916
pandas dataframe python-3.x xml python
1 Answers

I would recommend to build a dict in a loop and then create dataframe on basis of that dict. Here is an example:

xml = '''<?xml version="1.0" ?>
<TrackingResult>
   <Events>
      <TrackingEvent>
         <DateTimeStamp>202010</DateTimeStamp>
         <Event>Delivered</Event>
         <ExtraInfo>02921</ExtraInfo>
      </TrackingEvent>
      <TrackingEvent>
         <DateTimeStamp>202010</DateTimeStamp>
         <Event>Delivery today</Event>
         <ExtraInfo>31916</ExtraInfo>
      </TrackingEvent>
   </Events>
   <Signatures />
   <Errors />
</TrackingResult>'''

from lxml import etree as ET
import pandas as pd
from collections import defaultdict

d = defaultdict(list)

tree = ET.fromstring(xml)
for child in tree.iter('TrackingEvent'):
    for elem in child.iter():
        if (elem.text is not None and str(elem.text).strip() != ''):
            d[elem.tag].append(elem.text.strip())
        else:
            if len(list(elem)) == 0:
                d[elem.tag].append(None)

df = pd.DataFrame(d)

print(df)

Output:

  DateTimeStamp           Event ExtraInfo
0        202010       Delivered     02921
1        202010  Delivery today     31916
user_141686230
Related