scraping tables using selenium but the table's structure is messy

Question

My goal is to extract data from clinicaltrials. On each page there is a table, I try to extract the data table from each page then concatenate all the tables into one. My code works but there are two problems, the first one is that sometimes (depending on the link) the data table has not the correct structure like the following

    Row Saved   Status      Study Title   Conditions Interventions  Locations
0   1   NaN     Completed   Pilot Survey  Arbovirus  NaN            NaN
1   Locations: Tropical Medicine centerKaohsiung c...   Locations: Tropical Medicine centerKaohsiung c...   Locations: Tropical Medicine centerKaohsiung c...   Locations: Tropical Medicine centerKaohsiung c...   Locations: Tropical Medicine centerKaohsiung c...   Locations: Tropical Medicine centerKaohsiung c...   NaN

as you can see the second row has the column Location repeated multiple times ..

here's my code :

import time
import selenium 
from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import Select
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.ui import WebDriverWait
import html5lib
from bs4 import BeautifulSoup
import pandas as pd 
from selenium.common.exceptions import TimeoutException
from selenium.common.exceptions import WebDriverException
from selenium.webdriver.support import expected_conditions as EC

ser = Service("/xxxxxxx/chromedriver") # change it to where your chromedriver is 
options = webdriver.ChromeOptions() 
options.add_argument("--headless")
options.add_argument("start-maximized")
options.add_argument('disable-infobars')
driver = webdriver.Chrome(service=ser, options=options)

#url =  "https://clinicaltrials.gov/ct2/results?cond=Abruptio+Placentae"

url = "https://www.clinicaltrials.gov/ct2/results?cond=%2246%2C+XX+Disorders+of+Sex+Development%22"
driver.get(url)

#select = Select(driver.find_element_by_name('theDataTable_length'))
#select.select_by_value('100')
time.sleep(1)

html = driver.page_source
soup = BeautifulSoup(html, 'html.parser')

list_table = []
#df_tables = pd.read_html(str(soup), flavor="bs4")
df_tables = pd.read_html(str(soup))[1]
list_table.append(df_tables)

xp_next = '//*[@id="theDataTable_next"]/span'

while True:
    try:
        WebDriverWait(driver, 2).until(EC.visibility_of_element_located(
            (By.CSS_SELECTOR, "[class='paginate_button next']")))
        driver.find_element_by_xpath(xp_next).click()
        html = driver.page_source
        soup = BeautifulSoup(html, 'html.parser')
        df_tables = pd.read_html(str(soup))[1]
        #display(df_tables)
        list_table.append(df_tables)
        print("Navigating to Next Page")
        time.sleep(1)
    except (TimeoutException, WebDriverException) as e:
        print("Last page reached")
        break
        
driver.quit()

df = pd.concat(list_table)
df

I'm thinking maybe the problem is read_html?

It'll depend on the html you're trying to read. Can you post a sample of the source html? — Kyle Alm
– Kyle Alm, Commented Dec 14, 2021 at 11:48
the source html is really huge, don't really know which part would be interesting to post — user9364978
– user9364978, Commented Dec 14, 2021 at 11:56
read_html() works fine and df is displayed as table looks like (selenium4) - Could you provide full example and versions of modules? Would be great to be able to reproduce. — HedgeHog
– HedgeHog, Commented Dec 14, 2021 at 12:05
selenium version 4.1.0 // bs4 4.10.0 and as for the code, I just updated my post and added the whole thing so It could be easily reproduced — user9364978
– user9364978, Commented Dec 14, 2021 at 12:25

HedgeHog · Accepted Answer · 2021-12-14 16:38:19Z

1

What happens?

Issue is caused in headless mode in combination with your arguments - Tables structure is swapping in responsive mode and some columns changes to rows.

How to fix?

You have to set options more strict and can force webdriver to use a specific window-size while start-maximized

options = webdriver.ChromeOptions() 
options.add_argument("--window-size=1920,1080")
options.add_argument("--start-maximized")
options.add_argument("--headless")

Example

import selenium, html5lib, time
import pandas as pd 
from bs4 import BeautifulSoup

from selenium import webdriver
from selenium.webdriver.common.by import By
from selenium.webdriver.support.ui import Select
from selenium.webdriver.chrome.service import Service
from selenium.webdriver.chrome.options import Options
from selenium.webdriver.support.ui import WebDriverWait
from selenium.common.exceptions import TimeoutException
from selenium.common.exceptions import WebDriverException
from selenium.webdriver.support import expected_conditions as EC

ser = Service("#####################") # change it to where your chromedriver is 
options = webdriver.ChromeOptions() 
options.add_argument("--window-size=1920,1080")
options.add_argument("--start-maximized")
options.add_argument("--headless")
driver = webdriver.Chrome(service=ser, options=options)


url = "https://www.clinicaltrials.gov/ct2/results?cond=%2246%2C+XX+Disorders+of+Sex+Development%22"
driver.get(url)

list_table = []

while True:
    time.sleep(1)
    html = driver.page_source
    soup = BeautifulSoup(html, 'html.parser')
    list_table.append(pd.read_html(html)[1])
    print("Navigating to Next Page")

    try:
        WebDriverWait(driver, 10).until(EC.element_to_be_clickable((By.CSS_SELECTOR, "[class='paginate_button next'] span"))).click()
    except (TimeoutException, WebDriverException) as e:
        print("Last page reached")
        break

driver.quit()

df = pd.concat(list_table)
df

Outout

	Row	Saved	Status	Study Title	Conditions	Interventions	Locations
0	1	nan	Recruiting	Feasibility Study of Uterine Transplantation From Living Donors in Terms of Efficacy and Safety in Patients With Mayer-Rokitansky-Küster-Hauser Syndrome (MRKH)	Mayer Rokitansky Kuster Hauser Syndrome	Procedure: Uterine transplantation	Hopital FochSuresnes, France
1	2	nan	Recruiting	Molecular Genetic Study of Mayer-Rokitansky-Kuster-Hauser Syndrome	Mayer Rokitansky Kuster Hauser Syndrome	Genetic: Biological samples for patientsGenetic: Biological samples for healthy relatives	Necker - Enfants malades hospitalParis, FranceInstitut Mutualiste MontsourisParis, France
2	3	nan	Recruiting	Uterus Transplantation From a Multi-organ Donor	Infertility, FemaleMayer Rokitansky Kuster Hauser Syndrome	Procedure: uterus transplantation	Ghent University Hospital - Women's ClinicGhent, Belgium

edited Dec 14, 2021 at 16:38

answered Dec 14, 2021 at 14:07

HedgeHog

25.4k5 gold badges18 silver badges43 bronze badges

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

3 Comments

user9364978 Over a year ago

IT WORKED, thanks ! but I just noticed that the table I extract has the same 10 rows repeated all over, i guess the problem is related to the fact that the link doesn't change even when I go to the next page which is probably why I still get the same source page in my "while loop" ! how can I fix that?

HedgeHog Over a year ago

To keep the topic clean I would recommend to accept answer and ask that as a new question. We will take a look. Thanks

HedgeHog Over a year ago

Added additional cleaned example - Just take look and give it a try.

chitown88 · Accepted Answer · 2021-12-14 15:10:52Z

I think it may just be easer to go after the data directly. No need to use selenium then. Just need to parse some of the html:

import pandas as pd
import requests
from bs4 import BeautifulSoup

url = 'https://www.clinicaltrials.gov/ct2/results/rpc/mi0yqBc9u64Wpg4BvnGkBnjPewc9S6hHSwS3Z6p3C61JJPhHc67aZwhLzdUVp'
payload = '''draw=3&columns%5B0%5D%5Bdata%5D=0&columns%5B0%5D%5Bname%5D=&columns%5B0%5D%5Bsearchable%5D=true&columns%5B0%5D%5Borderable%5D=false&columns%5B0%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B0%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B1%5D%5Bdata%5D=1&columns%5B1%5D%5Bname%5D=&columns%5B1%5D%5Bsearchable%5D=false&columns%5B1%5D%5Borderable%5D=false&columns%5B1%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B1%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B2%5D%5Bdata%5D=2&columns%5B2%5D%5Bname%5D=&columns%5B2%5D%5Bsearchable%5D=true&columns%5B2%5D%5Borderable%5D=false&columns%5B2%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B2%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B3%5D%5Bdata%5D=3&columns%5B3%5D%5Bname%5D=&columns%5B3%5D%5Bsearchable%5D=true&columns%5B3%5D%5Borderable%5D=false&columns%5B3%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B3%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B4%5D%5Bdata%5D=4&columns%5B4%5D%5Bname%5D=&columns%5B4%5D%5Bsearchable%5D=true&columns%5B4%5D%5Borderable%5D=false&columns%5B4%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B4%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B5%5D%5Bdata%5D=5&columns%5B5%5D%5Bname%5D=&columns%5B5%5D%5Bsearchable%5D=true&columns%5B5%5D%5Borderable%5D=false&columns%5B5%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B5%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B6%5D%5Bdata%5D=6&columns%5B6%5D%5Bname%5D=&columns%5B6%5D%5Bsearchable%5D=true&columns%5B6%5D%5Borderable%5D=false&columns%5B6%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B6%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B7%5D%5Bdata%5D=7&columns%5B7%5D%5Bname%5D=&columns%5B7%5D%5Bsearchable%5D=true&columns%5B7%5D%5Borderable%5D=false&columns%5B7%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B7%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B8%5D%5Bdata%5D=8&columns%5B8%5D%5Bname%5D=&columns%5B8%5D%5Bsearchable%5D=true&columns%5B8%5D%5Borderable%5D=false&columns%5B8%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B8%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B9%5D%5Bdata%5D=9&columns%5B9%5D%5Bname%5D=&columns%5B9%5D%5Bsearchable%5D=true&columns%5B9%5D%5Borderable%5D=false&columns%5B9%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B9%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B10%5D%5Bdata%5D=10&columns%5B10%5D%5Bname%5D=&columns%5B10%5D%5Bsearchable%5D=true&columns%5B10%5D%5Borderable%5D=false&columns%5B10%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B10%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B11%5D%5Bdata%5D=11&columns%5B11%5D%5Bname%5D=&columns%5B11%5D%5Bsearchable%5D=true&columns%5B11%5D%5Borderable%5D=false&columns%5B11%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B11%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B12%5D%5Bdata%5D=12&columns%5B12%5D%5Bname%5D=&columns%5B12%5D%5Bsearchable%5D=true&columns%5B12%5D%5Borderable%5D=false&columns%5B12%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B12%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B13%5D%5Bdata%5D=13&columns%5B13%5D%5Bname%5D=&columns%5B13%5D%5Bsearchable%5D=true&columns%5B13%5D%5Borderable%5D=false&columns%5B13%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B13%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B14%5D%5Bdata%5D=14&columns%5B14%5D%5Bname%5D=&columns%5B14%5D%5Bsearchable%5D=true&columns%5B14%5D%5Borderable%5D=false&columns%5B14%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B14%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B15%5D%5Bdata%5D=15&columns%5B15%5D%5Bname%5D=&columns%5B15%5D%5Bsearchable%5D=true&columns%5B15%5D%5Borderable%5D=false&columns%5B15%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B15%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B16%5D%5Bdata%5D=16&columns%5B16%5D%5Bname%5D=&columns%5B16%5D%5Bsearchable%5D=true&columns%5B16%5D%5Borderable%5D=false&columns%5B16%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B16%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B17%5D%5Bdata%5D=17&columns%5B17%5D%5Bname%5D=&columns%5B17%5D%5Bsearchable%5D=true&columns%5B17%5D%5Borderable%5D=false&columns%5B17%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B17%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B18%5D%5Bdata%5D=18&columns%5B18%5D%5Bname%5D=&columns%5B18%5D%5Bsearchable%5D=true&columns%5B18%5D%5Borderable%5D=false&columns%5B18%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B18%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B19%5D%5Bdata%5D=19&columns%5B19%5D%5Bname%5D=&columns%5B19%5D%5Bsearchable%5D=true&columns%5B19%5D%5Borderable%5D=false&columns%5B19%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B19%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B20%5D%5Bdata%5D=20&columns%5B20%5D%5Bname%5D=&columns%5B20%5D%5Bsearchable%5D=true&columns%5B20%5D%5Borderable%5D=false&columns%5B20%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B20%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B21%5D%5Bdata%5D=21&columns%5B21%5D%5Bname%5D=&columns%5B21%5D%5Bsearchable%5D=true&columns%5B21%5D%5Borderable%5D=false&columns%5B21%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B21%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B22%5D%5Bdata%5D=22&columns%5B22%5D%5Bname%5D=&columns%5B22%5D%5Bsearchable%5D=true&columns%5B22%5D%5Borderable%5D=false&columns%5B22%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B22%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B23%5D%5Bdata%5D=23&columns%5B23%5D%5Bname%5D=&columns%5B23%5D%5Bsearchable%5D=true&columns%5B23%5D%5Borderable%5D=false&columns%5B23%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B23%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B24%5D%5Bdata%5D=24&columns%5B24%5D%5Bname%5D=&columns%5B24%5D%5Bsearchable%5D=true&columns%5B24%5D%5Borderable%5D=false&columns%5B24%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B24%5D%5Bsearch%5D%5Bregex%5D=false&columns%5B25%5D%5Bdata%5D=25&columns%5B25%5D%5Bname%5D=&columns%5B25%5D%5Bsearchable%5D=true&columns%5B25%5D%5Borderable%5D=false&columns%5B25%5D%5Bsearch%5D%5Bvalue%5D=&columns%5B25%5D%5Bsearch%5D%5Bregex%5D=false&start=0&length=100&search%5Bvalue%5D=&search%5Bregex%5D=false'''
headers = {'user-agent': 'Mozilla/5.0 (Windows NT 10.0; Win64; x64) AppleWebKit/537.36 (KHTML, like Gecko) Chrome/96.0.4664.93 Safari/537.36'}

jsonData = requests.post(url, data=payload, headers=headers).json()
df = pd.DataFrame(jsonData['data'])

for col in df.columns:
    df[col] = df[col].apply(lambda row: BeautifulSoup(row, 'html.parser').text.strip())

**Output:

print(df.head(5).to_string())
  0            1                   2                                                                                                                                                                3                                                               4                                                                                          5               6               7                                                                                8      9                                                                                                               10                                                                                                                                                                                                                                                                                                    11   12      13                                           14           15            16      17                 18                 19                20                  21                 22 23                                                                                         24 25
0  1  NCT03689842          Recruiting  Feasibility Study of Uterine Transplantation From Living Donors in Terms of Efficacy and Safety in Patients With Mayer-Rokitansky-Küster-Hauser Syndrome (MRKH)                         Mayer Rokitansky Kuster Hauser Syndrome                                                         Procedure: Uterine transplantation  Interventional  Not Applicable                                                                     Hopital Foch  Other  Allocation: N/AIntervention Model: Single Group AssignmentMasking: None (Open Label)Primary Purpose: Treatment                                                                                                                                                 PregnancySafety assessment of the donor, the recipient and the fœtusPsychological assessment of the donor and the recipientGraft Rejection assessment   20  Female  18 Years to 65 Years   (Adult, Older Adult)  NCT03689842       2016/26          December 14, 2017  December 14, 2024      June 1, 2025  September 28, 2018    August 14, 2019                                                                  Hopital FochSuresnes, France   
1  2  NCT02967822          Recruiting                                                                                               Molecular Genetic Study of Mayer-Rokitansky-Kuster-Hauser Syndrome                         Mayer Rokitansky Kuster Hauser Syndrome  Genetic: Biological samples for patientsGenetic: Biological samples for healthy relatives   Observational                  Imagine InstituteReference center for rare diseases (Rare Gynecologic Diseases)  Other                                                        Observational Model: CohortTime Perspective: Prospective                                                                                                                                                                                           Number of identified nucleotidic variation(s) whose consequences can explain the phenotype of MRKH syndrome  410     All                    Child, Adult, Older Adult  NCT02967822  IMNIS2015-06    MRKH           May 2016           May 2031          May 2031   November 18, 2016   October 12, 2018     Necker - Enfants malades hospitalParis, FranceInstitut Mutualiste MontsourisParis, France   
2  3  NCT03252795          Recruiting                                                                                                                  Uterus Transplantation From a Multi-organ Donor      Infertility, FemaleMayer Rokitansky Kuster Hauser Syndrome                                                          Procedure: uterus transplantation  Interventional  Not Applicable                                                       University Hospital, Ghent  Other  Allocation: N/AIntervention Model: Single Group AssignmentMasking: None (Open Label)Primary Purpose: Treatment                                                                                                                                                                Survival of the uterus 1 year after transplantationComplications after uterus transplantationOngoing pregnancy rateTake home baby rate   20  Female               18 Years to 38 Years   (Adult)  NCT03252795  EC/2016/0731           November 3, 2016      December 2021     December 2023     August 17, 2017  November 13, 2019                                      Ghent University Hospital - Women's ClinicGhent, Belgium   
3  4  NCT03188640           Completed                                                                                                                 Bariatric Surgery, Hormones, and Quality of Life       ObesityHormone DisturbanceQuality of LifeHyperandrogenism                                             Procedure: Laparoscopic gastric-bypass surgery  Interventional  Not Applicable                                                            Linkoeping University  Other  Allocation: N/AIntervention Model: Single Group AssignmentMasking: None (Open Label)Primary Purpose: Treatment                                                                                                                                                                                                                Sex-hormone levelsFemale sexual functionHormone-related quality of life(and 2 more...)   68  Female               18 Years to 50 Years   (Adult)  NCT03188640   2012/392-31   OBLIV      March 1, 2014   October 31, 2016  October 31, 2016       June 15, 2017      June 16, 2017                                                                                                 
4  5  NCT04912648  Not yet recruiting                                                                                        FEmale Metabolic Risk and Androgens: an Irish Longitudinal (FEMAIL) Study  HyperandrogenismMetabolic DiseaseSex Hormones Adverse Reaction                                                              Other: Longitudinal follow up   Observational                                                               Royal College of Surgeons, Ireland  Other                                                        Observational Model: CohortTime Perspective: Prospective  Correlation of sex hormone profiles and the metabolome of women across the lifespan with risk of metabolic dysfunction, cardiovascular disease and quality of life.Development of risk prediction models for development of diabetes in womenEstablishment of the longitudinal FEMAIL study database  500  Female    18 Years and older   (Adult, Older Adult)  NCT04912648         20/49  FEMAIL  September 1, 2021  September 1, 2025   August 31, 2031        June 3, 2021       June 3, 2021                                           Royal College of Surgeons in IrelandDublin, Ireland 

[58 rows x 26 columns]

It is indeed faster but I don't get how your link changed? also, your link allowed to extract only 58 studies however there are more then 390 000 studies, how would you fix that in a way that all the studies are extracted?
the link you have in your post only returns 58 rows. Secondly you get the url by looking at the Dev Tools -> Network -> XHR.
You could fix how to return more rows by adjusting your filters, and then use that payload in the query. Again you can find the payload in the Dev Tools.

Collectives™ on Stack Overflow

scraping tables using selenium but the table's structure is messy

2 Answers 2

What happens?

How to fix?

Example

Outout

3 Comments

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

What happens?

How to fix?

Example

Outout

3 Comments

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related