Pandas is the tool in Python for working with tables.
There's so much you can do once the data is in pandas,
so let's extract the data and get it into pandas.
Much of web scraping is cleaning and preparing data.
I'll show you how to iterate and clean to get the data you need,
how to refine selectors to get better results,
ways to resolve common scraping problems,
building a list of lists to prepare the data for pandas,
and making a pandas DataFrame.
I find that I normally go through a number of iterations as I refine the
selectors and clean the data to get the exact data that's needed.
We know we want table.wikitable, and soup.select will let us use CSS selectors,
so let's run the cell and see what we get.
We got exactly what we asked for, the entire table,
but we really just want the tbody with all the tr tags.
Let's add tbody to the selector and try again.
Better, but see the bracket?
We got back a list, when really all we want are the contents.
Let's add code to just get the zero element.
Remember lists in Python are zero based, so the first element is number 0.
That got just what we wanted, the contents of the table.
Many times I find it's best to break down the problem
and get the elements one by one.
We've now extracted just the table,
so we can keep going to get the rest of the data.
In the next cell, full_table is still a BeautifulSoup object,
so we can continue selecting.
To build our pandas DataFrame, we'll need a list with all the column headings,
so let's scrape that data.
The column headings are in th tags, so selecting tr space th will get that data.
It worked!
Only, we got a list again, and we still have all the html th tag markup.
Let's uncomment the code at the bottom and remove the
statement that prints table_head.
The new code is one way you can get just the text.
Let's run that.
Closer, but it's still not right.
See how Cumulative got blurred into production with no space.
Remember, I warned you about going through several iterations.
Let's try again.
In this cell, I've added a list called table_columns,
and we'll loop over all the elements in table_head,
then add them to the list.
Element is still a BeautifulSoup object,
and we can use the get_text method to clean up the results.
Separator is set to space, so that will use a space to separate elements.
Then I added strip=True to remove any white space from
the beginning and end of the text.
That's a lot better, and it might be good enough,
only spaces in the column names don't work so well in CSV,
or if we want to store our data in a database.
It would be better to replace the spaces with an underscore.
Let's try Iteration 3.
I've added a line that does exactly that.
It replaces space characters with an underscore.
Run the cell.
We're getting really close now.
The only thing I still don't like are the [b] and [c] characters.
Let's get rid of those with our final iteration.
I've imported re, that's Python's regex module, then created a regex string.
Regex is kind of ugly, but very powerful.
All this means to look for an underscore followed by a left bracket.
\w means any word character, so that will get either the b or c,
followed by a right bracket.
Brackets have meaning in regex, so we have to escape them with backslash bracket.
Then I added one more line that uses regex to
substitute a blank string for any matches.
This last iteration better work.
Looks great, just what we want.
Over time, you'll find that you build up a library of tricks for cleaning data,
and this gets a lot easier.
You may even start to build some function libraries
that handle all the common problems.
We're almost there.
All we need to do is extract the data rows and let pandas make the DataFrame.
In the next cell, I'm selecting all the tr elements.
Okay, we got a list of table_rows.
Really, though, for Pandas, what we want is a multidimensional list.
That means a list for every row in the table combined into a
higher level list, it's a list of list, and we'd like to skip the first row,
as we don't need the th column headers anymore.
In the Iteration 2 we create an empty table_data list,
then loop over all the table_rows.
Check if the index is greater than 0.
That will skip the first row.
Then build a list named row_list.
Start with an empty list,
and get the list of values by selecting all the td elements.
Element is still a BeautifulSoup object, so selecting works here.
And loop over all the values to append value.text to the list.
See the double brackets at the start of the results?
That's the list of lists that pandas wants.
The first bracket is the outer list.
Then the next bracket starts the first inner list, the first row of our table.
There's a comma, then the second row starts with another bracket and Q2 2013,
and so on.
It's almost right, but what are those \n values?
Well, they're code for new line, and we don't want that in our data.
One more tweak will do the trick.
For the final iteration, I added strip after value.text.
New line codes are considered white space, so strip will get rid of them.
I love it!
Now depending on your use case, there's more you could do.
You might want to remove commas or other punctuation from the numbers,
you might even want to convert the numeric strings to actual numbers,
but this will be good enough for now.
We're done with all the heavy lifting.
Just feed the table_data and table_columns list to pandas,
and wait for the magic.
There it is.
We've scraped a table from Wikipedia and put it into pandas.
There's nothing stopping us from total scraping domination.
You now know all the basics of web scraping with Python.
You can set up a Python project with a suitable version of Python
in appropriate packages using pyenv and pipenv.
Use Python's requests library to download a page and use
Jupyter Lab to further inspect the HTML page.
Plus,
you've learned some tricks for extracting and cleaning data
to get it ready to load it into pandas.
From here,
you'll learn more powerful and advanced techniques for scraping with Python.