I have used the code below in the Anaconda/pandas environment to create dataframes from multiple xml files and then began analyzing the data:
"""Thanks to Roberto Preste Author From XML to Pandas dataframes,
from xtree ... to return out_df
https://medium.com/@robertopreste/from-xml-to-pandas-dataframes-9292980b1c1c
"""
def parse_XML(xml_file, df_cols):
xtree = et.parse(xml_file)
xroot = xtree.getroot()
rows = []
for node in xroot:
res = []
res.append(node.attrib.get(df_cols[0]))
for el in df_cols[1:]:
if node is not None and node.find(el) is not None:
res.append(node.find(el).text)
else:
res.append(None)
rows.append({df_cols[i]: res[i]
for i, _ in enumerate(df_cols)})
out_df = pd.DataFrame(rows, columns=df_cols)
return out_df
As a means of keeping the data organized by year I want to add a column to the resulting individual dfs that gives the name of the file from which each year's data was parsed. I have seen many examples of how to add a column for filename when reading data from a csv file- but almost none with regard to how to add a filename column when parsing data from xml. Using some of the other examples I've found I have tried what I have shown below, replacing the csv with xml in the appropriate places in the code.
# Make the names of xml files read in a column in the resulting dataframe
import glob
import os.path
# Create a list of all XML files
files = glob.glob("*.xml")
# Create an empty list to append the df
filenames = []
for xml in files:
df = pd.read_xml(xml)
df['file name'] = os.path.basename(xml)
filenames.append(df)
path = r'xml_in'
allFiles = glob.glob(path + '/*.xml')
for file_ in allFiles:
df = pd.read_xml(file_, header=0)
df.name = file_
print(df.name)
This results in a variable called "files" of type list with all the correct filenames from the directory in the list but doesn't make all the desired dataframes as I had hoped. The only dataframe it creates correctly is the one for the filename in the directory that falls last alphabetically and that variable is called "df" which I realize is part of the problem from using an example meant to read csv and not xml- but the other dataframes I attempt to create are all filled with "None" in each cell even though the number of rows and columns I expect to be parsed in are correct based on previous running of the code I had prior to trying to add a filename column.
How can I parse xml and add a column for a filename for all the files in the directory?