Pandas and bs4 html scraping
I am extracting data from an html file, it is in a table format so I made this line of code to convert all the tables to a data frame with pandas.
dfs = pd.read_html("synced_contacts.html")
Now, printing the 2nd row of tables of the data frame
dfs[1]
The output is the following:
How can I do so that the information is not duplicated in two columns as shown in the image, and also separate "First NameDaniela" in "First Name" as first column and "Daniela" as value
Expected Output:
Table HTML structure:
<title>Synced contacts</title></head><body class="_5vb_ _2yq _a7o5"><div class="clearfix _ikh"><div class="_4bl9"><div class="_li"><div><table style="width:100%;background:white;position:fixed;z-index:99;"><tr style=""><td height="8" style="line-height:8px;"> </td></tr><tr style="background:white"><td style="text-align:left;height:28px;width:35px;"></td><td style="text-align:left;height:28px;"><img src="files/Instagram-Logo.png" height="28" alt="Instagram" /></td></tr><tr style=""><td height="5" style="line-height:5px;"> </td></tr></table><div style="width:100%;height:44px;"></div></div><div class="_a705"><div class="_3-8y _3-95 _a70a"><div class="_a70d"><div class="_a70e">Synced contacts</div><div class="_a70f">Contacts you've synced</div></div></div><div class="_a706" role="main"><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Daniela</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Last Name<div><div>Guevara</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3017004914</div></div></td></tr></table></div><div class="_3-94 _a6-o"></div></div><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Marianna</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3125761972</div></div></td></tr></table></div><div class="_3-94 _a6-o"></div></div><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Ana Maria</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Last Name<div><div>Garzon</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3214948507</div></div></td></tr></table></div>Solution
It is caused by the struture, everything is placed in a single <td> and will be concatenated, the colspan is creating the second column.
pd.read_html() is a good for the first and easiest pass, not necessarily that it will handle every messy table in real life.
So instead using the pd.read_html() you could use BeautifulSoup directly to fit the behavior of how to scrape to your needs and create a dataframe from the result. .stripped_strings is used here to split the texts of each element in the <tr> to a list.
pd.DataFrame(
[
dict([list(row.stripped_strings)for row in t.select('tr')])
for t in soup.select('table:has(._2pin)')
]
)
Example
from bs4 import BeautifulSoup
html='''
<title>Synced contacts</title></head><body class="_5vb_ _2yq _a7o5"><div class="clearfix _ikh"><div class="_4bl9"><div class="_li"><div><table style="width:100%;background:white;position:fixed;z-index:99;"><tr style=""><td height="8" style="line-height:8px;"> </td></tr><tr style="background:white"><td style="text-align:left;height:28px;width:35px;"></td><td style="text-align:left;height:28px;"><img src="files/Instagram-Logo.png" height="28" alt="Instagram" /></td></tr><tr style=""><td height="5" style="line-height:5px;"> </td></tr></table><div style="width:100%;height:44px;"></div></div><div class="_a705"><div class="_3-8y _3-95 _a70a"><div class="_a70d"><div class="_a70e">Synced contacts</div><div class="_a70f">Contacts you've synced</div></div></div><div class="_a706" role="main"><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Daniela</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Last Name<div><div>Guevara</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3017004914</div></div></td></tr></table></div><div class="_3-94 _a6-o"></div></div><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Marianna</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3125761972</div></div></td></tr></table></div><div class="_3-94 _a6-o"></div></div><div class="pam _3-95 _2ph- _a6-g uiBoxWhite noborder"><div class="_a6-p"><table style="table-layout: fixed;"><tr><td colspan="2" class="_2pin _a6_q">First Name<div><div>Ana Maria</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Last Name<div><div>Garzon</div></div></td></tr><tr><td colspan="2" class="_2pin _a6_q">Contact Information<div><div>3214948507</div></div></td></tr></table></div>
'''
soup = BeautifulSoup(html)
pd.DataFrame(
[
dict([list(row.stripped_strings)for row in t.select('tr')])
for t in soup.select('table:has(._2pin)')
]
)
Output
| First Name | Last Name | Contact Information | |
|---|---|---|---|
| 0 | Daniela | Guevara | 3017004914 |
| 1 | Marianna | nan | 3125761972 |
| 2 | Ana Maria | Garzon | 3214948507 |
- AttributeError: install_layout when attempting to install a package in a virtual environment
- Python list comprehension - want to avoid repeated evaluation
- Hash algorithm for dynamic growing/streaming data?
- matplotlib - making labels for violin plots
- Python How to I check if last element has been reached in iterator tool chain?
- Polars and the Lazy API: How to drop columns that contain only null values?
- Why are my Mean, Var, and Std outputs from NumPy different from what the online grader expects?
- Correlation dataframe convertion from results from pl.corr
- Polars DataFrame transformation
- Discord rate limiting while only sending 1 request per minute
- Check if column contains (/,-,_, *or~) and split in another column - Pandas
- How to draw a rectangle at (x,y) in a PyQt GraphicsView?
- how to calculate correlation between ten columns with polars
- How to set class attribute with await in __init__
- Detect hindi encoding, response received from Facebook API in Python
- Is it possible to write a horizontal if statement with a multi-line body?
- Max length of items in list
- Cannot subclass multiprocessing Queue in Python 3.5
- How can I get notified of updates to Python packages in a unified way?
- Using python AST to traverse code and extract return statements
- merge groups of columns in a polars dataframe to single columns
- Group Pandas DataFrame by Continuous Date Ranges
- Flask login @login_required not working
- Odoo: one2many and many2one? KeyError:'___'
- merge some columns in a Polars dataframe and duplicate the others
- Python: Create table from string mixed with separators using FOR loops
- How do I type hint a method with the type of the enclosing class?
- How can I verify an emails DKIM signature in Python?
- Writing a class that accepts a callback in Python?
- Python Paramiko channel.exec_command not returning output intermittently