Python Pandas Web Scraping

Question

I'm trying to turn a list of tables on this page into a Pandas DataFrame: https://intermediaries.hsbc.co.uk/products/product-finder/

I want to select the customer type box only and select one of the elements (from first to last) and then click find product to display the table for each one before concatenating all the DataFrames into 1 DataFrame.

So far I have managed to select the first element and print the table but I can't seem to turn it into a pandas DataFrame as I get a value error: Must pass 2-d input. shape=(1, 38, 12)

This is my code:

def product_type_button(self):
    select = Select(self.driver.find_element_by_id('Availability'))
    try:
        select.select_by_visible_text('First time buyer')
    except NoSuchElementException:
        print('The item does not exist')
    time.sleep(5)
    
    self.driver.find_element_by_xpath('//button[@type="button" and (contains(text(),"Find product"))]').click()
    time.sleep(5)

def create_dataframe(self):

    data1 = pd.read_html(self.driver.page_source)
    print(data1)

    data2 = pd.DataFrame(data1)
    time.sleep(5)

    data2.to_csv('Data1.csv')

I would like to find a way to print the table for each element, maybe selecting by index instead? and then concatenating into one DataFrame. Any help would be appreciated.

Andrej Kesely · Accepted Answer · 2022-10-31 12:32:54Z

3

All data for the table is located inside javascript file. You can use re/json to parse it and then construct the dataframe:

import re
import json
import requests
import pandas as pd


js_src = "https://intermediaries.hsbc.co.uk/component---src-pages-products-product-finder-js-9c7004fb8446c3fe0a07.js"
data = requests.get(js_src).text

data = re.search(r"JSON\.parse\('(.*)'\)", data).group(1)
data = json.loads(data)

df = pd.DataFrame(data)
print(df.head().to_markdown(index=False))

df.to_csv("data.csv", index=False)

Prints:

Changed	NewProductCode	ProductType	Deal Period (Fixed until)	ProductDescription1	ProductTerm	Availability	Repayment Basis	Min LTV %	MaxLTV	Minimum Loan ?	Fees Payable Per	Rate	Reversionary Rate %	APR	Cashback	ERC - Payable	Unlimited lump sum payments Premitted (without fees)	Unlimited overpayment permitted (without fees)	Overpayments	ERC	Portable	Completionfee	Free Legals for Remortgage	FreeStandardValuation	Loading
Continued	4071976	Fixed	31.03.25	Fee Saver*	2 Year Fixed	FTB	CR and IO	0%	60%	10,000	5,000,000	5.99%	5.04%	5.4	No	Yes	No	No	Yes	Please refer to HSBC policy guide	Yes	17	Yes	Yes	nan
Continued	4071977	Fixed	31.03.25	Fee Saver*	2 Year Fixed	FTB	CR and IO	0%	70%	10,000	2,000,000	6.04%	5.04%	5.4	No	Yes	No	No	Yes	Please refer to HSBC policy guide	Yes	17	Yes	Yes	nan
Continued	4071978	Fixed	31.03.25	Fee Saver*	2 Year Fixed	FTB	CR and IO	0%	75%	10,000	2,000,000	6.04%	5.04%	5.4	No	Yes	No	No	Yes	Please refer to HSBC policy guide	Yes	17	Yes	Yes	nan
Continued	4071979	Fixed	31.03.25	Fee Saver*	2 Year Fixed	FTB	CR	0%	80%	10,000	1,000,000	6.14%	5.04%	5.4	No	Yes	No	No	Yes	Please refer to HSBC policy guide	Yes	17	Yes	Yes	nan
Continued	4071980	Fixed	31.03.25	Fee Saver*	2 Year Fixed	FTB	CR	0%	85%	10,000	750,000	6.19%	5.04%	5.4	No	Yes	No	No	Yes	Please refer to HSBC policy guide	Yes	17	Yes	Yes	nan

and saves data.csv (screenshot from LibreOffice):

answered Oct 31, 2022 at 12:32

Andrej Kesely

196k15 gold badges60 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

user19795989 Over a year ago

You are a legend! Thank You! I only hope I can become this good at finding solutions one day. +1

user19795989 Over a year ago

Hi, this seems to be failing now with an error of 'NoneType' object has no attribute 'group'? Also is there a more robust way to find the json file automatically in case the name space changes?

Andrej Kesely Over a year ago

@user19795989 Seems the page is changing the name of the javascript file, now it's something like https://intermediaries.hsbc.co.uk/component---src-pages-products-product-finder-js-1cdc6933728e418b5106.js. More robust way is to load intermediaries.hsbc.co.uk/products/product-finder into a soup, find the javascript filename and then use that file.

user19795989 Over a year ago

Hey @Andrej not sure how to tag you, but would be awesome if you could help with this: stackoverflow.com/questions/74483599/… :)

ozacha · Accepted Answer · 2022-10-31 12:37:49Z

1

A minimal change to your code: pd.read_html returns a list of dataframes for all tables found on the webpage.

Since there is only one table on your page, data1 is a list of 1 dataframe. This is where the error Must pass 2-d input. shape=(1, 38, 12) comes from – data1 contains 1 dataframe of shape (38, 12).

You probably want to do just:

data2 = data1[0]
data2.to_csv(...)

(Also, no need to sleep after reading from the webpage)

answered Oct 31, 2022 at 12:37

ozacha

1,3529 silver badges14 bronze badges

1 Comment

user19795989 Over a year ago

Thank You very much, this did the trick. :) +1 I was adding the sleep just incase it took time for any additional data to load, but I guess I didn't need it

Collectives™ on Stack Overflow

Python Pandas Web Scraping

2 Answers 2

4 Comments

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

4 Comments

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related