CODEWITHSUNDEEP
pythonpandasweb-scraping
chitown88
chitown88

Reputation: 28565

python pd.read_html gives error

I've been using pandas and request to pull some tables to get NFL statistics. It's been going pretty well, I've been able to pull tables from other sites, until I tried to get NFL combine table from this one particular site.

It gives me the error message after df_list = pd.read_html(html)

The error I get is:

TypeError: ufunc 'add' did not contain a loop with signature matching types dtype('<U1') dtype('<U1') dtype('<U1')

Here's the code I've been using at other sites that worked really well.

import requests
import pandas as pd
df = pd.DataFrame()

url = 'http://nflcombineresults.com/nflcombinedata_expanded.php?
       year=1987&pos=&college='
html = requests.get(url).content
df_list = pd.read_html(html)
df = df_list[-1]

I've read and seen a little bit about BeautifulSoup, but the simplicity of the pd.read_html() is just so nice and compact. So I don't know if there's a quick fix that I am not aware of, or if I need to indeed dive into BeautifulSoup to get these tables from 1987 - 2017.

Upvotes: 0

Views: 2106

Answers (2)

payne
payne

Reputation: 14177

This isn't shorter, but may be more robust:

import requests
import pandas as pd
from bs4 import BeautifulSoup

A convenience function:

def souptable(table):
    for row in table.find_all('tr'):
        yield [col.text for col in row.find_all('td')]

Return a DataFrame with data loaded for a given year: 

def getyear(year):
    url = 'http://nflcombineresults.com/nflcombinedata_expanded.php?year=%d&pos=&college=' % year
    r = requests.get(url)
    soup = BeautifulSoup(r.text, 'lxml')
    data = list(souptable(soup.table))
    df = pd.DataFrame(data[1:], columns=data[0])
    df = df[pd.notnull(df['Name'])]
    return df.apply(pd.to_numeric, errors="ignore")

This function slices out the heading row when the DataFrame is created, uses the first row for column names, and filters out any rows with an empty Name value.

Finally, concatenate up as many years as you need into a single DataFrame:

dfs = pd.concat([getyear(year) for year in range(1987, 1990)])

Upvotes: 1

chitown88
chitown88

Reputation: 28565

Ok. After doing some more research. I looks like the issue is that the last row is one merged cell, and that's possibly where the issue is coming in. So I did go into using BeautifulSoup to pull the data. Here is my solution:

import requests
import pandas as pd
from bs4 import BeautifulSoup

I wanted to pull for each year from 1987 to 2017

seasons = list(range(1987, 2018))
df = pd.DataFrame()
temp_df = pd.DataFrame()

So it would run through each year. Appending each cell into a new row. Then again knowing the last cell is a "blank", I eliminate that last row by defining the dataframe as df[:-1] before it loops and appends the next years data.

for i in seasons:
    df = df[:-1] 
    url = 'http://nflcombineresults.com/nflcombinedata_expanded.php?
       year=%s&pos=&college=' % (i)
    r = requests.get(url) 
    soup = BeautifulSoup(r.text, 'lxml')
    for tr in soup.table.find_all('tr'):
        row = [td.text for td in tr.find_all('td')]
        temp_df = row
        df = df.append(temp_df, ignore_index = True)

Finally, since there is no new year to append, I need to eliminate the last row. Then I reshape the dataframe into the 16 columns, rename the columns from the first row, and then eliminate the row headers within the dataframe. 

df = df[:-1]    
df = (pd.DataFrame(df.values.reshape(-1, 16)))
df.columns = df.iloc[0]
df = df[df.Name != 'Name']

I'm still learning python, so any input, advice, any respectful constructive criticism is always welcome. Maybe there is a better, more appropriate solution?

Upvotes: 1

Related Questions