Add a column for filename when data is parsed from multiple xml files to individual pandas Data Frames

Viewed 40

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?

0 Answers
Related