How can I pull in a table from a webpage?-CodePudding

I am trying to pull a table of statistics from a broadway show page. This is what I have so far:

import requests
from bs4 import BeautifulSoup
springsteen_url = 'https://www.ibdb.com/broadway-production/springsteen-on-broadway-515480#Statistics'
springsteen_response = requests.get(springsteen_url)
springsteen_soup = BeautifulSoup(springsteen_response.text, 'html.parser')

I found the table tag and there were 2 named "striped" I selected the first one:

statistics = soup.find_all("table", attrs={"class": "striped"})
table1 = statistics[0]

I am able to create a df and populate the headers but the rows/cells won't populate:

# Defining of the dataframe
df = pd.DataFrame(columns=['Week Ending', 'Gross', '% Gross Pot.', 'Attendance', '% Capacity'])

# Collecting Ddata
for row in table.tbody.find_all('tr'):    
    # Find all data for each column
    columns = row.find_all('td')
    
    if(columns != []):
        week = columns[0].text.strip()
        gross = columns[1].text.strip()
        grosspot = columns[2].span.contents[0].strip('&0.')
        attendance = columns[3].span.contents[0].strip('&0.')
        capacity = columns[4].span.contents[0].strip('&0.')

        df = df.append({'Week Ending': week,  'Gross': gross, '% Gross Pot.': grosspot, 'Attendance': attendance, '% Capacity': capacity}, ignore_index=True)

df.head()

CodePudding user response：

First of all I found a mistake in your code that you used springsteen_response.text in BeautifulSoup(springsteen_response.text, 'html.parser') instead of springsteen_response.content so try to avoid it to not have problems with character encoding. The .content attribute holds raw bytes, which can be decoded better.

I also coded a solution to your problem with Ali Değer Ozbakir's hint to extract data with regex. I explained things in code comments, but in short you need to use Python regex to be able to extract a data within <script> tag then extract a group from that regex and finally you have to use ast.literal_eval to parse a data string because the string didn't match JSON format so you couldn't parse it.

Code:

import re
import ast
import requests
import pandas as pd
from bs4 import BeautifulSoup

springsteen_url = 'https://www.ibdb.com/broadway-production/springsteen-on-broadway-515480#Statistics'
springsteen_response = requests.get(springsteen_url)
springsteen_soup = BeautifulSoup(springsteen_response.content, 'html.parser')

# Find a <script> tag which have "grossdata" text inside
scriptdata = springsteen_soup.find("script", text=re.compile("grossdata")).string

# this is a regular expression
# ? after space means that space may or may not occur in string
# . (dot) means we are matching any character
#   sign when used with previous dot means that any character need to occur at least one time
# at the end ";" may or may not occur
grossdata_pattern = re.compile(r"grossdata ?= ?({. });?")
grossdata_search = grossdata_pattern.search(scriptdata)

if grossdata_search:
    # We are extracting here a second group (first is the whole string from "grossdata" to ";")
    # the second group is simply between parentheses "()"
    grossdata = grossdata_search.group(1)
else:
    print("Failed to locate string to match regex.")

# Because of a data didn't match JSON format we couldn't parse to it
# so we need to use safe evaluation of a python code
# to make it a python dictionary
grossdata = ast.literal_eval(grossdata)

grossdata_2018 = grossdata[0]

df = pd.DataFrame(grossdata_2018)

# Remove unwanted columns
df.drop([5, 6, 7, 8, 9, 10, 11], axis=1, inplace=True)
df.columns = ['Week Ending', 'Gross', '% Gross Pot.', 'Attendance', '% Capacity']

print(df)

Output:

     Week Ending       Gross % Gross Pot. Attendance % Capacity
0    Jun 3, 2018  $1,929,448         101%      3,792       100%
1   Jun 10, 2018  $2,402,103         101%      4,740       100%
2   Jun 17, 2018  $1,930,270         101%      3,792       100%
3   Jun 24, 2018  $2,411,075         101%      4,740       100%
4    Jul 1, 2018  $1,929,003         101%      3,792       100%
5   Jul 15, 2018  $2,410,195         101%      4,740       100%
6   Jul 22, 2018    $964,163         101%      1,895       100%
7   Jul 29, 2018  $1,931,618         101%      3,792       100%
8   Aug 12, 2018  $1,931,445         101%      3,792       100%
9   Aug 19, 2018  $2,406,003         101%      4,729       100%
10  Aug 26, 2018  $1,928,960         101%      3,792       100%
11   Sep 2, 2018  $1,930,020         101%      3,792       100%
12   Sep 9, 2018  $1,932,670         101%      3,792       100%
13  Sep 30, 2018  $1,927,620         101%      3,792       100%
14   Oct 7, 2018  $2,410,528         101%      4,740       100%
15  Oct 14, 2018  $1,929,795         101%      3,792       100%
16  Oct 21, 2018  $2,414,700         101%      4,740       100%
17  Oct 28, 2018  $1,937,995         102%      3,792       100%
18   Nov 4, 2018  $2,424,880         102%      4,740       100%
19  Nov 11, 2018  $1,935,170         101%      3,792       100%
20  Nov 18, 2018  $2,423,700         102%      4,740       100%
21   Dec 2, 2018  $1,929,320         101%      3,792       100%
22   Dec 9, 2018  $2,415,700         101%      4,740       100%
23  Dec 16, 2018  $1,895,695         100%      3,792       100%