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')
That was really complicated but here we go :).
import requests
from bs4 import BeautifulSoup
import re
import pandas as pdurls = ['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]]