Beautiful soup fining elements of a table that are of different sizes

Question

I have a page that has Auction details I like to extract. The data from these tables changes in size because there are times that not all data points are available. Is there a way to create missing columns even if they are missing in order to keep the table consistent.

This is an example of a table that has some data points missing


    
        
            
                Auction Type:
                FORECLOSURE
            
            
                Case #:
                2009-020878-CA-01
                
            
            
                Final Judgment Amount:
                $183,284.23
            
            
                Parcel ID:
                
            
            
                Plaintiff Max Bid:
                Hidden

This is an example of a table that has all the data points


    
        
            
                Auction Type:
                FORECLOSURE
            
            
                Case #:
                2013-037297-CA-01
                
            
            
                Final Judgment Amount:
                $242,181.24
            
            
                Parcel ID:
                04-3106-030-0070
            
            
                Property Address:
                131 W 41 ST
            
            
                
                HIALEAH, FL- 33012
            
            
                Assessed Value:
                $201,701.00
            
            
                Plaintiff Max Bid:
                Hidden

The short table is missing all the fields below in this example. But that may vary PropertyAddress, PropertyAddress1, AssessedValue, PlaintiffMaxBid

How would I extract existing columns and create NaN columns for the missing columns so table size is consistent.

Below are 2 samples one that has all data points and an other that has missing data points

No_Data_Missing

Missing Data Points

RJ Adriaansen · Answer

I suggest using pandas for this. It uses BeautifulSoup under the hood and is able to read html tables as pandas DataFrames, which you can easily export to various file formats. If you concatenate the two dataframes, pandas will fill the missing values with nan:

import pandas as pd

html1 = 'Auction Type: FORECLOSURE
Case #: 2009-020878-CA-01
Final Judgment Amount: $183,284.23
Parcel ID: 
 Plaintiff Max Bid: Hidden'
html2 = ' Auction Type: FORECLOSURE
Case #: 2013-037297-CA-01
Final Judgment Amount: $242,181.24
Parcel ID: 04-3106-030-0070
Property Address: 131 W 41 ST
HIALEAH, FL- 33012
 Assessed Value: $201,701.00
Plaintiff Max Bid: Hidden'

df1 = pd.read_html(html1)[0] #since read_html returns a list of tables on a page, select the one you need by index, in this case [0]
df2 = pd.read_html(html2)[0]

df1 = df1.T #transpose the table
df2 = df2.T

df1 = df1.rename(columns=df1.iloc[0]).drop(df1.index[0]).reset_index(drop=True) # set the first row as headers
df2 = df2.rename(columns=df2.iloc[0]).drop(df1.index[0]).reset_index(drop=True)

final_df = pd.concat([df1, df2], ignore_index=True, sort=False) # concatenate dataframes

final_df.to_csv('final.csv') # export to csv

Now, final_df will output:

	Auction Type:	Case #:	Final Judgment Amount:	Parcel ID:	Plaintiff Max Bid:	Property Address:	nan	Assessed Value:
0	FORECLOSURE	2009-020878-CA-01	$183,284.23	nan	Hidden	nan	nan	nan
1	FORECLOSURE	2013-037297-CA-01	$242,181.24	04-3106-030-0070	Hidden	131 W 41 ST	HIALEAH, FL- 33012	$201,701.00

If all tables are on the same page, you can process the page in one go with list comprehension:

dfs = pd.read_html(html1+html2)

dfs = [i.T for i in dfs]
dfs = [i.rename(columns=i.iloc[0]).drop(i.index[0]).reset_index(drop=True) for i in dfs]
final_df = pd.concat(dfs, ignore_index=True, sort=False)

HedgeHog · Answer

EDIT

BeautifulSoup - Scrape your tables, store results in a list of dicts and create a dataframe - pandas will fill values from non existing keys with NaN - See also full example.

for table in soup.select('table.ad_tab'):
    data.append(dict([(row.th.text,row.td.text) for row in table.select('tr') ]))

Pandas - Iterate list of urls, pd.read_html() your tables, pd.tranpose() and finally concate them:

import pandas as pd
urls = ['https://projectcodesamples.s3.amazonaws.com/CompleteFile.html','https://projectcodesamples.s3.amazonaws.com/shortFile.html']
pd.concat([df.set_index(0).T for url in urls for df in pd.read_html(url, attrs={'class':'ad_tab'})],ignore_index=True)

Example

from bs4 import BeautifulSoup
import pandas as pd
import requests

urls = ['https://projectcodesamples.s3.amazonaws.com/CompleteFile.html','https://projectcodesamples.s3.amazonaws.com/shortFile.html']

data = []

for url in urls:
    response = requests.get(url)
    soup = BeautifulSoup(response.text)
    for table in soup.select('table.ad_tab'):
            data.append(dict([(row.th.text,row.td.text) for row in table.select('tr') ]))


pd.DataFrame(data)

	Auction Type:	Case #:	Final Judgment Amount:	Parcel ID:	Plaintiff Max Bid:	Property Address:		Assessed Value:
0	FORECLOSURE	2009-020878-CA-01	$183,284.23		Hidden	nan	nan	nan
1	FORECLOSURE	2013-037297-CA-01	$242,181.24	04-3106-030-0070	Hidden	131 W 41 ST	HIALEAH, FL- 33012	$201,701.00
...	...	...	...	...	...	...	...	...
38	FORECLOSURE	2015-011730-CA-01	$558,875.05		nan	nan	nan	Hidden
39	FORECLOSURE	2015-012472-CA-01	$595,150.12		nan	nan	nan	Hidden

It is also possible to append() a new dict as row to your existing dataframe:

df.append({'Auction Type:': 'some value',
  'Case #:': 'some value',
  'Final Judgment Amount:': 'some value',
  'Parcel ID:': 'some value',
  'Plaintiff Max Bid:': ''}, ignore_index=True)

	Auction Type:	Case #:	Final Judgment Amount:	Parcel ID:	Plaintiff Max Bid:	Property Address:		Assessed Value:
0	FORECLOSURE	2009-020878-CA-01	$183,284.23		Hidden	nan	nan	nan
1	FORECLOSURE	2013-037297-CA-01	$242,181.24	04-3106-030-0070	Hidden	131 W 41 ST	HIALEAH, FL- 33012	$201,701.00
2	some value	some value	some value	some value		nan	nan	nan

Beautiful soup fining elements of a table that are of different sizes

2 Answers

EDIT

Example