通过pandas读取html中的表格
Reading HTML tables with Pandas
Posted by Chris Moffitt in articles
![article header image](https://pbpython.com/images/html-to-pandas-header.png)
Introduction
The pandas read_html() function is a quick and convenient way to turn an HTML table into a pandas DataFrame. This function can be useful for quickly incorporating tables from various websites without figuring out how to scrape the site’s HTML. However, there can be some challenges in cleaning and formatting the data before analyzing it. In this article, I will discuss how to use pandas read_html()
to read and clean several Wikipedia HTML tables so that you can use them for further numeric analysis.
Basic Usage
For the first example, we will try to parse this table from the Politics section on the Minnesota wiki page.
![MN Voting History](https://pbpython.com/images/mn_voting_history.png)
The basic usage is of pandas read_html
is pretty simple and works well on many Wikipedia pages since the tables are not complicated. To get started, I am including some extra imports we will use for data cleaning for more complicated examples:
import pandas as pd
import numpy as np
import matplotlib.pyplot as plt
from unicodedata import normalize
table_MN = pd.read_html('https://en.wikipedia.org/wiki/Minnesota')
The unique point here is that table_MN
is a list of all the tables on the page:
print(f'Total tables: {len(table_MN)}')
Total tables: 38
With 38 tables, it can be challenging to find the one you need. To make the table selection easier, use the match
parameter to select a subset of tables. We can use the caption “Election results from statewide races” to select the table:
table_MN = pd.read_html('https://en.wikipedia.org/wiki/Minnesota', match='Election results from statewide races')
len(table_MN)
1
df = table_MN[0]
df.head()
Year | Office | GOP | DFL | Others | |
---|---|---|---|---|---|
0 | 2018 | Governor | 42.4% | 53.9% | 3.7% |
1 | 2018 | Senator | 36.2% | 60.3% | 3.4% |
2 | 2018 | Senator | 42.4% | 53.0% | 4.6% |
3 | 2016 | President | 44.9% | 46.4% | 8.6% |
4 | 2014 | Governor | 44.5% | 50.1% | 5.4% |
Pandas makes it easy to read in the table and also handles the year column that spans multiple rows. This is an example where it is easier to use pandas than to try to scrape it all yourself.
Overall, this looks ok until we look at the data types with df.info()
:
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 24 entries, 0 to 23
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Year 24 non-null int64
1 Office 24 non-null object
2 GOP 24 non-null object
3 DFL 24 non-null object
4 Others 24 non-null object
dtypes: int64(1), object(4)
memory usage: 1.1+ KB
We need to convert the GOP, DFL and Other columns to numeric values if we want to do any analysis.
If we try:
df['GOP'].astype('float')
We get an error:
ValueError: could not convert string to float: '42.4%'
The most likely culprit is the %
. We can get rid of it using pandas replace()
function. I covered this in some detail in a previous article.
df['GOP'].replace({'%':''}, regex=True).astype('float')
Which looks good:
0 42.4
1 36.2
2 42.4
3 44.9
<...>
21 63.3
22 49.1
23 31.9
Name: GOP, dtype: float64
Note, that I had to use the regex=True
parameter for this to work since the %
is a part of the string and not the full string value.
Now, we can call replace all the %
values and convert to numbers using pd.to_numeric()
and apply()
df = df.replace({'%': ''}, regex=True)
df[['GOP', 'DFL', 'Others']] = df[['GOP', 'DFL', 'Others']].apply(pd.to_numeric)
df.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 24 entries, 0 to 23
Data columns (total 5 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Year 24 non-null int64
1 Office 24 non-null object
2 GOP 24 non-null float64
3 DFL 24 non-null float64
4 Others 24 non-null float64
dtypes: float64(3), int64(1), object(1)
memory usage: 1.1+ KB
df.head()
Year | Office | GOP | DFL | Others | |
---|---|---|---|---|---|
0 | 2018 | Governor | 42.4 | 53.9 | 3.7 |
1 | 2018 | Senator | 36.2 | 60.3 | 3.4 |
2 | 2018 | Senator | 42.4 | 53.0 | 4.6 |
3 | 2016 | President | 44.9 | 46.4 | 8.6 |
4 | 2014 | Governor | 44.5 | 50.1 | 5.4 |
This basic process works well. The next example is a little trickier.
More Advanced Data Cleaning
The previous example showed the basic concepts. Frequently more cleaning is needed. Here is an example that was a little trickier. This example continues to use Wikipedia but the concepts apply to any site that has data in an HTML table.
What if we wanted to parse the US GDP table show below?
![US GDP Table](https://pbpython.com/images/us_gdp.png)
This one was a little harder to use match to get only one table but matching on ‘Nominal GDP’ gets the table we want as the first one in the list.
table_GDP = pd.read_html('https://en.wikipedia.org/wiki/Economy_of_the_United_States', match='Nominal GDP')
df_GDP = table_GDP[0]
df_GDP.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 41 entries, 0 to 40
Data columns (total 9 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Year 41 non-null object
1 Nominal GDP(in bil. US-Dollar) 41 non-null float64
2 GDP per capita(in US-Dollar) 41 non-null int64
3 GDP growth(real) 41 non-null object
4 Inflation rate(in percent) 41 non-null object
5 Unemployment (in percent) 41 non-null object
6 Budget balance(in % of GDP)[107] 41 non-null object
7 Government debt held by public(in % of GDP)[108] 41 non-null object
8 Current account balance(in % of GDP) 41 non-null object
dtypes: float64(1), int64(1), object(7)
memory usage: 3.0+ KB
Not surprisingly we have some cleanup to do. We can try to remove the %
like we did last time:
df_GDP['GDP growth(real)'].replace({'%': ''}, regex=True).astype('float')
Unfortunately we get this error:
ValueError: could not convert string to float: '−5.9\xa0'
The issue here is that we have a hidden character, xa0
that is causing some errors. This is a “non-breaking Latin1 (ISO 8859-1) space”.
One option I played around with was directly removing the value using replace
. It worked but I worried about whether or not it would break with other characters in the future.
After going down the unicode rabbit hole, I decided to use normalize
to clean this value. I encourage you to read this article for more details on the rationale for my approach.
I also have found issues with extra spaces getting into the data in some of the other tables. I built a small function to clean all the text values. I hope others will find this helpful:
from unicodedata import normalize
def clean_normalize_whitespace(x):
if isinstance(x, str):
return normalize('NFKC', x).strip()
else:
return x
I can run this function on the entire DataFrame using applymap
:
df_GDP = df_GDP.applymap(clean_normalize_whitespace)
applymap
performanceapplymap
This function is very slow so you should be judicious in using it.The applymap
function is a very inefficient pandas function. You should not use it very often but in this case, the DataFrame is small and cleaning like this is tricky so I think it is a useful trade-off.
One thing that applymap
misses is the columns. Let’s look at one column in more detail:
df_GDP.columns[7]
'Government debt held by public(in\xa0% of GDP)[108]'
We have that dreaded xa0%
in the column names. There are a couple of ways we could go about cleaning the columns but I’m going to use clean_normalize_whitespace()
on the columns by converting the column to a series and using apply
to run the function. Future versions of pandas may make this a little easier.
df_GDP.columns = df_GDP.columns.to_series().apply(clean_normalize_whitespace)
df_GDP.columns[7]
'Government debt held by public(in % of GDP)[108]'
Now we have some of the hidden characters cleaned out. What next?
Let’s try it out again:
df_GDP['GDP growth(real)'].replace({'%': ''}, regex=True).astype('float')
ValueError: could not convert string to float: '−5.9 '
This one is really tricky. If you look really closely, you might be able to tell that the −
looks a little different than the -
. It’s hard to see but there is actually a difference between the unicode dash and minus. Ugh.
Fortunately, we can use replace
to clean that up too:
df_GDP['GDP growth(real)'].replace({'%': '', '−': '-'}, regex=True).astype('float')
0 -5.9
1 2.2
2 3.0
3 2.3
4 1.7
<...>
38 -1.8
39 2.6
40 -0.2
Name: GDP growth(real), dtype: float64
One other column we need to look at is the Year
column. For 2020, it contains “2020 (est)” which we want to get rid of. Then convert the column to an int. I can add to the dictionary but have to escape the parentheses since they are special characters in a regular expression:
df['Year'].replace({'%': '', '−': '-', '\(est\)': ''}, regex=True).astype('int')
0 2020
1 2019
2 2018
3 2017
4 2016
<...>
40 1980
Name: Year, dtype: int64
Before we wrap it up and assign these values back to our DataFrame, there is one other item to discuss. Some of these columns should be integers and some are floats. If we use pd.numeric()
we don’t have that much flexibility. Using astype()
we can control the numeric type but we don’t want to have to manually type this for each column.
The astype()
function can take a dictionary of column names and data types. This is really useful and I did not know this until I wrote this article. Here is how we can define the column data type mapping:
col_type = {
'Year': 'int',
'Nominal GDP(in bil. US-Dollar)': 'float',
'GDP per capita(in US-Dollar)': 'int',
'GDP growth(real)': 'float',
'Inflation rate(in percent)': 'float',
'Unemployment (in percent)': 'float',
'Budget balance(in % of GDP)[107]': 'float',
'Government debt held by public(in % of GDP)[108]': 'float',
'Current account balance(in % of GDP)': 'float'
}
Here’s a quick hint. Typing this dictionary is slow. Use this shortcut to build up a dictionary of the columns with float
as the default value:
dict.fromkeys(df_GDP.columns, 'float')
{'Year': 'float',
'Nominal GDP(in bil. US-Dollar)': 'float',
'GDP per capita(in US-Dollar)': 'float',
'GDP growth(real)': 'float',
'Inflation rate(in percent)': 'float',
'Unemployment (in percent)': 'float',
'Budget balance(in % of GDP)[107]': 'float',
'Government debt held by public(in % of GDP)[108]': 'float',
'Current account balance(in % of GDP)': 'float'}
I also created a single dictionary with the values to replace:
clean_dict = {'%': '', '−': '-', '\(est\)': ''}
Now we can call replace on this DataFrame, convert to the desired type and get our clean numeric values:
df_GDP = df_GDP.replace(clean_dict, regex=True).replace({
'-n/a ': np.nan
}).astype(col_type)
df_GDP.info()
<class 'pandas.core.frame.DataFrame'>
RangeIndex: 41 entries, 0 to 40
Data columns (total 9 columns):
# Column Non-Null Count Dtype
--- ------ -------------- -----
0 Year 41 non-null int64
1 Nominal GDP(in bil. US-Dollar) 41 non-null float64
2 GDP per capita(in US-Dollar) 41 non-null int64
3 GDP growth(real) 41 non-null float64
4 Inflation rate(in percent) 41 non-null float64
5 Unemployment (in percent) 41 non-null float64
6 Budget balance(in % of GDP)[107] 40 non-null float64
7 Government debt held by public(in % of GDP)[108] 41 non-null float64
8 Current account balance(in % of GDP) 40 non-null float64
dtypes: float64(7), int64(2)
memory usage: 3.0 KB
Which looks like this now:
Year | Nominal GDP(in bil. US-Dollar) | GDP per capita(in US-Dollar) | GDP growth(real) | Inflation rate(in percent) | Unemployment (in percent) | Budget balance(in % of GDP)[107] | Government debt held by public(in % of GDP)[108] | Current account balance(in % of GDP) | |
---|---|---|---|---|---|---|---|---|---|
0 | 2020 | 20234.0 | 57589 | -5.9 | 0.62 | 11.1 | NaN | 79.9 | NaN |
1 | 2019 | 21439.0 | 64674 | 2.2 | 1.80 | 3.5 | -4.6 | 78.9 | -2.5 |
2 | 2018 | 20580.2 | 62869 | 3.0 | 2.40 | 3.9 | -3.8 | 77.8 | -2.4 |
3 | 2017 | 19519.4 | 60000 | 2.3 | 2.10 | 4.4 | -3.4 | 76.1 | -2.3 |
4 | 2016 | 18715.0 | 57878 | 1.7 | 1.30 | 4.9 | -3.1 | 76.4 | -2.3 |
Just to prove it works, we can plot the data too:
plt.style.use('seaborn-whitegrid')
df_clean.plot.line(x='Year', y=['Inflation rate(in percent)', 'Unemployment (in percent)'])
![US GDP Chart](https://pbpython.com/images/us_gdp_chart.png)
If you are closely following along, you may have noticed the use of a chained replace
call:
.replace({'-n/a ': np.nan})
The reason I put that in there is that I could not figure out how to get the n/a
cleaned using the first dictionary replace
. I think the issue is that I could not predict the order in which this data would get cleaned so I decided to execute the replace in two stages.
I’m confident that if there is a better way someone will point it out in the comments.
Full Solution
Here is a compact example of everything we have done. Hopefully this is useful to others that try to ingest data from HTML tables and use them in a pandas DataFrame:
import pandas as pd
import numpy as np
from unicodedata import normalize
def clean_normalize_whitespace(x):
""" Normalize unicode characters and strip trailing spaces
"""
if isinstance(x, str):
return normalize('NFKC', x).strip()
else:
return x
# Read in the Wikipedia page and get the DataFrame
table_GDP = pd.read_html(
'https://en.wikipedia.org/wiki/Economy_of_the_United_States',
match='Nominal GDP')
df_GDP = table_GDP[0]
# Clean up the DataFrame and Columns
df_GDP = df_GDP.applymap(clean_normalize_whitespace)
df_GDP.columns = df_GDP.columns.to_series().apply(clean_normalize_whitespace)
# Determine numeric types for each column
col_type = {
'Year': 'int',
'Nominal GDP(in bil. US-Dollar)': 'float',
'GDP per capita(in US-Dollar)': 'int',
'GDP growth(real)': 'float',
'Inflation rate(in percent)': 'float',
'Unemployment (in percent)': 'float',
'Budget balance(in % of GDP)[107]': 'float',
'Government debt held by public(in % of GDP)[108]': 'float',
'Current account balance(in % of GDP)': 'float'
}
# Values to replace
clean_dict = {'%': '', '−': '-', '\(est\)': ''}
# Replace values and convert to numeric values
df_GDP = df_GDP.replace(clean_dict, regex=True).replace({
'-n/a ': np.nan
}).astype(col_type)
Summary
The pandas read_html()
function is useful for quickly parsing HTML tables in pages - especially in Wikipedia pages. By the nature of HTML, the data is frequently not going to be as clean as you might need and cleaning up all the stray unicode characters can be time consuming. This article showed several techniques you can use to clean the data and convert it to the proper numeric format. If you find yourself needing to scrape some Wikipedia or other HTML tables, these tips should save you some time.
If this is helpful to you or you have other tips, feel free to let me know in the comments.