Extract HTML Tables With Similar Data from Different Sources with Different Formatting - Python

Question

I am trying to scrape HTML tables from two different HTML sources. Both are very similar, each table includes the same data but they may be structured differently, with different column names etc. For one source, all of the data may be included in one table, while the other source may have the data broken up into two separate tables.

As an example, we can look at insider holders of both AAPL and MMM stocks.

Screenshots here - https://i.sstatic.net/dt6Pa.jpg

Lets say the end goal is to extract the total number of shares held by insiders - one singular number. Each table may be structured differently, but what should be similar is key words such as "Beneficially" or "Stock".

Any help would be greatly appreciated. In a previous post I was able to extract some of the data. But it can't be looped or repeated if structuring is different.

Extract HTML Table Based on Specific Column Headers - Python

df = pd.read_html("https://www.sec.gov/Archives/edgar/data/66740/000120677420000907/mmm3661701-def14a.htm", attrs={'style': 'border-collapse: collapse; width: 100%; font: 9pt Arial, Helvetica, Sans-Serif'}, match="Name/address")

df = df[0]
df = df.dropna(axis = 'columns')

Also attempted with BS


url = 'https://www.sec.gov/Archives/edgar/data/66740/000120677420000907/mmm3661701-def14a.htm'
r = requests.get(url) 
soup = BeautifulSoup(r.text, 'html.parser')
tables = soup.find_all('table')
rows = tables.find_all('tr')

@αԋɱҽԃαмєяιcαη this is really really good. Is there anyway to have the function return a singular output, the sum of the shares held by insiders? This is where it can get tricky. Because AAPL puts all insiders on one table, while MMM puts them on two tables. The function runs well on my machine, but in the CSV return I will need to manually go in and try to sum the shares up. — Patriots_25
– Patriots_25, Commented Apr 2, 2020 at 16:30

αԋɱҽԃ αмєяιcαη · Accepted Answer · 2020-04-02 15:52:00Z

That was really complicated but here we go :).

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


urls = ['https://www.sec.gov/Archives/edgar/data/320193/000119312520001450/d799303ddef14a.htm',
        'https://www.sec.gov/Archives/edgar/data/66740/000120677420000907/mmm3661701-def14a.htm']


def main(urls):
    with requests.Session() as req:
        for url in urls:
            r = req.get(url)
            soup = BeautifulSoup(r.content, 'html.parser')
            for item in soup.findAll("a", text=re.compile("^Security")):
                item = item.get("href")[1:]
                catch = soup.find("a", {'name': item}).find_next("table")
                df = pd.read_html(str(catch))
                print(df)
                df[0].to_csv(f"{item}.csv", index=False, header=None)


main(urls)

Output:

[                                                    0  ...    8
0                                                 NaN  ...  NaN
1                                                 NaN  ...  NaN
2                            Name of Beneficial Owner  ...  NaN
3                                                 NaN  ...  NaN
4                                  The Vanguard Group  ...    %
5                                                 NaN  ...  NaN
6                                     BlackRock, Inc.  ...    %
7                                                 NaN  ...  NaN
8         Berkshire Hathaway Inc. / Warren E. Buffett  ...    %
9                                                 NaN  ...  NaN
10                                         Kate Adams  ...  NaN
11                                                NaN  ...  NaN
12                                    Angela Ahrendts  ...  NaN
13                                                NaN  ...  NaN
14                                         James Bell  ...  NaN
15                                                NaN  ...  NaN
16                                           Tim Cook  ...  NaN
17                                                NaN  ...  NaN
18                                            Al Gore  ...  NaN
19                                                NaN  ...  NaN
20                                        Andrea Jung  ...  NaN
21                                                NaN  ...  NaN
22                                       Art Levinson  ...  NaN
23                                                NaN  ...  NaN
24                                       Luca Maestri  ...  NaN
25                                                NaN  ...  NaN
26                                    Deirdre O’Brien  ...  NaN
27                                                NaN  ...  NaN
28                                          Ron Sugar  ...  NaN
29                                                NaN  ...  NaN
30                                         Sue Wagner  ...  NaN
31                                                NaN  ...  NaN
32                                      Jeff Williams  ...  NaN
33                                                NaN  ...  NaN
34  All current executive officers and directors a...  ...  NaN

[35 rows x 9 columns]]
[                                                   0   1   ...                18  19 
0                        Name  and principal position NaN  ...  Percent of Class NaN  
1                    Thomas “Tony” K. Brown, Director NaN  ...               (5) NaN  
2                           Pamela J. Craig, Director NaN  ...               (5) NaN  
3                           David B. Dillon, Director NaN  ...               (5) NaN  
4                          Michael L. Eskew, Director NaN  ...               (5) NaN  
5                         Herbert L. Henkel, Director NaN  ...               (5) NaN  
6                               Amy E. Hood, Director NaN  ...               (5) NaN  
7                               Muhtar Kent, Director NaN  ...               (5) NaN  
8                           Edward M. Liddy, Director NaN  ...               (5) NaN  
9                           Dambisa F. Moyo, Director NaN  ...               (5) NaN  
10                          Gregory R. Page, Director NaN  ...               (5) NaN  
11                       Patricia A. Woertz, Director NaN  ...               (5) NaN  
12  Michael F. Roman, Chairman of the Board, Presi... NaN  ...               (5) NaN  
13  Inge G. Thulin, Former Executive Chairman of t... NaN  ...               (5) NaN  
14  Nicholas C. Gangestad, Senior Vice President a... NaN  ...               (5) NaN  
15  Ashish K. Khandpur, Executive Vice President, ... NaN  ...               (5) NaN  
16  Julie L. Bushman, Executive Vice President, In... NaN  ...               (5) NaN  
17  Joaquin Delgado, Former Executive Vice Preside... NaN  ...               (5) NaN  
18  Michael G. Vale, Executive Vice President, Saf... NaN  ...               (5) NaN  
19  All Directors and Executive Officers as a Grou... NaN  ...               (5) NaN  

[20 rows x 20 columns]]
[                                                   0   1  ...                  6   7 
0                                       Name/address NaN  ...  Percent  of Class NaN  
1  The Vanguard Group(1) 100 Vanguard Blvd. Malve... NaN  ...               8.78 NaN  
2  State Street Corporation(2) State Street Finan... NaN  ...               7.36 NaN  
3  BlackRock, Inc.(3) 55 East 52nd Street New Yor... NaN  ...               7.30 NaN  

[4 rows x 8 columns]]

Collectives™ on Stack Overflow

Extract HTML Tables With Similar Data from Different Sources with Different Formatting - Python

1 Answer 1

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related