How do I get all the tables from a website using pandas

user_17524062 0
Viewed 201

I am trying to get 3 tables from a particular website but only the first two are showing up. I have even tried get the data using BeautifulSoup but the third seems to be hidden somehow. Is there something I am missing?

url = "https://fbref.com/en/comps/9/keepersadv/Premier-League-Stats"
html = pd.read_html(url, header=1)
print(html[0])
print(html[1])
print(html[2]) # This prompts an error that the tables does not exist

The first two tables are the squad tables. The table not showing up is the individual player table. This also happens with similar pages from the same site.

pandas web-scraping dataframe python-3.x python
1 Answers

You could use Selenium as suggested, but I think is a bit overkill. The table is available in the static HTML, just within the comments. So you would need to pull the comments out of BeautifulSoup to get those tables.

To get all the tables:

import pandas as pd
import requests
from bs4 import BeautifulSoup, Comment

url = 'https://fbref.com/en/comps/9/keepersadv/Premier-League-Stats'
response = requests.get(url)

tables = pd.read_html(response.text, header=1)

# Get the tables within the Comments
soup = BeautifulSoup(response.text, 'html.parser')
comments = soup.find_all(string=lambda text: isinstance(text, Comment))
for each in comments:
    if 'table' in str(each):
        try:
            table = pd.read_html(str(each), header=1)[0]
            table = table[table['Rk'].ne('Rk')].reset_index(drop=True)
            tables.append(table)
        except:
            continue

Output:

for table in tables:
    print(table)


              Squad  # Pl   90s  GA  PKA  ...  Stp  Stp%  #OPA  #OPA/90  AvgDist
0           Arsenal     2  12.0  17    0  ...   10   8.8     6     0.50     14.6
1       Aston Villa     2  12.0  20    0  ...    6   6.8    13     1.08     16.2
2         Brentford     2  12.0  17    1  ...   10   9.9    18     1.50     15.6
3          Brighton     2  12.0  14    2  ...   17  16.2    13     1.08     15.3
4           Burnley     1  12.0  20    0  ...   14  11.7    17     1.42     16.6
5           Chelsea     2  12.0   4    2  ...    8   8.5     5     0.42     14.0
6    Crystal Palace     1  12.0  17    0  ...    7   7.5     6     0.50     13.5
7           Everton     2  12.0  19    0  ...    8   7.4     7     0.58     13.7
8      Leeds United     1  12.0  20    1  ...    8  12.5    15     1.25     16.3
9    Leicester City     1  12.0  21    2  ...    9   8.4     7     0.58     13.0
10        Liverpool     2  12.0  11    0  ...    9   9.7    16     1.33     17.0
11  Manchester City     2  12.0   6    1  ...    5   8.1    16     1.33     17.5
12   Manchester Utd     1  12.0  21    0  ...    4   4.4     2     0.17     13.3
13    Newcastle Utd     2  12.0  27    4  ...   10   9.8     4     0.33     13.9
14     Norwich City     1  12.0  27    2  ...    6   5.1     5     0.42     12.4
15      Southampton     1  12.0  14    0  ...   16  13.9     2     0.17     12.9
16        Tottenham     1  12.0  17    1  ...    3   2.7     5     0.42     14.1
17          Watford     2  12.0  20    1  ...    6   5.5     9     0.75     15.4
18         West Ham     1  12.0  14    0  ...    6   5.3     1     0.08     11.9
19           Wolves     1  12.0  12    3  ...    9  10.0    10     0.83     15.5

[20 rows x 28 columns]
                 Squad  # Pl   90s  GA  PKA  ...  Stp  Stp%  #OPA  #OPA/90  AvgDist
0           vs Arsenal     2  12.0  13    0  ...    4   5.9    11     0.92     15.5
1       vs Aston Villa     2  12.0  16    2  ...   11   8.0     7     0.58     14.8
2         vs Brentford     2  12.0  16    1  ...   16  14.0     9     0.75     15.7
3          vs Brighton     2  12.0  12    3  ...   11  12.5     8     0.67     15.9
4           vs Burnley     1  12.0  14    0  ...   16  10.7    12     1.00     15.1
5           vs Chelsea     2  12.0  30    2  ...   10  11.1    11     0.92     14.2
6    vs Crystal Palace     1  12.0  18    2  ...    7   7.2     9     0.75     14.4
7           vs Everton     2  12.0  16    3  ...    7   7.6     7     0.58     13.8
8      vs Leeds United     1  12.0  12    1  ...    8   7.3     5     0.42     14.2
9    vs Leicester City     1  12.0  16    0  ...    2   3.3     7     0.58     14.3
10        vs Liverpool     2  12.0  35    1  ...   12   9.9    14     1.17     13.7
11  vs Manchester City     2  12.0  25    0  ...    8   6.7     4     0.33     13.1
12   vs Manchester Utd     1  12.0  20    0  ...    7   7.8     7     0.58     14.7
13    vs Newcastle Utd     2  12.0  15    0  ...    8   8.0     8     0.67     15.3
14     vs Norwich City     1  12.0   7    2  ...    5   5.7    16     1.33     17.3
15      vs Southampton     1  12.0  11    2  ...    4   3.7     9     0.75     14.0
16        vs Tottenham     1  12.0  11    1  ...    9  12.2     9     0.75     16.0
17          vs Watford     2  12.0  16    0  ...    8   8.2     9     0.75     15.3
18         vs West Ham     1  12.0  23    0  ...   13  10.5     6     0.50     13.8
19           vs Wolves     1  12.0  12    0  ...    5   6.8     9     0.75     15.3

[20 rows x 28 columns]
    Rk             Player   Nation Pos  ... #OPA #OPA/90 AvgDist  Matches
0    1            Alisson   br BRA  GK  ...   15    1.36    17.1  Matches
1    2  Kepa Arrizabalaga   es ESP  GK  ...    1    1.00    18.8  Matches
2    3    Daniel Bachmann   at AUT  GK  ...    1    0.25    12.2  Matches
3    4      Asmir Begović   ba BIH  GK  ...    0    0.00    15.0  Matches
4    5        Karl Darlow  eng ENG  GK  ...    4    0.50    14.9  Matches
5    6            Ederson   br BRA  GK  ...   14    1.27    17.5  Matches
6    7   Łukasz Fabiański   pl POL  GK  ...    1    0.08    11.9  Matches
7    8   Álvaro Fernández   es ESP  GK  ...    5    1.67    15.3  Matches
8    9         Ben Foster  eng ENG  GK  ...    8    1.00    16.8  Matches
9   10       David de Gea   es ESP  GK  ...    2    0.17    13.3  Matches
10  11     Vicente Guaita   es ESP  GK  ...    6    0.50    13.5  Matches
11  12  Caoimhín Kelleher   ie IRL  GK  ...    1    1.00    14.6  Matches
12  13           Tim Krul   nl NED  GK  ...    5    0.42    12.4  Matches
13  14         Bernd Leno   de GER  GK  ...    1    0.33    13.1  Matches
14  15        Hugo Lloris   fr FRA  GK  ...    5    0.42    14.1  Matches
15  16  Emiliano Martínez   ar ARG  GK  ...   12    1.09    16.4  Matches
16  17      Alex McCarthy  eng ENG  GK  ...    2    0.17    12.9  Matches
17  18      Edouard Mendy   sn SEN  GK  ...    4    0.36    13.3  Matches
18  19      Illan Meslier   fr FRA  GK  ...   15    1.25    16.3  Matches
19  20    Jordan Pickford  eng ENG  GK  ...    7    0.64    13.6  Matches
20  21          Nick Pope  eng ENG  GK  ...   17    1.42    16.6  Matches
21  22     Aaron Ramsdale  eng ENG  GK  ...    5    0.56    14.9  Matches
22  23         David Raya   es ESP  GK  ...   13    1.44    15.7  Matches
23  24            José Sá   pt POR  GK  ...   10    0.83    15.5  Matches
24  25     Robert Sánchez   es ESP  GK  ...   13    1.18    15.4  Matches
25  26  Kasper Schmeichel   dk DEN  GK  ...    7    0.58    13.0  Matches
26  27       Jason Steele  eng ENG  GK  ...    0    0.00    13.0  Matches
27  28          Jed Steer  eng ENG  GK  ...    1    1.00    14.3  Matches
28  29       Zack Steffen   us USA  GK  ...    2    2.00    17.8  Matches
29  30    Freddie Woodman  eng ENG  GK  ...    0    0.00    11.6  Matches

[30 rows x 34 columns]
user_77522960
Related