Reading HTML table with data spread across rows

Question

I have extracted a HTML table using BeautifulSoup and would like to import it into a pandas DataFrame. However, data from the original table is spread out across multiple rows. Here are two entries for reference:

<table>
    
    <tbody><tr>
      <td>Record : 1 of 749</td>
      </tr>
    <tr>
      <td width="111">Patients Name</td>
        <td width="4">:</td>
        <td colspan="4">Andrew Smith</td>
        </tr>
    <tr>
      <td>Admit Date</td>
      <td>:</td>
      <td width="189">20-MAR-2018</td>
      <td>Group Number </td>
      <td>:</td>
      <td>17</td>
    </tr>
    <tr>
      <td>Address</td>
        <td>:</td>
        <td>123 Sunshine Ave </td>
        <td>Postal Code </td>
        <td>:</td>
        <td>12345</td>
    </tr>
    <tr>
      <td>Blood Type</td>
        <td>:</td>
        <td>A	</td>
        <td width="96">Ward Class</td>
        <td width="4">:</td>
        <td width="174">A</td>
      </tr>
    <tr>
      <td>Age</td>
        <td>:</td>
        <td>45</td>
        <td>Height</td>
        <td>:</td>
        <td>
		174cm	
		</td>
      </tr>
    <tr>
      <td>Weight</td>
        <td>:</td>
        <td>102kg</td>
        <td>ID</td>
        <td>:</td>
        <td>
		013</td>
      </tr>
    <tr>
      <td><hr/></td>
        </tr>
    
    <tr>
      <td>Record : 2 of 749</td>
      </tr>
    <tr>
      <td width="111">Patients Name</td>
        <td width="4">:</td>
        <td colspan="4">Margaret Chow</td>
        </tr>
    <tr>
      <td>Admit Date</td>
      <td>:</td>
      <td width="189">19-MAR-2018</td>
      <td>Group Number </td>
      <td>:</td>
      <td>14</td>
    </tr>
    <tr>
      <td>Address</td>
        <td>:</td>
        <td>5 Mango Beach </td>
        <td>Postal Code </td>
        <td>:</td>
        <td>54321</td>
    </tr>
    <tr>
      <td>Blood Type</td>
        <td>:</td>
        <td>B	</td>
        <td width="96">Ward Class</td>
        <td width="4">:</td>
        <td width="174">B2</td>
      </tr>
    <tr>
      <td>Age</td>
        <td>:</td>
        <td>32</td>
        <td>Height</td>
        <td>:</td>
        <td>
		154cm	
		</td>
      </tr>
    <tr>
      <td>Weight</td>
        <td>:</td>
        <td>52kg</td>
        <td>ID</td>
        <td>:</td>
        <td>
		051</td>
      </tr>
    <tr>
      <td><hr/></td>
        </tr>
    
  </tbody></table>

I have used the following code to extract the above table into a pandas DataFrame:

import pandas as pd
table = str(table)
df = pd.read_html(table)
df = pd.DataFrame(df)
df

My df looks like this:

but I would like it to be a DataFrame with columns ['Patients Name', 'Admit Date', 'Group Number', 'Address', 'Postal Code', 'Blood Type', 'Ward Class', 'Age', 'Height', 'Weight', 'ID'].

Am new to this. Greatly appreciate any advice!

Answer 1

import pandas as pd
from bs4 import BeautifulSoup as bs
soup = bs(table, 'html.parser')
df = pd.DataFrame() # you can add index and column details at this point too
row_index = -1
for row in soup.find_all('tr'):
    if row.find('td').find('hr'): # few rows has a horizontal line; skipping them
        continue
    if len(row.find_all('td')) == 1: # skipping the row stating Record : 1 of ...
    #if row.find_all('td')[0].get_text().startswith('Record :'):
        row_index += 1
        continue
    tds = [td.get_text().strip() for td in row.find_all('td')]
    df.at[row_index, tds[0]] = tds[2]
    if len(tds) > 3: #few rows have multiple tds; might have to make this dynamic if its more than 2 fields per row
        df.at[row_index, tds[3]] = tds[5]

This is my first time with web-scraping too and I enjoyed figuring out a solution! This piece of code works for your defined problem. You might have to change certain conditions depending on the the table structure.

PS: This is my first answer at Stack Overflow and I really hope this helps :)