I'm working with a number of files of this format (eliminated styling html):
<html xmlns:x="u:schemas-microsoft-com:office:excel">
<head>
<meta name="Generator" content="SAS Software Version 9.3, see www.sas.com">
<meta http-equiv="Content-type" content="charset=windows-1252">
</head>
<body>
<table class="table">
<colgroup>
<col>
<col>
<col>
<col>
</colgroup>
<colgroup>
<col>
<col>
</colgroup>
<thead>
<tr>
<td class="header" rowspan="2" colspan="4" scope="colgroup"> </td>
<td class="header" colspan="2" scope="colgroup">SubDistrict</td>
</tr>
<tr>
<td class="header" scope="col">Title1
<br>
<br>
</td>
<td class="header" scope="col">Title2
<br>
<br>
</td>
</tr>
</thead>
<tbody>
<tr>
<td class="rowheader" rowspan="12" scope="rowgroup">M1</td>
<td class="rowheader" scope="row">1.1</td>
<td class="rowheader" scope="row">var1</td>
<td class="rowheader" scope="row">TOTAL</td>
<td class="data">7</td>
<td class="data">7</td>
</tr>
<tr>
etc...
In the browser, they appear like this:
And I've written the following in Beautiful Soup, which I'm brand new to:
def read_xls(file):
f = open(file)
soup = BeautifulSoup(f.read(), 'html.parser')
table = soup.find_all('table')
#table[0].thead.find_all('tr')[1].td.get_text()
data = []
for tr in table[0].find_all('tr'):
temp = []
for td in tr.find_all('td'):
temp.append(td.get_text())
data.append(temp)
retu pd.DataFrame(data)
But my code is resulting in significant column alignment problems:
Any advice on how to improve my BeautifulSoup code to parse this more correctly? Thanks.