I have an XML file from which I want to extract data and put it into a Google BigQuery table using Python.

Hi,

Using the code below, I created a DataFrame like this:

        col                    value

0 ID 1
1 Name John
2 Start_Date 01-01-2011
0 ID 2
1 Name Doe
2 Start_Date 03-04-2011

I want to convert this into something like below to insert it into a Google BigQuery table:

ID Name Start_Date
1 John 01-01-2011
2 Doe 03-04-2011

CODE

import os
import pandas as pd
import xml.etree.ElementTree as ET

xml_data = 'Test.xml'

tree = ET.parse(xml_data) 

root = tree.getroot()

lstcol = []
lstValue = []

for child in root[1].iter():
    #print(child.get('name'), child.text)
    if child.get('name') != None :
        lstcol.append(child.get('name'))
        lstValue.append(child.text)
 

df = pd.DataFrame({'col' : lstcol, 'value' : lstValue})

print(df)

I’m new to python so any help would be greatly appreciated. Thanks