How to extract html tables from files, using pandas?

user_14363352 0
Viewed 926

I'm new to pandas, and I'm trying to extract some data from some HTML files.

How to convert multiple HTML tables that looks like this :

       PS4
Game Name | Price
GoW       | 49.99
FF VII R  | 59.99

       XBX
Game Name | Price
Gears 5   | 49.99
Forza 5   | 59.99

<table>
  <tr colspan="2">
    <td>PS4</td>
  </tr>
  <tr>
    <td>Game Name</td>
    <td>Price</td>
  </tr>
  <tr>
    <td>GoW</td>
    <td>49.99</td>
  </tr>
  <tr>
    <td>FF VII R</td>
    <td>59.99</td>
  </tr>
</table>

<table>
  <tr colspan="2">
    <td>XBX</td>
  </tr>
  <tr>
    <td>Game Name</td>
    <td>Price</td>
  </tr>
  <tr>
    <td>Gears 5</td>
    <td>49.99</td>
  </tr>
  <tr>
    <td>Forza 5</td>
    <td>59.99</td>
  </tr>
</table>

into a json object like this:

[
  { "Game Name": "Gow", "Price": "49.99", "platform": "PS4"},
  { "Game Name": "FF VII R", "Price": "59.99", "platform": "PS4"},
  { "Game Name": "Gears 5", "Price": "49.99", "platform": "XBX"},
  { "Game Name": "Forza 5", "Price": "59.99", "platform": "XBX"}
]

I tried to load the html file containing the tables with pandas.read_html(path/to/file) and it did return a list of DataFrames but I don't know how to extract the data after that, especially that the platform name is in the header and not as a separate column.

I'm using pandas because I'm extracting those tables from local htm files that contain other forms of tables and HTML code, so I use :

tables = pandas.read_html(file_path, match="Game Name")

to quickly isolate the tables I need with the match parameter based on that column name.

pandas data-mining html python
1 Answers
import pandas as pd

# list to save all dataframe from all tables in all files
df_list = list()

# list of files to load
list_of_files = ['test.html']

# iterate through your files
for file in list_of_files:
    
    # create a list of dataframes from the tables in the file
    dfl = pd.read_html(file, match='Game Name')
    
    # fix the headers and columns
    for d in dfl:

        # select row 1 as the headers
        d.columns = d.iloc[1]

        # select row 0, column 0 as the platform
        d['platform'] = d.iloc[0, 0]

        # selection row 2 and below as the data, row 0 and 1 were the headers
        d = d.iloc[2:]

        # append the cleaned dataframe to df_list
        df_list.append(d.copy())
        
# create a single dataframe
df = pd.concat(df_list).reset_index(drop=True)

# create a list of dicts from df
records = df.to_dict('records')

print(records)
[out]:
[{'Game Name': 'GoW', 'Price': '49.99', 'platform': 'PS4'},
 {'Game Name': 'FF VII R', 'Price': '59.99', 'platform': 'PS4'},
 {'Game Name': 'Gears 5', 'Price': '49.99', 'platform': 'XBX'},
 {'Game Name': 'Forza 5', 'Price': '59.99', 'platform': 'XBX'}]
user_77588040
Related