\n
Why this post?<\/h3>\n
As testers, we sometimes need real (or realistic) data for testing. But it is really hard to find data in the format you need. Often, we need to go to the websites such as Wikipedia <\/a>, extract the data and then do the formatting we need. Wikipedia tables have proven to be an excellent source of data for us<\/a>. So I thought I would share a couple of methods to scrape Wikipedia tables using Python. <\/p>\n Generally speaking, there are two basic tasks for scraping table data from a web page. First, get the HTML source. Second, parse the HTML to locate the table data. The key to scraping is looking at the HTML, understanding the page structure and figuring out how you want to pull the data.<\/p>\n We are going to scrape the table data (image below) from the Wikipedia web page for Hispanic and Latino population in USA<\/a>. This data includes States\/Territory, population growth percentage details etc., <\/p>\n Now that we know where our data is, we can start our coding. In this post I will show two methods to scrape the data: Note:<\/strong> If you have the option, I recommend the Pandas library because I found it magical!<\/p>\n Wikipedia module<\/a> has functions for searching Wikipedia, getting article summaries, getting data like links and images from a page, and more. However, we couldn’t find a way to scrape table data using Wikipedia library. That\u2019s why we used the Wikipedia module in combination with BeautifulSoup. <\/p>\n We used the Wikipedia library to search and get HTML source. We used Beautifulsoup to parse the HTML table. You will notice one difference between our implementation and other online solution: our implementation treats the table as a tree rather than as an orderly structure of Let\u2019s take a look at the code to see how this all works:<\/p>\n Step1: Get the HTML source<\/strong> Step2: Identify the table<\/strong> Step3: Extract the table data<\/strong> Great! Once you execute this code you should see below output –<\/p>\n I suspected that there might be an easier way to do this sort of thing. And my instinct was right. The other way is, using Pandas’ The output is –<\/p>\n After you call this function, you notice Pandas automatically fills in NaNs (Not a Number) for empty cells. I came away really impressed with how easy Pandas made parsing a relatively hard-to-parse table. <\/p>\n Note:<\/strong> This data may not be tidy enough or in the format needed by your project. You need to make it tidy before using it. You can refer to my other post Cleaning data with Python<\/a> for some tips and tricks on cleaning data and restructuring the data.<\/p>\n If you are a startup finding it hard to hire technical QA engineers, learn more about Qxf2 Services<\/a>.<\/strong><\/p>\n I hope you have found this post useful for web scraping with Python. Here are few references I found useful:
\nOverview<\/h3>\n
![\"List](\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2017\/05\/datascraping_lists.png\")
<\/a><\/p>\n
\n
\na) Method 1: Use Wikipedia module<\/a> and BeautifulSoup<\/a>
\nb) Method 2: Use Pandas<\/a> library<\/p>\n
\nMethod 1: Use Wikipedia module and BeautifulSoup<\/h3>\n
tr<\/code> and td<\/code> elements. <\/p>\n\r\nimport wikipedia, re\r\nfrom BeautifulSoup import BeautifulSoup\r\n\r\ndef get_hispanic_population_data():\r\n \"Get the details of hispanic and latino population by state\/territory\"\r\n wiki_search_string = \"hispanic and latino population\"\r\n wiki_page_title = \"List of U.S. states by Hispanic and Latino population\"\r\n wiki_table_caption = \"Hispanic and Latino Population by state or territory\"\r\n parsed_table_data = []\r\n\r\n search_results = wikipedia.search(wiki_search_string)\r\n for result in search_results:\r\n if wiki_page_title in result:\r\n my_page = wikipedia.page(result)\r\n #download the HTML source\r\n soup = BeautifulSoup(my_page.html())\r\n #Using a simple regex to do 'caption contains string'\r\n table = \r\n soup.find('caption',text=re.compile(r'%s'%wiki_table_caption)).findParent('table')\r\n rows = table.findAll('tr')\r\n for row in rows:\r\n children = row.findChildren(recursive=False)\r\n row_text = []\r\n for child in children:\r\n clean_text = child.text\r\n #This is to discard reference\/citation links\r\n clean_text = clean_text.split('[')[0]\r\n #This is to clean the header row of the sort icons\r\n clean_text = clean_text.split(' ')[-1]\r\n clean_text = clean_text.strip()\r\n row_text.append(clean_text)\r\n parsed_table_data.append(row_text)\r\n\r\n return parsed_table_data\r\n \r\n#----START OF SCRIPT\r\nif __name__==\"__main__\":\r\n print ('Hispanic and Latino population data in the USA looks like this:\\n\\n')\r\n hispanic_population_data = get_hispanic_population_data() \r\n for row in hispanic_population_data:\r\n print ('|'.join(row))\r\n<\/pre>\n
\nWe created a function and set up wiki_search_string<\/code>, wiki_page_title<\/code>, wiki_table_caption<\/code> variables. By using wikipedia.page()<\/code> method, we pull the HTML source based on the page title. The function returns the HTML of the page in the my_page<\/code> variable.<\/p>\n
\nNext, we pass this HTML to BeautifulSoup which turns it into a well-formatted DOM object. We are trying to extract table information about Hispanic and Latino Population details in the USA. With the help of BeautifulSoup\u2019s find()<\/code> command and a simple regex, we identify the right table based on the table’s caption. <\/p>\n
\nNow that we identified the table that we need, we need to parse this table. We pull all the tr<\/code> elements from the table. We use BeautifulSoup’s findChildren(recursive=False)<\/code> method to find the immediate children<\/u> of each row. Note that we do not bother identifying the columns using the td<\/code>. This is because the table is somewhat unstructured and contains images, links, line breaks and th<\/code> elements sprinkled in a few different places. In the above code, we have done some data cleaning to clean up few citation links and icons.<\/p>\n![\"Data](\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2017\/06\/datascraping_output.png\")
<\/a><\/p>\n
\nMethod 2: Use Pandas library<\/h3>\n
read_html()<\/code>. You can read HTML tables into a list of DataFrame objects. It finds the table element, does the parsing and creates a DataFrame. This function searches for table<\/code> elements and only for tr<\/code> and th<\/code> rows and td<\/code> elements within each tr<\/code> or th<\/code> element in the table. Below is a sample code to pass the HTML to pd.read_html().<\/p>\n\r\nimport pandas as pd\r\nimport wikipedia as wp\r\n \r\n#Get the html source\r\nhtml = wp.page(\"List of U.S. states by Hispanic and Latino population\").html().encode(\"UTF-8\")\r\ndf = pd.read_html(html)[0]\r\ndf.to_csv('beautifulsoup_pandas.csv',header=0,index=False)\r\nprint (df)\r\n<\/pre>\n![\"Data](\"https:\/\/qxf2.com\/blog\/wp-content\/uploads\/2017\/06\/datascraping_pandas.png\")
<\/a><\/p>\n
\nReferences:<\/h3>\n
\n1. Data Scraping<\/a>: Good article explaining about how to get data from the web, Scraping websites, tools that help to scrape.
\n2. Using Pandas for Data scraping<\/a>
\n3. Wikipedia Table data Scraping with Python and BeautifulSoup<\/a>This article shows you another way to use BeautifulSoup to scrape Wikipedia table data.<\/p>\n
\n