Using Regex + BeautifulSoup to scrape XML and store to Pandas

user_6367971 0
Viewed 140

I'm scraping some xml sites using beautifulSoup, and then storing the scraped data to a dataframe. The XML is usually consistently-formatted so the scraping works fine. But maybe 15% of the time, the data doesn't get saved to the dataframe because one of the prefixes is slightly different.

For example, when scraping these three URLs, the 2nd and 3rd ones gets stored to the dataframe without issue, whereas the 1st one doesn't. 

from bs4 import BeautifulSoup
import requests
import pandas as pd

session = requests.Session()

# urls to loop through
form_urls = ['https://www.sec.gov/Archives/edgar/data/1418814/000141881220000017/vac13f021420.xml',
             'https://www.sec.gov/Archives/edgar/data/820124/000095012320003895/408.xml',
             'https://www.sec.gov/Archives/edgar/data/1067983/000095012320002466/form13fInfoTable.xml']

# Create dataframe and set columns to match XML doc
cols = ['nameOfIssuer', 'titleOfClass', 'cusip', 'value', 'sshPrnamt',
        'sshPrnamtType', 'putCall', 'investmentDiscretion',
        'otherManager', 'Sole', 'Shared', 'None']

res_df = pd.DataFrame(columns=cols)


# Iterate over URLs
for form_url in form_urls:
    data = []
    soup = BeautifulSoup(session.get(form_url).content, 'lxml')
    print(soup)

    for info_table in soup.find_all(['ns1:infotable', 'infotable']):
        row = []
        for col in cols:
            d = info_table.find([col.lower(), 'ns1:' + col.lower()])
            row.append(d.text.strip() if d else 'NaN')
        data.append(row)
    url_df = pd.DataFrame(data, columns=cols)
    res_df = res_df.append(url_df, ignore_index=True)

print(res_df)

So how can I make the scraper more flexible in case the prefix is in an unexpected format (e.g., maybe it's an empty string or some other combination of uppercase and lower case letters and numbers)?

pandas beautifulsoup python regex
2 Answers

First link you provided has second row as n1:infoTable not as ns1:infoTable, so for your code to work you need to take that to account.

from bs4 import BeautifulSoup
import requests
import pandas as pd
import re


session = requests.Session()

# urls to loop through
form_urls = ['https://www.sec.gov/Archives/edgar/data/1418814/000141881220000017/vac13f021420.xml',
             'https://www.sec.gov/Archives/edgar/data/820124/000095012320003895/408.xml',
             'https://www.sec.gov/Archives/edgar/data/1067983/000095012320002466/form13fInfoTable.xml']

# Create dataframe and set columns to match XML doc
cols = ['nameOfIssuer', 'titleOfClass', 'cusip', 'value', 'sshPrnamt',
        'sshPrnamtType', 'putCall', 'investmentDiscretion',
        'otherManager', 'Sole', 'Shared', 'None']

res_df = pd.DataFrame(columns=cols)


# Iterate over URLs
for form_url in form_urls:
    data = []
    soup = BeautifulSoup(session.get(form_url).content, 'lxml')

    for info_table in soup.find_all(re.compile("([A-Za-z0-9]+:|)infotable")):
        row = []
        for col in cols:
            pattern = re.compile("([A-Za-z0-9]+:|)" + col.lower())
            d = info_table.find(pattern)
            row.append(d.text.strip() if d else 'NaN')
        data.append(row)
    url_df = pd.DataFrame(data, columns=cols)
    res_df = res_df.append(url_df, ignore_index=True)

Edit: Now prefix can be non existent (empty string '') or it can be combination of lowercase, uppercase letters and digits

user_87424410

In the first form_url you have "n1:..." tags. That's why you have empty list.

You can try:

from bs4 import BeautifulSoup
import requests
import pandas as pd

session = requests.Session()

# urls to loop through
form_urls = ['https://www.sec.gov/Archives/edgar/data/1418814/000141881220000017/vac13f021420.xml',
             'https://www.sec.gov/Archives/edgar/data/820124/000095012320003895/408.xml',
             'https://www.sec.gov/Archives/edgar/data/1067983/000095012320002466/form13fInfoTable.xml']

# Create dataframe and set columns to match XML doc
cols = ['nameOfIssuer', 'titleOfClass', 'cusip', 'value', 'sshPrnamt',
        'sshPrnamtType', 'putCall', 'investmentDiscretion',
        'otherManager', 'Sole', 'Shared', 'None',]

res_df = pd.DataFrame(columns=cols)


# Iterate over URLs
for form_url in form_urls:
    data = []
    soup = BeautifulSoup(session.get(form_url).content, 'lxml')
    for info_table in soup.find_all(['n1:infotable', 'infotable']):
        row = []
        for col in cols:
            if form_url == form_urls[0]:
                col = "n1:" + col
            d = info_table.find([col.lower(), 'ns1:' + col.lower()])
            row.append(d.text.strip() if d else 'NaN')
        # print(row)
        data.append(row)
    print(data)
    print("==============================================")
    url_df = pd.DataFrame(data, columns=cols)
    res_df = res_df.append(url_df, ignore_index=True)

print(res_df)
user_122136120
Related