This is related to Python XML to CSV help for novice. Thank you to @aneroid for helping me get started.
I'm able to get my columns and rows out, and have two remaining issues that I don't know what to search for to find answers.
Remaining Issue 1: My XML has up to 20 values for some attributes. I want to list these in a single comma-separated string. See "Years" in my example below.
I found some things on grouping, but I don't think that will work since I'm only getting the first value out of my XML. I need to list all of the matching values from the XML. Plus my full report has 120 columns, so would I need to list all the columns to group by?
(UPDATE START) I researched lxml.etree and can now get this:
['2019', '2020']
I've updated the Python below. If anyone can help with the homestretch of getting this to just 2019, 2020, that's be awesome. Issue 2 is similar - just need to pull values from the dictionary. (UPDATE END)
Remaining Issue 2: The XML lists related things by name, and then lists them again as separate "row"s with additional attributes for the things. I need to include a value of one of those additional attributes in the report. In my sample Python, I create a second dataframe, called thing_df that includes the names and the ID attribute. I need to match the thing name in the coll_df to the thing name in the thing_df to get its thing_id and add that to the coll_df.
I have found some things on merging datasets, but those examples seem to be for merging what I call collections in my example, and not for what I'm looking for.
Desired output:
,Collection item,ITEM-ID,ATTRIB-1,PERSON-TYPE-1-NAME,ATTRIB-2,PERSON-TYPE-2-NAME,RELATED-THING-1 id,RELATED-THING-2 IDs,Years
0,name of Item 1,item_000001,Yes,name of person 1,Yes,name of person 2,thing_000745,"thing_000783, thing_000803","2019, 2020"
Python (UPDATED):
# -*- coding: utf-8 -*-
# Importing the required libraries
#import xml.etree.ElementTree as Xet
import lxml.etree as Xet
import pandas as pd
#Define main collection columns and rows for dataframe
coll_cols = ["Collection item", "ITEM-ID", "ATTRIB-1", "PERSON-TYPE-1-NAME" ,
"ATTRIB-2", "PERSON-TYPE-2-NAME", "RELATED-THING-1 id",
"RELATED-THING-2 IDs", "Years"]
coll_rows = []
#Define thing lookup dataframe columns and rows
thing_cols = ["Thing Name", "Thing ID"]
thing_rows = []
# Parsing the XML file
xmlparse = Xet.parse('sample.xml')
root = xmlparse.getroot()
for row in root:
# Create thing lookup dataframe
if (
row.findtext('type') == "THING-TYPE-1" or
row.findtext('type') == "THING-TYPE-2"
):
thing_id = row.findtext("THING-ID")
thing_name = row.findtext("name")
thing_rows.append({"Thing Name": thing_name,
"Thing ID": thing_id})
thing_df = pd.DataFrame(thing_rows, columns=thing_cols)
# Find only collection items
if row.findtext('type') != "COLLECTION-ITEM":
continue
# Define values for collection item dataframe
name = row.findtext("name", "Missing name")
item_id = row.findtext("ITEM-ID", "Missing item ID")
attrib_1 = row.findtext("ATTRIB-1", "Missing attribute 1")
p1_name = row.findtext("./PERSON-TYPE-1-NAME/result/row/name")
attrib_2 = row.findtext("ATTRIB-2", "Missing attribute 2")
p2_name = row.findtext("./PERSON-TYPE-2-NAME/result/row/name")
relat_thing1 = row.xpath("./RELATED-THING-1/result/row/name/text()")
#relat_thing1_id = look up relat_thing1 in infr_df as "Thing Name" \
# and return "Thing ID"
relat_thing2 = row.xpath("./RELATED-THING-2/result/row/name/text()")
#relat_thing2_id = look up every relat_thing2 in infr_df as "Thing Name" \
# and return all "Thing ID"
years = row.xpath("./RPTD-HIST-CODE/result/row/name/text()")
coll_rows.append({"Collection item": name,
"ITEM-ID": item_id,
"ATTRIB-1": attrib_1,
"PERSON-TYPE-1-NAME": p1_name,
"ATTRIB-2": attrib_2,
"PERSON-TYPE-2-NAME": p2_name,
#"RELATED-THING-1 id": relat_thing1_id,
#"RELATED-THING-2 IDs": relat_thing2_ids,
"Years": years
})
coll_df = pd.DataFrame(coll_rows, columns=coll_cols)
# Writing dataframe to csv
coll_df.to_csv('output.csv')