Scrape HTML Table as Dataframe using Python Pandas and R RVest - Hands-on Tutorial

Key Takeaways

dear fellow coders welcome to one little coder data collection is a very important process in the data science life cycle a lot of times you do not get the data that you want let us say that you are working in an organization and suddenly you want third party data you don't want the data and you cannot afford to buy the data as well so a lot of scenarios like this you finally end up sourcing the data outside using web scraping so web scraping is a method where you try to programmatically extract the data that you want from the internet or some from web page and this being a data science process a lot of times you want this data to be in a data frame because we mostly deal with tabular data and you want the data that you have scraped especially a table into a data frame format and many times you would actually see some table especially html table available on a website and then you just want to extract it and then start using it as a data frame to give you an example if you go to the page wikipedia page of world population you would see a lot of tables now let us say that you won't extract this particular information in a typical web scraping scenario there are a lot of things that you have to do to extract this particular table and then make a data frame and make it some some kind of insight let's say you want to make a bar chart or something but there are a couple of solutions that are available to make it extremely easier for you what what will it do in simple one or two lines of python or r code you can basically extract this table make it into a data frame and then make a wonderful visualization or data inside on top of it so in this video i'm going to show you how you can extract html table like this from either python or r using pandas or r waste respectively so first we are going to look at a python example then we are going to look at an r example so for python we are going to use pandas library for our we are going to use our waste library so what we are going to do here is go to this web page extract the content extract the table and make a beautiful visualization just to say that we have successfully managed to extract data out of it so let's get started the first thing that we want to know is we want to know whether the library that we want to use is available or not i'm using a google collab environment on google collab you can actually see that pandas which i'm going to use for extracting the web page table and c bond which i'm going to use for visualization are already available so the next thing is i want to define the url that i'm going to collect data from so this is the page from where i'm going to collect the url the world population page from wikipedia so this is exactly where my content of interest or the table of interest is available so i'm going to extract this page and unlike a typical web scraping process we are not going to separately read the web page content and then extract table that's what you would typically do in a typical web scraping process but here we can use a very beautiful function that is available and pandas says read underscore html so read underscore html will help you get any table that is available on an html page so if you read the description read html tables into a list of data frame objects so to repeat again when you have html tables in a web page this function this method will help you read them into a list of data frames because you have a lot of tables so every table is like a list element and all you have to give is html content or the url from where you have to extract one more thing that i would like to highlight at this point is this will work only when the html table actually has any table tag so you can see here now this html table is basically designed using a table tag but a lot of cases you would see on the internet that a table that looks like a table has been actually designed with different tags like arranging separate diff tags so this whatever we are going to see in this video would not work if you have a table that looks like a table but it is designed using diff tag the iv tag but it will work only if there is a table tag table tag so that's why i mentioned in the title as well extract html table with pandas as data frame in python so now that we know that our web page has an html table and we want to extract this table and this is our url we have already imported pandas and c1 we have defined the url the next step is we have to do pd dot read underscore html so pd dot read underscore html will help us read the html table from this url so pd dot read underscore html of the url and assign it to the object tables once you read this it's going to take a couple of seconds because it's going to extract the entire page content and read the table for you now that this is done you can now print or see the length of the tables tables you can see the length which should i do okay so there are 26 elements in this so and if you see the type it would basically be a list so it is a list of data frames so there are a lot of data frames that it has whatever this function this method has identified as a table would have been a data frame inside this list so every element in this is a list if you want to quickly check out you can say tables of 0 and then when you see you should ideally see a table yeah you can see a table here so where does this table exist so if you copy this go here control v control you can see this is this is that table so this is the table that is the first element in the list we are not interested in this table but what we are interested is in this particular table so let's go ahead and then try to see where the table exists so you can either iteratively see or you can just print the entire list and then try to see okay where my table the table that i am interested in is available so uh you can you can quickly see here that like the first table the second table and the third table and most likely it looks like uh fourth table let's let's let's just quickly see the fourth table you can see the fourth table is where we have got the country the population so these are the two columns that we are interested in and the rank of course but this is sorted based on population so we don't need so the next thing that we're going to do is assign this this particular data frame into a new table like new data frame sorry so assign this table tables of 4 into a new table we call it pop table because it's a population table if you look at the columns of pop table you get rank country population and everything that you have got here and if you want to also see the types the types you can see the rank is a number again same population as a number so basically this is a character and this is a number so we are good with that so the next step is because we have already got the table we are going to make a bar chart out of it so what do we want to make a bar chart out of we want to make country and population in a bar chart so our data is pop table our y-axis is country the reason i'm giving y-axis is country is because let me start with x-axis so you would know why okay if you make like this you would have the labels overlap so my easiest trick always to avoid this overlap is swap the axis then you should have this on the y-axis so you don't have to deal with the labels being overlapping on each other so you can you can see this is this is basically on on a different scale altogether but you can see the bar chart successfully being displayed after you extracted the table and made into a data frame and then displayed like made a chart or insight out of it so all this has been done just simply using pandas and seaborn we are not using beautiful soup we are not using scrapey we are not using any web scraping library but simply using the read underscore html lib method and pandas we have managed to extract the tables that are available from this url and then display them and make it a data frame and then make a visualization out of it so the python part where we wanted to extract the table is completed use pointers read underscore html extract the table and then find the table that you want make it due to or it's already data frame assign it to a new object make a beautiful insider chart out of it that's your python now let us see how to do the same in our using ros library so again like i said extracting table is one of the most important things a lot of times data scientists do and because they do not know how easy it is to do with python and r they might actually go with a lot of traditional ways that is not bad or not wrong but when you have easier solution that are very easy for you to do you should probably go with the easier solution and in this case i'm going to use our waste which is beautiful soups equivalent in r but again it gives you a bunch of more flexibilities that beautiful soup might not give you so our waste is the library that we are going to use let me rode our waste and if you are wondering how am i using google collab or sorry r in google collab i've got a separate video where i've shown you how to use r and google collab you can definitely check it out harvest is already available so next i have to define the url let me copy this url and paste it here than the existing url so define the url and then it's just a sing string object so the next thing is in this case we're going to follow a typical web scraping process so the first step starts with extracting the content of the html page so you're going to simply extract the content of the html page very coincidently the function that you're going to use the name the method name here is read underscore html here you underscore html as well in r but it does do different thing here on pandas read underscore html gives you only the tables but here it gives you a lot more than tables so the entire html content so from ros i'm going to do read underscore html and then assign it to the html content object so at this point you can quickly take a look at html content and you would see that it's an html document it has two elements head and body and then you have blah blah blah you can check everything else now that we know we have got html content now we have to extract the html tables from the html content so you have already extracted this content from this now you are going to specifically look for html table tag so the way you can do that is html underscore table and pass this html content object so now what would happen is if you do not give fill is equal to true is when you have tables that are imbalanced when you have tables where the scraping is not proper it might ask you to fill it but because here it worked out you don't have to be bothered so now we have got a tables object which is the output of html underscore table from html content let's look at what is there inside it when i print this oh it's going to print over you can see bunch of tables bunch of table objects so now you just have to think what is required for us and let's let's quickly see where is the table that we want which is specifically related okay i'm just i think okay oops i think which is specifically related to the population so i think this is the one that we want oh this is continent this is the one we wanted this is the table which is the fifth element so what i'm going to do here is i'm going to just print the fifth element and we have got the country and the population and unlike pandas you can actually see this is still being read as a character because of commas that we have got but unlike pandas again here you have also got this element which is the percentage so now what we can start doing is we can start processing the data so i'm going to assign this to a data frame i'm going to call it d5 df and then i'm going to assign it and so because df is a list we need to extract and then put df into a new data frame so i'm going to just overwrite it and after i overwrite it if we run this back you can see now this is a table data frame and i'm going to now mutate it and after i do that you can see this is double so again i'm going to assign it back to this so at this point i have got let me just add this snippet that i removed df okay so now at this point you can see that we have got a data frame and we have got the column names and now all we have to do is we have to plot them so i have already loaded ideas so i don't need to load this again i've got df and then i've got the x axis called popular country and then the y-axis is population let me run this oh you can you can already see that if you want to change y and x-axis you can again do the same thing that we did in python and if you want to let's say arrange it let's say new date of country is equal to fct reorder country population you can see that it is being reordered like you have got in the uh descending order like where the country with population most is already available so we have successfully managed to use harvest to extract tables from a web page and then also make some kind of insider with data visualization on top of it so if you had seen this video from the start you would have known that how easy it is to extract table from a web page using both r and python and especially instead of taking a traditional web scraping route we took a slightly easier route or more abstracted route where we use pandas read underscore html and also we used html underscore table from rvs to only extract the table component table tag other than anything else but if you still want to take a traditional web scraping route you can still do it but as a data scientist if your objective is to extract table content and put it in a data frame and access or manipulate with python our typically pandas or direvers then this should give you the solution that you might be looking for if you have any comments please let me know in the comment section otherwise i might link this google collab both the google collab notebooks the youtube description please check it out please give a thumbs up and share your feedback with me stay safe happy coding