CODEWITHSUNDEEP
pythonpandas
Pythonuser
Pythonuser

Reputation: 213

appending multiple pandas DataFrames read in from files

Hello I am trying to read in multiple files, create a dataframe of the specific key information i need and then append each dataframe for each file to a main dataframe called topics. I have tried the following code.

import pandas as pd
import numpy as np
from lxml import etree
import os

topics = pd.DataFrame()
for filename in os.listdir('./topics'):
    if not filename.startswith('.'):
        #print(filename)
        tree = etree.parse('./topics/'+filename)
        root = tree.getroot() 
        childA = []
        elementT = []
        ElementA = []
        for child in root:
            elementT.append(str(child.tag))
            ElementA.append(str(child.attrib))
            childA.append(str(child.attrib))
            for element in child:
                elementT.append(str(element.tag))
                #childA.append(child.attrib)
                ElementA.append(str(element.attrib))
                childA.append(str(child.attrib))
                for sub in element:
                    #print('***', child.attrib , ':' , element.tag, ':' , element.attrib, '***')
                    #childA.append(child.attrib)
                    elementT.append(str(sub.tag))
                    ElementA.append(str(sub.attrib))
                    childA.append(str(child.attrib))

        df = pd.DataFrame()
        df['c'] = np.array (childA)
        df['t'] = np.array(ElementA)
        df['a'] = np.array(elementT)

        file = df['t'].str.extract(r'([A-Z][A-Z].*[words.xml])#')
        start = df['t'].str.extract(r'words([0-9]+)')
        stop = df['t'].str.extract(r'.*words([0-9]+)')
        tags = df['a'].str.extract(r'.*([topic]|[pointer]|[child])')
        rootTopic = df['c'].str.extract(r'rdhillon.(\d+)')
        df['f'] = file
        df['start'] = start
        df['stop'] = stop
        df['tags'] = tags
        # c= topic
        # r = pointerr
        # d= child
        df['topicID'] = rootTopic

        df = df.iloc[:,3:]
        topics.append(df)

However when i call topics i get the following output

topics
Out[19]:_

Can someone please let me know where i am going wrong, also any suggestions on improving my messy code would be appreciated

Upvotes: 1

Views: 987

Answers (1)

ALollz
ALollz

Reputation: 59549

Unlike lists, when you append to a DataFrame you return a new object. So topics.append(df) returns an object that you are never storing anywhere and topics remains the empty DataFrame you declare on the 6th line. You can fix this by

# Append is deprecated so will need to `concat` to join
#topics = topics.append(df)

topics = pd.concat([topics, df])

However, appending to a DataFrame within a loop is a very costly exercise. Instead you should append each DataFrame to a list within the loop and call pd.concat() on the list of DataFrames after the loop.

import pandas as pd

topics_list = []
for filename in os.listdir('./topics'):
    # All of your code
    topics_list.append(df) # Lists are modified with append

# After the loop one call to concat
topics = pd.concat(topics_list)

Upvotes: 1

Related Questions